#!/usr/bin/perl  
# Copyright 2004 Jerzy Wachowiak

use strict;
use warnings;
use Text::CSV_XS;
use xdSRA;

my $filepath = shift;
my $database = shift;

defined( $filepath ) or usage();
defined( $database ) or $database = "xdash";

my $result = xdSRA::create_sra_from( $filepath );
my @sender = @{ $result->{sender} };
my @receiver = @{ $result->{receiver} };
my @archivist = @{ $result->{archivist} };

my $jclientpath 
    = "$archivist[0]{username}\@$archivist[0]{hostname}_$archivist[0]{resource}";

print "\n---Start creating SQL---\n"
."Creating $database.sql in the directory $jclientpath.\n";

xdSRA::create_directory( $jclientpath );
open( DBSQL, "> $jclientpath/$database.sql" )
 or die "Cannot create $database.sql. ($!). Bye, Bye...";

print DBSQL "drop table messages;
create table messages (
thread varchar( 250 ),
entrytime timestamp,
fromuser varchar( 257 ),
server varchar( 342 ),
resource varchar( 250 ),
type varchar( 20 ),
subject varchar( 500 ),
body text,
errorcode int,
errordescription varchar( 500 )
);
create index threads on messages ( thread );
\n
drop table senders;
create table senders (
JID varchar( 600 ) not null check ( JID <> '' ),
alias_in_statistics varchar( 20 ) check ( alias_in_statistics <> '' ),
update_call varchar( 100 ) check ( update_call <> '' ),
primary key ( JID )
);\n
drop table receivers;
create table receivers (
JID varchar( 600 ) not null check ( JID <> '' ),
alias_in_statistics char( 20) check ( alias_in_statistics <> '' ),
update_call varchar( 100 ) check ( update_call <> '' ),
primary key ( JID )
);\n\n";


for my $si (0..$#sender) {
        print DBSQL "insert into senders "
	."( JID, alias_in_statistics, update_call )\n"
	." values ( '"."$sender[$si]{username}\@$sender[$si]{hostname}"
	."', '".$sender[$si]{dbalias}
	."', 'add_to_stat_".$sender[$si]{dbalias}."_with' );\n\n";

	print DBSQL "create or replace function 
 add_to_stat_".$sender[$si]{dbalias}."_with( varchar ) returns integer as '
  ----
  declare 
    v_message_thread alias for ". '$1'.";
    v_query_result varchar;
  ----
  begin
    --
    -- Checking the contract with the table statistics...
    if char_length( v_message_thread ) > 250 then
	-- C error code covention 0 -> Ok, else -> something wrong.
	return 1;
    end if;
    --
    -- Checking, if this thread already exists and acting accordingly...
    select thread into v_query_result from statistics 
     where thread=v_message_thread;
    if not found then
	insert into statistics 
	 ( thread, lastupdate, starttime, ".$sender[$si]{dbalias}."_occurence ) 
	 values ( v_message_thread, now(), now(), 1 );
    else
	update statistics set 
         $sender[$si]{dbalias}_occurence=".$sender[$si]{dbalias}."_occurence+1,
	 lastupdate=now(), 
         deltatime=age( now(), starttime ) 
         where thread=v_message_thread;
    end if;
    return 0; 
  end;        
  ----
' language 'plpgsql';\n\n";

};

for my $ri (0..$#receiver) {
	print DBSQL "insert into receivers" 
	." ( JID, alias_in_statistics, update_call )\n values ( '"
	."$receiver[$ri]{username}\@$receiver[$ri]{hostname}"
	."', '".$receiver[$ri]{dbalias}
	."', 'add_to_stat_".$receiver[$ri]{dbalias}."_with' );\n\n";
	print DBSQL "create or replace function 
 add_to_stat_".$receiver[$ri]{dbalias}."_with( varchar, integer )"
 ." returns integer as '
  ----
  declare 
    v_message_thread alias for ".'$1'.";
    v_job_result alias for ".'$2'.";
    v_query_result varchar;
  ----
  begin
    --
    -- Checking the contract with the table statistics...
    if char_length( v_message_thread ) > 250 then
	-- C error code covention 0 -> Ok, else -> something wrong.
	return 1;
    end if;
    --
    -- Checking, if this thread already exists, and acting accordingly...
    select thread into v_query_result from statistics
     where thread=v_message_thread;
    if not found then
	if v_job_result = 0 then
	    insert into statistics 
	     ( thread, lastupdate, starttime, "
	     .$receiver[$ri]{dbalias}."_occurence ) 
	     values ( v_message_thread, now(), now(), 1 );
	else
	    insert into statistics 
	     ( thread, lastupdate, starttime, 
	     $receiver[$ri]{dbalias}_occurence, "
	     .$receiver[$ri]{dbalias}."_result, error_counter ) 
	     values ( v_message_thread, now(), now(), 1, v_job_result, 1);
	end if;	     
    else
	if v_job_result = 0 then
	    update statistics set
	     $receiver[$ri]{dbalias}_occurence="
	     .$receiver[$ri]{dbalias}."_occurence+1, 
	     lastupdate=now(), 
	     deltatime=age( now(), starttime ) 
	     where thread=v_message_thread;
        else
	    update statistics set 
    	     $receiver[$ri]{dbalias}_occurence="
	     .$receiver[$ri]{dbalias}."_occurence+1,
    	     $receiver[$ri]{dbalias}_result=v_job_result, 
    	     error_counter=error_counter+1,
    	     lastupdate=now(), 
    	     deltatime=age( now(), starttime ) 
    	     where thread=v_message_thread;	    
     	end if;	     
    end if;
    return 0; 
   end;        
   ----
' language 'plpgsql';\n\n";
};

print DBSQL 
"\ndrop table statistics;
create table statistics ( 
thread varchar( 250 ) not null,
lastupdate timestamp,
starttime timestamp,
deltatime interval default '0 second',\n";
for my $si (0..$#sender) {
        print DBSQL "$sender[$si]{dbalias}_occurence int not null default '0',\n";

};

for my $ri (0..$#receiver) {
	print DBSQL "$receiver[$ri]{dbalias}_occurence  int not null default '0',\n";
	print DBSQL "$receiver[$ri]{dbalias}_result int not null default '0',\n";
};
print DBSQL 
"error_counter int not null default '0',
primary key ( thread )
);";

close DBSQL;
print "Creating bash script $database in the directory $jclientpath.\n";
open( DBSHELL, "> $jclientpath/$database.psql" )
 or die "Cannot create bash script $database. ($!). Bye, Bye...";
my $q = '$?';
my $z = '$0';
print DBSHELL <<EOS;
#!/bin/bash
echo ---Start making the database---
createdb $database
if [ $q -ne 0 ]; 
    then
        echo "$database propably already exists !!! Bye, bye ..."
        echo
	exit 1
    fi 
createlang plpgsql $database   
psql -f ./$database.sql $database
echo ---End making the database---
EOS

print "---End creating SQL---\n";
exit;

sub usage {
    print <<EOU;

USAGE:
$0 filename [database_name]

DESCRIPTION:
xdpg creates sql for the initialization of the xDash database on the PostgreSQL
in the directory with the name of archivist JID. The mandatory input parameter
is a file and optional a name for the result sql file. The records in the input
file must have the format: description; role; hostname; port; username; 
password; resource; operating system; home path. The role can be only: sender, 
receiver or archivist. Comments have to start with #.

EOU
exit 1
}