#!/usr/bin/perl

use constant 'OUTDIR' => 'royalty_accounting';

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

use Git;
use JSON::XS;
use DateTime;
use FileHandle;
use Digest::MD5 qw(md5 md5_hex md5_base64);
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::NameCase;
use Orchard::DB::Connect;
use Orchard::Accounting::JIRA;
use Orchard::ETL::Extract::CSV;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'help|?|h'        => "print usage message and exit" ],
    [ 'contract|c=s'    => 'Contract ID to test' ],
    [ 'file|f=s'        => 'Terms file to parse',                               { 'required' => 1 } ],
    [ 'git_version|v=i' => 'git tree version <ACC>-<1234>-<VERSION> default 1', { 'default'  => 1 } ],
);

if ( $opt->help ) {
    logMessage( 'info', $usage->text, 'options' );
    exit 1;
}

## only include these contracts - for testing
my $include_contracts;
map { $include_contracts->{$_}++ } split ',', $opt->contract if defined $opt->contract;

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

my $dbh = Orchard::Session::getdbh('orchqa');

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

## countries
my $ww;
my $cns;
my $rows = $dbh->selectcol_arrayref("SELECT iso3166a3 FROM country");
foreach my $code ( sort @{$rows} ) {
    $cns->{$code} = $code;
    $cns->{'WW'}->{$code}++;
}

my $snowflake_dbh = Orchard::Session::getdbh('snowflake');
my $contrib_sth   = get_contrib_sth($snowflake_dbh);

## get all contracts
my $contracts = {};

my $csv = Orchard::ETL::Extract::CSV->new( $opt->file );
while ( my $row = $csv->getrow() ) {
    next if defined $csv->getcol( $row, 'R' );

    my $cid = $csv->getcol( $row, 'D' );
    if ( not defined $cid ) {
        my $err;
        exit;
    }

    my $skip = 0;
    if ( defined $include_contracts ) {
        $skip = exists $include_contracts->{$cid} ? 0 : 1;
    }
    next if $skip;

    ## set/get contract
    my $contract;
    if ( not exists $contracts->{$cid} ) {
        my $primary    = $csv->getcol( $row, 'F' );
        my $is_primary = ( defined $primary and $primary =~ m/yes/i ) ? 1 : 0;
        $contracts->{$cid} = { 'terms' => {}, 'is_primary_for_calc' => $is_primary };
    }
    $contract = $contracts->{$cid};

    ## set/get contract term
    my $term_name   = $csv->getcol( $row, 'J' );
    my $contributor = $csv->getcol( $row, 'H' );
    my $ctid        = md5_hex("${term_name}-${contributor}");

    if ( not exists $contract->{'terms'}->{$ctid} ) {
        my $attrels = JSON::XS::encode_json( { 'contributor_ids' => [$contributor] } );
        my $atts    = [];

        ## only if column set to ALL with we add contributions
        ## contributions in separate attachements file
        if ( $csv->getcol( $row, 'I' ) =~ m/all/i ) {
            $contrib_sth->execute($contributor);
            if ( $contrib_sth->rows ) {
                while ( my $row = $contrib_sth->fetchrow_arrayref() ) {
                    push @{$atts}, $row->[0];
                }
            }
        }
        $atts = JSON::XS::encode_json($atts);

        ## contract term object
        $contract->{'terms'}->{$ctid} = {
            'contract_id'           => $cid,
            'contract_term_name'    => $csv->getcol( $row, 'J' ),
            'attachments'           => $atts,
            'attachments_relations' => $attrels,
            'is_base_term'          => ( $csv->getcol( $row, 'F' ) =~ m/yes/i ) ? 1 : 0,
        };
    }
    my $contract_term = $contract->{'terms'}->{$ctid};

    ## term_rate
    my $commission = $csv->getcol( $row, 'N' );
    $commission =~ s/%//;
    my $term_rate = 100 - $commission;

    my $in_territory = $csv->getcol( $row, 'L' );
    my $ex_territory = $csv->getcol( $row, 'M' );

    my $conditions = { 'stores' => [], 'countries' => [], 'transaction_types' => [] };

    my $included = {};
    foreach my $inter ( split ',', $in_territory ) {
        $inter =~ s/ *//g;
        if ( $inter =~ m/ww/i or $inter =~ m/world/i ) {
            map { $included->{$_}++ } sort keys %{ $cns->{'WW'} };
        } else {
            $included->{ $cns->{$inter} }++;
        }
    }
    if ( defined $ex_territory ) {
        foreach my $exter ( split ',', $ex_territory ) {
            $exter =~ s/  *//g;
            delete $included->{$exter};
        }
    }
    $conditions->{'countries'} = [ sort keys %{$included} ];

    push @{ $contract_term->{'term_conditions'} },
      {
        'contract_term_condition_name' => $csv->getcol( $row, 'K' ),
        'term_rate'                    => $term_rate,
        'commission'                   => $commission,
        'priority'                     => 1,
        'conditions'                   => JSON::XS::encode_json($conditions)
      };
}
$csv->fh->close;

if ( not scalar keys %{$contracts} ) {
    logMessage( 'fatal', "No contract terms to add" );
    exit 1;
}

my $body;
foreach my $cid ( keys %{$contracts} ) {

    my $ct = $contracts->{$cid};

    my $sql;

    foreach my $ctid ( keys %{ $ct->{'terms'} } ) {
        my $cterm   = $ct->{'terms'}->{$ctid};
        my $cid     = $cterm->{'contract_id'};
        my $ct_name = $dbh->quote( $cterm->{'contract_term_name'} );
        my $atts    = $dbh->quote( $cterm->{'attachments'} );
        my $arels   = $dbh->quote( $cterm->{'attachments_relations'} );
        my $ctsql   = <<"!";
-- contract term $ct_name
INSERT INTO contract_term (
  contract_id,
  contract_term_name,
  term_type,
  attachments,
  attachments_relations,
  is_base_term,
  created_by,
  created_at,
  last_modified_by,
  last_modified
) VALUES (
  $cid,
  $ct_name,
  'contribution',
  $atts,
  $arels,
  $cterm->{'is_base_term'},
  '$branch',
  NOW(),
  '$branch',
  NOW()
);

SET \@contract_term_id=LAST_INSERT_ID();

!
        $sql .= $ctsql;
        foreach my $cond ( @{ $cterm->{'term_conditions'} } ) {
            my $conds    = $cond->{'conditions'};
            my $ctc_name = $dbh->quote( $cond->{'contract_term_condition_name'} );
            my $ctc      = <<"!";
-- contract_term_condition $ctc_name
INSERT INTO contract_term_condition (
  contract_term_id,
  contract_term_condition_name,
  conditions,
  term_rate,
  commission,
  priority,
  created_by,
  created_at,
  last_modified_by,
  last_modified
) VALUES (
  \@contract_term_id,
  $ctc_name,
  '$conds',
  $cond->{'term_rate'},
  $cond->{'commission'},
  1,
  '$branch',
  NOW(),
  '$branch',
  NOW()
);

!
            $sql .= $ctc;
        }
    }
    $body .= "\n$sql\n";
}

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

-- changeset jgetter:1
$body
--rollback DELETE FROM contract_term WHERE created_by='${branch}';
!

## primary for calc
my $primes;
foreach my $cid ( keys %{$contracts} ) {
    if ( $contracts->{$cid}->{'is_primary_for_calc'} ) {
        push @{$primes}, $cid;
    }
}
if ( defined $primes ) {
    my $pids = join ",", sort @{$primes};

    $body .= <<"!";

-- changeset jgetter:2

UPDATE account_contract SET is_primary_for_calc=1 WHERE contract_id IN (
$pids
);

--rollback UPDATE account_contract SET is_primary_for_calc=0 WHERE contract_id IN (
--rollback $pids
--rollback );

!

}

$jira->printfile( $filename, $body );
$jira->showfile($filename);

sub get_contrib_sth {
    my $dbh = shift;
    my $sth = $dbh->prepare(
        qq{
with contribs as (
    select a.id contributor,a.name, c.id contribution
    from facts.prod.nr_contributor a
    join facts.prod.nr_contributor_is_main_performer_nr_contribution b on a.id=b.nr_contributor_id
    join facts.prod.nr_contribution c on c.id=b.nr_contribution_id
    union 
    select a.id contributor,a.name, c.id contribution
    from facts.prod.nr_contributor a
    join facts.prod.nr_contributor_is_featuring_performer_nr_contribution b on a.id=b.nr_contributor_id
    join facts.prod.nr_contribution c on c.id=b.nr_contribution_id
    union
    select a.id contributor,a.name, c.id contribution
    from facts.prod.nr_contributor a
    join facts.prod.nr_contributor_is_session_musician_nr_contribution b on a.id=b.nr_contributor_id
    join facts.prod.nr_contribution c on c.id=b.nr_contribution_id
)
select contribution 
from contribs where contributor=?
order by 1
}
    );
    $sth;
}