#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

use JSON::XS;
use FileHandle;
use Array::Utils qw/:all/;
use Text::CSV::Easy(qw/csv_build csv_parse/);

use lib '/var/app/orchard/collab/jgetter/perllib';
use Orchard::Session;
use Orchard::DB::Connect;
use Orchard::ETL::Extract::CSV;
use Orchard::ETL::Extract::SpreadSheet;

my $file = shift @ARGV;
my $db   = 'abacusqa';
#my $db   = 'archivedb';

logMessage( 'info', "Connecting to $db" );
my $dsn = Orchard::DB::Connect->dsn($db);
my $dbh;
if ( defined $dsn->{'ssh'} ) {
    my $ssh = $dsn->{'ssh'};
    $ssh->{'net'}->spawn( $ssh->{'tunnel'} );
    sleep 1;
}
$dbh = DBI->connect( @{ $dsn->{'conn'} } ) || die "Conn: $DBI::errstr";

my $sth = $dbh->prepare(
    qq{
select
	contract_term_id,
	contract_term_name,
	attachments
from contract_term ct
join account_contract ac on ac.contract_id=ct.contract_id
where created_by='knr_sax_ingestion'
-- and (ac.account_id=67154 or ac.account_id=68695)
}
);
$sth->execute;
my $dbrows;
while ( my $row = $sth->fetchrow_arrayref() ) {
    my ( $id, $name, $attachments ) = @{$row};
    my $attachment_array = JSON::XS::decode_json($attachments);
    $dbrows->{$name}->{'attachments'} = $attachment_array;
    $dbrows->{$name}->{'id'}          = $id;
}

my $text;
my $csv = Orchard::ETL::Extract::CSV->new($file);
while ( my $line = $csv->getrow() ) {
    my $name = $line->{'SCHEDULE'};
    my $cons = JSON::XS::decode_json( $line->{'ATTACHMENTS'} );
    push @{ $text->{$name} }, @{$cons};
}

foreach my $name ( keys %{$text} ) {
    logMessage( 'info', "Checking $name" );
    my $diff = [ array_diff( @{ $dbrows->{$name}->{'attachments'} }, @{ $text->{$name} } ) ];
    if ( scalar @{$diff} ) {
        my $cnt = scalar @{$diff};
		my $id = defined $dbrows->{$name}->{'id'}  ? $dbrows->{$name}->{'id'} : 'XX';
        logMessage( 'error', "ctid: $id $name missing $cnt rows WTF?!!" );
    }
}