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

#use warnings;

use Data::Dumper;
use Getopt::Std;
use Carp;

use Spreadsheet::WriteExcel;
require Spreadsheet::WriteExcel::Big;

use lib '/app/tools/common/lib';
use Common::Assert;
use Common::RSMath;
use Common::Locale;
use Common::Client;

use lib '/app/tools/rps/lib';
use RPS::Statement::Artist::StatementFull;

use RPS::DB::Item::ContractRateType;
use RPS::DB::Item::IncomeSource;
use RPS::DB::Item::Region;
use RPS::DB::Item::Channel;
use RPS::DB::Item::PriceLevel;
use RPS::DB::Item::NewArtistContract;
use RPS::DB::Item::NewArtistContractTerm;
use RPS::DB::Item::Track;
use RPS::DB::Item::Master;

use lib '/app/tools/rps/bin/statements';
use Formats;

$SIG{__DIE__} = \&Carp::confess;

# This determines how much output we spew forth to STDERR.
# The user can set this with the -V command-line argument.
#
my $gVerbosityLevel = 1;

# We'll store formatting info here, once we have a worbook object to work with.
#
my $formats;

# Parse the command-line, then create the CSV!
#
my %options;
parseCommandLine( \%options );

processArtistRunExcel( $options{clientID}, $options{artistRunID}, $options{artistStatementID}, $options{outputPath} );

# ----------------------------------------------------------------------------------------------

sub processArtistRunExcel {
    my ( $clientID, $runID, $statementID, $outFilePath ) = @_;

    # Instantiate the application singleton object.
    #
    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    # This report is intended to be run after the statements have been
    # generated, so the output path should already exist.

    # Create the output path if it isn't there already.
    #
    if ( '/' ne substr( $outFilePath, -1, 1 ) ) {
        $outFilePath .= "/";
    }

    if ( !-d $outFilePath ) {
        mkpath($outFilePath) or die "ERROR: Unable to create path $outFilePath: $!\n";
    }

    # Get rid of the semaphore file
    my $semaphore = $outFilePath . "EXPORT_CP_EXCEL_COMPLETE";
    unlink($semaphore);

    my $targetFile = $outFilePath . "cp_export_run_$runID.xls";

    # Instantiate the Excel object.
    #
    my $workbook = Spreadsheet::WriteExcel::Big->new($targetFile);

    # Set up the special formats.
    #
    ($workbook) = addFormats($workbook);

    # Add the first worksheet
    #
    my $row;
    my $worksheet;
    ( $row, $workbook, $worksheet ) = addWorksheet( $row, $workbook, $worksheet );

    # Get the collection of statements for this run.
    #
    my $statements = RPS::DB::Item::ArtistRoyaltyStatement->GetByArtistRoyaltyRunID($runID);
    while ( my $statement = $statements->next() ) {

        my $_stmtID = $statement->artist_royalty_statement_id;

        next if ( $statementID && $_stmtID != $statementID );

        ( $row, $workbook, $worksheet ) = processStatementExcel( $row, $_stmtID, $workbook, $worksheet, $formats );
    }

    # All done, close it up.
    #
    $workbook->close() or die "Error closing file: $!";

    # Create the semaphore
    #
    open SEMAPHORE, "> $semaphore";
    print SEMAPHORE "done\n";
    close SEMAPHORE;

}    #processArtistRunExcel

sub processStatementExcel {
    my ( $row, $artistStatementID, $workbook, $worksheet, $formats ) = @_;

    # Instantiate the Artist Statement object.
    # This object will contain all (most of) the info we need to output the statement document.
    #
    my $statement = RPS::Statement::Artist::StatementFull->new( artistRoyaltyStatementID => $artistStatementID, );

    ( $row, $workbook, $worksheet ) = addJumboDetails( $row, $statement, $workbook, $worksheet, $formats );

    return ( $row, $workbook, $worksheet );
}

sub addJumboDetails {
    my ( $row, $statement, $workbook, $worksheet, $formats ) = @_;

    my $payee = $statement->ArtistPayee();

    my $albumList = $statement->ArtistStatementAlbumList()->getList();
    foreach my $album (@$albumList) {

        # We need to skip the albums whose income items use the non-payable rate type exclusively.
        #
        my $incomeItemList = $album->IncomeItemList()->getList();
        my $skipAlbum      = 1;
        foreach my $incomeItem (@$incomeItemList) {
            if ( $incomeItem->ContractRateTypeID() != RPS::DB::Item::ContractRateType::kRateTypeNonPayable ) {
                $skipAlbum = 0;
                last;
            }
        }

        # If there are no sales, then we're dealing with license income
        # and/or expenses, which should be included on the statement.
        #
        if ( @$incomeItemList == 0 ) {
            $skipAlbum = 0;
        }

        if ( $skipAlbum == 1 ) {
            next;
        }

        my $rsContractID         = $album->ArtistContractID();
        my $contract             = RPS::DB::Item::NewArtistContract->Lookup( artist_contract_id => $rsContractID );
        my $clientContractID     = $contract ? ( $contract->client_contract_id || '' ) : '';
        my $contractDateModified = $contract ? ( $contract->date_modified || '' ) : '';

        # Ok to look at the album..
        #
        my $incomeItemList = $album->IncomeItemList()->getList();
        foreach my $incomeItem (@$incomeItemList) {

            ( $row, $workbook, $worksheet ) = incrementRow( $row, $workbook, $worksheet );

            my $_termID = $incomeItem->ArtistContractTermID();

            my $term = RPS::DB::Item::NewArtistContractTerm->Lookup( artist_contract_term_id => $_termID, );

            my $trackArtist  = "";
            my $trackCustom1 = "";
            my $trackCustom2 = "";
            my $trackCustom3 = "";
            my $isrc         = "";

            my $_trackID = $incomeItem->TrackID();

            if ($_trackID) {
                my $track = RPS::DB::Item::Track->Lookup( track_id => $_trackID );
                $trackCustom1 = $track->custom_1;
                $trackCustom2 = $track->custom_2;
                $trackCustom3 = $track->custom_3;

                my $artist = RPS::DB::Item::Artist->Lookup( artist_id => $track->artist_id );
                $trackArtist = $artist->name;

                my $master = RPS::DB::Item::Master->Lookup( master_id => $track->master_id );
                $isrc = $master->isrc;
            }

            #----------------------------------------
            # Generate report line with album/track details
            #----------------------------------------

            $worksheet->write_string( $row, 0, $statement->ArtistPayee()->Name() );               # A - payee-name
            $worksheet->write_string( $row, 1, $statement->ArtistPayee()->ClientAccountID() );    # B - client-no
            $worksheet->write_string( $row, 2, $statement->PayeeID() );                           # C - rs-payee-id
            $worksheet->write_string( $row, 3, $album->Album()->Title() );                        # D - album-title
            $worksheet->write_string( $row, 4, $album->Album()->ArtistName() );                   # E - album-artist
            $worksheet->write_string( $row, 5, $album->Album()->CatalogNumber() );                # F - catalog-no
            $worksheet->write_string( $row, 6, $album->Album()->ClientAlbumID() );                # G - client-album-id
            $worksheet->write_string( $row, 7, $album->Album()->AlbumID() );                      # H - rs-album-id
            $worksheet->write_string( $row, 8, $album->ContractName() );                          # I - contract-name
            $worksheet->write_string( $row, 9, $clientContractID );                               # J - contract-id
            $worksheet->write_string( $row, 10, $incomeItem->TrackName() );                       # K - track-title
            $worksheet->write_string( $row, 11, $trackArtist );                                   # L - track-artist
            $worksheet->write_string( $row, 12, $isrc );                                          # M - ISRC
            $worksheet->write_string( $row, 13, incomeSourceIDToName($incomeItem->IncomeSourceID()) ); # N - source
            $worksheet->write_string( $row, 14, regionIDToName( $incomeItem->RegionID(), $incomeItem->UsesDefaultNetRate() ) ); # O - region
            $worksheet->write_string( $row, 15, channelIDToName( $incomeItem->ChannelID() ) );          # P - channel
            $worksheet->write_string( $row, 16, priceLevelIDToName( $incomeItem->PriceLevelID() ) );    # Q - price-tier
            $worksheet->write(        $row, 17, $incomeItem->Price() );                                 # R - sales-price
            $worksheet->write_string( $row, 18, contractRateTypeIDToName( $incomeItem->ContractRateTypeID() ) );    # S - rate-type
            $worksheet->write( $row, 19, $incomeItem->BaseRate() );                                                 # T - base-rate
            $worksheet->write( $row, 20, formatProration( $incomeItem->ProrateTrackCount() ) );                     # U - proration
            $worksheet->write( $row, 21, $incomeItem->Rate() );                                                     # V - prorated-rate
            $worksheet->write( $row, 22, $incomeItem->RateReduction() );                                            # W - rate-reduction
            $worksheet->write( $row, 23, $incomeItem->PackagingDeduction() );                                       # X - packaging
            $worksheet->write( $row, 24, $incomeItem->NetRate() );                                                  # Y - effective-rate
            $worksheet->write( $row, 25, $incomeItem->Sales() );                                                    # Z - gross-units
            $worksheet->write( $row, 26, $incomeItem->Revenue() );                                                  # AA - gross-sales
            $worksheet->write( $row, 27, $incomeItem->PercentageOfSales() );                                        # AB - percent-of-sales
            $worksheet->write( $row, 28, $incomeItem->FreeGoodsDeduction() );                                       # AC - free-goods

            if ( $incomeItem->ContractRateTypeID() == RPS::DB::Item::ContractRateType::kRateTypePercentRevenue ) {
                $worksheet->write( $row, 29, $incomeItem->RevenueReserved() );                                      # AD - reserves
                $worksheet->write( $row, 30, $incomeItem->RevenueLiquidated() );                                    # AE - liquidations
            } else {
                $worksheet->write( $row, 29, $incomeItem->UnitsReserved() );                                        # AD - reserves
                $worksheet->write( $row, 30, $incomeItem->UnitsLiquidated() );                                      # AE - liquidations
            }

            $worksheet->write( $row, 31, $incomeItem->Returns() );                                                  # AF - returns
            $worksheet->write( $row, 32, $incomeItem->NetUnits() );                                                 # AG - net-units
            $worksheet->write( $row, 33, $incomeItem->NetRevenue() );                                               # AH - net-sales
            $worksheet->write( $row, 34, $incomeItem->Total() );                                                    # AI - total
            $worksheet->write( $row, 35, $album->ArtistContractID );                                                # AJ - rs-contract-id
            $worksheet->write_string( $row, 36, $contractDateModified );                                            # AK - contract-date-modified
            $worksheet->write_string( $row, 37, $album->Album()->Custom1() );                                       # AL - album-custom-1
            $worksheet->write_string( $row, 38, $album->Album()->Custom2() );                                       # AM - album-custom-2
            $worksheet->write_string( $row, 39, $album->Album()->Custom3() );                                       # AN - album-custom-3
            $worksheet->write_string( $row, 40, $trackCustom1);                                                     # AO - track-custom-1
            $worksheet->write_string( $row, 41, $trackCustom2 );                                                    # AP - track-custom-2
            $worksheet->write_string( $row, 42, $trackCustom3 );                                                    # AQ - track-custom-3

        }    #income item loop

    }    #album loop
    return ( $row, $workbook, $worksheet );
}    #addJumboDetails

# For now, we are not realy going to be doing any formatting of numbers.
#

sub formatNumber {
    my ($value) = @_;

    #$value = Formats::formatNumber($value);

    return $value;
}

sub formatMoney {
    my ($value) = @_;

    #$value = Formats::formatMoney($value);

    return $value;
}

sub formatPercent {
    my ($value) = @_;

    #$value = Formats::formatPercent($value);

    return $value;
}

sub formatProration {
    my ($value) = @_;

    if ( $value == 0 ) {
        return '';
    }

    return $value;
}

# These functions map ids to their text representations.
# Basically, the first time we ask for a particular id->string mapping, we
# will query the correct database, and build a hash.  Subsequent calls just
# hit the hash.
#
# Because these tables are so similar, I abstracted the guts into the _genericMapAccessor
# method (to save myself some typing).
#
my %idMaps;

sub _genericMapAccessor {
    my ( $collectionAccessor, $idName, $id ) = @_;

    if ( !defined $idMaps{$collectionAccessor} ) {
        $idMaps{$collectionAccessor} = {};

        # This is a very naughty thing to do, but it works great in this context.
        #
        #        no strict 'refs';
        #        my $c = &$collectionAccessor();
        #        use strict 'refs';
        my $c = $collectionAccessor->GetAll();

        while ( my $item = $c->next() ) {
            if ( $idName ne 'product_type_id' ) {
                $idMaps{$collectionAccessor}{ $item->$idName() } = $item->name;
            } else {
                $idMaps{$collectionAccessor}{ $item->$idName() } =
                  $item->description;
            }
        }
    }

    return $idMaps{$collectionAccessor}{$id};
}

sub incomeSourceIDToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::IncomeSource', 'income_source_id', $id );
}

sub channelIDToName {
    my ($id) = @_;

    #return "All" if( 0 == $id );
    return _genericMapAccessor( 'RPS::DB::Item::Channel', 'channel_id', $id );
}

sub priceLevelIDToName {
    my ($id) = @_;
    return "" if ( 4 == $id || 5 == $id );    # ignore album and track download
    return _genericMapAccessor( 'RPS::DB::Item::PriceLevel', 'price_level_id', $id );
}

sub contractRateTypeIDToName {
    my ($id) = @_;
    return _genericMapAccessor( 'RPS::DB::Item::ContractRateType', 'contract_rate_type_id', $id );
}

sub regionIDToName {
    my ( $id, $defaultRate ) = @_;

    if ( $id == 0 && $defaultRate == 1 ) {
        return 'All';
    } else {
        return _genericMapAccessor( 'RPS::DB::Item::Region', 'region_id', $id );
    }
}

# Excel support subroutines
#
sub incrementRow {
    my ( $row, $workbook, $worksheet ) = @_;

    # Have we reached the max row for excel?
    #
    if ( $row >= 65534 ) {

        # Add a new worksheet
        #
        ( $row, $workbook, $worksheet ) = addWorksheet( $row, $workbook, $worksheet );
    }

    $row++;

    return ( $row, $workbook, $worksheet );
}

sub addJumboHeaders {
    my ( $row, $worksheet ) = @_;

    $worksheet->freeze_panes( 1, 0 );
    $worksheet->write( $row, 0,  'payee-name',             $formats->{header} );    # A
    $worksheet->write( $row, 1,  'client-no',              $formats->{header} );    # B
    $worksheet->write( $row, 2,  'rs-payee-id',            $formats->{header} );    # C
    $worksheet->write( $row, 3,  'album-title',            $formats->{header} );    # D
    $worksheet->write( $row, 4,  'album-artist',           $formats->{header} );    # E
    $worksheet->write( $row, 5,  'catalog-no',             $formats->{header} );    # F
    $worksheet->write( $row, 6,  'client-album-id',        $formats->{header} );    # G
    $worksheet->write( $row, 7,  'rs-album-id',            $formats->{header} );    # H
    $worksheet->write( $row, 8,  'contract-name',          $formats->{header} );    # I
    $worksheet->write( $row, 9,  'contract-id',            $formats->{header} );    # J
    $worksheet->write( $row, 10, 'track-title',            $formats->{header} );    # K
    $worksheet->write( $row, 11, 'track-artist',           $formats->{header} );    # L
    $worksheet->write( $row, 12, 'ISRC',                   $formats->{header} );    # M
    $worksheet->write( $row, 13, 'source',                 $formats->{header} );    # N
    $worksheet->write( $row, 14, 'region',                 $formats->{header} );    # O
    $worksheet->write( $row, 15, 'channel',                $formats->{header} );    # P
    $worksheet->write( $row, 16, 'price-tier',             $formats->{header} );    # Q
    $worksheet->write( $row, 17, 'sales-price',            $formats->{header} );    # R
    $worksheet->write( $row, 18, 'rate-type',              $formats->{header} );    # S
    $worksheet->write( $row, 19, 'base-rate',              $formats->{header} );    # T
    $worksheet->write( $row, 20, 'proration',              $formats->{header} );    # U
    $worksheet->write( $row, 21, 'prorated-rate',          $formats->{header} );    # V
    $worksheet->write( $row, 22, 'rate-reduction',         $formats->{header} );    # W
    $worksheet->write( $row, 23, 'packaging',              $formats->{header} );    # X
    $worksheet->write( $row, 24, 'effective-rate',         $formats->{header} );    # Y
    $worksheet->write( $row, 25, 'gross-units',            $formats->{header} );    # Z
    $worksheet->write( $row, 26, 'gross-sales',            $formats->{header} );    # AA
    $worksheet->write( $row, 27, 'percent-of-sales',       $formats->{header} );    # AB
    $worksheet->write( $row, 28, 'free-goods',             $formats->{header} );    # AC
    $worksheet->write( $row, 29, 'reserves',               $formats->{header} );    # AD
    $worksheet->write( $row, 30, 'liquidations',           $formats->{header} );    # AE
    $worksheet->write( $row, 31, 'returns',                $formats->{header} );    # AF
    $worksheet->write( $row, 32, 'net-units',              $formats->{header} );    # AG
    $worksheet->write( $row, 33, 'net-sales',              $formats->{header} );    # AH
    $worksheet->write( $row, 34, 'total',                  $formats->{header} );    # AI
    $worksheet->write( $row, 35, 'rs-contract-id',         $formats->{header} );    # AJ
    $worksheet->write( $row, 36, 'contract-date-modified', $formats->{header} );    # AK
    $worksheet->write( $row, 37, 'album-custom-1',         $formats->{header} );    # AL
    $worksheet->write( $row, 38, 'album-custom-2',         $formats->{header} );    # AM
    $worksheet->write( $row, 39, 'album-custom-3',         $formats->{header} );    # AN
    $worksheet->write( $row, 40, 'track-custom-1',         $formats->{header} );    # AO
    $worksheet->write( $row, 41, 'track-custom-2',         $formats->{header} );    # AP
    $worksheet->write( $row, 42, 'track-custom-3',         $formats->{header} );    # AQ

    return ( $row, $worksheet );
}

sub addWorksheet {
    my ( $row, $workbook, $worksheet ) = @_;

    $worksheet = $workbook->add_worksheet();

    print STDERR "worksheet added \n";

    # Set up the row variable.
    #
    $row = 0;

    # Add the column headers
    #
    ( $row, $worksheet ) = addJumboHeaders( $row, $worksheet );

    return ( $row, $workbook, $worksheet );
}

sub addFormats {
    my ($workbook) = @_;

    my $formatB = $workbook->add_format();
    $formatB->set_bold();

    my $formatRB = $workbook->add_format();
    $formatRB->set_bold();
    $formatRB->set_align('right');

    my $formatR = $workbook->add_format();
    $formatR->set_align('right');

    my $formatL = $workbook->add_format();
    $formatL->set_align('left');

    my $formatUB = $workbook->add_format();
    $formatUB->set_bold();
    $formatUB->set_bottom(1);

    my $formatUR = $workbook->add_format();
    $formatUR->set_align('right');
    $formatUR->set_bottom(1);

    my $formatU = $workbook->add_format();
    $formatU->set_bottom(1);
    my $formatHeader = $workbook->add_format();
    my $headerFillColor = $workbook->set_custom_color( 23, 216, 216, 216 );
    $formatHeader->set_bg_color($headerFillColor);
    $formatHeader->set_bottom(2);    # continuous (weight 2)
    $formatHeader->set_left(1);      # continuous (weight 1)
    $formatHeader->set_right(1);     # continuous (weight 1)
    $formatHeader->set_top(1);       # continuous (weight 1)
    $formatHeader->set_bold();

    #$formatHeader->set_size(8);
    $formatHeader->set_text_wrap();

    $formats = {
        bold            => $formatB,
        right_bold      => $formatRB,
        right           => $formatR,
        left            => $formatL,
        underline_bold  => $formatUB,
        right_underline => $formatUR,
        underline       => $formatU,
        header          => $formatHeader,
    };

    return ($workbook);
}

#
# Boring script stuff below...
#

sub parseCommandLine {
    my ($settings) = @_;

    my %opt;
    getopts( 's:r:c:p:V:', \%opt );

    if ( !$opt{r} || !$opt{c} ) {
        usage();
        exit(1);
    }
    $settings->{artistRunID}       = $opt{r};
    $settings->{clientID}          = $opt{c};
    $settings->{outputPath}        = $opt{p};
    $settings->{artistStatementID} = $opt{s};

    if ( defined $opt{V} ) {
        $gVerbosityLevel = $opt{V};
    }
}

sub usage {
    print STDERR "\nusage: $0 -c <client_id> -r <artist_run_id> [-p <output path>]\n";
    print STDERR "\n";
    print STDERR "Arguments:\n";
    print STDERR "\t-c <client_id>\t\t\tThe client_id of the client to process\n";
    print STDERR "\t-r <artist_royalty_run_id>\tThe id of the artist run to generate export data for\n";
    print STDERR "\t-p <output path>\tThe output path. Optional. If not provided, use current directory.\n";
}

sub report {
    my ( $string, $verbosity ) = @_;
    $verbosity = 1 unless defined $verbosity;

    if ( $gVerbosityLevel >= $verbosity ) {
        print STDERR $string . "\n";
    }
}