#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

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::Session;
use Orchard::RunCommand;
use Orchard::DB::Connect;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'help|?|h'   => 'print usage message and exit' ],
    [ 'file|f=s'   => 'Publishing calculation file to load', { 'required' => 1 } ],
    [ 'period|p=i' => 'Publishing period to load',           { 'required' => 1 } ],
);
if ( $opt->help ) {
    logMessage( 'info', $usage->text, 'options' );
    exit 1;
}

my $file = $opt->file;
Orchard::RunCommand::execute_log("wc -l $file");

my $s3path = 's3://qa-royalties-sales-files/temp/john';
my $s3file = $s3path . '/' . $opt->period . '.txt';

logMessage( 'info', "Copying $file to $s3path" );

if ( $file =~ m/gz$/ ) {
    logMessage( 'fatal', "$file must not be compressed" );
    exit 1;
} else {
    Orchard::RunCommand::execute_log("aws s3 cp $file $s3file");
    Orchard::RunCommand::execute_log("aws s3 ls $s3path/");
}

logMessage( 'info', "Loading $s3file into Snowflake" );

my $sfdbh = Orchard::Session::getdbh('snowflake');

my $warehouse = "qa_abacus_wh";
my $role      = "dev_engineering";
my $database  = "royalty_accounting";
my $schema    = "qa";

$sfdbh->do("use warehouse $warehouse") or die "Error: $DBI::errstr\n\n";
$sfdbh->do("use role $role")           or die "Error: $DBI::errstr\n\n";
$sfdbh->do("use $database")            or die "Error: $DBI::errstr\n\n";
$sfdbh->do("use schema $schema")       or die "Error: $DBI::errstr\n\n";

my $sfsth = sfsth( $sfdbh, $opt->period );
eval {
    $sfsth->execute();
    my $href = $sfsth->fetchrow_hashref;
    logMessage( 'info', "Parsed:" . $href->{'rows_parsed'} );
    logMessage( 'info', "Loaded:" . $href->{'rows_loaded'} );
    logMessage( 'info', "Status:" . $href->{'status'} );
    logMessage( 'info', "Compressing $file" );
    Orchard::RunCommand::execute_log("pigz --best $file");
};
if ($@) {
    logMessage( 'error', $@ );
    exit 1;
}

sub sfsth {
    my $dbh    = shift;
    my $period = shift;
    my $sql    = <<"!";
COPY INTO royalty_accounting.qa.fact_sale_publishing (
    STATMENT_DETAIL_ID,
    PERIOD_ID,
    SONG_NO,
    SONG,
    WRITER,
    SOURCE_NAME,
    SOURCE_COUNTRY,
    SOURCE2_NAME,
    SOURCE2_COUNTRY,
    SOURCE3_NAME,
    SOURCE3_COUNTRY,
    SOURCE4_NAME,
    SOURCE4_COUNTRY,
    INCOME_TYPE,
    SH_ID,
    RPTG_PD,
    SALES_PD,
    PRODUCT_NUMBER,
    ARTIST_PRODUCT_NUMBER,
    UNITS,
    SONG_SHARE_PERCENT,
    CONTROL_PERCENT,
    AMOUNT,
    ROYALTY_PERCENT,
    ROYALTY_AMOUNT,
    DF,
    SOURCE_PRODUCT,
    ISWC,
    EXTERNAL_SONG_ID,
    ARTIST,
    SOURCE_SONG,
    ISRC,
    TRANSACTION_ID,
    ACCOUNT_ID,
    HAS_MULTIPLE_OWNERS,
    VENDOR_CONTROLLED_SHARE,
    ADJUSTED_SHARE,
    ADJUSTED_GROSS,
    FEE_PERCENT,
    ORCHARD_FEE,
    NET_REVENUE
) FROM (
    SELECT royalty_accounting.qa.sequence_fact_sale_publishing_txn.nextval,$period,
	\$1,\$2,\$3,\$4,\$5,\$6,\$7,\$8,\$9,\$10,\$11,\$12,\$13,\$14,\$15,\$16,\$17,\$18,\$19,\$20,
    \$21,\$22,\$23,\$24,\$25,\$26,\$27,\$28,\$29,\$30,\$31,\$32,\$33,\$34,\$35,\$36,\$37,\$38,\$39
    FROM \@ABACUS_TSV_FILE/temp/john/${period}.txt.gz
)
FILE_FORMAT = (
    SKIP_HEADER=1,
    FIELD_DELIMITER = "\t",
    FIELD_OPTIONALLY_ENCLOSED_BY = '"',
    TRIM_SPACE = TRUE,
    TYPE = CSV,
    COMPRESSION=GZIP
)
PATTERN = '.*[.](tsv|txt|gz)'
FORCE = TRUE;
!
    $dbh->prepare($sql);
}

__END__
   'error_limit' => 1
   'errors_seen' => 0
   'file' => 's3://qa-royalties-sales-files/temp/john/294.txt.gz'
   'first_error' => undef
   'first_error_character' => undef
   'first_error_column_name' => undef
   'first_error_line' => undef
   'rows_loaded' => 991924
   'rows_parsed' => 991924
   'status' => 'LOADED'