#!/usr/bin/perl

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

use JSON::XS;
use FileHandle;
use File::Find::Rule;
use Text::CSV::Easy(qw/csv_build csv_parse/);
use Getopt::Long::Descriptive(qw/describe_options/);

use lib '/var/app/orchard/collab/jgetter/perllib';
use Orchard::Session;
use Orchard::RunCommand;
use Orchard::DB::Connect;

my $dirs = [ File::Find::Rule->directory->maxdepth(1)->mindepth(1)->name(qr/\d\d\d/)->in(".") ];
my $sql  = "with filecount (sid,ttl) as (";
my $periods;
foreach my $period ( sort { $a <=> $b } @{$dirs} ) {
    push @{$periods}, "    select '${period}', count(*) from  \@ABACUS_TSV_FILE/temp/john/${period}.txt";
}
$sql .= "\n" . ( join " union\n", @{$periods} ) . "\n";
$sql .= <<"!";
)
select pub.period_id, fc.ttl-1 file_count, count(*) snowflake_count
from royalty_accounting.qa.fact_sale_publishing pub
join filecount fc on fc.sid=pub.period_id
group by 1,2 order by 1;
!

logMessage( 'info',  "Running Snowflake Query...\n$sql" );
logMessage( 'info1', "this should take around a minute or so..." );

my $sfdbh = Orchard::Session::getdbh('snowflake');

my $warehouse = "qa_abacus_wh";
my $role      = "dev_engineering";
my $database  = "royalty_accounting";
my $schema    = "qa";

$sfdbh->do("use warehouse $warehouse") or die "Error: $DBI::errstr\n\n";
$sfdbh->do("use role $role")           or die "Error: $DBI::errstr\n\n";
$sfdbh->do("use $database")            or die "Error: $DBI::errstr\n\n";
$sfdbh->do("use schema $schema")       or die "Error: $DBI::errstr\n\n";

my $sfsth = $sfdbh->prepare($sql);
$sfsth->execute();
if ( $sfsth->rows() ) {
    while ( my $r = $sfsth->fetchrow_arrayref() ) {
        my ( $per, $fcnt, $sfcnt ) = @{$r};
        logMessage( 'info', "period: $per file_count: $fcnt snowflake_count: $sfcnt" );
    }
}