package API::Command::StatementBase;
use strict;
use warnings;

use Apache2::Const qw(OK FORBIDDEN HTTP_OK HTTP_UNAUTHORIZED HTTP_NOT_FOUND HTTP_NOT_IMPLEMENTED);

use lib '/app/tools/api/lib';
use API::Util;

use lib '/app/tools/common/lib';
use Common::DB::Item::PayeeType;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Payor;
use RPS::Statement::Artist::BaseStatement;
use RPS::ArtistRoyalty::ArtistRoyaltyRun;
use RPS::RoyaltyRun::Status;
use RPS::ArtistPayee::ArtistPayee;

use lib '/app/tools/rps/bin/statements';
use Formats;

use base 'API::Command';

use constant EXCEL_START_DATE => "2013-09-09 00:00:00";

sub getActiveArtistStatementID {
    my ($self, $runID, $payeeID) = @_;

    my $dbo = Common::RSApp::GetClientDB();

    # The following query will return the statementID for the payeeID in the
    # specified runID, regardless of whether there is statement activity or not.
    my $sql = qq/
        SELECT artist_royalty_statement_id
        FROM artist_royalty_statement AS s
        INNER JOIN artist_payee p ON ( p.artist_payee_id = s.payee_id )
        WHERE s.artist_royalty_run_id = $runID
            AND s.date_sent IS NOT NULL
            AND s.payee_id = $payeeID
    /;
    my $sth = $dbo->DoCmd($sql);

    return $sth->fetchrow_array();
}

sub _getStatementsByPayeeID {
    my ($self, $payeeID, $runType) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    $payeeID = $dbo->DBQuote($payeeID);

    # Query for all of the payee's (publisher's) statements on closed runs (status 5)
    # and the current statement (statements on runs with status 2).
    # Also, the statement must not be marked as on-hold.

    my $sqlArtistPayee = qq/
        SELECT
            r.artist_royalty_run_id,
            r.start_time,
            r.payor_id,
            r.label,
            r.status,
            p.name AS payee_name,
            p.client_account_id,
            rs.artist_royalty_statement_id,
            rs.payee_id,
            rs.payor_id,
            p2.name AS payor_name,
            rs.previous_balance,
            rs.balance,
            rs.min_payment,
            rs.total,
            rs.amount_due,
            (
                SELECT COUNT(*)
                FROM artist_royalty_transaction t
                WHERE (t.artist_royalty_statement_id = rs.artist_royalty_statement_id)
            ) AS "t-count",
            rs.on_hold
        FROM artist_royalty_statement rs
        INNER JOIN artist_royalty_run r  ON rs.artist_royalty_run_id = r.artist_royalty_run_id
        INNER JOIN artist_payee       p  ON p.artist_payee_id = rs.payee_id
        INNER JOIN payor              p2 ON r.payor_id = p2.payor_id
        WHERE rs.payee_id = $payeeID
            AND rs.date_sent IS NOT NULL
            AND r.status IN (2, 5)
        ORDER BY rs.artist_royalty_statement_id DESC
    /;

    my $sqlPublisher = qq/
        SELECT
            mr.mechanical_run_id,
            mr.start_time,
            mr.payor_id,
            mr.label,
            mr.status,
            p.publisher_name,
            p.client_account_id,
            ms.mechanical_statement_id,
            ms.publisher_id,
            ms.payor_id,
            p2.name AS payor_name,
            ms.previous_balance,
            ms.balance,
            ms.min_payment,
            ms.statement_total,
            ms.amount_due,
            (
                SELECT COUNT(*)
                FROM mechanical_statement_transaction t
                WHERE (t.mechanical_statement_id = ms.mechanical_statement_id)
            ) AS "t-count",
            ms.on_hold
        FROM mechanical_statement AS ms
        INNER JOIN mechanical_run AS mr ON ms.mechanical_run_id = mr.mechanical_run_id
        INNER JOIN publisher      AS p  ON ms.publisher_id = p.publisher_id
        INNER JOIN payor          AS p2 ON mr.payor_id = p2.payor_id
        WHERE ms.publisher_id = $payeeID
            AND mr.status IN (2, 5)
            AND ms.date_sent IS NOT NULL
        ORDER BY ms.mechanical_statement_id DESC
    /;

    my $sql = '';
    $sql = $sqlArtistPayee if $runType eq 'artist';
    $sql = $sqlPublisher   if $runType eq 'publisher';

    my $sth = $dbo->DoCmd($sql);

    return $sth;
}

sub _getTransactionsByStatementID {
    my ($self, $statementID, $runType) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    $statementID = $dbo->DBQuote($statementID);

    my $sqlArtistPayee = qq/
        SELECT
            IF(amount, amount, 0)                      AS amount,
            IF(LENGTH(memo), memo, '')                 AS memo,
            IF(check_number, check_number, '')         AS check_number,
            IF(transaction_date, transaction_date, '') AS transaction_date,
            CASE
                WHEN type_code = 2 THEN 'Adjustment'
                WHEN type_code = 3 THEN 'Advance'
                WHEN type_code = 4 THEN 'Payment'
                ELSE ''
            END AS `type`
        FROM artist_royalty_transaction
        WHERE artist_royalty_statement_id = $statementID
        ORDER BY artist_royalty_transaction_id DESC
    /;

    my $sqlPublisher = qq/
        SELECT
            IF(amount, amount, 0)                      AS amount,
            IF(LENGTH(memo), memo, '')                 AS memo,
            IF(check_number, check_number, '')         AS check_number,
            IF(transaction_date, transaction_date, '') AS transaction_date,
            CASE
                WHEN type_code = 2 THEN 'Adjustment'
                WHEN type_code = 3 THEN 'Advance'
                WHEN type_code = 4 THEN 'Payment'
                ELSE ''
            END AS `type`
        FROM mechanical_statement_transaction
        WHERE mechanical_statement_id = $statementID
        ORDER BY mechanical_statement_id DESC;
    /;

    my $sql = '';
    $sql = $sqlArtistPayee if $runType eq 'artist';
    $sql = $sqlPublisher   if $runType eq 'publisher';

    my $sth = $dbo->DoCmd($sql);

    return $sth;
}

sub _getTransactionLines {
    my ($self, $statementID, $runType) = @_;

    my @lines;

    my $sth = $self->_getTransactionsByStatementID($statementID, $runType);
    while ( my ( $amount, $memo, $checkNum, $date, $type ) = $sth->fetchrow_array() ) {
        my $hRow = {
            'desc'        => $type,
            'amount'      => $amount,
            'checknumber' => $checkNum,
            'memo'        => $memo,
            'date'        => formatDate($date),
        };

        push @lines, $hRow;
    }

    return \@lines;
}

sub getStatementData {
    my ($self, $statementType, $oPortalPayee, $runType) = @_;

    my $clientID         = $oPortalPayee->client_id;
    my $payeeID          = $oPortalPayee->payee_id;
    # greater than 0 means it is an additional recipient
    my $payeeRecipientID = $oPortalPayee->statement_recipient_id;

    my @payeeStatements;

    # $statementType eq 'historical'
    # We are going to exclude the current statement (status = 2).
    # The current statement is the 1st statement of the payor.
    my $currentStatementFound = 0;

    # $statementType eq 'current'
    # Check if there are historical statements after the current statement.
    # Historical statements have status = 5.
    my $hasHistoricalStatements = 0;

    my $sth = $self->_getStatementsByPayeeID($payeeID, $runType);
    while (
        my (
            $runID,
            $runStartTime,
            $runPayorID,
            $runLabel,
            $runStatus,
            $payeeName,
            $payeeClientAccountID,
            $stmtID,
            $payeeID,
            $stmtPayorID,
            $payorName,
            $stmtPreviousBalance,
            $stmtBalance,
            $stmtMinPayment,
            $stmtTotal,
            $stmtAmountDue,
            $tranCount,
            $onHold,
        ) = $sth->fetchrow_array()
    ) {

        if ( $statementType eq 'current' ) {
            last if $currentStatementFound && $hasHistoricalStatements;

            # calculate historical statements depending on the type of payee (primary or additional recipient)
            if ($payeeRecipientID) {
                $hasHistoricalStatements++ if $currentStatementFound && $runStartTime ge $oPortalPayee->date_invited;
            } else {
                $hasHistoricalStatements++ if $currentStatementFound;
            }

            # get and process only the first statement record
            next if $currentStatementFound++;
        }

        if ( $statementType eq 'historical' ) {
            # skip the first (current) statement record
            next unless $currentStatementFound++;
        }

        my $currencyCode   = Common::Client::Current()->Locale()->currencyFormat()->currencyCode();
        my $currencySymbol = Common::Client::Current()->Locale()->currencyFormat()->symbol();
        my $aTransactionLines = $tranCount ? $self->_getTransactionLines($stmtID, $runType) : [];
        my $baseUrl  = "https://portal.royaltyshare.com/api/client/$clientID/runtype/$runType/statement/$stmtID/download";
        my $excelURL = $baseUrl . '?format=xls' if $runStartTime gt EXCEL_START_DATE;
        my $txtURL   = $baseUrl . '?format=txt';
        my $pdfURL   = $baseUrl . '?format=pdf';


        # Setup JSON return value
        my %data = (
            run_id                  => 0 + $runID,
            payee_id                => 0 + $payeeID,
            payee_name              => $payeeName,
            previous_balance        => 0 + $stmtPreviousBalance,
            balance                 => 0 + $stmtBalance,                 # Ending Balance
            min_payment             => 0 + $stmtMinPayment,
            total                   => 0 + $stmtTotal,                   # Current Period Royalties
            amount_payable          => 0 + $stmtAmountDue,
            payor_id                => 0 + $stmtPayorID,
            payor_name              => $payorName,
            statement_id            => 0 + $stmtID,
            lines                   => $aTransactionLines,
            num_lines               => scalar @$aTransactionLines,
            on_hold                 => 0 + $onHold,
            client_account_id       => $payeeClientAccountID,
            run_name                => $runLabel,
            run_type                => $runType,
            run_status              => 0 + $runStatus,
            date                    => $runStartTime,
            excel_url               => $excelURL,
            txt_url                 => $txtURL,
            pdf_url                 => $pdfURL,
            currency_code           => $currencyCode,
            currency_symbol         => $currencySymbol,
        );

        push @payeeStatements, \%data;
    }

    return \@payeeStatements, $hasHistoricalStatements;
}

sub formatDate {
    my ($value) = @_;

    $value = Formats::formatDate($value);

    return $value;
}

# Check if a payee's name is unique.  If more than one payee exists with that name
# then concatenate the client account information to the name.  It is the caller's
# responsibilty to check if the same unique name is returned for different payees.
# This can happen if more than one payee has the same name and client_account_id
# information.
#
sub _getUniqueArtistPayeeName {
    my $self  = shift;
    my $dbo   = shift;
    my $payee = shift;
    my $sql   = "SELECT artist_payee_id FROM artist_payee WHERE name = " . $dbo->DBQuote( $payee->name );
    my $sth   = $dbo->DoCmd($sql);
    if ( $sth->rows > 1 ) {
        return $payee->name . ' (' . $payee->client_account_id . ')';
    } else {
        return $payee->name;
    }
}


1;