#!/usr/bin/perl

use strict;
use warnings;

use FileHandle;
use Getopt::Long::Descriptive(qw/describe_options/);

use lib '/app/tools/common/lib';
use Common::Consts;
use Common::Session;
use Common::RSApp;
use Common::RunCommand;
use Common::Util qw(trimspaces clean_name_catalog);

my ( $opt, $usage ) = getOptions();
my $dbName          = $opt->dbname;
my $serverID        = lc $opt->serverid if defined $opt->serverid;
my $clientID        = $opt->clientid;
my $clientName      = $opt->clientname;
my $clientNameClean = lc $opt->clientnameclean;
my $typeMask        = $opt->typemask;
my $countryCode     = uc $opt->countrycode;
my $languageCode    = lc $opt->languagecode;
my $payor           = $opt->payor;
my $labelName       = $opt->labelname;

## make these mysql friendly
$payor =~ s/'/'"'"'/g if defined $payor;

my $labelNameClean;
if ($labelName) {
    $labelNameClean = clean_name_catalog($labelName);
    $labelNameClean =~ s/'/\\'/g;
    $labelName =~ s/'/'"'"'/g;
}

my $productionBool = $opt->productionflag;

# dbmaster\RSCOMMON>select client_type_id,pow(2,(client_type_id-1)) as mask, description from client_type;
# +----------------+------+-----------------------+
# | client_type_id | mask | description           |
# +----------------+------+-----------------------+
# |              1 |    1 | Digital Advantage     |
# |              2 |    2 | Artist Royalties      |
# |              3 |    4 | Label                 |
# |              4 |    8 | Distribution          |
# |              5 |   16 | US Mechanicals        |
# |              6 |   32 | UK Mechanicals        |
# |              7 |   64 | CA Mechanicals        |
# |              8 |  128 | Book Publishing       |
# |              9 |  256 | Sale Price Validation |  ** INVALID
# |             10 |  512 | Product Monitoring    |  ** INVALID
# |             11 | 1024 | Payee Portal          |
# |             12 | 2048 | Alternate Conversion  |  ** INVALID
# +----------------+------+-----------------------+
if ( !( $typeMask && $typeMask >= 1 && ( $typeMask & 2048 ) == 0 && ( $typeMask & 512 ) == 0 && ( $typeMask & 256 ) == 0 ) ) {
    displayUsage("Missing/invalid type mask!");
}

if ( !$Common::Consts::CODE_TO_COUNTRY{$countryCode} ) {
    displayUsage("Missing/invalid country code!");
}

if ($productionBool) {
    if ( !( $serverID && $serverID =~ /db01/i ) ) {
        displayUsage("Production flag passed but missing/invalid server ID [$serverID]!");
    }
}

my $serverAlias;
if ( defined $serverID ) {
    $serverAlias = {
        'db01' => 'db01'
    }->{$serverID};
} else {
    my $envDBFile = '/var/run/royaltyshare/envdb';
    if ( -e $envDBFile ) {
        $serverAlias = `cat $envDBFile | tr -d '\n'`;
    } else {
        $serverAlias = 'localhost';
    }
}

## username/password from RSDB - not hard coded if/when we change it
## client 0 is RSCOMMON i.e. admin DB for production
my $username  = $Common::RSDB::CLIENT_DB{0}->{'username'};
my $password  = $Common::RSDB::CLIENT_DB{0}->{'password'};
my $dbConnect = "MYSQL_PWD=$password mysql -u $username -v";
my $dbString  = "$dbConnect -h $serverAlias";

my $dbStringName = "$dbString -D $dbName";

my $createDir = '/app/tools/rps/sql/create';

#Array of commands to be executed in order on command line
my @commands;

#create database
push @commands, "$dbString -e 'CREATE DATABASE $dbName CHARACTER SET utf8 COLLATE utf8_general_ci'";

foreach my $sqlScript ( ( qw{
        RPS_CLIENT_DB.sql
        default_rows/channel_default_rows.sql
        default_rows/deduction_type_default_rows.sql
        default_rows/label_default_rows.sql
        default_rows/payor_default_rows.sql
        default_rows/region_default_rows.sql
        default_rows/client_options_default_rows.sql
        }
    )
  ) {
    push @commands, "$dbStringName < $createDir/$sqlScript";
}

## label always has a default so we'll always update it
my $cmd = $dbStringName . " -e 'UPDATE label SET label_name=\"$labelName\", label_name_clean=\"$labelNameClean\" WHERE label_id=1'";
push @commands, $cmd;

## set payor
if ( defined $payor ) {
    my $cmd = $dbStringName . " -e 'UPDATE payor SET name=\"$payor\",country_code=\"$countryCode\" WHERE payor_id=1'";
    push @commands, $cmd;
}

if ( $opt->execute ) {
    logMessage( 'info', "Running in exec mode!" );
}

my @opts = join ', ', map { "--$_ = '$opt->{$_}'" } keys %{$opt};
logMessage( 'info', "Running: $0 @opts" );

my $fh;
if ( $opt->execute and not $opt->nolog ) {
    my $logFileName = '/tmp/' . $dbName . '.log';
    logMessage( 'info', "Logging all DB output to $logFileName" );
    $fh = FileHandle->new( $logFileName, 'w' );

}

foreach my $command (@commands) {
    runCommand( $fh, $command );
}

## setup INSERT statement for dev or prod
my $clientArgs = {
    'client_id'         => $clientID,
    'client_name'       => $clientName,
    'display_name'      => $clientName,
    'client_name_clean' => $clientNameClean,
    'type_mask'         => $typeMask,
    'country_code'      => $countryCode,
    'language_code'     => $languageCode,
};

## need DBH for quote()
my $appSingleton = Common::RSApp->new( clientID => 0 );
my $cdbo         = Common::RSApp::GetCommonDB();
my $dbh          = $cdbo->DBH;

my ( $cols, $vals ) = ( undef, undef );
foreach my $col ( keys %{$clientArgs} ) {
    push @{$cols}, $col;
    push @{$vals}, $dbh->quote( $clientArgs->{$col} );
}
$cols = join ',', @{$cols};
$vals = join ',', @{$vals};
my $insert = "INSERT INTO client ($cols,main_page,date_created) VALUES ($vals,'/rps/main',NOW())";

## production uses dbmaster.RSCOMMON
my $insertConnect = "$dbConnect -D RSCOMMON";
$insertConnect .= " -h " . ( $productionBool ? 'dbmaster' : $serverAlias );
runCommand( $fh, "$insertConnect -e \"$insert\"" );

sub displayUsage {
    my $message = shift;
    logMessage( 'info', $usage->text, 'options' );
    if ($message) {
        logMessage( 'fatal', $message );
        exit 1;
    }
}

sub runCommand {
    my $fileHandle = shift;
    my $command    = shift;
    if ( !$opt->execute ) {
        logMessage( 'info', "Non-exec command: $command" );
    } else {
        my $output = Common::RunCommand::execute($command);
        foreach my $line ( split "\n", $output->[0] ) {
            print STDERR "$line\n" if $opt->verbose;
            print $fileHandle "$line\n" if defined $fh;
        }
        if ( $output->[2] ) {
            my $err = $output->[1];
            logMessage( 'fatal', $err );
            print $fileHandle "$err\n" if defined $fh;
            exit 1;
        }
    }
}

sub getOptions {
    my $desc = optDescription();
    my ( $opt, $usage ) = describe_options(
        'my-program %o <some-arg>',
        [ 'dbName|d=s'          => $desc->{'dbName'},              { 'required'     => 1 } ],
        [ 'execute|x'           => $desc->{'execute'},             { 'default'      => 0 } ],
        [ 'countryCode|o=s'     => $desc->{'countryCode'},         { 'required'     => 1 } ],
        [ 'clientId|c=i'        => $desc->{'clientId'},            { 'required'     => 1 } ],
        [ 'clientName|n=s'      => $desc->{'clientName'},          { 'required'     => 1 } ],
        [ 'clientNameClean|e=s' => $desc->{'clientNameClean'},     { 'required'     => 1 } ],
        [ 'labelName|L=s'       => $desc->{'labelName'},           { 'default'      => 'Default Label' } ],
        [ 'languageCode|l=s'    => $desc->{'languageCode'},        { 'required'     => 1 } ],
        [ 'noLog|N=s'           => $desc->{'noLog'},               { 'default'      => 0 } ],
        [ 'payor|y=s'           => $desc->{'payor'} ],
        [ 'productionFlag|p'    => $desc->{'productionFlag'},      { 'default'      => 0 } ],
        [ 'serverId|s=s'        => $desc->{'serverId'} ],
        [ 'typeMask|m=s'        => $desc->{'typeMask'},            { 'required'     => 1 } ],
        [ 'verbose|v'           => $desc->{'productionFlag'},      { 'default'      => 0 } ],
        [ 'help|h|?'            => "Print usage message and exit", { 'shortcircuit' => 1 } ]
    );
    if ( $opt->help ) {
		my $text = $usage->text;
		$text =~ s/my-program/$0/;
        logMessage( 'info', $text );
        exit 1;
    }
    ( $opt, $usage );
}

sub optDescription {
    {
        'dbName'          => "REQ: C_DATABASE_NAME (which loosely matches the client's site URL/name)",
        'clientId'        => "REQ: Valid numerical client ID (non-pre-existing in database)",
        'clientName'      => "REQ: Client's full name with punctuation/spaces escaped",
        'clientNameClean' => "REQ: Client's name which matches the client site URL exactly",
        'countryCode'     => (
qq{REQ: Two letter ISO country code for client's country of origin. The country code determines the client's currency as well. For instance, all clients with country code of 'GB' (great britain) will have a currency code of 'GBP' (british pounds sterling)
}
        ),
        'languageCode' => "REQ: Language code for client, usually 'en' even for non US/UK clients.",
        'typeMask'     => (
qq{REQ: Value used to indicate activated services, add up values associated with services to get final value. 1 - dsm (all clients get this) 2 - artist royalties 4 - label royalties 8 - content management/distribution 16 - US mechanicals 32 - UK Mechanicals 64 - CA Mechanicals 1024 - Payee Portal Example: A client with label royalties and artist royalties would have a type mask of 7
}
        ),
        'serverId' => "OPT: RPS01..06 depending on which site client is set up on (only required when running in production mod)",
        'payor'    => (
qq{OPT: parameter to set the name of the default payor in the client's payor table. Spaces in name must be escaped!! (e.g. Music Inc.)}
        ),
        'labelName' => (
qq{OPT: parameter to set the name of the default label in the client's label table. Spaces in name must be escaped!! (e.g. Music Inc.) If omitted, the default label will be named 'Default Label'.
}
        ),
        'noLog'          => 'Don\'t create a /tmp/dbName.log file',
        'productionFlag' => (
qq{OPT: Flag to indicate if site creation is being done on a dev box or on a production site (RPS01/RPS02). If production mode is turned on then the 'server ID' parameter is required
}
        ),
        'execute' => "OPT: run script in exec mode and actually create the databases and the client entry on RSCOMMON",
        'verbose' => "OPT: Show all DB output from create scripts"
    };
}