#!/usr/bin/perl -w -s

############################# migrate.pl ###############################
#								       #
#	This perl script creates the new 2011 db and migrates          #
#       from the old db by...                                          #
#	1. creates the new database using create.sql                   #
#	2. maps the data from the old db to the new db                 #
#	3. drops the excess data and schema                            #
#								       #
#       Usage: migrate.pl [options]                                    #
#       options:                                                       #
#         -olddb='<DATABASENAME>'    Name of the old STRI Database     #
#         -newdb='<DATABASENAME>'    Name of the new STRI Database     #
#         -user='<USERNAME>'         MySQL username                    #
#         -passwd='<PASSWORD>'       Password of the MySQL user        #
#         -cfile='<FILEPATH>'        Absolute path to create.sql       #
#         -dfile='<FILEPATH>'        Absolute path to drop.sql         #
#         -mysql='<FILEPATH>'        Absolute path to mysql            #
#         -mysqldump='<FILEPATH>'    Absolute path to mysqldump        #
#         -quiet                     Ignores errors                    #
#                                                                      #
########################################################################
 
############################## Developers ##############################
#								       #
#		Bradley University, Computer Science Dept.	       #
#		Smithsonian Tropical Research Institute		       #
#								       #
#			2011 Capstone Project			       #
#		Mario Campos, Michael Koeber, Zach Caschetta 	       #
#								       #
########################################################################

use strict;
use DBI;
use Fcntl;

our($olddb,$newdb,$user,$passwd,$cfile,$dfile,$mysql,$mysqldump,$quiet);
BEGIN {
    $olddb or print 'Enter the old database name:' and chomp($olddb=<>);
    $newdb or print 'Enter the new database name:' and chomp($newdb=<>);
    $user or print 'Enter the username:' and chomp($user=<>);
    $passwd or print 'Enter the password:' and chomp($passwd=<>);
    $cfile or print 'Enter the path of the sql create file:' and chomp($cfile=<>);
    $dfile or print 'Enter the path of the sql drop file:' and chomp($dfile=<>);
    $mysql or $mysql='mysql';
    $mysqldump or $mysqldump='mysqldump';
    sysopen FILE,'migrate.log',O_CREAT|O_EXCL|O_WRONLY,0755 or die $!;
}

# create new db
print "Creating `$newdb`...";
system "$mysql -u $user -p$passwd -e 'create database $newdb;'";
print "done!\n";

# dump old db
print "Dumping `$olddb`...";
system "$mysqldump --opt -u $user -p$passwd $olddb > $olddb";
print "done!\n";

# inserting data into new db
print "Undumping into `$newdb`...";
system "$mysql -u $user -p$passwd $newdb < $olddb";
print "done!\n";

# running create.sql
system "$mysql -v -u $user -p$passwd $newdb < $cfile";


# connect to MySQL
my $dbh = DBI->connect("DBI:mysql:database=$newdb;host=localhost",$user,$passwd);

#   create coordinates of plot points
my $sql = 'SELECT PlotID, Latitude, Longitude, Elevation FROM Site';
my $sth = $dbh->prepare("$sql");
$sth->execute;
my $tbl_ref = $sth->fetchall_hashref('PlotID');
foreach my $key (keys %$tbl_ref) {
    $sql = 'INSERT INTO Coordinates (PlotID,GX,GY';
    defined $tbl_ref->{$key}->{'Elevation'} and $sql.=',GZ';
    $sql.=") VALUES ($key,$tbl_ref->{$key}->{Longitude},$tbl_ref->{$key}->{Latitude}";
    defined $tbl_ref->{$key}->{'Elevation'} and $sql.=",$tbl_ref->{$key}->{'Elevation'}";
    $sql.=')';
    if($dbh->do($sql)) {
	print "$sql\n";
    } else {
	die "Error executing: $sql\n" unless defined $quiet;
	print FILE "Error executing: $sql\n";
    }
}

#   create coordinates of quadrat points
$sql = 'SELECT QuadratID, PlotID, StartX, StartY FROM Quadrat';
$sth = $dbh->prepare("$sql");
$sth->execute;
$tbl_ref = $sth->fetchall_hashref('QuadratID');
foreach my $key (keys %$tbl_ref) {
    $sql = "INSERT INTO Coordinates (QuadratID,PlotID,PX,PY) VALUES ($key,$tbl_ref->{$key}->{PlotID},$tbl_ref->{$key}->{StartX},$tbl_ref->{$key}->{StartY})";
    if($dbh->do($sql)) {
        print "$sql\n";
    } else {
        die "Error executing: $sql\n" unless defined $quiet;
        print FILE "Error executing: $sql\n";
    }
}

$sql = 'SELECT DISTINCT DimX, DimY, PlotID FROM Quadrat';
$sth = $dbh->prepare("$sql");
$sth->execute;
$tbl_ref = $sth->fetchall_hashref('PlotID');
foreach my $key (keys %$tbl_ref) {
    if(defined $tbl_ref->{$key}->{'DimX'} && defined $tbl_ref->{$key}->{'DimY'}) {
	$sql = "UPDATE Site SET QDimX=$tbl_ref->{$key}->{DimX}, QDimY=$tbl_ref->{$key}->{DimY} WHERE PlotID=$key";
	if($dbh->do($sql)) {
	    print "$sql\n";
	} else {
	    die "Error executing: $sql\n" unless defined $quiet;
	    print FILE "Error executing: $sql\n";
	}
    }
}

# LogTreeHistory
$sql = 'SELECT TreeID,ChangeDate,ChangeDescription,ChangeCodeID,QuadratID,PlotID,Tag,X,Y,SpeciesID,SubSpeciesID FROM LogTreeHistory ORDER BY ChangeDate';
$sth = $dbh->prepare("$sql");
$sth->execute;
my @row = ();
while( @row = $sth->fetchrow_array ) {
    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";
    defined $row[1] and $sql .= 'ChangeDate,';
    defined $row[2] and $sql .= 'Description,';
    $sql .= "New) VALUES ('Tree',$row[0],";
    defined $row[1] and $sql .= "'$row[1]',";
    defined $row[2] and $sql .= "'$row[2]',";
    $sql .= "'ChangeCodeID=$row[3],QuadratID=$row[4],PlotID=$row[5],Tag=$row[6],X=$row[7],Y=$row[8],SpeciesID=$row[9],SubspeciesID=$row[10]')";
    if($dbh->do($sql)) {
	print "$sql\n";
    } else {
	die "Error executing: $sql\n" unless defined $quiet;
	print FILE "Error executing: $sql\n";
    }
}

# LogMeasurementHistory
$sql = 'SELECT MeasureID,CensusID,DateOfChange,DescriptionOfChange,StemID,TreeID,DBH,HOM,ExactDate FROM LogMeasurementHistory ORDER BY DateOfChange';
$sth = $dbh->prepare("$sql");
$sth->execute;
while( @row = $sth->fetchrow_array ) {
    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";
    defined $row[2] and $sql .= 'ChangeDate,';
    defined $row[3] and $sql .= 'Description,';
    $sql .= "New) VALUES ('Measurement','MeasureID=$row[0],CensusID=$row[1]',";
    defined $row[2] and $sql .= "'$row[2]',";
    defined $row[3] and $sql .= "'$row[3]',";
    $sql .= "'StemID=$row[4],TreeID=$row[5],DBH=$row[6],HOM=$row[7],ExactDate=$row[8]')";

    if($dbh->do($sql)) {
	print "$sql\n";
    } else {
        die "Error executing: $sql\n" unless defined $quiet;
        print FILE "Error executing: $sql\n";
    }
}

# LogMAttrHistory
$sql = 'SELECT MeasureID,CensusID,TSMID,DateOfChange,DescriptionOfChange FROM LogMAttrHistory ORDER BY DateOfChange';
$sth = $dbh->prepare($sql);
$sth->execute;
while( @row = $sth->fetchrow_array ) {
    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";
    defined $row[3] and $sql .= 'ChangeDate,';
    defined $row[4] and $sql .= 'Description,';
    $sql .= "New) VALUES ('MeasurementAttributes','MeasureID=$row[0],CensusID=$row[1],TSMID=$row[2]',";
    defined $row[3] and $sql .= "'$row[3]',";
    defined $row[4] and $sql .= "'$row[4]',";
    $sql .= "'MeasureID=$row[0],CensusID=$row[1],TSMID=$row[2]')";

    if($dbh->do($sql)) {
        print "$sql\n";
    } else {
        die "Error executing: $sql\n" unless defined $quiet;
        print FILE "Error executing: $sql\n";
    }
}

# LogTreeAttrHistory
$sql = 'SELECT CensusID,TreeID,TSMID,DateOfChange,DescriptionOfChange FROM LogTreeAttrHistory ORDER BY DateOfChange';
$sth = $dbh->prepare("$sql");
$sth->execute;
while( @row = $sth->fetchrow_array ) {
    $sql = "INSERT INTO Log (ChangedTable,PrimaryKey,";
    defined $row[3] and $sql .= 'ChangeDate,';
    defined $row[4] and $sql .= 'Description,';
    $sql .= "New) VALUES ('TreeAttributes','CensusID=$row[0],TreeID=$row[1],TSMID=$row[2]',";
    defined $row[3] and $sql .= "'$row[3]',";
    defined $row[4] and $sql .= "'$row[4]',";
    $sql .= "'CensusID=$row[0],TreeID=$row[1],TSMID=$row[2]')";

    if($dbh->do($sql)) {
        print "$sql\n";
    } else {
        die "Error executing: $sql\n" unless defined $quiet;
        print FILE "Error executing: $sql\n";
    }
}

# execute drop.sql
system "$mysql -v -u $user -p$passwd $newdb < $dfile" or die $!;

close FILE or die $!;
