#!/usr/bin/perl

use strict;

use Getopt::Long;
use Data::Dumper;
use Date::Calc qw(Add_Delta_YM Days_in_Month Month_to_Text);

use lib '/app/tools/common/lib';
use Common::Consts;
use Common::RSDB;
use Common::DB::Item::Client;
use Common::RSApp;
use Common::Client;
use Common::RSDB::StaticList;

use lib '/app/tools/rps/lib';
use RPS::File::File;
use RPS::File::Sale;

use lib '/app/tools/raptor/lib';
use Raptor::DB::Item::Period;
use Raptor::DB::Item::Sale;

use lib '/app/tools/data_classes/lib';
use Period::RoyaltyPeriod;

my %args = ();
parseCommandLine(\%args);
my $reportDate = $args{date};

if ( !$reportDate ) {
    die "Date required";
}

my $startDate;
my $endDate;

if ( $reportDate =~ /(\d{4})(\d{2})(\d{2})/ ) {
    my ( $year, $month, $day ) = Add_Delta_YM( $1, $2, $3, 0, -1 );
    $startDate = sprintf( "%04d-%02d-%02d", $year, $month, 1 );
    $endDate =   sprintf( "%04d-%02d-%02d", $year, $month, Days_in_Month( $year, $month ) );
} else {
    die "Date must be in YYYYMMDD format.";
}

# database key of RSCOMMON database in RSDB.pm
my $rscommon = 0;

# database handle for RSCOMMON.
my $rscommonDBH;

# gets db/host information of RSCOMMON.
my ( $host, $dbname, $user, $pwd ) = getRsCommonHostInfo();

# connects to RSCOMMON database.
$rscommonDBH = DBI->connect( "DBI:mysql:host=$host:database=$dbname", $user, $pwd, { 'RaiseError' => 1 } );

# stores full customer names.
my %fullCustomerNames = ();

# gets all full customer names, rather then clean name in
# Common/Const.pm.
getFullCustomerNames();


my %clientIDs;
$clientIDs{10}  = 1; # DigitalPressure.com
$clientIDs{11}  = 1; # Instinct Records
$clientIDs{15}  = 1; # Oh Boy Records
$clientIDs{23}  = 1; # Compass Records
$clientIDs{45}  = 1; # Festival Five Records
$clientIDs{46}  = 1; # Mardi Gras Records
$clientIDs{53}  = 1; # Spy Music Group / M2K Records
$clientIDs{67}  = 1; # Digital Music Marketing
$clientIDs{68}  = 1; # Six Degrees Records
$clientIDs{127} = 1; # Arc Music
$clientIDs{141} = 1; # Dischord Records
# $clientIDs{150} = 1; # Barbes Records
$clientIDs{162} = 1; # CBS Records
$clientIDs{203} = 1; # Locobop
$clientIDs{209} = 1; # Mountain Apple
$clientIDs{223} = 1; # REL
$clientIDs{226} = 1; # Greentrax
$clientIDs{252} = 1; # ATO Records
$clientIDs{258} = 1; # Bama Rags
$clientIDs{349} = 1; # August Creative Content


my @headers = ('RS Customer Name', 'RS Customer ID', 'Revenue Period Name', 'Revenue Period ID', 'Currency Code', 'Digital Revenue Processed (Billable)', 'Physical Revenue Processed (Billable)');
print join(",", @headers), "\n";

our %CLIENT_DB = Common::RSDB::StaticList::clientDB;

foreach my $clientID ( ( sort sortIdByCustomerName ( keys %clientIDs ) ) ) {
	my $app = Common::RSApp->new(clientID => $clientID);
    my $dbName = $CLIENT_DB{$clientID}{db_name};
    my $server = $CLIENT_DB{$clientID}{server};
    my $userName = $CLIENT_DB{$clientID}{username};
    my $password = $CLIENT_DB{$clientID}{password};
    next unless ($dbName =~ m/^C_/);
    next unless ($password);

    my $client = Common::DB::Item::Client->Lookup(client_id => $clientID);
    my $clientName = $client->client_name();
    my $baseCurrency = Common::Client::Current()->Locale()->currencyFormat()->currencyCode();

    my $services = Client::Service::GetAllServiceNames( client_id => $clientID );

    my $periods = Raptor::DB::Item::Period->GetByDate(start_date => $startDate, end_date => $endDate);

    while ( $periods->hasNext() ) {
        my $period = $periods->next();

        my $periodID    = $period->period_id;
        my $periodName  = $period->name;
        if ( !$periodName ) {
            my ( $startYear, $startMonth, $startDay ) = split( '-', $period->start_date );
            my $startDisplay = substr( Month_to_Text($startMonth), 0, 3 ) . " " . $startDay . ", " . $startYear;

            my $endDisplay;
            if ( defined $period->end_date && $period->end_date ne '' ) {
                my ( $endYear, $endMonth, $endDay ) = split( '-', $period->end_date );
                $endDisplay = substr( Month_to_Text($endMonth), 0, 3 ) . " " . $endDay . ", " . $endYear;
            }

            $periodName = $startDisplay . " - " . $endDisplay;
        }

        # Surrounding the period name in quotes in case it contains a comma.
        $periodName = '"' . $periodName . '"';

        my $files = Raptor::DB::Item::File->GetByPeriodID($periodID);

        my %digitalSales;
        my %physicalSales;
        my %licenseIncome;

        while ( my $file = $files->next() ) {
            my $serviceID   = $file->service_id;
            my $serviceName = $services->{$serviceID};
            my $fileID      = $file->file_id;
            my $fileObj     = RPS::File::File->new( file_id => $fileID );

            my $revenue;
            if ( $file->input_conversion_rate ) {
                $revenue = $fileObj->AdjustedRevenue;
            } else {
                $revenue = $file->revenue;
            }

            if ( $file->type_id eq File::File::FILETYPE_LICENSE_INCOME ) {

                # license income (no service name)
                $licenseIncome{revenue} += $revenue;
            } elsif ( $file->physical == 1 ) {

                # physical sales
                $physicalSales{$serviceName}{revenue} += $revenue;
            } elsif ( $file->physical == 2 ) {

                # digital and/or physical
                my $sales = Raptor::DB::Item::Sale->GetByFileID( $file->file_id );
                while ( my $sale = $sales->next() ) {
                    my $distFee = $fileObj->GetDistFeePct( $sale->format_type );
                    if ($distFee) {
                        $distFee = ( 100 - $distFee ) * .01;
                    } else {
                        $distFee = 1;
                    }
                    if (   $sale->product_type eq RPS::File::Sale::TYPE_TRACK
                        || $sale->product_type eq RPS::File::Sale::TYPE_ALBUM ) {
                        $digitalSales{$serviceName}{revenue} += ( $sale->price * $sale->units * $sale->conversion_rate * $distFee );
                    } else {
                        $physicalSales{$serviceName}{revenue} += ( $sale->total_revenue * $sale->conversion_rate * $distFee );
                    }
                }
            } else {

                # digital sales
                $digitalSales{$serviceName}{revenue} += $revenue;
            }
        }

        my $billableDigital;
        my $hasNegativeDigital;
        foreach my $serviceName ( sort { uc($a) cmp uc($b) } keys %digitalSales ) {
            my $digitalRevenue  = sprintf( "%.2f", $digitalSales{$serviceName}{revenue} );
            if ( $digitalRevenue > 0 ) {
                $billableDigital += $digitalRevenue;
            } elsif ( $digitalRevenue < 0 ) {
                $hasNegativeDigital = 1;
            }
        }

        my $billablePhysical;
        my $hasNegativePhysical;
        foreach my $serviceName ( sort { uc($a) cmp uc($b) } keys %physicalSales ) {
            my $physicalRevenue  = sprintf( "%.2f", $physicalSales{$serviceName}{revenue} );
            if ( $physicalRevenue > 0 ) {
                $billablePhysical += $physicalRevenue;
            } elsif ( $physicalRevenue < 0 ) {
                $hasNegativePhysical = 1;
            }
        }

        # Adding License Income to the physical revenue total
        my $hasNegativeLicense;
        if ( $licenseIncome{revenue} ) {
            my $licenseRevenue  = sprintf( "%.2f", $licenseIncome{revenue} );
            if ( $licenseRevenue > 0 ) {
                $billablePhysical += $licenseRevenue;
            } elsif ( $licenseRevenue < 0 ) {
                $hasNegativeLicense = 1;
            }
        }

        # If the revenue fields are empty because of negative amounts, we want to display 0 instead of BLANK.
        if ( !$billableDigital && $hasNegativeDigital ) {
            $billableDigital = 0;
        }

        if ( !$billablePhysical && ( $hasNegativePhysical || $hasNegativeLicense ) ) {
            $billablePhysical = 0;
        }

        my @clientData = ($clientName, $clientID, $periodName, $periodID, $baseCurrency, $billableDigital, $billablePhysical);
        print join(",", @clientData), "\n";
    }
}


sub _parseResults {
    my $results = shift;
    my @resultArray = split('\n',$results);
    return $resultArray[1];
}

# gets full customer names.
sub getFullCustomerNames {
    my $sql = "select client_id, client_name from client";

    my $sth = $rscommonDBH->prepare($sql)
      || die $DBI::err . ": " . $DIB::errstr;
    $sth->execute() || die $DBI::err . ": " . $DIB::errstr;

    while ( my $ref = $sth->fetchrow_hashref() ) {
        $fullCustomerNames{ $ref->{'client_id'} } = $ref->{'client_name'};
    }
    $sth->finish();
}

# sorts customer ID by its customer name.
sub sortIdByCustomerName {

    # filters out RSCOMMON database.
    if ( $a == 0 || $b == 0 ) {
        return 1;
    }

    my $name1 = "";
    my $name2 = "";

    # makes sure all customer have a name.
    if ( defined $fullCustomerNames{$a} ) {
        $name1 = $fullCustomerNames{$a};
    }

    if ( defined $fullCustomerNames{$b} ) {
        $name2 = $fullCustomerNames{$b};
    }

    return uc($name1) cmp uc($name2);
}

# gets db/host information for RSCOMMON.
sub getRsCommonHostInfo {
    return $Common::RSDB::CLIENT_DB{$rscommon}{"server"},
      $Common::RSDB::CLIENT_DB{$rscommon}{"db_name"},
      $Common::RSDB::CLIENT_DB{$rscommon}{"username"},
      $Common::RSDB::CLIENT_DB{$rscommon}{"password"};
}

sub parseCommandLine {
   my( $a ) = @_;
   my $date;
   GetOptions(
      'd|date=s' => \$date,
   );
   ${_[0]}{date} = $date;

   unless( $date ) {
      die( usage("You must specify a date") );
   }


}#parseCommandLine

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