#------------------------------------------------------------
# Copyright (C) 2012 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::DynamicReport::LicenseIncome::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::LicenseIncome;

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

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

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

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

    return {
        'file_name'                    => {},
        'date'                         => {},
        'units'                        => {},
        'amount'                       => {},
        'album_title'                  => {},
        'catalog_number'               => {},
        'track_title'                  => {},
        'contract_title'               => {},
        'contract_id'                  => {},
        'income_type'                  => {},
        'memo'                         => {},
        'period_name'                  => {},
        'period_start_date'            => {},
        'period_end_date'              => {},
        'period_id'                    => {},
        'sale_id'                      => {},

        'file_id'                      => {},
        'client_album_id'              => {},
        'album_id'                     => {},
        'isrc'                         => {},
        'track_id'                     => {},
        'artist_contract_id'           => {},
        'payor_name'                   => {},
        'payee_name'                   => {},
        'client_account_id'            => {},
        'payee_id'                     => {},
        'license_income_date_created'  => {},
        'license_income_created_by'    => {},
        'license_income_date_modified' => {},
        'license_income_modified_by'   => {},
        'license_income_id'            => {},
        'album_custom_1'               => {},
        'album_custom_2'               => {},
        'album_custom_3'               => {},
        'track_custom_1'               => {},
        'track_custom_2'               => {},
        'track_custom_3'               => {},
        'license_income_line_num'      => {},

    };
}

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

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

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

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

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

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

    $sql .= "\n" . join( "\n", @joins )             if @joins;
    $sql .= "\nWHERE " . join( "\n  AND ", @where ) if @where;
    $sql .= ' ORDER BY file_name, line_num, period_id';

    $sql = qq/
        SELECT
            t.*,
            IF( \@fname = t.file_name, \@rnum := \@rnum + 1, \@rnum := 1 ) AS license_income_line_num,
            \@fname := t.file_name
        FROM (
            $sql
        ) AS t
        INNER JOIN (SELECT \@rnum := 0, \@fname := NULL) vars
    /;

    Log->debug("QUERY: $sql");

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

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

    push @$select, 'license_income.sale_id                     AS sale_id';
    push @$select, 'license_income.units                       AS units';
    push @$select, 'license_income.revenue                     AS amount';
    push @$select, 'license_income_type.name                   AS income_type';
    push @$select, 'license_income.memo                        AS memo';
    push @$select, 'file.orig_file_name                        AS file_name';
    push @$select, 'sale.line_num                              AS line_num';
    push @$select, 'sale.date_end                              AS date';
    push @$select, 'album.title                                AS album_title';
    push @$select, 'album.catalog_number                       AS catalog_number';
    push @$select, 'track.title                                AS track_title';
    push @$select, 'new_artist_contract.title                  AS contract_title';
    push @$select, 'new_artist_contract.client_contract_id     AS contract_id';
    push @$select, 'period.name                                AS period_name';
    push @$select, 'period.period_id                           AS period_id';
    push @$select, 'period.start_date                          AS period_start_date';
    push @$select, 'period.end_date                            AS period_end_date';

    push @$select, 'license_income.file_id                     AS file_id';
    push @$select, 'album.client_album_id                      AS client_album_id';
    push @$select, 'album.album_id                             AS album_id';
    push @$select, 'master.isrc                                AS isrc';
    push @$select, 'track.track_id                             AS track_id';
    push @$select, 'new_artist_contract.artist_contract_id     AS artist_contract_id';
    push @$select, 'payor.name                                 AS payor_name';
    push @$select, 'artist_payee.name                          AS payee_name';
    push @$select, 'artist_payee.client_account_id             AS client_account_id';
    push @$select, 'new_artist_contract.artist_payee_id        AS payee_id';
    push @$select, 'license_income.date_created                AS license_income_date_created';
    push @$select, 'license_income.created_by                  AS license_income_created_by';
    push @$select, 'license_income.date_modified               AS license_income_date_modified';
    push @$select, 'license_income.modified_by                 AS license_income_modified_by';
    push @$select, 'license_income.license_income_id           AS license_income_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 _BuildFrom {
    my ( $class, $from, %args ) = @_;

    push @$from, 'license_income';
}

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

    push @$joins, "JOIN file ON (license_income.file_id = file.file_id)";
    push @$joins, "JOIN sale ON (license_income.sale_id = sale.sale_id)";
    push @$joins, "JOIN period ON (period.period_id = file.period_id)";
    push @$joins, "JOIN license_income_type USING (license_income_type_id)";
    push @$joins, "LEFT JOIN new_artist_contract ON (license_income.contract_id = new_artist_contract.artist_contract_id and new_artist_contract.deleted = 0)";
    push @$joins, "LEFT JOIN album ON (license_income.album_id = album.album_id)";
    push @$joins, "LEFT JOIN track ON (license_income.track_id = track.track_id)";
    push @$joins, "LEFT JOIN master ON (track.master_id = master.master_id)";
    push @$joins, "LEFT JOIN artist_payee ON (new_artist_contract.artist_payee_id = artist_payee.artist_payee_id)";
    push @$joins, "LEFT JOIN payor ON (new_artist_contract.payor_id = payor.payor_id)";

}

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

    my $dateType  = $args{dateType};
    my $startDate = $args{startDate} ? $class->quote($args{startDate}) : '';
    my $endDate   = $args{endDate}   ? $class->quote($args{endDate})   : '';

    if ( $dateType =~ /^By Date$/i ) {
        push @$where, "TO_DAYS(sale.date_end) >= TO_DAYS($startDate)" if $startDate;
        push @$where, "TO_DAYS(sale.date_end) <= TO_DAYS($endDate)"   if $endDate;
    } elsif ( $dateType =~ /^Date Created$/i ) {
        push @$where, "TO_DAYS(license_income.date_created) >= TO_DAYS($startDate)" if $startDate;
        push @$where, "TO_DAYS(license_income.date_created) <= TO_DAYS($endDate)"   if $endDate;
    } elsif ( $dateType =~ /^Date Modified$/i ) {
        push @$where, "TO_DAYS(license_income.date_modified) >= TO_DAYS($startDate)" if $startDate;
        push @$where, "TO_DAYS(license_income.date_modified) <= TO_DAYS($endDate)"    if $endDate;
    }

}

1;