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

# This is the base class for the ArtistProducer and Label reports.

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::FinanceTransaction;

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

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

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

sub _config {
    my ($class) = @_;
    return {
        'publisher'          => {},
        'client_account_no'  => {},
        'publisher_id'       => {},
        'publisher_status'   => {},
        'publisher_type'     => {},
        'agent_name'         => {},
        'admin_name'         => {},
        'license_count'      => {},
        'payor_name'         => {},
        'payor_id'           => {},
        'client_payor_no'    => {},
        'payor_status'       => {},
        'default_payor'      => {},
        'min_payment'        => {},
        't_status'           => {},
        't_type'             => {},
        't_amount'           => {},
        't_date'             => {},
        't_check_no'         => {},
        't_memo'             => {},
        't_balance'          => {},
        't_date_created'     => {},
        't_date_modified'    => {},
        't_id'               => {},
        'finance_account_id' => {},
    };

}

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

    # Since we're possibly combinine the contents of two transaction tables,
    # we've got to build two queries and possibly union them.
    #

    # And... we also need to take the 'publisher_indirect_balance_account' into account for
    # pending transactions that will end up being reported via an admin or agent.
    # I think that's going to be another set of queries unioned together.
    #

    my @queries;
    push @queries, $class->_BuildQuery( 'pending_transaction', %args )
      if ( $args{showUnprocessed} );

    push @queries, $class->_BuildQuery( 'finance_transaction', %args )
      if ( $args{showProcessed} );

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

    my $sql;
    if ( scalar @queries > 1 ) {
        $sql = join( ' UNION ', @queries );
        $sql = "SELECT * FROM ( $sql ) as subq ";
    } else {
        $sql = $queries[0];
    }

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

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

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

    return $class->_BuildQueryRegular( $tType, %args ) . ' UNION ' . $class->_BuildQueryIndirect( $tType, %args );
}

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

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

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

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

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

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

    my $sql;

    $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 );
    }

    return $sql;
}

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

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

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

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

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

    # Both the 'direct' and 'indirect' queries start with the same tables (the actual transaction table)
    #
    my @from;
    $class->_BuildFrom( \@from, $tType, %args );

    my $sql;

    $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 );
    }

    return $sql;
}

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

    push @$from, $tType;
}

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

    return $class->_BuildSelectActual( $select, $t, $class->_publisher(), $class->_publisherAccount() );
}

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

    # Passing '0' for the publisher account means that we don't care about any of those fields.
    # Right now we just look there for 'min_payment', which the indirect balance account doesn't have.
    #
    return $class->_BuildSelectActual( $select, $t, $class->_publisher(), 0 );
}

sub _BuildSelectActual {
    my ( $class, $select, $t, $publisher, $publisherAccount ) = @_;

    push @$select, (
        "$publisher.publisher_name as publisher",
        "$publisher.client_account_id as client_account_no",
        "$publisher.$publisher" . '_id as publisher_id',
        "IF( $publisher.status = 0, 'inactive', "
          . "IF( $publisher.status = 1, 'active', "
          . "IF( $publisher.status = 2, 'on hold', 'unknown' ) ) ) AS publisher_status",

        "IF( $publisher.is_agency = 1, 'agent', " . "IF( $publisher.is_admin = 1, 'admin', 'standard' ) ) as publisher_type",

        'payor.name as payor_name',
        'payor.payor_id as payor_id',
        "IF( payor.inactive, 'inactive', 'active' ) as payor_status",

        'payor.client_payor_id as client_payor_no',
        "IF( payor.is_default, 'yes', 'no' ) as default_payor",
        $publisherAccount
        ? "$publisherAccount.min_payment as min_payment"
        : "IF( $publisher.agent_id > 0, 'See Agent', IF( $publisher.admin_id > 0, 'See Admin', '')) as min_payment",
        ( $t eq 'finance_transaction' ? "'Processed'" : "'Pending'" ) . " as t_status",

        $class->_transactionTypeSQL($t) . ' as t_type',
        "$t.amount as t_amount",
        "$t.transaction_date as t_date",
        "$t.check_number as t_check_no",
        "$t.memo as t_memo",
        ( $t eq 'finance_transaction' ? "$t.balance" : "''" ) . " as t_balance",
        "$t.date_modified as t_date_created",
        "$t.date as t_date_modified",
        "$t." . $t . "_id as t_id",
        "$t.finance_account_id as finance_account_id",
        "IF( $publisher.agent_id, agent.publisher_name, '') as agent_name",
        "IF( $publisher.admin_id, admin.publisher_name, '') as admin_name",
    );

    # This bit of wackiness is to get a non-numeric field to sort numerically if it just contains digits, and
    # alphabetically otherwise.
    #
    push @$select, "IF($publisher.client_account_id REGEXP '^[[:digit:]]+" . '$'
      . "',LPAD($publisher.client_account_id,32,'0'),$publisher.client_account_id) as sort2";

    # JPK - Note the wacky mapping of dates.  That's just the way it is, sadly.
}

sub _transactionTypeSQL {
    my ( $class, $t ) = @_;

    return
        " IF( $t.type_code = "
      . RPS::DB::Item::FinanceTransaction::kTypeOpeningBalance
      . ", 'opening balance', "
      . " IF( $t.type_code = "
      . RPS::DB::Item::FinanceTransaction::kTypeAdjustment
      . ", 'adjustment', "
      . " IF( $t.type_code = "
      . RPS::DB::Item::FinanceTransaction::kTypeAdvance
      . ", 'advance', "
      . " IF( $t.type_code = "
      . RPS::DB::Item::FinanceTransaction::kTypePayment
      . ", 'payment', "
      . " IF( $t.type_code = "
      . RPS::DB::Item::FinanceTransaction::kTypeClosingBalance
      . ", 'closing balance', "
      . " IF( $t.type_code IS NULL, NULL, "
      . " 'unknown' ) ) ) ) ) ) ";
}

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

    push @$joins, 'JOIN ' . $class->_publisherAccount() . ' USING (finance_account_id)';
    push @$joins, 'JOIN ' . $class->_publisher() . ' USING (' . $class->_publisher() . '_id)';
    push @$joins, 'JOIN payor USING (payor_id)';
    push @$joins,
      'LEFT JOIN ' . $class->_publisher() . ' AS agent ON (' . $class->_publisher() . '.agent_id = agent.' . $class->_publisher() . '_id)';
    push @$joins,
      'LEFT JOIN ' . $class->_publisher() . ' AS admin ON (' . $class->_publisher() . '.admin_id = admin.' . $class->_publisher() . '_id)';
}

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

    push @$joins, 'JOIN ' . $class->_publisherAccountIndirect() . ' USING (finance_account_id)';
    push @$joins, 'JOIN ' . $class->_publisher() . ' USING (' . $class->_publisher() . '_id)';
    push @$joins, 'JOIN payor USING (payor_id)';
    push @$joins,
      'LEFT JOIN ' . $class->_publisher() . ' AS agent ON (' . $class->_publisher() . '.agent_id = agent.' . $class->_publisher() . '_id)';
    push @$joins,
      'LEFT JOIN ' . $class->_publisher() . ' AS admin ON (' . $class->_publisher() . '.admin_id = admin.' . $class->_publisher() . '_id)';
}

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

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

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

    if ($dateType) {

        # Need to map the dateType to the actual field names.
        #
        my $dateField;
        if ( 'date_modified' eq $dateType ) {
            $dateField = 'date';
        } elsif ( 'date_created' eq $dateType ) {
            $dateField = 'date_modified';
        } else {
            die "ERROR : Invalid dateType $dateType";
        }

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

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

    #    push @$orderBy, "client_account_no";
    push @$orderBy, "sort2";
    push @$orderBy, "finance_account_id";
    push @$orderBy, "payor_name";
    push @$orderBy, "t_status";
    push @$orderBy, "t_date_created";
}

1;