#!/usr/bin/perl

# TODO : add a test for the filename

$filename=$ARGV[0];
$dir = $filename;
$dir =~ s#[^/]*$##;
$db="linuxman";

use Pg;

# Connect to the linuxman database

$conn = Pg::connectdb("dbname=$db");
die $conn->errorMessage unless PGRES_CONNECTION_OK eq $conn->status;
print "connected to $db\n";

open(FD, "<$filename") or die "can not open $filename";

$_=<FD>;
/(\d{4})(\d{2})(\d{2})-(\d+)/;
$year=$1;
$month=$2;
$day=$3;
$numero=$4;

$_=<FD>;
s/\s+$//g;
s/^\s+//g;
s/\s*,\s*/,/g;
s/'/\\'/g;
@keywords = split (',',$_);

$_=<FD>;
s/\s+$//g;
s/^\s+//g;
s/\s*,\s*/,/g;
s/'/\\'/g;
@attachments = split (',',$_);

$result = $conn->exec("SELECT num FROM articles WHERE year = '$year' AND month = '$month' AND day = '$day' GROUP BY num");
die $conn->errorMessage unless PGRES_TUPLES_OK eq $result->resultStatus;

$num = $result->ntuples+1;

if ($num!=$numero) {
	print "Warning : given num ($numero) and the calculated one ($num) are different, continue (y/n) ? ";
	if (lc(getc)=='n'){
		exit(0);
	}
}	

print "Creating $year$month$day-$num entry\n";

print "Keywords : @keywords\nAttachments : @attachments\n";

while (<FD>) {
	encode();
        $line="$line$_";
}

print TMP $line;

$result = $conn->exec("BEGIN");
die $conn->errorMessage unless PGRES_COMMAND_OK eq $result->resultStatus;

$oid = $conn->lo_creat(0644) or die $conn->errorMessage;

$ofd = $conn->lo_open($oid,0644);
die $conn->errorMessage if $ofd==-1; 

$len = length($line);
print "Creating article $oid, length $len\n";

die $conn->errorMessage unless $conn->lo_write($ofd,$line,$len) == $len;

$result = $conn->exec("END");
die $conn->errorMessage unless PGRES_COMMAND_OK eq $result->resultStatus;

for ($i = 0; $i<@keywords;$i++){
	$result = $conn->exec("INSERT INTO articles VALUES ('$year','$month','$day','$num','$keywords[$i]','$oid')");
	die $conn->errorMessage unless PGRES_COMMAND_OK eq $result->resultStatus;
}

# Creating an entry for attachments

for ($i=0;$i<@attachments;$i++){

	$result = $conn->exec("BEGIN");
	die $conn->errorMessage unless PGRES_COMMAND_OK eq $result->resultStatus;
	
	$oid = $conn->lo_import("$dir$attachments[$i]") or die $conn->errorMessage;

	$result = $conn->exec("END");
	die $conn->errorMessage unless PGRES_COMMAND_OK eq $result->resultStatus;
	print "Adding entry for attachments $attachments[$i]\n";
        $result = $conn->exec("INSERT INTO attachments VALUES ('$year','$month','$day','$num','$attachments[$i]','$oid')");
        die $conn->errorMessage unless PGRES_COMMAND_OK eq $result->resultStatus;
}

exit(0);

sub encode {
	s/(\s|&nbsp;)\s/&nbsp;&nbsp;/g;
	s/é/&eacute;/g;	
	s/É/&Eacute;/g;	
	s/è/&egrave;/g;
	s/È/&Egrave;/g;
	s/ê/&ecirc;/g;
	s/Ê/&Ecirc;/g;
	s/ë/&euml;/g;
	s/Ë/&Euml;/g;
	s/à/&agrave;/g;
	s/À/&Agrave;/g;
	s/â/&acirc;/g;
	s/Â/&Acirc;/g;
	s/ä/&auml;/g;
	s/Ä/&Auml;/g;
	s/ù/&ugrave;/g;
	s/Ù/&Ugrave;/g;
	s/û/&ucirc;/g;
	s/Û/&Ucirc;/g;
	s/ü/&uuml;/g;
	s/Ü/&Uuml;/g;
	s/ô/&ocirc;/g;
	s/Ô/&Ocirc;/g;
	s/ö/&ouml;/g;
	s/Ö/&Ouml;/g;
	s/ï/&iuml;/g;
	s/Ï/&Iuml;/g;
	s/î/&icirc;/g;
	s/Î/&Icirc;/g;
	s/ç/&ccedil;/g;
	s/Ç/&Ccedil;/g;
	s/£/&pound;/g;
	return $_;
	
}