#!/usr/bin/perl

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

use JSON::XS;
use FileHandle;
#use Spreadsheet::Write;
use Getopt::Long::Descriptive(qw/describe_options/);

use lib '/Users/denk002/projects/collab/jgetter/perllib';
use Orchard::Period;
use Orchard::Session;
use Orchard::ETL::Extract::CSV;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'period|p=i' => 'Period ID for the statements i.e. 267 - March 2021', { 'required' => 1 } ],
    [ 'outdir|o=s' => 'Output directory default == "."',                    { 'default'  => "." } ],
    [ 'vendor|v=i' => 'Produce statement for this vendor' ],
    [ 'help|?|h'   => "print usage message and exit" ]
);

my $odir = $opt->outdir;
if ( not -e $odir ) {
    mkdir $odir;
}

my $dt = Orchard::Period->getDateTimeFromId( $opt->period );

my $xlsxHeaderRow = [
    qw/song_no song writer src_nm src_ctry src2_nm
      src2_ctry src3_nm src3_ctry src4_nm src4_ctry
      inc_typ sh_id rptg_pd sales_pd prod_no art_prd_no
      units sng_shr_pct cntrl_pct amount df src_prod
      iswc_cd ext_song artist src_song isrc
      vendor_id adjusted_gross fee_pct net_revenue
      preferred_currency currency_conversion_rate net_revenue_preferred_currency
      /
];
my $colmap = _colmap();

my $pid = $opt->period;
my $dbh = Orchard::Session::getdbh('snowflake');
my $tab = 'royalty_accounting.qa.fact_sale_publishing';
my $sth = $dbh->prepare("SELECT * FROM $tab WHERE period_id=? AND account_id=?");

logMessage( 'info', "Getting all accounts from Snowflake" );
my $_acct = defined $opt->vendor ? '='.$opt->vendor : ">0";
my $accts = $dbh->selectcol_arrayref( "SELECT DISTINCT account_id FROM $tab WHERE period_id=$pid AND account_id$_acct");

my $i = 1;
foreach my $aid ( sort {$a<=>$b} @{$accts} ) {
	my $idx = sprintf("(%03d/%03d)",$i++,scalar @{$accts});
    $sth->execute( $pid, $aid );
    if ( $sth->rows ) {
        logMessage( 'info', "$idx Creating statment for account: $aid total rows: " . $sth->rows );

        my $xlsxRows;
		logMessage('info1',"fetching rows from Snowflake");
        while ( my $row = $sth->fetchrow_hashref() ) {
            my $xlsxRow;
            foreach my $cname ( @{$xlsxHeaderRow} ) {
                my $dbcol = $colmap->{$cname};
                my $data  = $row->{$dbcol};
                $data =~ s/\\N// if defined $data;
                $data = ''       if not defined $data;
                push @{$xlsxRow}, $data;
            }
            push @{$xlsxRows}, $xlsxRow;
        }

        my $filename = "$odir/L" . join '_', ( $aid, $pid, "publishing", "statement", $dt->month_name, $dt->year . ".xlsx" );

        logMessage( 'info1', "Creating $filename" );
        my $sp = Spreadsheet::Write->new( file => $filename );
        $sp->addrow( { 'content' => $xlsxHeaderRow } );
        map { $sp->addrow( { 'content' => $_ } ) } @{$xlsxRows};
        $sp->close;

    } else {
        logMessage( 'error', "$idx No rows for account: $aid - check that!" );
    }
}

sub _colmap {
    {
        'song_no'                        => 'SONG_NO',
        'song'                           => 'SONG',
        'writer'                         => 'WRITER',
        'src_nm'                         => 'SOURCE_NAME',
        'src_ctry'                       => 'SOURCE_COUNTRY',
        'src2_nm'                        => 'SOURCE2_COUNTRY',
        'src2_ctry'                      => 'SOURCE2_NAME',
        'src3_nm'                        => 'SOURCE3_COUNTRY',
        'src3_ctry'                      => 'SOURCE3_NAME',
        'src4_nm'                        => 'SOURCE4_COUNTRY',
        'src4_ctry'                      => 'SOURCE4_NAME',
        'inc_typ'                        => 'INCOME_TYPE',
        'sh_id'                          => 'SH_ID',
        'rptg_pd'                        => 'RPTG_PD',
        'sales_pd'                       => 'SALES_PD',
        'prod_no'                        => 'PRODUCT_NUMBER',
        'art_prd_no'                     => 'ARTIST_PRODUCT_NUMBER',
        'units'                          => 'UNITS',
        'sng_shr_pct'                    => 'SONG_SHARE_PERCENT',
        'cntrl_pct'                      => 'CONTROL_PERCENT',
        'amount'                         => 'AMOUNT',
        'df'                             => 'DF',
        'src_prod'                       => 'SOURCE_PRODUCT',
        'iswc_cd'                        => 'ISWC',
        'ext_song'                       => 'EXTERNAL_SONG_ID',
        'artist'                         => 'ARTIST',
        'src_song'                       => 'SOURCE_SONG',
        'isrc'                           => 'ISRC',
        'vendor_id'                      => 'ACCOUNT_ID',
        'adjusted_gross'                 => 'ADJUSTED_GROSS',
        'fee_pct'                        => 'FEE_PERCENT',
        'net_revenue'                    => 'NET_REVENUE',
        'preferred_currency'             => 'PREFERRED_CURRENCY',
        'currency_conversion_rate'       => 'CURRENCY_CONVERSION_RATE',
        'net_revenue_preferred_currency' => 'NET_REVENUE_PREFERRED_CURRENCY'
    };
}