#!/usr/bin/perl


# Upload a super-module layer test to gateDB.
#
# The script takes a super-module number, a layer, a report file and a
# file with pictures as arguments. The files are uploaded to gateDB
# through a direct PostgreSQL database connection (not over the
# web). Then a comment is added to the super-module linking to the
# files.
#
# (c) Tom Dietel, 2011
#

#use strict;
use POSIX;
use DBI;
use Term::ReadKey;

sub upload_file($$$);
sub roman($);

# -----------------------------------------------------------------------------
# parse command line
# -----------------------------------------------------------------------------

$usage = "\n".
    "usage:   upload-report.pl <SM> <layer> <report.pdf> <pics.pdf>\n".
    "\n";

($sm     = shift) ne '' or die $usage;
($layer  = shift) ne '' or die $usage;
($report = shift) ne '' or die $usage;
($pics   = shift) ne '' or die $usage;

$smname = "SM-".roman($sm);

if ( $layer ne '0' and $layer ne '1' and $layer ne '2' and 
     $layer ne '3' and $layer ne '4' and $layer ne '5' and 
     $layer ne 'final') {

    die $usage;
}

if ($layer eq 'final') {
    $lyname = 'Final';
} else {
    $lyname = "L$layer";
}


# -----------------------------------------------------------------------------
# connect to gateDB and start transaction
# -----------------------------------------------------------------------------

my $dbh = DBI->connect("DBI:Pg:dbname=gateDB;host=alice.physi.uni-heidelberg.de",
		       "alice", 'alice!@#',
		       {'RaiseError' => 1});
$dbh->begin_work;
 


# -----------------------------------------------------------------------------
# check if the report or parts exist already
# -----------------------------------------------------------------------------


$sth = $dbh->prepare("SELECT file_id,name,mime_type FROM file WHERE name=?;");

$sth->execute("report-$smname-$lyname.pdf");
if ( $sth->fetchrow() ) {
    die "report file \"report-$smname-$lyname.pdf\" exists in gateDB";
}

$sth->execute("pics-$smname-$lyname.pdf");
if ( $sth->fetchrow() ) {
    die "pics file \"pics-$smname-$lyname.pdf\" exists in gateDB";
}

# TODO: check if comment exists

# -----------------------------------------------------------------------------
# check the password
# -----------------------------------------------------------------------------

print "Enter your gateDB username: ";
ReadMode 'normal';
$username = ReadLine 0;
chomp $username;

print "Enter your password: ";
ReadMode 'noecho';
$password = ReadLine 0;
chomp $password;
ReadMode 'normal';

print "\n";

$sth = $dbh->prepare("SELECT username,initials,passwd FROM users WHERE username=?");
$sth->execute(uc($username));

while ( ($user, $initials, $pwhash ) = $sth->fetchrow()){

    # verify UPPERCASED password 
    # I have no idea why Paul stores uppercased PWs?!?

    if (crypt(uc($password), $pwhash) ne $pwhash) {
	die "You are not $username\n";
    } else {
	$person = $initials;
    }
}


# -----------------------------------------------------------------------------
# upload report and pics files
# -----------------------------------------------------------------------------



$rep_fid = upload_file($report, "report-$smname-$lyname.pdf", "application/pdf");
$pic_fid = upload_file($pics,   "pics-$smname-$lyname.pdf",   "application/pdf");


# -----------------------------------------------------------------------------
# insert comment
# -----------------------------------------------------------------------------


$sth = $dbh->prepare("INSERT INTO comment(title,body,person,attachment0,attachment1)".
		     " VALUES (?,?,?,?,?);");

$sth->execute("Report $smname $lyname", "", $person, $rep_fid, $pic_fid);

$sth = $dbh->prepare("INSERT INTO sm_cmnt_link(sm_id,cmnt_id) VALUES (?,lastval());");
$sth->execute($sm);



# -----------------------------------------------------------------------------
# commit transaction and terminate DB connection
# -----------------------------------------------------------------------------
#$dbh->rollback;

#exit;

$dbh->commit;
$dbh->disconnect;



exit;




$sth = $dbh->prepare("INSERT INTO wafer_test(wafer,trap_id,laser_id,result,timestamp,".
		     "                       tester_ver,tester_board_id,logfile) ".
		     "VALUES (?,?,?,?,now(),?,?,(SELECT file_id FROM file WHERE loid=?))");

$sth->execute($wafer,$trapid,$laserid,$result,$prog_version,$board_id,$lobjId);





sub upload_file($$$)
{
    my $file     = shift(@_);
    my $filename = shift(@_);
    my $filetype = shift(@_);

    my $maxsize  = (-s $file) + 1; 

    $| = 1; # hot file handle -> immediate flushing
    print "Uploading $file...";
    
    open IN, "<$file";
    binmode IN;

    $len = read IN,$buf,$maxsize;
    
    $lobjId = $dbh->func($dbh->{pg_INV_WRITE}, 'lo_creat');
    
    $lobj_fd = $dbh->func($lobjId, $dbh->{pg_INV_WRITE} , 'lo_open');
    $nbytes = $dbh->func($lobj_fd, $buf, $len, 'lo_write');

    print "  ".($nbytes+1)." bytes written.\n";
    
    
    $sth = $dbh->prepare("INSERT INTO file(name,mime_type,loid) VALUES (?,?,?);");
    $sth->execute($filename, $filetype, $lobjId );

    $sth = $dbh->prepare("SELECT lastval();");
    $sth->execute;

    $| = 0; # STDIO buffering


    if ( ($fileid) =  $sth->fetchrow()){
	return $fileid;
    } else {
	die "error inserting file into gateDB";
    }
    
};


sub roman($)
{
    my $nr = shift(@_);

    if    ($nr ==  1) { return "I"; }
    elsif ($nr ==  2) { return "II"; }
    elsif ($nr ==  3) { return "III"; }
    elsif ($nr ==  4) { return "IV"; }
    elsif ($nr ==  5) { return "V"; }
    elsif ($nr ==  6) { return "VI"; }
    elsif ($nr ==  7) { return "VII"; }
    elsif ($nr ==  8) { return "VIII"; }
    elsif ($nr ==  9) { return "IX"; }
    elsif ($nr == 10) { return "X"; }
    elsif ($nr == 11) { return "XI"; }
    elsif ($nr == 12) { return "XII"; }
    elsif ($nr == 13) { return "XIII"; }
    elsif ($nr == 14) { return "XIV"; }
    elsif ($nr == 15) { return "XV"; }
    elsif ($nr == 16) { return "XVI"; }
    elsif ($nr == 17) { return "XVII"; }
    elsif ($nr == 18) { return "XVIII"; }
    else  { die "unknown super-module";}

};
