#------------------------------------------------------------
# Copyright (C) 2012 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------
package RPS::DB::Item::ReportQueries::MechanicalReservePipeline;

use strict;
use warnings;
use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::DB::ItemCollection;
use Common::Assert;

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

use RPS::DB::Item::LicenseReserve;
use RPS::DB::Item::TrackLicense;

use RPS::DB::Item::ReserveLiquidation;
use RPS::DB::Item::Product;
use RPS::DB::Item::Track;
use RPS::DB::Item::Album;

use base 'RPS::DB::Item::ReportQueries';

# _config: returns an empty hash with the fields that would be
# returned if we could get this data from a single database table
#
sub _config {
    return {
        'track_id'          => {},
        'track_title'       => {},
        'album_id'          => {},
        'album_title'       => {},
        'catalog_number'    => {},
        'upc'               => {},
        'publisher_name'    => {},
        'publisher_id'      => {},
        'client_account_id' => {},
        'region_name'       => {},
        'region_id'         => {},

        'total_revenue' => {},
        'total_units'   => {},
        'revenue_0'     => {},
        'units_0'       => {},
        'revenue_1'     => {},
        'units_1'       => {},
        'revenue_2'     => {},
        'units_2'       => {},
        'revenue_3'     => {},
        'units_3'       => {},
        'revenue_4'     => {},
        'units_4'       => {},
        'revenue_5'     => {},
        'units_5'       => {},
        'revenue_6'     => {},
        'units_6'       => {},
        'revenue_7'     => {},
        'units_7'       => {},
        'revenue_8'     => {},
        'units_8'       => {},
        'revenue_9'     => {},
        'units_9'       => {},
    };
}

sub ReportAvailable {
    my ( $class, $runID ) = @_;

    my $dbo = Common::RSApp::GetClientDB();
    my $sth = $dbo->DoCmdWithPlaceholders("SELECT COUNT(*) from mechanical_reserve_pipeline where mechanical_run_id = ?", [$runID]);
    my $hr  = $sth->fetchrow_hashref();
    if ( $hr->{'COUNT(*)'} > 0 ) {
        return 1;
    }
    return 0;
}

sub GetByMechanicalRunID {
    my ( $class, $runID, $regionID ) = @_;

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

    my $regionFilter;

    if ( defined $regionID ) {
        $regionFilter = " AND region.region_id = " . $dbo->DBQuote($regionID);
    }

    my @selectFields;
    push @selectFields, "region.name as region_name";
    push @selectFields, "region.region_id";

    push @selectFields, "publisher.publisher_name as publisher_name";
    push @selectFields, "publisher.publisher_id";
    push @selectFields, "publisher.client_account_id";

    push @selectFields, "mechanical_reserve_pipeline.upc";

    push @selectFields, "album.catalog_number";
    push @selectFields, "album.album_id";
    push @selectFields, "album.title as album_title";

    push @selectFields, "track.track_id";
    push @selectFields, "track.title as track_title";

    push @selectFields, "mechanical_reserve_pipeline.total_revenue";
    push @selectFields, "mechanical_reserve_pipeline.total_units";
    push @selectFields, "mechanical_reserve_pipeline.revenue_0";
    push @selectFields, "mechanical_reserve_pipeline.units_0";
    push @selectFields, "mechanical_reserve_pipeline.revenue_1";
    push @selectFields, "mechanical_reserve_pipeline.units_1";
    push @selectFields, "mechanical_reserve_pipeline.revenue_2";
    push @selectFields, "mechanical_reserve_pipeline.units_2";
    push @selectFields, "mechanical_reserve_pipeline.revenue_3";
    push @selectFields, "mechanical_reserve_pipeline.units_3";
    push @selectFields, "mechanical_reserve_pipeline.revenue_4";
    push @selectFields, "mechanical_reserve_pipeline.units_4";
    push @selectFields, "mechanical_reserve_pipeline.revenue_5";
    push @selectFields, "mechanical_reserve_pipeline.units_5";
    push @selectFields, "mechanical_reserve_pipeline.revenue_6";
    push @selectFields, "mechanical_reserve_pipeline.units_6";
    push @selectFields, "mechanical_reserve_pipeline.revenue_7";
    push @selectFields, "mechanical_reserve_pipeline.units_7";
    push @selectFields, "mechanical_reserve_pipeline.revenue_8";
    push @selectFields, "mechanical_reserve_pipeline.units_8";
    push @selectFields, "mechanical_reserve_pipeline.revenue_9";
    push @selectFields, "mechanical_reserve_pipeline.units_9";

    my $selectClause = join( ',', @selectFields );

    my $sql =
        "SELECT $selectClause "
      . "FROM region, publisher, track, album, mechanical_reserve_pipeline "
      . "WHERE "
      . "mechanical_reserve_pipeline.mechanical_run_id = " . $dbo->DBQuote($runID)
      . "AND album.album_id         = mechanical_reserve_pipeline.album_id "
      . "AND region.region_id       = mechanical_reserve_pipeline.region_id "
      . "AND publisher.publisher_id = mechanical_reserve_pipeline.publisher_id "
      . "AND track.track_id         = mechanical_reserve_pipeline.track_id "
      . $regionFilter
      . " ORDER BY track_title, album_title, publisher_name, region_name";

    # Return the table as a collection
    #
    return $class->SUPER::GetAll($sql);
}

sub DeleteByMechanicalRunID {
    my ( $class, $runID ) = @_;

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

    $dbo->DoCmdWithPlaceholders("DELETE FROM mechanical_reserve_pipeline WHERE mechanical_run_id = ?", [$runID]);
}

sub GetDistinctRegions {
    my ( $class, $runID ) = @_;

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

    my @selectFields;
    push @selectFields, "region.name as region_name";
    push @selectFields, "region.region_id";

    my $selectClause = join( ',', @selectFields );

    my $sql =
        "SELECT DISTINCT $selectClause "
      . " FROM region, mechanical_reserve_pipeline "
      . " WHERE mechanical_reserve_pipeline.mechanical_run_id = " . $dbo->DBQuote($runID)
      . " AND region.region_id = mechanical_reserve_pipeline.region_id"
      . " ORDER BY region_name";

    # Return the table as a collection
    #
    return $class->SUPER::GetAll($sql);
}

1;