package Raptor::DB::Item::Sale;
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 kSaleTypeTrack => 'T';
use constant kSaleTypeAlbum => 'A';

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

use lib '/app/tools/rps/lib';
use RPS::DB::Item::MediaType;
use constant kMediaTypeDefault => RPS::DB::Item::MediaType::kMediaTypeAudio();

use lib '/app/tools/raptor/lib';
use Raptor::DB::Item::File;

# These constants will be used to show the state of royalty
# processing (probably both artist and mechanicals).
#
use constant kUnprocessed => 0;
use constant kPending     => 1;
use constant kProcessed   => 2;

sub GetByFileID {
    my ( $class, $fileID ) = @_;
    return $class->GetAll( "SELECT * FROM " . kTable . " WHERE file_id=$fileID" );
}

sub GetCAPublisherMappedSales {
    my ( $class, $publisherID, $runID ) = @_;
    assert($publisherID);
    assert($runID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE sale_id IN"
      . " ( SELECT sale_id FROM sale_ca_publisher_map WHERE ca_publisher_id=$publisherID AND run_id=$runID )";

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

# !!! US Publishers only...
#
sub GetPublisherMappedSales {
    my ( $class, $publisherID, $runID ) = @_;
    assert($publisherID);
    assert($runID);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE sale_id IN"
      . " ( SELECT sale_id FROM sale_publisher_map WHERE publisher_id=$publisherID AND run_id=$runID )";

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

# These are sales that might relate to the Uk Mechanicals 'AP1' scheme.
#
sub GetUnprocessedAP1SaleIDs {
    my ( $class, %args ) = @_;
    my $endingSaleDate = $args{ending_sale_date};

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

    # Note that we DO look at free sales...
    #
    my @ids;
    my $sql =
        "SELECT * from sale WHERE uk_mechanical_royalty_status < 2"
      . " AND file_id in (select file_id from file where period_id>0 AND service_id NOT IN (select service_id from service where exclude_mechanicals = 1))"
      . " AND ( (product_type IN ('1','2','4','7','B','C','P')) )"

      #	 . " AND ( (product_type IN ('1','2','4','7','B','C')) OR (product_type IN ('T','A')  AND format_type IN ('D', 'P', 'E', 'H')))"
      ;

    if ($endingSaleDate) {
        $sql .= " AND date_end <= " . $dbo->DBQuote($endingSaleDate);
    }

    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @ids, $hr->{sale_id};
    }

    return \@ids;
}

sub GetUnprocessedDVD1SaleIDs {
    my ( $class, %args ) = @_;
    my $endingSaleDate = $args{ending_sale_date};

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

    # Note that we DO look at free sales...
    #
    my @ids;
    my $sql =
        "SELECT sale_id from sale WHERE uk_mechanical_royalty_status < 2"
      . " AND file_id in (select file_id from file where period_id>0 AND service_id NOT IN (select service_id from service where exclude_mechanicals = 1))"
      . " AND product_type IN ('9')";

    if ($endingSaleDate) {
        $sql .= " AND date_end <= " . $dbo->DBQuote($endingSaleDate);
    }

    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @ids, $hr->{sale_id};
    }

    return \@ids;
}

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

    # !!! Exactly the same as DVD1...
    #
    return $class->GetUnprocessedDVD1SaleIDs(%args);
}

sub GetDualtoneUnprocessedMechanicalSales {
    my ($class);
    return $class->GetUnprocessedMechanicalSales();
}

sub GetUnprocessedCAMechanicalSales {
    my ( $class, %args ) = @_;
    my $endingSaleDate = $args{ending_sale_date};
    my $dbo            = Common::RSApp::GetClientDB();

    # for reference, from /app/tools/data_classes/lib/File/Sale.pm (should clean this up at some point...)
    #
    # use constant TYPE_ALBUM             =>  'A';
    # use constant TYPE_TRACK             =>  'T';
    # ## leaving TYPE_PHYSICAL for now for back-compat until we've figured it out better
    # use constant TYPE_PHYSICAL          =>  '2';
    # use constant TYPE_LP                =>  '1';
    # use constant TYPE_CD                =>  '2';
    # use constant TYPE_VHS               =>  '3';
    # use constant TYPE_CASS              =>  '4';
    # use constant TYPE_VCD               =>  '5';
    # use constant TYPE_EP                =>  '7';
    # use constant TYPE_MD                =>  '8';
    # use constant TYPE_DVD               =>  '9';
    # use constant TYPE_MASTER            =>  'M';
    # use constant TYPE_CAS_SIN           =>  'K';
    # use constant TYPE_CD_SIN            =>  'C';
    # use constant TYPE_DVD_CD_SET        =>  'D';
    # use constant TYPE_DBL_CD            =>  'B';
    # use constant TYPE_LICENSE_INCOME    =>  'L';

    my $sql =
        "SELECT * from sale WHERE ca_mechanical_royalty_status<>2"
      . " AND file_id in (select file_id from file where period_id>0 AND service_id NOT IN (select service_id from service where exclude_ca_mechanicals = 1))"
      . " AND free<>1"
      . " AND ( (product_type IN ('1','2','4','7','9','B','C','D','P','U')) OR (product_type IN ('T','A')  AND format_type IN ('D', 'P', 'E', 'H')  AND (units < 0 || price<>0)))";

    if ($endingSaleDate) {
        $sql .= " AND date_end <= " . $dbo->DBQuote($endingSaleDate);
    }

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

sub GetUnprocessedMechanicalSales {
    my ( $class, %args ) = @_;
    my $endingSaleDate = $args{ending_sale_date};
    my $dbo            = Common::RSApp::GetClientDB();

    # for reference, from /app/tools/data_classes/lib/File/Sale.pm (should clean this up at some point...)
    #
    # use constant TYPE_ALBUM             =>  'A';
    # use constant TYPE_TRACK             =>  'T';
    # ## leaving TYPE_PHYSICAL for now for back-compat until we've figured it out better
    # use constant TYPE_PHYSICAL          =>  '2';
    # use constant TYPE_LP                =>  '1';
    # use constant TYPE_CD                =>  '2';
    # use constant TYPE_VHS               =>  '3';
    # use constant TYPE_CASS              =>  '4';
    # use constant TYPE_VCD               =>  '5';
    # use constant TYPE_EP                =>  '7';
    # use constant TYPE_MD                =>  '8';
    # use constant TYPE_DVD               =>  '9';
    # use constant TYPE_MASTER            =>  'M';
    # use constant TYPE_CAS_SIN           =>  'K';
    # use constant TYPE_CD_SIN            =>  'C';
    # use constant TYPE_DVD_CD_SET        =>  'D';
    # use constant TYPE_DBL_CD            =>  'B';
    # use constant TYPE_LICENSE_INCOME    =>  'L';

    my $sql =
        "SELECT * from sale WHERE mechanical_royalty_status<>2"
      . " AND file_id in (select file_id from file where period_id>0 AND service_id NOT IN (select service_id from service where exclude_mechanicals = 1))"
      . " AND free<>1"

# JPK - Replace this line when we want to re-enable RINGBACK sales
#	 . " AND ( (product_type IN ('1','2','4','7','9','B','C','D','P','U')) OR (product_type IN ('T','A')  AND format_type IN ('D', 'P', 'E', 'H', 'R', '1')  AND (units < 0 ||price<>0)))"
      . " AND ( (product_type IN ('1','2','4','7','9','B','C','D','P','U')) OR (product_type IN ('T','A')  AND format_type IN ('D', 'P', 'E', 'H', 'R')  AND (units < 0 ||price<>0)))";

    if ($endingSaleDate) {
        $sql .= " AND date_end <= " . $dbo->DBQuote($endingSaleDate);
    }

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

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

    my $sql = "SELECT * from sale WHERE mechanical_royalty_status<>2"

      #	 . " AND file_id in (select file_id from file where period_id>0 AND service_id <> 5 ) "
      . " AND file_id in (select file_id from file where period_id>0 AND service_id NOT IN (select service_id from service where exclude_mechanicals = 1))"
      . " AND free<>1"
      . " AND product_type='9'"
      . " AND country_code IN ('US')";

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

sub GetUnprocessedRoyaltySales {
    my ( $class, %args ) = @_;
    my $endingSaleDate = $args{ending_sale_date};
    my $dbo            = Common::RSApp::GetClientDB();

    my $sql =
        "SELECT * from sale WHERE artist_royalty_status<>2"
      . " AND file_id in (select file_id from file where period_id > 0) "
      . " AND free<>1"
      . " AND product_type not in ('L')"
      . " AND ( price <> 0 or product_type not in ('T', 'A'))";

    if ($endingSaleDate) {
        $sql .= " AND date_end <= " . $dbo->DBQuote($endingSaleDate);
    }

    # !!! Duh - forgot this order by...
    if ( $args{sortByProductID} ) {
        $sql .= " ORDER BY product_id";
    }

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

sub GetUnprocessedRoyaltySaleIDsUsingProductArtistPayeeMap {
    my ( $class, $payeeID, $runID, $endingSaleDate ) = @_;
    assert($payeeID);
    assert($runID);

    my $dbo = Common::RSApp::GetClientDB();
    my @ids;
    my $sql =
        "SELECT sale_id FROM sale"
      . " LEFT JOIN file USING (file_id)"
      . " LEFT JOIN product_artist_payee_map USING (product_id)"
      . " WHERE file.period_id > 0"
      . " AND artist_royalty_status<>2"
      . " AND artist_payee_id=$payeeID"
      . " AND run_id=$runID"
      . " AND free<>1"
      . " AND product_type NOT IN ('L')"
      . " AND ( price <> 0 OR product_type NOT IN ('T', 'A'))";

    if ($endingSaleDate) {
        $sql .= " AND sale.date_end <= " . $dbo->DBQuote($endingSaleDate);
    }

    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref() ) {
        push @ids, $hr->{sale_id};
    }

    return \@ids;
}

sub GetUnprocessedLicenseIncomeRoyaltySales {
    my ( $class, %args ) = @_;
    my $endingSaleDate = $args{ending_sale_date};
    my $dbo            = Common::RSApp::GetClientDB();

    my $sql =
        "SELECT * from sale WHERE artist_royalty_status<>2"
      . " AND file_id in (select file_id from file where period_id > 0) "
      . " AND free<>1"
      . " AND product_type in ('L')";

    if ($endingSaleDate) {
        $sql .= " AND date_end <= " . $dbo->DBQuote($endingSaleDate);
    }

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

sub GetUnprocessedLabelSales {
    my ( $class, %args ) = @_;
    my $endingSaleDate = $args{ending_sale_date};
    my $dbo            = Common::RSApp::GetClientDB();

    my $sql =
        "SELECT * from sale WHERE label_royalty_status<>2"
      . " AND file_id in (select file_id from file where period_id > 0) "
      . " AND free<>1";

    #	 . " AND ( price <> 0 or product_type not in ('T', 'A'))";

    if ($endingSaleDate) {
        $sql .= " AND date_end <= " . $dbo->DBQuote($endingSaleDate);
    }

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

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

    my $sql =
        "SELECT period.period_id, period.name, orig_file_name, file.file_id, COUNT(*) as exceptions "
      . "FROM sale "
      . "INNER JOIN file USING (file_id) "
      . "INNER JOIN period USING (period_id) "
      . "WHERE "
      . "(product_id IS NULL OR product_id=0) "
      . "AND file.period_id > 0 AND "
      .

      # Need to exclude licence income files.
      "file.type_id <> " . Raptor::DB::Item::File::kFileTypeLicenseIncome . " GROUP BY " . "file.period_id, file.file_id ";

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

sub GetDateRange {
    my $class = shift;
    my $sql = "SELECT begin, end FROM "
            . "  (SELECT MIN(date_begin) begin FROM sale WHERE date_begin > '0000-00-00') AS q1, "
            . "  (SELECT MAX(date_end) end FROM sale) AS q2";

    my $collection = $class->SUPER::GetAll($sql);

    my $row = $collection->next();

    return $row ? ( $row->{begin}, $row->{end} ) : ();
}

sub UpdateMappedSalesIDs {
    my ($class, $hData, $salesID) = @_;

    my $args = join ', ', map { "$_ = '$hData->{$_}'" } keys %$hData;
    return unless $salesID && $args;

    my $sql = "UPDATE " . kTable . " SET $args WHERE sale_id IN ($salesID)";
    my $dbo = Common::RSApp::GetClientDB();

    $dbo->DoCmd($sql);

    return 1;
}

1;