package Common::Stats::DTMVBuilder;

use strict;
use warnings;

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

use base 'Common::Stats::Builder';

sub type { 'dtmv' }

sub dimensions {
    my $self = shift;
    my $dims = [ (qw/sales product_sales author_sales summary/) ];
    $dims;
}

sub insert_select_book {
    my $self = shift;
    my $sth  = $self->dbh->prepare(
        qq{
INSERT INTO agg_sale_stage
SELECT
    sale.sale_id,
    sale.file_id,
    IF(
        TIMESTAMPDIFF(MONTH,sale.date_begin,sale.date_end),0,
        ABS(TIMESTAMPDIFF(MONTH,sale.date_begin,'1916-08-01'))
    ) month_id,
    ABS(TIMESTAMPDIFF(QUARTER,sale.date_begin,'1916-07-01'))+667 quarter_id,
    YEAR(sale.date_end) `year`,
    sale.product_id,
    'bk' product_type,
    onix_code.code_value format_type,
    IFNULL(sale.service_id, file.service_id) service_id,
    book_product.imprint_id,
    book_product.publisher_id,
    sale.units,
    sale.revenue,
    sale.conversion_rate,
    sale.country_code,
    sale.revenue * sale.conversion_rate revenue
FROM
    sale
JOIN
    `file` USING (file_id)
JOIN
    book_product USING (product_id)
JOIN
    book USING (book_id)
JOIN
    book_format USING (book_format_id)
JOIN
    book_format_type USING (book_format_type_id)
JOIN
    onix_code USING (onix_code_id)
WHERE 1
    AND DAY(sale.date_begin) != 0
    AND IFNULL(sale.product_id, 0) != 0
    AND IFNULL(sale.revenue, 0) != 0
    AND IFNULL(sale.conversion_rate, 0) != 0
    AND sale.file_id = ?
        }
    );

    $sth;

}

sub insert_select_chapter {
    my $self = shift;
    my $sth  = $self->dbh->prepare(
        qq{
INSERT INTO agg_sale_stage
SELECT
    sale.sale_id,
    sale.file_id,
    IF(
        TIMESTAMPDIFF(MONTH,sale.date_begin,sale.date_end),0,
        ABS(TIMESTAMPDIFF(MONTH,sale.date_begin,'1916-08-01'))
    ) month_id,
    ABS(TIMESTAMPDIFF(QUARTER,sale.date_begin,'1916-07-01'))+667 quarter_id,
    YEAR(sale.date_end) `year`,
    sale.product_id,
    'ch' product_type,
    book_format_id format_type,
    IFNULL(sale.service_id, file.service_id) service_id,
    chapter_product.imprint_id,
    chapter_product.publisher_id,
    sale.units,
    sale.revenue,
    sale.conversion_rate,
    sale.country_code,
    sale.revenue * sale.conversion_rate revenue
FROM
    sale
JOIN
    file ON sale.file_id = file.file_id
JOIN
    chapter_product ON sale.product_id = chapter_product.product_id
JOIN
    chapter ON chapter.chapter_id = chapter_product.chapter_id
JOIN
    book ON chapter.book_id = book.book_id
WHERE 1
    AND DAY(sale.date_begin) != 0
    AND IFNULL(sale.product_id, 0) != 0
    AND IFNULL(sale.revenue, 0) != 0
    AND IFNULL(sale.conversion_rate, 0) != 0
    AND sale.file_id = ?
    }
    );

    $sth;

}

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

    my $dbh = $self->dbh;

    # Truncate the staging table first, so that we can keep
    # the actual aggregates available as much as possible.
    $self->truncate('agg_sale_stage');


    ## file candidate join table for outer select
    $self->truncate('agg_file_candidate');

    ## insert open/closed files into file candidate
    $dbh->do(
        qq{
INSERT INTO agg_file_candidate (file_id)
SELECT
    file_id
FROM
    file
WHERE 1
    AND file_status IN ( 4,5 )
    }
    );

    my $sth_insert         = $dbh->prepare("INSERT INTO agg_sale_stage VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    my $sth_select_book    = $self->insert_select_book;
    my $sth_select_chapter = $self->insert_select_chapter;

    my $sth_files = $dbh->prepare(
        qq{
SELECT
    f.file_id,
    COUNT(s.sale_id) AS total_records,
    SUM(IF(
        IFNULL(s.product_id,0) != 0 AND IFNULL(s.conversion_rate,0) != 0, 1, 0
    )) AS good_records
FROM
    sale s
JOIN
    agg_file_candidate f USING (file_id)
GROUP BY f.file_id
    }
    );

    ## only execute the chapter query if the BP DB has chapter products
    ## otherwise we'll be doing a lot of wasted DB connection/queries
    ## to get NULL results - JDG
    my $row = $dbh->selectcol_arrayref("SELECT COUNT(*) FROM chapter_product");
    my $do_chapter_query = $row->[0] ? 1 : 0;

    my $rate = $gsm / 100;

    ## get all file candidates
    logMessage( 'info', "fetching file-sale summary" );
    $sth_files->execute;

    while ( my $row = $sth_files->fetchrow_arrayref() ) {
        my ( $file_id, $total_recs, $good_recs ) = @{$row};
        next if $total_recs < 1;
        next if ( $good_recs / $total_recs ) < $rate;

        logMessage( 'info', "Query sales for file: $file_id" );
        $sth_select_book->execute($file_id);

        if ($do_chapter_query) {
            $sth_select_chapter->execute($file_id);

            while ( my $row = $sth_select_chapter->fetchrow_arrayref() ) {
                $sth_insert->execute( @{$row} );
            }
        }
    }
}

1;

__END__

## comments from the old build_sale_aggregates.pl


################################################################
# 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
#
# 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)

################################################################
# 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
#
# 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)

## Sales dimensions
################################################################
# 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, imprint_id, publisher_id & format_type

## Product Sales dimensions
################################################################
# 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, imprint_id, publisher_id & format_type

## Author Sales dimensions
################################################################
# LOAD AGG_[M,Q,Y]_AUTHOR_SALES
################################################################
# The AGG_X_AUTHOR_SALES table contains summed revenue and summed units
# rolled up by quarter_id, author_id, product_type, service_id, imprint_id, publisher_id & format_type


## Summaries
################################################################
# 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.