#!/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/);

use lib '/var/app/orchard/collab/jgetter/perllib';
use Orchard::Accounting::JIRA;
use Orchard::ETL::Extract::SpreadSheet;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'git_version|g=i' => 'git tree version <ACC>-<1234>-<VERSION> default 1', { 'default' => 1 } ],
    [ 'help|?|h'        => "print usage message and exit" ],
    [ 'file|f=s'        => "file to read UPCs",          { 'required' => 1 } ],
    [ 'vendor_id|v=i'   => 'Label/Vendor ID - required', { 'required' => 1 } ],
    [ 'percent|p=i'     => 'Split percentage enter whole will convert decimal' ],
);

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

my $jira     = Orchard::Accounting::JIRA->new( $jira_ticket, $opt->git_version );
my $branch   = $jira->branch;
my $filename = $jira->getfilepath("bulk_load_product_split");
my $vendorId = $opt->vendor_id;

my $global_percent = 0;
if ( defined $opt->percent ) {
    $global_percent = sprintf( "%.2f", ( $opt->percent / 100 ) );
}

my $sheet = Orchard::ETL::Extract::SpreadSheet->new( $opt->file, 1 );
my $rows  = $sheet->getrows();

my $upcs;
my $inserts;

foreach my $row ( @{$rows} ) {
    my $upc     = $sheet->getcol( $row, 'D' );
    my $percent = $sheet->getcol( $row, 'E' );

    $percent = defined $percent ? $percent : $global_percent;
    if ( not defined $percent ) {
        logMessage( 'fatal', "Percent is required" );
        exit 1;
    }

    push @{$inserts}, "(NULL,$vendorId,$upc,$percent,NULL)";
	push @{$upcs}, $upc;

}

$upcs = join ",", @{$upcs};
$inserts = join ",\n", @{$inserts};

my $table = "`ROLLBACK-DATA-${branch}`";

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

-- changeset jgetter:1

CREATE TABLE $table AS
SELECT ai.vendor_id,r.upc
FROM art_relations.releases r
JOIN art_relations.artist_info ai ON r.artist_id=ai.artist_id
WHERE ai.vendor_id = $vendorId 
AND r.upc IN (
$upcs
)
;

INSERT INTO product_split VALUES
$inserts
;

--rollback DELETE ps.* FROM product_split ps JOIN $table rb WHERE rb.upc=ps.product_id AND rb.vendor_id=ps.vendor_id;

!

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