#!/usr/bin/perl

use constant 'INDIR'  => '/Users/jgetter/JIRA-Attachments';
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 DateTime;
use JSON::XS;
use FileHandle;
use Text::CSV::Easy(qw/csv_build csv_parse/);
use Getopt::Long::Descriptive(qw/describe_options/);

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'both|b'              => 'Both checkspaid and vendor_checkspaid' ],
    [ 'checkspaid|c'        => 'checkspaid table update' ],
    [ 'git_version|g=i'     => 'git tree version <ACC>-<1234>-<VERSION> default 1', { 'default' => 1 } ],
    [ 'jira_id|j=i'         => 'JIRA ticket number', { 'required' => 1 } ],
    [ 'month|m=i'           => 'Month to process - default current month', { 'default' => DateTime->now->month } ],
    [ 'vendor_checkspaid|v' => 'vendor_checkspaid table update' ],
    [ 'help|?|h'            => "print usage message and exit" ]
);

my $dir    = 'ACC' . $opt->jira_id;
my $ticket = 'ACC-' . $opt->jira_id;
my $branch = $ticket . '-' . $opt->git_version;

my $sql = <<"!";
--liquibase formatted sql

--changeset jgetter:1
!

if ( $opt->checkspaid or $opt->both ) {
    my $infile   = INDIR . "/${dir}/CPIDs.txt";
    my $table    = '`art_relations`.`checkspaid`';
    my $rollback = '`art_relations`.`ROLLBACK-DATA-' . $dir . '`';
    $sql .= parse_file( $infile, $table, $rollback );

}

if ( $opt->vendor_checkspaid or $opt->both ) {
    my $infile   = INDIR . "/${dir}/VCIDs.txt";
    my $table    = '`art_relations`.`vendor_checkspaid`';
    my $rollback = '`art_relations`.`ROLLBACK-DATA-VP-' . $dir . '`';
    $sql .= parse_file( $infile, $table, $rollback );
}

if ( $opt->checkspaid or $opt->both ) {
    my $table = '`art_relations`.`ROLLBACK-DATA-' . $dir . '`';
    $sql .= <<"!";

--rollback UPDATE art_relations.checkspaid cp JOIN $table rb USING (id) SET cp.cut_date = rb.cut_date;
--rollback DROP TABLE $table;

!
}

if ( $opt->vendor_checkspaid or $opt->both ) {
    my $table = '`art_relations`.`ROLLBACK-DATA-VP-' . $dir . '`';
    $sql .= <<"!";

--rollback UPDATE art_relations.checkspaid cp JOIN $table rb USING (id) SET cp.cut_date = rb.cut_date;
--rollback DROP TABLE $table;

!
}

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 $outfile = OUTDIR . "/${ticket}_fix_checkspaid_cut_date_fintunes_" . $opt->git_version . ".sql";

print "Creating file $outfile\n";

my $ofh = FileHandle->new( $outfile, 'w' );
$ofh->print($sql);
$ofh->close;

map { print "$_\n", } $repo->command('status');

sub parse_file {
    my $infile   = shift;
    my $inTable  = shift;
    my $outTable = shift;

    my $lastDayDate = DateTime->last_day_of_month( 'year' => 2019, 'month' => $opt->month )->date;

    my $sql .= <<"!";
DROP TABLE IF EXISTS $outTable;

CREATE TABLE $outTable AS
SELECT * FROM $inTable
WHERE 1 AND id IN (
!
    my $fh = FileHandle->new( $infile, 'r' ) || die "No such file $infile";
    my $data;
    my $i = 1;
    while (<$fh>) {
        chomp;
        my $text = sprintf( "%8s", $_ );
        if ( $i++ % 17 == 0 ) {
            push @{$data}, $text;
            my $line = join ",", @{$data};
            $sql .= "$line,\n";
            undef $data;
        } else {
            push @{$data}, $text;
        }
    }
    $fh->close;
    my $line = join ",", @{$data};

    $sql .= <<"!";
$line
);

UPDATE $inTable cp JOIN $outTable rb USING (id) SET cp.cut_date = "$lastDayDate";
!
    $sql;
}