#!/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 $e;
my $inp = "/home/camdpams_www/signer/include/data/trusted.txt";
my $out = "/home/camdpams_www/signer/include/data/NotFoundInCAMDPAMS.txt";
$dbh->do("create TEMPORARY table \"cert_temp\" as (select cert_subjects.\"Name\",samples_authcodes.\"SampleId\",                                                                 
	    \"cert_subjects\".\"VerdictId\"                                                                                                                                  
	    FROM certs join cert_subjects using(\"SubjectId\") join samples_authcodes using(\"CertId\"));");
my $sql = "select \"Name\" from \"cert_temp\" where \"VerdictId\"=0 group by \"Name\";";
$sth=$dbh->prepare($sql);
$sth->execute;
while(my @row=$sth->fetchrow_array){$uni{"$row[0]"}=1;}
$dbh->do("drop table if exists \"cert_temp\";"); 
open(IF,"<","$inp");
open (OF,">",$out);
#open (OF1,">","insert");
while(<IF>){
    s/\s+$//;
    s/^\s+//;
    if(not defined $uni{"$_"}){print OF "$_\n";}
    else{
    s/\'/''/;
    $dbh->do("select submit_cert_subject_info(\'$_\', \'$usr\', 2, \'botan changed\', false, false, false);");
#    print OF1 "select submit_cert_subject_info(\'$_\', \'$usr\', 2, \'botan changed\', false, false, false);\n";
    }
}
$sth->finish;
#$dbh->commit;
$dbh->disconnect;
#close(OF1);
close(OF);
close(IF);
print "1\n";