#!/usr/bin/perl

use constant 'OUTDIR' => '/var/app/orchard/database/art_relations/build/changelog/dml';

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

use Git;
use JSON::XS;
use FileHandle;
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::DB::Connect;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'file|f=s'        => 'TSV result file',              { 'required'  => 1 } ],
    [ 'git_version|g=i' => 'git tree version <ACC>-<1234>-<VERSION> default 1', { 'default'  => 1 } ],
    [ 'jira_id|j=i'     => 'JIRA ticket number',                                { 'required' => 1 } ],
    [ 'period|p=i'      => 'Period ID to adjust',                               { 'required' => 1 } ],
    [ 'vendor|v=i'      => 'Vendor ID to adjust' ],
    [ 'help|?|h'        => "print usage message and exit" ]
);

my $dir     = 'ACC' . $opt->jira_id;
my $ticket  = 'ACC-' . $opt->jira_id;
my $branch  = $ticket . '-' . $opt->git_version;
my $rbTable = '`art_relations`.`ROLLBACK-DATA-' . $dir . '`';

my $period = $opt->period;

my $vendorPredicate = defined $opt->vendor ? "=" . $opt->vendor : ">0";

my $columns = [ 'balance_forward', 'amount_payable', 'dig_amount_payable', 'dig_balance_forward' ];
my $list    = join ",", map { "'$_'" } @{$columns};

my $vfile = "/tmp/variance_${period}.csv";
my $fh    = FileHandle->new( $vfile, "w" );

my $header = csv_build( ( 'vendorId', 'currency', 'period', 'FX', 'USD' ) );

$fh->print("$header\n");


my $vids;
my $updates;
my $infile = FileHandle->new($opt->file);
while(<$infile>) {
	chomp;
	my $r = [ split /\t/, $_ ];
    push @{$vids}, $r->[0];
    my $update .= "UPDATE vendor_accounting SET amount = amount + $r->[4] WHERE vendor_id=$r->[0] AND period_id=$period\n";
    $update .= "AND entry_type IN ($list);\n\n";
    push @{$updates}, $update;
    my $line = csv_build( @{$r} );
    $fh->print("$line\n");
}
$fh->close;

print "$vfile created\n";

$vids = join ",", sort { $a <=> $b } @{$vids};
my $sql = <<"!";
-- liquibase formatted sql

-- changeset jgetter:1

DROP TABLE IF EXISTS $rbTable;

CREATE TABLE $rbTable AS 
SELECT 
	va.* 
FROM art_relations.vendor_accounting va
WHERE 1
    AND va.period_id=$period
    AND va.entry_type IN ($list)
    AND va.vendor_id IN ($vids);

!

map { $sql .= $_ } @{$updates};

$sql .= <<"!";

--rollback UPDATE vendor_accounting va INNER JOIN $rbTable rb USING(`id`) SET va.amount = rb.amount;
--rollback DROP TABLE $rbTable;
!

my $repo = Git->repository(OUTDIR);

my $currentBranch = $repo->command( 'rev-parse', '--abbrev-ref', 'HEAD' );
chomp($currentBranch);
if ( $currentBranch ne $branch ) {
    print "Need to create " . OUTDIR . " branch [$branch] - current branch is $currentBranch\n";
    exit;
}

my $outfile = OUTDIR . "/" . $ticket . "_fix_period_" . $period . "_fx_bals_" . $opt->git_version . ".sql";

print "Creating file $outfile\n";

my $ofh = FileHandle->new( $outfile, 'w' );
$ofh->print($sql);
$ofh->close;

map { print "$_\n", } $repo->command('status');

sub prepareSQL {
    my $dbh = shift;
    my $sql = (
        qq{
SELECT
	workstation.vendor_id,
	workstation.currency_id,
	workstation.period_id,
	IF(outstanding_balance,
		## if prior period outstanding balance should be set
		(outstanding_balance-(opening_balance+revenue+publishing+advanced_paid+expenses+adjustments+payment)),
		## if currenct period no outstanding balance so use
		## balance_forward + manual_adjustments + checkspaid entries
		((balance_forward+expenses+adjustments+payment)-
		(opening_balance+revenue+publishing+advanced_paid+expenses+adjustments+payment))
	) fx,
	-IF(outstanding_balance,
		(outstanding_balance-(opening_balance+revenue+publishing+advanced_paid+expenses+adjustments+payment)),
		((balance_forward+expenses+adjustments+payment)-
		(opening_balance+revenue+publishing+advanced_paid+expenses+adjustments+payment))
	)* xr.exchange_rate usd
FROM (
	SELECT
		rev.currency_id,
		rev.period_id,
		rev.vendor_id,

		IFNULL(ob.amount,0)/IFNULL(priorToUSD.exchange_rate,1) opening_balance,

		## advance is vendor_accounting.advance_payment 
		## converted to vendor currency
		IFNULL(adv.amount,0)/IFNULL(currToUSD.exchange_rate,1)*-1 advanced_paid,

		## revenue is vendor_accounting.dig_actual_net
		## converted to vendor currency
		(
			(
				## dig_actual_net + recoup
				IFNULL(va.amount,0)+IFNULL(rec.amount,0) -
				## dpd pub + oms fees + ringtone pub
				(IFNULL(dpd.amount,0)+IFNULL(oms.amount,0)+IFNULL(ring.amount,0))
			)
		)/IFNULL(currToUSD.exchange_rate,1) revenue,
		
		## publishing
		(IFNULL(dpd.amount,0)+IFNULL(oms.amount,0)+IFNULL(ring.amount,0)) /
		 IFNULL(currToUSD.exchange_rate,1) publishing,
		
		## expenses from release_manual_adjustments
		## converted to vendor currency
		
			IFNULL((
				SELECT
					SUM(rma.amount*IFNULL((
						## get the most current contract payout currency
						## get the exchange rate for that currency
						SELECT xr.exchange_rate
						FROM currency_exchange_rates xr
						WHERE 1
						AND xr.period_id=ma.apply_to_period_id
						AND xr.currency_from_id=rma.currencies_id
						AND xr.currency_to_id=rev.currency_id
					),1))
				FROM
					manual_adjustment ma
				JOIN
					release_manual_adjustment rma ON rma.vendor_manual_adjustment_id=ma.id
				WHERE 1
					AND ma.category_id=65
					AND ma.parent_id=rev.vendor_id	
					AND ma.apply_to_period_id=rev.period_id
				GROUP BY 
					ma.parent_id,ma.apply_to_period_id
			),0) expenses,

		## adjustments from manual_adjustments
		## converted to vendor currency
		
			IFNULL((
				SELECT
					ROUND(SUM(ma.amount_in_original_currency*IFNULL((		
						SELECT xr.exchange_rate
						FROM currency_exchange_rates xr
						WHERE 1
						AND xr.period_id=ma.apply_to_period_id
						AND xr.currency_from_id=ma.currencies_id
						AND xr.currency_to_id=rev.currency_id
						),1)),
					2) orig
				FROM
					manual_adjustment ma
				WHERE 1
					AND ma.category_id!=65
					AND ma.parent_id=rev.vendor_id
					AND ma.apply_to_period_id=rev.period_id
			),0) adjustments,

		## checks paid total
		## converted to vendor currency
	
			IF(
				## if vendor_accounting.total_checkspaid IS NULL get payment from 
				## checkspaid and vendor_checkspaid tables
				IFNULL(chk.amount,1)=1,
				IFNULL((
					## vendor checkspaid
					(
						SELECT IFNULL(SUM(vp.check_amt),0)
						FROM vendor_checkspaid vp
						WHERE vp.vendor_id=rev.vendor_id
						AND vp.paidfor_period_id=rev.period_id
					)+
					## checkspaid
					(
						SELECT IFNULL(SUM(cp.check_amt),0)
						FROM artist_info ai
						JOIN releases r ON r.artist_id=ai.artist_id
						JOIN checkspaid cp ON cp.upc=r.upc
						WHERE ai.vendor_id=rev.vendor_id AND cp.paidfor_period_id=rev.period_id
					)
				),0),
				## vendor_accounting.total_checkspaid
				IFNULL(chk.amount,0)
			)/IFNULL(currToUSD.exchange_rate,1) * -1 payment,

		IFNULL(outb.amount,0)/IFNULL(currToUSD.exchange_rate,1) outstanding_balance,

		bal.amount/IFNULL(currToUSD.exchange_rate,1) balance_forward	

	FROM (
		SELECT
			ven.vendor_id,
			ven.period_id,
			ven.currency_id
		FROM
			booked_vendor_contract_snapshot ven
		WHERE 1	
			AND ven.period_id=$period
			AND ven.vendor_id$vendorPredicate
			AND ven.currency_id<>1
	) rev
	JOIN
		vendor ven ON ven.vendor_id=rev.vendor_id	
	LEFT JOIN
		vendor_accounting va ON va.vendor_id=rev.vendor_id
		AND va.period_id=rev.period_id
		AND va.entry_type='dig_actual_net' 
	LEFT JOIN
		vendor_accounting outb ON outb.vendor_id=rev.vendor_id
		AND outb.period_id=rev.period_id
		AND outb.entry_type='outstanding_balance'
	LEFT JOIN
		vendor_accounting rec ON rec.vendor_id=rev.vendor_id
		AND rec.period_id=rev.period_id
		AND rec.entry_type='dig_recoupe'
	LEFT JOIN
		vendor_accounting ob ON ob.vendor_id=rev.vendor_id
		AND ob.period_id=rev.period_id-1
		AND ob.entry_type='outstanding_balance'
	LEFT JOIN
		vendor_accounting chk ON chk.vendor_id=rev.vendor_id
		AND chk.period_id=rev.period_id
		AND chk.entry_type='total_checkspaid'
	LEFT JOIN
		vendor_accounting adv ON adv.vendor_id=rev.vendor_id
		AND adv.period_id=rev.period_id
		AND adv.entry_type='advance_payment'
	LEFT JOIN
		vendor_accounting net ON net.vendor_id=rev.vendor_id
		AND net.period_id=rev.period_id
		AND net.entry_type='dig_net_receipt'
	LEFT JOIN
		vendor_accounting dpd ON dpd.vendor_id=rev.vendor_id
		AND dpd.period_id=rev.period_id
		AND dpd.entry_type='dpd_publishing'
	LEFT JOIN
		vendor_accounting ring ON ring.vendor_id=rev.vendor_id
		AND ring.period_id=rev.period_id
		AND ring.entry_type='ringtone_publishing'
	LEFT JOIN
		vendor_accounting oms ON oms.vendor_id=rev.vendor_id
		AND oms.period_id=rev.period_id
		AND oms.entry_type='oms_fees'
	LEFT JOIN
		vendor_accounting bal ON bal.vendor_id=rev.vendor_id
		AND bal.period_id=rev.period_id
		AND bal.entry_type='balance_forward'
	LEFT JOIN
		vendor_accounting gross ON gross.vendor_id=rev.vendor_id
		AND gross.period_id=rev.period_id
		AND gross.entry_type='dig_gross'
	LEFT JOIN
		vendor_accounting fee ON fee.vendor_id=rev.vendor_id
		AND fee.period_id=rev.period_id
		AND fee.entry_type='dig_distribution_fees'
	LEFT JOIN
		currency_exchange_rates currToUSD ON currToUSD.period_id=rev.period_id
		AND currToUSD.currency_to_id=1
		AND currToUSD.currency_from_id=rev.currency_id
	LEFT JOIN
		currency_exchange_rates priorToUSD ON priorToUSD.period_id=ob.period_id
		AND priorToUSD.currency_to_id=1
		AND priorToUSD.currency_from_id=rev.currency_id
) workstation
JOIN
    currency_exchange_rates xr ON xr.period_id=$period
    AND xr.currency_from_id=workstation.currency_id
    AND xr.currency_to_id=1
HAVING abs(fx) > .01
ORDER BY abs(fx) DESC
}
    );
    $dbh->prepare($sql);
}