#!/usr/bin/perl 

#
# This program converts squirrelmail users from storing preferences
# and such in the filesystem to storing them in a database.
#
# Errata:
#
# It doesn't convert calendars.
# It converts dictionary word lists (email if you want my modified
# squirrelmail plugin that supports storing word lists in the database)
#


my $delete = 1;

my $db_user = 'DBUSER';
my $db_pass = 'DBPASS';
my $db_dsn = "DBI:mysql:database=DB;host=DBHOST";

my $addhostname = '@example.com';

my $prefdir = "/var/lib/squirrelmail/data";

my $passwd = '/etc/master.passwd';

my $disabled_pass = qr/^(?:\*$|HOLD|\*\*\*)/;
my $disabled_shell = qr/nologin/;



use DBI;
use File::Slurp;
use strict;

my $dbh = DBI->connect($db_dsn, $db_user, $db_pass) || die;

my %userok;
open(PASSWD, "<$passwd") || die;
while (<PASSWD>) {
	next unless /^(\w.*?):(.*?):(\d+):/;
	my $uname = $1;
	my $pass = $2;
	die unless /:([^:]*)$/;
	my $shell = $1;
	next if $shell eq '/sbin/nologin';
	next if $pass =~ /$disabled_pass/;
	next if $shell =~ /$disabled_shell/;
	$userok{$uname} = $_;
}
close(PASSWD);

chdir($prefdir) || die;

if ($delete && $addhostname) {
	$dbh->do("delete from userprefs where user regexp '$addhostname\$'")
		|| die;
	$dbh->do("delete from address where owner regexp '$addhostname\$'")
		|| die;
}

my %counts;
my %done;

for my $f (sort {$a cmp $b} read_dir(".")) {
	next if -d $f;
	next unless $f =~ /(.*)\.pref$/;
	my $u = $1;
	unless ($userok{$u}) {
		$counts{skipped}++;
		next;
	}
	my $user = ($u =~ /@/
		? $u
		: "$u$addhostname");
	my $identities;

	if ($u ne lc($u) && -e lc($u).".pref") {
		if (-M $f > -M lc($u).".pref") {
			print "preferring \L$u\E to $u\n";
		}
	}
	next if $done{lc($user)}++;

	if ($delete && ! $addhostname) {
		$dbh->do("delete from userprefs where user = ?", 
			undef, $user) || die;
		$dbh->do("delete from address where owner = ?", 
			undef, $user) || die;
	}

	$counts{users}++;

	open(PREF, "<$u.pref") || die;
	while(<PREF>) {
		die unless /^(\S+?)=(.*)/;
		my ($key, $val) = ($1, $2);
#		print "INSERT userprefs($user, $key, $val)\n";
		$dbh->do("insert into userprefs (user, prefkey, prefval) values (?, ?, ?)",
			undef, $user, $key, $val) || die;
		if ($key eq 'identities') {
			$identities = $val;
		}
		$counts{prefs}++;
	}
	close(PREF);

	if (-e "$u.abook") {
		$counts{abook}++;
		open(ABOOK, "<$u.abook") || die;
		while(<ABOOK>) {
			chomp;
			my ($nickname, $firstname, $lastname, $email, $label) = split(/\|/, $_);
			$dbh->do("insert into address (owner, nickname, firstname, lastname, email, label) values (?, ?, ?, ?, ?, ?)",
				undef, $user, $nickname, $firstname, $lastname, $email, $label) || die;
#			print "INSERT address($user, $nickname, $firstname, $lastname, $email, $label)\n";
			$counts{abook_entries}++;
		}
	}

	if (-e "$u.sig") {
		$counts{sigs}++;
		my $sig = read_file("$u.sig");
		$dbh->do("insert into userprefs (user, prefkey, prefval) values (?, ?, ?)",
			undef, $user, '___signature___', $sig) || die;
#		print "INSERT userprefs($user, ___signature___, $sig)\n";
	}

	for my $i (1..($identities-1)) {
		if (-e "$u.si$i") {
			$counts{sigs}++;
			my $sig = read_file("$u.sig");
			$dbh->do("insert into userprefs (user, prefkey, prefval) values (?, ?, ?)",
				undef, $user, "___sig${i}___", $sig) || die;
#			print "INSERT userprefs($user, ___sig${i}___, $sig)\n";
		}
	}

	if (-e "$u.words") {
		$counts{dicts}++;
		my $dict = read_file("$u.words");
		$dbh->do("insert into userprefs (user, prefkey, prefval) values (?, ?, ?)",
			undef, $user, '___dictionary___', $dict) || die;
#		print "INSERT userprefs($user, ___dictionary___, $dict)\n";
	}
}

for my $c (sort keys %counts) {
	printf "% 6d %s\n", $counts{$c}, $c;
}