package BookPub::Stats::Sales;

use strict;

#use warnings;
use Data::Dumper;
use lib '/app/tools/bookpub/lib';
use BookPub::Stats::Metric;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::Log;

use lib '/app/tools/appuser/lib';
use AppUser::User::User;

use DBI;

use constant AGG_PERIOD_MONTH   => 'M';
use constant AGG_PERIOD_QUARTER => 'Q';
use constant AGG_PERIOD_YEAR    => 'Y';
use constant AGG_RANK_LIMIT     => 200;

my $AGG_SALE_LIMIT = 200;
my $AGG_SALES_TMP  = 'agg_q_sales_period_stage';
my %AGG_TABLE_MAP  = (
    M => 'month_id',
    Q => 'quarter_id',
    Y => 'year',
);
my %AGG_SALES_MAP = (
    country   => 'country_code',
    product   => 'product_id',
    author    => 'author_id',
    imprint   => 'imprint_id',
    publisher => 'publisher_id',
    service   => 'service_id',
    format    => 'format_type',
);

my @properties =
  qw(period_type period_id end_period_id imprint_id publisher_id service_id format_type country_code product_id product_type author_id offset ranking user_id);

sub new {
    my $proto = shift;
    my $class = ref($proto) || $proto;
    my %in    = @_;

    my $self = {
        client_id => undef,
        rsdb      => undef,
        dbh       => undef,
        debug     => $in{debug}
    };

    if ( exists $in{client_id} && $in{client_id} =~ /^\d+$/ ) {
        $self->{client_id} = $in{client_id};
    }

    foreach my $field (@properties) {
        $self->{$field} = $in{$field};
    }
    $self->{offset} ||= 0;

    bless( $self, $class );

    return $self;
}

# ------------------------------------
# Public Functions
# ------------------------------------

sub GetSummaryMetric {
    my $self    = shift;
    my %in_args = @_;

    return unless $in_args{category};

    my %my_args = ();
    foreach my $field (qw(author_id product_id period_type period_id end_period_id service_id format_type imprint_id publisher_id country_code user_id)) {
        next if ( $field eq $AGG_SALES_MAP{ $in_args{category} } );
        $my_args{$field} = $in_args{$field} || $self->{$field};
    }

    $self->_check_input(
        input    => \%my_args,
        required => [ 'period_id', 'author_id' ],
        optional => [ 'end_period_id', 'service_id', 'format_type', 'imprint_id', 'publisher_id', 'user_id' ],
    ) || return $self->bail("b input check failed");

    my $table_base =
        $my_args{product_id} ? 'product_sales'
      : $my_args{author_id}  ? 'author_sales'
      :                        'sales';
    my $agg_table = $self->getAggTable(
        table_base  => $table_base,
        period_type => $my_args{period_type},
    ) || return $self->bail("couldn't load agg table");

    my $where = $self->_getBaseWhere( \%my_args )
      || return $self->bail("couldn't build WHERE");

    my $metric_list;
    if ( $in_args{category} eq 'format' ) {
        $metric_list = "'" . join( "','", @{ $in_args{metric_list} } ) . "'";
    } else {
        $metric_list = join( ',', @{ $in_args{metric_list} } );
    }

    $where .= " AND agg.$AGG_SALES_MAP{$in_args{category}} IN($metric_list)";

    my $sql = qq|
SELECT agg.$AGG_SALES_MAP{$in_args{category}},
ROUND(SUM(IFNULL(agg.revenue, 0)), 2) as revenue
FROM $agg_table agg
WHERE $where
GROUP BY 1
|;

    #print STDERR "sql: $sql\n";

    return $self->_getHashedResults($sql);
}

sub GetTotalMetric {
    my $self    = shift;
    my %in_args = @_;

    my %my_args = ();
    foreach my $field (
        qw(period_type period_id end_period_id service_id format_type imprint_id publisher_id product_type product_id author_id country_code user_id)) {
        $my_args{$field} = $in_args{$field} || $self->{$field};
    }

    $self->_check_input(
        input    => \%my_args,
        required => [ 'period_id', 'period_type' ],
        optional => [ 'end_period_id', 'service_id', 'format_type', 'imprint_id', 'publisher_id', 'product_id', 'author_id', 'user_id' ],
    ) || return $self->bail("check input failed");

    my $table_base =
        $my_args{product_id} ? 'product_sales'
      : $my_args{author_id}  ? 'author_sales'
      :                        'sales';
    my $agg_table =
      $self->getAggTable( table_base => $table_base, period_type => $my_args{period_type} ) || return $self->bail("couldn't get agg table");

    my $where = $self->_getBaseWhere( \%my_args ) || return $self->bail("couldn't build WHERE");
    $where .= " AND country_code = '$my_args{country_code}'" if $my_args{country_code};

    my $sql = qq{    SELECT
                    SUM(IFNULL(agg.units, 0)) as units,
                    ROUND(SUM(IFNULL(agg.revenue, 0)), 2) as revenue
                    FROM $agg_table agg
                    WHERE $where
                };

    #print STDERR "sql: $sql\n";
    return $self->_getMetric($sql);
}

sub GetTopRanking {
    my $self    = shift;
    my %in_args = @_;

    my %my_args = ();
    foreach my $field (
        qw(period_type period_id end_period_id product_type product_id author_id service_id format_type imprint_id publisher_id country_code offset user_id)
      ) {
        next if ( $field eq $AGG_SALES_MAP{ $in_args{category} }
            or $field eq $AGG_SALES_MAP{ $in_args{ignore} } );
        $my_args{$field} = $in_args{$field} || $self->{$field};
    }

    my $table_base =
        ( $my_args{product_id} || $in_args{category} eq 'product' ) ? 'product_sales'
      : ( $my_args{author_id}  || $in_args{category} eq 'author' )  ? 'author_sales'
      :                                                               'sales';

    my $agg_table = $self->getAggTable(
        table_base  => $table_base,
        period_type => $my_args{period_type},
    ) || return $self->bail("couldn't get agg table");

    $my_args{period_id}-- if $in_args{prev_rank};
    my $where = $self->_getBaseWhere( \%my_args ) || return $self->bail("couldn't build WHERE");

    #$where .= "  AND agg.revenue != 0";

    my $limit = $in_args{limit} > 10 ? " LIMIT $my_args{offset}, $AGG_SALE_LIMIT" : '';

    my $select = $AGG_SALES_MAP{ $in_args{category} } || return $self->bail('invalid category');

    my $rank_init = $in_args{prev_rank} ? 0 : $my_args{offset};
    $self->dbh->do( 'set @sales_rank:=' . $rank_init );

    my $sql = qq|
SELECT id, \@sales_rank:=\@sales_rank+1 as sales_rank, units, revenue FROM (SELECT agg.$select as id, SUM(IFNULL(units,0)) as units,
    ROUND(SUM(IFNULL(agg.revenue, 0)), 2) as revenue
FROM $agg_table agg
WHERE $where
GROUP BY 1
ORDER BY revenue DESC
$limit) result
|;

    #print STDERR "sql: $sql\n";

    return ( $in_args{prev_rank} || $in_args{rank_only} )
      ? $self->_getHashedResults( $sql, 1 )
      : $self->_getMetricArray( $sql, $in_args{limit} || $AGG_SALE_LIMIT );
}

sub GetDetailRanking {
    my $self    = shift;
    my %in_args = @_;

    my %my_args = ();
    foreach my $field (
        qw(period_type period_id end_period_id product_type product_id author_id service_id format_type imprint_id publisher_id country_code user_id)) {
        next if ( $field eq $AGG_SALES_MAP{ $in_args{category} }
            or $field eq $AGG_SALES_MAP{ $in_args{ignore} } );
        $my_args{$field} = $self->{$field};
    }

    my ( $select, $table_base, $id );
    if ( $my_args{product_id} ) {
        $select     = 'product_id';
        $table_base = 'product_sales';
        $id         = $my_args{product_id};
        delete $my_args{product_id};
    } elsif ( $my_args{author_id} ) {
        $select     = 'author_id';
        $table_base = 'author_sales';
        $id         = $my_args{author_id};
        delete $my_args{author_id};
    }
    return undef unless $id;

    my $agg_table = $self->getAggTable(
        table_base  => $table_base,
        period_type => $my_args{period_type},
    ) || return $self->bail("couldn't get agg table");

    $my_args{period_id}-- if $in_args{prev_rank};
    my $where = $self->_getBaseWhere( \%my_args ) || return $self->bail("couldn't build WHERE");

    my $group = $AGG_SALES_MAP{ $in_args{category} } || return $self->bail('invalid category');
    $where .= " and agg.$group in(" . join( ',', @{ $in_args{list} } ) . ")" if $in_args{list};

    my $sql = qq|
SELECT agg.$select, agg.$group, SUM(agg.revenue)
FROM $agg_table agg
WHERE $where
GROUP BY 1,2
ORDER BY 2, 3 DESC
|;

    #print STDERR "sql: $sql\n";
    return $self->_getRankInGroup( $sql, $id );
}

sub FormatMap {
    my $self = shift;

    if ( !$self->{format_map} ) {

        # info is in RSCOMMON, so we have to connect there.
        my $dbo_c = Common::RSDB->new( client_id => 0 );
        my $sth = $dbo_c->DoCmd("SELECT code_value, description FROM onix_code WHERE code_list IN (7,150)");

        my %format_map;
        while ( my ( $format_type, $format_name ) = $sth->fetchrow_array() ) {
            $format_map{$format_type} = $format_name;
        }
        $self->{format_map} = \%format_map;
    }

    return $self->{format_map};
}

sub GetCountryList {
    my $self    = shift;
    my %in_args = @_;

    my %my_args = ();
    foreach my $field (qw(period_type period_id end_period_id service_id product_id product_type imprint_id publisher_id format_type author_id user_id)) {
        $my_args{$field} = $in_args{$field} || $self->{$field};
    }

    $self->_check_input(
        input => \%my_args,

        #required  => [],
        optional => [ 'service_id', 'imprint_id', 'publisher_id', 'product_type', 'product_id', 'author_id', 'user_id' ],
    ) || return $self->bail("check input failed");

    my $table_base =
        $my_args{author_id}  ? 'author_sales'
      : $my_args{product_id} ? 'product_sales'
      :                        'sales';

    my $agg_table =
      $self->getAggTable( table_base => $table_base, period_type => $my_args{period_type} ) || return $self->bail("couldn't get agg table");

    my $where = $self->_getBaseWhere( \%my_args ) || return $self->bail("couldn't build WHERE");

    my $sql = "SELECT DISTINCT(upper(country_code)) FROM $agg_table agg";
    $sql .= " WHERE $where" if ( $where && !$in_args{all} );

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

    my @country_list = ();
    while ( my $row = $sth->fetchrow_arrayref() ) {
        push @country_list, $row->[0];
    }

    return \@country_list;
}

sub GetActiveImprints {
    my $self        = shift;
    my %args        = @_;
    my $period_id   = $args{period_id};
    my $end_period_id   = $args{end_period_id};
    my $period_type = $args{period_type};

    my ( $table, $field ) = $self->getAggTable(
        table_base  => 'sales',
        period_type => $args{period_type},
    ) || return $self->bail("couldn't load agg table");

    my $sql = "SELECT DISTINCT(imprint_id) from $table WHERE units > 0";

    if ($end_period_id) {
        $sql .= "AND $field >= $period_id AND $field <= $end_period_id";
    } else {
        $sql .= "AND $field = $period_id";
    }

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

    if ( $sth->rows > 0 ) {
        return $sth->fetchall_arrayref();
    } else {
        return undef;
    }
}

# use Client::Label->GetAllLabelNames();
sub LabelMap {
    my $self = shift;

    if ( !$self->{imprint_map} ) {
        my $sth = $self->dbh->prepare("SELECT imprint_id, name FROM imprint");
        $sth->execute();

        my %imprint_map;
        while ( my ( $imprint_id, $name ) = $sth->fetchrow_array() ) {
            $imprint_map{$imprint_id} = $name;
        }
        $self->{imprint_map} = \%imprint_map;
    }

    return $self->{imprint_map};
}

sub GetServiceFileStatus {
    # Is this used anymore???
    my $self        = shift;
    my %args        = @_;
    my $period_id   = $args{period_id};
    my $end_period_id = $args{end_period_id};
    my $period_type = $args{period_type};
    my $service_id  = $args{service_id};
    my $field       = $AGG_TABLE_MAP{ uc($period_type) };

    my ( $sql, $sth );
    my $months = [];
    my $month_limit =
        lc($period_type) eq 'm' ? 1
      : lc($period_type) eq 'q' ? 3
      :                           12;

    if ( lc($period_type) eq 'm' ) {
        $months = [ [$period_id] ];
    } else {
        $sql = "SELECT distinct(month_id) FROM agg_file_summary WHERE $field=$period_id ORDER BY month_id";
        $sth = $self->dbh->prepare($sql);
        $sth->execute() if $sth;

        $months = $sth->fetchall_arrayref();
    }

    my @statuses    = ();
    my $month_count = 0;
    foreach my $month (@$months) {
        last if ( $month_count++ == $month_limit );
        $sql = "SELECT count(*) FROM agg_file_summary WHERE service_id=$service_id AND month_id=$month->[0]";
        $sth = $self->dbh->prepare($sql);
        $sth->execute() if $sth;
        my $count = $sth->fetchrow_arrayref();
        push @statuses, $count->[0] > 0 ? 1 : 0;
    }

    return \@statuses;
}

sub GetPeriodNavParams {
    my $self = shift;

    my $sql = '
select min(year) as min_y, min(month_id) as min_m, min(quarter_id) as min_q,
  max(year) as max_y, max(month_id) as max_m, max(quarter_id) as max_q
from agg_file_summary';

    my $sth = $self->dbh->prepare($sql) || return $self->bail("db prepare failed");
    $sth->execute();
    my $min_max = $sth->fetchrow_hashref();

    # determine starting month
    # info is in RSCOMMON, so we have to connect there first.
    my $dbo_c = Common::RSDB->new( client_id => 0 );
    $sql = sprintf( "select month from period_month where month_id=%s", $dbo_c->DBQuote( $min_max->{max_m} ) );
    $sth = $dbo_c->DoCmd($sql);

    my ($month) = $sth->fetchrow_array();

    return {
        min_y       => $min_max->{min_y},
        max_y       => $min_max->{max_y},
        min_q       => $min_max->{min_q},
        max_q       => $min_max->{max_q},
        min_m       => $min_max->{min_m},
        max_m       => $min_max->{max_m},
        start_month => $month,
    };
}

# ------------------------------------
# Private Functions
# ------------------------------------
sub _check_input {
    my $self = shift;
    my %in   = @_;

    my $input           = $in{input};
    my $required_params = $in{required};
    my $optional_params = $in{optional};
    my $invalid_params  = $in{invalid};

    my %regexp = (
        period_id => qr/^\d+$/,
        end_period_id => qr/^\d+$/,
        # period_type  => qr/^(M|Q)$/,
        period_type  => qr/^(@{[AGG_PERIOD_MONTH]}|@{[AGG_PERIOD_QUARTER]}|@{[AGG_PERIOD_YEAR]})$/,
        product_id   => qr/^\d+$/,
        user_id      => qr/^\d+$/,
        service_id   => qr/^\d+$/,
        imprint_id   => qr/^\d+$/,
        publisher_id => qr/^\d+$/,
        format_type  => qr/^[a-z0-9]+$/i,
        product_type => qr/^[a-z0-9]+$/i,
        top          => qr/^\d+$/,
    );

    foreach my $param (@$required_params) {
        if ( !exists $input->{$param} ) {
            $self->{errstr} = "The $param parameter is required";
            return undef;
        }
        if ( $input->{$param} !~ m/$regexp{$param}/ ) {
            $self->{errstr} = "The value for the $param parameter is not valid";
            return undef;
        }
    }

    foreach my $param (@$optional_params) {
        if ( defined $input->{$param} && $input->{$param} !~ m/$regexp{$param}/ ) {
            $self->{errstr} = "The value for the $param parameter is not valid";
            return undef;
        }
    }

    foreach my $param (@$invalid_params) {
        if ( defined $input->{$param} ) {
            $self->{errstr} = "The $param parameter is not allowed";
            return undef;
        }
    }

    return 1;

}

# can be called as an instance or class method
sub getAggTable {
    my $self = shift;
    my %in   = @_;

    unless ( exists $in{period_type} && $in{period_type} =~ /^(m|q|y)$/i ) {
        $self->{errstr} = "period_type must be 'Q' for quarter, 'M' for month, 'Y' for year" if ref($self);
        return undef;
    }

    unless ( exists $in{table_base} && $in{table_base} =~ /^\w+$/ ) {
        $self->{errstr} = "table_base must be an alphanumeric baseword for the table name" if ref($self);
        return undef;
    }

    my $agg_table = sprintf( 'agg_%s_%s', lc $in{period_type}, lc $in{table_base} );

    return wantarray ? ( $agg_table, $AGG_TABLE_MAP{ uc $in{period_type} } ) : $agg_table;
}

sub _getBaseWhere {
    my $self = shift;
    my $in   = shift;

    if ( !defined $in->{period_id} || $in->{period_id} < 1 ) {
        $self->{errstr} = "period_id must be a integer greater than zero";
        return undef;
    }

    if ( defined $in->{end_period_id} && $in->{end_period_id} < 1 ) {
        $self->{errstr} = "end_period_id must be a integer greater than zero";
        return undef;
    }

    my @fields;

    # We're adding support for period ranges, but only for months.
    if ($in->{end_period_id} && uc $in->{period_type} eq 'M') {
        push @fields, sprintf( 'agg.%s >= %d', $AGG_TABLE_MAP{ uc $in->{period_type} }, $in->{period_id} );
        push @fields, sprintf( 'agg.%s <= %d', $AGG_TABLE_MAP{ uc $in->{period_type} }, $in->{end_period_id} );
    } else {
        push @fields, sprintf( 'agg.%s = %d', $AGG_TABLE_MAP{ uc $in->{period_type} }, $in->{period_id} );
    }

    if ( defined $in->{product_id} && $in->{product_id} > 0 ) {
        push @fields, "agg.product_id = $in->{product_id}";
    }

    if ( defined $in->{author_id} && $in->{author_id} > 0 ) {
        push @fields, "agg.author_id = $in->{author_id}";
    }

    if ( defined $in->{format_type} && $in->{format_type} ne '' ) {
        push @fields, "agg.format_type = '$in->{format_type}'";
    }

    if ( defined $in->{service_id} && $in->{service_id} > 0 ) {
        push @fields, "agg.service_id = $in->{service_id}";
    }

    if ( defined $in->{imprint_id} && $in->{imprint_id} > 0 ) {
        push @fields, "agg.imprint_id = $in->{imprint_id}";
    }

    if ( defined $in->{publisher_id} && $in->{publisher_id} >= 0 ) {
        push @fields, "agg.publisher_id = $in->{publisher_id}";
    }

    # Need to restrict All Publishers for publisher view users
    # and All Imprints for imprint view users
    #
    if ( defined $in->{user_id} && ( !defined $in->{publisher_id} || !defined $in->{imprint_id} ) ) {
        my $userID = $in->{user_id};
        my $user = AppUser::User::User->new( userID => $userID, loadSubs => 0 );

        if ( !defined $in->{publisher_id} ) {
            my $allowedPublishers;
            my $listOfPublishers = $user->getPublisherIDs();
            if ( $listOfPublishers && scalar @$listOfPublishers > 0 ) {
                $allowedPublishers = join( ',', @$listOfPublishers );
                push @fields, "agg.publisher_id IN ($allowedPublishers)";
            }
        }

        if ( !defined $in->{imprint_id} ) {
            my $allowedImprints;
            my $listOfImprints = $user->getImprintIDs();
            if ( $listOfImprints && scalar @$listOfImprints > 0 ) {
                $allowedImprints = join( ',', @$listOfImprints );
                push @fields, "agg.imprint_id IN ($allowedImprints)";
            }
        }
    }

    if ( defined $in->{product_type} && $in->{product_type} ne '' ) {
        push @fields, "agg.product_type = '$in->{product_type}'";
    }

    if ( defined $in->{country_code} && $in->{country_code} ne '' ) {
        push @fields, "agg.country_code = '$in->{country_code}'";
    }

    my $where = join( ' AND ', @fields );

    return $where;
}

sub _getMetric {
    my $self = shift;
    my $sql  = shift;

    my $sth = $self->dbh->prepare($sql) || return $self->bail("_getMetric prepare failed");
    $sth->execute();

    my $href = $sth->fetchrow_hashref() if ($sth);
    if ($href) {
        return BookPub::Stats::Metric->new(%$href);
    } else {
        return BookPub::Stats::Metric->new();
    }
}

sub _getMetricArray {
    my $self  = shift;
    my $sql   = shift;
    my $limit = shift || 0;

    my $sth = $self->dbh->prepare($sql) || return $self->bail("_getMetricArray prepare failed");
    $sth->execute();
    return undef unless $sth->rows;

    my $c    = 1;
    my @data = ();

    while ( my $row = $sth->fetchrow_hashref() ) {
        push @data, BookPub::Stats::Metric->new(%$row);
        last if ( $c++ == $limit );
    }

    return wantarray ? ( \@data, $sth->rows ) : \@data;
}

sub _getArrayRef {
    my $self  = shift;
    my $sql   = shift;
    my $limit = shift || 0;

    my $sth = $self->dbh->prepare($sql) || return $self->bail("_getArrayRef prepare failed");
    $sth->execute();
    return undef unless $sth->rows;

    my $c    = 1;
    my @data = ();

    while ( my $row = $sth->fetchrow_arrayref() ) {
        push @data, $row->[0];
        last if ( $c++ == $limit );
    }

    return wantarray ? ( \@data, $sth->rows ) : \@data;
}

sub _getHashedResults {
    my $self = shift;
    my $sql  = shift;
    my $rank = shift;

    my $sth = $self->dbh->prepare($sql) || return $self->bail("_getHashedResults prepare failed");
    $sth->execute();
    return undef unless $sth->rows;

    my %results = ();

    while ( my $row = $sth->fetchrow_arrayref() ) {
        last if ( $rank && $row->[1] >= AGG_RANK_LIMIT );
        $results{ $row->[0] } = $row->[1];
    }

    return \%results;
}

sub _getRankInGroup {
    my ($self, $sql, $id) = @_;

    my $sth = $self->dbh->prepare($sql) || return $self->bail("_getRankInGroup prepare failed");
    $sth->execute();
    return undef unless $sth->rows;

    my %data = ();
    while ( my $row = $sth->fetchrow_arrayref() ) {
        # Skip lines where first entity is not equal to $id
        next unless $row->[0] == $id;
        $data{ $row->[1] } += $row->[2];
    }

    # Now sort by the third entity (money) descending
    my @sortedIDsDesc = sort { $data{$b} <=> $data{$a} } keys %data;

    my $rank = 0;
    my %groupRank = ();
    foreach  my $_id ( @sortedIDsDesc ) {
        $groupRank{ $_id } = ++$rank;
        last if $rank >= AGG_RANK_LIMIT;
    }

    return \%groupRank;
}

sub rsdb {
    my $self = shift;
    my %in   = @_;

    if ( !defined $self->{rsdb} ) {
        if ( !defined $self->{client_id} ) {
            $self->{errstr} = "client_id not defined";
            return undef;
        }

        my $rsdb = new Common::RSDB( client_id => $self->{client_id}, dbi_attr => { RaiseError => 1 } );

        if ( defined $rsdb ) {
            $self->{rsdb} = $rsdb;
        } else {
            die "rsdb not defined!\n";
        }

    }

    return $self->{rsdb};
}

sub dbh {
    my $self = shift;
    my %in   = @_;

    return $self->rsdb(%in)->DBH();
}

sub bail {
    my $self = shift;
    my $msg  = shift;

    print STDERR "BookPub::Stats::Sales bailing: $msg: $self->{errstr}\n";

    return undef;
}

sub errstr {
    my $self = shift;
    return $self->{errstr};
}

DESTROY {
    my $self = shift;

    if ( defined $self->{dbh} ) {
        $self->dbh->disconnect();
    }

}

1;