#------------------------------------------------------------
# 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,
"LEFT 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)";
}

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;