#------------------------------------------------------------
# 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::Format;
use RPS::DB::Item::SaleRunMap;
use RPS::DB::Item::Service;

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'          => {},
        'gross_revenue'     => {},
    };

    # 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 @sort;
    $class->_BuildSort( \@sort, %args );

    my $sql = 'SELECT DISTINCT ';

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

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

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

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

    print STDERR $sql . "\n";

    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, 'product.product_type_id 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, 'sale.gross_revenue AS gross_revenue' );
    push( @$select, 'IFNULL(dist_fee_pct / 100, 0) AS dist_fee' );
    push( @$select, 'file.orig_file_name AS file_name' );
    push( @$select, 'sale.line_num AS line_number' );
    push( @$select, 'sale.channel AS channel' );
    push( @$select, 'sale.price_level AS price_level' );
    push( @$select, 'sale.wholesale_price AS wholesale_price' );
    push( @$select, 'sale.retail_price AS retail_price' );
    push( @$select, qq/
        CASE
            WHEN period.period_id = 0 AND (period.name IS NULL OR period.name = '') THEN
                CONCAT(DATE_FORMAT(period.start_date, '%b %e, %Y'), ' - Present')
            WHEN period.period_id = 0 AND period.name IS NOT NULL AND period.name != '' THEN
                period.name
            WHEN (period.name IS NULL OR period.name = '') THEN
                CONCAT(DATE_FORMAT(period.start_date, '%b %e, %Y'), ' - ', DATE_FORMAT(period.end_date, '%b %e, %Y'))
            ELSE
                period.name
        END AS period_name
    / );
    push( @$select, 'file.period_id AS file_period_id' ),
    push( @$select, 'album_product.product_code AS product_code' );
    push( @$select, 'product.asset_id AS product_asset_id' );

    push( @$select, 'album.label_id AS label_id' );
    push( @$select, 'album.title AS album_name' );
    push( @$select, 'album.client_album_id AS client_album_id' );
    push( @$select, 'album.catalog_number AS catalog_id' );
    push( @$select, 'album.custom_1 AS album_custom_1' );
    push( @$select, 'album.custom_2 AS album_custom_2' );
    push( @$select, 'album.custom_3 AS album_custom_3' );
    push( @$select, 'album.album_id AS album_id' );
    push( @$select, 'album_product.title AS product_title' );
    push( @$select, 'album_product.release_date AS release_date' );
    push( @$select, 'album_product.upc_ean AS upc' );
    push( @$select, 'album_product.upc_alt AS upc_alt' );
    push( @$select, 'album_artist.name AS album_artist_name' );

    push( @$select, 'track.client_track_id AS client_track_id' );
    push( @$select, 'track.title AS track_name' );
    push( @$select, 'track.custom_1 AS track_custom_1' );
    push( @$select, 'track.custom_2 AS track_custom_2' );
    push( @$select, 'track.custom_3 AS track_custom_3' );
    push( @$select, 'track_artist.name AS track_artist_name' );
    push( @$select, 'master.isrc AS isrc' );
    push( @$select, 'product_track.disc_number AS disc_number' );
    push( @$select, 'product_track.disc_track AS disc_track' );

    push( @$select, $args{_tempTableName} . '.*' ) if exists $args{_tempTableName} && $args{_tempTableName};
}

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)';

    # We can have track products and album products in this report.
    # For track products, we need to pull data from it and its parent product.
    # So, we'll join the product table twice as 'product' and 'album_product'.
    # For album products, those will be the same record.  Seems like the only
    # way to do it all in one query though.
    push @$joins, 'LEFT JOIN product ON (product.product_id = sale.product_id)';
    push @$joins, 'LEFT JOIN product AS album_product ON album_product.product_id = ' .
        '( CASE ' .
            'WHEN sale.product_type = "T" THEN product.parent_product_id ' .
            'ELSE product.product_id ' .
        'END )';

    push @$joins, 'LEFT JOIN album ON (album_product.asset_id = album.album_id)';
    push @$joins, 'LEFT JOIN artist as album_artist ON (album.artist_id = album_artist.artist_id)';

    # Only track products will have corresponding entries in the track table
    # so I'll just join on 'track_id = 0' for album products.
    push @$joins, 'LEFT JOIN track ON track.track_id = ' .
        '( CASE ' .
            'WHEN sale.product_type = "T" THEN product.asset_id ' .
            'ELSE 0 ' .
        'END )';
    push @$joins, 'LEFT JOIN artist as track_artist ON (track.artist_id = track_artist.artist_id)';
    push @$joins, 'LEFT JOIN master ON (track.master_id = master.master_id)';
    push @$joins, 'LEFT JOIN product_track ON ( ' .
        'product_track.product_id = album_product.product_id AND ' .
        'product_track.track_id = track.track_id' .
    ')';

    push @$joins, 'LEFT JOIN period ON (file.period_id = period.period_id)'
}

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

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

    my $prodTypeType = $args{prodTypeType};
    if ( $prodTypeType eq 'Physical' ) {
        push( @$where, "sale.product_type NOT IN ('T','A')" );
    } elsif ( $prodTypeType eq 'Digital' ) {
        push( @$where, "sale.product_type IN ('T','A')");
    } 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};
    my $filter     = $args{filter};

    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) . ")";
        }
    }

    if ( $showByType eq "by_sales_period" ) {
        my $startPeriod = $args{startPeriod} // '';
        my $endPeriod   = $args{endPeriod}   // '';
        if ( $startPeriod =~ /^\d+$/ && $endPeriod =~ /^\d+$/ ) {
            # period_id = 0 is the current/latest period; it gets a new ID when closed
            $endPeriod = 0 if $startPeriod == 0;
            push @$where, "period.period_id = 0"                                       if $startPeriod == 0 && $endPeriod == 0;
            push @$where, "(period.period_id = 0 OR period.period_id >= $startPeriod)" if $startPeriod != 0 && $endPeriod == 0;
            push @$where, "period.period_id BETWEEN $startPeriod AND $endPeriod"       if $startPeriod != 0 && $endPeriod != 0;

        } else {
            # Fallback case
            push @$where, "period.period_id = -1"
        }
    }

    if ($filter) {
        my @filterGroups;

        foreach my $key ( keys %{ $filter } ) {

            # We need to convert the filter names to the database field names.
            # Let's store the original value because we'll need it later.
            my $originalKey = $key;

            foreach my $op ( keys %{ $filter->{$originalKey} } ) {
                # $op may change as well, so store that too.
                my $originalOp = $op;
                my @filters;

                foreach my $value ( @{ $filter->{$originalKey}{$originalOp} } ) {
                    # Reset these values now.
                    $key = $originalKey;
                    $op = $originalOp;
                    my $extraCriteria = '';

                    # For these filters, we just need to update the key
                    # to match the database field name.
                    #
                    if ($key eq 'album_artist') {
                        $key = 'album_artist.name';
                    }

                    if ($key =~ /^album_custom_(\d)$/) {
                        $key = 'album.custom_' . $1;

                        if ($op eq '!=') {
                            $extraCriteria = "OR ($key IS NULL)";
                        }
                    }

                    if ($key eq 'album_title') {
                        $key = 'album.title';
                    }

                    if ($key eq 'catalog_#') {
                        $key = 'album.catalog_number';
                    }

                    if ($key eq 'client_album_id') {
                        $key = 'album.client_album_id';

                        if ($op eq '!=') {
                            $extraCriteria = "OR ($key IS NULL)";
                        }
                    }

                    if ($key eq 'client_track_id') {
                        $key = 'track.client_track_id';

                        if ($op eq '!=') {
                            $extraCriteria = "OR ($key IS NULL)";
                        }
                    }

                    if ($key eq 'currency_code') {
                        $key = 'sale.currency_code';
                    }

                    if ($key eq 'isrc') {
                        $key = 'master.isrc';

                        if ($op eq '!=') {
                            $extraCriteria = "OR ($key IS NULL)";
                        }
                    }

                    if ($key eq 'release_date') {
                        $key = 'album_product.release_date';
                    }

                    if ($key eq 'territory') {
                        $key = 'sale.country_code';
                    }

                    if ($key eq 'track_artist') {
                        $key = 'track_artist.name';
                    }

                    if ($key =~ /^track_custom_(\d)$/) {
                        $key = 'track.custom_' . $1;

                        if ($op eq '!=') {
                            $extraCriteria = "OR ($key IS NULL)";
                        }
                    }

                    if ($key eq 'track_title') {
                        $key = 'track.title';
                    }

                    if ($key eq 'upc') {
                        $key = 'album_product.upc_ean';
                    }

                    if ($key eq 'upc_alt') {
                        $key = 'album_product.upc_alt';

                        if ($op eq '!=') {
                            $extraCriteria = "OR ($key IS NULL)";
                        }
                    }

                    # For these filters, we also need to manipulate the values.
                    #
                    if ($key eq 'distributor') {
                        $key = 'file.service_id';
                        my $service = RPS::DB::Item::Service->Lookup( service_name => $value );
                        if ($service) {
                            $value = $service->service_id;

                            # Distributor is a weird, derived value, so we need
                            # some weird logic to make it work.
                            if ($op eq '=') {
                                $extraCriteria = "AND sale.service_id != $value";
                            } else {
                                $extraCriteria = "OR (file.service_id = $value AND (sale.service_id = $value OR sale.service_id IS NULL))";
                            }
                        }
                    }

                    if ($key eq 'label_name') {
                        $key = 'album.label_id';
                        my $label = RPS::DB::Item::Label->Lookup( label_name => $value );
                        if ($label) {
                            $value = $label->label_id;
                        }
                    }

                    if ($key eq 'media_type') {
                        $key = 'sale.media_type';
                        if ($value eq 'Audio') {
                            $value = 1;
                        } else {
                            $value = 2; # Video
                        }
                    }

                    if ($key eq 'product_format') {
                        $key = 'sale.format_type';

                        if ($value eq 'PI (All)') {
                            # We want to include all of the PI subtypes
                            # when they use this filter.
                            $value = "('O', 'L', '!', '\@', '#', '\$', '%', '[', ']', ':', 'N', '&', '*', '(', ')', '_', '{', '}', ';')";

                            if ($op eq '=') {
                                $op = 'IN';
                            } else {
                                $op = 'NOT IN';
                            }
                        } else {
                            my $format = RPS::DB::Item::Format->Lookup( format_name => $value );
                            if ($format) {
                                $value = $format->format_type;
                            }
                        }
                    }

                    if ($key eq 'product_type') {
                        $key = 'product.product_type_id';
                        my $productType = RPS::DB::Item::ProductType->Lookup( description => $value );
                        if ($productType) {
                            $value = $productType->product_type_id;
                        }
                    }

                    if ($key eq 'service_name') {
                        $key = 'IFNULL(sale.service_id, file.service_id)';
                        my $service = RPS::DB::Item::Service->Lookup( service_name => $value );
                        if ($service) {
                            $value = $service->service_id;
                        }
                    }

                    # Finished normalizing, add it to query
                    if ($op =~ /IN/) {
                        push(@filters, "$key $op $value");
                    } else {
                        push(@filters, "(LOWER($key) $op LOWER(" . $class->quote($value) . ") $extraCriteria)");
                    }
                }

                my $whereOp = "OR";
                if ($originalOp eq '!=') {
                    $whereOp = "AND";
                }

                push(@filterGroups, "(" . join(" $whereOp ", @filters) . ")");
            }
        }

        $filter = join(" AND ", @filterGroups);
        push @$where, $filter;
    }
}

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

    if ( $consolidateBy ne 'None' ) {

        if ( $runType eq 'Artist/Producer' || $runType eq 'Label' ) {
            push( @$sort, 'rs_payee_id' );
        } elsif ( $runType eq 'US Mechanicals' || $runType eq 'CA Mechanicals' ) {
            push( @$sort, 'rs_publisher_payee_id' );
        }

        push( @$sort, 'period_begin' ),
        push( @$sort, 'period_end' ),
        push( @$sort, 'file.service_id' ),
        push( @$sort, 'sale_service_id' ),
        push( @$sort, 'sale.product_id' ),
        push( @$sort, 'sale.country_code' ),
        push( @$sort, 'sale.format_type' ),
        push( @$sort, 'sale.channel' ),
        push( @$sort, 'sale.price_level' ),
        push( @$sort, 'sale.retail_price' ),
        push( @$sort, 'sale.wholesale_price' ),
        push( @$sort, 'sale.currency_code' ),
        push( @$sort, 'sale.free' );
    }
}

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;