#------------------------------------------------------------
# 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'      => {},
        'album_artist'    => {},
        'label_name'      => {},
        'track_title'     => {},
        'track_artist'    => {},
        'track_minutes'   => {},
        'track_seconds'   => {},
        'proration'       => {},
        'contract_status' => {},
        'crossed'         => {},

        # 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, "new_artist_contract_term.rate";
        push @$select, "new_artist_contract_term.rate_reduction";
        push @$select, "new_artist_contract_term.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, '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, '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, "all", \N))) as channel';
#        push @$select, "price_level.name as price";
        push @$select, "new_artist_contract_term.price_level_id as price_level_id";

        push @$select, "new_artist_contract_term.contract_rate_type_id 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" ) ), \N ) ) 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" ), \N ) ) 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", \N ) ) 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)",
                        "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)",
                        "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};

    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;