#!/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 DateTime;
use JSON::XS;
use FileHandle;
use Spreadsheet::ParseExcel;
use Data::Validate::Type qw/is_number/;
use Digest::MD5 qw(md5 md5_hex md5_base64);

use lib '/var/app/orchard/collab/jgetter/perllib';
use Orchard::Period;
use Orchard::Session;
use Orchard::NameCase;
use Orchard::DB::Connect;
use Orchard::ManualAdjustmentCategory;

my ( $opt, $usage ) = clopt(
    [ 'bump|b=i'        => 'Bump the auto increment by this much default 10,000', { 'default'  => 10_000 } ],
    [ '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 } ],
    [ '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 $templateHeader = [
    "Type",           "ID",              "UPC",           "Amount",
    "Currency",       "Adj for year",    "Adj for Month", "Apply to Year",
    "Apply to Month", "Adjustment Type", "Comment",       "Distribution Context Type"
];

#my $checksum = join '::', map { lc($_) } @{$templateHeader};
my $checksum = md5_hex( @{$templateHeader} );
my $colCount = scalar @{$templateHeader};

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse( $opt->file );
if ( !defined $workbook ) {
    logMessage( 'error', $parser->error() );
}
my $worksheet = $workbook->worksheet(0);
my $rmax      = ( $worksheet->row_range() )[1];

## check header for manual adjustment template
my $sheetHeader;
for ( my $i = 0 ; $i < $colCount ; $i++ ) {
    push @{$sheetHeader}, $worksheet->get_cell( 0, $i )->value;
}
my $sheetChecksum = md5_hex( @{$sheetHeader} );

if ( $checksum ne $sheetChecksum ) {
    logMessage( 'error', "Manual adjustment template not used: " . $opt->file );
    exit;
}

## connect to prod and get the max(manual_adjustment_id)
my $dsn = Orchard::DB::Connect->dsn('orchprod');
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.manual_adjustment') or die $dbh->errstr;
my $max = $row->[0] + $opt->bump;

## object to parse dates into Orchard period Ids
# my $cat = Orchard::ManualAdjustmentCategory->new;

my @rows;
for ( my $r = 1 ; $r < $rmax ; $r++ ) {
    my $row;
    ## cut off distribution context type from the end "-1"
    for ( my $c = 0 ; $c < $colCount - 1 ; $c++ ) {
        my $cell = $worksheet->get_cell( $r, $c );
        my $val = defined $cell ? $cell->value : '';
        last if $c == 0 and $val eq '';
        push @{$row}, $val eq '' ? undef : $val;
    }
    last if not defined $row;

    ## adjust_for_period_id
    my $adjdt = DateTime->new( 'year' => $row->[5], 'month' => $row->[6] );
    push @{$row}, Orchard::Period->getIdFromDateTime($adjdt);
    $row->[6] = $adjdt->quarter;

    ## apply_to_period_id
    my $apydt = DateTime->new( 'year' => $row->[7], 'month' => $row->[8] );
    push @{$row}, Orchard::Period->getIdFromDateTime($apydt);
    $row->[8] = $apydt->quarter;

    ## category
    my $categoryId = Orchard::ManualAdjustmentCategory->getCategoryIdByName( $row->[9] );
    my $category   = Orchard::NameCase->underscore( lc( $row->[9] ) );
    $row->[9] = $category;

    ## remove currency text
    splice @{$row}, 4, 1;

    ## add currenices_id
    push @{$row}, 1;

    ## add amount_in_original_currency
    push @{$row}, $row->[3];

    ## date_added
    my $dateAdded = DateTime->now->datetime(' ');
    push @{$row}, $dateAdded;

    ## remove UPC
    splice @{$row}, 2, 1;

    ## add category_id
    push @{$row}, $categoryId;

    push @rows, [ $max++, @{$row} ];
}

my $values;
foreach my $row (@rows) {
    my $text = '(' . ( join ',', map { is_number($_) ? $_ : $dbh->quote($_) } @{$row} ) . ')';
    push @{$values}, $text;
}

my $filename = OUTDIR . '/' . $jira . '_batch_manual_adjustments_' . $opt->git_version . '.sql';
my $table    = '`art_relations`.`ROLLBACK-DATA-' . $jira . '`';
my $top      = printTOP($table);
$top .= join ",\n", @{$values};
$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 int(11) NOT NULL AUTO_INCREMENT,
  parent_type enum('vendor','oms_client','publisher','partner') DEFAULT NULL,
  parent_id int(11) NOT NULL,
  amount decimal(18,6) DEFAULT NULL,
  adjust_for_year smallint(6) DEFAULT NULL,
  adjust_for_quarter tinyint(4) DEFAULT NULL,
  apply_to_year smallint(6) DEFAULT NULL,
  apply_to_quarter tinyint(4) DEFAULT NULL,
  category enum('label_earnings','publisher_earnings','legal_fees','label_publisher_costs','admin_fees','reclass_between_labels','returned_check','stale_dated_check') DEFAULT NULL,
  comment mediumtext,
  adjust_for_period_id smallint(5) unsigned DEFAULT NULL,
  apply_to_period_id smallint(5) unsigned DEFAULT NULL,
  currencies_id smallint(5) NULL,
  amount_in_original_currency decimal(18,6) DEFAULT NULL,
  date_added DATETIME NULL,
  category_id int NULL,
  PRIMARY KEY (id)
);


INSERT INTO $table (
  id, parent_type,parent_id,amount,adjust_for_year,adjust_for_quarter,apply_to_year,
  apply_to_quarter, category,comment,adjust_for_period_id,apply_to_period_id,
  currencies_id, amount_in_original_currency, date_added, category_id
) 
VALUES
!
    $sql;
}

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


INSERT INTO `art_relations`.`manual_adjustment` (
  id, parent_type,parent_id,amount,adjust_for_year,adjust_for_quarter,apply_to_year,
  apply_to_quarter, category,comment,adjust_for_period_id,apply_to_period_id,
  currencies_id, amount_in_original_currency, date_added, category_id
) SELECT * FROM $table;

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

!
    $sql;
}