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