#!/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 '/var/app/orchard/collab/jgetter/perllib';
use Orchard::Period;
use Orchard::Session;
use Orchard::ETL::Extract::CSV;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'detailFile|d=s' => 'Detailed sales file that includes calculated details', { 'required' => 1 } ],
    [ 'period|p=i'     => 'Period ID for the statements i.e. 267 - March 2021',   { 'required' => 1 } ],
    [ 'outdir|o=s'     => 'Output directory default == "."',                      { 'default'  => "." } ],
    [ 'help|?|h'       => "print usage message and exit" ]
);

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/
];

logMessage( 'info', 'Reading detail file ' . $opt->detailfile );
my $totals;
my $vendorData;
my $sales_csv = Orchard::ETL::Extract::CSV->new( $opt->detailfile, 1, undef, "\t" );
$sales_csv->csv->quote_char(undef);
$sales_csv->csv->escape_char(undef);
while ( my $row = $sales_csv->getrow() ) {
    my $venid = $sales_csv->getkey( $row, 'vendor_id' );
    if ( $venid == 0 ) { 
        $venid = 32824;
    } 

    ## report Dance Planet under 16475
    $venid = 16475 if $venid == 16476;

	$totals->{$venid}->{'revenue'} += $row->{'net_revenue'};
	$totals->{$venid}->{'count'} ++;

    my $xlsxRow;
    foreach my $col ( @{$xlsxHeaderRow} ) {
        my $col = $sales_csv->getkey( $row, $col );
        $col =~ s/\\N// if defined $col;
        $col = ''       if not defined $col;
        push @{$xlsxRow}, $col;
    }
    push @{ $vendorData->{$venid} }, $xlsxRow;

}

mkdir $opt->outdir if not -d $opt->outdir;

foreach my $vid ( sort keys %{$vendorData} ) {
    my $rows = $vendorData->{$vid};

    my $filename = $opt->outdir . "/L" . join '_', ( $vid, $opt->period, "publishing", "statement", $dt->month_name, $dt->year . ".xlsx" );
    logMessage( 'info', "Creating $filename" );

    my $sp = Spreadsheet::Write->new( file => $filename );
    $sp->addrow( { 'content' => $xlsxHeaderRow } );
    map { $sp->addrow( { 'content' => $_ } ) } @{$rows};
    $sp->close;
}