package Stats::Sales;

use strict;

#use warnings;

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

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

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',
    artist  => 'artist_id',
    label   => 'label_id',
    service => 'service_id',
    format  => 'format_type',
);

my @properties = qw(period_type period_id label_id service_id format_type country_code product_id product_type artist_id offset ranking);

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(artist_id product_id period_type period_id service_id format_type label_id country_code)) {
        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', 'artist_id' ],
        optional => [ 'service_id', 'format_type', 'label_id' ],
    ) || return $self->bail("b input check failed");

    my $table_base =
        $my_args{product_id} ? 'product_sales'
      : $my_args{artist_id}  ? 'artist_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 service_id format_type label_id product_type product_id artist_id country_code)) {
        $my_args{$field} = $in_args{$field} || $self->{$field};
    }

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

    my $table_base =
        $my_args{product_id} ? 'product_sales'
      : $my_args{artist_id}  ? 'artist_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, %in_args) = @_;

    my @fields = qw/
        period_type
        period_id
        product_type
        product_id
        artist_id
        service_id
        format_type
        label_id
        country_code
        offset
    /;

    my %my_args = ();
    foreach my $field ( @fields ) {
        next if $field eq $AGG_SALES_MAP{ $in_args{category} };
        next if $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{artist_id}  || $in_args{category} eq 'artist' )  ? 'artist_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 $select = $AGG_SALES_MAP{ $in_args{category} } || return $self->bail('invalid category');
    my $where  = $self->_getBaseWhere( \%my_args )    || return $self->bail("couldn't build WHERE");
    my $limit  = $in_args{limit} > 10 ? " LIMIT $my_args{offset}, $AGG_SALE_LIMIT" : '';

    # RSD-8600, do not calculate rank for all records in a table, it is slow
    $limit = " LIMIT " . AGG_RANK_LIMIT if $in_args{limit} && $in_args{limit} == 10;

    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 "HHH => 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 product_type product_id artist_id service_id format_type label_id country_code)) {
        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{artist_id} ) {
        $select     = 'artist_id';
        $table_base = 'artist_sales';
        $id         = $my_args{artist_id};
        delete $my_args{artist_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 = new Common::RSDB( client_id => 0 );
        my $sth = $dbo_c->DoCmd("select format_type, format_name from format");

        #my $sth = $self->dbh->prepare("select format_type, format_name from format");
        #$sth->execute();

        my %format_map;
        while ( my ( $format_type, $format_name ) = $sth->fetchrow_array() ) {
            # We need to update the format name here for format type X - Orchard Sync. RSD-6740
            $format_name = "Synchronization Revenue" if $format_type eq 'X';
            $format_map{$format_type} = $format_name;
        }
        $self->{format_map} = \%format_map;

        $sth->finish();
    }

    return $self->{format_map};
}

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

    my %my_args = ();
    foreach my $field (qw(period_type period_id service_id product_id product_type label_id format_type artist_id)) {
        $my_args{$field} = $in_args{$field} || $self->{$field};
    }

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

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

    my $table_base =
        $my_args{artist_id}  ? 'artist_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 GetActiveLabels {
    my $self        = shift;
    my %args        = @_;
    my $period_id   = $args{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(label_id) from $table WHERE $field = $period_id and units > 0";

    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;

    #
    # do not use!
    #
    return undef;

    if ( !$self->{label_map} ) {
        my $sth = $self->dbh->prepare("select label_id, label_name from label");
        $sth->execute();

        my %label_map;
        while ( my ( $label_id, $label_name ) = $sth->fetchrow_array() ) {
            $label_map{$label_id} = $label_name;
        }
        $self->{label_map} = \%label_map;

        $sth->finish();
    }

    return $self->{label_map};
}

sub GetServiceFileStatus {
    my $self        = shift;
    my %args        = @_;
    my $period_id   = $args{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 GetAllServicesFileStatus {
    my ($self, %args) = @_;

    my $period_id   = $args{period_id};
    my $period_type = $args{period_type};
    my $field       = $AGG_TABLE_MAP{ uc($period_type) };

    my $sql = qq/
        SELECT
            t.service_name,
            t.service_id,
            GROUP_CONCAT(t.file_cnt) AS file_cnt
        FROM (
            SELECT
                s.service_name,
                s.service_id,
                mqy.month_id,
                IF(count(c.file_id) > 0, 1, 0) AS file_cnt
            FROM service AS s
            LEFT JOIN (
                SELECT DISTINCT(month_id)
                FROM agg_file_summary
                WHERE $field = $period_id
                ORDER BY month_id
            ) AS mqy ON mqy.month_id -- alternative to CROSS JOIN but faster
            LEFT JOIN agg_file_summary AS c ON s.service_id = c.service_id AND mqy.month_id=c.month_id
            WHERE s.service_id != 0
            GROUP BY s.service_id, mqy.month_id
            ORDER BY s.service_name, mqy.month_id
        ) AS t
        GROUP BY t.service_id
        ORDER BY t.service_id;
    /;
    my $sth = $self->dbh->prepare($sql);
    $sth->execute();

    my $aRes = [];    # [ {Service => 'iTunes', Data => [0,1,0]} ];
    while ( my $hRow = $sth->fetchrow_hashref() ) {
        push @$aRes, {
            Service => $hRow->{service_name},
            Data    => [ split(',', $hRow->{file_cnt}) ],
        };
    }

    return $aRes;
}


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 = new Common::RSDB( 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+$/,

        # period_type     => qr/^(M|Q)$/,
        period_type  => qr/^(@{[AGG_PERIOD_MONTH]}|@{[AGG_PERIOD_QUARTER]}|@{[AGG_PERIOD_YEAR]})$/,
        product_id   => qr/^\d+$/,
        service_id   => qr/^\d+$/,
        label_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;
    }

    my @fields;
    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->{artist_id} && $in->{artist_id} > 0 ) {
        push @fields, "agg.artist_id = $in->{artist_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->{label_id} && $in->{label_id} > 0 ) {
        push @fields, "agg.label_id = $in->{label_id}";
    }

    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 new Stats::Metric(%$href);
    } else {
        return new Stats::Metric();
    }
}

sub _getMetricArray {
    my ($self, $sql, $limit) = @_;

    $limit ||= 0;

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

    $sth->execute();
    return undef unless $sth->rows;

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

    while ( my $row = $sth->fetchrow_hashref() ) {
        push @data, 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, $sql, $rank) = @_;

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

    $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 "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;