#------------------------------------------------------------
# Copyright (C) 2011 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package BookPub::DB::Item::SaleFeedFile;

use strict;
use warnings;

use Carp;

use lib '/app/tools/common/lib';
use Common::Assert;

use Common::DB::Item;
use base 'Common::DB::Item';

use constant kTable => 'sale_feed_file';
use constant kDB    => Common::DB::Item::kClientDB;

sub LookupByName {
    my $class             = shift;
    my %args              = @_;
    my $saleFeedServiceID = $args{sale_feed_service_id} || die "sale feed service id required";
    my $filename          = $args{filename} || die "filename required";

    my $sql =
        "SELECT "
      . kTable
      . ".* FROM "
      . kTable
      . " INNER JOIN sale_feed USING (sale_feed_id) "
      . " INNER JOIN sale_feed_service USING (sale_feed_service_id) "
      . " WHERE sale_feed_service_id = "
      . $class->quote($saleFeedServiceID)
      . " AND filename = "
      . $class->quote($filename);

    Log->debug("QUERY: $sql");
    my $collection = $class->SUPER::GetAll($sql);

    return $collection->next();
}

sub LookupByStartDate {
    my $class      = shift;
    my %args       = @_;
    my $startDate  = $args{start_date} || die "Start date required";
    my $saleFeedID = $args{sale_feed_id} || die "sale feed id required";

    my $sql =
        "SELECT "
      . kTable
      . ".* FROM "
      . kTable
      . " WHERE sale_feed_id = "
      . $class->quote($saleFeedID)
      . " AND start_date = "
      . $class->quote($startDate);

    Log->debug("QUERY: $sql");
    my $collection = $class->SUPER::GetAll($sql);

    return $collection->next();
}

sub GetAllByServiceID {
    my $class = shift;
    my $serviceID = shift || die;

    my $sql =
        "SELECT * FROM "
      . kTable
      . " INNER JOIN sale_feed USING (sale_feed_id) "
      . " INNER JOIN sale_feed_service USING (sale_feed_service_id) "
      . " WHERE service_id = "
      . $class->quote($serviceID);

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

sub GetAllBySaleFeedServiceID {
    my $class = shift;
    my $saleFeedServiceID = shift || die;

    my $sql =
        "SELECT * FROM "
      . kTable
      . " INNER JOIN sale_feed USING (sale_feed_id) "
      . " INNER JOIN sale_feed_service USING (sale_feed_service_id) "
      . " WHERE sale_feed_service_id = "
      . $class->quote($saleFeedServiceID);

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

sub GetAllBySaleFeedID {
    my $class = shift;
    my $saleFeedID = shift || die;

    my $sql = "SELECT * FROM " . kTable . " WHERE sale_feed_id = " . $class->quote($saleFeedID);

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

sub GetAllMissingBySaleFeedServiceID {
    my $class = shift;
    my $saleFeedServiceID = shift || die;

    my $sql =
        "SELECT * FROM "
      . kTable
      . " INNER JOIN sale_feed USING (sale_feed_id) "
      . " INNER JOIN sale_feed_service USING (sale_feed_service_id) "
      . " WHERE sale_feed_service_id = "
      . $class->quote($saleFeedServiceID) . " AND "
      . kTable
      . ".status = 'missing'";

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

sub GetAllMissingBySaleFeedID {
    my $class = shift;
    my $saleFeedID = shift || confess;

    my $sql = "SELECT * FROM " . kTable . " WHERE sale_feed_id = " . $class->quote($saleFeedID) . " AND " . kTable . ".status = 'missing'";

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

# Find any sale feed files covering the same time period with a different filename
sub hasDuplicateDate {
    my $class = shift;
    my %args  = @_;

    assert( $args{start_date} );
    assert( $args{end_date} );
    assert( $args{sale_feed_id} );
    assert( $args{filename} );

    my $sql =
        "SELECT * FROM "
      . kTable
      . " WHERE "
      . " start_date >= "
      . $class->quote( $args{start_date} )
      . " AND end_date <= "
      . $class->quote( $args{end_date} )
      . " AND sale_feed_id = "
      . $class->quote( $args{sale_feed_id} )
      . " AND filename <> "
      . $class->quote( $args{filename} );

    Log->debug("QUERY: $sql");

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

sub lastMissedDate {
    my $class             = shift;
    my %args              = @_;
    my $saleFeedServiceID = $args{sale_feed_service_id};
    my $saleFeedID        = $args{sale_feed_id};

    assert( $saleFeedServiceID || $saleFeedID );

    my $sql =
        "SELECT "
      . kTable
      . ".* FROM "
      . kTable
      . " INNER JOIN sale_feed USING (sale_feed_id) "
      . " INNER JOIN sale_feed_service USING (sale_feed_service_id) "
      . " WHERE "
      . kTable
      . ".status IN ( 'pending', 'missing' )";

    $sql .= " AND sale_feed_service_id = " . $class->quote($saleFeedServiceID) if ($saleFeedServiceID);
    $sql .= " AND sale_feed_id = " . $class->quote($saleFeedID) if ($saleFeedID);

    $sql .= " ORDER BY " . kTable . ".start_date";

    Log->debug("QUERY: $sql");
    my $collection = $class->SUPER::GetAll($sql);

    return $collection->hasNext() ? $collection->next()->start_date : undef;
}

sub getFirstDownloadDate {
    my $class             = shift;
    my %args              = @_;
    my $saleFeedID        = $args{sale_feed_id};
    my $saleFeedServiceID = $args{sale_feed_service_id};

    assert( $saleFeedID || $saleFeedServiceID );

    my $sql =
        "SELECT MIN("
      . kTable
      . ".start_date) as start_date FROM "
      . kTable
      . " INNER JOIN sale_feed USING (sale_feed_id) "
      . " INNER JOIN sale_feed_service USING (sale_feed_service_id) "
      . " WHERE "
      . kTable
      . ".status = 'downloaded'";

    $sql .= " AND sale_feed_service_id = " . $class->quote($saleFeedServiceID) if ($saleFeedServiceID);
    $sql .= " AND sale_feed_id = " . $class->quote($saleFeedID) if ($saleFeedID);

    Log->debug("QUERY: $sql");
    my $collection = $class->SUPER::GetAll($sql);

    return $collection->hasNext() ? $collection->next()->start_date : undef;
}

sub getLastDownloadDate {
    my $class             = shift;
    my %args              = @_;
    my $saleFeedID        = $args{sale_feed_id};
    my $saleFeedServiceID = $args{sale_feed_service_id};

    assert( $saleFeedID || $saleFeedServiceID );

    my $sql =
        "SELECT MAX("
      . kTable
      . ".start_date) as start_date FROM "
      . kTable
      . " INNER JOIN sale_feed USING (sale_feed_id) "
      . " INNER JOIN sale_feed_service USING (sale_feed_service_id) "
      . " WHERE "
      . kTable
      . ".status = 'downloaded'";

    $sql .= " AND sale_feed_service_id = " . $class->quote($saleFeedServiceID) if ($saleFeedServiceID);
    $sql .= " AND sale_feed_id = " . $class->quote($saleFeedID) if ($saleFeedID);

    Log->debug("QUERY: $sql");
    my $collection = $class->SUPER::GetAll($sql);

    return $collection->hasNext() ? $collection->next()->start_date : undef;
}

1;