#!/usr/bin/perl

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 '/Users/denk002/projects/collab/jgetter/perllib';
use Orchard::Session;
use Orchard::DB::Connect;
use Orchard::Accounting::JIRA;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'help|?|h'        => "print usage message and exit" ],
    [ 'database|d=s'    => 'DB connection host - default orchdev',              { 'default'  => 'orchprod' } ],
    [ 'git_version|v=i' => 'git tree version <ACC>-<1234>-<VERSION> default 1', { 'default'  => 1 } ],
    [ 'jira|j=i'        => 'JIRA ticket number',                                { 'required' => 1 } ],
    [ 'query|q'         => 'Print the query used' ],
    [ 'period|p=i'      => 'Period ID to adjust', { 'required' => 1 } ],
    [ 'usd|u'           => 'Patch USD vendor',    { 'default'  => 0 } ],
    [ 'vendor'          => 'Vendor ID to adjust' ]

);

if ( $opt->help ) {
    logMessage( 'info', $usage->text, 'options' );
    exit 1;
}

my $period   = $opt->period;
my $jira     = Orchard::Accounting::JIRA->new( $opt->jira, $opt->git_version );
my $branch   = $jira->branch;
my $filename = $jira->getfilepath( "fix_period_" . $period . "_fx_bals" );

my $dsn = Orchard::DB::Connect->dsn( $opt->database );
my $dbh;
if ( defined $dsn->{'ssh'} ) {
    my $ssh = $dsn->{'ssh'};
    $ssh->{'net'}->spawn( $ssh->{'tunnel'} );
    sleep 1;
}
$dbh = DBI->connect( @{ $dsn->{'conn'} } ) || die "Conn: $DBI::errstr";

my $vendor   = defined $opt->vendor ? "=" . $opt->vendor : ">0";
my $currency = $opt->usd            ? "=1"               : "<>1";

$vendor = "=25954" if $opt->usd;

my $sth = workstation_sth( $dbh, $period, $vendor, $currency );
if ( $opt->query ) {
    print $sth->{'Statement'} . "\n";
    exit;
}

logMessage( 'info', "Running query to get delta's to adjust - will take awhile for more than 1 vendor..." );

$sth->execute();
if ( !$sth->rows ) {
    logMessage( 'fatal', "No rows returned from query" );
    exit 1;
}
logMessage( 'info', $sth->rows . " FX vendors need adjustment" );

my $vids;
my $updates;
my $list = join ",", map { "'$_'" } ( 'balance_forward', 'amount_payable', 'dig_amount_payable', 'dig_balance_forward' );
while ( my $r = $sth->fetchrow_arrayref ) {
    my $vid = $r->[0];
    my $amt = $r->[4];
    push @{$vids}, $vid;
    my $update;
    $update .= "UPDATE vendor_accounting SET amount = amount + $amt ";
    $update .= "WHERE vendor_id=$vid AND period_id=$period ";
    $update .= "\nAND entry_type IN ($list);";
    push @{$updates}, $update;
}
$sth->finish;

my $table = "`ROLLBACK-DATA-${branch}`";

my $vens = join ",", sort { $a <=> $b } @{$vids};

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

-- changeset jgetter:1

DROP TABLE IF EXISTS $table;

CREATE TABLE $table AS
SELECT * FROM vendor_accounting
WHERE period_id=$period AND vendor_id IN ($vens)
AND entry_type IN ($list);

!

map { $sql .= $_ } join "\n\n", @{$updates};

$sql .= <<"!";


--rollback UPDATE vendor_accounting va JOIN $table rb USING(id) SET va.amount=rb.amount;

!

$jira->finish( $filename, $sql );

sub workstation_sth {
    my $dbh      = shift;
    my $period   = shift;
    my $vendor   = shift;
    my $currency = 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$vendor
			AND ven.currency_id$currency
	) 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);
}