#------------------------------------------------------------
# Copyright (C) 2012 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package RPS::DB::Item::DynamicReport::Sales::Base;

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

use lib '/app/tools/rps/lib';

use RPS::DB::Item::SaleRunMap;

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

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

# Most if not all of the Sales group will have the same base group of columns.

sub _config
{
    my ($class) = @_;

    return 
    {
        'file_service_id'       => {},
        'sale_service_id'       => {},
        'product_id'            => {},
        'product_type'          => {},
        'format_type'           => {},
        'media_type'            => {},
        'period_begin'          => {},
        'period_end'            => {},
        'units'                 => {},
        'sum_units'             => {},
        'price'                 => {},
        'free'                  => {},
        'currency_code'         => {},
        'conversion_rate'       => {},
        'country_code'          => {},
        'sales'                 => {},
        'sum_sales'             => {},
        'returns'               => {},
        'sum_returns'           => {},
        'total_revenue'         => {},
        'sum_total_revenue'     => {},
        'dist_fee'              => {},
    };

    # To add additional columns in a subclass, do this:
    #
    # my $config = $class->SUPER::_config();
    # $config->{'additional_column_1'} = {};
    # ...
    # return $config;
}



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


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

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

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

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


    my $sql = 'SELECT ';

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

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

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

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

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

    push (@$select, 'sale.sale_id AS sale_sale_id'),
    push (@$select, 'file.service_id AS file_service_id'),
    push (@$select, 'file.file_id AS file_file_id'),
    push (@$select, 'IFNULL(sale.service_id, file.service_id) AS sale_service_id'),
    push (@$select, 'sale.product_id AS product_id'),
    push (@$select, 'sale.product_type AS product_type'),
    push (@$select, 'sale.format_type AS format_type'),
    push (@$select, 'IF(sale.media_type = 2, "Video", "Audio") AS media_type'),
    push (@$select, 'sale.currency_code AS currency_code'),
    push (@$select, 'sale.conversion_rate AS conversion_rate'),
    push (@$select, 'sale.country_code AS country_code'),
    push (@$select, 'sale.price AS price'),
    push (@$select, 'sale.free AS free'),
    push (@$select, 'sale.units AS units');

    if( $consolidateBy eq 'Month' ) {
        push (@$select, 'CONCAT(YEAR(sale.date_begin), LPAD(MONTH(sale.date_begin), 2, "0")) AS period_begin'),
        push (@$select, 'CONCAT(YEAR(sale.date_end), LPAD(MONTH(sale.date_end), 2, "0")) AS period_end'),
    } elsif( $consolidateBy eq 'Quarter' ) {
        push (@$select, 'CONCAT(YEAR(sale.date_begin), "Q", QUARTER(sale.date_begin)) AS period_begin');
        push (@$select, 'CONCAT(YEAR(sale.date_end), "Q", QUARTER(sale.date_end)) AS period_end');
    } elsif( $consolidateBy eq 'Year' ) {
        push (@$select, 'YEAR(sale.date_begin) AS period_begin');
        push (@$select, 'YEAR(sale.date_end) AS period_end');
    } else {
        push (@$select, 'CONCAT(YEAR(sale.date_begin), LPAD(MONTH(sale.date_begin), 2, "0")) AS period_begin');
        push (@$select, 'CONCAT(YEAR(sale.date_end), LPAD(MONTH(sale.date_end), 2, "0")) AS period_end');
    }

    push (@$select, 'sale.sales AS sales');
    push (@$select, 'sale.returns AS returns');
    push (@$select, 'sale.total_revenue AS total_revenue');
    push (@$select, 'IFNULL(dist_fee_pct / 100, 0) AS dist_fee');
    push (@$select, 'file.orig_file_name AS file_name');

}

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

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

    push @$joins, 'JOIN file ON (sale.file_id = file.file_id)';
    push @$joins, "LEFT JOIN user_input_dist_fee ON (sale.file_id = user_input_dist_fee.file_id " .
                  "AND sale.format_type = user_input_dist_fee.associated_format)";
}

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

    push (@$where, "sale.product_id IS NOT NULL");

    my $prodTypeType = $args{prodTypeType};
    if( $prodTypeType eq 'Physical' )
    {
        push (@$where, 'file.physical = 1');
    }
    elsif( $prodTypeType eq 'Digital' )
    {
        push (@$where, 'file.physical = 0');
    }
    elsif( $prodTypeType eq 'Digital And Physical' )
    {
        #do nothing
    }
    else {
        die "product type needs to be Digital, Physical, or 'Digital And Physical'";
    }

    
    # JPK - We don't want license income sales in the current crop of reports.
    #
    push @$where, "sale.product_type <> 'L'";
    

    # We also don't want sales from invalid files to show up here.
    #
    push @$where, "file.file_status in (4,5)";    

    my $showByType = $args{showByType};
    my $startDate = $args{startDate};
    my $endDate = $args{endDate};
    my $runID = $args{runID};

    if ( $showByType eq "by_date" )
    {
        if ($startDate)
        {
            push @$where, "TO_DAYS(sale.date_begin) >= TO_DAYS(" . $class->quote($startDate) . ")";
        }
        if ($endDate)
        {
            push @$where, "TO_DAYS(sale.date_end) <= TO_DAYS(" . $class->quote($endDate) . ")";
        }          
    }
}


sub _getRunType
{
    my ($class, %args) = @_;
    my $runType = $args{runType};

    if ($runType eq 'Artist/Producer') {
        return RPS::DB::Item::SaleRunMap::kRunTypeArtistRoyalty;
    }
    elsif ($runType eq 'Label') {
        return RPS::DB::Item::SaleRunMap::kRunTypeLabelRoyalty;
    }
    elsif ($runType eq 'US Mechanicals') {
        return RPS::DB::Item::SaleRunMap::kRunTypeMechanical;
    }
    elsif ($runType eq 'UK Mechanicals') {
        return RPS::DB::Item::SaleRunMap::kRunTypeUKMechanical;
    }
    elsif ($runType eq 'CA Mechanicals') {
        return RPS::DB::Item::SaleRunMap::kRunTypeCAMechanical;
    }

}


1;