#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# 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;