#!/usr/bin/perl

use strict;
use warnings;
use DBI;                                                                                                                                                                         
                                                                                                                                     
my $host = '192.168.8.10';                                                                                                                                                       
my $db='CAMDPAMS';                                                                                                                                                               
my $usr = 'cert_botan';                                                                                                                                                          
my $pass = 'BSiyA1ov';                                                                                                                                                           
my $dbh = DBI ->connect("dbi:Pg:dbname=$db;host=$host","$usr","$pass",{AutoCommit=>1,RaiseError=>1,ChopBlanks=>1});
my $sth;
my @trust  =();
my @unknown = ();
my %uni;
my %trust;
my $e;
my $inp = shift;
my $out = shift;
my $out_ex = shift;
$dbh->do("create TEMPORARY table \"cert_temp\" as (select cert_subjects.\"Name\",samples_authcodes.\"SampleId\",                                                                 
	    \"cert_subjects\".\"VerdictId\",\"cert_subjects\".\"IsReviewed\"                                                                                                                                  
	    FROM certs join cert_subjects using(\"SubjectId\") join samples_authcodes using(\"CertId\"));");
my $sql = "select distinct \"Name\" from \"cert_temp\" where \"VerdictId\"=? and \"IsReviewed\"::integer=?;";
$sth=$dbh->prepare($sql);
$sth->execute(0,0);
while(my @row=$sth->fetchrow_array){$uni{"$row[0]"}=1;}
$sth->execute(2,1);
while(my @row=$sth->fetchrow_array){$trust{"$row[0]"}=1;}
$sth->finish;
$dbh->do("drop table if exists \"cert_temp\";"); 
open(IF,"<","$inp");
open (OF,">",$out);
open (OF_EX,">",$out_ex);
#open (OF1,">","/home/camdpams_www/signer/include/uploads/review.txt");
while(<IF>){
    s/\s+$//;
    s/^\s+//;
    s/[^[:ascii:]]+//g;
    if(not defined $uni{"$_"}){
	if(not defined $trust{"$_"}){print OF "$_\n";}
	else{print OF_EX "$_\n";}
    }
    else{
    s/\'/''/;
    $dbh->do("select submit_cert_subject_info(\'$_\', \'$usr\', 2, \'botan changed\', true, false, false);");
#    print OF1 "select submit_cert_subject_info(\'$_\', \'$usr\', 2, \'botan changed\', true, false, false);\n";
    }
}

#$dbh->commit;
$dbh->disconnect;
#close(OF1);
close(OF_EX);
close(OF);
close(IF);
print "1\n";