#!/usr/bin/perl

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

use JSON::XS;
use FileHandle;

use lib "$ENV{'HOME'}/sandbox/orchard_db";
use Connect;
my $dbh = Connect->connect('docker');

my $ordpos  = shift @ARGV || 1;
my $sqlFile = 'runControllerCols.sql';

my $sql;
my $fh = FileHandle->new( $sqlFile, 'r' );
read $fh, $sql, -s $fh;
$fh->close;

eval {
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    if ( $sth->rows ) {
        while ( my $row = $sth->fetchrow_hashref() ) {
            next if $row->{'ordinal_position'} != $ordpos;
            my $stores    = stringify( [ split ',', $row->{'stores'} ] );
            my $countries = stringify( [ split ',', $row->{'countries'} ] );
            my $txntypes  = stringify( [ split ',', $row->{'txntypes'} ] );
            my $sql       = (
                qq{
SELECT 
	dsdn.* 
FROM 
	dig_sales_detail_denorm dsdn
WHERE 1  
    AND vendor_id=$row->{'key_id'}
    AND store_id IN ( $stores )
    AND country_id IN ( $countries )
    AND trans_type_id IN ( $txntypes )
    AND date >= '$row->{'contract_term_start'}'
    AND date <= '$row->{'contract_term_end'}'
        }
            );
            print "$sql\n";
        }
    }
};
if ($@) {
    print $@;
}

sub stringify {
    my $aref = shift;
    my $text;
    my $i = 1;
    foreach my $n ( sort { $a <=> $b } @{$aref} ) {
        $n = sprintf( "%4d", $n );
        if ( $i++ % 24 == 0 ) {
            $text .= "$n,\n";
            $i = 1;
        } else {
            $text .= "$n,";
        }
    }
    $text =~ s/,$//;
    "\n$text\n";
}