#!/usr/bin/perl
use File::Copy;
require "prontolib.pl";
&read_prefs();

$conn=&open_db_conn;

&add_xuidl;
&add_serverstat;
&up_vfolders;
&update_addybook;
&fix_boxlist;

sub update_addybook
{
	$sql = "create table groups (id int not null, name text)";
	$conn->do($sql);
	if ($prefs{'DatabaseDriver'} eq "mysql") {
		$sql = "alter table addresses add groups text";		
	} elsif ($prefs{'DatabaseDriver'} eq "Pg") {
		$sql = "alter table addresss add column groups text";
	} else {
		&add_csv_column("$prefs{'MailDir'}/addresses","groups");
		return 1;
	}
	$conn->do($sql);
}	

sub up_vfolders
{


	$sql = "select id from boxlist where id > 100";
	$query=$conn->prepare($sql);
	$query->execute();
	while (my ($id) = $query->fetchrow_array()) {
		$sql2 = "update boxlist set id = $id+1000 where id = $id";
		$conn->do($sql2);
	}	
	$sql = "update boxlist set id = 1000 where id = 100";
	$conn->do($sql);		
}

sub add_serverstat
{
	if ($prefs{'DatabaseDriver'} eq "mysql") {
		$sql = "alter table messages add serverstat int";
	} elsif ($prefs{'DatabaseDriver'} eq "Pg") {
		$sql = "alter table messages add column serverstat int";
	} elsif ($prefs{'DatabaseDriver'} eq "CSV") {
		&add_csv_column("$prefs{'MailDir'}/messages","serverstat");
		return 1;
	}	
	$query = $conn->prepare($sql);
	$query->execute();
	$sql = "update messages set serverstat = 0";
	$conn->do($sql);
}

sub add_xuidl
{
	if ($prefs{'DatabaseDriver'} eq "mysql") {
		$sql = "alter table messages add xuidl text";
	} elsif ($prefs{'DatabaseDriver'} eq "Pg") {
		$sql = "alter table messages add column xuidl text";
	} elsif ($prefs{'DatabaseDriver'} eq "CSV") {
		&add_csv_column("$prefs{'MailDir'}/messages","xuidl");
		return 1;
	}	
	$query = $conn->prepare($sql);
	$query->execute();
}

sub fix_boxlist
{
	my($parent) = @_;
	my($sql,$query,$query2,@row,$lastid);
	if(defined $parent) {
		$sql = "select id,sibling,parent from boxlist where parent=? order by id desc";
		$query=$conn->prepare($sql);
		$query->execute($parent);
	}
	else {
		$sql = "select id,sibling,parent from boxlist where (parent=-1 or parent is NULL) order by id desc";
		$query=$conn->prepare($sql);
		$query->execute();
	}
	$lastid=65535;
	while(@row=$query->fetchrow_array()) {
		$sql = "update boxlist set sibling=? where id=?";
		$query2=$conn->prepare($sql);
		$query2->execute($lastid,$row[0]);
		$lastid=$row[0];
		&fix_boxlist($row[0]);
	}
}


sub add_csv_column
{
	my ($filename, $column) = @_;
	open (FILE, $filename);
	@file = <FILE>;
	close (FILE);
	$columnlist = $file[0];
	if ($columnlist =~ m/\b$column\b/) { print "has been added already\n"; return 1; }
	my @fields = split(/,/,$columnlist);
	my $last = scalar(@fields);
	$last--;
	#maybe some one upgraded from 1.0 to 1.2.x and now upping to 2.x
	if ($column eq "xuidl") {
		if ($fields[$last] =~ /ref/) {
			$columnlist =~ s/,ref/,ref,$column/;
		} else { #maybe not 
			$columnlist =~ s/,score/,score,$column/;
		}	
	}
	if ($column eq "serverstat") {
		$columnlist=~ s/,xuidl/,xuidl,$column/;
	}
	if ($column eq "groups") {
		$columnlist=~ s/,public_key/,public_key,groups/;
	}	
	$file[0] = $columnlist;
	copy($filename,$filename."_bak");
	open(FILE,">$filename");
	foreach(@file) { print FILE $_  }
	close(FILE);
}