#!/usr/bin/perl

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

use JSON::XS;
use FileHandle;
use File::Find::Rule;
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;

## connect to royalty_accounting
my $dbx = Orchard::Session::getdbx('abacusqa');
my $rcn = $dbx->resultset('RoyaltyAccounting::RunController')->search( { 'run_controller_name' => 'Orchard Legacy OA' } )->first;
if ( not defined $rcn ) {
    logMessage( 'fatal', "All contracts that will be created need a run controller - go insert one in the database" );
    #exit 1;
}

## connect to and set snowflake session to prod royalty accounting
my $dbh = Orchard::Session::getdbh('snowflake');
$dbh = Orchard::DB::Connect->snowflake_session( $dbh, { 'schema' => 'prod' } );

my $dbuser = "'jgtest'";

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

-- changeset jgetter:1

!

my $file = 'snowflake-view-query.sql';
logMessage( 'info', "Getting all vendors from $file" );
my $sth = Orchard::DB::Connect->prepare_file( $dbh, $file );
$sth->execute();

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

if ( $sth->rows() ) {
    while ( my $row = $sth->fetchrow_hashref('NAME_lc') ) {

        my $aid  = $row->{'account_id'};
        my $cid  = $row->{'contract_id'};
        my $name = $dbh->quote( $row->{'account_name'} );

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

        logMessage( 'info', "Processing $aid: $row->{'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( $row, $name, $dbuser );
        }

        ## if not exist account_payment_term
        my $acct_pay_term = getrow( $dbx, 'AccountPaymentTerm', { 'account_id' => $aid } );
        $sql .= ins_acct_payment( $row, $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( $row, $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( $row, $dbuser ) if not defined $acct_tax_info;

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

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

        ## if not exist legacy contract
        my $legacy_contract = getrow( $dbx, 'LegacyContract', { 'contract_id' => $cid } );
        $sql .= ins_legacy_contract($row) 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( $row, $dbuser ) if not defined $acct_contract;

        my $contract_terms = JSON::XS::decode_json( $row->{'contract_terms'} );
        foreach my $term ( @{$contract_terms} ) {
            my $ctrow = $term->{'contract_term'};
            my $name  = $dbh->quote("$row->{'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 $row->{'reserve_installments_in_months'} ) {
            my $contract_reserve = getrow( $dbx, 'ContractReserve', { 'contract_id' => $cid } );
            $sql .= ins_contract_reserve( $row, $dbuser ) if not defined $contract_reserve;
        }

        ## if not exist mechanical deduction
        if ( defined $row->{'mech_type'} ) {
            my $mech_deduction = getrow( $dbx, 'MechanicalDeduction', { 'contract_id' => $cid } );
            $sql .= ins_mechanical( $row, $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 $row    = shift;
    my $name   = shift;
    my $dbuser = shift;
    my $sql    = <<"!";
INSERT INTO account VALUES ($row->{'account_id'},$name,NULL,$dbuser,NOW(),$dbuser,NOW());
	
!
    $sql;
}

sub ins_acct_payment {
    my $row    = shift;
    my $dbuser = shift;
    my $sched  = Orchard::NameCase->underscore( $row->{'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 (
    $row->{'account_id'},'$row->{'account_currency'}','$row->{'payment_minimum'}','$sched',$dbuser,NOW(),$dbuser,NOW()
)
;
	
!
    $sql;
}

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

}

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

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

}

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

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

    my $base = grep { 'base label' eq $_ } map { $_->{'name'} } @{ $row->{'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 $row    = shift;
    my $dbuser = shift;

    my $countries = '"countries":' . JSON::XS::encode_json( $row->{'conditions'}->{'countries'} );
    my $stores    = '"stores":' . JSON::XS::encode_json( $row->{'conditions'}->{'stores'} );
    my $types     = '"transaction_types":' . JSON::XS::encode_json( $row->{'conditions'}->{'transaction_types'} );

    my $conditions = "'{" . ( join ",", ( $stores, $countries, $types ) ) . "}'";
    my $rate       = $row->{'term_rate'};
    my $comm       = 100 - $rate;
    my $sql        = <<"!";
INSERT INTO contract_term_condition VALUES (NULL,\@contract_term_id,'$row->{'name'}',$conditions,$rate,$comm,1,$dbuser,NOW(),$dbuser,NOW(),NULL,NULL)
;

!
    $sql;

}

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

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

!
    $sql;
}

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

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

!
    $sql;
}

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

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

!

    $sql;

}

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

!
    $sql;

}

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

    my $cid         = $row->{'contract_id'};
    my $rate        = $row->{'reserve_rate'};
    my $offset      = $row->{'reserve_release_offset_in_months'};
    my $installment = $row->{'reserve_installments_in_months'};
    my $schedule    = JSON::XS::encode_json( JSON::XS::decode_json( $row->{'reserve_release_schedule'} ) );
    my $conds       = JSON::XS::encode_json( JSON::XS::decode_json( $row->{'reserve_conditions'} ) );

    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 $row    = shift;
    my $dbuser = shift;

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

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

!
    $sql;

}