#!/usr/bin/perl

use strict;
use warnings;

use Getopt::Std;
use Data::Dumper;
use POSIX qw(floor);
use Carp;

use Excel::Writer::XLSX;

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

use lib '/app/tools/rps/lib';
use RPS::DB::Item::LicenseIncome;
use RPS::DB::Item::Album;
use RPS::DB::Item::Track;
use RPS::DB::Item::Contract;
use RPS::DB::Item::Payee;
use RPS::DB::Item::ArtistRoyaltyRun;

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

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

my $gVerbosityLevel = 1;

my @months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);

my %options;
parseCommandLine(\%options);

createLicenseIncomePayableAuditReport($options{clientID}, $options{runID}, $options{outputPath});

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

    my $appSingleton = Common::RSApp->new(clientID => $clientID);

    # Get run information using ArtistRoyaltyRun
    my ($runName, $payor) = RPS::DB::Item::ArtistRoyaltyRun->GetRunNameAndPayor($runID);

    # Sanitize runName and payor for filename
    $runName =~ s/[^a-zA-Z0-9]/_/g;
    $payor =~ s/[^a-zA-Z0-9]/_/g;
    # Remove multiple consecutive underscores
    $runName =~ s/_+/_/g;
    $payor =~ s/_+/_/g;
    # Remove leading/trailing underscores
    $runName =~ s/^_|_$//g;
    $payor =~ s/^_|_$//g;

    if ('/' ne substr($outFilePath, -1, 1)) {
        $outFilePath .= "/";
    }

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

    my $semaphore = $outFilePath . "LICENSE_INCOME_PAYABLE_AUDIT_COMPLETE";
    unlink($semaphore);

    my $targetFile = $outFilePath . "License_Income_${runName}_${payor}_${runID}.xlsx";

    my $workbook = Excel::Writer::XLSX->new($targetFile);
    my $worksheet = $workbook->add_worksheet();

    # Replace the call to Formats::addFormats with a local function
    my $formats = addFormats($workbook);

    my @headers = (
        'payee-name', 'client-account-no', 'rs-payee-id', 'contract-title', 'contract-id',
        'rs-contract-id', 'album-title', 'catalog-no', 'client-album-id', 'rs-album-id',
        'track-title', 'isrc', 'rs-track-id', 'income-type', 'memo', 'rate-type', 'rate',
        'units', 'sales', 'total', 'file-name', 'rs-file-id', 'line-#', 'sales-period-name',
        'sales-period-id', 'license-income-id', 'album-custom-1', 'album-custom-2',
        'album-custom-3', 'track-custom-1', 'track-custom-2', 'track-custom-3'
    );

    for my $col (0 .. $#headers) {
        $worksheet->write(0, $col, $headers[$col], $formats->{header});
    }

    # Freeze the top row
    $worksheet->freeze_panes(1, 0);

    my $row = 1;
    my $licenseIncomeColl = RPS::DB::Item::LicenseIncome->GetPayableAuditReportByRunID($runID);
    while (my $licenseIncome = $licenseIncomeColl->next()) {
        # Write each column from the query results to the worksheet
        $worksheet->write($row, 0, $licenseIncome->{payee_name});
        $worksheet->write($row, 1, $licenseIncome->{client_account_no});
        $worksheet->write($row, 2, $licenseIncome->{payee_id});
        $worksheet->write($row, 3, $licenseIncome->{contract_title});
        $worksheet->write($row, 4, $licenseIncome->{client_contract_id});
        $worksheet->write($row, 5, $licenseIncome->{artist_contract_id});
        $worksheet->write($row, 6, $licenseIncome->{album_title});
        $worksheet->write($row, 7, $licenseIncome->{catalog_number});
        $worksheet->write($row, 8, $licenseIncome->{client_album_id});
        $worksheet->write($row, 9, ($licenseIncome->{album_id} != 0 ? $licenseIncome->{album_id} : ''));
        $worksheet->write($row, 10, $licenseIncome->{track_title});
        $worksheet->write($row, 11, $licenseIncome->{isrc});
        $worksheet->write($row, 12, ($licenseIncome->{track_id} != 0 ? $licenseIncome->{track_id} : ''));
        $worksheet->write($row, 13, $licenseIncome->{income_type_name});
        $worksheet->write($row, 14, Common::UTF8::Encode($licenseIncome->{memo}));
        $worksheet->write($row, 15, "% Net Revenue");
        $worksheet->write($row, 16, $licenseIncome->{rate}, $formats->{number4});
        $worksheet->write($row, 17, $licenseIncome->{units});
        $worksheet->write($row, 18, $licenseIncome->{revenue}, $formats->{money});
        $worksheet->write($row, 19, $licenseIncome->{net_revenue}, $formats->{money});
        $worksheet->write($row, 20, $licenseIncome->{file_name});
        $worksheet->write($row, 21, $licenseIncome->{file_id});
        $worksheet->write($row, 22, $licenseIncome->{line_number});

        my $periodName = $licenseIncome->{sales_period_name};
        if (!$periodName && $licenseIncome->{sales_period_start_date} && $licenseIncome->{sales_period_end_date}) {
            $periodName = sprintf("%s - %s",
                formatDate($licenseIncome->{sales_period_start_date}),
                formatDate($licenseIncome->{sales_period_end_date})
            );
        }
        $worksheet->write($row, 23, $periodName);

        $worksheet->write($row, 24, $licenseIncome->{sales_period_id});
        $worksheet->write($row, 25, $licenseIncome->{license_income_id});
        $worksheet->write($row, 26, $licenseIncome->{album_custom_1});
        $worksheet->write($row, 27, $licenseIncome->{album_custom_2});
        $worksheet->write($row, 28, $licenseIncome->{album_custom_3});
        $worksheet->write($row, 29, $licenseIncome->{track_custom_1});
        $worksheet->write($row, 30, $licenseIncome->{track_custom_2});
        $worksheet->write($row, 31, $licenseIncome->{track_custom_3});

        $row++;
    }

    $workbook->close();

    open(my $semaphoreFile, '>', $semaphore) or die "Could not open file '$semaphore' $!";
    print $semaphoreFile "done";
    close $semaphoreFile;
}

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

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

    if (!$opt{c} || !$opt{r} || !$opt{o}) {
        usage();
        exit(1);
    }

    $settings->{clientID} = $opt{c};
    $settings->{runID} = $opt{r};
    $settings->{outputPath} = $opt{o};
    $gVerbosityLevel = $opt{V} if defined $opt{V};
}

sub usage {
    print STDERR "\nusage: $0 -c <client_id> -r <run_id> -o <output_path> [-V <verbosity>]\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 <run_id>\t\t\tThe id of the run to process\n";
    print STDERR "\t-o <output_path>\t\tThe path where output files should be written\n";
    print STDERR "\t-V <verbosity>\t\t\tVerbosity level (optional)\n";
}

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

    $formats{header} = $workbook->add_format();
    $formats{header}->set_bold();
    $formats{header}->set_align('left');
    $formats{header}->set_bg_color('#D8D8D8');
    $formats{header}->set_text_wrap();
    $formats{header}->set_border();
    $formats{header}->set_border_color('#000000');

    $formats{number4} = $workbook->add_format();
    $formats{number4}->set_num_format('0.0000');

    $formats{money} = $workbook->add_format();
    $formats{money}->set_num_format('$#,##0.00');

    return \%formats;
}

sub formatDate {
    my ($dateStr) = @_;
    my ($year, $month, $day) = split('-', $dateStr);
    return sprintf("%s %d, %d", $months[$month - 1], $day, $year);
}