#!/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;
}