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