#!/usr/bin/perl

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

use JSON::XS;
use FileHandle;
use Sort::Naturally;
use File::Find::Rule;
use Encode 'encode_utf8';
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::Accounting::Country;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'help|?|h'     => "print usage message and exit" ],
    [ 'accounts|a=s' => 'Account file CSV list' ],
    [ 'outdir|o=s'   => 'Outdir files are in', { 'default' => 'output' } ]
);

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

my $dbuser = "'jgtest'";

## connect to royalty_accounting
my $dbx = Orchard::Session::getdbx('abacusqa');
my $dbh = $dbx->getdbh;
my $rcn = $dbx->resultset('RoyaltyAccounting::RunController')->search( { 'run_controller_name' => 'Orchard Legacy OA' } )->first;
if ( not defined $rcn ) {
    logMessage( 'info', "Adding run controller 'Orchard Legacy OA' to QA" );
    $rcn = $dbx->resultset('RoyaltyAccounting::RunController')->create( {
            'run_controller_id'   => 1,
            'run_controller_name' => 'Orchard Legacy OA',
            'contract_type'       => 'distribution',
            'created_by'          => $dbuser,
            'created_at'          => Orchard::Session::mysql_now,
            'last_modified_by'    => $dbuser,
            'last_modified'       => Orchard::Session::mysql_now,
            'deleted_at'          => undef,
            'deleted_by'          => undef
        }
    );
}

my $maxc = $dbx->resultset('RoyaltyAccounting::Contract');
my $res  = $maxc->search(
    undef,
    {
        columns => [ { maxContractId => { max => "contract_id" } }, ]
    }
);
my $max_id = $res->get_column('maxContractId')->first + 1;

my $ttrows = [ $dbx->resultset('RoyaltyAccounting::ReferenceTransactionType')->all ];
my $ttypes;
map { $ttypes->{ $_->transaction_type_code } = $_->reference_transaction_type_id } @{$ttrows};

my $stores;
my $stfh = FileHandle->new( 'stores.csv', 'r' );
while (<$stfh>) {
    chomp;
    my ( $id, $name ) = csv_parse($_);
    $stores->{$name} = $id;
}
$stfh->close;

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

-- changeset jgetter:1

!

my $rollbacks = {
    'accounts'  => [],
    'contracts' => [],
};

my $account_filter = defined $opt->accounts ? [ csv_parse( $opt->accounts ) ] : undef;

my $files = [ File::Find::Rule->file->name("*.json")->in( $opt->outdir ) ];
foreach my $file ( nsort @{$files} ) {
    my $skip = 0;
    if ( defined $account_filter ) {
        my $name = File::Basename::basename( $file, '.json' );
        if ( not grep /^$name$/, @{$account_filter} ) {
            $skip = 1;
        }
    }
    next if $skip;

    my $fh = FileHandle->new( $file, 'r' );
    my $json;
    read $fh, $json, -s $fh;
    $fh->close;

    my $href = JSON::XS->new->utf8->decode( encode_utf8($json) );

    my $aid = $href->{'account_id'};
    my $cid = $href->{'contract_id'};
    if ( $cid < 0 ) {
        $cid = $max_id++;
        $href->{'contract_id'} = $cid;
    }

    my $name          = $dbh->quote( $href->{'account_name'} );
    my $contract_name = $dbh->quote("$href->{'account_name'} [$aid]");

    push @{ $rollbacks->{'accounts'} },  $aid;
    push @{ $rollbacks->{'contracts'} }, $cid;

    logMessage( 'info', "Processing $aid: $href->{'account_name'}" );

    ## if not exist account
    my $acct = getrow( $dbx, 'Account', { 'account_id' => $aid } );
    if ( defined $acct ) {
        logMessage( 'info1', "$aid is already in abacus - adding contracts..." );
    } else {
        $sql .= ins_acct( $href, $name, $dbuser );
    }

    ## if not exist account_payment_term
    my $acct_pay_term = getrow( $dbx, 'AccountPaymentTerm', { 'account_id' => $aid } );
    $sql .= ins_acct_payment( $href, $dbuser ) if not defined $acct_pay_term;

    ## if not exist account_payee
    my $acct_payee = getrow( $dbx, 'AccountPayee', { 'account_id' => $aid } );
    $sql .= ins_acct_payee( $href, $dbuser ) if not defined $acct_payee;

    ## if not exist account_tax_info
    my $acct_tax_info = getrow( $dbx, 'AccountTaxInfo', { 'account_id' => $aid } );
    $sql .= ins_acct_tax_info( $href, $dbuser ) if not defined $acct_tax_info;

    ## if not exist contract
    my $contract = getrow( $dbx, 'Contract', { 'contract_id' => $cid } );
    $sql .= ins_contract( $href, $contract_name, $dbuser ) if not defined $contract;

    ## reserves
    if ( defined $href->{'reserves'} and values %{ $href->{'reserves'} } ) {
        my $reserves = getrow( $dbx, 'ContractReserve', { 'contract_id' => $cid } );
        $sql .= ins_contract_reserve( $href, $dbuser );
    }

    ## if not exist run controller contract
    my $rcc = getrow( $dbx, 'RunControllerContract', { 'contract_id' => $cid } );
    $sql .= ins_rcc( $href, $rcn ) if not defined $rcc;

    ## if not exist legacy contract
    my $legacy_contract = getrow( $dbx, 'LegacyContract', { 'contract_id' => $cid } );
    $sql .= ins_legacy_contract($href) if not defined $legacy_contract;

    ## if not exist account_contract
    my $acct_contract = getrow( $dbx, 'AccountContract', { 'account_id' => $aid, 'contract_id' => $cid } );
    $sql .= ins_acct_contract( $href, $dbuser ) if not defined $acct_contract;

    my $contract_terms = $href->{'contract_terms'};
    foreach my $term ( @{$contract_terms} ) {
        my $ctrow = $term->{'contract_term'};
        my $name  = $dbh->quote("$href->{'account_name'} - $ctrow->{'term_type'}");
        $sql .= ins_contract_term( $cid, $name, $ctrow, $dbuser );

        foreach my $ctcrow ( @{ $ctrow->{'term_conditions'} } ) {
            $sql .= ins_contract_term_cond( $ctcrow, $dbuser );
        }
    }

    ## if not exist contract reserve
    if ( defined $href->{'reserve_installments_in_months'} ) {
        my $contract_reserve = getrow( $dbx, 'ContractReserve', { 'contract_id' => $cid } );
        $sql .= ins_contract_reserve( $href, $dbuser ) if not defined $contract_reserve;
    }

    ## if not exist mechanical deduction
    if ( defined $href->{'mech_type'} ) {
        my $mech_deduction = getrow( $dbx, 'MechanicalDeduction', { 'contract_id' => $cid } );
        $sql .= ins_mechanical( $href, $dbuser ) if not defined $mech_deduction;
    }

}

## rollbacks
if ( scalar @{ $rollbacks->{'accounts'} } > 0 ) {

    my $in_contracts = join ',', @{ $rollbacks->{'contracts'} };
    my $in_accounts  = join ',', @{ $rollbacks->{'accounts'} };

    $sql .= <<"!";
--rollback DELETE FROM contract WHERE contract_id IN ($in_contracts);
--rollback DELETE FROM account WHERE account_id IN ($in_accounts);

!

}

print $sql;

sub getrow {
    my $dbx = shift;
    my $obj = shift;
    my $sel = shift;

    $obj = 'RoyaltyAccounting::' . $obj;
    $dbx->resultset($obj)->search($sel)->first;

}

sub ins_acct {
    my $href   = shift;
    my $name   = shift;
    my $dbuser = shift;
    my $sql    = <<"!";
INSERT INTO account VALUES ($href->{'account_id'},$name,NULL,$dbuser,NOW(),$dbuser,NOW());
	
!
    $sql;
}

sub ins_acct_payment {
    my $href   = shift;
    my $dbuser = shift;
    my $sched  = Orchard::NameCase->underscore( $href->{'payment_schedule'} );
    my $sql    = <<"!";
INSERT INTO account_payment_term (
    account_id,currency_code,payment_minimum,payment_schedule,created_by,created_at,last_modified_by,last_modified
) VALUES (
    $href->{'account_id'},'$href->{'account_currency'}','$href->{'payment_minimum'}','$sched',$dbuser,NOW(),$dbuser,NOW()
)
;
	
!
    $sql;
}

sub ins_acct_contract {
    my $href   = shift;
    my $dbuser = shift;
    my $sql    = <<"!";
INSERT INTO account_contract (account_id,contract_id) VALUES ($href->{'account_id'},$href->{'contract_id'})
;
	
!
    $sql;

}

sub ins_contract {
    my $href   = shift;
    my $name   = shift;
    my $dbuser = shift;

    my $end = ( defined $href->{'term_end'} ) ? "'$href->{'term_end'}'" : 'NULL';
    my $sql = <<"!";
INSERT INTO contract VALUES (
$href->{'contract_id'},1,1,$name,'distribution',NULL,NULL,NULL,1,NULL,NULL,'$href->{'term_start'}',$end,$dbuser,NOW(),$dbuser,NOW()
)
;
	
!
    $sql;

}

sub ins_contract_term {
    my $cid    = shift;
    my $name   = shift;
    my $href   = shift;
    my $dbuser = shift;
    my $atts   = JSON::XS::encode_json( $href->{'attachments'} );
    my $type   = $href->{'term_type'};

    my $atts_rels = 'NULL';
    if ( scalar keys %{ $href->{'attachments_relations'} } > 0 ) {
        $atts_rels = "'" . JSON::XS::encode_json( $href->{'attachments_relations'} ) . "'";
    }

    my $base = grep { 'base label' eq $_ } map { $_->{'contract_term_condition_name'} } @{ $href->{'term_conditions'} };
    my $sql  = <<"!";
INSERT INTO contract_term VALUES (NULL,$cid,$name,'$type','$atts',$atts_rels,$base,$dbuser,NOW(),$dbuser,NOW(),NULL,NULL)
;

SET \@contract_term_id=LAST_INSERT_ID()
;

!
    $sql;
}

sub ins_contract_term_cond {
    my $href   = shift;
    my $dbuser = shift;

    my $_countries = '"countries":' . JSON::XS::encode_json( map_list( $href->{'conditions'}->{'countries'}, 'country' ) );
    my $_stores    = '"stores":' . JSON::XS::encode_json( map_list( $href->{'conditions'}->{'stores'}, 'stores' ) );
    my $_types     = '"transaction_types":' . JSON::XS::encode_json( map_list( $href->{'conditions'}->{'transaction_types'}, 'ttypes' ) );

    my $conditions = "'{" . ( join ",", ( $_stores, $_countries, $_types ) ) . "}'";
    my $rate       = $href->{'term_rate'};
    my $comm       = 100 - $rate;
    my $sql        = <<"!";
INSERT INTO contract_term_condition VALUES (NULL,\@contract_term_id,'$href->{'contract_term_condition_name'}',$conditions,$rate,$comm,1,$dbuser,NOW(),$dbuser,NOW(),NULL,NULL)
;

!
    $sql;

}

sub ins_rcc {
    my $href = shift;
    my $rcn  = shift;
    my $rcid = $rcn->id;

    my $sql = <<"!";
INSERT INTO run_controller_contract VALUES (NULL,$rcid,$href->{'contract_id'})
;

!
    $sql;
}

sub ins_acct_payee {
    my $href   = shift;
    my $dbuser = shift;

    my $sql = <<"!";
INSERT INTO account_payee VALUES (NULL,$href->{'account_id'},100176930,NULL,NULL,NULL,NULL,NULL,NULL,$dbuser,NOW(),$dbuser,NOW())
;

!
    $sql;
}

sub ins_acct_tax_info {
    my $href   = shift;
    my $dbuser = shift;

    my $sql = <<"!";
INSERT INTO account_tax_info VALUES (NULL,$href->{'account_id'},'TBD',0,0,$dbuser,NOW(),$dbuser,NOW())
;

!

    $sql;

}

sub ins_legacy_contract {
    my $href = shift;
    my $sql  = <<"!";
INSERT INTO legacy_contract VALUES (NULL,$href->{'contract_id'},$href->{'contract_id'})
;

!
    $sql;

}

sub ins_contract_reserve {
    my $href   = shift;
    my $dbuser = shift;

    my $cid         = $href->{'contract_id'};
    my $res         = $href->{'reserves'};
    my $rate        = $res->{'reserve_rate'};
    my $offset      = $res->{'reserve_release_offset_in_months'};
    my $installment = $res->{'reserve_installments_in_months'};
    my $schedule    = JSON::XS::encode_json( $res->{'reserve_release_schedule'} );
    my $ttypes      = map_list( $res->{'reserve_conditions'}->{'transaction_types'}, 'ttypes' );
    my $conds       = JSON::XS::encode_json( { 'transction_types' => $ttypes } );

    my $sql = <<"!";
INSERT INTO contract_reserve VALUES (NULL,$cid,$rate,$offset,$installment,'$schedule','$conds',$dbuser,NOW(),$dbuser,NOW(),NULL,NULL)
;

!
    $sql;

}

sub ins_mechanical {
    my $href   = shift;
    my $dbuser = shift;

    my $cid    = $href->{'contract_id'};
    my $fee    = $href->{'mech_admin_fee'};
    my $type   = ( defined $href->{'mech_type'} and $href->{'mech_type'} eq 'payee' ) ? 'label' : $href->{'mech_type'};
    my $setval = $href->{'mech_type_set_value'};

    my $sql = <<"!";
INSERT INTO mechanical_deduction VALUES (NULL,$cid,$setval,'$type',$fee,$dbuser,NOW(),$dbuser,NOW())
;

!
    $sql;

}

sub map_list {
    my $list = shift;
    my $type = shift;

    my $aref = [];
    if ( defined $list and scalar @{$list} > 0 ) {
        if ( $type eq 'country' ) {
            foreach my $c ( @{$list} ) {
                my $cid = get_country_id($c);
                if ( defined $cid ) {
                    push @{$aref}, $cid;
                } else {
                    logMessage( 'error1', "$c not in country list" );
                }
            }
        } elsif ( $type eq 'ttypes' ) {
            map { push @{$aref}, $ttypes->{$_} } @{$list};
        } else {
            foreach my $s ( @{$list} ) {
                my $sid = $stores->{$s};
                if ( defined $sid ) {
                    push @{$aref}, $sid;
                } else {
                    logMessage( 'error1', "$s not in store list" );
                }
            }
        }
    }
    $aref = [ sort { $a <=> $b } @{$aref} ] if defined $aref and scalar @{$aref};
    $aref;
}