#!/usr/bin/perl -w 

use strict;
use DBI;


# Create a blank mysql database.

# Connect to postgres database

my $old_handle = DBI->connect("dbi:Pg:dbname=rt3",'pguser','password') or die "Can't connect to mSQL database: $DBI::errstr\n";




# Connect to mysql database
my $new_handle = DBI->connect("dbi:mysql:dbname=rt3;hostname=mysqlhost",'mysqluser','password') or die "Can't connect to mSQL database: $DBI::errstr\n";





# For each table in our list of tables
my @tables = qw/ Users ACL Attachments Attributes CachedGroupMembers CustomFieldValues CustomFields GroupMembers Groups Links Principals Queues ScripActions ScripConditions Scrips Templates Tickets Transactions /; 

foreach my $table (@tables) {
	print "Migrating $table...\n";
	# Find all the rows in the table
	my $sth = $old_handle->prepare("SELECT * FROM $table") || die $@;
	$sth->execute || die $@;


	# Copy all rows from postgres to mysql
	copy_table_rows($table, $sth);
}







sub copy_table_rows {
	my $table = shift;
	my $sth = shift;

	while (my $row = $sth->fetchrow_hashref) {
		my @keys;
		my @values;
		my @placeholders;
		foreach my $key (keys %$row) {
		if ($table eq 'Users' && $key =~ /^Name$/i) { 
			$row->{$key} = check_for_duplicate_name($row->{$key});
		}
		if ($table eq 'Users' && $key =~ /EmailAddress/i) { 
			$row->{$key} = check_for_duplicate_email($row->{$key});
		}

			push @keys, $key;
			push @values, $row->{$key};
			push @placeholders, '?';
			

		}

		my $insert = "INSERT into $table (".join(',',@keys).") VALUES (".join(',',@placeholders).")\n";
		my $sth= $new_handle->prepare($insert) || die  $@;
		$sth->execute(@values) || die $@	. "\n ".join(",",@values);
	}


}


sub check_for_duplicate_name {
	my $address = shift;


	my $lookup = $new_handle->quote($address);
	
	my $result = $new_handle->selectrow_arrayref("SELECT id FROM Users WHERE  Name = $lookup");
	if ($result->[0]) {
		my $new_addr = "$address (Duplicate ".time().")";
	
	print "Found duplicate name '$address' :". $result->[0].". replacing with alternate address - ".$new_addr."\n";;
		check_for_duplicate_email($new_addr);
	}
	else {
		return($address);
	}
}

sub check_for_duplicate_email {
	my $address = shift;


	my $lookup = $new_handle->quote($address);
	
	my $result = $new_handle->selectrow_arrayref("SELECT id FROM Users WHERE  EmailAddress = $lookup");
	if ($result->[0]) {
		my $new_addr = "$address, nobody+".time();
	
	print "Found duplicate address '$address':". $result->[0].". replacing with alternate address - ".$new_addr."\n";;
		check_for_duplicate_email($new_addr);
	}
	else {
		return($address);
	}
}




$old_handle->disconnect();
$new_handle->disconnect();