#!/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 = 244;
my $vendor = shift @ARGV;

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

my $updates;
my $inserts;

my $columns = [ 'balance_forward', 'amount_payable', 'dig_amount_payable', 'dig_balance_forward' ];

my $list = join ",", map { "'$_'" } @{$columns};

use Orchard::DB::Connect;
my $dbh = Orchard::DB::Connect->connect('orchrpt');
my $ids = getids($dbh);
my $sth = prepareSQL($dbh);

my $rbtab = 'ROLLBACK-DATA-ACC1985';
my $vids  = join ",", @{$ids};
my $text  = <<"!";
-- liquibase formatted sql

-- changeset jgetter:1

INSERT INTO `art_relations`.`$rbtab` 
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);

!

foreach my $id ( @{$ids} ) {
    print STDERR "Processing $id...\n";
    my $ttlUSDAmount = 0;
    for my $p ( ( $period - 1 ) ... ( $period + 1 ) ) {
        $sth->execute( $p, $id );
        my $href = $sth->fetchrow_hashref();
        if ( $p ne ( $period + 1 ) ) {
            $ttlUSDAmount += sprintf( "%.6f", $href->{'deltaUSD'} / $href->{'periodUSDRate'} );
        } else {
            $ttlUSDAmount = sprintf( "%.6f", ( $ttlUSDAmount * $href->{'periodUSDRate'} ) );
        }
    }

    my $update = "UPDATE vendor_accounting SET amount = amount + $ttlUSDAmount\n";
    $update .= "WHERE vendor_id=$id AND period_id=" . ( $period + 1 ) . "\n";
    $update .= "AND entry_type IN ($list);";

    push @{$updates}, $update;
}

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 $sql = (
        qq{
SELECT
	xx.vendor_id,
	xx.period_id,
	xx.currency_id,
	xx.manFX correctFX,
	xx.manUSD wrongUSD,
	xx.currUSD periodUSDRate,
	xx.nextUSD nextUSDRate,
	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=?
		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=?
	) va
) xx
	}
    );
    $dbh->prepare($sql);
}

sub getids {
    my $dbh = shift;
    my $sql = (
        qq{
SELECT
	xx.vendor_id
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 IN (25954,26871,28470,28469,25937,28460,26022,25997,25711,25680,25784,25965,28417,
25910,25755,8568,25953,25787,25922,25904,25776,25671,25906,28520,25803,25902)
	) va
) xx
}
    );
    $dbh->selectcol_arrayref($sql);
}