#!/usr/bin/perl
use strict;

use Data::Dumper;
use Getopt::Std;
use POSIX qw(ceil);
use POSIX ":sys_wait_h";
use Sys::Hostname;
use Carp;

use lib '/app/tools/common/lib';
use Common::DB::Item;
use Common::Assert;
use Common::Util;

use lib '/app/tools/rps/lib';
use RPS::DB::Item::FinanceAccount;
use RPS::DB::Item::PublisherAccount;
use RPS::DB::Item::FinanceTransaction;
use RPS::DB::Item::ArtistPayeeAccount;
use RPS::DB::Item::ArtistPayee;

my $gVerbosityLevel = 1;

# Parse the command-line options.
#
my %options;
_parseCommandLine( \%options );

my @clientIDs;
if ( $options{clientID} ) {
    push @clientIDs, $options{clientID};
} else {

    #    @clientIDs = _getAllAvailableClientIDs();
}

foreach my $clientID (@clientIDs) {
    my $appSingleton = Common::RSApp->new( clientID => $clientID );

    _createAlert();
    _alterFinanceTransaction();
    _dropAlbumContractAdvance();
    _dropTrackContractAdvance();
    _createArtistPayeeAccount();
    _alterArtistPayee();
    _alterTrackLicense();
    _alterPublisher();
    _alterArtistRoyaltyAlbumBalanceAccount();
}

sub _alterFinanceTransaction {
    _report("adding transaction_date to finance_transaction table");

    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd("alter table finance_transaction add column `transaction_date` date default NULL");

}

sub _alterArtistRoyaltyAlbumBalanceAccount {
    _report("adding payor_id to artist_royalty_album_balance_account");

    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd("alter table artist_royalty_album_balance_account add column `payor_id` int(10) unsigned NOT NULL default '0'");
    $dbo->DoCmd("alter table artist_royalty_album_balance_account add key `payor_id` (`payor_id`)");

    $dbo->DoCmd("update artist_royalty_album_balance_account set payor_id=1");
}

sub _dropAlbumContractAdvance {
    _report("dropping album_contract_advance table");
    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd('drop table album_contract_advance');
}

sub _dropTrackContractAdvance {
    _report("dropping track_contract_advance table");
    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd('drop table track_contract_advance');
}

sub _createAlert {
    my $sql =
        "CREATE TABLE `alert` ("
      . "`alert_id` int(10) unsigned NOT NULL auto_increment,"
      . "`message` text,"
      . "`alert_level` tinyint(3) unsigned NOT NULL default '1',"
      . "`date_created` timestamp NOT NULL default '0000-00-00 00:00:00',"
      . "`created_by` int(10) unsigned NOT NULL default '0',"
      . "`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"
      . "`modified_by` int(10) unsigned NOT NULL default '0',"
      . "PRIMARY KEY  (`alert_id`))";

    _report("creating alert table");
    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd($sql);
}

sub _createArtistPayeeAccount {

    # First, create the table.
    #
    my $sql =
        "CREATE TABLE `artist_payee_account` ("
      . "`artist_payee_id` int(10) unsigned NOT NULL default '0',"
      . "`payor_id` int(10) unsigned NOT NULL default '0',"
      . "`finance_account_id` int(10) unsigned NOT NULL default '0',"
      . "`min_payment` decimal(16,4) NOT NULL default '0.0000',"
      . "`date_created` timestamp NOT NULL default '0000-00-00 00:00:00',"
      . "`created_by` int(10) unsigned NOT NULL default '0',"
      . "`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"
      . "`modified_by` int(10) unsigned NOT NULL default '0',"
      . "KEY `artist_payee_id` (`artist_payee_id`),"
      . "KEY `payor_id` (`payor_id`))";

    _report("creating artist_payee_account table");
    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd($sql);

    # Now populate this table.
    #
    _report("populating artist_payee_account table");
    $sql = "SELECT artist_payee_id,finance_account_id,opening_balance from artist_payee";
    my $sth = $dbo->DoCmd($sql);
    while ( my $hr = $sth->fetchrow_hashref ) {

        # If this artist already has an account, we're good to go.
        #
        my $accountID = $hr->{finance_account_id};
        if ( !$accountID ) {

            # Do we wish to have an opening balance?
            #
            if ( $hr->{opening_balance} > 0 ) {
                _report( "... creating a new account for artist_payee " . $hr->{artist_payee_id}, 2 );
                my $newAccount = RPS::DB::Item::FinanceAccount->Create(
                    description => 'account for artist payee ' . $hr->{artist_payee_id},
                    type_code   => RPS::DB::Item::FinanceAccount::kAccountTypeAdvance,
                );
                $newAccount->save();
                $accountID = $newAccount->finance_account_id;

                _report( "..... creating opening balance of " . $hr->{opening_balance}, 2 );
                my $newTransaction = RPS::DB::Item::FinanceTransaction->Create(
                    finance_account_id => $accountID,
                    amount             => $hr->{opening_balance},
                    type_code          => RPS::DB::Item::FinanceTransaction::kTypeOpeningBalance,
                    memo               => "opening balance",
                );
                $newTransaction->save();
            }
        }

        #  !!! Don't create a mapping if they don't have an account...
        #
        if ($accountID) {
            _report( "... creating artist_payee_account entry for artist_payee " . $hr->{artist_payee_id}, 2 );
            my %createHash = (
                artist_payee_id    => $hr->{artist_payee_id},
                finance_account_id => $hr->{finance_account_id},
                payor_id           => 1,                           # hard coded to the default
            );
            if ( $hr->{min_payment} ) {
                $createHash{min_payment} = $hr->{min_payment};
            }
            my $newPayeeAccount = RPS::DB::Item::ArtistPayeeAccount->Create(%createHash);
            $newPayeeAccount->save();
        }
    }
}

sub _alterArtistPayee {
    my $dbo = Common::RSApp::GetClientDB();
    $dbo->DoCmd("ALTER TABLE artist_payee drop column opening_balance");
    $dbo->DoCmd("ALTER TABLE artist_payee drop column finance_account_id");
    $dbo->DoCmd("ALTER TABLE artist_payee drop column min_payment");
}

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

    # Make sure all the advance balances are represented in the transaction table someplace.
    #
    _report("making sure all track_licenses with advances have accounts...");
    my $sth = $dbo->DoCmd("select track_license_id,advance_account_id,advance_amount from track_license");
    while ( my $hr = $sth->fetchrow_hashref() ) {

        # presuming non-negative initial advances.
        #
        if ( !$hr->{advance_account_id} && $hr->{advance_amount} > 0 ) {

            # Create an advance account, set up the initial balance.
            #
            _report( "... creating a new account for track_license" . $hr->{track_license_id}, 2 );
            my $newAccount = RPS::DB::Item::FinanceAccount->Create(
                description => 'account for track_license' . $hr->{track_license_id},
                type_code   => RPS::DB::Item::FinanceAccount::kAccountTypeAdvance,
            );
            $newAccount->save();
            my $accountID = $newAccount->finance_account_id;

            _report( "..... creating opening balance of " . $hr->{advance_amount}, 2 );
            my $newTransaction = RPS::DB::Item::FinanceTransaction->Create(
                finance_account_id => $accountID,
                amount             => $hr->{advance_amount},
                type_code          => RPS::DB::Item::FinanceTransaction::kTypeOpeningBalance,
                memo               => "opening balance",
            );
            $newTransaction->save();
        }
    }

    _report("altering track_license table");
    $dbo->DoCmd("alter table track_license drop column advance_amount");
    $dbo->DoCmd("alter table track_license change column advance_account_id finance_account_id int(10) unsigned");
}

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

    _report("altering publisher table");

    # Create the new publisher_account table.
    #
    my $sql =
        "CREATE TABLE `publisher_account` ("
      . "`payor_id` int(10) unsigned NOT NULL default '0',"
      . "`publisher_id` int(10) unsigned NOT NULL default '0',"
      . "`finance_account_id` int(10) unsigned default NULL,"
      . "`min_payment` decimal(16,4) NOT NULL default '0.0000',"
      . "`date_created` timestamp NOT NULL default '0000-00-00 00:00:00',"
      . "`created_by` int(10) unsigned NOT NULL default '0',"
      . "`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,"
      . "`modified_by` int(10) unsigned NOT NULL default '0',"
      . "KEY `publisher_id` (`publisher_id`),"
      . "KEY `payor_id` (`payor_id`))";
    $dbo->DoCmd($sql);

    # So...  the goal here is to combine the finance_account and the advance_account.
    # The 'advance_account_id' in Publisher is already deprecated, as far as I can tell.
    #
    my $sth = $dbo->DoCmd("select * from publisher");
    while ( my $hr = $sth->fetchrow_hashref() ) {
        my $publisherID = $hr->{publisher_id};

        _report( "  publisher $publisherID", 2 );

        # First, is there a publisher_advance record for this guy?
        # If there is, then create a corresponding publisher_account record.
        #
        my $advanceID;
        my $sth2 = $dbo->DoCmd("select * from publisher_advance where publisher_id=$publisherID");
        my $hr2  = $sth2->fetchrow_hashref();

        my $advanceAccountID = $hr2->{advance_account_id};
        my $financeAccountID = $hr->{finance_account_id};
        my $minPayment       = $hr->{min_payment};
        $minPayment = 0 unless $minPayment;

        # Do we have any reason to mess with the publisher_account table?
        #
        if ( $advanceAccountID || $financeAccountID || $minPayment > 0 ) {

            # If they have a finance account, we'll just use that.
            # Otherwise, create a new one.
            #
            my $financeAccount;
            if ( !$financeAccountID ) {
                _report( "    creating a new finance account", 2 );
                $financeAccount = RPS::DB::Item::FinanceAccount->Create(
                    description => "account for publisher $publisherID payor 1",
                    type_code   => RPS::DB::Item::FinanceAccount::kAccountTypeAdvance,
                );
                $financeAccount->save();
                $financeAccountID = $financeAccount->finance_account_id;
            }

            # Now, if they have a publisher_advance record, we'll post transactions
            # from that to the (possibly new) account.
            #
            if ($advanceAccountID) {
                _report( "    moving transactions from advance account to finance account", 2 );
                my $advanceTransactions =
                  RPS::DB::Item::FinanceTransaction::GetAccountTransactions( finance_account_id => $advanceAccountID );
                while ( my $advanceTransaction = $advanceTransactions->next() ) {
                    my $newTransaction = RPS::DB::Item::FinanceTransaction->Create( finance_account_id => $financeAccountID );

                    # Note that this old 'advance account' had the sign flipped...
                    #
                    $newTransaction->amount( -1 * $advanceTransaction->amount );
                    $newTransaction->memo( $advanceTransaction->memo );
                    $newTransaction->save();
                }
            }

            # Now we can create the publisher_account record.
            #
            my $publisherAccount = RPS::DB::Item::PublisherAccount->Create(
                payor_id           => 1,                   # using default payor
                publisher_id       => $publisherID,
                finance_account_id => $financeAccountID,
                min_payment        => $minPayment,
            );
            $publisherAccount->save();
        }
    }
}

# =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

sub _usage {
    print "\nusage: $0 -c client_id [-V]\n";
    print "\n";
    print "Arguments:\n";
    print "\t-c <client_id>\t\tThe client_id of the client to process\n";
    print "\t-V <N>\t\t\tVerbosity level - 0 means no output\n";
}

sub _report {
    my ( $string, $verbosity ) = @_;
    $verbosity = 1 unless defined $verbosity;

    if ( $gVerbosityLevel >= $verbosity ) {
        print STDERR $string . "\n";
    }
}

# Pass dates in 'yyyy-mm-dd' format.
#
sub _parseCommandLine {
    my ($settings) = @_;

    my %opt;
    getopts( 'dc:V:', \%opt );

    if ( $opt{d} ) {
        $settings->{deleteFirst} = 1;
    }

    if ( $opt{c} ) {
        $settings->{clientID} = $opt{c};
    }

    if ( defined $opt{V} ) {
        $gVerbosityLevel = $opt{V};
    }
}