package Stats::Redshift::LoadDimension;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use Common::Session;

use lib '/app/tools/stats/lib';
use Stats::Redshift::Dimension;
use base 'Stats::Redshift';

__PACKAGE__->mk_accessors(qw/dimensions/);

sub _init {
    my $self = shift;
    $self->SUPER::_init();
    my $dim = Stats::Redshift::Dimension->new( $self->type );
    $self->dimensions( $dim->dimensions );
    $self;
}

sub refresh_file_summary {
    my $self = shift;

    $self->truncate('agg_file_summary');

    ## populate agg_file_summary for all clients
    my $schema = $self->redshift_schema;
    my $sql    = (
        qq{
INSERT INTO ${schema}.agg_file_summary
SELECT sub.client_id, sub.date_loaded, sub.file_id, sub.service_id, mm.month_id, sub.myy, sub.mqq FROM (
    SELECT s.file_id, s.service_id, min(month_id) min_m,
           max(end_month_id) max_m, min(year) myy, min(quarter_id) mqq,
           s.date_loaded,s.client_id
    FROM ${schema}.sale_stage s
    GROUP BY s.file_id,s.service_id,s.client_id,s.date_loaded
) sub JOIN (
    SELECT row_number() OVER () AS month_id
    FROM pg_catalog.pg_namespace p1
    CROSS JOIN pg_catalog.pg_tables p2
) mm ON mm.month_id >= sub.min_m AND mm.month_id <= sub.max_m}
    );
    $self->dosql($sql);
}

sub refresh_sale_log {
    my $self = shift;

    $self->truncate('agg_sale_log');

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

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

    ## populate agg_file_summary for all clients
    my $schema = $self->redshift_schema;
    my $sql    = (
        qq{
INSERT INTO ${schema}.agg_sale_log
SELECT
	client_id, TRUNC(date_loaded), RANK() OVER (ORDER BY total_records DESC),
	COUNT(*) AS total_records, NVL(SUM(units),0),$rev,
	MAX(s.sale_id),MAX(s.date_created),MAX(s.date_modified),NULL,NULL,NULL
FROM
	${schema}.sale s
GROUP BY 1,2 
ORDER BY 3
}
    );
    $self->dosql($sql);
}

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

    my $schema   = $self->redshift_schema;
    my $type     = $self->type;
    my $table    = $dim->tablename;
    my $key      = $dim->key;
    my $_groupby = $dim->groupby;

    $self->truncate($table);

    my $from = "FROM ${schema}.sale_stage s";

    my $where;
    push @{$where}, "$key>0" if $table =~ m/.*_m_.*$/;

    my $groupby = [qw/s.client_id s.date_loaded/];
    if ( defined $_groupby ) {
        push @{$groupby}, $key;
        map { push @{$groupby}, $_ } @{$_groupby};
    }

    if ( $table =~ m/author_sales/ ) {
        my $dtmv = 'royshare.dtmv';
        $from = "FROM $dtmv.sale_stage s, $dtmv.book_product bp, $dtmv.book_contributor bc";
        push @{$where},
          (
            's.product_id=bp.product_id', 'bp.book_id=bc.book_id', 'bc.contributor_role_id=1', 's.client_id=bp.client_id',
            'bp.client_id=s.client_id',   'bc.client_id=s.client_id'
          );
    }

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

    my $cols;
    if ( $table =~ m/summary/ ) {
        push @{$cols},    $key;
        push @{$groupby}, $key;
    } else {
        $cols = $dim->columns;
        push @{$cols}, 'NVL(SUM(s.units),0)';
        push @{$cols}, 'NVL(SUM(s.revenue),0)';
    }

    $groupby = join ',', @{$groupby};

    my $sql = "INSERT INTO ${schema}.$table\n";
    $sql .= "SELECT\n\ts.client_id,\n\ts.date_loaded,\n";
    $sql .= join ",\n", map { "\t$_" } @{$cols};
    $sql .= "\n$from\n";
    $sql .= "\t$clause\n";
    $sql .= "GROUP BY $groupby\n";
    $sql .= "ORDER BY $key\n";

    $self->dosql($sql);

}

sub refresh_aggregates {
    my $self = shift;

    my $schema = $self->redshift_schema;

    ## refresh file summary table
    logMessage( 'info', "Refreshing ${schema}.agg_file_summary" );
    $self->refresh_file_summary();

    ## refresh agg sale log
    logMessage( 'info', "Refreshing ${schema}.agg_sale_log" );
    $self->refresh_sale_log();

    ## refresh dimension tables
    my $dims = $self->dimensions;
    foreach my $dim ( @{$dims} ) {
        next if $dim->tablename eq 'agg_file_summary';
        logMessage( 'info', "Refreshing $schema." . $dim->tablename );
        $self->refresh_dimension($dim);
    }

}

1;