#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# Copyright (C) 2011 RoyaltyShare, Inc.   All Rights Reserved
#---------------------------------------------------------------

package RPS::DB::Item::DynamicReport::Licenses::UK;

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

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

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

# We'll return the same columns for both types of license reports.
#
sub _config {
    my ($class) = @_;
    return {
        'mcps_license_id'          => {},
        'album_title'              => {},
        'catalog_number'           => {},
        'artist'                   => {},
        'product_type_id'          => {},
        'product_type_description' => {},
        'product_id'               => {},
        'product_code'             => {},
        'mcps_id'                  => {},
        'product_status'           => {},
        'release_date'             => {},
        'default_price'            => {},
        'default_price_level'      => {},
        'payor'                    => {},
        'supplier_code'            => {},
        'tv_advertised'            => {},
        'periods_completed'        => {},
        'retentions_period_1'      => {},
        'price_tier_period_1'      => {},
        'price_period_1'           => {},
        'retentions_period_2'      => {},
        'price_tier_period_2'      => {},
        'price_period_2'           => {},
        'retentions_period_3'      => {},
        'price_tier_period_3'      => {},
        'price_period_3'           => {},
        'retentions_period_4'      => {},
        'price_tier_period_4'      => {},
        'price_period_4'           => {},
        'upc'                      => {},
        'deleted'                  => {},
        'deleted_date'             => {},
        'album_status'             => {},
        'dvd_category'             => {},

        #        'date_created'          => {},
        #        'created_by'            => {},
        #        'date_modified'         => {},
        #        'modified_by'           => {},
    };

}

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

    # We want to sort by product_type.
    # Alas, we don't HAVE the product_type strings in the client databases, just the ids.
    # The product_type table lives in RSCOMMON.
    # So, I'm going to use a temporary table and populate it with the RSCOMMON data, plus
    # entries for 'All Product' and 'All Digital Products', just for kicks.
    #
    my $tempTableName = $class->_BuildProductTypeTempTable(%args);
    $args{_productTypeTable} = $tempTableName;

    my @select;
    $class->_BuildSelect( \@select, %args );

    my @joins;
    $class->_BuildJoins( \@joins, %args );

    my @groupBy;
    $class->_BuildGroupBy( \@groupBy, %args );

    my @orderBy;
    $class->_BuildOrderBy( \@orderBy, %args );

    my @where;
    $class->_BuildWhere( \@where, %args );

    my @from;
    $class->_BuildFrom( \@from, %args );

    my $sql = 'SELECT ' . join( ',', @select ) . ' FROM ' . join( ',', @from );

    if ( scalar @joins ) {
        $sql .= ' ' . join( ' ', @joins );
    }

    if ( scalar @where ) {
        $sql .= ' WHERE ' . join( ' AND ', @where );
    }

    if ( scalar @groupBy ) {
        $sql .= ' GROUP BY ' . join( ',', @groupBy );
    }

    if ( scalar @orderBy ) {
        $sql .= ' ORDER BY ' . join( ',', @orderBy );
    }

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

sub _BuildProductTypeTempTable {
    my ( $class, %args ) = @_;
    my $tempTableName = 'zz_product_type_' . $$;

    # JPK - We shouldn't need entries for the magical 'All *' product types, since
    # we are reporting the product types for actual products (which can't have global types).
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql =
        "CREATE TEMPORARY TABLE $tempTableName "
      . ' (`product_type_id` int(10) unsigned NOT NULL PRIMARY KEY,'
      . '  `description` varchar(255) )';
    $dbo->DoCmd($sql);

    my $allTypes = RPS::DB::Item::ProductType->GetAll();
    while ( my $type = $allTypes->next() ) {
        $dbo->DoCmd(
            "INSERT INTO $tempTableName SET product_type_id=" . $type->product_type_id() . ", description='" . $type->description() . "'" );
    }

    return $tempTableName;
}

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

    push @$from, 'mcps_license';
}

sub _BuildGroupBy {
    my ( $class, $groupBy, %args ) = @_;
}

sub _BuildOrderBy {
    my ( $class, $orderBy, %args ) = @_;
    push @$orderBy, 'album_title';
    push @$orderBy, 'product_type_description';
}

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

    my $dateType  = $args{dateType};
    my $startDate = $args{startDate};
    my $endDate   = $args{endDate};

    if ($dateType) {
        if ($startDate) {
            push @$where, "TO_DAYS(mcps_license.$dateType) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate) {
            push @$where, "TO_DAYS(mcps_license.$dateType) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }
    }
}

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

    my $tempTable = $args{_productTypeTable};

    push @$select, "album.title AS album_title";
    push @$select, "album.catalog_number AS catalog_number";
    push @$select, "IF(album.status = 1, 'Active', 'Inactive') as album_status";
    push @$select, "artist.name AS artist";
    push @$select, "product.product_type_id AS product_type_id";
    push @$select, "$tempTable.description AS product_type_description";
    push @$select, "product.product_id AS product_id";
    push @$select, "product.product_code AS product_code";
    push @$select, "product.default_price_level_id AS default_price_level";
    push @$select, "mcps_license.mcps_id AS mcps_id";
    push @$select, "mcps_license.mcps_license_id AS mcps_license_id";
    push @$select, "mcps_license.dvd_category AS dvd_category";
    push @$select, "IF(product.product_status_id = 1, 'Active', 'Inactive') as product_status";
    push @$select, "product.release_date as release_date";
    push @$select, "product.upc_ean as upc";
    push @$select, "IF(product.deleted = 1, 'Yes', 'No') as deleted";
    push @$select, "product.deleted_date as deleted_date";

    push @$select, "price.retail as default_price";

    push @$select, "payor.name as payor";
    push @$select, "payor.supplier_code as supplier_code";
    push @$select, "IF(mcps_license.retention_category = 'T', 'Y', 'N') as tv_advertised";
    push @$select, "mcps_license.initial_periods_completed as periods_completed";

    push @$select, "retention_1.units_held as retentions_period_1";
    push @$select, "retention_2.units_held as retentions_period_2";
    push @$select, "retention_3.units_held as retentions_period_3";
    push @$select, "retention_4.units_held as retentions_period_4";

    push @$select, "retention_1.price_level_id as price_tier_period_1";
    push @$select, "retention_2.price_level_id as price_tier_period_2";
    push @$select, "retention_3.price_level_id as price_tier_period_3";
    push @$select, "retention_4.price_level_id as price_tier_period_4";

    push @$select, "retention_1.price as price_period_1";
    push @$select, "retention_2.price as price_period_2";
    push @$select, "retention_3.price as price_period_3";
    push @$select, "retention_4.price as price_period_4";

    #    push @$select, "mcps_license.date_created AS date_created";
    #    push @$select, "mcps_license.date_modified AS date_modified";
    #    push @$select, "mcps_license.created_by AS created_by";
    #    push @$select, "mcps_license.modified_by AS modified_by";
}

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

    my $tempTable = $args{_productTypeTable};

    push @$joins, "LEFT JOIN product ON (product.product_id = mcps_license.product_id)";
    push @$joins, "LEFT JOIN album ON (product.asset_id = album.album_id)";
    push @$joins, "LEFT JOIN artist ON (album.artist_id = artist.artist_id)";
    push @$joins, "LEFT JOIN payor ON (mcps_license.payor_id = payor.payor_id)";
    push @$joins,
"LEFT JOIN product_price ON (product_price.product_id = mcps_license.product_id && product_price.price_level_id = product.default_price_level_id)";
    push @$joins, "LEFT JOIN price ON (price.price_id = product_price.price_id)";
    push @$joins, "LEFT JOIN $tempTable ON (product.product_type_id = $tempTable.product_type_id)";

    push @$joins,
"LEFT JOIN mcps_license_retention AS retention_1 ON (retention_1.mcps_license_id = mcps_license.mcps_license_id && retention_1.period_id = 1)";
    push @$joins,
"LEFT JOIN mcps_license_retention AS retention_2 ON (retention_2.mcps_license_id = mcps_license.mcps_license_id && retention_2.period_id = 2)";
    push @$joins,
"LEFT JOIN mcps_license_retention AS retention_3 ON (retention_3.mcps_license_id = mcps_license.mcps_license_id && retention_3.period_id = 3)";
    push @$joins,
"LEFT JOIN mcps_license_retention AS retention_4 ON (retention_4.mcps_license_id = mcps_license.mcps_license_id && retention_4.period_id = 4)";
}

1;