#!/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::TextProgressBar;
use Common::Timer;
use Common::DB::Item::Service;


use lib '/app/tools/rps/lib';
use RPS::DB::Item::MechanicalCarryover;
use RPS::DB::Item::TrackLicense;
use RPS::DB::Item::Track;
use RPS::DB::Item::TrackContract;
use RPS::DB::Item::Master;
use RPS::DB::Item::Album;
#use RPS::DB::Item::License;
use RPS::DB::Item::LicenseReserve;
use RPS::DB::Item::LicenseReserveLiquidation;
use RPS::DB::Item::StatRate;
use RPS::DB::Item::ProductTrack;
use RPS::DB::Item::Song;

use RPS::DB::Item::Publisher;
use RPS::DB::Item::Product;
use RPS::DB::Item::ControlledComposition;

use RPS::DB::Item::AlbumContract;
use RPS::DB::Item::MechanicalRun;
use RPS::DB::Item::MechanicalStatement;
use RPS::DB::Item::MechanicalStatementItem;
use RPS::DB::Item::MechanicalStatementItem;
use RPS::DB::Item::MechanicalStatementTrack;
use RPS::DB::Item::MechanicalStatementLicense;
use RPS::DB::Item::MechanicalStatementAdjustmentItem;
use RPS::DB::Item::SaleMechanicalStatementItemMap;

use RPS::License::TrackLicense;
use RPS::License::TrackLicenseList;
use RPS::DB::Item::NewArtistContract;
use RPS::DB::Item::ReserveLiquidation;


# INTEGRATION
#
# Adding some new fields.
# Plus, we're taking the 'client_album_id' field from raptor and adding it.
#

my $gVerbosityLevel = 1;

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


my $clientAlbumIDMap = _readClientAlbumIDFromRaptor($options{origClientID});

_alterAlbum($options{clientID}, $clientAlbumIDMap);


sub _readClientAlbumIDFromRaptor
{
    my ($raptorClientID) = @_;
    assert($raptorClientID);

    my %map;
    my $appSingleton = Common::RSApp->new(clientID => $raptorClientID);

    # Can't use a fancy db abstraction layer safely, so we'll query this the old fashioned way
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "select album_id, client_album_id, company_id, vendor_id, location_id from album";
    my $sth = $dbo->DoCmd($sql);
    while (my $hr = $sth->fetchrow_hashref())
    {
        $map{$hr->{album_id}} = 
        {
            clientAlbumID => $hr->{client_album_id},
            companyID => $hr->{company_id},
            vendorID => $hr->{vendor_id},
            locationID => $hr->{location_id},
        }
    }

    return \%map;
}

sub _alterAlbum
{
    my ($clientID, $clientAlbumIDMap) = @_;
    assert($clientID);
    assert($clientAlbumIDMap);

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

    # Add the client_album_id column
    #
    my $dbo = Common::RSApp::GetClientDB();
    my $sql = "ALTER TABLE album ADD COLUMN client_album_id varchar(50)";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN company_id varchar(50)";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN vendor_id varchar(50)";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN location_id varchar(50)";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN title_clean varchar(255)";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }

    $sql = 'ALTER TABLE album ADD KEY `client_album_id` (`client_album_id`)';
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }
    $sql = 'ALTER TABLE album ADD KEY `title_clean` (`title_clean`)';
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }

    # Add the custom columns
    #
    $sql = "ALTER TABLE album ADD COLUMN custom_1 varchar(255)";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN custom_2 varchar(255)";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }
    $sql = "ALTER TABLE album ADD COLUMN custom_3 varchar(255)";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }


    # I need to set the title_clean field.   To insure consistency, I'll derive it
    # from the current title.
    #
    $sql = "SELECT album_id, title from album";
    my $sth = $dbo->DoCmd($sql);
    while (my $hr = $sth->fetchrow_hashref)
    {
        $clientAlbumIDMap->{$hr->{album_id}}{titleClean} = Common::Utils::clean_name($hr->{title});
    }

    while (my ($albumID, $raptorData) = each(%$clientAlbumIDMap))
    {
        my $clientAlbumID = $raptorData->{clientAlbumID};
        my $companyID = $raptorData->{companyID};
        my $vendorID = $raptorData->{vendorID};
        my $locationID = $raptorData->{locationID};
        my $upcAlt = $raptorData->{upcAlt};
        my $titleClean = $raptorData->{titleClean};

        $sql = "UPDATE album SET client_album_id = " . $dbo->DBQuote($clientAlbumID) 
             . ", company_id = " . $dbo->DBQuote($companyID)
             . ", vendor_id = " . $dbo->DBQuote($vendorID)
             . ", location_id = " . $dbo->DBQuote($locationID)
             . ", title_clean = " . $dbo->DBQuote($titleClean)
             . " WHERE album_id = " . $albumID;
        if (!$dbo->DoCmd($sql))
        {
            _report("error " . $dbo->LastErrorStr());
            return;
        }
    }
}


sub _alterProduct
{
    _report("altering product table");

    # First, we'll add the new column.
    #
    _report("adding asset_id column");

	my $dbo = Common::RSApp::GetClientDB();
	my $sql = "ALTER TABLE product ADD COLUMN asset_id int(10) unsigned default NULL";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }


    # Now we'll move through the products, setting asset_id to the proper value.
    #
    my $productList = RPS::DB::Item::Product->GetAll();
    _report("updating " . $productList->size() . " products");
    while (my $product = $productList->next())
    {
        if (RPS::DB::Item::Product::kProductTypeDigitalTrack == $product->product_type_id)
        {
            $product->asset_id($product->track_id);
        }
        else
        {
            $product->asset_id($product->album_id);
        }
        $product->save();
    }


    # Last, drop track_id and album_id
    #
    _report("dropping album_id");
    $sql = "ALTER TABLE product DROP COLUMN album_id";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }


    _report("dropping track_id");
    $sql = "ALTER TABLE product DROP COLUMN track_id";
    if (!$dbo->DoCmd($sql))
    {
        _report("error " . $dbo->LastErrorStr());
        return;
    }

    _report("product table has been altered");
}

sub _usage
{
    print "\nusage: $0 -o original client_id -c client_id [-V]\n";
    print "\n";
    print "Arguments:\n";
    print "\t-o <client_id>\t\tThe client_id of the Raptor client (from which we'll copy some stuff)\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('o:c:V:', \%opt);

    if (! $opt{c})
    {
        _usage();
        exit(1);
    }
    $settings->{clientID} = $opt{c};
    if (! $opt{o})
    {
        _usage();
        exit(1);
    }
    $settings->{origClientID} = $opt{o};

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