package Distribution::DB::Item::ProductDistribution;
use strict;
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 => 'product_distribution';
use constant kDB    => Common::DB::Item::kClientDB;

sub GetAllToDistribute {
    my ( $class, %args ) = @_;
    my $dbo           = Common::RSApp::GetClientDB();
    my $album_where   = "";
    my $service_where = "";

    if ( $args{album_ids} ) {
        $album_where = " AND album_distribution_ready.album_id IN ( " . join( ",", @{ $args{album_ids} } ) . " )";
    }

    if ( $args{service_ids} ) {
        $album_where = " AND service_id IN ( " . join( ",", @{ $args{service_ids} } ) . " )";
    }

    if ( $args{service_id} ) {
        $service_where = " AND service_id = " . $dbo->DBQuote( $args{service_id} );
    }

    my $sql =
        "SELECT * "
      . "FROM album "
      . "INNER JOIN album_distribution_ready USING (album_id) "
      . "INNER JOIN product ON ( product.asset_id = album.album_id AND "
      . "product_type_id = 3 ) "
      . "INNER JOIN product_digital USING (product_id) "
      . "INNER JOIN product_distribution USING (product_id) "
      . "WHERE delivery_date IS NULL "
      . " AND metadata_valid "
      . " AND image_valid "
      . " AND audio_valid "
      . " AND distribution_process_id IS NULL "
      . " AND not invalid "
      . " AND dist_allow = 1"
      . $service_where
      . $album_where . " "
      . "GROUP BY product.product_id ";

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

sub GetAllRunning {
    my ( $class, %args ) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql =
        "SELECT "
      . kTable
      . ".*, job_id, "
      . " GROUP_CONCAT( service_id ) as services " . "FROM "
      . kTable . " "
      . "INNER JOIN distribution_process USING ( distribution_process_id ) "
      . "WHERE delivery_date IS NULL "
      . " AND not invalid";
    if ( $args{date} ) {
        $sql .= " AND DATE(delivery_date) = " . $dbo->DBQuote( $args{date} );
    }

    $sql .= " GROUP BY product_distribution_id ";

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

sub GetAll {
    my ( $class, %args ) = @_;
    my $dbo = Common::RSApp::GetCommonDB();

    my $sql = "SELECT * FROM " . kTable . " INNER JOIN product USING (product_id) ";

    $sql .= " WHERE distribution_process_id= " . $dbo->DBQuote( $args{distributionProcessID} )
      if ( $args{distributionProcessID} );

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

sub GetAllByDistributionProcessID {
    my ( $class, $distProcessID ) = @_;
    assert($distProcessID);

    return $class->GetAll( distributionProcessID => $distProcessID );
}

sub GetDeliveryReport {
    my ( $class, $date ) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = "SELECT *, GROUP_CONCAT( service_id ) as services FROM product_distribution ";

    $sql .= " WHERE DATE(delivery_date) = " . $dbo->DBQuote($date);
    $sql .= " GROUP BY product_id";

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

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

    assert($class);

    return unless ( $args{product_id} && $args{service_id} );

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

    my $sql =
        'SELECT * '
      . 'FROM product_distribution '
      . 'WHERE service_id = '
      . $dbo->DBQuote( $args{service_id} ) . " AND "
      . 'product_id = '
      . $dbo->DBQuote( $args{product_id} ) . " AND "
      . 'not invalid';

    Common::Log::Debug("QUERY: $sql");

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

    return $collection->next;
}

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

    assert($class);
    assert( defined( $args{product_id} ) );
    assert( defined( $args{service_id} ) );

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

    my $sql =
        'DELETE '
      . 'FROM product_distribution '
      . 'WHERE service_id = '
      . $dbo->DBQuote( $args{service_id} ) . " AND "
      . 'product_id = '
      . $dbo->DBQuote( $args{product_id} ) . " AND "
      . 'distribution_process_id IS NULL AND '
      . 'delivery_date IS NULL AND '
      . 'not invalid';

    Common::Log::Debug("QUERY: $sql");
    $dbo->DoCmd($sql);
}

sub GetSuccessfulDistributionCount {
    my $class    = shift;
    my $album_id = shift;
    my $dbo      = Common::RSApp::GetClientDB();

    assert($album_id);

    my $sql =
        "SELECT count(distinct(service_id)) "
      . "FROM album "
      . "INNER JOIN product ON ( product.asset_id = album.album_id AND "
      . "product_type_id = 3 ) "
      . "INNER JOIN product_distribution USING (product_id) "
      . "WHERE delivery_date IS NOT NULL "
      . " AND album_id = "
      . $dbo->DBQuote($album_id)
      . " AND not invalid";

    Common::Log::Debug("QUERY: $sql");
    my $sth = $dbo->DoCmd($sql);

    my ($count) = $sth->fetchrow_array();
    return $count;
}

sub GetStagedDistributionCount {
    my $class    = shift;
    my %args     = @_;
    my $album_id = $args{album_id};
    my $services = "";

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

    assert($album_id);

    if ( $args{service_ids} ) {
        $services = " AND service_id IN (" . join( ",", @{ $args{service_ids} } ) . ") ";
    }

    my $sql =
        "SELECT count(distinct(service_id)) "
      . "FROM album "
      . "INNER JOIN product ON ( product.asset_id = album.album_id AND "
      . "product_type_id = 3 ) "
      . "INNER JOIN product_distribution USING (product_id) "
      . "WHERE (distribution_process_id IS NULL OR distribution_process_id = 0) "
      . " AND album_id = "
      . $dbo->DBQuote($album_id)
      . " AND not invalid"
      . $services;

    Common::Log::Debug("QUERY: $sql");
    my $sth = $dbo->DoCmd($sql);

    my ($count) = $sth->fetchrow_array();
    return $count;
}

sub setQueuedDate {
    my $class                   = shift;
    my $product_distribution_id = shift;
    my $dbo                     = Common::RSApp::GetClientDB();

    assert($product_distribution_id);

    my $sql = "UPDATE " . kTable . " SET date_queued = NOW() WHERE product_distribution_id = " . $dbo->DBQuote($product_distribution_id);

    Common::Log::Debug("QUERY: $sql");
    my $sth = $dbo->DoCmd($sql);
}

sub setPackageDate {
    my $class                   = shift;
    my $product_distribution_id = shift;
    my $dbo                     = Common::RSApp::GetClientDB();

    assert($product_distribution_id);

    my $sql = "UPDATE " . kTable . " SET package_date = NOW() WHERE product_distribution_id = " . $dbo->DBQuote($product_distribution_id);

    Common::Log::Debug("QUERY: $sql");
    my $sth = $dbo->DoCmd($sql);
}

sub setDeliveredDate {
    my $class                   = shift;
    my $product_distribution_id = shift;
    my $dbo                     = Common::RSApp::GetClientDB();

    assert($product_distribution_id);

    my $sql = "UPDATE " . kTable . " SET delivery_date = NOW() WHERE product_distribution_id = " . $dbo->DBQuote($product_distribution_id);

    Common::Log::Debug("QUERY: $sql");
    my $sth = $dbo->DoCmd($sql);
}

sub setDenied {
    my $class                   = shift;
    my $product_distribution_id = shift;
    my $msg                     = shift;
    my $dbo                     = Common::RSApp::GetClientDB();

    assert($product_distribution_id);
    assert($msg);

    my $sql =
        "UPDATE "
      . kTable
      . " SET denied = "
      . $dbo->DBQuote($msg)
      . " WHERE product_distribution_id = "
      . $dbo->DBQuote($product_distribution_id);

    Common::Log::Debug("QUERY: $sql");
    my $sth = $dbo->DoCmd($sql);
}

sub SetDeniedByUPC {
    my $class      = shift;
    my $upc        = shift;
    my $service_id = shift;
    my $msg        = shift;
    my $dbo        = Common::RSApp::GetClientDB();

    assert($upc);
    assert($service_id);
    assert($msg);

    my $sql =
        "UPDATE product_distribution INNER JOIN product ON "
      . "( product_distribution.product_id = product.product_id AND "
      . "product_type_id = 3 ) "
      . "SET delivery_date = NULL, denied = "
      . $dbo->DBQuote($msg) . " "
      . "WHERE upc_ean = "
      . $dbo->DBQuote($upc) . " AND "
      . "invalid <> 1 " . " AND "
      . "service_id = "
      . $dbo->DBQuote($service_id);

    Common::Log::Debug("QUERY: $sql");
    my $sth = $dbo->DoCmd($sql);
}

sub SetDeliveryDateByUPC {
    my $class      = shift;
    my $upc        = shift;
    my $service_id = shift;

    assert($upc);
    assert($service_id);

    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "UPDATE product_distribution INNER JOIN product ON "
      . "( product_distribution.product_id = product.product_id AND "
      . "product_type_id = 3 ) "
      . "SET delivery_date = NOW(), denied = NULL "
      . "WHERE upc_ean = "
      . $dbo->DBQuote($upc) . " AND "
      . "invalid <> 1 " . " AND "
      . "service_id = "
      . $dbo->DBQuote($service_id);

    Common::Log::Debug("QUERY: $sql");
    $dbo->DoCmd($sql);
}

sub uniqueServiceID {
    my $class     = shift;
    my $distJobID = shift;
    my $svcID     = shift;
    my $dbo       = Common::RSApp::GetClientDB();

    my $sth = $dbo->DoCmd( 'SELECT COUNT(*) FROM ' . kTable . " WHERE distribution_process_id=$distJobID AND service_id !=$svcID" );

    return $sth->fetchrow_arrayref()->[0] ? 0 : 1;
}

sub isServiceIDUnique {
    my $class     = shift;
    my $distJobID = shift;

    assert($distJobID);

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

    my $sql = 'SELECT COUNT(DISTINCT(service_id)) FROM ' . kTable . ' WHERE distribution_process_id = ' . $dbo->DBQuote($distJobID);

    Common::Log::Debug("QUERY: $sql");

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

    my ($count) = $sth->fetchrow_array();
    return $count == 1;
}

sub getDeliveryReportByService {
    my $class = shift;
    my $dbo   = Common::RSApp::GetClientDB();
    my $sql =
        "SELECT upc_ean, product_distribution.* "
      . "FROM product_distribution INNER JOIN product USING (product_id) "
      . "WHERE invalid <> 1 and delivery_date IS NOT NULL";

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

1;