#!/usr/bin/perl
#------------------------------------------------------------
# Copyright (C) 2006 RoyaltyShare, Inc.   All Rights Reserved
# $Id$
#------------------------------------------------------------

use strict;

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

use Common::Util qw(trimspaces clean normalize_upc normalize_isrc);
use Common::RSDB;
use Getopt::Std;

my %opt;
getopts( 'c:f:', \%opt );

my $clientID = $opt{c};

unless ( $clientID =~ /^\d+$/ && $clientID > 0 ) {
    die usage("You must specify a valid client ID");
}

sub usage {
    my $errstr = shift;
    my $text = ($errstr) ? "ERROR: $errstr\n" : '';
    $text .= "Usage: $0 -c <clientID>\n";
    return $text;
}

my $dbObj = new Common::RSDB( client_id => $clientID );

my $dbh = $dbObj->{DBH};    # TODO: should NOT peek in the internals of objects

my @header = (
    "album-name",       "album-artist",           "label",                  "location-id",
    "catalog-id",       "upc",                    "album-id",               "company-id",
    "vendor-id",        "track-name",             "disc-no",                "disc-name",
    "track-no",         "track-minutes",          "track-seconds",          "track-artist",
    "isrc",             "track-id",               "genre",                  "track-product-id",
    "album-product-id", "physical-cd-product-id", "physical-lp-product-id", "physical-cas-product-id",
    "rs-track-id",      "rs-album-id"
);

my $catalog = getCatalog($dbh);

foreach my $field (@header) {
    print("$field\t");
}
print("\n");

my %seenAlbumIDs = ();
foreach my $clientTrackID ( sort keys %{ $catalog->{tracks} } ) {
    my $track      = $catalog->{tracks}->{$clientTrackID};
    my $disc       = $catalog->{discIDs}->{ $track->{discID} };
    my $discNumber = $disc->{discNumber};
    my $album      = $catalog->{albums}->{ $disc->{clientAlbumID} };
    my $minutes    = int( $track->{duration} / 60 );
    my $seconds    = $track->{duration} % 60;

    print "$album->{albumName}\t";
    print "$catalog->{artists}->{$catalog->{artistIDs}->{$album->{artistID}}}->{artistName}\t";
    print "$catalog->{labels}->{$catalog->{labelIDs}->{$album->{labelID}}}->{labelName}\t";
    print "$album->{locationID}\t";
    print "$album->{catalogID}\t";
    print "$album->{upc}\t";
    print "$disc->{clientAlbumID}\t";
    print "$album->{companyID}\t";
    print "$album->{vendorID}\t";
    print "$track->{trackName}\t";
    print "$discNumber\t";
    print "$album->{discs}->{$discNumber}->{discName}\t";
    print "$track->{trackNumber}\t";
    print "$minutes\t";
    print "$seconds\t";
    print "$catalog->{artists}->{$catalog->{artistIDs}->{$track->{artistID}}}->{artistName}\t";
    print "$track->{isrc}\t";
    print "$clientTrackID\t";
    print "$track->{genreName}\t";
    print "$track->{productID}\t";
    print "$album->{productID}\t";
    print "$album->{physicalCDProductID}\t";          # -jff- new physical product id
    print "$album->{physicalLPProductID}\t";          # -jpk- new lp product id
    print "$album->{physicalCassetteProductID}\t";    # -jpk- new casssette product id
    print "$track->{trackID}\t";
    print "$album->{albumID}\n";

    $seenAlbumIDs{ $disc->{clientAlbumID} }++;
}

foreach my $clientAlbumID ( sort keys %{ $catalog->{albums} } ) {
    next if ( $seenAlbumIDs{$clientAlbumID} );

    my $album = $catalog->{albums}->{$clientAlbumID};

    print "$album->{albumName}\t";
    print "$catalog->{artistIDs}->{$album->{artistID}}\t";
    print "$catalog->{labels}->{$catalog->{labelIDs}->{$album->{labelID}}}->{labelName}\t";
    print "$album->{locationID}\t";
    print "$album->{catalogID}\t";
    print "$album->{upc}\t";
    print "\t";    # print "$disc->{clientAlbumID}\t";
    print "$album->{companyID}\t";
    print "$album->{vendorID}\t";
    print "\t";    # print "$track->{trackName}\t";
    print "\t";    # print "$discNumber\t";
    print "\t";    # print "$album->{discs}->{$discNumber}->{discName}\t";
    print "\t";    # print "$track->{trackNumber}\t";
    print "\t";    # print "$minutes\t";
    print "\t";    # print "$seconds\t";
    print "\t";    # print "$catalog->{artists}->{$catalog->{artistIDs}->{$track->{artistID}}}->{artistName}\t";
    print "\t";    # print "$track->{isrc}\t";
    print "\t";    # print "$clientTrackID\t";
    print "\t";    # print "$track->{genreName}\t";
    print "\t";    # print "$track->{productID}\t";
    print "$album->{productID}\t";
    print "$album->{physicalCDProductID}\t";          # -jff- new physical product id
    print "$album->{physicalLPProductID}\t";          # -jpk- new lp product id
    print "$album->{physicalCassetteProductID}\t";    # -jpk- new lp product id
    print "\t";                                       # print "$track->{trackID}\t";
    print "$album->{albumID}\n";
}

exit;

sub getCatalog {
    my $dbh = shift;
    my %catalog;

    my $sql = "SELECT label_id, label_name FROM label";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    while ( my ( $labelID, $labelName ) = $sth->fetchrow_array() ) {
        my $lowerLabelName = lc($labelName);

        $catalog{labels}->{$lowerLabelName}->{labelID}   = $labelID;
        $catalog{labels}->{$lowerLabelName}->{labelName} = $labelName;

        $catalog{labelIDs}->{$labelID} = $lowerLabelName;
    }
    $sth->finish();

    $sql = "SELECT artist_id, artist_name FROM artist";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    while ( my ( $artistID, $artistName ) = $sth->fetchrow_array() ) {
        my $lowerArtistName = lc($artistName);
        $catalog{artists}->{$lowerArtistName}->{artistID}   = $artistID;
        $catalog{artists}->{$lowerArtistName}->{artistName} = $artistName;

        $catalog{artistIDs}->{$artistID} = $lowerArtistName;
    }
    $sth->finish();

    # -jff- let's add product_id from the product table
    #
    $sql =
        "SELECT client_album_id, album_id, album_name, upc, upc_alt, artist_id, "
      . "label_id, catalog_id, company_id, vendor_id, location_id, number_of_discs, product.product_id, product.product_type "
      . "FROM album, product "
      . "WHERE album.album_id=product.owner_id AND product.product_type != 'T'";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    while (
        my (
            $clientAlbumID, $albumID,   $albumName, $upc,        $upcAlt,        $artistID,  $labelID,
            $catalogID,     $companyID, $vendorID,  $locationID, $numberOfDiscs, $productID, $productType
        )
        = $sth->fetchrow_array()
      ) {
        # we're going to be getting many rows for each album,
        # one for each product type, so we need to store those
        # product ids in different fields. -jff-
        #
        $catalog{albums}->{$clientAlbumID}->{albumID}       = $albumID;
        $catalog{albums}->{$clientAlbumID}->{albumName}     = $albumName;
        $catalog{albums}->{$clientAlbumID}->{upc}           = $upc;
        $catalog{albums}->{$clientAlbumID}->{upcAlt}        = $upcAlt;
        $catalog{albums}->{$clientAlbumID}->{artistID}      = $artistID;
        $catalog{albums}->{$clientAlbumID}->{labelID}       = $labelID;
        $catalog{albums}->{$clientAlbumID}->{catalogID}     = $catalogID;
        $catalog{albums}->{$clientAlbumID}->{companyID}     = $companyID;
        $catalog{albums}->{$clientAlbumID}->{vendorID}      = $vendorID;
        $catalog{albums}->{$clientAlbumID}->{locationID}    = $locationID;
        $catalog{albums}->{$clientAlbumID}->{numberOfDiscs} = $numberOfDiscs;
        $catalog{albumIDs}->{$albumID}                      = $clientAlbumID;

        if ( $productType eq 'A' ) {
            $catalog{albums}->{$clientAlbumID}->{productID} = $productID;
        } elsif ( $productType eq '2' ) {
            $catalog{albums}->{$clientAlbumID}->{physicalCDProductID} = $productID;
        } elsif ( $productType eq '1' ) {
            $catalog{albums}->{$clientAlbumID}->{physicalLPProductID} = $productID;
        } elsif ( $productType eq '4' ) {
            $catalog{albums}->{$clientAlbumID}->{physicalCassetteProductID} = $productID;
        }
    }
    $sth->finish();

    $sql = "SELECT client_album_id, disc_id, disc_name, disc_number, number_of_tracks "
      . "FROM album_disc, album WHERE album_disc.album_id=album.album_id";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    while ( my ( $clientAlbumID, $discID, $discName, $discNumber, $numberOfTracks ) = $sth->fetchrow_array() ) {
        $catalog{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{discID}         = $discID;
        $catalog{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{discName}       = $discName;
        $catalog{albums}->{$clientAlbumID}->{discs}->{$discNumber}->{numberOfTracks} = $numberOfTracks;

        $catalog{discIDs}->{$discID}->{clientAlbumID} = $clientAlbumID;
        $catalog{discIDs}->{$discID}->{discNumber}    = $discNumber;
    }
    $sql =
        "SELECT client_track_id, track_id, track_name, disc_id, artist_id, "
      . "label_id, track_number, duration, isrc, genre_name, product.product_id "
      . "FROM track, product WHERE track.track_id=product.owner_id AND product.product_type='T'";
    $sth = $dbh->prepare($sql);
    $sth->execute();
    while (
        my ( $clientTrackID, $trackID, $trackName, $discID, $artistID, $labelID, $trackNumber, $duration, $isrc, $genreName, $productID ) =
        $sth->fetchrow_array() ) {
        $catalog{tracks}->{$clientTrackID}->{trackID}     = $trackID;
        $catalog{tracks}->{$clientTrackID}->{trackName}   = $trackName;
        $catalog{tracks}->{$clientTrackID}->{discID}      = $discID;
        $catalog{tracks}->{$clientTrackID}->{artistID}    = $artistID;
        $catalog{tracks}->{$clientTrackID}->{labelID}     = $labelID;
        $catalog{tracks}->{$clientTrackID}->{trackNumber} = $trackNumber;
        $catalog{tracks}->{$clientTrackID}->{duration}    = $duration;
        $catalog{tracks}->{$clientTrackID}->{isrc}        = $isrc;
        $catalog{tracks}->{$clientTrackID}->{genreName}   = $genreName;
        $catalog{tracks}->{$clientTrackID}->{productID}   = $productID;
    }
    $sth->finish();

    return \%catalog;
}