#!/usr/bin/perl

use constant 'OUTDIR' => '/var/app/orchard/database/payment_holds/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;
use Orchard::ETL::Extract::SpreadSheet;

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 } ],
    [ 'file|f=s'        => 'Checks Payable File to use',                        { 'required' => 1 } ],
    [ 'verbose|v'       => 'Verbose output for debugging ' ],
    [ 'help|?|h'        => "print usage message and exit" ]
);

my $jira   = 'ACC-' . $opt->jira;
my $branch = $jira . '-' . $opt->git_version;
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 . '_update_payment_hold_desc.sql';

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";
my $sth = $dbh->prepare(qq{ select * from vendor_payment_hold where vendor_id=? and status = 'active' });

my $errs;
my $updates;
my $inserts;

my $sheet = Orchard::ETL::Extract::SpreadSheet->new( $opt->file, 1 );
my $rows  = $sheet->getrows();
my $i     = 1;
foreach my $row ( @{$rows} ) {
    ## last if $i == 11;
    my $venid = $sheet->getcol( $row, 'A' );
    my $desc  = $sheet->getcol( $row, 'C' );

    print "$i - $venid:\t$desc\n" if $opt->verbose;

    $sth->execute($venid);
    ## if active hold just update the desc
    if ( $sth->rows ) {
        push @{$updates}, "$venid\t$desc";
    } else {
        push @{$inserts}, "$venid\t$desc";
    }
    $i++;
}

my $table = '`ROLLBACK-DATA-' . $jira . '`';

my $insertSql;
if ( defined $inserts ) {
    foreach my $insert ( @{$inserts} ) {
        my ( $id, $desc ) = split "\t", $insert;

        ## vendor payment hold
        $insertSql .= " -- vendor payment hold $id\n";
        $insertSql .= "INSERT INTO vendor_payment_hold (creator_id,status,description,vendor_id)\n   ";
        $insertSql .= "VALUES ('oa:179','active'," . $dbh->quote($desc) . ",$id);\n";

        ## vendor payment hold log
        $insertSql .= " -- vendor payment hold log $id\n";
        $insertSql .= "INSERT INTO vendor_payment_hold_log SELECT NOW(), 'create', 'oa:179', id, description, status\n   ";
        $insertSql .= "FROM vendor_payment_hold WHERE vendor_id=$id AND status='active';\n";

        ## rollback table
        $insertSql .= " -- rollback $id\n";
        $insertSql .= "INSERT INTO $table (creator_id,status,newdesc,vendor_id,rollback_delete)\n   ";
        $insertSql .= "VALUES ('oa:179','active'," . $dbh->quote($desc) . ",$id,1);\n\n";

    }
}

my $updateSql;
if ( defined $updates ) {
    foreach my $update ( @{$updates} ) {
        my ( $id, $desc ) = split "\t", $update;
        $updateSql .= "INSERT INTO $table SELECT *,NULL,NULL FROM vendor_payment_hold WHERE vendor_id=$id AND status='active';\n";
        $updateSql .= "UPDATE $table SET newdesc=".$dbh->quote($desc)." WHERE vendor_id=$id;\n\n";
    }
}

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

my $top = printTOP($table);
## $top .= join ",\n", @{$lines};
my $end = printEND($table);

my $out = FileHandle->new( $filename, 'w' );
$out->print($top);
$out->print($insertSql) if $insertSql;
$out->print($updateSql) if $updateSql;
$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 AS SELECT * FROM vendor_payment_hold WHERE 1=2;
ALTER TABLE $table ADD newdesc TEXT NULL;
ALTER TABLE $table ADD rollback_delete tinyint(1) NULL;

!
}

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

UPDATE vendor_payment_hold vp JOIN $table rb USING(id) SET vp.description=rb.newdesc, vp.last_updated=NOW();
UPDATE vendor_payment_hold_log vp JOIN $table rb ON vp.vendor_payment_hold_id=rb.id SET vp.description=rb.newdesc,vp.timestamp=NOW();

-- rollback DELETE vp.* FROM vendor_payment_hold vp JOIN $table rb USING(id) WHERE rb.rollback_delete=1;
-- rollback UPDATE vendor_payment_hold vp JOIN $table rb USING(id) SET vp.description=rb.description,vp.last_updated=NOW();
-- rollback UPDATE vendor_payment_hold_log vp JOIN $table rb ON vp.vendor_payment_hold_id=rb.id SET vp.description=rb.description,vp.timestamp=NOW();
-- rollback DROP TABLE $table;
!
    $sql;
}