#!/usr/bin/perl

print "Use /Users/jgetter/collab/perllib/bin/accounting/legacy/add_checkspaid.pl\n";

__END__

use constant 'OUTDIR' => '/var/app/orchard/database/art_relations/build/changelog/dml';

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

use Git;
use JSON::XS;
use FileHandle;
use DateTime::Format::DateParse;
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::Session;
use Orchard::DB::Connect;
use Orchard::ETL::Extract::CSV;
use Orchard::ETL::Extract::SpreadSheet;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'help|?|h'        => "print usage message and exit" ],
    [ 'id|i=i'          => 'ID to start with - default 10k above max checkspaid', { 'default'  => 10_000 } ],
    [ 'database|d=s'    => 'DB connection host - default orchdev',                { 'default'  => 'orchprod' } ],
    [ 'currency|c=s'    => 'Currency to convert to USD for checkspaid',           { 'default'  => 'USD' } ],
    [ 'git_version|v=i' => 'git tree version <ACC>-<1234>-<VERSION> default 1',   { 'default'  => 1 } ],
    [ 'jira|j=i'        => 'JIRA ticket number',                                  { 'required' => 1 } ],
    [ 'period|p=i'      => 'Period ID to adjust',                                 { 'required' => 1 } ],
    [ 'file|f=s'        => 'Checks Payable File to use',                          { 'required' => 1 } ],
);

if ( $opt->help ) {
    logMessage( 'info', $usage->text, 'options' );
    exit 1;
}

my $jira   = 'ACC-' . $opt->jira;
my $branch = $jira . '-' . $opt->git_version;
my $period = $opt->period;
my $repo   = Git->repository(OUTDIR);

my $currentBranch = $repo->command( 'rev-parse', '--abbrev-ref', 'HEAD' );
chomp($currentBranch);
if ( $currentBranch ne $branch ) {
    print "Need to create " . OUTDIR . " branch [$branch] - current branch is $currentBranch\n";
    exit;
}

my $filename = OUTDIR . '/' . $jira . '_checkspaid_for_tickler_' . $period . '_' . $opt->git_version . '.sql';

logMessage( 'info', 'Connecting to ' . $opt->database );

my $dsn = Orchard::DB::Connect->dsn( $opt->database );
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";

## deal with currency conversion
my $currencies = $dbh->selectall_hashref(
    qq{
SELECT cur.ISO_4217_code AS `code`, xr.exchange_rate, xr.currency_from_id
FROM currency_exchange_rates xr
JOIN currencies cur on cur.id=xr.currency_from_id
WHERE xr.period_id = ? AND xr.currency_to_id = 1
}, 'code', undef, ( $opt->period )
);

## UPC lookup statement handle in case UPC is blank
my $sth = $dbh->prepare(
    qq{
SELECT r.upc FROM releases r 
JOIN artist_info ai ON ai.artist_id=r.artist_id
WHERE ai.vendor_id=? LIMIT 1
}
);

my $row = $dbh->selectcol_arrayref('SELECT MAX(id) FROM art_relations.checkspaid') or die $dbh->errstr;
my $max = $row->[0] + $opt->id;

## id entry_date check_payable upc paidfor_type check_no
## check_amt amount_in_original_currency paidfor_period_id currency_id cut_date

my $errs;

my $sheet = Orchard::ETL::Extract::SpreadSheet->new( $opt->file, 1 );
my $rows  = $sheet->getrows();
my $lines;
my $i = 1;
foreach my $row ( @{$rows} ) {
    my $data;
    push @{$data}, ++$max;     ## id
    push @{$data}, 'NOW()';    ## entry_date

    my $vendor = $sheet->getcol( $row, 'A' );
    my $idx    = sprintf( "(%03d/%03d)", $i++, scalar @{$rows} );
    logMessage( 'info', "$idx processing $vendor" );

    my $payable = $sheet->getcol( $row, 'B' );    ## check payable
    if ( defined $payable and length($payable) > 64 ) {
        $payable =~ s/\r\n//;
        $payable =~ s/\n//;

        ## try converting limited to LTD
        if ( $payable =~ m/limited/ ) {
            $payable =~ s/limited/ltd/i;
        } elsif ( $payable =~ m/LIMITED/ ) {
            $payable =~ s/LIMITED/LTD/i;
        }
        if ( length($payable) > 64 ) {
            push @{$errs}, "ChecksPayable: $payable is too long (" . length($payable) . ")" if length($payable) > 64;
        }
    }
    push @{$data}, defined $payable ? $payable : '';

    my $upc = $sheet->getcol( $row, 'D' );    ## upc
    if ( not defined $upc or $upc eq '' ) {
        logMessage( 'info2', "Getting a valid UPC for $vendor" );
        $sth->execute($vendor);
        my $aref = $sth->fetchrow_arrayref();
        $upc = $aref->[0];
    }
    push @{$data}, $upc;

    push @{$data}, lc( $sheet->getcol( $row, 'E' ) );    ## paid for type

    my $checkno = $sheet->getcol( $row, 'F' );           ## check no
    if ( length($checkno) > 16 ) {
        ## cut down the year digits
        $checkno =~ s!/20(..)!/$1!;
        if ( length($checkno) > 16 ) {
            logMessage( 'error', "Checkno $checkno is too long" );
            exit;
        }
    }
    $checkno = defined $checkno ? $checkno : 'NULL';
    push @{$data}, $checkno;

    my $amount = $sheet->getcol( $row, 'G' );            ## amount
    $amount =~ s/,//g;                                   ## strip commas from amount

    my $xr      = 1;
    my $curid   = 1;
    my $curcode = "USD";
    my $curcol  = $sheet->getcol( $row, 'H' );
    if ( $curcol !~ m/.*USD.*/ ) {
        foreach my $cur ( keys %{$currencies} ) {
            if ( $curcol =~ m/$cur/ ) {
                $xr      = $currencies->{$cur}->{'exchange_rate'};
                $curid   = $currencies->{$cur}->{'currency_from_id'};
                $curcode = $cur;
            }
        }
    }
    logMessage( 'info2', "using curcode: $curcode curid: $curid xr: $xr ($curcol)" );

    ## convert cutdate to mysql date
    my $cutdate = $sheet->getcol( $row, 'I' );    ## cutdate

    my $dt = DateTime::Format::DateParse->parse_datetime($cutdate);
    if ( $dt->year =~ m/^19/ ) {
        $dt->add( years => 100 );
    }

    #my $dt = DateTime::Format::DateParse->parse_datetime($cutdate)->ymd;

    my $comments = $sheet->getcol( $row, 'J' );    ## comments
    $comments = defined $comments ? $comments : 'NULL';

    push @{$data}, $amount * $xr;                  ## USD amount
    push @{$data}, $amount;                        ## FX currency
    push @{$data}, $period;                        ## period
    push @{$data}, $curid;                         ## currency
    push @{$data}, $dt->ymd;                       ## cut date
    push @{$data}, $comments;                      ## comments
    my $line = csv_build( @{$data} );
    $line =~ s/"NOW\(\)"/NOW()/g;
    $line =~ s/"NULL"/NULL/g;
    push @{$lines}, "($line)";
}

if ( defined $errs ) {
    map { print STDERR "$_\n" } @{$errs};
    exit 1;
}

my $table = '`art_relations`.`ROLLBACK-DATA-' . $jira . '-' . $opt->git_version . '`';
my $top   = printTOP($table);
$top .= join ",\n", @{$lines};
$top .= ';';
my $end = printEND($table);

my $out = FileHandle->new( $filename, 'w' );
$out->print($top);
$out->print($end);
$out->close;

system("cat $filename");

sub printTOP {
    my $table = shift;
    my $sql   = <<"!";
-- liquibase formatted sql

-- changeset jgetter:1

DROP TABLE IF EXISTS $table;

CREATE TABLE $table (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `entry_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `upc` bigint(20) unsigned NOT NULL,
  `check_payable` varchar(65) NOT NULL,
  `check_no` varchar(16) DEFAULT NULL,
  `check_amt` decimal(18,6) NOT NULL,
  `cut_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cash_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `comments` varchar(255) DEFAULT NULL,
  `paidfor_type` enum('physical','digital','phy_recoupe','dig_recoupe') DEFAULT NULL,
  `paidfor_period_id` smallint(6) unsigned DEFAULT NULL,
  `currency_id` smallint(6) DEFAULT NULL,
  `amount_in_original_currency` decimal(18,6) DEFAULT NULL,
  `last_modified_by` int(11) DEFAULT '179',
  `user_type` enum('oa','alw','system') DEFAULT 'system',
  PRIMARY KEY (`id`)
);

INSERT INTO $table (
id,entry_date,check_payable,upc,paidfor_type,check_no,check_amt,amount_in_original_currency,paidfor_period_id,currency_id,cut_date,comments
) 
VALUES
!
    $sql;
}

sub printEND {
    my $table = shift;
    my $sql   = <<"!";


INSERT INTO `art_relations`.`checkspaid` SELECT * FROM $table;

--rollback DELETE cp.* FROM `art_relations`.`checkspaid` cp JOIN $table rb USING (id);
--rollback DROP TABLE $table;

!
    $sql;
}