#!/usr/bin/perl

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

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

use lib '/var/app/orchard/collab/jgetter/perllib';
use Orchard::Period;
use Orchard::Session;

my $head =
  [qw/AccountID ContractID UPC Amount Currency ActivityYear ActivityMonth ApplyToStatementYear ApplyToStatementMonth AdjustmentType Comment/
  ];
my $header = csv_build( @{$head} );
print "$header\n";

my $dbh = Orchard::Session::getdbh('orchrpt');
print_this( $dbh->selectall_hashref( expense_sql(),    'id', undef, (280) ) );
print_this( $dbh->selectall_hashref( adjustment_sql(), 'id', undef, (280) ) );

sub print_this {
    my $this = shift;
    my $keys = [
        qw/account_id contract_id upc amount currency activity_year activity_month apply_to_statement_year apply_to_statement_month adjustment_type comment/
    ];
    foreach my $id ( keys %{$this} ) {
        my $href = $this->{$id};
        my $data;
        foreach my $key ( @{$keys} ) {
            push @{$data}, $href->{$key};
        }
        my $activitydt  = DateTime->new( 'year' => $href->{'activity_year'},           'month' => $href->{'activity_month'} );
        my $applytodt   = DateTime->new( 'year' => $href->{'apply_to_statement_year'}, 'month' => $href->{'apply_to_statement_month'} );
        my $activitypid = Orchard::Period->getIdFromDateTime($activitydt);
        my $applytopid  = Orchard::Period->getIdFromDateTime($applytodt);
        my $line        = csv_build( @{$data} );
        print "$line\n";
    }
}

sub expense_sql {
    my $sql = <<"!";
## expenses
SELECT
	rma.id,
    ma.parent_id account_id,
    NULL contract_id,
    rel.upc upc,
    rma.amount amount,
    cur.ISO_4217_code currency,
    p1.year activity_year,
    p1.month activity_month,
    p2.year apply_to_statement_year,
    p2.month apply_to_statement_month,
    cat.category adjustment_type,
    rma.description comment
FROM    
    manual_adjustment ma
JOIN 
	release_manual_adjustment rma ON rma.vendor_manual_adjustment_id=ma.id
JOIN 
	releases rel on rel.release_id=rma.release_id
JOIN 
    manual_adjustment_category cat on cat.category_id=ma.category_id
JOIN 
    currencies cur ON cur.id=rma.currencies_id
JOIN 
    period p1 on p1.period_id=ma.adjust_for_period_id
JOIN 
    period p2 on p2.period_id=ma.apply_to_period_id
WHERE 1 
    AND ma.category_id = 65
    AND ma.adjust_for_period_id = ?
    AND ma.apply_to_period_id >= ma.adjust_for_period_id
LIMIT 5
!
    $sql;
}

sub adjustment_sql {
    my $sql = <<"!";
## manual adjustments without reserves or expenses
SELECT
	ma.id,
	ma.parent_id account_id,
	NULL contract_id,
	NULL upc,
	ma.amount_in_original_currency amount,
	cur.ISO_4217_code currency,
	p1.year activity_year,
	p1.month activity_month,
	p2.year apply_to_statement_year,
	p2.month apply_to_statement_month,
	cat.category adjustment_type,
	ma.comment
FROM	
	manual_adjustment ma
JOIN 
	manual_adjustment_category cat on cat.category_id=ma.category_id
JOIN 
	currencies cur ON cur.id=ma.currencies_id
JOIN 
	period p1 on p1.period_id=ma.adjust_for_period_id
JOIN 
	period p2 on p2.period_id=ma.apply_to_period_id
WHERE 1 
	AND ma.adjust_for_period_id = ?
	and ma.category_id NOT IN (65,68,69)
	AND ma.apply_to_period_id >= ma.adjust_for_period_id
LIMIT 5
!
    $sql;
}