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

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 {
        'payee'                         => {},
        'client_account_no'             => {},
        'abacus_account_id'             => {},
        'abacus_contract_id'            => {},
        'status'                        => {},
        'address_1'                     => {},
        'address_2'                     => {},
        'address_3'                     => {},
        'city'                          => {},
        'state'                         => {},
        'postal_code'                   => {},
        'country'                       => {},
        'email'                         => {},
        'phone'                         => {},
        'fax'                           => {},
        'tax_id'                        => { _encrypted => 1 },
        'comments'                      => { _encrypted => 1 },
        'distribution'                  => {},
        'distribution_status'           => {},
        'date_modified'                 => {},
        'date_created'                  => {},
        'created_by'                    => {},
        'modified_by'                   => {},
        'artist_payee_id'               => {},
        'recipient_name'                => {},
        'recipient_email'               => {},
        'recipient_distribution_status' => {},
        'recipient_id'                  => {},

        # Transactions
        #        'total_no_licenses'  => {},
        'payor_name'         => {},
        'client_payor_no'    => {},
        'payor_status'       => {},
        'default_payor'      => {},
        'minimum_payment'    => {},
        'transaction_status' => {},
        'transaction_type'   => {},
        'amount'             => {},
        'transaction_date'   => {},
        'check_no'           => {},
        'memo'               => {},
        'balance'            => {},
    };

}

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

    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;

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

    if ( scalar @joins ) {
        $sql .= "\n" . join( "\n", @joins );
    }

    if ( scalar @where ) {
        $sql .= "\nWHERE " . join( "\n  AND ", @where );
    }

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

    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, 'artist_payee';
}

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

    push @$select, "artist_payee.name AS payee";
    push @$select, "artist_payee.client_account_id AS client_account_no";
    if ( $args{showAbacusData} ) {
        push @$select, "artist_payee.abacus_account_id AS abacus_account_id";
        push @$select, "artist_payee.abacus_contract_id AS abacus_contract_id";
    }
    push @$select,
        "IF( artist_payee.status = 0, 'inactive', "
      . "IF( artist_payee.status = 1, 'active', "
      . "IF( artist_payee.status = 2, 'on hold', 'unknown' ) ) ) AS status";
    push @$select, "artist_payee.street_address AS address_1";
    push @$select, "artist_payee.street_address_2 AS address_2";
    push @$select, "artist_payee.street_address_3 AS address_3";
    push @$select, "artist_payee.city AS city";
    push @$select, "artist_payee.state_province AS state";
    push @$select, "artist_payee.postal_code AS postal_code";
    push @$select, "artist_payee.country_code AS country";
    push @$select, "artist_payee.email AS email";
    push @$select, "artist_payee.phone_number AS phone";
    push @$select, "artist_payee.fax_number AS fax";
    push @$select, "artist_payee.tax_id AS tax_id";
    push @$select, "artist_payee.comments AS comments";
    push @$select, "artist_payee.distribution AS distribution";
    push @$select,
      "IF( artist_payee.distribution = 1 AND artist_payee.date_confirmed IS NULL, 'Waiting on confirmation', "
      . "IF( artist_payee.distribution = 1 AND artist_payee.date_confirmed IS NOT NULL, 'Confirmed', '' ) ) AS distribution_status";
    push @$select, "artist_payee.date_modified AS date_modified";
    push @$select, "artist_payee.date_created AS date_created";
    push @$select, "artist_payee.created_by AS created_by";
    push @$select, "artist_payee.modified_by AS modified_by";
    push @$select, "artist_payee.artist_payee_id AS artist_payee_id";

    # This is just for sorting purposes.
    push @$select, "IF(artist_payee.client_account_id REGEXP '^[[:digit:]]+" . '$'
      . "',LPAD(artist_payee.client_account_id,32,'0'),artist_payee.client_account_id) as sort2";

    if ( $args{reportType} =~ /^PayeesAndRecipients$/i ) {
        push @$select, "artist_payee_statement_recipient.name  AS recipient_name";
        push @$select, "artist_payee_statement_recipient.email AS recipient_email";
        push @$select,
        qq/CASE
               WHEN artist_payee_statement_recipient.date_invited IS NOT NULL AND artist_payee_statement_recipient.date_confirmed IS NULL THEN 'Waiting on confirmation'
               WHEN artist_payee_statement_recipient.date_invited IS NOT NULL AND artist_payee_statement_recipient.date_confirmed IS NOT NULL THEN 'Confirmed'
               ELSE ''
           END AS recipient_distribution_status
        /;
        push @$select, "artist_payee_statement_recipient.statement_recipient_id AS recipient_id";
    }
    push @$select, "artist_payee.key_id";
}

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

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

    if ( $args{reportType} eq 'PayeesAndRecipients' ) {
        push @$joins, 'LEFT JOIN artist_payee_statement_recipient USING (artist_payee_id)';
    }
}

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(artist_payee.$dateType) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate) {
            push @$where, "TO_DAYS(artist_payee.$dateType) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }
    }
}

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

#    push @$orderBy, "(client_account_no + 0)";
#    root@localhost [C_RSDEMO]> select artist_payee_id,client_account_id, LPAD(client_account_id,32,' '),IF(client_account_id REGEXP '^[[:digit:]]+$',LPAD(client_account_id,32,'0'),client_account_id) as sortme from artist_payee order by sortme;
}

1;