#!/usr/bin/perl -w 

use strict;
use DBI;
use MIME::Base64 qw(encode_base64);
use Encode;

# Connect to mysql database

my $old_handle = DBI->connect("dbi:mysql:dbname=rt4",'rt_user','cdia3712');

# Connect to postgres database (on remote host)
my $new_handle = DBI->connect("dbi:Pg:dbname=rt4",'postgres','aidc2173');

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

#
# Updated list for "plain" RT 4.0.1 as of 28 June 2011
# Dario Landazuri, dario@astro.as.utexas.edu
#
my @tables = qw/ ACL Articles Attachments Attributes CachedGroupMembers Classes CustomFieldValues CustomFields GroupMembers Groups Links ObjectClasses ObjectCustomFieldValues ObjectCustomFields ObjectTopics Principals Queues ScripActions ScripConditions Scrips Templates Tickets Topics Transactions Users /; 


foreach my $table (@tables) {
	print "Migrating $table...\n";
	# Find all the rows in the table
	my $mymaxidq = $old_handle->prepare("select max(id) as id from $table") || die $@;
	$mymaxidq->execute || die $@;
	my $mymaxidr = $mymaxidq->fetchrow_hashref;
       my $mymaxid = $mymaxidr->{"id"};
	print "maxid=$mymaxid\n";

       my $reached_end = 0;	
	my $table_step = 2000;
	my $current = 0;

	while ($reached_end == 0)
	{

	    my $sth = $old_handle->prepare("SELECT * FROM $table where id>$current order by id limit $table_step") || die $@;
	    $sth->execute || die $@;

	    print "Asked $table for $table_step starting from $current, got ".$sth->rows."\n";

	    if ($sth->rows == 0 && $current >= $mymaxid)
	    {
		print "Exiting $table at $current, table size was $mymaxid\n";
		my $seq_name;
		if (($table eq "ObjectCustomFields") or ($table eq "ObjectCustomFieldValues"))
		{
		     $seq_name = lc($table) . "_id_s";
		}
		else
		{
		     $seq_name = lc($table) . "_id_seq";
		}
		my $new_seq_start = $mymaxid + 1;
		my $sth = $new_handle->prepare("select setval('$seq_name', $new_seq_start)");
		$sth->execute || die $@;
		$reached_end = 1;
	    } else {
		# Copy all rows from postgres to mysql
		$current = &copy_table_rows($table, $sth);
	    }
	}
 }

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

	while (my $row = $sth->fetchrow_hashref) {
		my @keys;
		my @values;
		my @placeholders;

		$lastrow = $row->{"id"};
		# print "Row $lastrow\n";

		if ($table eq "Attachments" and (defined($row->{"ContentType"}) and $row->{"ContentType"} =~ /application|image/ )) {
			$row->{"ContentEncoding"} = "base64";
			$row->{"Content"} = encode_base64($row->{"Content"});
		} 

		if ($table eq "ObjectCustomFieldValues") 
		{
		    if (defined($row->{"Content"}) and length $row->{"Content"} >= 255) {
			# Copy the contents to LargeContent and then truncate Content
			$row->{"LargeContent"} = $row->{"Content"};
			$row->{"Content"} = substr $row->{"Content"}, 0, 255;
		    }

		    if (!$row->{"SortOrder"}) {
			$row->{"SortOrder"} = 0;
		    }
		    if (!$row->{"Creator"}) {
			$row->{"Creator"} = 0;
		    }
		    if (!$row->{"LastUpdatedBy"}) {
			$row->{"LastUpdatedBy"} = 0;
		    }
		    if (!$row->{"Disabled"}) {
			$row->{"Disabled"} = 0;
		    }
		}

		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;
			# Line below edited per advice from Frank Wiles 
			# (fwiles@revsys.com) to cope with UTF-8 encoding
			# issues - see http://www.perlmonks.org/?node_id=591180
			push @values, encode("UTF-8", $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);
	}
	$lastrow;
 }


 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();
