#---------------------------------------------------------------
# ____                   _ _         ____  _                    
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___ 
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/                            
#
# Copyright (C) 2012 RoyaltyShare, Inc.   All Rights Reserved
#---------------------------------------------------------------


# This package will just contain some static methods to create and read the sales file.
# And it will provide constants to use as array indexes.
# But that's about it...

# For the Sale mapping, I just need labelID, albumID, and trackID.
# But for the final output file, I'll need a lot more stuff.
# I could use two different files, but I think to start I will one. Which means
# I need to load this up with a ton of data.


package RPS::LabelRoyalty::Fast::Static::Products;

use strict;
use Data::Dumper;
use File::Path;

use lib '/app/tools/common/lib';
use lib '/app/tools/rps/lib';
use lib '/app/tools/raptor/lib';


use DB_File;
use DBM_Filter;

use Common::Log;
use Common::Util;

use Raptor::DB::Item::Sale;
use RPS::DB::Item::Product;
use RPS::File::Sale;
use RPS::DB::Item::DistributionFee;


use base 'RPS::LabelRoyalty::Fast::Static';


use constant kProductID     => 0;
use constant kLabelID       => 1;
use constant kAlbumID       => 2;
use constant kTrackID       => 3;
use constant kProductTypeID => 4;
use constant kDiscNo        => 5;
use constant kTrackNo       => 6;
use constant kUPC           => 7;
use constant kUPCAlt        => 8;
use constant kReleaseDate   => 9;
use constant kCatalogID     => 10;
use constant kAlbumName     => 11;
use constant kAlbumArtist   => 12;
use constant kAlbumCustom1  => 13;
use constant kAlbumCustom2  => 14;
use constant kAlbumCustom3  => 15;
use constant kISRC          => 16;
use constant kTrackName     => 17;
use constant kTrackArtist   => 18;
use constant kTrackCustom1  => 19;
use constant kTrackCustom2  => 20;
use constant kTrackCustom3  => 21;
use constant kMediaType     => 22;
use constant kLabelName     => 23; # Am I starting to lard this up with too much stuff?


sub FileName { 'products' }
sub CacheQueryTableList { "'product','album','track','product_track','artist','master'"};


sub _Create
{
    my ($class, $filename) = @_;

#    open FILE, "> $filename" or die "ERROR: Unable to open $filename for writing: $!";

    my %hash;
    my $db = tie %hash, "DB_File", $filename, O_RDWR | O_CREAT, 0666, $DB_HASH or die "Error opening $filename: $!\n";
    $db->Filter_Push('utf8');
    
    my $dbo = Common::RSApp::GetClientDB();


    # This will probably require a union of two queries:
    # One to fetch the album products, and one to fetch the track products.

	my $albumSql = "SELECT "
     . " product.product_id,"
     . " album.label_id,"
     . " album.album_id,"
     . " 0,"
     . " product.product_type_id,"
     . " 0," # disc_no
     . " 0," # track_no
     . " product.upc_ean,"
     . " product.upc_alt,"
     . " product.release_date,"
     . " album.catalog_number,"
     . " product.title,"
     . " album_artist.name,"
     . " album.custom_1,"
     . " album.custom_2,"
     . " album.custom_3,"
     . " '',"   # isrc
     . " '',"   # track name
     . " '',"   # track artist
     . " '',"   # track custom 1
     . " '',"   # track custom 2
     . " '',"   # track custom 3
     . " '',"   # media type
     . " label.label_name"
     . " FROM product"
     . " JOIN album ON (album.album_id = product.asset_id)"
     . " JOIN artist AS album_artist ON (album.artist_id = album_artist.artist_id)"
     . " JOIN label ON (album.label_id = label.label_id)"
     . " WHERE product.product_type_id = 3";

	my $trackSql = "SELECT "
     . " product.product_id,"
     . " album.label_id,"
     . " album.album_id,"
     . " track.track_id,"
     . " product.product_type_id,"
     . " 0," # disc_no
     . " 0," # track_no
     . " album_product.upc_ean,"
     . " album_product.upc_alt,"
     . " album_product.release_date,"
     . " album.catalog_number,"
     . " album_product.title,"
     . " album_artist.name,"
     . " album.custom_1,"
     . " album.custom_2,"
     . " album.custom_3,"
     . " master.isrc,"
     . " track.title,"
     . " track_artist.name,"
     . " track.custom_1,"
     . " track.custom_2,"
     . " track.custom_3,"
     . " track.media_type,"
     . " label.label_name"
     . " FROM product"
     . " JOIN track ON (track.track_id = product.asset_id)"
     . " JOIN album ON (album.album_id = track.album_id)"
     . " JOIN artist AS album_artist ON (album.artist_id = album_artist.artist_id)"
     . " JOIN artist AS track_artist ON (track.artist_id = track_artist.artist_id)"
     . " JOIN master ON (track.master_id = master.master_id)"
     . " JOIN label ON (label.label_id = album.label_id)"
     . " JOIN product AS album_product ON (album_product.product_id = product.parent_product_id AND album_product.product_type_id = 3)"
     . " WHERE product.product_type_id = 4";

    my $productTrackSql = "SELECT "
     . " product_track.track_id,"
     . " product_track.disc_number,"
     . " product_track.disc_track"
     . " FROM product_track"
     . " JOIN product ON (product.product_id = product_track.product_id AND product.product_type_id=3)";


# I think what I may do here is make an initial query to get all the track_id/disc_no/track_no data for digital album products.
# Then I can do the unioned query to get the rest of the stuff, and fill in the disc_no/track_no as I write it out.

    my %productTrackData;
    my $sth = $dbo->DoCmd($productTrackSql);
    while (my @row = $sth->fetchrow_array())
    {
        $productTrackData{ $row[0] } = \@row;
    }


    my $unionSql = "$albumSql UNION $trackSql";
    $sth = $dbo->DoCmd($unionSql);
    while (my @row = $sth->fetchrow_array())
    {
        if (4 == $row[ kProductTypeID ])
        {
            my $productTrackRow = $productTrackData{ $row[kTrackID] };
            if ($productTrackRow)
            {
                $row[ kDiscNo ] = $productTrackRow->[1];
                $row[ kTrackNo ] = $productTrackRow->[2];
            }
        }

        my $key = $row[ kProductID ];
        $hash{$key} = join("\t", @row);
    }

    untie %hash;
}


sub GetProducts
{
    my ($class, $dataPath) = @_;


    # Currently the sales file is a text file.
    # We'll use DB_RECNO, which allows us to access the file through
    # a tied array ref.
    #
    my $filename = "$dataPath/".$class->FileName();


    my %hash;
    my $dbObj = tie %hash, "DB_File", $filename, O_RDONLY, 0666, $DB_HASH or die "Error opening $filename: $!\n";
    $dbObj->Filter_Push('utf8');
    return \%hash;
}


1;