#!/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 "$ENV{'HOME'}/sandbox/collab/jgetter/perllib";

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

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

my $updates;
my $inserts;

my $columns =
  [ 'dig_outstanding_balance', '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 $rbtab = 'ROLLBACK-DATA-ACC1985';
my $vids = join ",", @{$inserts};
my $text = <<"!";
-- liquibase formatted sql

-- changeset jgetter:1

CREATE TABLE `art_relations`.`$rbtab` 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 { $text .= "$_\n\n" } @{$updates};

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

print $text;

sub prepareSQL {
    my $dbh  = shift;
    my $list = shift;
    my $sql  = (
        qq{
SELECT
    xx.vendor_id,
    CONCAT(
        "UPDATE art_relations.vendor_accounting SET amount = amount + ",ROUND((xx.manFX*currUSD)-xx.manUSD,6),"\n"
        "WHERE entry_type IN ($list)\nAND period_id=",xx.period_id," AND vendor_id=",xx.vendor_id,";"
    ),
	ROUND(
		( xx.manFX * currUSD ) - xx.manUSD 
	,6) deltaUSD
FROM (
	SELECT
		va.vendor_id,
		va.period_id,
		va.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=va.currency_id	
		) currUSD,
		(	
			## get the next 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+1
			AND xr.currency_to_id=1
			AND xr.currency_from_id=va.currency_id	
		) nextUSD,
		(	
			## get the next 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+1
			AND xr.currency_from_id=1
			AND xr.currency_to_id=va.currency_id	
		) nextFX,
		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) manFX,
		IFNULL((
			SELECT sum(amount) 
			FROM manual_adjustment ma
			WHERE 1
				AND ma.parent_id=va.vendor_id
				AND ma.apply_to_period_id=va.period_id
		),0) manUSD
	FROM ( 
		SELECT
			ven.vendor_id,
			per.period_id,
			per.year,
			per.month,
			IFNULL(CASE 
				WHEN
					## filter out noise b/c the vendor_contract flipped currencies 
					## without creating a new contract for vendor_id 10264
					## there has to be more of these and we can more to the CASE
					## statement as we discover them
					ven.vendor_id = 10264 AND per.period_id <= 237 THEN 1
				ELSE
					IFNULL(vc.currency_id,(
						SELECT currency_id
						FROM vendor_contract 
						WHERE vendor_id=ven.vendor_id
						ORDER BY cont_end DESC LIMIT 1		
					))
			END,1) currency_id
		FROM
			vendor ven
		JOIN
			period per ON per.period_id=$period
		JOIN
			vendor_payment_interval_history vh ON vh.vendor_id=ven.vendor_id
			AND vh.period_id=per.period_id
			AND vh.payment_interval = 'MONTH'
		LEFT JOIN
			vendor_contract vc ON vc.vendor_id=ven.vendor_id
			AND (   
				vc.cont_start <= STR_TO_DATE(CONCAT_WS(',',per.year,per.month,1),"%Y,%m,%dd") AND 
				vc.cont_end >= STR_TO_DATE(CONCAT_WS(',',per.year,per.month,1),"%Y,%m,%dd"
			)) 
		WHERE 1
			AND ven.vendor_id$vendorPredicate
	) va
) xx
HAVING abs(deltaUSD) > .01
}
    );
    $dbh->prepare($sql);
}