#!/usr/bin/perl

use strict;
use POSIX qw(ceil);

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

use Getopt::Std;
use Common::Consts;

use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::RSMath;

use lib '/app/tools/data_classes/lib';
use File::File;
use Client::Service;

use lib '/app/tools/sale_import/lib';
use Sale::File;

use lib '/app/tools/common/lib';
use Common::Util;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::Track;
use RPS::DB::Item::TrackLicense;
use RPS::DB::Item::LicenseReserve;
use RPS::DB::Item::Album;
use RPS::DB::Item::AlbumContract;
use RPS::DB::Item::ControlledComposition;
use RPS::DB::Item::ReserveLiquidation;
use RPS::DB::Item::NewArtistContractTerm;
use RPS::DB::Item::NewArtistContract;
use RPS::DB::Item::ArtistContractTermReserve;
use RPS::DB::Item::ArtistContractTermReserveRun;
use RPS::DB::Item::LicenseReserveRun;


my $licenseReserveSQL = "CREATE TABLE `license_reserve` ("
."`license_reserve_id` int(10) unsigned NOT NULL auto_increment,"
."`track_license_id` int(10) unsigned NOT NULL default '0',"
."`original_statement_item_id` int(10) unsigned NOT NULL default '0',"
."`product_id` int(10) unsigned NOT NULL default '0',"
."`sale_stat_rate_id` int(10) unsigned NOT NULL default '0',"
."`issue_stat_rate_id` int(10) unsigned NOT NULL default '0',"
."`units` int(10) NOT NULL default '0',"
."`effective_rate` decimal(8,4) unsigned NOT NULL default '0.0000',"
."`periods_remaining` tinyint(3) unsigned NOT NULL default '0',"
."`date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"
."`date_modified` timestamp NOT NULL default '0000-00-00 00:00:00',"
."PRIMARY KEY  (`license_reserve_id`),"
."KEY `track_license_id` (`track_license_id`),"
."KEY `product_id` (`product_id`),"
."KEY `original_statement_item_id` (`original_statement_item_id`)"
.") ENGINE=MyISAM DEFAULT CHARSET=latin1;";

my $artistContractTermReserveSQL = "CREATE TABLE `artist_contract_term_reserve` ("
."`artist_contract_term_reserve_id` int(10) unsigned NOT NULL auto_increment,"
."`artist_contract_term_id` int(10) unsigned NOT NULL default '0',"
."`income_source_id` int(10) unsigned NOT NULL default '0',"
."`region_id` int(10) unsigned NOT NULL default '0',"
."`channel_id` int(10) unsigned NOT NULL default '0',"
."`price_level_id` int(10) unsigned NOT NULL default '0',"
."`original_statement_item_id` int(10) unsigned NOT NULL default '0',"
."`product_id` int(10) unsigned NOT NULL default '0',"
."`product_format_id` int(10) unsigned NOT NULL default '0',"
."`units` int(10) unsigned NOT NULL default '0',"
."`revenue` decimal(16,4) NOT NULL default '0.0000',"
."`price` decimal(16,4) NOT NULL default '0.0000',"
."`revenue_based` tinyint(1) unsigned NOT NULL default '0',"
."`effective_rate` decimal(8,4) unsigned NOT NULL default '0.0000',"
."`periods_remaining` tinyint(3) unsigned NOT NULL default '0',"
."`date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"
."`date_modified` timestamp NOT NULL default '0000-00-00 00:00:00',"
."PRIMARY KEY  (`artist_contract_term_reserve_id`),"
."KEY `artist_contract_term_id` (`artist_contract_term_id`),"
."KEY `original_statement_item_id` (`original_statement_item_id`)"
.") ENGINE=MyISAM DEFAULT CHARSET=latin1;";


my @clientIDs;
push @clientIDs, _getLocalClientIDs();

#foreach my $clientID (@clientIDs) {
foreach my $clientID (14) {
    next unless ($clientID);
    print $clientID."\n";


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

    _convertMechanicalReserves();
    _convertArtistRoyaltyReserves();
}


sub _convertMechanicalReserves
{
    my $dbo = Common::RSApp::GetClientDB();

    # First, we need to read the current reserve table.
    # I don't think I can rely on the DB::Item class to work here.
    # We'll read the entire thing into memory.
    #
    my @oldReserves;
    my $sth = $dbo->DoCmd("SELECT * FROM license_reserve");
    while (my $hr = $sth->fetchrow_hashref())
    {
        push @oldReserves, $hr;
    }

    # Now we drop the table, and re-create it.
    # !!! Actually... let's rename the table so we have a fallback.
    #
    $dbo->DoCmd("RENAME TABLE license_reserve TO old_license_reserve");
    $dbo->DoCmd($licenseReserveSQL);


    # Loop through the old reserves and create appropriate new reserves.
    #
    foreach my $oldReserve (@oldReserves)
    {
        # We need to get the original liquidation schedule.
        #
        my $trackLicenseID = $oldReserve->{track_license_id};
        my $trackLicense = RPS::DB::Item::TrackLicense->Lookup(track_license_id => $trackLicenseID);

        my $reserveID;
        my $reserveType;
        if (RPS::DB::Item::TrackLicense::kPublishingLicense == $trackLicense->type)
        {
            $reserveID = $trackLicenseID;
            $reserveType = RPS::DB::Item::ReserveLiquidation::kTypePublishingLicense;
        }
        elsif (RPS::DB::Item::TrackLicense::kControlledComposition == $trackLicense->type)
        {
            # !!! Pretty weird - Why do we get that from the track id, rather than from the
            # track license?
            # This seems to only be used to fetch the liquidation schedule.
            # !!! Therefore, it's going to be going away anyway.
            #
            $reserveID = _getControlledCompIDFromTrackID($trackLicense->track_id);
            $reserveType = RPS::DB::Item::ReserveLiquidation::kTypeControlledComposition;
        }


        # Get the associated run id(s).
        #
        my @reserveRunIDs;
        my $mapItems = RPS::DB::Item::LicenseReserveRun->GetAll("select * from license_reserve_run where license_reserve_id=".$oldReserve->{license_reserve_id});
        while (my $mapItem = $mapItems->next())
        {
            push @reserveRunIDs, $mapItem->mechanical_run_id;
        }

        my %scheduleTable;
        my $schedule = RPS::DB::Item::ReserveLiquidation->GetByIDType($reserveID, $reserveType);

        while ($schedule->hasNext())
        {
            my $scheduleEntry = $schedule->next();
            $scheduleTable{$scheduleEntry->period} = $scheduleEntry->percent;
        }


        # Now we can finally create the new reserves.
        #
        my $units = $oldReserve->{initial_units};
        for (my $i = 1; $units > 0 && $i <= 8; $i++)
        {
            my $p = $scheduleTable{$i};
            next unless $p;

            my $periodUnits = ceil($oldReserve->{initial_units} * ($p / 100));
            $periodUnits = $units unless $periodUnits < $units;

            $units -= $periodUnits;


            # If we've already done this period, we don't need to create the reserve.
            #
            my $periodsRemaining = 1 + ($i - $oldReserve->{next_period});
            next if $periodsRemaining < 1;


            # Create the new reserve
            #
            my $newReserve = RPS::DB::Item::LicenseReserve->Create
            (
                track_license_id => $oldReserve->{track_license_id},
                original_statement_item_id => $oldReserve->{original_statement_item_id},
                product_id => $oldReserve->{product_id},
                sale_stat_rate_id => $oldReserve->{sale_stat_rate_id},
                issue_stat_rate_id => $oldReserve->{issue_stat_rate_id},
                units => $periodUnits,
                effective_rate => $oldReserve->{effective_rate},
                periods_remaining => $periodsRemaining,
            );

            $newReserve->save();

            # Create a new mapping table entry, too
            #
            foreach my $runID (@reserveRunIDs)
            {
                my $newMap = RPS::DB::Item::LicenseReserveRun->Create(license_reserve_id => $newReserve->license_reserve_id, mechanical_run_id => $runID);
                $newMap->save();
            }
        }
    }

}


sub _convertArtistRoyaltyReserves
{
    my ($clientID) = @_;

    my $dbo = Common::RSApp::GetClientDB();

    # First, we need to read the current reserve table.
    # I don't think I can rely on the DB::Item class to work here.
    # We'll read the entire thing into memory.
    #
    my @oldReserves;
    my $sth = $dbo->DoCmd("SELECT * FROM artist_contract_term_reserve");
    while (my $hr = $sth->fetchrow_hashref())
    {
        push @oldReserves, $hr;
    }

    # Now we drop the table, and re-create it.
    # !!! Actually... let's rename the table so we have a fallback.
    #
    $dbo->DoCmd("RENAME TABLE artist_contract_term_reserve TO old_artist_contract_term_reserve");
    $dbo->DoCmd($artistContractTermReserveSQL);


    # Loop through the old reserves and create appropriate new reserves.
    #
    foreach my $oldReserve (@oldReserves)
    {
        my $termID = $oldReserve->{artist_contract_term_id};

        # Need to fetch the contract id, from the term.
        #
        my $term = RPS::DB::Item::NewArtistContractTerm->Lookup(artist_contract_term_id => $termID);
        my $contract = RPS::DB::Item::NewArtistContract->Lookup(artist_contract_id => $term->artist_contract_id);

        # Get the associated run id(s).
        #
        my @reserveRunIDs;
        my $mapItems = RPS::DB::Item::LicenseReserveRun->GetAll("select * from artist_contract_term_reserve_run where artist_contract_term_reserve_id =".$oldReserve->{artist_contract_term_reserve_id});
        while (my $mapItem = $mapItems->next())
        {
            push @reserveRunIDs, $mapItem->artist_royalty_run_id;
        }

        my %scheduleTable;

        my $schedule = RPS::DB::Item::ReserveLiquidation->GetByIDType($term->artist_contract_id, RPS::DB::Item::ReserveLiquidation::kTypeArtistContract);

        while ($schedule->hasNext())
        {
            my $scheduleEntry = $schedule->next();
            $scheduleTable{$scheduleEntry->period} = $scheduleEntry->percent;
        }


        # Now we can finally create the new reserves.
        # !!! Need to take units and revenue into account.
        #
        my $units = $oldReserve->{initial_units};
        my $revenue = $oldReserve->{initial_revenue};
        for (my $i = 1; ($units > 0 || $revenue > 0) && $i <= 8; $i++)
        {
            my $p = $scheduleTable{$i};
            next unless $p;

            my $periodUnits = ceil($oldReserve->{initial_units} * ($p / 100));
            $periodUnits = $units unless $periodUnits < $units;
            $units -= $periodUnits;

            my $periodRevenue = ceil($oldReserve->{initial_revenue} * ($p / 100));
            $periodRevenue = $revenue unless $periodRevenue < $revenue;
            $revenue -= $periodRevenue;

            # If we've already done this period, we don't need to create the reserve.
            #
            my $periodsRemaining = 1 + ($i - $oldReserve->{next_period});
            next if $periodsRemaining < 1;


            # Create the new reserve
            #
            my $newReserve = RPS::DB::Item::ArtistContractTermReserve->Create
            (
                artist_contract_term_id => $oldReserve->{artist_contract_term_id},
                income_source_id => $oldReserve->{income_source_id},
                region_id => $oldReserve->{region_id},
                channel_id => $oldReserve->{channel_id},
                price_level_id => $oldReserve->{price_level_id},
                original_statement_item_id => $oldReserve->{original_statement_item_id},
                product_id => $oldReserve->{product_id},
                product_format_id => $oldReserve->{product_format_id},
                units => $periodUnits,
                revenue => $periodRevenue,
                price => $oldReserve->{price},
                revenue_based => $oldReserve->{revenue_based},
                effective_rate => $oldReserve->{effective_rate},
                periods_remaining => $periodsRemaining,
            );

            $newReserve->save();

            # Create a new mapping table entry, too
            #
            foreach my $runID (@reserveRunIDs)
            {
                my $newMap = RPS::DB::Item::ArtistContractTermReserveRun->Create(artist_contract_term_reserve_id => $newReserve->artist_contract_term_reserve_id, artist_royalty_run_id => $runID);
                $newMap->save();
            }
        }

    }

}


sub _getLocalClientIDs
{
    # We have a nifty little script that will give us a list of client databases.
    #
    my $clientListString = `/app/tools/rps/bin/db/list_client_dbs.pl`;
    chomp $clientListString;


    # Find the client id that matches each database.
    #
    my @ids;
    my @dbNames = split(/ /, $clientListString);
    foreach my $dbName (@dbNames)
    {
        my $id = Common::RSDB::DBNameToClientID($dbName);
        push @ids, $id;

    }

#    return @ids;
    return 14;
}

sub _getControlledCompIDFromTrackID
{
    my ($track_id) = @_;

    my $track = RPS::DB::Item::Track->Lookup(track_id => $track_id);
    my $albumID = $track->album_id;


    my $albumContract = RPS::DB::Item::AlbumContract->GetCComp($albumID);
    return undef unless $albumContract;
                                        
    my $cc = RPS::DB::Item::ControlledComposition->GetByContractID($albumContract->artist_contract_id);
    return $cc->controlled_composition_id;
}