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

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::Expense;

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

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

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

sub _config {
    my ($class) = @_;

    return {
        # Contract
        'artist_contract_title'    => {},
        'artist_name'              => {},
        'client_contract_id'       => {},
        'rs_contract_id'           => {},
        'payor'                    => {},
        'payee'                    => {},
        'artist_client_account_id' => {},
        'artist_payee_id'          => {},
        'date_issued'              => {},
        'term_start'               => {},
        'term_end'                 => {},
        'album_title'              => {},
        'catalog_number'           => {},
        'client_album_id'          => {},
        'album_artist'             => {},
        'label_name'               => {},
        'rs_album_id'              => {},
        'track_title'              => {},
        'track_no'                 => {},
        'track_artist'             => {},
        'isrc'                     => {},
        'rs_track_id'              => {},
        'duration'                 => {},
        'prorate_track_count'      => {},
        'contract_status'          => {},
        'cross_collateralized'     => {},
        'deduction_type'           => {},
        'expense_type_name'        => {},
        'percent'                  => {},
        'amount'                   => {},
        'net_rate'                 => {},
        'memo'                     => {},
        'processed'                => {},
        'date_created'             => {},
        'created_by'               => {},
        'date_modified'            => {},
        'modified_by'              => {},
        'expense_id'               => {},
        'artist_run_name'          => {},
        'artist_run_id'            => {},
        'artist_statement_id'      => {},
        'track_custom_1'           => {},
        'track_custom_2'           => {},
        'track_custom_3'           => {},
        'album_custom_1'           => {},
        'album_custom_2'           => {},
        'album_custom_3'           => {},
    };
}

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

    my $sql = $class->_AlbumContractSQL(%args) . ' UNION ' . $class->_TrackContractSQL(%args);

    $sql .= ' ORDER BY artist_contract_title, payee, album_title, catalog_number, track_no, date_created';

    if ( $args{reportType} && $args{reportType} =~ /^(?:Processed|All)$/i ) {
        $sql = $class->_wrapQueryToGetRunStatementDetail($sql);
    }
    Log->debug("QUERY: $sql");

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

sub _wrapQueryToGetRunStatementDetail {
    my ($class, $sql) = @_;

    return unless $sql;

    $sql = qq/
        SELECT
            tmp.*,
            IF(tmp.processed = 'yes', rr.label, NULL)                       AS artist_run_name,
            IF(tmp.processed = 'yes', rs.artist_royalty_run_id, NULL)       AS artist_run_id,
            IF(tmp.processed = 'yes', rs.artist_royalty_statement_id, NULL) AS artist_statement_id
        FROM (
            $sql
        ) as tmp
        LEFT JOIN (
            SELECT expense_id, MAX(artist_royalty_album_id) AS artist_royalty_album_id
            FROM artist_royalty_expense_item
            GROUP BY expense_id
        ) AS rei ON tmp.expense_id = rei.expense_id
        LEFT JOIN artist_royalty_album        AS ra  ON rei.artist_royalty_album_id    = ra.artist_royalty_album_id
        LEFT JOIN artist_royalty_statement    AS rs  ON ra.artist_royalty_statement_id = rs.artist_royalty_statement_id
        LEFT JOIN artist_royalty_run          AS rr  ON rs.artist_royalty_run_id       = rr.artist_royalty_run_id
    /;
}

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

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

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

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

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

    my $sql;

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

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

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

    return $sql;
}

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

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

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

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

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

    my $sql;

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

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

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

    return $sql;
}

sub _BuildAlbumSelect {
    my ( $class, $select, %args ) = @_;
    push @$select, 'new_artist_contract.title AS artist_contract_title';
    push @$select, 'album_artist.name AS artist_name';
    push @$select, 'new_artist_contract.client_contract_id AS client_contract_id';
    push @$select, "new_artist_contract.artist_contract_id AS rs_contract_id";
    push @$select, 'payor.name AS payor';
    push @$select, 'artist_payee.name AS payee';
    push @$select, 'artist_payee.client_account_id AS artist_client_account_id';
    push @$select, 'artist_payee.artist_payee_id AS artist_payee_id';
    push @$select, 'new_artist_contract.issue_date AS date_issued';
    push @$select, 'new_artist_contract.term_start AS term_start';
    push @$select, 'new_artist_contract.term_end AS term_end';
    push @$select, 'album.title AS album_title';
    push @$select, 'album.catalog_number AS catalog_number';
    push @$select, 'album.client_album_id AS client_album_id';
    push @$select, 'album_artist.name AS album_artist';
    push @$select, 'label.label_name AS label_name';
    push @$select, 'album.album_id AS rs_album_id';
    push @$select, "'' AS track_title";
    push @$select, "'' AS track_no";
    push @$select, "'' AS track_artist";
    push @$select, "'' AS isrc";
    push @$select, "'' AS rs_track_id";
    push @$select, "'' AS duration";
    push @$select, "'' AS prorate_track_count";
    push @$select, 'album_contract.status AS contract_status';
    push @$select, "IF(album_contract.cross_collateralized,'yes','no') AS cross_collateralized";
    push @$select, "IF(expense.pre_process,'Net Revenue Deduction','Album Expense') as deduction_type";
    push @$select, 'expense_name.name AS expense_type_name';
    push @$select, 'expense.percent AS percent';
    push @$select, 'expense.amount AS amount';
    push @$select, 'IF(expense.pre_process = 1, new_artist_contract_term.rate, "") AS net_rate';
    push @$select, 'expense.memo AS memo';
    push @$select, "IF(expense.processed,'yes','no') as processed";
    push @$select, 'expense.date_created AS date_created';
    push @$select, 'expense.created_by AS created_by';
    push @$select, 'expense.date_modified AS date_modified';
    push @$select, 'expense.modified_by AS modified_by';
    push @$select, 'expense.expense_id AS expense_id';
    push @$select, 'album.custom_1 AS album_custom_1';
    push @$select, 'album.custom_2 AS album_custom_2';
    push @$select, 'album.custom_3 AS album_custom_3';
    push @$select, "'' AS track_custom_1";
    push @$select, "'' AS track_custom_2";
    push @$select, "'' AS track_custom_3";
}

sub _BuildTrackSelect {
    my ( $class, $select, %args ) = @_;
    push @$select, "new_artist_contract.title AS artist_contract_title";
    push @$select, "IFNULL(track_artist.name, album_artist.name) AS artist_name";
    push @$select, "new_artist_contract.client_contract_id AS client_contract_id";
    push @$select, "new_artist_contract.artist_contract_id AS rs_contract_id";
    push @$select, "payor.name AS payor";
    push @$select, "artist_payee.name AS payee";
    push @$select, "artist_payee.client_account_id AS artist_client_account_id";
    push @$select, "artist_payee.artist_payee_id AS artist_payee_id";
    push @$select, "new_artist_contract.issue_date AS date_issued";
    push @$select, "new_artist_contract.term_start AS term_start";
    push @$select, "new_artist_contract.term_end AS term_end";
    push @$select, "album.title AS album_title";
    push @$select, "album.catalog_number AS catalog_number";
    push @$select, 'album.client_album_id AS client_album_id';
    push @$select, "album_artist.name AS album_artist";
    push @$select, "label.label_name AS label_name";
    push @$select, 'album.album_id AS rs_album_id';
    push @$select, "track.title AS track_title";
    push @$select, "track.track_order AS track_no";
    push @$select, "track_artist.name AS track_artist";
    push @$select, "master.isrc AS isrc";
    push @$select, "track.track_id AS rs_track_id";
    push @$select, "master.duration AS duration";
    push @$select, "track_contract.prorate_track_count AS prorate_track_count";
    push @$select, "track_contract.status AS contract_status";
    push @$select, "IF(track_contract.cross_collateralized,'yes','no') AS cross_collateralized";
    push @$select, "IF(expense.pre_process,'Net Revenue Deduction','Track Expense') as deduction_type";
    push @$select, "expense_name.name AS expense_type_name";
    push @$select, "expense.percent AS percent";
    push @$select, "expense.amount AS amount";
    push @$select, 'IF(expense.pre_process = 1, new_artist_contract_term.rate, "") AS net_rate';
    push @$select, "expense.memo AS memo";
    push @$select, "IF(expense.processed,'yes','no') as processed";
    push @$select, "expense.date_created AS date_created";
    push @$select, "expense.created_by AS created_by";
    push @$select, "expense.date_modified AS date_modified";
    push @$select, "expense.modified_by AS modified_by";
    push @$select, "expense.expense_id AS expense_id";
    push @$select, 'album.custom_1 AS album_custom_1';
    push @$select, 'album.custom_2 AS album_custom_2';
    push @$select, 'album.custom_3 AS album_custom_3';
    push @$select, "track.custom_1 AS track_custom_1";
    push @$select, "track.custom_2 AS track_custom_2";
    push @$select, "track.custom_3 AS track_custom_3";
}

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

    push @$from, 'expense';
}

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

    push @$from, 'expense';
}

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

    push @$joins, "JOIN album_contract ON (expense.parent_id = album_contract.album_contract_id)";
    push @$joins, "JOIN new_artist_contract ON (album_contract.artist_contract_id = new_artist_contract.artist_contract_id AND new_artist_contract.deleted = 0)";
    push @$joins, "JOIN album ON (album_contract.album_id = album.album_id)";
    push @$joins, "LEFT JOIN new_artist_contract_term ON (album_contract.artist_contract_id = new_artist_contract_term.artist_contract_id AND new_artist_contract_term.priority = 0)";
    push @$joins, "JOIN label on (album.label_id = label.label_id)";
    push @$joins, "JOIN artist_payee ON (artist_payee.artist_payee_id = new_artist_contract.artist_payee_id)";
    push @$joins, "JOIN artist AS album_artist ON (album.artist_id = album_artist.artist_id)";
    push @$joins, "JOIN expense_type ON (expense_type.expense_type_id = expense.expense_type_id)";
    push @$joins, "JOIN expense_name ON (expense_name.expense_name_id = expense_type.expense_name_id)";
    push @$joins, "JOIN payor ON (new_artist_contract.payor_id = payor.payor_id)";
}

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

    push @$joins, "JOIN track_contract ON (expense.parent_id = track_contract.track_contract_id)";
    push @$joins, "JOIN new_artist_contract ON (track_contract.artist_contract_id = new_artist_contract.artist_contract_id AND new_artist_contract.deleted = 0)";
    push @$joins, "JOIN track ON (track_contract.track_id = track.track_id)";
    push @$joins, "JOIN album ON (album.album_id = track.album_id)";
    push @$joins, "LEFT JOIN new_artist_contract_term ON (track_contract.artist_contract_id = new_artist_contract_term.artist_contract_id AND new_artist_contract_term.priority = 0)";
    push @$joins, "JOIN label on (album.label_id = label.label_id)";
    push @$joins, "JOIN master ON (track.master_id = master.master_id)";
    push @$joins, "JOIN artist_payee ON (artist_payee.artist_payee_id = new_artist_contract.artist_payee_id)";
    push @$joins, "JOIN artist AS album_artist ON (album.artist_id = album_artist.artist_id)";
    push @$joins, "JOIN artist AS track_artist ON (track.artist_id = track_artist.artist_id)";
    push @$joins, "JOIN expense_type ON (expense_type.expense_type_id = expense.expense_type_id)";
    push @$joins, "JOIN expense_name ON (expense_name.expense_name_id = expense_type.expense_name_id)";
    push @$joins, "JOIN payor ON (new_artist_contract.payor_id = payor.payor_id)";
}

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

    my $type = $args{reportType};

    push @$where, "expense.parent_type=1";
    push @$where, "expense.processed=0" if 'Pending' eq $type;
    push @$where, "expense.processed=1" if 'Processed' eq $type;

    $class->_BuildDateWhere( $where, %args );
}

sub _BuildDateWhere {
    my ( $class, $where, %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.
        #
        if ($startDate) {
            push @$where, "TO_DAYS(expense.$dateType) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate) {
            push @$where, "TO_DAYS(expense. $dateType) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }
    }
}

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

    my $type = $args{reportType};
    push @$where, "expense.parent_type=2";
    push @$where, "expense.processed=0" if 'Pending' eq $type;
    push @$where, "expense.processed=1" if 'Processed' eq $type;

    $class->_BuildDateWhere( $where, %args );
}

1;