#!/usr/bin/perl

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

use JSON::XS;
use FileHandle;
use Text::CSV::Easy(qw/csv_build csv_parse/);

use lib '/var/app/orchard/collab/jgetter/perllib';

my $period = shift @ARGV || 243;
my $vendor = shift @ARGV;

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

my $updates;
my $inserts;

my $columns = [ 'label_outstanding_balance', 'manual_adjustment', 'man_adj_outstanding_balance', 'outstanding_balance' ];
my $list = join ",", map { "'$_'" } @{$columns};

use Orchard::DB::Connect;
my $dbh = Orchard::DB::Connect->connect('orchrpt');
my $sth = prepareSQL( $dbh, $list );
$sth->execute();
while ( my $r = $sth->fetchrow_arrayref ) {
    push @{$inserts}, $r->[0];
    push @{$updates}, $r->[1];
}


my $vids = join ",", @{$inserts};
my $text = <<"!";
-- liquibase formatted sql

-- changeset jgetter:1

DROP TABLE IF EXISTS `art_relations`.`ROLLBACK-DATA-ACC1950`;

CREATE TABLE `art_relations`.`ROLLBACK-DATA-ACC1950` 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 ($vids);

!

map { $text .= "$_\n\n" } @{$updates};

$text .= "--rollback UPDATE vendor_accounting va INNER JOIN `ROLLBACK-ACC-19450` rb USING(`id`) SET va.amount = rb.amount;";

print $text;

sub prepareSQL {
    my $dbh  = shift;
	my $list = shift;
    my $sql  = (
        qq{
SELECT
    calc.vendor_id,
    CONCAT(
        "UPDATE art_relations.vendor_accounting SET amount = amount + ",ROUND((calc.maFX * calc.xrUSD)-calc.maUSD,6),"\n"
        "WHERE entry_type IN ($list)\nAND period_id=",calc.period_id," AND vendor_id=",calc.vendor_id,";"
    ), 
    ROUND((calc.maFX * calc.xrUSD)-calc.maUSD,6) deltaUSD,
    ROUND(((calc.maFX * calc.xrUSD)-calc.maUSD)/calc.xrUSD,6) deltaFX
FROM (
	SELECT
		va.vendor_id,
		va.period_id,
		## vendor's payout currency_id
		IFNULL((
			SELECT currency_id
			FROM vendor_contract 
			WHERE vendor_id=va.vendor_id
			ORDER BY cont_end DESC LIMIT 1
		),1) currency_id,
		(	
			## get the most current vendor payout currency
			## get the exchange rate for that currency to USD
			SELECT xr.exchange_rate
			FROM currency_exchange_rates xr
			WHERE 1
			AND xr.period_id=va.period_id
			AND xr.currency_to_id=1
			AND xr.currency_from_id=(
				SELECT currency_id
				FROM vendor_contract 
				WHERE vendor_id=va.vendor_id
				ORDER BY cont_end DESC LIMIT 1
			)
		) xrUSD,
		va.amount obUSD,
		IFNULL(va1.amount,0) maUSD,
		ROUND(
			## adjustments from manual_adjustments
			IFNULL((
				SELECT
					ROUND(SUM(ma.amount_in_original_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=ma.currencies_id
						AND xr.currency_to_id=(
							SELECT currency_id
							FROM vendor_contract 
							WHERE vendor_id=ma.parent_id
							ORDER BY cont_end DESC LIMIT 1
						))
					),2) orig
				FROM
					manual_adjustment ma
				WHERE 1
					AND ma.category_id!=65
					AND ma.parent_id=va.vendor_id
					AND ma.apply_to_period_id=va.period_id
			),0)+
			## expenses from release_manual_adjustments
			IFNULL((
				SELECT
					SUM(rma.amount *(
						## 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=ma1.apply_to_period_id
						AND xr.currency_from_id=rma.currencies_id
						AND xr.currency_to_id=(
							SELECT currency_id
							FROM vendor_contract 
							WHERE vendor_id=ma1.parent_id
							ORDER BY cont_end DESC LIMIT 1
						))
					)
				FROM
					manual_adjustment ma1
				JOIN
					release_manual_adjustment rma ON rma.vendor_manual_adjustment_id=ma1.id
				WHERE 1
					AND ma1.category_id=65
					AND ma1.parent_id=va.vendor_id	
					AND ma1.apply_to_period_id=va.period_id
				GROUP BY 
					ma1.parent_id,ma1.apply_to_period_id
		),0),6) maFX
	FROM ( 
		SELECT vendor_id,period_id,amount
		FROM vendor_accounting 
		WHERE 1
			AND vendor_id$vendorPredicate
			AND period_id=$period
			AND entry_type='outstanding_balance'
	) va
	JOIN
		vendor_accounting va1 ON va1.vendor_id=va.vendor_id
		AND va1.period_id=va.period_id
		AND va1.entry_type='manual_adjustment'
	GROUP BY
		va.vendor_id,va.period_id
	HAVING
		abs(maUSD) > 1 AND abs(maFX) <> 0
) calc
JOIN
	vendor v ON v.vendor_id=calc.vendor_id
JOIN
	period per ON per.period_id=calc.period_id
JOIN
	currencies cur ON cur.id=calc.currency_id
WHERE calc.currency_id=1
GROUP BY 
	calc.vendor_id, calc.period_id
HAVING abs(deltaUSD) > .01
ORDER BY 
	abs(deltaFX) DESC
	}
    );
    $dbh->prepare($sql);
}