#!/usr/bin/perl

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

use JSON::XS;
use FileHandle;
use List::Uniq (qw/:all/);
use DateTime::Format::DateParse;
use Text::CSV::Easy(qw/csv_build csv_parse/);
use Getopt::Long::Descriptive(qw/describe_options/);

use lib '/Users/denk002/projects/collab/jgetter/perllib';
use Orchard::Session;
use Orchard::DB::Connect;
use Orchard::Accounting::JIRA;
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 } ],
    [ 'period|p=i'      => 'Period ID to adjust',                                 { 'required' => 1 } ],
    [ 'file|f=s'        => 'Checks Payable File to use',                          { 'required' => 1 } ],
);
## [ 'jira|j=i'        => 'JIRA ticket number',                                  { 'required' => 1 } ],

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

my ($jira_ticket) = $opt->file =~ m!.*?/ACC(....)/.*$!;

my $period   = $opt->period;
my $jira     = Orchard::Accounting::JIRA->new( $jira_ticket, $opt->git_version );
my $branch   = $jira->branch;
my $filename = $jira->getfilepath("add_checkspaid_p${period}");

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 $vendors;
my $i = 1;
ROW: foreach my $row ( @{$rows} ) {
    my $data;
    push @{$data}, ++$max;     ## id
    push @{$data}, 'NOW()';    ## entry_date

    my $vendor = $sheet->getcol( $row, 'A' );
    if ( not defined $vendor or $vendor eq '' ) {
        logMessage( 'error', "Label ID is blank - skipping" );
        next ROW;
    }
    ## next if $vendor > 24100;
    push @{$vendors}, $vendor;

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

    my $payable = $sheet->getcol( $row, 'B' );    ## check payable
    if ( defined $payable ) {
        $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) > 63 ) {
            push @{$errs}, "ChecksPayable: $payable is too long (" . length($payable) . ")";
        }
    }
    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];
        if ( not defined $upc ) {
            logMessage( 'error', "No UPC found in DB for label " . $row->{'label_id'} );
            next ROW;
        }
    }
    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 = "`ROLLBACK-DATA-${branch}`";
my $body  = printTOP($table);
$body .= join ",\n", @{$lines};
$body .= ';';
$body .= printEND($table);

$jira->finish( $filename, $body );

$vendors = uniq($vendors);
$vendors = [ sort { $a <=> $b } @{$vendors} ];
my $list;
for ( my $i = 0 ; $i < scalar @{$vendors} ; $i++ ) {
    if ( $i > 0 and $i % 25 == 0 ) {
        $list .= "\n";
    } else {
        $list .= $vendors->[$i] . ',';
    }
}
$list =~ s/,$//g;

my $checksum = <<"!";
SELECT
	cp.id,
	cp.entry_date,
	cp.check_payable,
	cp.upc,
	cp.paidfor_type,
	cp.check_no,
	cp.check_amt,
	cp.amount_in_original_currency,
	cp.paidfor_period_id,
	cp.currency_id,
	cp.cut_date,
	cp.comments
FROM
	art_relations.checkspaid cp
JOIN
	$table rb USING (id)
;

-- SELECT r.*
-- FROM releases r
-- JOIN artist_info ai ON ai.artist_id=r.artist_id
-- WHERE TRUE
-- AND ai.vendor_id IN (21865,74763)
-- ;

SELECT v.vendor_id
FROM vendor v
LEFT JOIN (
	SELECT ai.vendor_id
	FROM artist_info ai
	JOIN releases r ON r.artist_id=ai.artist_id
	JOIN $table rb ON rb.upc=r.upc
) xx ON xx.vendor_id=v.vendor_id
WHERE xx.vendor_id IS NULL
AND  v.vendor_id IN (
$list
);

!

my $parent_dir  = File::Basename::dirname( $opt->file );
my $chkfilename = "$parent_dir/checksum.sql";
my $chkfile     = FileHandle->new( "$chkfilename", "w" );
$chkfile->print($checksum);
$chkfile->close;

system("cat $chkfilename");

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

-- changeset tdenkinger: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 checkspaid SELECT * FROM $table;

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

!
    $sql;
}