#------------------------------------------------------------
# Copyright (C) 2011 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::DynamicReport::Transactions::LicenseBase;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use lib '/app/tools/rps/lib';

use Common::Log;
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

use RPS::DB::Item::FinanceTransaction;

use Data::Dumper;
use List::Compare;

use base 'RPS::DB::Item::DynamicReport';

use constant kDB => Common::DB::Item::kClientDB();


sub _config
{
    my ($class) = @_;
    return
    {
        'is_crossed_account' => {},
        'album_title'            => {},
        'album_title_version'    => {},
        'catalog_no'    => {},
        'album_artist'    => {},
        'label_name'    => {},
        'track_title'    => {},
        'track_number'    => {},
        'track_artist'    => {},
        'isrc'    => {},

        'license_type'    => {},
        'product_type'    => {},
        'region'    => {},
        'share'    => {},
        'publisher'    => {},
        'publisher_id'    => {},
        'client_account_no'    => {},
        'admin'    => {},
        'agent'    => {},
        'publisher_direct'    => {},
        'crossed'    => {},
        'status'    => {},
        'issuer_license_id'    => {},
        'issuer_song_id'    => {},


        'payor_name'            => {},
        'client_payor_no'       => {},
        'payor_status'          => {},
        'default_payor'         => {},
        'min_payment'           => {},
        't_status'              => {},
        't_type'                => {},
        't_amount'              => {},
        't_date'                => {},
        't_check_no'            => {},
        't_memo'                => {},
        't_balance'             => {},
        't_date_created'        => {},
        't_date_modified'       => {},
        't_id'                  => {},
        'finance_account_id'    => {},
    };

}


sub ReportQuery
{
    my ($class, %args) = @_;

    # Since we're possibly combinine the contents of two transaction tables, 
    # we've got to build two queries and possibly union them.
    #
    my @queries;
    push @queries, $class->_BuildQuery('pending_transaction', %args) 
        if ($args{showUnprocessed});

    push @queries, $class->_BuildQuery('finance_transaction', %args) 
        if ($args{showProcessed});

    my @orderBy;
    $class->_BuildOrderBy(\@orderBy, %args);

    my $sql;
    if (scalar @queries > 1)
    {
        $sql = join(' UNION ', @queries);
        $sql = "SELECT * FROM ( $sql ) as subq ";
    }
    else
    {
        $sql = $queries[0];
    }

    if (scalar @orderBy) {
        $sql .= "\nORDER BY " . join(',', @orderBy);
    }


    return $class->GetAll($sql);
}


sub _BuildQuery
{
    my ($class, $tType, %args) = @_;

    # Oddly enough, we're going to have to stuff another UNION query in here.
    # That's how we're going to be able to get transactions for each regular
    # track license account, AND the transactions for the crossed account.
    #
    my @queries;
    push @queries, $class->_BuildTrackLicenseQuery($tType, %args);
    push @queries, $class->_BuildCrossedQuery($tType, %args);

    my $sql;
    if (scalar @queries > 1)
    {
        $sql = join(' UNION ', @queries);
#        $sql = "SELECT * FROM ( $sql ) as subq ";
    }
    else
    {
        $sql = $queries[0];
    }

    return $sql;
}


sub _BuildTrackLicenseQuery
{
    my ($class, $tType, %args) = @_;

    my @select;
    $class->_BuildTrackLicenseSelect(\@select, $tType, %args);

    my @joins;
    $class->_BuildTrackLicenseJoins(\@joins, $tType, %args);

    my @where;
    $class->_BuildWhere(\@where, $tType, %args);

    my @from;
    $class->_BuildFrom(\@from, $tType, %args);


    my $sql;

    $sql .= 'SELECT ' . join(",\n", @select ) . " FROM " . join(',\n', @from);

    if (scalar @joins) {
        $sql .= " \n" . join(" \n", @joins);
    }

    if (scalar @where) {
        $sql .= " WHERE " . join(" AND ", @where);
    }

    return $sql;
}

sub _BuildCrossedQuery
{
    my ($class, $tType, %args) = @_;

    my @select;
    $class->_BuildCrossedSelect(\@select, $tType, %args);

    my @joins;
    $class->_BuildCrossedJoins(\@joins, $tType, %args);

    my @where;
    $class->_BuildWhere(\@where, $tType, %args);

    my @from;
    $class->_BuildFrom(\@from, $tType, %args);


    my $sql;

    $sql .= 'SELECT ' . join(",\n", @select ) . " FROM " . join(',\n', @from);

    if (scalar @joins) {
        $sql .= " \n" . join(" \n", @joins);
    }

    if (scalar @where) {
        $sql .= " WHERE " . join(" AND ", @where);
    }

    return $sql;
}

sub _BuildFrom
{
    my ($class, $from, $tType, %args) = @_;

    push @$from, $tType;
}


sub _BuildWhere
{
    my ($class, $where, $t, %args) = @_;

    my $dateType = $args{dateType};
    my $startDate = $args{startDate};
    my $endDate = $args{endDate};

    
    if ($dateType)
    {
        # Need to map the dateType to the actual field names.
        #
        my $dateField;
        if ('date_modified' eq $dateType)
        {
            $dateField = 'date';
        }
        elsif ('date_created' eq $dateType)
        {
            $dateField = 'date_modified';
        }
        else
        {
            die "ERROR : Invalid dateType $dateType";
        }

        if ($startDate)
        {
            push @$where, "TO_DAYS($t.$dateField) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate)
        {
            push @$where, "TO_DAYS($t. $dateField) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }                        
    }
}

sub _BuildTrackLicenseSelect
{
    my ($class, $select, $t, %args) = @_;


    push @$select, (
        'album.title as album_title',
        'album.title_version as album_title_version',
        'album.catalog_number as catalog_no',
        'album_artist.name as album_artist',
        'label.label_name as label_name',
        'track.title as track_title',
        'track.track_order as track_number',
        'track_artist.name as track_artist',
        'master.isrc as isrc',
        $class->_license . '.type as license_type',   # I guess we convert this inline?
        $class->_license . ".product_type_id as product_type",
        'region.name as region',
        $class->_license . '.share as share',
        $class->_publisher. '.publisher_name as publisher',
        $class->_publisher. '.'.$class->_publisher.'_id as publisher_id',
        $class->_publisher . '.client_account_id as client_account_no',
        "IF( ".$class->_publisher().".agent_id, agent.publisher_name, '') as agent",
        "IF( ".$class->_publisher().".admin_id, admin.publisher_name, '') as admin",
        "IF( ".$class->_license().".publisher_direct, 'Y', 'N') as publisher_direct",
        "IF( ".$class->_license().".cross_collateralized, 'Y', 'N') as crossed",
        "IF( ".$class->_license().".inactive = 1, 'Inactive', 'Active') as status",
        $class->_license . '.issuer_license_id as issuer_license_id',
        $class->_issuerSongID . ' as issuer_song_id',  # US licenses don't have this...
        'payor.name as payor_name',
        'payor.client_payor_id as client_payor_no',
        "IF( payor.inactive, 'inactive', 'active' ) as payor_status",
        "IF( payor.is_default, 'yes', 'no' ) as default_payor",
        "pub_account.min_payment as min_payment", 
        ($t eq 'finance_transaction' ? "'Processed'" : "'Pending'") . " as t_status",
        $class->_transactionTypeSQL($t) . ' as t_type',
        "$t.amount as t_amount",
        "$t.transaction_date as t_date",
        "$t.check_number as t_check_no",
        "$t.memo as t_memo",
        ($t eq 'finance_transaction' ? "$t.balance" : "''") . " as t_balance",
        "$t.date_modified as t_date_created",
        "$t.date as t_date_modified",
        "$t.".$t."_id as t_id",
        "$t.finance_account_id as finance_account_id",
        "0 as is_crossed_account",
    );

    # !!! I might need to add a 'magic' column for sorting purposes.  We'll see how this comes through.

}



sub _BuildTrackLicenseJoins
{
    my ($class, $joins, $t, %args) = @_;
    my $license = $class->_license;
    my $publisher = $class->_publisher;
    my $publisherID = $publisher . '_id';
    my $publisherAccount = $publisher . '_account';
    my $tempTable = $args{_productTypeTable};

    push @$joins, "JOIN $license ON ($license.finance_account_id = $t.finance_account_id)";
    push @$joins, "JOIN $publisher ON ($license.$publisherID = $publisher.$publisherID)";
    push @$joins, 'JOIN payor USING (payor_id)';
    push @$joins, "JOIN track ON ($license.track_id = track.track_id)";
    push @$joins, "JOIN master ON (track.master_id = master.master_id)";
    push @$joins, "JOIN album ON (track.album_id = album.album_id)";
    push @$joins, "JOIN label ON (album.label_id = label.label_id)";
    push @$joins, "JOIN region ON ($license.region_id = region.region_id)";
    push @$joins, "JOIN artist AS album_artist ON (album.artist_id = album_artist.artist_id)";
    push @$joins, "JOIN artist AS track_artist ON (album.artist_id = track_artist.artist_id)";
    push @$joins, "LEFT JOIN $publisher AS agent ON ($publisher.agent_id = agent.$publisherID)";
    push @$joins, "LEFT JOIN $publisher AS admin ON ($publisher.admin_id = admin.$publisherID)";
    push @$joins, "JOIN $publisherAccount AS pub_account ON ($license.$publisherID = pub_account.$publisherID AND $license.payor_id = pub_account.payor_id)";

}


sub _BuildCrossedSelect
{
    my ($class, $select, $t, %args) = @_;

    push @$select, (
        'album.title as album_title',
        'album.title_version as album_title_version',
        'album.catalog_number as catalog_no',
        'album_artist.name as album_artist',
        'label.label_name as label_name',
        'track.title as track_title',
        'track.track_order as track_number',
        'track_artist.name as track_artist',
        'master.isrc as isrc',
        # All these license-specific fields are blank for the crossed select.
        "'' as license_type",  
        "'magic' as product_type",
        "'' as region",
        "'' as share",
        $class->_publisher. '.publisher_name as publisher',
        $class->_publisher. '.'.$class->_publisher.'_id as publisher_id',
        $class->_publisher . '.client_account_id as client_account_no',
        "IF( ".$class->_publisher().".agent_id, agent.publisher_name, '') as agent",
        "IF( ".$class->_publisher().".admin_id, admin.publisher_name, '') as admin",
        "'' as publisher_direct",
        "'' as crossed",
        "'' as status",
        "''as issuer_license_id",
        "'' as issuer_song_id",  # US licenses don't have this...
        'payor.name as payor_name',
        'payor.client_payor_id as client_payor_no',
        "IF( payor.inactive, 'inactive', 'active' ) as payor_status",
        "IF( payor.is_default, 'yes', 'no' ) as default_payor",
        $class->_publisher().".min_payment as min_payment", 
        ($t eq 'finance_transaction' ? "'Processed'" : "'Pending'") . " as t_status",
        $class->_transactionTypeSQL($t) . ' as t_type',
        "$t.amount as t_amount",
        "$t.transaction_date as t_date",
        "$t.check_number as t_check_no",
        "$t.memo as t_memo",
        ($t eq 'finance_transaction' ? "$t.balance" : "''") . " as t_balance",
        "$t.date_modified as t_date_created",
        "$t.date as t_date_modified",
        "$t.".$t."_id as t_id",
        "$t.finance_account_id as finance_account_id",
        "1 as is_crossed_account",
    );
}


sub _BuildCrossedJoins
{
    my ($class, $joins, $t, %args) = @_;
    my $publisher = $class->_publisher;
    my $publisherID = $publisher . '_id';
    my $linkTable = $publisher . '_track_crossed_license_account';
    # !!! finance_account_id in the link table IS NOT A KEY...  This could get expensive
    my $publisherAccount = $publisher . '_account';

    push @$joins, "JOIN $linkTable ON ($linkTable.finance_account_id = $t.finance_account_id)";
    push @$joins, "JOIN $publisher ON ($linkTable.$publisherID = $publisher.$publisherID)";
    push @$joins, 'JOIN payor USING (payor_id)';
    push @$joins, "JOIN track ON ($linkTable.track_id = track.track_id)";
    push @$joins, "JOIN master ON (track.master_id = master.master_id)";
    push @$joins, "JOIN album ON (track.album_id = album.album_id)";
    push @$joins, "JOIN label ON (album.label_id = label.label_id)";
    push @$joins, "JOIN artist AS album_artist ON (album.artist_id = album_artist.artist_id)";
    push @$joins, "JOIN artist AS track_artist ON (album.artist_id = track_artist.artist_id)";
    push @$joins, "LEFT JOIN $publisher AS agent ON ($publisher.agent_id = agent.$publisherID)";
    push @$joins, "LEFT JOIN $publisher AS admin ON ($publisher.admin_id = admin.$publisherID)";
    push @$joins, "JOIN $publisherAccount AS pub_account ON ($linkTable.$publisherID = pub_account.$publisherID AND $linkTable.payor_id = pub_account.payor_id)";

}


sub _transactionTypeSQL {
    my ($class, $t) = @_;

    return " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypeOpeningBalance . ", 'opening balance', " .
           " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypeAdjustment . ", 'adjustment', " .
           " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypeAdvance . ", 'advance', " .
           " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypePayment . ", 'payment', " .
           " IF( $t.type_code = " . RPS::DB::Item::FinanceTransaction::kTypeClosingBalance . ", 'closing balance', " .
           " IF( $t.type_code IS NULL, NULL, " .
           " 'unknown' ) ) ) ) ) ) ";
}

sub _BuildOrderBy
{
    my ($class, $orderBy, %args) = @_;

    # I will probably need to add another sort term to get the crossed stuff to clump together.
    # license_type might just do it...
    # or, it might not...

    push @$orderBy, 'album_title';
    push @$orderBy, 'catalog_no';
    push @$orderBy, 'track_number';
    push @$orderBy, 'publisher';
    push @$orderBy, 'is_crossed_account';
    push @$orderBy, "t_status";
    push @$orderBy, "t_date_created";
}

1;