#!/usr/bin/perl

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::DB::Connect;
use Orchard::ETL::Extract::CSV;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'database|d=s'    => 'DB connection host - default orchdev',              { 'default'  => 'orchprod' } ],
    [ 'git_version|g=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 } ],
    [ 'help|?|h'        => "print usage message and exit" ]
);

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 . '.sql';

my $dsn = Orchard::DB::Connect->dsn( $opt->database );
my $dbh;
if ( defined $dsn->{'ssh'} ) {
    my $ssh = $dsn->{'ssh'};
    $ssh->{'net'}->spawn( $ssh->{'tunnel'} );
}
$dbh = DBI->connect( @{ $dsn->{'conn'} } ) || die "Conn: $DBI::errstr";

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

## 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 $csv = Orchard::ETL::Extract::CSV->new( $opt->file, 1 );
my $lines;
while ( my $row = $csv->getrow() ) {
    my $data;
    push @{$data}, ++$max;     ## id
    push @{$data}, 'NOW()';    ## entry_date

    my $payable = $csv->getcol( $row, 'B' );    ## check payable
    push @{$data}, $payable;

    push @{$errs}, "ChecksPayable: $payable is too long (" . length($payable) . ")" if length($payable) > 64;

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

    my $checkno = $csv->getcol( $row, 'F' );           ## check no
    $checkno = defined $checkno ? $checkno : 'NULL';
    push @{$data}, $checkno;

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

    ## convert cutdate to mysql date
    my $cutdate = $csv->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 = $csv->getcol( $row, 'J' );          ## comments
    $comments = defined $comments ? $comments : 'NULL';

    push @{$data}, $amount;                            ## normalized amount
    push @{$data}, $amount;                            ## orig currency == amount
    push @{$data}, $period;                            ## period
    push @{$data}, 1;                                  ## USD currency == 1
    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 . '`';
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;
}