#!/usr/bin/perl

use constant 'SQLBY' => 'knr_sax_ingestion';

use strict;
use warnings;

use Git;
use JSON::XS;
use FileHandle;
use DateTime::Format::DateParse;
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::DB::Connect;
use Orchard::ETL::Extract::CSV;
use Orchard::ETL::Extract::SpreadSheet;

my $db = 'archivedb';

my $profitctr    = mkhref( 'TEMP-ProfitCenter.csv', 'ACCOUNT_ID' );
my $agreements   = mkhref( 'TEMP-Agreements.csv',   'AGREEMENT_ID' );
my $schedules    = mkhref( 'TEMP-Schedules.csv',    'AGREEMENT_ID-SCHEDULE_ID', 1 );
my $rightsblocks = mkhref( 'TEMP-RightsBlocks.csv', 'AGREEMENT_ID-SCHEDULE_ID-RIGHT_BLOCK' );

logMessage( 'info', "Connecting to $db" );
my $dsn = Orchard::DB::Connect->dsn($db);
my $dbh;
if ( defined $dsn->{'ssh'} ) {
    my $ssh = $dsn->{'ssh'};
    $ssh->{'net'}->spawn( $ssh->{'tunnel'} );
    sleep 1;
}
$dbh = DBI->connect( @{ $dsn->{'conn'} } ) || die "Conn: $DBI::errstr";
my $dbx = Orchard::Session::getdbx($db);

my $agcnt = scalar keys %{$agreements};

my $i = 1;
foreach my $agid ( sort keys %{$agreements} ) {
    ## next if $agid ne 'AGR676019';
    my $idx     = sprintf( "(%03d/%03d", $i++, $agcnt );
    my $agref   = $agreements->{$agid};
    my $acctid  = $agref->{'ACCOUNT_ID'};
    my $pcenter = defined $profitctr->{$acctid} ? $profitctr->{$acctid}->{'SAP_PROFIT_CENTER_ID'} : undef;

    my $ts = $agref->{'TERM_START'} eq '' ? '1970-02-01' : $agref->{'TERM_START'};
    my $te = $agref->{'TERM_END'} eq ''   ? undef        : $agref->{'TERM_END'};

    my $contract = {
        'reference_sap_profit_center_id' => $pcenter,
        'contract_name'                  => $agref->{'CONTRACT_NAME'},
        'contract_type'                  => $agref->{'CONTRACT_TYPE'},
        'sap_created_at'                 => undef,
        'term_start'                     => $ts,
        'term_end'                       => $te,
        'created_by'                     => SQLBY,
        'created_at'                     => mysql_now(),
        'last_modified_by'               => SQLBY,
        'last_modified'                  => mysql_now()
    };
    my $contract_name = $contract->{'contract_name'};
    my $_contract     = $dbx->resultset('RoyaltyAccounting::Contract')->search( { 'contract_name' => $contract_name } )->first;
    my $cid;

    if ( not defined $_contract ) {
        logMessage( 'info', "$idx Creating contract for agreement: $agid" );
        $_contract = $dbx->resultset('RoyaltyAccounting::Contract')->create($contract);
        $cid       = $_contract->contract_id;

        ## run controller
        my $rcdata = { 'contract_id' => $cid, 'run_controller_id' => 6 };
        my $rc     = $dbx->resultset('RoyaltyAccounting::RunControllerContract')->create($rcdata);

        ## account_contract
        my $acdata = { 'contract_id' => $cid, 'account_id' => $acctid, 'is_primary_for_calc' => $agref->{'IS_PRIMARY_FOR_CALC'} };
        my $ac     = $dbx->resultset('RoyaltyAccounting::AccountContract')->create($acdata);

        ## add agreement to external source connected to contract
        create_external_source( $dbx, 'contract', $cid, $agid, 'sax_agreement_id' );
    } else {
        logMessage( 'warn', "Contract already exists agreement: $agid" );
        $cid = $_contract->contract_id;
    }

    my $sids = [ split '\|', $agref->{'SCHEDULES'} ];
    foreach my $sid ( sort @{$sids} ) {
        my $skey = "$agid-$sid";
        foreach my $schref ( @{ $schedules->{$skey} } ) {
            my $contract_term = {
                'contract_id'           => $cid,
                'contract_term_name'    => $schref->{'CONTRACT_TERM_NAME'},
                'term_type'             => $schref->{'TERM_TYPE'},
                'attachments'           => $schref->{'ATTACHMENTS'},
                'attachments_relations' => $schref->{'ATTACHMENT_RELATIONS'},
                'is_base_term'          => $schref->{'IS_BASE_TERM'} eq 'TRUE' ? 1 : 0,
                'created_by'            => SQLBY,
                'created_at'            => mysql_now(),
                'last_modified_by'      => SQLBY,
                'last_modified'         => mysql_now()
            };
            my $contract_term_name = $contract_term->{'contract_term_name'};

            my $ct             = { 'contract_id' => $cid, 'contract_term_name' => $contract_term_name };
            my $_contract_term = $dbx->resultset('RoyaltyAccounting::ContractTerm')->search($ct)->first;
            my $ctid;
            if ( not defined $_contract_term ) {
                logMessage( 'info1', "Creating contract_term for agreement/schedule: $agid/$sid" );
                $_contract_term = $dbx->resultset('RoyaltyAccounting::ContractTerm')->create($contract_term);
                $ctid           = $_contract_term->contract_term_id;
                create_external_source( $dbx, 'contract_term', $ctid, $sid, 'sax_schedule_id' );
            } else {
                logMessage( 'warn1', "contract_term already exists for agreement/schedule: $agid/$sid" );
                $ctid = $_contract_term->contract_term_id;
            }

            my $bids = [ split '\|', $schref->{'BLOCKS'} ];

            my $p = 1;
            foreach my $bid ( sort @{$bids} ) {
                my $bkey   = "$agid-$sid-$bid";
                my $blkref = $rightsblocks->{$bkey};

                my $conditions = {
                    'contract_term_id'             => $ctid,
                    'contract_term_condition_name' => $blkref->{'RIGHT_BLOCK_DESC'},
                    'conditions'                   => $blkref->{'TERM_CONDITIONS'},
                    'term_rate'                    => $blkref->{'TERM_RATE'},
                    'priority'                     => $blkref->{'PRIORITY'},
                    'created_by'                   => SQLBY,
                    'created_at'                   => mysql_now(),
                    'last_modified_by'             => SQLBY,
                    'last_modified'                => mysql_now()
                };

                my $ctc_name = $conditions->{'contract_term_condition_name'};
                my $ctc      = { 'contract_term_id' => $ctid, 'contract_term_condition_name' => $ctc_name };

                my $_condition = $dbx->resultset('RoyaltyAccounting::ContractTermCondition')->search($ctc)->first;

                if ( not defined $_condition ) {
                    logMessage( 'info1', "Creating contract_term_condition for agreement/schedule/right: $agid/$sid/$bid" );
                    $_condition = $dbx->resultset('RoyaltyAccounting::ContractTermCondition')->create($conditions);
                    my $cnid = $_condition->contract_term_condition_id;
                    create_external_source( $dbx, 'contract_term_condition', $cnid, $bid, 'sax_right_id' );
                } else {
                    logMessage( 'warn1', "contract_term_condition already exists for agreement/schedule/right: $agid/$sid/$bid" );
                }

            }
        }
    }
}

sub create_external_source {
    my $dbx      = shift;
    my $ptab     = shift;
    my $ptab_id  = shift;
    my $exsrc_id = shift;
    my $exsrc    = shift;

    logMessage( 'info2', "Creating external reference for $exsrc:$ptab" );
    my $row = {
        'parent_table_id'    => $ptab_id,
        'parent_table_name'  => $ptab,
        'external_source_id' => $exsrc_id,
        'external_source'    => $exsrc,
        'created_by'         => SQLBY,
        'created_at'         => \'NOW()',
        'last_modified_by'   => SQLBY,
        'last_modified'      => \'NOW()',
    };
    my $_exsrc = $dbx->resultset('RoyaltyAccounting::ReferenceExternalSource')->create($row);
    $_exsrc;
}

sub mkhref {
    my $file = shift;
    my $key  = shift;
    my $set  = shift;

    logMessage( 'info', "Parsing $file..." );

    my $csv  = Orchard::ETL::Extract::CSV->new($file);
    my $keys = [ split '-', $key ];
    my $href;
    while ( my $row = $csv->getrow() ) {
        my $_keys;
        foreach my $k ( @{$keys} ) {
            push @{$_keys}, $csv->getkey( $row, $k );
        }
        my $id = join '-', @{$_keys};
        if ($set) {
            push @{ $href->{$id} }, $row;
        } else {
            $href->{$id} = $row;
        }
    }
    $href;

}