#!/usr/bin/perl

package Stats::Aggregate;

use strict;

use Date::Calc;
use Data::Dumper;
use Getopt::Std;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::Consts qw(%CLIENT_SALE_AGG);

use lib '/app/tools/stats/lib';
use Stats::Sales;

use lib '/app/tools/data_classes/lib';
use Client::Client;

$| = 1;
### aggregate table names

use constant AGG_SALE_STAGE_TABLE   => 'agg_sale_stage';
use constant AGG_FILE_SUMMARY_TABLE => 'agg_file_summary';

use constant AGG_TABLE_PERIODS  => qw(m q y);
use constant PERIOD_TYPE_TABLES => qw(sales product_sales artist_sales summary);

use constant RUN_LOG => '/app/shared/sale_report/stats.log';

sub new {
    my ( $class, %args ) = @_;
    my $self = bless {}, $class;

    return $self->_init(%args);
}

sub _init {
    my ( $self, %args ) = @_;
    $self->{_clientID} = $args{client_id};
    $self->{_force}    = $args{force};
    $self->{_debug}    = $args{debug};

    $self->{_dbo}  = new Common::RSDB( client_id => $self->{_clientID}, dbi_attr => { RaiseError => 1 } );
    $self->{_dbh}  = $self->{_dbo}->DBH();
    $self->{_dboC} = new Common::RSDB( client_id => 0 );
    $self->{_dbhC} = $self->{_dboC}->DBH();

    return $self;
}

sub generate {
    my $self = shift;

    #    Conflicts with base class Common::Script stderr caputre
    #    open(STDERR, ">>" . RUN_LOG) unless (-t STDIN && -t STDOUT);

    my $client_id     = $self->{_clientID};
    my $force_rebuild = $self->{_force};

    unless ( $client_id =~ /^\d+$/ && $client_id > 0 ) {
        die usage("client_id '$client_id' must be a number greater than zero");
    }

    $self->debug("client_id = $client_id");

    # get client's free track/stream preference
    my $client = new Client::Client( client_id => $client_id );
    $self->{_skipFree} = $client->SkipFreeTracks();

    # good_sale_min is the minimum threshold of good sale records to total records
    # for a file to be included in the sales aggregates. It should be specified as
    # a percentage out of 100. See the sub load_stage_table for the definition of
    # "good" sale record.
    my $good_sale_min = $CLIENT_SALE_AGG{$client_id}->{good_sale_min};
    unless ( $good_sale_min >= 1 && $good_sale_min <= 100 ) {
        die "The 'good_sale_min' value specified in \%Common:Consts:CLIENT_SALE_AGG must be percentage between 1 and 100";
    }
    $self->{_goodSaleMin} = $good_sale_min;

    $self->debug("fetching sales data");

    my $sale_data = $self->get_sale_data();

    my $tables_rebuilt = 0;
    if ( $force_rebuild || $self->sale_data_has_changed($sale_data) ) {

        # the sale data has changed; rebuild the aggregate tables
        my $start = time;
        $self->rebuild_aggregates();
        $tables_rebuilt = 1;
        $self->debug( sprintf( "runtime: %0.2f min", ( time - $start ) / 60 ) );
    }

    $self->log_sale_data( $sale_data, $tables_rebuilt );

}

#/////////////////////////////////////////////////

sub get_sale_data {
    my $self = shift;

    my $sql = qq{
					 SELECT now() AS date_started,
					 COUNT(*) AS total_records,
					 IFNULL(SUM(units),0) AS total_units,
					 IFNULL(ROUND(SUM(IFNULL(units,0) * IFNULL(price,0) * IFNULL(conversion_rate,0)),8),0) as total_revenue,
					 IFNULL(MAX(sale_id),0) AS max_sale_id,
					 IFNULL(MAX(date_modified),0) AS max_date_modified,
					 IFNULL(MAX(date_created),0) AS max_date_created
					 FROM sale
                     WHERE product_type IN ('A','T')
					};
    $sql .= " AND free = 0" if $self->{_skipFree};

    my $sth = $self->{_dbh}->prepare($sql);

    $sth->execute();

    my $sale_data = $sth->fetchrow_hashref();

    $sth->finish();

    return $sale_data;
}

sub sale_data_has_changed {
    my $self      = shift;
    my $sale_data = shift;

    my $sql = qq{
					 SELECT total_records, total_units, total_revenue,
					 max_sale_id, max_date_created, max_date_modified
					 FROM agg_sale_log
					 WHERE log_id = (SELECT MAX(log_id) FROM agg_sale_log)
					};

    my $sth = $self->{_dbh}->prepare($sql);

    $sth->execute();

    my $log_data = $sth->fetchrow_hashref();

    $sth->finish();

    # if we don't get any log_data back, we want to rebuild the agg tables
    unless ( defined $log_data && exists $log_data->{total_records} ) {
        return 1;
    }

    my $has_changed = 0;
    foreach my $key ( keys %$log_data ) {
        if ( $sale_data->{$key} ne $log_data->{$key} ) {
            $has_changed++;
        }
    }

    return $has_changed > 0 ? 1 : undef;

}

sub log_sale_data {
    my $self           = shift;
    my $sale_data      = shift;
    my $tables_rebuilt = shift;

    my $sql = qq{
					 INSERT INTO agg_sale_log
					 (total_records, total_units, total_revenue,
					  max_sale_id, max_date_created, max_date_modified,
					  tables_rebuilt, date_started, date_finished)
					 VALUES (?, ?, ?, ?, ?, ?, ?, ?, now())
					};

    my $sth = $self->{_dbh}->prepare($sql);

    $sth->execute(
        $sale_data->{total_records},    $sale_data->{total_units},       $sale_data->{total_revenue}, $sale_data->{max_sale_id},
        $sale_data->{max_date_created}, $sale_data->{max_date_modified}, $tables_rebuilt,             $sale_data->{date_started},
    );

    return 1;

}

sub rebuild_aggregates {
    my $self = shift;

    my $agg_sale_stage_table   = AGG_SALE_STAGE_TABLE;
    my $agg_file_summary_table = AGG_FILE_SUMMARY_TABLE;
    my @period_type_tables     = PERIOD_TYPE_TABLES;

    # Truncate the staging table first, so that we can keep
    # the actual aggregates available as much as possible.

    $self->{_dbh}->do("TRUNCATE TABLE $agg_sale_stage_table");

    # Load staging table
    $self->debug( time, " load stage" );
    $self->load_stage_table();

    # Then we truncate and reload the summary table. Probably seems
    # weird, but it simply reports which services have posted what
    # *and* it's not surfaced today anyway. That said, it seems like
    # there's little need to coordinate service summary state with
    # aggregates anyway (meaning we could keep it here should we
    # re-surface the information)

    $self->{_dbh}->do("TRUNCATE TABLE $agg_file_summary_table");

    # Load file summary table
    $self->debug( time, " load file summary" );
    $self->load_file_summary_table();

    # Finally, truncate the aggregate tables. This is done so that if
    # only some of the tables are successfully built, the remaining
    # tables will be empty instead of having outdated data.
    # YOU SHOULD ONLY CONSIDER THE AGGREGATES SUCCESSFULLY BUILT IF
    # THE LAST TABLE AGG_Q_SUMMARY (AND THEREFORE, ALL TABLES) ARE LOADED

    foreach my $period (qw(m q y)) {
        foreach my $table (@period_type_tables) {
            my $table_name = Stats::Sales->getAggTable( period_type => $period, table_base => $table );
            $self->{_dbh}->do("TRUNCATE TABLE $table_name") if $table_name;
        }
    }

    # Load aggregate tables
    $self->debug( time, " load sales" );
    $self->load_sales_tables();

    $self->debug(" load prod sales");
    $self->load_product_sales_tables();

    $self->debug(" load artist sales");
    $self->load_artist_sales_tables();

    $self->debug( time, " load summary" );
    $self->load_summary_tables();

    # The staging table occupies signficant space for some customers and across them it really adds up
    $self->debug( time, " zap staging" );
    $self->{_dbh}->do("TRUNCATE TABLE $agg_sale_stage_table");

    $self->debug( time, "\ndone!!" );
}

################################################################
# LOAD AGG_SALE_STAGE                                          #
################################################################
# The AGG_SALE_STAGE table contains a subset of sale records from the
# sale table that are ready to be included in the sales reports.
#
# It is used as the basis for all other aggregates.
#
# Only "good" sale records from "eligible" files are copied to the AGG_SALE_STAGE table
#
# The list of "good" sale records for a given file must meet the following conditions:
#    (a) sale.product_id must NOT be NULL or 0
#    (b) sale.price must NOT be NULL or 0
#    (c) sale.conversion_rate must NOT be NULL or 0
#    (d) sale.format_type must NOT be 'M' (mechanical payments)
#
# The list of "total" sale records for a given file must meet the following condition:
#    (a) sale.format_type must NOT be 'M' (mechanical payments)
#
# A given file is considered "eligible" if it meets the following conditions:
#    (a) "total" sale records > 0
#    (b) "good" sale records / "total" sale records >= ($good_sale_min / 100)
# Note: we're not doing the eligible file check anymore, so our file filter has been
# updated to only look for physical files that are open or closed (see Case 13295).
sub load_stage_table {
    my $self                 = shift;
    my $agg_sale_stage_table = AGG_SALE_STAGE_TABLE;

    my $outerSelect = qq(
        SELECT file_id
        FROM file
        WHERE physical IN (0,2)
        AND file_status IN (4,5)
	);

    my $sth_outerSelect = $self->{_dbh}->prepare($outerSelect);

    my $sql_insert = qq{
INSERT INTO $agg_sale_stage_table
(
  sale_id, file_id, artist_id, product_id, product_type, format_type, service_id,
  label_id, units, price, conversion_rate, country_code, year, month_id, quarter_id
)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
};
    my $sth_insert = $self->{_dbh}->prepare($sql_insert);

    # jpk - This statement is looking for the product_catalog_extract table.
    # I'd like to make that table go away - Can I just query the new, integrated product table instead?
    # - label_id : This lives in the album table.  So I need to do some sort of join to get that to work.
    # - artist_id : In both track and album tables.
    # ----- not convinced trying to make a fancy join statement is actually worth it.
    # ----- trying to imbed funky conditional logic seems very very slow.
    #       Why not just make a couple of queries and build a data structure out here?
    #
    # sah - As it turns out, the MySQL query optimizer didn't always do very well with a non-fancy
    # join statement, so... Here we go tricky (but not too bad, I think)
    #
    # The key is to leave the join to album as a left join, so it doesn't get bumped up in the query
    # plan (where MySQL does a full scan on the album table, slow mo). We should probably check it
    # during iteration, just to be safe. You can't add album_id IS NOT NULL to there WHERE because
    # that will put you right back where you started.
    #
    # If this breaks down again, we're going to just have to defer the album table look up (and we
    # could proably defer product as well...)
    #
    # Ditto all of this for the track query as well.
    #
    my $sql_select_album =
        'SELECT STRAIGHT_JOIN'
      . ' sale.sale_id,'
      . ' sale.file_id,'
      . ' album.artist_id,'
      . ' sale.product_id,'
      . ' sale.product_type,'
      . ' sale.format_type,'
      . ' IFNULL(sale.service_id, file.service_id),'
      . ' album.label_id,'
      . ' sale.units,'
      . ' sale.price,'
      . ' sale.conversion_rate,'
      . ' sale.country_code,'
      . ' year(sale.date_end),'
      . ' sale.date_begin,'
      . ' sale.date_end' . ' FROM' . ' file'
      . ' JOIN sale USING (file_id)'
      . ' JOIN product USING (product_id)'
      . ' LEFT JOIN album ON (album.album_id = product.asset_id)'
      . ' WHERE'
      . ' file.file_id = ?'
      . " AND sale.product_type = 'A'"
      . ' AND IFNULL(sale.product_id, 0) != 0'
      . ' AND IFNULL(sale.price, 0) != 0'
      . ' AND IFNULL(sale.conversion_rate, 0) != 0'
      . " AND sale.format_type != 'M'"
      . ' AND product.product_type_id = 3';

    $sql_select_album .= " AND sale.free = 0" if $self->{_skipFree};
    my $sth_select_album = $self->{_dbh}->prepare($sql_select_album);

    my $sql_select_track =
        'SELECT STRAIGHT_JOIN'
      . ' sale.sale_id,'
      . ' sale.file_id,'
      . ' track.artist_id,'
      . ' sale.product_id,'
      . ' sale.product_type,'
      . ' sale.format_type,'
      . ' IFNULL(sale.service_id, file.service_id),'
      . ' album.label_id,'
      . ' sale.units,'
      . ' sale.price,'
      . ' sale.conversion_rate,'
      . ' sale.country_code,'
      . ' year(sale.date_end),'
      . ' sale.date_begin,'
      . ' sale.date_end' . ' FROM' . ' file'
      . ' JOIN sale USING (file_id)'
      . ' JOIN product USING (product_id)'
      . ' LEFT JOIN track ON (track.track_id = product.asset_id)'
      . ' JOIN album USING (album_id)'
      . ' WHERE'
      . ' file.file_id = ?'
      . " AND sale.product_type = 'T'"
      . ' AND IFNULL(sale.product_id, 0) != 0'
      . ' AND IFNULL(sale.price, 0) != 0'
      . ' AND IFNULL(sale.conversion_rate, 0) != 0'
      . " AND sale.format_type != 'M'"
      . ' AND product.product_type_id = 4';

    $sql_select_track .= " AND sale.free = 0" if $self->{_skipFree};
    my $sth_select_track = $self->{_dbh}->prepare($sql_select_track);

    my %date_cache = (
        month   => $self->_get_date_cache('month'),
        quarter => $self->_get_date_cache('quarter'),
    );

    $sth_outerSelect->execute();
    my %errLog = ();

    while ( my $fileList = $sth_outerSelect->fetchrow_arrayref() ) {
        $sth_select_album->execute( $fileList->[0] );    # fileList->[0] is the fileID

        while ( my $row = $sth_select_album->fetchrow_arrayref() ) {

            # !!! JPK - If we're going to normalize dates, this seems like the place to do that.
            # !!! We'd need to dereference year, start_date and end_date.
            #
            # !!! Need to take a close look at how _search_date_cache works.
            # !!! If we're going to 'normalize' the dates, the we can probably just
            # !!! use the month of the normalized date_end for both.
            #
            my ( $sID,    $fID )  = ( $row->[0],  $row->[1] );
            my ( $dBegin, $dEnd ) = ( $row->[-2], $row->[-1] );
            my $monthID   = _search_date_cache( $date_cache{month},   $dBegin, $dEnd );
            my $quarterID = _search_date_cache( $date_cache{quarter}, $dBegin, $dEnd );

            if ( !$quarterID && !$monthID ) {

                # try using just the end date
                if ( !$errLog{$fID}{$dEnd} ) {
                    $self->debug("couldn't map '$dBegin' '$dEnd' to a month or qtr, attempt end date only (sale: $sID, file: $fID A)");
                    $errLog{$fID}{$dEnd} = 1;
                }

                $monthID   = _search_date_cache( $date_cache{month},   $dEnd, $dEnd );
                $quarterID = _search_date_cache( $date_cache{quarter}, $dEnd, $dEnd );
                if ( !$quarterID || !$monthID ) {
                    die "invalid dates: '$dBegin' '$dEnd' (sale: $sID file: $fID A)";
                }
            }

            #$self->debug("insert with: " . join(',', @$row[0..9], $quarterID) );
            $sth_insert->execute( @$row[ 0 .. 12 ], $monthID, $quarterID );
        }

        $sth_select_track->execute( $fileList->[0] );

        while ( my $row = $sth_select_track->fetchrow_arrayref() ) {
            my ( $sID,    $fID )  = ( $row->[0],  $row->[1] );
            my ( $dBegin, $dEnd ) = ( $row->[-2], $row->[-1] );
            my $monthID   = _search_date_cache( $date_cache{month},   $dBegin, $dEnd );
            my $quarterID = _search_date_cache( $date_cache{quarter}, $dBegin, $dEnd );

            if ( !$quarterID && !$monthID ) {

                # try using just the end date
                if ( !$errLog{$fID}{$dEnd} ) {
                    $self->debug("couldn't map '$dBegin' '$dEnd' to a month or qtr, attempt end date only (sale: $sID, file: $fID T)");
                    $errLog{$fID}{$dEnd} = 1;
                }

                $monthID   = _search_date_cache( $date_cache{month},   $dEnd, $dEnd );
                $quarterID = _search_date_cache( $date_cache{quarter}, $dEnd, $dEnd );
                if ( !$quarterID || !$monthID ) {
                    die "invalid dates: '$dBegin' '$dEnd' (sale: $sID file: $fID T)";
                }
            }

            #$self->debug("insert with: " . join(',', @$row[0..9], $quarterID));
            $sth_insert->execute( @$row[ 0 .. 12 ], $monthID, $quarterID );
        }
    }

    # now update revenue
    $self->{_dbh}->do("update $agg_sale_stage_table set revenue = price * units * conversion_rate");

}

# !!! Seems rather weird to have this sitting in a database table.
#
sub _get_date_cache {
    my $self = shift;
    my $type = shift || return undef;
    return undef unless ( $type =~ /^(month|quarter)$/i );
    $type = lc($type);

    my %cache = ();

    ## info is in RSCOMMON, so we have to connect there first.
    my $sql = sprintf( "select %s_id, date_begin, date_end from period_%s", $type, $type );
    my $sth = $self->{_dboC}->DoCmd($sql);

    while ( my $row = $sth->fetchrow_arrayref() ) {
        push( @{ $cache{_order} }, $row->[0] );
        $cache{ $row->[0] } = {
            date_begin => $row->[1],
            date_end   => $row->[2],
        };

        $cache{ $row->[0] }{date_begin} =~ s/-//g;
        $cache{ $row->[0] }{date_end} =~ s/-//g;
    }
    $sth->finish();

    return \%cache;
}

sub _search_date_cache {
    my ( $cache, $date_begin, $date_end ) = @_;
    $date_begin =~ s/-//g;
    $date_end =~ s/-//g;

    my $key = $date_begin . $date_end;

    return $cache->{$key} if ( exists $cache->{$key} );

    my $ID = 0;
    foreach my $id ( @{ $cache->{_order} } ) {
        if (   $date_begin >= $cache->{$id}{date_begin}
            && $date_end <= $cache->{$id}{date_end} ) {
            $cache->{$key} = $ID = $id;
            last;
        }
    }

    return $ID;
}

sub load_file_summary_table {

    # okay, this is a little bit more involved than originally thought.
    # since a sales file can have entries that can span more than one month,
    # we need to handle that.
    #
    # the most important point is that the month_id from the sale stage table
    # is pretty much useless (this also means we can't do monthly reporting right,
    # but since we don't, we'll choose not worry about that right now).
    #
    # so we look back into the original sales file, check the date range and create
    # a monthly entry for each month in the mix. probably could have been done as
    # some kind of sick SQL statement, but no thanks.
    #
    # final note, this is technically a problem for quarters as well, but there
    # we must (at least for now) force sales into a single quarter. for that reason,
    # we don't do the same thing for quarters.

    my $self = shift;

    my $agg_sale_stage_table   = AGG_SALE_STAGE_TABLE;
    my $agg_file_summary_table = AGG_FILE_SUMMARY_TABLE;

    my $sql        = "INSERT INTO $agg_file_summary_table " . "(file_id, service_id, month_id, quarter_id, year) VALUES (?,?,?,?,?)";
    my $sth_insert = $self->{_dbh}->prepare($sql);

    # !!! jpk - We're going to be relying on date_end now exclusively, so this logic can change.
    # (i.e. change MIN(DATE_BEGIN) to MIN(DATE_END) )
    $sql = "SELECT MIN(DATE_BEGIN), MAX(DATE_END) FROM file, sale "
      . "WHERE sale.file_id=? AND sale.file_id=file.file_id AND (file.service_id=? OR sale.service_id=?)";
    my $sth_dates = $self->{_dbh}->prepare($sql);

    # !!! jpk - RSCOMMON is not on this host anymore - this needs to be changed.
    #
    $sql = "SELECT month_id FROM period_month month " . "WHERE ? BETWEEN month.date_begin AND month.date_end";
    my $sth_month = $self->{_dbhC}->prepare($sql);

    $sql =
      "SELECT DISTINCT a.file_id, f.service_id, month_id, quarter_id, year FROM $agg_sale_stage_table a " . "JOIN file f USING(file_id)";
    my $aggs = $self->{_dbh}->selectall_arrayref($sql);

    foreach my $agg (@$aggs) {
        $sth_dates->execute( $agg->[0], $agg->[1], $agg->[1] );    #yes, use service id twice
        my @dates      = $sth_dates->fetchrow_array();
        my $yearBegin  = substr( $dates[0], 0, 4 );
        my $monthBegin = substr( $dates[0], 5, 2 );
        my $yearEnd    = substr( $dates[1], 0, 4 );
        my $monthEnd   = substr( $dates[1], 5, 2 );

        my ( $years, $months );

        eval { ( $years, $months ) = Date::Calc::Delta_YMD( $yearBegin, $monthBegin, 1, $yearEnd, $monthEnd, 1 ); };

        if ($@) {
            print STDERR "Failed date span calc: YB: $yearBegin, MB: $monthBegin, YE: $yearEnd, ME: $monthEnd\n";
            die $@;
        }

        #if($months<0 && $years==1) {
        #    $months = ($years*12)+$months;
        #    $years=0;
        #}

        if ( $months < 0 ) {
            $months = 12 + $months;
            $years--;
        }

        if ( $years == 0 && $months == 0 )    # this should cover most records
        {
            $sth_insert->execute(@$agg);
            next;
        }

        for ( my $year = 0 ; $year <= $years ; $year++ ) {
            for ( my $month = 0 ; $month <= $months ; $month++ ) {
                my ( $yyyy, $mm, $dd ) = Date::Calc::Add_Delta_YM( $yearBegin, $monthBegin, 1, $year, $month );
                my $theDate = sprintf( "%04d-%02d-%02d", $yyyy, $mm, $dd );
                $sth_month->execute($theDate);
                my ($monthID) = $sth_month->fetchrow();
                if ($monthID) {
                    $sth_insert->execute( $agg->[0], $agg->[1], $monthID, $agg->[3], $agg->[4] );
                }
            }
        }
    }
}

################################################################
# 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
sub load_sales_tables {
    my $self                 = shift;
    my @agg_table_periods    = AGG_TABLE_PERIODS;
    my $agg_sale_stage_table = AGG_SALE_STAGE_TABLE;

    foreach my $period (@agg_table_periods) {
        my ( $table_name, $table_key ) = Stats::Sales->getAggTable( period_type => $period, table_base => 'sales' );
        $self->debug("  process $table_name - $table_key");
        my $where = $period eq 'm' ? "WHERE $table_key > 0" : '';

        my $sql_insert_sales = qq{
INSERT INTO $table_name
    ($table_key, product_type, service_id, label_id, format_type, country_code, units, revenue)
    SELECT $table_key, product_type, service_id, label_id, format_type, country_code,
        IFNULL(sum(units), 0),
        ifnull(sum(revenue), 0) as revenue
    FROM $agg_sale_stage_table
    $where
    GROUP BY $table_key, product_type, service_id, label_id, format_type, country_code
};

        $self->{_dbh}->do($sql_insert_sales);
    }

}

################################################################
# 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
sub load_product_sales_tables {
    my $self                 = shift;
    my @agg_table_periods    = AGG_TABLE_PERIODS;
    my $agg_sale_stage_table = AGG_SALE_STAGE_TABLE;

    foreach my $period (@agg_table_periods) {
        my ( $table_name, $table_key ) = Stats::Sales->getAggTable( period_type => $period, table_base => 'product_sales' );
        $self->debug("  process $table_name - $table_key");
        my $where = $period eq 'm' ? "WHERE $table_key > 0" : '';

        my $sql_insert_product_sales = qq{
INSERT INTO $table_name
    ($table_key, product_id, product_type, label_id, format_type, service_id, country_code, units, revenue)
    SELECT $table_key, product_id, product_type, label_id, format_type, service_id, country_code,
        IFNULL(SUM(units), 0),
        IFNULL(SUM(revenue), 0) as revenue
    FROM $agg_sale_stage_table
    $where
    GROUP BY $table_key, product_id, product_type, label_id, format_type, service_id, country_code
};
        $self->{_dbh}->do($sql_insert_product_sales);
    }
}

################################################################
# 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
sub load_artist_sales_tables {
    my $self                 = shift;
    my @agg_table_periods    = AGG_TABLE_PERIODS;
    my $agg_sale_stage_table = AGG_SALE_STAGE_TABLE;

    foreach my $period (@agg_table_periods) {
        my ( $table_name, $table_key ) = Stats::Sales->getAggTable( period_type => $period, table_base => 'artist_sales' );
        $self->debug("  process $table_name - $table_key");
        my $where = $period eq 'm' ? "WHERE $table_key > 0" : '';

        my $sql_insert_artist_sales = qq{
INSERT INTO $table_name
    ($table_key, artist_id, product_type, label_id, format_type, service_id, country_code, units, revenue)
    SELECT $table_key, artist_id, product_type, label_id, format_type, service_id, country_code,
        IFNULL(SUM(units), 0),
        IFNULL(SUM(revenue), 0) as revenue
    FROM $agg_sale_stage_table
    $where
    GROUP BY $table_key, artist_id, product_type, label_id, format_type, service_id, country_code
};
        $self->{_dbh}->do($sql_insert_artist_sales);
    }
}

################################################################
# 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.
sub load_summary_tables {
    my $self                 = shift;
    my @agg_table_periods    = AGG_TABLE_PERIODS;
    my $agg_sale_stage_table = AGG_SALE_STAGE_TABLE;

    foreach my $period (@agg_table_periods) {
        my ( $table_name, $table_key ) = Stats::Sales->getAggTable( period_type => $period, table_base => 'summary' );
        $self->debug("process $table_name - $table_key");
        my $where = $period eq 'm' ? "WHERE $table_key > 0" : '';
        my $sql_insert_summary = qq{
									 INSERT INTO $table_name ($table_key)
									 SELECT DISTINCT $table_key
									 FROM $agg_sale_stage_table
                                     $where
									 ORDER BY $table_key
									};

        $self->{_dbh}->do($sql_insert_summary);
    }
}

sub debug {
    my $self = shift;
    print STDERR "@_\n" if ( $self->{_debug} );
}

1;