#!/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 $checkem = 0;

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 $dbx = Orchard::Session::getdbx($db);

my $lines;
my $seen;
my $csv = Orchard::ETL::Extract::CSV->new($file);
while ( my $row = $csv->getrow() ) {
    my ( $aid, $cid, $curr, $amt ) =
      ( $csv->getcol( $row, 'A' ), $csv->getcol( $row, 'B' ), $csv->getcol( $row, 'E' ), $csv->getcol( $row, 'F' ), );
    $amt =~ s/,//g;

    $seen->{$aid}++;

    my $predicate = { 'contract_id' => $cid, 'account_id' => $aid };

    if ($checkem) {
        logMessage( 'info', "checking for contract: $cid and account: $aid" );
        my $act = $dbx->resultset('RoyaltyAccounting::AccountContract')->search($predicate)->first;
        if ( defined $act ) {
            my $id  = $act->account_id;
            my $apt = $dbx->resultset('RoyaltyAccounting::AccountPaymentTerm')->search( { 'account_id' => $id } )->first;
            if ( defined $apt ) {
                if ( $apt->currency_code ne $curr ) {
                    logMessage( 'error1', "account: $aid currency: " . $apt->currenc_code . " not $curr" );
                } else {
                    logMessage( 'info1', "account: $aid contract: $cid currency: $curr [ok] " );
                }
            } else {
                logMessage( 'error1', "account: $aid not in account payment term" );
            }
        } else {
            logMessage( 'error1', "bad pair contract: $cid and account: $aid" );
        }
    }
    my $line = csv_build(
        ( $aid, $cid, '', $amt, $curr, 2022, 9, 2022, 9, 'Monthly Royalty Payment', 'Transfer payment from Kobalt to Kollective', '' ) );
    push @{$lines}, $line;

}

foreach my $k ( sort keys %{$seen} ) {
    next if $seen->{$k} < 2;
    logMessage( 'info', "account: $k has $seen->{$k} contracts" );
}

## header
my $header = csv_build( (
        "Account ID (Required",
        "Contract ID (Optional)",
        "UPC (Optional)",
        "Amount (Required)",
        "Currency (Required)",
        "Activity year (Required)",
        "Activity month (Required)",
        "Apply to Statement year (Required)",
        "Apply to Statement month (Required)",
        "Adjustment Type (Required)",
        "Comment (Optional)",
        "Distribution Type (Optional)",
    )
);
print "$header\n";

## data
map { print "$_\n" } @{$lines};