package Common::Stats::RPSBuilder;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Logger;
use Common::DatePeriod;

use base 'Common::Stats::Builder';

sub type { 'rps' }

sub dimensions {
    my $self = shift;
    my $dims = [ (qw/sales product_sales artist_sales summary/) ];
    $dims;
}

sub album_sale_sql {
    my $self = shift;
    my $sql  = (
        qq{
SELECT STRAIGHT_JOIN
    s.sale_id,
    s.file_id,
    IF(
        TIMESTAMPDIFF(MONTH,s.date_begin,s.date_end),0,
        ABS(TIMESTAMPDIFF(MONTH,s.date_begin,'1916-08-01'))
    ) month_id,
    ABS(TIMESTAMPDIFF(QUARTER,s.date_begin,'1916-07-01'))+667 quarter_id,
    YEAR(s.date_end) `year`,
    a.artist_id,
    s.product_id,
    s.product_type,
    s.format_type,
    IFNULL(s.service_id, f.service_id) service_id,
    a.label_id,
    s.units,
    s.price,
    s.country_code,
    s.conversion_rate,
    s.units * s.price * s.conversion_rate revenue
FROM
    sale s
JOIN
    file f ON f.file_id=s.file_id
JOIN
    product p ON p.product_id=s.product_id
JOIN
    album a ON a.album_id = p.asset_id
WHERE 1
    AND s.file_id=?
    AND s.product_type = 'A'
    AND IFNULL(s.product_id,0) != 0
    AND IFNULL(s.price,0) != 0
    AND IFNULL(s.conversion_rate,0) != 0
    AND s.format_type != 'M'
    AND p.product_type_id = 3
        }
    );
    $sql .= "    AND s.free=0" if $self->skipfree;
    $sql;
}

sub track_sale_sql {
    my $self = shift;
    my $sql  = (
        qq{
SELECT STRAIGHT_JOIN
    s.sale_id,
    s.file_id,
    IF(
        TIMESTAMPDIFF(MONTH,s.date_begin,s.date_end),0,
        ABS(TIMESTAMPDIFF(MONTH,s.date_begin,'1916-08-01'))
    ) month_id,
    ABS(TIMESTAMPDIFF(QUARTER,s.date_begin,'1916-07-01'))+667 quarter_id,
    YEAR(s.date_end) `year`,
    t.artist_id,
    s.product_id,
    s.product_type,
    s.format_type,
    IFNULL(s.service_id, f.service_id) service_id,
    a.label_id,
    s.units,
    s.price,
    s.country_code,
    s.conversion_rate,
    s.units * s.price * s.conversion_rate revenue
FROM
    sale s
JOIN
	file f ON f.file_id=s.file_id
JOIN
    product p ON p.product_id=s.product_id
JOIN
    track t ON t.track_id=p.asset_id
JOIN
    album a ON a.album_id = t.album_id
WHERE 1
    AND s.file_id=?
    AND s.product_type = 'T'
    AND IFNULL(s.product_id,0) != 0
    AND IFNULL(s.price,0) != 0
    AND IFNULL(s.conversion_rate,0) != 0
    AND s.format_type != 'M'
    AND p.product_type_id = 4
    }
    );
    $sql .= "    AND s.free=0" if $self->skipfree;
    $sql;

}

sub load_stage_table {
    my $self = shift;

    my $dbh = $self->dbh;

    # Truncate the staging table first, so that we can keep
    # the actual aggregates available as much as possible.
    $self->truncate('agg_sale_stage');

    ## file candidate join table for outer select
    $self->truncate('agg_file_candidate');

    ## insert open/closed files into file candidate
    $dbh->do(
        qq{
INSERT INTO agg_file_candidate
SELECT
	file_id
FROM
	file
WHERE 1
	AND physical IN (0,2)
	AND file_status IN (4,5)
    }
    );

    my $sql = "INSERT INTO agg_sale_stage";
    $sql .= $self->album_sale_sql;
    $sql .= "\nUNION ALL\n";
    $sql .= $self->track_sale_sql;

    my $sth = $self->dbh->prepare($sql);

    ## get all file candidates
    my $files = $dbh->selectcol_arrayref("SELECT file_id FROM agg_file_candidate");

    my $ttl_sale_records = $self->total_records || 0;
    logMessage( 'info', "Loading agg_sale_stage table: $ttl_sale_records records" );
    my $marker = 1000;
    if ( $ttl_sale_records >= 100000 and $ttl_sale_records < 500000 ) {
        $marker = 10000;
    } elsif ( $ttl_sale_records >= 500000 and $ttl_sale_records < 1000000 ) {
        $marker = 50000;
    } elsif ( $ttl_sale_records >= 1000000 and $ttl_sale_records < 5000000 ) {
        $marker = 100000;
    } elsif ( $ttl_sale_records >= 5000000 ) {
        $marker = 1000000;
    }
    logMessage( 'info', " o marker set to log every $marker records" );

    my $floor    = $marker;
    my $ttl_rows = 0;
    foreach my $file_id ( @{$files} ) {
        $sth->execute( $file_id, $file_id );
        $ttl_rows += $sth->rows;
        if ( $ttl_rows >= $floor ) {
            logMessage( 'info', " o ttl_rows loaded : $ttl_rows of $ttl_sale_records" );
            $floor += $marker;
        }
    }

    logMessage( 'info', " o ttl_rows loaded : $ttl_rows" );

}

1;

__END__

## comments from the old Stat::Aggregate

################################################################
# LOAD AGG_[M,Q,Y]_SALES
################################################################
# The AGG_Q_SALES table contains summed revenue and summed units
# rolled up by quarter_id, product_type, service_id, label_id & format_type


################################################################
# LOAD AGG_[M,Q,Y]_PRODUCT_SALES
################################################################
# The AGG_Q_PRODUCT_SALES table contains summed revenue and summed units
# rolled up by quarter_id, product_id, product_type, service_id, label_id & format_type

################################################################
# LOAD AGG_[M,Q,Y]_ARTIST_SALES
################################################################
# The AGG_X_ARTIST_SALES table contains summed revenue and summed units
# rolled up by quarter_id, artist_id, product_type, service_id, label_id & format_type

################################################################
# LOAD AGG_[M,Q,Y]_SUMMARY
################################################################
# The AGG_Q_SUMMARY table just contains a high-level summary of
# which quarters are included in the sales reports. It is meant
# to be used as a "boolean" to determine whether or not the
# aggregate tables were built successfully or not. Since it is
# the first aggregate table to be truncated and the last to be
# loaded, if there is any rows in agg_q_summary, that means the
# other tables are ready.