#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::TrackLicense;
use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;
use Common::Log;
use base 'Common::DB::Item';

use lib '/app/tools/rps/lib';
use RPS::DB::Item::ReserveLiquidation;

use constant kRateBasisSale => 1;
use constant kRateBasisLock => 2;

use constant kRateTypeFull    => 1;
use constant kRateTypeMinimum => 2;
use constant kRateTypePenny   => 3;

use constant kPublishingLicense     => 1;
use constant kControlledComposition => 2;
use constant kUnknownPublisher      => 3;
use constant kPublicDomain          => 4;
use constant kRingtone              => 5;

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

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

    $prefix //= kTable;

    my $sql = qq/
        (
            $prefix.publisher_id NOT IN ( select publisher_id from publisher where status = 0 )
            AND (
                ( $prefix.publisher_id = $publisherID AND $prefix.publisher_direct = 1 )
                OR (
                    $prefix.publisher_direct = 0
                    AND (
                        $prefix.publisher_id IN (
                            SELECT publisher_id
                            FROM publisher
                            WHERE 1 = 1
                                AND agent_id = $publisherID
                                OR  ( agent_id IS NULL OR agent_id = 0 )
                                AND admin_id = $publisherID
                        )
                    )
                    OR (
                        $prefix.publisher_id IN (
                            SELECT publisher_id
                            FROM publisher
                            WHERE 1 = 1
                                AND ( agent_id IS NULL OR agent_id = 0 )
                                AND ( admin_id IS NULL OR admin_id = 0 )
                                AND publisher_id = $publisherID
                        )
                    )
                )
            )
        )
    /;

    return $sql;
}

sub GetByParentID {
    my ( $class, $parentID ) = @_;

    my $sql = "SELECT * FROM track_license WHERE track_license_id = " . $class->quote($parentID) . " OR parent_track_license_id = " . $class->quote($parentID);
    return $class->GetAll($sql);
}

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

    my $sql = "SELECT * FROM track_license WHERE track_id = " . $class->quote($trackID);

    if ( defined $type ) {
        $sql .= " AND type = " . $class->quote($type);
    }

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

sub GetLicensesByTrackIDFiltered {
    my ( $class, $trackID, $productFilter, $licenseFilter ) = @_;
    assert($trackID);

    my $dbo = Common::RSApp::GetClientDB();
    my ($where, $having) = ('') x 2;

    $trackID = $dbo->DBQuote($trackID);
    $where = qq{ AND track_id = $trackID };

    if ( $licenseFilter && $licenseFilter =~ /^licensed$/i ) {
        my $productID = $dbo->DBQuote($productFilter);
        if ( defined $productFilter && $productFilter =~ /^15$/ ) {
            $having = qq{ AND track_license_type IN ($productID) };
        }
        elsif ( defined $productFilter && $productFilter =~ /^3|4$/ ) {
            $having = qq{ AND track_license_type IN ($productID, 'all_products', 'all_digital_products') };
        }
        elsif ( defined $productFilter && $productFilter =~ /^\d+$/ ) {
            $having = qq{ AND track_license_type IN ($productID, 'all_products', 'all_physical_products') };
        }
    }
    elsif ( $licenseFilter && $licenseFilter =~ /^unlicensed$/i ) {
        my $productID = $dbo->DBQuote($productFilter);
        if ( defined $productFilter && $productFilter =~ /^15$/ ) {
            $having = qq{ AND track_license_type NOT IN ($productID) };
        }
        elsif ( defined $productFilter && $productFilter =~ /^3|4$/ ) {
            $having = qq{ AND track_license_type NOT IN ($productID, 'all_products', 'all_digital_products') };
        }
        elsif ( defined $productFilter && $productFilter =~ /^\d+$/ ) {
            $having = qq{ AND track_license_type NOT IN ($productID, 'all_products', 'all_physical_products') };
        }
    }
    elsif ( $productFilter && !$licenseFilter ) {
        my $productID = $dbo->DBQuote($productFilter);
        $having = qq{ AND track_license_type IN ($productID) };
    }

    my $sql = qq{
        SELECT
            CASE
                WHEN tl.track_license_id AND tl.product_type_id IS NULL THEN 'all_products'
                WHEN tl.product_type_id = 255 THEN 'all_physical_products'
                WHEN tl.product_type_id = 254 THEN 'all_digital_products'
                ELSE IFNULL(tl.product_type_id, 'n/a')
            END AS track_license_type,
        tl.*
        FROM track_license AS tl
        WHERE 1 $where
        HAVING 1 $having
        ORDER BY track_license_id
    };

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

sub GetLicensesByAlbumID {
    my ( $class, $albumID, $type ) = @_;
    assert($albumID);
    my $dbo = Common::RSApp::GetClientDB();

    my $sql =
        "SELECT * FROM track_license "
      . "INNER JOIN track USING (track_id) "
      . "WHERE mechanical_exempt <> 1 AND album_id = "
      . $dbo->DBQuote($albumID);

    if ( defined $type ) {
        $sql .= " AND type=$type";
    }

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

sub GetIssuedLicensesByDate {
    my ( $class, $beginDate, $endDate ) = @_;
    assert($beginDate);
    assert($endDate);

    # Quote the dates properly.
    #
    my $dbo = Common::RSApp::GetClientDB();
    $beginDate = $dbo->DBQuote($beginDate);
    $endDate   = $dbo->DBQuote($endDate);

    my $sql =
        "SELECT * from track_license WHERE " . " (" . "   ( "
      . "     term_start IS NOT NULL AND term_start <= $endDate" . "   ) "
      . "   OR " . "   ( "
      . "     term_start IS NULL" . "   ) " . " )" . " AND " . " (" . "   ( "
      . "     term_end IS NOT NULL AND term_end >= $endDate" . "   ) "
      . "   OR " . "   ( "
      . "     term_end IS NULL" . "   ) "
      . " ) AND inactive=0";

    #     . " ) AND date_issued IS NOT NULL";

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

sub GetLicensesByTrackIDAndDate {
    my ( $class, $trackID, $beginDate, $endDate, $skipUnissuedFlag ) = @_;
    assert($trackID);
    assert($beginDate);
    assert($endDate);

    # Quote the dates properly.
    #
    my $dbo = Common::RSApp::GetClientDB();
    $beginDate = $dbo->DBQuote($beginDate);
    $endDate   = $dbo->DBQuote($endDate);

    my $sql =
        "SELECT * from track_license WHERE track_id = " . $dbo->DBQuote($trackID) . " AND " . " (" . "   ( "
      . "     term_start IS NOT NULL AND term_start <= $endDate" . "   ) "
      . "   OR " . "   ( "
      . "     term_start IS NULL" . "   ) " . " )" . " AND " . " (" . "   ( "
      . "     term_end IS NOT NULL AND term_end >= $endDate" . "   ) "
      . "   OR " . "   ( "
      . "     term_end IS NULL" . "   ) "
      . " ) AND inactive=0";

    if ($skipUnissuedFlag) {
        $sql .= " AND date_issued IS NOT NULL";
    }

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

sub GetLicensesForPublisherStatement {
    my ( $class, $publisherID, $payorID, $skipUnissuedFlag ) = @_;
    assert($publisherID);

    # Quote the dates properly.
    #
    my $dbo = Common::RSApp::GetClientDB();

    # We want all _active_ licenses that are either:
    # - direct licenses with this publisher or
    # - indirect licenses, where this publisher is the agent or admin
    #   (and agents _trump_ admins)
    #
    my $sql = "SELECT * from track_license" . " WHERE " . _forPublisher( $dbo->DBQuote($publisherID) ) . " AND payor_id = " . $dbo->DBQuote($payorID) . " AND inactive=0";

    if ($skipUnissuedFlag) {
        $sql .= " AND date_issued IS NOT NULL";
    }

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

sub GetLicensesForPublisherStatementWithTrackAndRegion {
    my ( $class, $trackID, $regionID, $beginDate, $endDate, $publisherID, $payorID, $skipUnissuedFlag ) = @_;
    assert($trackID);
    assert($regionID);
    assert($beginDate);
    assert($endDate);
    assert($publisherID);
    assert($payorID);

    # Quote the dates properly.
    #
    my $dbo = Common::RSApp::GetClientDB();
    $beginDate = $dbo->DBQuote($beginDate);
    $endDate   = $dbo->DBQuote($endDate);

    # We want all _active_ licenses that are either:
    # - direct licenses with this publisher or
    # - indirect licenses, where this publisher is the agent or admin
    #   (and agents _trump_ admins)
    #
    # alas, the 'publisher_direct' flag is not always accurate, in that
    # if a publisher HAS NO agent or admin, then the license is always
    # treated as direct.... that makes the query uglier.
    #
    my $sql =
        "SELECT * from track_license WHERE track_id = " . $dbo->DBQuote($trackID)
      . " AND region_id = " . $dbo->DBQuote($regionID) . " AND "
      . _forPublisher( $dbo->DBQuote($publisherID) )
      . " AND payor_id = " . $dbo->DBQuote($payorID) . " AND " . " (" . "   ( "
      . "     term_start IS NOT NULL AND term_start <= $endDate" . "   ) "
      . "   OR " . "   ( "
      . "     term_start IS NULL" . "   ) " . " )" . " AND " . " (" . "   ( "
      . "     term_end IS NOT NULL AND term_end >= $endDate" . "   ) "
      . "   OR " . "   ( "
      . "     term_end IS NULL" . "   ) "
      . " ) AND inactive=0";

    if ($skipUnissuedFlag) {
        $sql .= " AND date_issued IS NOT NULL";
    }
    $sql .= " ORDER BY track_license_id";

    return $class->GetAll($sql);

}

sub GetLicensesWithPendingTransactionsForMechanicalStatement {
    my ( $class, $publisherID, $payorID ) = @_;

    my $sql =
        "SELECT * FROM track_license WHERE finance_account_id IN (select finance_account_id from pending_transaction)" . " AND "
      . _forPublisher( $class->quote($publisherID) )
      . " AND payor_id = "
      . $class->quote($payorID)
      . " AND inactive=0";

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

sub GetCrossedLicensesWithPendingTransactionsForMechanicalStatement {
    my ( $class, $publisherID, $payorID ) = @_;

    $publisherID = $class->quote($publisherID);
    $payorID     = $class->quote($payorID);

    my $publisherSQL = _forPublisher($publisherID, 'tl');
    my $sql = qq/
        SELECT tl.*
        FROM track_license AS tl
        INNER JOIN publisher_track_crossed_license_account AS ptcla ON tl.payor_id = ptcla.payor_id
            AND tl.publisher_id = ptcla.publisher_id
            AND tl.track_id = ptcla.track_id
        INNER JOIN pending_transaction AS pt ON ptcla.finance_account_id = pt.finance_account_id
        WHERE 1 = 1
            AND tl.payor_id = $payorID
            AND tl.inactive = 0
            AND $publisherSQL
    /;

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

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

    my $sql = "SELECT * from track_license where finance_account_id in (select finance_account_id from pending_transaction)";
    return $class->GetAll($sql);
}

# !!! Just used in the mechanicals script.
# !!! THis does nothing - that flag is never set.
sub GetNewTrackLicenses {
    my ($class) = @_;

    # Return all track licenses that have yet to experience the joy of a royalty run
    #
    my $sql = "SELECT * FROM track_license WHERE last_mechanical_run_id = 0";

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

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

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

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

sub GetByType {
    my ( $class, $type ) = @_;
    assert($type);

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

sub GetByTrackPublisherProductType {
    my ( $class, $trackID, $publisherID, $productTypeID ) = @_;

    my $productTypeClause;
    if ( !$productTypeID ) {
        $productTypeClause = "(product_type_id IS NULL or product_type_id=0)";
    } else {
        $productTypeClause = "product_type_id = " . $class->quote($productTypeID);
    }

    my $sql = "SELECT * FROM track_license" . " WHERE track_id = " . $class->quote($trackID) . " AND publisher_id = " . $class->quote($publisherID) . " AND $productTypeClause";

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

sub GetByTrackPublisherProductTypeAndDate {
    my ( $class, $trackID, $publisherID, $payorID, $productTypeID, $termStart, $termEnd ) = @_;
    my $dateSelect = "";
    my $dbo        = Common::RSApp::GetClientDB();
    my $productTypeGroupSQL;
    my @ids = ref($productTypeID) ? @$productTypeID : ($productTypeID);
    my $hasDigital;
    my $hasPhysical;

    foreach my $id (@ids) {
        if ( $id == 3 || $id == 4 || $id == 15 ) {
            $hasDigital = 1;
        } else {
            $hasPhysical = 1;
        }
    }

    $productTypeGroupSQL = " OR product_type_id = 254 " if ($hasDigital);
    $productTypeGroupSQL = " OR product_type_id = 255 " if ($hasPhysical);
    $productTypeGroupSQL = "" unless ($productTypeGroupSQL);

    my $typeIDs = '(' . join( ',', map { $dbo->DBQuote($_) } @ids ) . ')';

    if ( $termStart && $termEnd ) {
        $dateSelect .=
            ", if( term_start >= "
          . $dbo->DBQuote($termStart)
          . " AND term_end <= "
          . $dbo->DBQuote($termStart)
          . ", 1, NULL ) as start_term_overlap ";
        $dateSelect .=
            ", if( term_start >= "
          . $dbo->DBQuote($termEnd)
          . " AND term_end <= "
          . $dbo->DBQuote($termEnd)
          . ", 1, NULL ) as end_term_overlap ";
    }

    elsif ($termStart) {
        $dateSelect .= ", if( term_end > " . $dbo->DBQuote($termStart) . ", 1, NULL ) as start_term_overlap ";
        $dateSelect .= ", NULL as end_term_overlap";
    }

    elsif ($termEnd) {
        $dateSelect .= ", NULL as start_term_overlap";
        $dateSelect .= ", if( term_start < " . $dbo->DBQuote($termEnd) . ", 1, NULL ) as end_term_overlap ";
    }

    else {
        $dateSelect .= ", 1 as start_term_overlap, 1 as end_term_overlap ";
    }

    my $productTypeClause = "";
    if ($productTypeID) {
        $productTypeClause = " AND ( product_type_id IN $typeIDs OR product_type_id = 0 OR product_type_id IS NULL $productTypeGroupSQL ) ";
    }

    my $sql = "SELECT * $dateSelect FROM track_license";
      $sql .= " WHERE track_id = " . $dbo->DBQuote($trackID);
      $sql .= " AND publisher_id = " . $dbo->DBQuote($publisherID);
      $sql .= " AND payor_id = " . $dbo->DBQuote($payorID);
      $sql .= $productTypeClause;

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

sub GetByPublisherIDPayorID {
    my ( $class, $publisherID, $payorID ) = @_;

    assert($publisherID);
    assert($payorID);

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

    my $sql = "SELECT * FROM " . kTable . " WHERE publisher_id = " . $dbo->DBQuote($publisherID) . " AND payor_id = "  . $dbo->DBQuote($payorID);
    return $class->GetAll($sql);
}

sub GetDirectByPublisherIDPayorID {
    my ( $class, $publisherID, $payorID ) = @_;

    assert($publisherID);
    assert($payorID);

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

    my $sql = "SELECT * FROM " . kTable . " WHERE publisher_id = " . $dbo->DBQuote($publisherID) . " AND payor_id = " . $dbo->DBQuote($payorID) . " AND publisher_direct=1";
    return $class->GetAll($sql);
}

sub GetIndirectByPublisherIDPayorID {
    my ( $class, $publisherID, $payorID ) = @_;

    assert($publisherID);
    assert($payorID);

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

    my $sql = "SELECT * FROM " . kTable . " WHERE publisher_id = " . $dbo->DBQuote($publisherID) . " AND payor_id = " . $dbo->DBQuote($payorID) . " AND publisher_direct=0";
    return $class->GetAll($sql);
}

sub GetByPayorID {
    my ( $class, $payorID ) = @_;

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

    my $sql = "SELECT * FROM " . kTable . " WHERE payor_id = " . $dbo->DBQuote($payorID);
    return $class->GetAll($sql);
}

sub GetCountByPayorID {
    my ( $class, $payorID ) = @_;

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

    my $sql = "SELECT COUNT(*) as count FROM " . kTable . " WHERE payor_id = " . $dbo->DBQuote($payorID);
    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{count};
}

sub GetAllWithoutReserveLiquidation {
    my $class = shift;

    my $types = join(
        ',',
        ( RPS::DB::Item::ReserveLiquidation::kTypePublishingLicense, RPS::DB::Item::ReserveLiquidation::kTypeControlledComposition,
            RPS::DB::Item::ReserveLiquidation::kTypeRingtoneLicense
        )
    );
    my $table = kTable;

    my $sql = "SELECT *
               FROM $table
                  LEFT JOIN reserve_liquidation ON ( $table.track_license_id = reserve_liquidation.id AND reserve_liquidation.type IN ($types) )
               WHERE reserve_percentage > 0 AND reserve_liquidation.id IS NULL and inactive = 0";

    Common::Log::Debug("QUERY: $sql");
    return $class->SUPER::GetAll($sql);
}

sub GetAllWithInvalidPennyRate {
    my $class = shift;

    my $table = kTable;

    my $sql = "SELECT *
               FROM $table
               WHERE rate_type = " . kRateTypePenny . " AND penny_rate <= 0";

    Common::Log::Debug("QUERY: $sql");
    return $class->SUPER::GetAll($sql);
}

sub GetAllWithZeroShare {
    my $class = shift;

    my $table = kTable;

    my $sql = "SELECT *
               FROM $table
               WHERE share = 0 AND inactive = 0";

    Common::Log::Debug("QUERY: $sql");
    return $class->SUPER::GetAll($sql);
}

# This will return an array reference of hash references that look like this:
# [ { publisher_id => 123, count => 5 }, ... ]
#
sub GetCountsForAllPublishers {
    my ($class) = @_;

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

    my @returnArray;
    my $sql = "SELECT publisher_id, COUNT(*) AS count FROM track_license GROUP BY publisher_id";
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @returnArray, $hr;
    }

    return \@returnArray;
}

sub GetAllPayorsShare {
    my ( $class, $trackID, $productTypeID, $regionIDs) = @_;

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

    my $sql = "SELECT IFNULL(SUM(share), 0) as share_sum FROM " . kTable . " WHERE track_id = " . $class->quote($trackID);

    if ( $productTypeID == 15 ) {
        $sql .= " AND product_type_id = " . $class->quote($productTypeID);
    } elsif ( $productTypeID =~ /^3|4$/ ) {
        $sql .= " AND (product_type_id IS NULL OR product_type_id IN (254, " . $class->quote($productTypeID) . "))";
    } else {
        $sql .= " AND (product_type_id IS NULL OR product_type_id IN (255, " . $class->quote($productTypeID) . "))";
    }

    $sql .= " AND region_id IN (" . join(',', @$regionIDs) . ")";
    $sql .= " AND inactive = 0";

    my $sth = $dbo->DoCmd($sql);
    my $hr  = $sth->fetchrow_hashref();
    return $hr->{share_sum};
}

1;