package Distribution::DB::Item::DistributionJob;
use strict;

use lib '/app/tools/common/lib';
use base 'Common::DB::Item';
use Common::Assert;
use Data::Dumper;

use constant kTable => 'distribution_job';
use constant kDB    => Common::DB::Item::kCommonDB;

sub GetRetryDeliveryCount {
    my $distProcessID = shift;
    my $clientID      = shift;

    assert($distProcessID);

    my $dbo = Common::RSApp::GetCommonDB();
    my $sql =
        "SELECT count(*) " . "FROM "
      . kTable
      . " INNER JOIN job USING (job_id) "
      . "WHERE "
      . " distribution_process_id = "
      . $dbo->DBQuote($distProcessID)
      . " AND client_id = "
      . $dbo->DBQuote($clientID)
      . " AND class = 'Delivery' ";

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

    return $count;
}

sub GetAllDistributionProcesses {
    my $class = shift;
    my %args  = @_;

    my $client  = $args{ClientID};
    my $service = $args{Subclass};
    my $age     = $args{Age};
    my $distID  = $args{DistributionProcessID};

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

    my $where;

    $where = "job.client_id = " . $dbo->DBQuote($client) if ($client);

    if ($service) {
        $where .= " AND " if ($where);
        $where .= "subclass = " . $dbo->DBQuote($service) if ($service);
    }

    if ($age) {
        $where .= " AND " if ($where);
        $where .= '( job.queue_date > DATE_SUB( Now(), interval ' . $dbo->DBQuote($age) . ' hour ) OR ';
        $where .= '  job.start_date > DATE_SUB( Now(), interval ' . $dbo->DBQuote($age) . ' hour ) OR';
        $where .= '  job.end_date > DATE_SUB( Now(), interval ' . $dbo->DBQuote($age) . ' hour ) )';
    }

    if ($distID) {
        $where .= " AND " if ($where);
        $where .= 'distribution_process_id = ' . $dbo->DBQuote($distID);
    }

    $where .= " AND " if ($where);
    $where .= 'class IN ( "Packager", "Delivery" )';

    if ($where) {
        $where = " WHERE $where ";
    } else {
        $where = '';
    }

    my $sql =
        "SELECT * " . "FROM "
      . kTable
      . " INNER JOIN job USING (job_id) "
      . "LEFT JOIN distribution_job_status ON ( "
      . "distribution_job.distribution_process_id = distribution_job_status.distribution_process_id AND "
      . "job.client_id = distribution_job_status.client_id ) "
      . $where
      . "GROUP BY job.client_id, distribution_job.distribution_process_id";

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

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

sub LookupByClient {
    my $class         = shift;
    my $distProcessID = shift;
    my $clientID      = shift;

    my $dbo = Common::RSApp::GetCommonDB();
    my $sql =
        "SELECT client_id, distribution_job.* " . "FROM "
      . kTable
      . " INNER JOIN job USING (job_id) "
      . "WHERE distribution_process_id = "
      . $dbo->DBQuote($distProcessID) . " AND "
      . "client_id = "
      . $dbo->DBQuote($clientID) . " "
      . "ORDER BY job_id desc ";

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

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

sub clearInvalidStatus {
    my $class = shift;

    my $dbo = Common::RSApp::GetCommonDB();
    my $sql =
        "DELETE distribution_job_status.* "
      . "FROM distribution_job "
      . "INNER JOIN job USING (job_id) "
      . "LEFT JOIN distribution_job_status ON ( "
      . "distribution_job.distribution_process_id = distribution_job_status.distribution_process_id AND "
      . "job.client_id = distribution_job_status.client_id ) "
      . "WHERE "
      . "distribution_job_status.date_modified < job.date_modified";

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

sub GetAllStatusUpdates {
    my $class = shift;

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

    my $sql =
        "SELECT distribution_job.*, job.client_id "
      . "FROM distribution_job "
      . "INNER JOIN job USING (job_id) "
      . "LEFT JOIN distribution_job_status ON ( "
      . "distribution_job.distribution_process_id = distribution_job_status.distribution_process_id AND "
      . "job.client_id = distribution_job_status.client_id ) "
      . "WHERE distribution_job_status_id IS NULL "
      . "GROUP BY distribution_job.distribution_process_id, job.client_id";

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

sub getJobCount {
    my $class    = shift;
    my $distID   = shift;
    my $clientID = shift;

    return unless ( $distID && $clientID );

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

    my $sql =
        "SELECT count(*) "
      . "FROM distribution_job "
      . "INNER JOIN job USING (job_id) "
      . "WHERE distribution_process_id = "
      . $dbo->DBQuote($distID) . " AND "
      . "client_id = "
      . $dbo->DBQuote($clientID);

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

1;