#!/usr/bin/perl

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

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::NameCase;
use Orchard::DB::Connect;
use Orchard::ETL::Extract::CSV;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'help|?|h'    => "print usage message and exit" ],
    [ 'account|a=i' => 'Account ID to test' ],
    [ 'file|f=s'    => 'File to parse', { 'required' => 1 } ],
    [ 'jira|j=s'    => 'JIRA tickets',  { 'required' => 1 } ],
    [ 'nocreate|n'  => 'No create contract just terms and conditions' ],
    [ 'verbose|v'   => 'Verbose' ],
);

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

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

## dbh
my $dsn = Orchard::DB::Connect->dsn('abacusqa');
my $dbh = DBI->connect( @{ $dsn->{'conn'} } ) || die "Conn: $DBI::errstr";

## countries
my $cns;
my $ww;
my $afh = FileHandle->new('countries.csv');
while (<$afh>) {
    chomp;
    my ( $name, $code ) = csv_parse($_);
    $cns->{$code} = $code;
    $cns->{'WW'}->{$code}++;
}
$afh->close;

## company codes
my $company_codes = {
    '4914' => '1',
    '4915' => '2',
    '4916' => '3',
    '2820' => '4',
    '2821' => '5',
    '4917' => '6',
    '4918' => '7',
    '4919' => '8',
    '4920' => '9'
};

## get all contracts
my $contracts = {};
my $csv       = Orchard::ETL::Extract::CSV->new( $opt->file );
while ( my $row = $csv->getrow() ) {

    my $acctid = $csv->getcol( $row, 'A' );
    next if defined $opt->account and $opt->account != $acctid;

    my $name = $csv->getcol( $row, 'C' );
    my $cid  = md5_hex($name);

    ## set or get contract
    if ( not exists $contracts->{$cid} ) {

        ## contracts require a term start
        my $term_start = $csv->getcol( $row, 'M' );
        next if not defined $term_start;

        my ( $dd, $mm, $yyyy ) = $term_start =~ m!(..)/(..)/(....)!;
        my $dt = DateTime->new( 'year' => $yyyy, 'month' => $mm, 'day' => $dd );

        $contracts->{$cid} = {
            'account_id'                     => $acctid,
            'contract_name'                  => $name,
            'contract_type'                  => 'neighbouring_rights',
            'is_primary_for_calc'            => ( $csv->getcol( $row, 'D' ) =~ m/yes/i ) ? 1 : 0,
            'term_start'                     => $dt->ymd,
            'reference_sap_profit_center_id' => $company_codes->{ $csv->getcol( $row, 'N' ) },
        };
    }
    my $contract = $contracts->{$cid};

    ## set or get contract term
    my $term_name = $csv->getcol( $row, 'G' );
    my $ctid      = md5_hex($term_name);
    if ( not exists $contract->{'terms'}->{$ctid} ) {
        $contract->{'terms'}->{$ctid} = {
            'contract_term_name'    => $csv->getcol( $row, 'G' ),
            'attachments_relations' => [ $csv->getcol( $row, 'F' ) ],
            'is_base_term'          => ( $csv->getcol( $row, 'H' ) =~ m/yes/i ) ? 1 : 0,
        };
    }
    my $contract_term = $contract->{'terms'}->{$ctid};

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

    my $in_territory = $csv->getcol( $row, 'J' );
    my $ex_territory = $csv->getcol( $row, 'K' );

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

    my $included = {};
    foreach my $inter ( split ',', $in_territory ) {
        $inter =~ s/ *//g;
        if ( $inter =~ m/ww/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, 'I' ),
        'term_rate'                    => $term_rate,
        'priority'                     => 1,
        'conditions'                   => $conditions
      };
    my $x;

}
$csv->fh->close;

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

    my $ct     = $contracts->{$cid};
    my $c_name = $dbh->quote( $ct->{'contract_name'} );

    my $sql = <<"!";
-- contract $c_name
INSERT INTO contract (
reference_sap_profit_center_id,contract_name,contract_type,term_start,
created_by,created_at,last_modified_by,last_modified
) VALUES (
$ct->{'reference_sap_profit_center_id'},$c_name,'$ct->{'contract_type'}','$ct->{'term_start'}',
'$opt->{'jira'}',NOW(),'$opt->{'jira'}',NOW()
);

SET \@contract_id=LAST_INSERT_ID();

-- account contract
INSERT INTO account_contract (account_id,contract_id,is_primary_for_calc) VALUES ($ct->{'account_id'},\@contract_id,$ct->{'is_primary_for_calc'});

-- run controller contract
INSERT INTO run_controller_contract (run_controller_id,contract_id) VALUES (6,\@contract_id);

!

    foreach my $ctid ( keys %{ $ct->{'terms'} } ) {
        my $cterm   = $ct->{'terms'}->{$ctid};
        my $arels   = JSON::XS::encode_json( { 'contributor_ids' => $cterm->{'attachments_relations'} } );
        my $ct_name = $dbh->quote( $cterm->{'contract_term_name'} );
        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 (
\@contract_id,$ct_name,'contribution','[]','$arels',$cterm->{'is_base_term'},
'$opt->{'jira'}',NOW(),'$opt->{'jira'}',NOW()
);

SET \@term_id=LAST_INSERT_ID();

!
        $sql .= $ctsql;
        foreach my $cond ( @{ $cterm->{'term_conditions'} } ) {
            my $jcond    = JSON::XS::encode_json( $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,priority,
created_by,created_at,last_modified_by,last_modified
) VALUES (
\@term_id,$ctc_name,'$jcond',$cond->{'term_rate'},1,
'$opt->{'jira'}',NOW(),'$opt->{'jira'}',NOW()
);

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

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

-- changeset jgetter:1
$body

--rollback DELETE FROM account_contract WHERE account_id IN (
--rollback 75789,75790,75791,75792,75793,75794,75795,75796,75797,75798,75799,75800,75801,75802,75803,75804,75805,75806,75807,75808,
--rollback 75809,75810,75811,75812,75814,75815,75816,75817,75818,75819,75820,75821,75822,75823,75824,75825,75826,75827,75828,75829,
--rollback 75830,75831,75832,75833,75834,75835,75837,75838,75839,75840,75841,75842,75843,75844,75845,75846,75847,75848,75849,75850,
--rollback 75851,75852,75853,75854,75855,75856,75857,75858,75859,75860,75862,75863,75864,75865,75866,75867,75869,75870,75871,75872,
--rollback 75873,75874,75875,75876,75877,75878,75879,75880,75881,75882,75883,75884,75885,75886,75887,75888,75889,75890,75891,75892,
--rollback 75893,75894,75896,75897,75898,75899,75900,75901,75902,75903,75904,75905,75906,75907,75908,75909,75910,75912,75913,75914,
--rollback 75916,75917,75918,75919,75920,75921,75922,75923,75924,75925,75926,75927,75928,75929,75930,75931,75932,75933,75934,75935,
--rollback 75936,75937,75938,75939,75941,75942,75943,75944,75945,75946,75947,75948,75950,75951,75952,75953,75954,75955,75956,75957,
--rollback 75958,75959,75960,75961,75962,75964,75965,75966,75967,75968,75969,75970,75971,75972,75973,75974,75975,75976,75977,75978,
--rollback 75979,75980,75981,75982,75983,75984,75985,75986,75987,75988,75989,75991,75992,75993,75994,75995,75996,75997,75999,76000,
--rollback 76001,76002,76003,76004,76005,76006,76007,76009,76010,76011,76013,76014,76015,76016,76017,76018,76019,76020,76022,76023,
--rollback 76024,76025,76026,76027,76028,76029,76030,76031,76032,76033,76034,76035,76036,76037,76038,76039,76041,76042,76043,76044,
--rollback 76045,76046,76047,76048,76049,76050,76052,76054,76055,76056,76057,76058,76060,76061,76062,76063,76064,76065,76068,76069,
--rollback 76070,76071,76073,76074,76075,76077,76078,76079,76080,76081,76082,76083,76084,76085,76087);
!

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