#------------------------------------------------------------
# Copyright (C) 2012 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::DynamicReport::Sales::ByRoyaltyRun;

use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::RSApp;
use Common::Log;
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;
use Data::Dumper;

use lib '/app/tools/rps/lib';

use RPS::DB::Item::SaleRunMap;

# !!! Not sure if I can actually inherit from 'Base' or not.
# !!! They do return the same columns, and support the same options.
# !!! But, this class queries sale_run_map primarily, and joins sale.
# !!! Still, it might work...
use base 'RPS::DB::Item::DynamicReport::Sales::Base';

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

# Before we do the 'real' query, we need to build a temporary table to contain all the sale_ids, gleaned
# from the sale_run_map.   Using a temp table avoids having to do a dependent subquery, which would be
# extrememly slow on large data sets.
#
sub ReportQuery {
    my ( $class, %args ) = @_;
    my $showByType = $args{showByType};

    # Create a reasonable unique table name using our PID.
    #
    my $tempTable = 'zz_sale_report_sale_ids_' . $$;
    $class->_BuildTemporarySaleIDTable( $tempTable, %args );

    $args{_tempTableName} = $tempTable;
    $class->SUPER::ReportQuery(%args);
}

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

    my $dbo     = Common::RSApp::GetClientDB();
    my $runID   = $args{runID};
    my $runType = $class->_getRunType(%args);
    my $payeeID = $args{payeeID} || 0;

    my $sql = '';

    if ( $args{runType} =~ /^(?:US|CA) Mechanicals$/i ) {
        $sql = _getMechanicalQuery($class, $tableName, $runID, $runType, $payeeID, \%args);
    } elsif ( $args{runType} =~ /^(?:Artist\/Producer|Label)$/i ) {
        $sql = _getArtistOrLabelQuery($class, $tableName, $runID, $runType, $payeeID, \%args);
    } else {
        $runID   = $class->quote($runID);
        $runType = $class->quote($runType);
        $sql = qq/
            CREATE TEMPORARY TABLE $tableName
                SELECT DISTINCT
                    srm.sale_id
                FROM sale_run_map AS srm
                WHERE 1
                    AND srm.run_id   = $runID
                    AND srm.run_type = $runType
                    AND srm.status   = 'paid'
        /;
    }

    $dbo->DoCmd($sql) if $sql;
}

sub _getMechanicalQuery {
    my ($class, $tableName, $runID, $runType, $payeeID, $hArgs) = @_;

    my %runTypePrefixes = (
        'US Mechanicals' => {
            prefix => '',
        },
        'CA Mechanicals' => {
            prefix => 'ca_',
        },
    );
    return unless exists $runTypePrefixes{ $hArgs->{runType} };

    $runID   = $class->quote($runID);
    $runType = $class->quote($runType);

    my $sql = '';
    # old mechanical runs
    if ( $hArgs->{runIDFlag} =~ /^old$/i ) {
        $sql = qq/
            CREATE TEMPORARY TABLE $tableName
            SELECT DISTINCT
                NULL AS 'publisher',
                NULL AS 'publisher_client_no',
                NULL AS 'rs_publisher_payee_id',
                NULL AS 'agent',
                NULL AS 'agent_client_no',
                NULL AS 'rs_agent_payee_id',
                NULL AS 'admin',
                NULL AS 'admin_client_no',
                NULL AS 'rs_admin_payee_id',
                IF(
                    mr.label != '',
                    mr.label,
                    CONCAT_WS(' - ', mr.start_date, mr.end_date)
                )                                     AS 'run_name',
                pa.name                               AS 'payor_name',
                srm.sale_id
            FROM sale_run_map AS srm
            INNER JOIN %PREFIX%mechanical_run AS mr ON srm.run_id = mr.%PREFIX%mechanical_run_id
            LEFT JOIN payor AS pa ON mr.payor_id = pa.payor_id
            WHERE 1
                AND srm.run_id   = $runID
                AND srm.run_type = $runType
                AND srm.status   = 'paid'
        /;
    }
    # new mechanical runs
    elsif ( $hArgs->{runIDFlag} =~ /^new$/i ) {
        my $where = '';
        if ( $hArgs->{payeeDetail} && $hArgs->{payeeDetail} =~ /^Single Payee$/i ) {
            my $publisherAdminAgencyIDs = _getPublisherAdminOrAgentId(
                $class, $hArgs->{payeeID}, $runTypePrefixes{ $hArgs->{runType} }{prefix}
            );
            $where .= " AND pu.%PREFIX%publisher_id IN ($publisherAdminAgencyIDs) ";
        }

        $sql = qq/
            CREATE TEMPORARY TABLE $tableName
            SELECT DISTINCT
                pu.publisher_name                     AS 'publisher',
                IFNULL(pu.client_account_id, '')      AS 'publisher_client_no',
                pu.%PREFIX%publisher_id               AS 'rs_publisher_payee_id',
                IFNULL(pag.publisher_name, '')        AS 'agent',
                IFNULL(pag.client_account_id, '')     AS 'agent_client_no',
                IFNULL(pag.%PREFIX%publisher_id, '')  AS 'rs_agent_payee_id',
                IFNULL(pad.publisher_name, '')        AS 'admin',
                IFNULL(pad.client_account_id, '')     AS 'admin_client_no',
                IFNULL(pad.%PREFIX%publisher_id, '')  AS 'rs_admin_payee_id',
                -- ca doesn't use label
                IF(
                    mr.label != '',
                    mr.label,
                    CONCAT_WS(' - ', mr.start_date, mr.end_date)
                )                                     AS 'run_name',
                pa.name                               AS 'payor_name',
                -- should be at the very end
                srm.sale_id
            FROM sale_run_map AS srm
            INNER JOIN %PREFIX%mechanical_run AS mr ON srm.run_id = mr.%PREFIX%mechanical_run_id
            INNER JOIN %PREFIX%mechanical_statement_item AS msi ON srm.statement_item_id = msi.%PREFIX%mechanical_statement_item_id
            INNER JOIN %PREFIX%mechanical_statement AS ms ON msi.%PREFIX%mechanical_statement_id = ms.%PREFIX%mechanical_statement_id
            LEFT JOIN payor AS pa ON ms.payor_id = pa.payor_id
            INNER JOIN %PREFIX%publisher AS pu  ON msi.%PREFIX%publisher_id = pu.%PREFIX%publisher_id
            LEFT JOIN  %PREFIX%publisher AS pag ON pu.agent_id = pag.%PREFIX%publisher_id
            LEFT JOIN  %PREFIX%publisher AS pad ON pu.admin_id = pad.%PREFIX%publisher_id
            WHERE 1
                AND srm.run_id   = $runID
                AND srm.run_type = $runType
                AND srm.status   = 'paid'
                $where
        /;
    }

    my $prefix = $runTypePrefixes{ $hArgs->{runType} }{prefix};
    $sql =~ s/%PREFIX%/$prefix/g;

    return $sql;
}

sub _getPublisherAdminOrAgentId {
    my ($class, $publisherID, $prefix) = @_;

    $publisherID = $class->quote( $publisherID || 0 );
    my $sql = qq/
        SELECT GROUP_CONCAT(DISTINCT %PREFIX%publisher_id) AS ids
        FROM %PREFIX%publisher
        WHERE admin_id = $publisherID OR agent_id = $publisherID
    /;
    $sql =~ s/%PREFIX%/$prefix/g;

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmd($sql);
    my ($result) = $sth->fetchrow_array() if ($sth);

    return $result ? "$publisherID,$result" : $publisherID;
}

sub _getArtistOrLabelQuery {
    my ($class, $tableName, $runID, $runType, $payeeID, $hArgs) = @_;

    my %runTypePrefixes = (
        'Artist/Producer' => {
            prefix => 'artist',
        },
        'Label' => {
            prefix => 'label',
        },
    );
    return unless exists $runTypePrefixes{ $hArgs->{runType} };

    $runID   = $class->quote($runID);
    $runType = $class->quote($runType);

    my $innerJoins = '';
    if ( $hArgs->{runType} eq 'Label' ) {

        $innerJoins .= qq/
            INNER JOIN label_royalty_label_item AS lrli ON srm.statement_item_id = lrli.label_royalty_label_item_id
            INNER JOIN %PREFIX%_royalty_statement AS ars ON lrli.label_royalty_statement_id = ars.%PREFIX%_royalty_statement_id
        /;
    } else {
        $innerJoins .= qq/
            INNER JOIN artist_royalty_income_item AS arii ON srm.statement_item_id = arii.artist_royalty_income_item_id
            INNER JOIN artist_royalty_album AS ara ON arii.artist_royalty_album_id = ara.artist_royalty_album_id
            INNER JOIN artist_royalty_statement AS ars ON ara.artist_royalty_statement_id = ars.artist_royalty_statement_id
        /;
    }

    my $where = '';
    if ( $hArgs->{payeeDetail} && $hArgs->{payeeDetail} =~ /^Single Payee$/i ) {
        $where .= "AND ars.payee_id = " . $class->quote( $hArgs->{payeeID} )
    }

    my $sql = qq/
        CREATE TEMPORARY TABLE $tableName
        SELECT DISTINCT
            ap.name              AS 'payee',
            ap.client_account_id AS 'client-account-no',
            ap.%PREFIX%_payee_id AS 'rs_payee_id',
            arr.label            AS 'run_name',
            pa.name              AS 'payor',
            -- should be at the very end
            srm.sale_id
        FROM sale_run_map AS srm
        $innerJoins
        INNER JOIN %PREFIX%_payee AS ap  ON ars.payee_id = ap.%PREFIX%_payee_id
        LEFT JOIN %PREFIX%_royalty_run AS arr ON srm.run_id = arr.%PREFIX%_royalty_run_id
        LEFT JOIN payor AS pa  ON ars.payor_id = pa.payor_id
        WHERE 1
            AND srm.run_id   = $runID
            AND srm.run_type = $runType
            AND srm.status   = 'paid'
            $where
    /;

    my $prefix = $runTypePrefixes{ $hArgs->{runType} }{prefix};
    $sql =~ s/%PREFIX%/$prefix/g;

    return $sql;
}

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

    my $tempTable = $args{_tempTableName};
    if ($tempTable) {
        push @$joins, "INNER JOIN $tempTable USING (sale_id)";
    }

    return $class->SUPER::_BuildJoins( $joins, %args );
}

1;