#------------------------------------------------------------
# Copyright (C) 2010 RoyaltyShare, Inc.   All Rights Reserved
#------------------------------------------------------------
package BookPub::DB::Item::SaleReport;
use strict;
use warnings;

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

#use Common::DB::Item;
use base 'Common::DB::Item';

use constant kTable => 'NONE';
use constant kDB    => Common::DB::Item::kClientDB;

# This instance method will return a sql statement that will fetch all of the
# columns joined from wherever necessary, sorted and aggregated.
# In other words, here's where the magic happens.
#
my %gColumnDefs = (

    #    isbn10 => {
    #        select => 'book_product.isbn10 as isbn10',
    #        orderBy => 1,
    #        groupBy => 1,
    #    },
    #    isbn13 => {
    #        select => 'book_product.isbn13 as isbn13',
    #        orderBy => 1,
    #        groupBy => 1,
    #    },
    #    imprint => {
    #        select => 'imprint.name as imprint',
    #        from => 'imprint',
    #        where => 'imprint.imprint_id=book.imprint_id',
    #        groupBy => 1,
    #    },
    # Note that we're using a space as the SEPARATOR here - The file might be comma-delimited.
    #    author => {
    #        select => "GROUP_CONCAT(DISTINCT contributor.last_name ORDER BY last_name SEPARATOR ' ') as author",
    #        where => ['book_contributor.contributor_id=contributor.contributor_id','book_contributor.book_id=book.book_id'],
    #        from => ['book_contributor', 'contributor'], # !!! May not really be worth it to have a 'from' field.
    #        groupBy => 1,
    #    },
    #    chapter_title => {
    #        select => 'chapter.title as chapter_title',
    #        where => ['chapter_product.product_id=product.product_id'],
    #        from => ['chapter', 'chapter_product'],
    #        orderBy => 1,
    #    },
    #    service => {
    #        select => 'service.service_name as service',
    #        where => 'file.service_id=service.service_id',
    #        from => 'service',
    #        orderBy => 1,
##        groupBy => 1,
    #    },
    #    service_id => {
    #        select => 'service.service_id as service_id',
    #        where => 'file.service_id=service.service_id',
    #        from => 'service',
    #        orderBy => 1,
    #        groupBy => 1,
    #    },
    currency => {
        select  => 'sale.currency_code as currency',
        groupBy => 1,
    },

    #    title => {
    #        select => 'book.title as title',
    #        groupBy => 1,
    #        orderBy => 1,
    #    },
    #    subtitle => {
    #        select => 'book.subtitle as subtitle',
    #        groupBy => 1,
    #        orderBy => 1,
    #    },
    units => {
        select => 'SUM(sale.units) as units',
    },
    net_revenue => {
        select => 'SUM(sale.revenue) as net_revenue',
    },

    # !!! What is 'territory', anyway?
    territory => {
        select  => 'sale.country_code as territory',
        orderBy => 1,
        groupBy => 1,
    },
    free_flag => {
        select => 'sale.is_free as free_flag',
    },
    period_begin => {
        select => 'sale.date_begin as period_begin',
    },
    period_end => {
        select => 'sale.date_end as period_end',
    },

    # ... and which 'format' are we talking about here?
    #    format => {
    #        select => 'book_format.description as format',
    #        groupBy => 1,
    #        from => 'book_format',
    #        where => 'book_format.book_format_id=book_product.book_format_id',
    #    },
);

# !!! A bit of a hack.
sub Lookup {
    my ( $class, %params ) = @_;

    my $self = {};
    bless $self, $class;
    $self->{_ignore_config} = 1;
    return $self->_init(%params);
}

sub ReportQuery {
    my ( $class, $columns, $periodID ) = @_;
    assert( defined $columns );
    assert( defined $periodID );
    assert( 'ARRAY' eq ref($columns) );

    my @select;
    my @from;
    my @where;
    my @groupBy;
    my @orderBy;

    # ALWAYS going to want product_id in there.  We just do.  Trust me.
    #
    push @select,  'product.product_id as product_id';
    push @select,  'service.service_id as rs_service_id';
    push @groupBy, 'product_id';

    # Going to 'seed' the WHERE clause with the main linkages.
    #
    push @where, 'product.product_id=sale.product_id';

    #    push @where, 'book_product.product_id=product.product_id';
    #    push @where, 'book.book_id=book_product.book_id';
    push @where, 'file.file_id=sale.file_id';
    push @where, 'file.period_id=' . $periodID;
    push @where, 'file.service_id=service.service_id';

    push @from, 'sale';
    push @from, 'product';

    #    push @from, 'book_product';
    #    push @from, 'book';
    push @from, 'file';
    push @from, 'service';

    foreach my $column (@$columns) {
        my $columnData = $gColumnDefs{$column};

        # We are NOT going to be able to do everything in a single query.
        # So not all config parameters can be honored here.
        #
        next unless $columnData;

        push @select, $columnData->{select};

        if ( $columnData->{orderBy} ) {
            push @orderBy, $column;
        }

        if ( $columnData->{groupBy} ) {
            push @groupBy, $column;
        }

        if ( $columnData->{where} ) {
            my $arrayRef = $columnData->{where};
            if ( 'ARRAY' ne ref($arrayRef) ) {
                $arrayRef = [ $columnData->{where} ];
            }
            foreach my $whereItem (@$arrayRef) {
                push @where, $whereItem;
            }
        }

        if ( $columnData->{from} ) {
            my $arrayRef = $columnData->{from};
            if ( 'ARRAY' ne ref($arrayRef) ) {
                $arrayRef = [ $columnData->{from} ];
            }
            foreach my $fromItem (@$arrayRef) {
                push @from, $fromItem;
            }
        }
    }

    # Now we can assemble the statement.
    #
    my $sql = 'SELECT ' . join( ',', @select );
    $sql .= ' FROM ' . join( ',', @from );
    $sql .= ' WHERE ' . join( ' AND ', @where );
    if ( scalar @groupBy ) {
        $sql .= ' GROUP BY ' . join( ',', @groupBy );
    }
    if ( scalar @orderBy ) {
        $sql .= ' ORDER BY ' . join( ',', @orderBy );
    }

    Common::Log::Print("SaleReport SQL: $sql");

    # Now, finally, whip up a DB::ItemCollection encapsulating the query.
    #
    my $collection = Common::DB::ItemCollection->new(
        class => $class,
        query => $sql,
        dbID  => kDB(),
    );

    return $collection;
}

1;