package Common::Stats::Builder;

use strict;
use warnings;

use constant 'MAXBULKLOAD' => 10000;

use base 'Class::Accessor';

use POSIX (qw/strftime/);
use DateTime::Format::MySQL;

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

__PACKAGE__->mk_accessors(qw/dbo dbh file_summary file_candidate schema skipfree sale_stage total_records/);

sub new {
    my $class = shift;
    my $self = { 'dbo' => shift };
    bless $self, $class;
    $self->_init;
    $self;
}

sub _init {
    my $self = shift;
    $self->sale_stage('agg_sale_stage');
    $self->file_summary('agg_file_summary');
    $self->file_candidate('agg_file_candidate');
    $self->dbh( $self->dbo->DBH );
    $self->schema( Common::RSDB::ClientIDToDBName( $self->dbo->{'CLIENT_ID'} ) );
}

sub max_bulk_load { MAXBULKLOAD }

sub get_sale_data {
    my $self = shift;

    my $rev;
    if ( $self->type eq 'rps' ) {
        $rev = 'IFNULL(ROUND(SUM(IFNULL(units,0)*IFNULL(price,0)*IFNULL(conversion_rate,0)),8),0)';

    } else {
        $rev = 'IFNULL(SUM(IFNULL(revenue,0)*IFNULL(conversion_rate,0)),0)';
    }

    my $sql = (
        qq{
SELECT
    now() AS date_started,
    COUNT(*) AS total_records,
    IFNULL(SUM(units),0) AS total_units,
    ROUND($rev,2) 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
}
    );
    if ( $self->type eq 'rps' ) {
        $sql .= "\nWHERE product_type IN ('A','T')\n";
        $sql .= "AND free = 0\n" if $self->skipfree;
    }

    my $sth = $self->dbo->DoCmd($sql);
    $sth->fetchrow_hashref();

}

sub execute_bulk {
    my $self = shift;
    my $sth  = shift;
    my $data = shift;

    logMessage( 'info', "Inserting " . scalar @{$data} . " rows" );

    my $dbh = $self->dbh;
    $dbh->{'AutoCommit'} = 0;
    foreach my $row ( @{$data} ) {
        $sth->execute( @{$row} );
    }
    $dbh->commit;
    $dbh->{'AutoCommit'} = 1;
}

sub sale_data_has_changed {
    my $self = shift;
    my $data = shift;
    my $sth  = $self->dbo->DoCmd(
        qq{
SELECT
    total_records,
    total_units,
    ROUND(total_revenue,2) total_revenue,
    max_sale_id,
    max_date_created,
    max_date_modified
FROM
    agg_sale_log
ORDER BY
    log_id DESC
LIMIT 1
    }
    );

    my $log_data = $sth->fetchrow_hashref;

    ## if we don't get any log_data back, we want to rebuild the agg tables
    return 1 if not defined $log_data;

    my $has_changed = 0;
    foreach my $key ( qw/total_records total_units total_revenue max_date_modified/ ) {
        if ( $data->{$key} ne $log_data->{$key} ) {
            $has_changed++;
        }
    }
    $has_changed;
}

sub load_file_summary_table {
    my $self = shift;

    # 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 $dbh = $self->dbh;

    # 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->truncate('agg_file_summary');

    my $sth_insert = $dbh->prepare("INSERT IGNORE INTO agg_file_summary VALUES (?,?,?,?,?)");
    my $sth        = $dbh->prepare(
        qq{
SELECT
    s.file_id,
    s.service_id,
    ABS(TIMESTAMPDIFF(MONTH,MIN(s.date_begin),'1916-08-01')),
    YEAR(MIN(s.date_begin)),
    ABS(TIMESTAMPDIFF(QUARTER,MIN(s.date_begin),'1916-07-01'))+667,
    ABS(TIMESTAMPDIFF(MONTH,MIN(s.date_begin),MAX(s.date_end))),
    MIN(s.date_begin)
FROM
    sale s
JOIN (
    SELECT DISTINCT
        file_id,
        service_id
    FROM
        agg_sale_stage
) xx ON xx.file_id=s.file_id AND xx.service_id=s.service_id
GROUP BY
    s.file_id, s.service_id
    }
    );

    $sth->execute;

    my $data;
    while ( my $row = $sth->fetchrow_arrayref ) {
        my ( $fid, $sid, $mid, $yid, $qid, $mons, $date ) = @{$row};
        if ( $fid == 31 ) {
            my $x;
        }
        my $rref = [ @{$row}[ 0 ... 4 ] ];
        push @{$data}, $rref;
        if ($mons) {
            my $dt = DateTime::Format::MySQL->parse_date($date);
            for ( my $i = 1 ; $i <= $mons ; $i++ ) {
                $dt->add( 'months' => 1 );
                my ( $_mid, $_qid ) = getMonthQuarterByDate( $dt->ymd('-') );
                my $_yid = $dt->year;
                push @{$data}, [ $fid, $sid, $_mid, $_yid, $_qid ];
            }
        }
        if ( scalar @{$data} >= $self->max_bulk_load ) {
            $self->execute_bulk( $sth_insert, $data );
            undef $data;
        }
    }
    $self->execute_bulk( $sth_insert, $data ) if defined $data and scalar @{$data};

}

sub log_sale_data {
    my $self    = shift;
    my $data    = shift;
    my $rebuilt = shift;

    $self->dbh->do(
        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())
    }, undef,
        (
            $data->{total_records},    $data->{total_units},       $data->{total_revenue}, $data->{max_sale_id},
            $data->{max_date_created}, $data->{max_date_modified}, $rebuilt,               $data->{date_started}
        )
    );

}

sub table_cols {
    my $self = shift;
    my $tab  = shift;

    my $cols = $self->dbh->selectcol_arrayref(
        qq{
SELECT
    c.column_name
FROM
    information_schema.columns c
WHERE 1
    AND c.table_schema=?
    AND c.table_name=?
    AND c.column_name NOT IN ('units','revenue')
ORDER BY
    c.ordinal_position
    }, undef, ( $self->schema, $tab )
    );
    $cols;
}

sub get_dim_table {
    my $self   = shift;
    my $dim    = shift;
    my $period = shift;

    my $key = {
        'm' => 'month_id',
        'q' => 'quarter_id',
        'y' => 'year',
    }->{$period};

    my $dim_table = sprintf( "agg_%s_%s", $period, $dim );

    my $type    = $self->type;
    my $groupby = {
        'dtmv' => {
            'sales'         => 'product_type, service_id, imprint_id, publisher_id, format_type, country_code',
            'product_sales' => 'product_id, product_type, imprint_id, publisher_id, format_type, service_id, country_code',
            'author_sales'  => 'contributor_id, product_type, imprint_id, publisher_id, format_type, service_id, country_code',
            'summary'       => undef,
        },
        'rps' => {
            'sales'         => 'product_type, service_id, label_id, format_type, country_code',
            'product_sales' => 'product_id, product_type, label_id, format_type, service_id, country_code',
            'artist_sales'  => 'artist_id, product_type, label_id, format_type, service_id, country_code',
            'summary'       => undef,
        }
    }->{$type}->{$dim};

    ( $dim_table, $key, $groupby );

}

sub load_dimension {
    my $self = shift;
    my $dim  = shift;

    my $dbh = $self->dbh;

    foreach my $period (qw/m q y/) {
        my ( $table, $key, $groupby ) = $self->get_dim_table( $dim, $period );

        # truncate the dimesion
        $self->truncate($table);

        my $from = 'FROM agg_sale_stage s';

        my $where;
        push @{$where}, "$key > 0" if $period eq 'm';

        $groupby = "$key, $groupby" if defined $groupby;

        if ( $dim eq 'author_sales' ) {
            $from = 'FROM agg_sale_stage s, book_product bp, book_contributor bc';
            push @{$where}, ( 's.product_id=bp.product_id', 'bp.book_id=bc.book_id', 'bc.contributor_role_id=1' );
        }

        my $clause = "WHERE 1\n";
        if ( defined $where ) {
            $clause .= join "\n", map { "\tAND $_" } @{$where};
        }

        my $cols;
        if ( $dim eq 'summary' ) {
            push @{$cols}, "DISTINCT s. " . $key;
        } else {
            $cols = $self->table_cols($table);
            $cols = [ map { $_ eq 'author_id' ? "bc.contributor_id" : "s." . $_ } @{$cols} ];
            push @{$cols}, 'IFNULL(SUM(s.units),0)';
            if ( $self->type eq 'rps' ) {
                push @{$cols}, 'IFNULL(SUM(s.revenue),0)';
            } else {
                push @{$cols}, 'IFNULL(SUM(IFNULL(s.revenue,0)*IFNULL(s.conversion_rate,0)),0)';
            }
        }

        my $sql = "INSERT INTO $table\nSELECT\n";
        $sql .= join ",\n", map { "\t$_" } @{$cols};
        $sql .= "\n$from\n";
        $sql .= "$clause\n";
        $sql .= "GROUP BY $groupby\n" if $dim ne 'summary';
        $sql .= "ORDER BY $key\n" if $dim eq 'summary';

        logMessage( 'info', "building $table : $key" );
        $dbh->do($sql);
    }
}

sub truncate {
    my $self  = shift;
    my $table = shift;
    logMessage( 'warn', "Truncating table $table" );
    $self->dbh->do("TRUNCATE TABLE $table");
}

sub rebuild_aggregates {
    my $self = shift;
    my $gsm  = shift;

    my $dbh = $self->dbh;

    # Load stage table
    logMessage( 'info', "load sale stage" );
    $self->load_stage_table($gsm);

    # Load file summary table
    logMessage( 'info', "load file summary" );
    $self->load_file_summary_table($dbh);

    ## load dimension tables
    my $dims = $self->dimensions;
    foreach my $dim ( @{$dims} ) {
        logMessage( 'info', "loading $dim" );
        $self->load_dimension($dim);
    }

    # The staging table occupies signficant space for some customers and across them it really adds up
    # $self->truncate('agg_sale_stage');

}

sub finish {
    my $self   = shift;
    my $time   = time - $^T;
    my $hhmmss = strftime( "\%H:\%M:\%S", gmtime($time) );
    logMessage('info',"Complete in $time seconds [ $hhmmss ]");
}

1;