#!/usr/bin/perl

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

use JSON::XS;
use FileHandle;
use Spreadsheet::Write;
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::Period;
use Orchard::Session;
use Orchard::DB::Connect;
use Orchard::ETL::Extract::CSV;

my $deals;
my $file      = shift @ARGV;
my $sales_tsv = Orchard::ETL::Extract::CSV->new( $file, 1, undef, "\t" );
$sales_tsv->csv->quote_char(undef);
$sales_tsv->csv->escape_char(undef);
while ( my $row = $sales_tsv->getrow() ) {
    my $awalid   = $row->{'ASSIGNOR_ID'};
    my $currency = $row->{'AGR_CURRENCY'};
    my $period   = $row->{'ACCOUNTING_PERIOD'};
    my $sched;
    if ( $period eq 'Monthly' ) {
        $sched = '30_days_after_month_end';
    } else {
        $sched = '30_days_after_quater_end';
    }
    $deals->{$awalid}->{'schedule'} = $sched;
    $deals->{$awalid}->{'currencies'}->{$currency}++;
    $deals->{$awalid}->{'account_id'} = undef;
}

my $dsn = Orchard::DB::Connect->dsn('orchprod');
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 $vendors = $dbh->selectall_hashref(
    qq{
select vendor_id,contact_email
from vendor 
where contact_email like 'IP%' 
and owner = 'awal'}
    , "contact_email"
);
foreach my $awalid ( keys %{$vendors} ) {
    my $vid = $vendors->{$awalid}->{'vendor_id'};
    $deals->{$awalid}->{'account_id'} = $vid if exists $deals->{$awalid};
}

my $mapped;
my $ip_tsv = Orchard::ETL::Extract::CSV->new( 'interested_parties.tsv', 1, undef, "\t" );
$ip_tsv->csv->quote_char(undef);
$ip_tsv->csv->escape_char(undef);
while ( my $iprow = $ip_tsv->getrow() ) {
    my $awalid = $ip_tsv->getcol( $iprow, 'A' );
    next if not exists $deals->{$awalid};

    my $acct_name = $ip_tsv->getcol( $iprow, 'B' );

    my $comment;
    my $cur  = $ip_tsv->getcol( $iprow, 'C' );
    my $curs = $deals->{$awalid}->{'currencies'};
    if ( defined $cur ) {
        if ( scalar keys %{$curs} == 1 ) {
            my ($agr_cur) = keys %{ $deals->{$awalid}->{'currencies'} };
            if ( $agr_cur ne $cur ) {
                $comment = "Agreement currency $agr_cur != IP cur $cur";
            }
        } else {
            my $agr_curs = join ',', keys %{ $deals->{$awalid}->{'currencies'} };
            $comment = "Multiple agreement currencies $agr_curs != IP cur $cur";
        }
    } else {
        if ( scalar keys %{$curs} == 1 ) {
            $cur = keys %{ $deals->{$awalid}->{'currencies'} };
        } else {
            my $agr_curs = join ',', keys %{ $deals->{$awalid}->{'currencies'} };
            $cur     = undef;
            $comment = "Multiple agreement currencies $agr_curs and IP has no currency";
        }
    }

    my $min = $ip_tsv->getcol( $iprow, 'D' );
    if ( defined $min and $min < 0 ) {
        $min     = '';
        $comment = "$cur not supported";
    }
    my $acctid = $deals->{$awalid}->{'account_id'};
	my $schedule = $deals->{$awalid}->{'schedule'};

    push @{$mapped}, [ $awalid, $acctid, $acct_name, $cur, $min, $schedule, 'The Orchard', $comment ];

}

my $header = [
      "AWAL ID","Account ID","Account Name","Account Payment Currency",
      "Account Payment Minimum","Payment Schedule","Signing Entity","Comment"
];

my $head = csv_build( @{$header} );
print "$head\n";
foreach my $aref ( @{$mapped} ) {
    my $line = csv_build( @{$aref} );
    print "$line\n";
}

__END__
interested parties query

with awal as (
SELECT
	i.id awal_id,
    i.name account_name,
	c.abbreviation account_payment_currency
FROM
	awal_sax.sax.interested_parties i
	LEFT JOIN awal_sax.sax.currencies c ON c.id = i.currency_id
)
select
   a.*,
   CASE
     WHEN a.account_payment_currency = 'GBP' THEN 20
     WHEN a.account_payment_currency IN ('AUD','CAD','USD') THEN 30
     WHEN a.account_payment_currency = 'SEK' THEN 1000
     WHEN a.account_payment_currency IN ('EUR','CHF') THEN 25
     WHEN a.account_payment_currency IS NULL THEN NULL
   ELSE -1 END AS account_payment_minimum
from
    awal a