#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# Copyright (C) 2011 RoyaltyShare, Inc.   All Rights Reserved
#---------------------------------------------------------------

package RPS::DB::Item::DynamicReport::Licenses::USCABase;

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 base 'RPS::DB::Item::DynamicReport';

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


# We'll return the same columns for both types of license reports.
# 
sub _config
{
    my ($class) = @_;

    return
    {
        'type'              => {},
        'track_title'       => {},
        'album_title'       => {},
        'album_title_version' => {},
        'catalog_number'    => {},
        'album_artist'      => {},
        'track_artist'      => {},
        'track_order'       => {},
        'duration'          => {},
        'label_name'        => {},
        'product_type_id'   => {},
        'product_type_description'   => {},
        'isrc'              => {},
        'region'            => {},
        'share'             => {},
        'payor'             => {},
        'publisher_name'    => {},
        'publisher_id'      => {},
        'publisher_client_account_id' => {},
        'admin_id'          => {},
        'agent_id'          => {},
        'publisher_direct'  => {},
        'issuer_license_id' => {},
        'issuer_song_id'    => {},
        'crossed'           => {},
        'status'            => {},
        'mechanical_exempt' => {},
        'date_sent'         => {},
        'date_received'     => {},
        'date_issued'       => {},
        'term_start'        => {},
        'term_end'          => {},
        'contract_title'    => {},
        'track_cap'         => {},
        'rate_type'         => {},
        'rate_percent'      => {},
        'rate_basis'        => {},
        'lock_date'         => {},
        'penny_rate'        => {},
        'reserve_percent'   => {},
        'digital_reserves'  => {},
        'controlled_composition_id'  => {},
        'percent_of_sales'  => {},
        'free_goods'        => {},
        'misc_deduction'    => {},
        'comments'          => {},
        'date_created'      => {},
        'created_by'        => {},
        'date_modified'     => {},
        'modified_by'       => {},
        'rs_id'             => {},
    };
}



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

    my $tempTableName = $class->_BuildProductTypeTempTable(%args);
    $args{_productTypeTable} = $tempTableName;

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

    my @joins;
    $class->_BuildJoins(\@joins, %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 = 'SELECT ' . join(',', @select) . ' FROM ' . join(',', @from);

    if (scalar @joins)
    {
        $sql .= ' ' . join(' ', @joins);
    }

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

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

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

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



sub _BuildFrom
{
    my ($class, $from, %args) = @_;
    
    push @$from, $class->_TrackLicenseTable();
}

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

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

    push @$orderBy, 'album_title';
    push @$orderBy, 'catalog_number';
    push @$orderBy, 'track_order';
    push @$orderBy, 'publisher_name';
    push @$orderBy, 'product_type_description';

}

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

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

    my $table = $class->_TrackLicenseTable();

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



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

    my $licenseTable = $class->_TrackLicenseTable();
    my $publisherTable = $class->_PublisherTable();
    my $licenseKey = $class->_TrackLicenseKey();
    my $publisherTableKey = $class->_PublisherTableKey();

    my $mechanicalExemptColumn = $class->_ExemptColumn();

    my $tempTable = $args{_productTypeTable};

    push @$select, "$licenseTable.date_created AS date_created";
    push @$select, "$licenseTable.date_modified AS date_modified";
    push @$select, "$licenseTable.created_by AS created_by";
    push @$select, "$licenseTable.modified_by AS modified_by";
    push @$select, "$licenseTable.$licenseKey AS rs_id";

    push @$select, "$licenseTable.type AS type";
    push @$select, "track.title AS track_title";
    push @$select, "track.track_order AS track_order";
    push @$select, "album.title AS album_title";
    push @$select, "album.title_version AS album_title_version";
    push @$select, "album.catalog_number AS catalog_number";
    push @$select, "album_artist_table.name AS album_artist";
    push @$select, "track_artist_table.name AS track_artist";
    push @$select, "label.label_name AS label_name";
    push @$select, "master.isrc AS isrc";
    push @$select, "master.duration AS duration";
    push @$select, "$licenseTable.product_type_id AS product_type_id";
    push @$select, "region.name as region";
    push @$select, "$licenseTable.share AS share";
    push @$select, "payor.name as payor";
    push @$select, "$publisherTable.publisher_name as publisher_name";
    push @$select, "$publisherTable.$publisherTableKey as publisher_id";
    push @$select, "$publisherTable.client_account_id as publisher_client_account_id";
    push @$select, "$publisherTable.agent_id as agent_id";
    push @$select, "$publisherTable.admin_id as admin_id";
#    push @$select, "admin_table.publisher_name as admin";
#    push @$select, "agent_table.publisher_name as agent";
    push @$select, "IF($licenseTable.publisher_direct = 1, 'Y', 'N') as publisher_direct";

    $class->_BuildSelectIssuerColumns($select);

    push @$select, "IF($licenseTable.cross_collateralized = 1, 'Y', 'N') as crossed";
    push @$select, "IF(track.$mechanicalExemptColumn = 1, 'Y', 'N') as mechanical_exempt";
    push @$select, "IF($licenseTable.inactive = 1, 'Inactive', 'Active') as status";
    push @$select, "$licenseTable.date_sent as date_sent";
    push @$select, "$licenseTable.date_received as date_received";
    push @$select, "$licenseTable.date_issued as date_issued";
    push @$select, "$licenseTable.term_start as term_start";
    push @$select, "$licenseTable.term_end as term_end";
    push @$select, "new_artist_contract.title as contract_title";
    push @$select, "controlled_composition.track_cap as track_cap";
    push @$select, "controlled_composition.controlled_composition_id as controlled_composition_id";
    push @$select, "IF($licenseTable.type = 4, '', IF($licenseTable.type = 2, controlled_composition.rate_type, $licenseTable.rate_type)) as rate_type";
    push @$select, "IF($licenseTable.type = 4, '', IF($licenseTable.type = 2, controlled_composition.rate_percentage, $licenseTable.rate_percentage)) as rate_percent";
    push @$select, "IF($licenseTable.type = 4, '', IF($licenseTable.type = 2, controlled_composition.rate_basis, $licenseTable.rate_basis)) as rate_basis";
    push @$select, "IF($licenseTable.type = 2, controlled_composition.lock_date, $licenseTable.lock_date) as lock_date";
    push @$select, "IF($licenseTable.type = 2, controlled_composition.penny_rate, $licenseTable.penny_rate) as penny_rate";
    push @$select, "IF($licenseTable.type = 2, controlled_composition.reserve_percentage, $licenseTable.reserve_percentage) as reserve_percent";
    push @$select, "IF($licenseTable.digital_reserves_enabled = 1, 'Y', 'N') as digital_reserves";

    # !!! Will need to build this out in the join.
    # !!! all types of schedules live in the reserve_liquidation table.
    # !!! We've got different types in there - Not sure how I will be able to make this join work,
    # !!! because it will be based on the type of the license (CA, US, ccomp, ringtone).
    # !!! I could punt and query that table seperately, but that seems like a cop-out.
    # !!! Still, it might end up less incomprehensible if I just query the entire reserve_liquidation table
    # !!! at the beginning of the report, and do what is required as I spew out each line.

#    push @$select, "liquidation_1.percent as ls_1";
#    push @$select, "liquidation_2.percent as ls_2";
#    push @$select, "liquidation_3.percent as ls_3";
#    push @$select, "liquidation_4.percent as ls_4";
#    push @$select, "liquidation_5.percent as ls_5";
#    push @$select, "liquidation_6.percent as ls_6";
#    push @$select, "liquidation_7.percent as ls_7";
#    push @$select, "liquidation_8.percent as ls_8";
#    push @$select, "'1' as ls_1";
#    push @$select, "'2' as ls_2";
#    push @$select, "'3' as ls_3";
#    push @$select, "'4' as ls_4";
#    push @$select, "'5' as ls_5";
#    push @$select, "'6' as ls_6";
#    push @$select, "'7' as ls_7";
#    push @$select, "'8' as ls_8";

    push @$select, "$licenseTable.percentage_of_sales as percent_of_sales";
    push @$select, "IF($licenseTable.free_goods <> 0, $licenseTable.free_goods, '0') as free_goods";
    push @$select, "IF($licenseTable.misc_deduction <> 0, $licenseTable.misc_deduction, '0') as misc_deduction";
    push @$select, "$licenseTable.comments as comments";

    push @$select, "IF($licenseTable.product_type_id, $tempTable.description, 'All') as product_type_description";
#    push @$select, "$tempTable.description as product_type_description";
}




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

    my $licenseTable = $class->_TrackLicenseTable();
    my $licenseKey = $class->_TrackLicenseKey();
    my $publisherTable = $class->_PublisherTable();
    my $publisherTableKey = $class->_PublisherTableKey();
    my $controlledCompFlag = $class->_ControlledCompFlag();

    my $tempTable = $args{_productTypeTable};

    push @$joins, "LEFT JOIN track ON ($licenseTable.track_id = track.track_id)";
    push @$joins, "LEFT JOIN album ON (track.album_id = album.album_id)";
    push @$joins, "LEFT JOIN label ON (album.label_id = label.label_id)";
    push @$joins, "LEFT JOIN master ON (track.master_id = master.master_id)";
    push @$joins, "LEFT OUTER JOIN artist AS track_artist_table ON (track.artist_id = track_artist_table.artist_id)";
    push @$joins, "LEFT OUTER JOIN artist AS album_artist_table ON (album.artist_id = album_artist_table.artist_id)";
    push @$joins, "LEFT JOIN region ON ($licenseTable.region_id = region.region_id)";
    push @$joins, "LEFT JOIN payor ON ($licenseTable.payor_id = payor.payor_id)";
    push @$joins, "LEFT JOIN $publisherTable ON ($licenseTable.$publisherTableKey = $publisherTable.$publisherTableKey)";

    # !!! These are not keys, so this might not be a good idea...
    #
#    push @$joins, "LEFT JOIN $publisherTable as admin_table ON ($publisherTable.$publisherTableKey = $publisherTable.admin_id)";
#    push @$joins, "LEFT JOIN $publisherTable as agent_table ON ($publisherTable.$publisherTableKey = $publisherTable.agent_id)";

    # !!! Will this work?
    #
    push @$joins, "LEFT JOIN album_contract ON ($licenseTable.type = 2 && album_contract.$controlledCompFlag = 1 && track.album_id = album_contract.album_id)";
    push @$joins, "LEFT JOIN new_artist_contract ON ($licenseTable.type = 2 && album_contract.artist_contract_id = new_artist_contract.artist_contract_id)";
    push @$joins, "LEFT JOIN controlled_composition ON ($licenseTable.type = 2 && controlled_composition.artist_contract_id = new_artist_contract.artist_contract_id)";

    push @$joins, "LEFT JOIN $tempTable ON ($tempTable.product_type_id = $licenseTable.product_type_id)";

    # !!! Now for the reserve liquidation schedule...
#    $class->_BuildReserveJoins($joins, %args);
}

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

    my $licenseTable = $class->_TrackLicenseTable();
    my $licenseKey = $class->_TrackLicenseKey();
    my $reserveType = $class->_ReserveType();

    for (my $i = 1; $i <= 8; $i++)
    {
        push @$joins, "LEFT JOIN reserve_liquidation as liquidation_$i ON ( liquidation_$i.period = $i && ( ($licenseTable.type = 1 && liquidation_$i.type = $reserveType && liquidation_$i.id = $licenseTable.$licenseKey) || ($licenseTable.type = 2 && liquidation_$i.type = 2 && liquidation_$i.id = controlled_composition.controlled_composition_id) || ($licenseTable.type = 5 && liquidation_$i.type = 6 && liquidation_$i.id = $licenseTable.$licenseKey)))";
    }
}


sub _BuildProductTypeTempTable
{
    my ($class, %args) = @_;
    my $tempTableName = 'zz_product_type_'.$$;

    # We will also need placeholders for the 'All' types.
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "CREATE TEMPORARY TABLE $tempTableName "
     . ' (`product_type_id` int(10) unsigned PRIMARY KEY,'
     . '  `description` varchar(255) )';
    $dbo->DoCmd($sql);

    my $allTypes = RPS::DB::Item::ProductType->GetAll();
    while (my $type = $allTypes->next())
    {
        $dbo->DoCmd("INSERT INTO $tempTableName SET product_type_id=".$type->product_type_id().", description='".$type->description()."'");
    }

    # Can't have a NULL key, so I'll use '0'.  Might work...
    #
    $dbo->DoCmd("INSERT INTO $tempTableName SET product_type_id=0, description='All'");
    $dbo->DoCmd("INSERT INTO $tempTableName SET product_type_id=255, description='All Physical'");
    $dbo->DoCmd("INSERT INTO $tempTableName SET product_type_id=254, description='All Digital'");

    return $tempTableName;
}


1;