#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

use JSON::XS;
use FileHandle;
use Text::CSV::Easy(qw/csv_build csv_parse/);

use lib '/app/tools/common/lib';
use Common::Session;
use Common::DB::Export;

my ( $opt, $usage ) = clopt(
    [ 'help|?|h' => "print usage message and exit", { 'implies' => { 'table' => 1 } } ],
    [
        'type' => 'hidden' => {
            'one_of'  => [ [ 'rps|r' => 'RPS tables default' ], [ 'dtmv|d' => 'DTMV tables' ] ],
            'default' => 'rps'
        }
    ],
    [ 'file|f=s'  => 'File list of tables',                 { 'implies'  => { 'table' => 1 } } ],
    [ 'table|t=s' => 'Table or list to create on redshfit', { 'required' => 1 } ],
    [ 'crtab|c'   => 'Create table on reshift cluser' ],
    [ 'dbhost=s'  => 'DB host to get mysql defition - default localhost', { 'default' => 'localhost' } ],
);

if ( $opt->help ) {
    logMessage( 'info', $usage->text, 'options' );
    exit 1;
}

my $schema;
if ( $opt->type eq 'rps' ) {
    $schema = 'C_CURB';
} else {
    $schema = 'BP_MACMILLAN';
}

my $tables;
if ( $opt->file ) {
    my $fh = FileHandle->new( $opt->file, 'r' );
    while (<$fh>) {
        chomp;
        next if m/^-- /;
        push @{$tables}, $_;
    }
    $fh->close;
} elsif ( $opt->table =~ m/,/ ) {
    $tables = [ split ',', $opt->table ];
} else {
    push @{$tables}, $opt->table;
}

foreach my $table ( @{$tables} ) {

    my $export = Common::DB::Export->new($schema);
    my $search = { 'table_schema' => $schema, 'table_name' => $table };

    logMessage( 'info', "Getting col-defs for $schema.$table on " . $opt->dbhost );
    my $dbx  = Common::Session::getdbx( $opt->dbhost );
    my $rset = $dbx->resultset('InformationSchema::Columns')->search( $search, { 'order_by' => 'ordinal_position' } );

    ## convert mysql types to redshift types
    my $cols = [ "    client_id INTEGER,", "   date_loaded TIMESTAMP," ];
    my $i    = 1;
    while ( my $col = $rset->next ) {
        my $type = typemap( $col->data_type );
        $type = defined $type ? $type : $col->column_type;
        $type =~ s/unsigned//i;
        my $name = $col->column_name;
        my $comm = "-- " . $col->data_type;
        if ( $i++ == $rset->count ) {
            push @{$cols}, "    $name $type) $comm";
        } else {
            push @{$cols}, "    $name $type, $comm";
        }
    }

    my $redshift_table = $export->redshift_schema . ".$table";

    my $sqltext = <<"!";

DROP TABLE IF EXISTS $redshift_table;
 
CREATE TABLE $redshift_table (
!
    map { $sqltext .= "$_\n" } @{$cols};

    if ( $opt->crtab ) {

        logMessage( 'info', "Creating $redshift_table" );

        my $fh = FileHandle->new( '/tmp/create.sql', 'w' );
        $fh->print($sqltext);
        $fh->close;

        ## get psql connection vars from .dbconn.yml
        my $dsn = Common::DB::Connect->dsn('redshift');
        my ( $dbname, $host, $port ) = split ';', $dsn->[0];
        $dbname =~ s/.*:/--/;
        $host = "--$host";
        $port = "--$port";

        my $user = $dsn->[1];
        my $pass = $dsn->[2];

        my $cmd = "psql -U $user $host $port $dbname --file /tmp/create.sql";
        logMessage( 'info', "$cmd" );
        $cmd = "PGPASSWORD=$pass $cmd";
        my $out = runcmd($cmd);
        if ( $out->[2] ) {
            logMessage( 'error', $out->[1] );
        }

        #runlogcmd('sudo rm -f /tmp/create.sql');

    }
}

sub typemap {
    my $type = shift;
    $type = uc($type);
    {
        'BIGINT'     => 'DECIMAL(20,0)',
        'BIT'        => 'DECIMAL(20,0)',
        'DATETIME'   => 'TIMESTAMP',
        'DOUBLE'     => 'DOUBLE PRECISION',
        'ENUM'       => 'VARCHAR(255)',
        'FLOAT'      => 'REAL',
        'INT'        => 'BIGINT',
        'LONGTEXT'   => 'VARCHAR(65000)',
        'MEDIUMINT'  => 'INTEGER',
        'MEDIUMTEXT' => 'VARCHAR(65000)',
        'SET'        => 'VARCHAR(4000)',
        'SMALLINT'   => 'INTEGER',
        'TEXT'       => 'VARCHAR(65000)',
        'TIME'       => 'TIMESTAMP',
        'TINYINT'    => 'SMALLINT',
        'TINYTEXT'   => 'VARCHAR(256)',
        'YEAR'       => 'INTEGER'
    }->{$type};
}