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

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();

# Most if not all of the Catalog group will have the same base group of columns.

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

    return {
        # Contract
        'title'                    => {},
        'artist_payee_id'          => {},
        'artist_description'       => {},
        'client_contract_id'       => {},
        'label_name'               => {},
        'payor'                    => {},
        'payee'                    => {},
        'date_issued'              => {},
        'term_start'               => {},
        'term_end'                 => {},
        'rs_contract_id'           => {},
        'date_created'             => {},
        'date_modified'            => {},
        'created_by'               => {},
        'modified_by'              => {},
        'comments'                 => {},
        'reserve_rate'             => {},
        'artist_client_account_id' => {},

        # Contract Terms
        'artist_contract_term_id' => {},
        'priority'                => {},
        'contract_term_source_id' => {},
        'region'                  => {},
        'channel'                 => {},
        'price_level_id'          => {},
        'contract_rate_type_id'   => {},
        'rate'                    => {},
        'rate_reduction'          => {},
        'percentage_of_sales'     => {},
        'packaging_deduction'     => {},
        'free_goods_deduction'    => {},
        'term_status'             => {},

        # Attached tracks and albums
        'album_title'     => {},
        'catalog_no'      => {},
        'client_album_id' => {},
        'album_artist'    => {},
        'label_name'      => {},
        'track_title'     => {},
        'track_artist'    => {},
        'track_minutes'   => {},
        'track_seconds'   => {},
        'proration'       => {},
        'contract_status' => {},
        'crossed'         => {},
        'album_custom_1'  => {},
        'album_custom_2'  => {},
        'album_custom_3'  => {},
        'track_custom_1'  => {},
        'track_custom_2'  => {},
        'track_custom_3'  => {},

        # Income Sources
        'source_type'        => {},
        'income_type'        => {},
        'net_revenue_rate'   => {},
        'expense_type'       => {},
        'recoupable_percent' => {},

        # Expenses
        'deduction_type'   => {},
        'expense_type'     => {},
        'percent'          => {},
        'amount'           => {},
        'effective_amount' => {},
        'memo'             => {},
        'processed'        => {},
    };

}

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

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

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

    # Because we are working on two seperate data paths ( license income and expense )
    # we have to do two queries because we want 1 touple returned for each license income
    # and expence type record.
    my @unionJoins;
    $class->_BuildUnionJoins( \@unionJoins, %args );

    my @groupBy;
    $class->_BuildGroupBy( \@groupBy, %args );

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

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

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

    my $sql;

    foreach my $uJoins (@unionJoins) {
        $sql .= "\nUNION\n" if ($sql);

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

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

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

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

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

    $sql = "SELECT * FROM ( $sql ) as subq ";

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

    # SQL is much easier to debug when it has new lines, but it doesn't log well.
    $sql =~ s/\n/ /g;
    Log->debug("QUERY: $sql");

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

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

    push @$from, 'new_artist_contract';
}

sub _BuildSelect {
    my ( $class, $select, %args ) = @_;
    push @$select, "new_artist_contract.artist_payee_id AS artist_payee_id";
    push @$select, "new_artist_contract.title AS title";
    push @$select, "new_artist_contract.artist_description as artist_description";
    push @$select, "new_artist_contract.artist_contract_id as rs_contract_id";
    push @$select, "new_artist_contract.client_contract_id as client_contract_id";
    push @$select, "new_artist_contract.date_created as date_created";
    push @$select, "new_artist_contract.date_modified as date_modified";
    push @$select, "new_artist_contract.created_by as created_by";
    push @$select, "new_artist_contract.modified_by as modified_by";
    push @$select, "new_artist_contract.comments as comments";
    push @$select, "new_artist_contract.reserve_rate as reserve_rate";
    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, "IF( new_artist_contract.issue_date, new_artist_contract.issue_date, NULL ) as date_issued";
    push @$select, "new_artist_contract.term_start as term_start";
    push @$select, "new_artist_contract.term_end as term_end";

    if ( $args{withTerms} ) {
        push @$select, "IF(new_artist_contract_term.priority, new_artist_contract_term.priority, 'default') as priority";
        push @$select, "new_artist_contract_term.artist_contract_term_id as artist_contract_term_id";
        push @$select, "IF(new_artist_contract_term.rate, new_artist_contract_term.rate, '0.0000') AS rate";
        push @$select, "IF(new_artist_contract_term.rate_reduction, new_artist_contract_term.rate_reduction, '100.0000') AS rate_reduction";
        push @$select, "IF(new_artist_contract_term.percentage_of_sales, new_artist_contract_term.percentage_of_sales, '100.0000') AS percentage_of_sales";
        push @$select,
          "IF(new_artist_contract_term.packaging_deduction, new_artist_contract_term.packaging_deduction, '0.0000') AS packaging_deduction";
        push @$select,
"IF(new_artist_contract_term.free_goods_deduction, new_artist_contract_term.free_goods_deduction, '0.0000') AS free_goods_deduction";
        push @$select, "IF( new_artist_contract_term.inactive OR new_artist_contract_term.artist_contract_term_id IS NULL, 'inactive', 'active' ) as term_status";

        # !!! Can't join to income_source - that lives in RSCOMMON.
        # !!! We'll just return the id and translate id to income source on output.
        #        push @$select, "income_source.name as source";
        #        push @$select, "new_artist_contract_term.income_source_id as income_source_id";
        push @$select, "new_artist_contract_term.contract_term_source_id as contract_term_source_id";

        push @$select, "IF(new_artist_contract_term.priority = 0 OR new_artist_contract_term.artist_contract_term_id IS NULL, 'All', region.name) as region";
        push @$select,
'IF(channel.channel_id, channel.name, IF(new_artist_contract_term.channel_id IS NOT NULL, "All", IF(new_artist_contract_term.priority = 0 OR new_artist_contract_term.artist_contract_term_id IS NULL, "All", NULL))) as channel';

        #        push @$select, "price_level.name as price";
        push @$select, "new_artist_contract_term.price_level_id as price_level_id";

        push @$select, "IF(new_artist_contract_term.artist_contract_term_id, new_artist_contract_term.contract_rate_type_id, 5) as contract_rate_type_id";
    }

    if ( $args{withAttached} ) {
        push @$select, 'album.title as album_title';
        push @$select, 'album.catalog_number as catalog_no';
        push @$select, 'album_artist.name as album_artist';
        push @$select, 'label.label_name as label_name';
        push @$select, 'track.title as track_title';
        push @$select, 'track_artist.name as track_artist';
        push @$select, 'FLOOR(master.duration / 60) as track_minutes';
        push @$select, 'master.duration % 60 as track_seconds';
        push @$select, 'prorate_track_count as proration';
        push @$select,
'if( album.album_id, IF( album_contract.status = 1, "active", IF( album_contract.status = 2, "inactive", "unknown" ) ), if( track.track_id, IF( track_contract.status = 1, "active", IF( track_contract.status = 2, "inactive", "unknown" ) ), NULL ) ) as contract_status';
        push @$select,
'if( album.album_id, IF( album_contract.cross_collateralized, "yes", "no" ), if( track.track_id, IF( track_contract.cross_collateralized, "yes", "no" ), NULL ) ) as crossed';

        if ( $args{unprocessedExpenses} || $args{processedExpenses} ) {
            push @$select, "IF( album_expense.expense_id, IF( album_expense.pre_process, 'Net Revenue Deduction', 'Album Expense' ), "
              . "IF( track_expense.expense_id, IF( track_expense.pre_process, 'Net Revenue Deduction', 'Track Expense' ), NULL ) ) as deduction_type";
            push @$select, "IF( album_expense.expense_id, album_expense_name.name, track_expense_name.name ) as expense_type";
            push @$select, "IF( album_expense.expense_id, album_expense.percent, track_expense.percent ) as percent";
            push @$select, "IF( album_expense.expense_id, album_expense.amount, track_expense.amount ) as amount";
            push @$select,
"ROUND( IF( album_expense.expense_id, album_expense.amount * album_expense.percent / 100, track_expense.amount * track_expense.percent / 100 ), 2) as effective_amount";
            push @$select, "IF( album_expense.expense_id, album_expense.memo, track_expense.memo ) as memo";
            push @$select, "IF( album_expense.expense_id, album_expense.processed, track_expense.processed )as processed";
        }
    }

    if ( $args{withIncomeSources} ) {
        push @$select,
'IF( license_income_type.license_income_type_id, "License Income", IF( expense_type.expense_type_id, "Recoupable Expense", NULL ) ) as source_type';
        push @$select, 'license_income_type.name as income_type';
        push @$select, 'artist_contract_license_income.percent as net_revenue_rate';
        push @$select, 'expense_name.name as expense_type';
        push @$select, 'expense_type.percent as recoupable_percent';
    }

}

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

    push @$joins, "LEFT JOIN payor ON (new_artist_contract.payor_id = payor.payor_id)";
    push @$joins, "LEFT JOIN artist_payee ON (new_artist_contract.artist_payee_id = artist_payee.artist_payee_id)";

    if ( $args{withTerms} ) {
        push @$joins,
          "LEFT JOIN new_artist_contract_term ON (new_artist_contract.artist_contract_id = new_artist_contract_term.artist_contract_id)";

        #        push @$joins, "LEFT JOIN income_source ON (new_artist_contract_term.income_source_id = income_source.income_source_id)";
        push @$joins, "LEFT JOIN region ON (new_artist_contract_term.region_id = region.region_id)";
        push @$joins, "LEFT JOIN channel ON (new_artist_contract_term.channel_id = channel.channel_id)";

#        push @$joins, "LEFT JOIN price_level ON (new_artist_contract_term.price_level_id = price_level.price_level_id)";
#        push @$joins, "LEFT JOIN contract_rate_type ON (new_artist_contract_term.contract_rate_type_id = contract_rate_type.contract_rate_type_id)";
    }

    # !!! We are 'smashing' the track contracts and album contracts together here.
    # !!! In other words, if there is an album and a track attached, we get just get 1 line, not two.
    # !!! This is wrong.
    # !!! It seems like I just can't do what I want to do here in a single query.
    # !!! So rather than attempt this wacky join stuff, I'll just have to make multiple queries in the Report class.
    #
    if ( $args{withAttached} ) {
        push @$joins, "LEFT JOIN album_contract ON (new_artist_contract.artist_contract_id = album_contract.artist_contract_id)";
        push @$joins, "LEFT JOIN album ON (album_contract.album_id = album.album_id)";
        push @$joins, "LEFT JOIN artist as album_artist ON (album_artist.artist_id = album.artist_id)";
        push @$joins, "LEFT JOIN label ON (album.label_id = label.label_id)";
        push @$joins, "LEFT JOIN track_contract ON (new_artist_contract.artist_contract_id = track_contract.artist_contract_id)";
        push @$joins, "LEFT JOIN track ON (track_contract.track_id = track.track_id)";
        push @$joins, "LEFT JOIN artist as track_artist ON (track_artist.artist_id = track.artist_id)";
        push @$joins, "LEFT JOIN master ON (track.master_id = master.master_id)";
        if ( $args{unprocessedExpenses} || $args{processedExpenses} ) {
            my $albumType = RPS::DB::Item::Expense::kParentAlbumContract;
            my $trackType = RPS::DB::Item::Expense::kParentTrackContract;

            push @$joins,
"LEFT JOIN expense as album_expense ON (album_contract.album_contract_id = album_expense.parent_id AND album_expense.parent_type = $albumType)";
            push @$joins,
              "LEFT JOIN expense_type as album_expense_type ON (album_expense.expense_type_id = album_expense_type.expense_type_id)";
            push @$joins,
              "LEFT JOIN expense_name as album_expense_name ON (album_expense_type.expense_name_id = album_expense_name.expense_name_id)";
            push @$joins,
"LEFT JOIN expense as track_expense ON (track_contract.track_contract_id = track_expense.parent_id AND track_expense.parent_type = $trackType)";
            push @$joins,
              "LEFT JOIN expense_type as track_expense_type ON (track_expense.expense_type_id = track_expense_type.expense_type_id)";
            push @$joins,
              "LEFT JOIN expense_name as track_expense_name ON (track_expense_type.expense_name_id = track_expense_name.expense_name_id)";
        }
    }

}

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

    if ( $args{withIncomeSources} ) {
        push @$joins,
          [
            "LEFT JOIN artist_contract_license_income ON new_artist_contract.artist_contract_id = artist_contract_license_income.artist_contract_id AND artist_contract_license_income.inactive = 0",
            "LEFT JOIN license_income_type ON artist_contract_license_income.license_income_type_id = license_income_type.license_income_type_id "
          ];

        push @$joins,
          [
            "LEFT JOIN expense_type ON new_artist_contract.artist_contract_id = expense_type.artist_contract_id AND expense_type.inactive = 0",
            "LEFT JOIN expense_name ON expense_type.expense_name_id = expense_name.expense_name_id"
          ];
    } else {
        push @$joins, [""];
    }
}

sub _BuildGroupBy {
    my ( $class, $groupBy, %args ) = @_;
}

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

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

    push @$where, "new_artist_contract.deleted = 0";
    if ($dateType) {
        if ($startDate) {
            push @$where, "TO_DAYS(new_artist_contract.$dateType) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate) {
            push @$where, "TO_DAYS(new_artist_contract.$dateType) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }
    }
}

sub _BuildOrderBy {
    my ( $class, $orderBy, %args ) = @_;
    push @$orderBy, "title";
    push @$orderBy, "rs_contract_id";
    push @$orderBy, "payee";
    push @$orderBy, "artist_client_account_id";

    if ( $args{withIncomeSources} ) {
        push @$orderBy, "source_type";
    }

    if ( $args{withTerms} ) {
        push @$orderBy, "priority";
    }
}

# This method cleans out tables in the select that are referenced in the ujoin
# list, but not in the current union join.
sub _cleanSelect {
    my $currentJoin = shift;
    my $unionJoins  = shift;
    my $selectList  = shift;
    my @list        = @$selectList;

    # If there is only one join then we don't need to worry about cleaning out
    # missing references.
    return @$selectList if ( scalar @$unionJoins <= 1 );

    foreach my $uJoin (@$unionJoins) {
        next if ( _joinsMatch( $uJoin, $currentJoin ) );

        _scrubSelect( $currentJoin, $uJoin, \@list );
    }

    @list;
}

sub _scrubSelect {
    my $currentJoin = shift;
    my $unionJoin   = shift;
    my $selectList  = shift;

    my @joinTables    = _getTables($unionJoin);
    my @currentTables = _getTables($currentJoin);

    foreach my $select (@$selectList) {
        foreach my $table (@joinTables) {
            next if ( grep( /^$table$/, @currentTables ) );

            if ( $select =~ /\b$table\b/ ) {
                $select =~ s/$table\.[\w\d_]+/NULL/;
            }
        }
    }
}

sub _joinsMatch {
    my $lJoin = shift;
    my $rJoin = shift;

    my $lc = new List::Compare( $lJoin, $rJoin );

    return $lc->is_LequivalentR;
}

sub _getTables {
    my $joins = shift || return;
    my @tables;

    foreach my $j (@$joins) {
        push( @tables, _getTable($j) );
    }

    return @tables;
}

sub _getTable {
    my $join = shift || return;

    $join =~ /JOIN\s([\w\d_]+)/;

    return $1;
}

1;