#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::TrackContract;
use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;
use base 'Common::DB::Item';

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

use constant kStatusActive   => 1;
use constant kStatusInactive => 2;

sub GetByContractIDAlbumID {
    my ( $class, $contractID, $albumID ) = @_;
    assert($contractID);
    assert($albumID);

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE artist_contract_id = "
      . $class->quote($contractID)
      . " AND track_id IN (select track_id from track where album_id = "
      . $class->quote($albumID)
      . ")";

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

sub GetByTrackID {
    my ( $class, $trackID ) = @_;
    assert($trackID);

    my $sql = "SELECT * FROM " . kTable . " WHERE track_id = " . $class->quote($trackID);
    return $class->GetAll($sql);
}

sub GetByContractID {
    my ( $class, $contractID ) = @_;
    assert($contractID);

    my $sql = "SELECT * FROM " . kTable . " WHERE artist_contract_id = " . $class->quote($contractID);
    return $class->GetAll($sql);
}

sub GetByAlbumID {
    my ( $class, $albumID ) = @_;
    assert($albumID);

    my $sql = "SELECT * FROM " . kTable . " WHERE track_id IN " . " (SELECT track_id FROM track WHERE album_id = " . $class->quote($albumID) . ")";

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

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

    my $sql = "SELECT * FROM " . kTable . " where cross_collateralized=1";

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

sub GetByPayorIDArtistIDAlbumID {
    my ( $class, $payorID, $artistPayeeID, $albumID ) = @_;
    assert($payorID);
    assert($artistPayeeID);
    assert($albumID);

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE track_id in (select track_id from track where album_id = " . $class->quote($albumID) . ")"
      . " AND artist_contract_id IN "
      . " (SELECT artist_contract_id FROM new_artist_contract WHERE deleted = 0 AND artist_payee_id = " . $class->quote($artistPayeeID) . " AND payor_id = " . $class->quote($payorID) . ")";

    my $collection = $class->GetAll($sql);
    assert( $collection->size < 2 );

    return $collection->next;
}

sub HasBeenIncludedInRoyaltyRun {
    my ( $class, $trackContractID ) = @_;

    my $item = $class->Lookup( track_contract_id => $trackContractID );
    return undef unless $item;

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

    my $sql =
      "SELECT COUNT(*) FROM artist_royalty_income_item INNER JOIN new_artist_contract_term USING (artist_contract_term_id) WHERE track_id="
      . $dbo->DBQuote($item->track_id)
      . " AND artist_contract_id="
      . $dbo->DBQuote($item->artist_contract_id);

    my $sth   = $dbo->DoCmd($sql);
    my $hr    = $sth->fetchrow_hashref();
    my $count = $hr->{"COUNT(*)"};
    return ( $count > 0 );
}

sub getTrackContractsByTrackIDs {
    my ( $class, $aTrackIDs ) = @_;

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

    my @trackIDs = join ",", map{ $dbo->DBQuote($_) } grep {$_} @$aTrackIDs;

    my $sql = qq/
        SELECT
            t.track_id             AS TrackID,
            t.title                AS TrackTitle,
            t.track_order          AS TrackOrder,
            tc.track_contract_id   AS TrackContractID,
            tc.status              AS TrackContractStatus,
            nac.artist_contract_id AS ArtistContractID,
            nac.artist_description AS ArtistDescription,
            nac.client_contract_id AS ClientContractID,
            nac.title              AS ArtistContractTitle,
            nac.issue_date         AS ArtistContractIssueDate,
            ap.name                AS ArtistPayeeName,
            ( SELECT COUNT(*)
              FROM expense
              WHERE 1 = 1
                  AND parent_id = tc.track_contract_id
                  AND parent_type = 2
                  AND processed > 0
            ) AS processed_expenses,
            (
              SELECT COUNT(*)
              FROM expense
              WHERE 1 = 1
                  AND parent_id = tc.track_contract_id
                  AND parent_type = 2
                  AND processed = 0
            ) AS pending_expenses,
            (
              SELECT COUNT(*)
              FROM artist_royalty_income_item
              INNER JOIN new_artist_contract_term USING (artist_contract_term_id)
              WHERE 1 = 1
                  AND track_id = t.track_id
                  AND artist_contract_id = nac.artist_contract_id
            ) AS included_on_royalty_run
        FROM track                    AS t
        LEFT JOIN track_contract      AS tc  ON t.track_id = tc.track_id
        LEFT JOIN new_artist_contract AS nac ON tc.artist_contract_id = nac.artist_contract_id
        LEFT JOIN artist_payee        AS ap  ON nac.artist_payee_id = ap.artist_payee_id
        WHERE t.track_id IN (@trackIDs)
        ORDER BY t.track_id;
    /;

    my $sth = $dbo->DoCmd($sql);
    my $aData = $sth->fetchall_arrayref({});

    return $aData;
}

1;