#!/usr/bin/perl

use strict;
use warnings;

use Getopt::Std;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::Locale;
use Common::Client;
use Common::RSApp;
use Common::Locale;
use Excel::Writer::XLSX;

use constant MAX_WORKSHEET_HYPERLINKS => 65_000;

# Parse the command-line, then create the report
#
my %options;
parseCommandLine( \%options );
gen_exception_report( $options{clientID}, $options{fileName} );

sub gen_exception_report {
    my ($clientID, $fileName) = @_;

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

    my $locale = Common::Client::Current()->Locale();
    my $baseCurrencyCode = lc $locale->currencyFormat()->currencyCode();

    my $tmpFile    = $fileName . '-tmp';
    my $exampleURL = "https://" . Common::RSApp::GetClientVHost() . ".royaltyshare.com/app/matcher?c=sale&sale=";

    my $totalCurrencyRevenueField = "total_" . $baseCurrencyCode . "_revenue";
    my @headerFields = (
        "service",
        "product_type",
        "media_type",
        "service_product_id",
        "client_product_id",
        "upc",
        "isrc",
        "artist_name",
        "album_name",
        "track_name",
        "label_name",
        "track_num",
        "sum_net_units",
        $totalCurrencyRevenueField,
        "total_exceptions",
        "example_url"
    );

    my $sql = qq/
        SELECT
            se.service_name                             AS service,
            s.product_type                              AS product_type,
            s.media_type                                AS media_type,
            s.service_product_id                        AS service_product_id,
            s.client_product_id                         AS client_product_id,
            s.upc                                       AS upc,
            s.isrc                                      AS isrc,
            s.artist_name                               AS artist_name,
            s.album_name                                AS album_name,
            s.track_name                                AS track_name,
            s.label_name                                AS label_name,
            s.track_num                                 AS track_num,
            SUM( s.units + (s.sales - s.returns) )      AS sum_net_units,
            SUM(
                IF(s.conversion_rate = 0, 1, s.conversion_rate) *
                ( IFNULL(s.units, 0) * IFNULL(s.price, 0) + IFNULL(s.total_revenue, 0) )
            )                                           AS $totalCurrencyRevenueField,
            COUNT(s.sale_id)                            AS total_exceptions,
            s.sale_id                                   AS sale_id
        FROM `file` AS f, sale AS s, service AS se
        WHERE 1
            AND f.file_id = s.file_id
            AND f.service_id = se.service_id
            AND product_id IS NULL
            AND file_status = 4
            AND period_id = 0
        GROUP BY se.service_name, s.product_type, s.media_type,
                 s.service_product_id, s.client_product_id, s.upc, s.isrc,
                 s.artist_name, s.album_name, s.track_name, s.track_num
        ORDER BY $totalCurrencyRevenueField DESC
    /;

    my $db   = Common::RSDB->new( client_id => $clientID );
    my $dbh  = $db->DBH;
    my $sth  = $dbh->prepare($sql);
    my $rows = $sth->execute();

    # create an Excel file
    my $oWorkbook    = _createWorkbook( $tmpFile );
    my $hWbFormats   = _initWorkbookFormats( $oWorkbook, { bold => 1 } );

    my ($sheetIndex, $sheetNamePattern) = (1, 'consolidated_exceptions');
    my $sheetName  = $sheetNamePattern . '_' . $sheetIndex++;
    my $oWorksheet = _createWorksheet($oWorkbook, $sheetName, $hWbFormats, \@headerFields);

    my $row = 1;
    while ( my $hRow = $sth->fetchrow_hashref ) {
        # add a new worksheet if needed
        if ( $row == MAX_WORKSHEET_HYPERLINKS ) {
            my $newSheetName = $sheetNamePattern . '_' . $sheetIndex++;
            $oWorksheet = _createWorksheet( $oWorkbook, $newSheetName , $hWbFormats, \@headerFields);
            $row = 1;
        }

        my $col = 0;
        $hRow->{example_url} = $exampleURL . $hRow->{sale_id};
        foreach my $value ( @$hRow{@headerFields} ) {
            $oWorksheet->write( $row, $col, $value );
            $col++;
        }

        $row++;
    }

    $oWorkbook->close;

    system("mv $tmpFile $fileName");
}

sub _createWorkbook {
    my $fileName = shift;

    my $oWorkbook  = Excel::Writer::XLSX->new( $fileName );
    return $oWorkbook;
}

sub _initWorkbookFormats {
    my ($oWorkbook, $hFormats) = @_;

    my %formats;
    while ( my($key, $val) = each %$hFormats ) {
        my $oFormat = $oWorkbook->add_format( $key => $val );
        $formats{$key} = $oFormat;
    }

    return \%formats;
}

sub _createWorksheet {
    my ($oWorkbook, $sheetName, $hFormats, $aHeader) = @_;

    my $oWorksheet = $oWorkbook->add_worksheet($sheetName);
    for my $col ( 0 .. $#{$aHeader} ) {
        $oWorksheet->write( 0, $col, $aHeader->[$col], $hFormats->{bold} );
    }

    return $oWorksheet;
}


sub _formatDate {
    my ($date) = @_;

    $date = Common::Client::Current()->Locale()->formatDate($date);

    return $date;
}

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

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

    if ( !$opt{c} || !$opt{f} ) {
        usage();
        exit(1);
    }
    $settings->{clientID} = $opt{c};
    $settings->{fileName} = $opt{f};
}

sub usage {
    print STDERR "\nusage: $0 -c <client_id> -f <file_name>\n";
    print STDERR "\n";
    print STDERR "Arguments:\n";
    print STDERR "\t-c <client_id>\tThe client_id of the client to process\n";
    print STDERR "\t-f <file_name>\tThe name for the file that is produced\n";

    return 1;
}