#!/usr/bin/perl -w

use strict;
use Data::Dumper;
use Getopt::Long;
use DBI;

my $usage = <<EOS;
Usage:
	$0 [options] 

options:	
	debug db=s table=s user=s password=s 
EOS

my %opt = (
	'debug'    => undef,
	'driver'   => 'mysql',
	'db'       => 'smc',
	'user'     => 'samba',
	'table'    => undef,
	'password' => 'mikiraja',
	'truncate' => undef,
);

GetOptions(
	'debug'      => \$opt{'debug'},
	'db=s'       => \$opt{'db'},
	'user=s'     => \$opt{'user'},
	'password=s' => \$opt{'password'},
	'table=s'    => \$opt{'table'},
	'truncate'   => \$opt{'truncate'},
);

my $dbhi = DBI->connect( "DBI:$opt{'driver'}:database=$opt{'db'}",
	$opt{'user'}, $opt{'password'}, 
	{ AutoCommit => 0, RaiseError => 1 } )
  or die "Connect to Mysql db $opt{'db'} failed";

my $dbho =
  DBI->connect( "DBI:PgPP:dbname=$opt{'db'};host=localhost", $opt{'user'}, $opt{'password'},
	{ AutoCommit => 0, RaiseError => 1 } )
  or die "Connect to Pg db $opt{'db'} failed";

copyData( $opt{db}, $opt{table} );

sub copyData {
	my ( $db, $tableName ) = @_;

	return 0 unless defined $tableName;
	return 0 if $tableName =~ /^#/;

	my $sthx =
	  $dbhi->prepare(
"SELECT a.*  FROM INFORMATION_SCHEMA.COLUMNS a WHERE LOWER(a.TABLE_NAME) = ? AND LOWER(a.TABLE_SCHEMA) = ?"
	  );
	$sthx->execute( ( lc $tableName, lc $db ) );
	my @icolumns = ();
	my %columnmap = ();
	my %icoldef  = ();
	while ( my $aref = $sthx->fetchrow_hashref ) {
		push @icolumns, $aref->{COLUMN_NAME};
		$icoldef{ $aref->{COLUMN_NAME} } = $aref;
		$columnmap{lc  $aref->{COLUMN_NAME}} =  $aref->{COLUMN_NAME};
	}
	
	$sthx =
	  $dbho->prepare(
"SELECT a.*  FROM INFORMATION_SCHEMA.COLUMNS a WHERE LOWER(a.TABLE_NAME) = ? AND LOWER(a.TABLE_SCHEMA) = ?"
	  );
	$sthx->execute( ( lc $tableName, "public" ) );
	my @ocolumns = ();
	my %ocoldef  = ();
	while ( my $aref = $sthx->fetchrow_hashref ) {
		push @ocolumns, $aref->{column_name};
		$ocoldef{ $aref->{column_name} } = $aref;
	}

	print "Reading data from $tableName\n";
	my $sthi = $dbhi->prepare( sprintf( "select * from %s", $tableName ) );
	$sthi->execute();

	my $stto = $dbho->do( sprintf( "TRUNCATE TABLE %s CASCADE", $tableName ) )
		 if $opt{truncate};
	my $q = sprintf(
		"INSERT INTO %s (%s) VALUES (%s)",
		$tableName,
		join( ',', @ocolumns ),
		join( ',', map( '?', @ocolumns ) )
	);
	my $stho = $dbho->prepare($q);

	print "Insert data to $tableName\n";
	my $i = 1;
	while ( my $aref = $sthi->fetchrow_hashref ) {
		# print Data::Dumper->Dump([$aref], [qw(aref)]);

		my @mapcols = ();
		# in postgresql column_name is lowercase, while in mysql camelcase
		foreach my $colName (@ocolumns) {
			my $map = exists $columnmap{$colName} ?  $columnmap{$colName} : $colName;
			my $value = defined $aref->{$map} ? $aref->{$map} : undef;
			if ( $ocoldef{$colName}->{data_type} =~ /^(boolean)$/i ) {
				$value = defined $value ? "TRUE" : "FALSE";

				# print "change $colName $value\n";
			}
			push @mapcols, $value;
		}

		# print Data::Dumper->Dump([\@mapcols], [qw(mapcols)]);
		$stho->execute(@mapcols);
		print "\r", $i;
		$i++;
	}
	print "\n";
	$dbho->commit;
	print "$i record(s) has been inserted into $tableName\n";
}