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

my $schemaFile     = shift @ARGV;
my $constraintFile = shift @ARGV;

my $tables;
my $sfh = FileHandle->new( $schemaFile, 'r' );
while (<$sfh>) {
    ##chomp;
    next if $. == 1;
    my ( $owner, $table, $column, $type, $len, $precision, $scale ) = csv_parse( lc($_) );

    $type = translate_type($type);
    $len  = $precision if $type eq 'integer';

    if ( $type eq 'integer' and defined $precision and defined $scale ) {
        if ( $precision ne '' and $scale ne '' ) {
            $type = 'decimal';
        }
    } elsif ( $type eq 'integer' and defined $precision and $precision ne "" ) {
        if ( $precision > 10 ) {
            $type = 'bigint';
        } elsif ( $precision < 5 ) {
            $type = 'smallint';
        }
    }

    if ( $type eq 'integer' and defined $precision and $precision eq "" ) {
        $len       = 10;
        $precision = 10;
    }

    if ( $type eq 'varchar' and defined $len and $len > 3000 ) {
        $type = 'text';
    }

    $tables->{$table}->{$column} = [ $type, $len, $precision, $scale ];
    push @{ $tables->{$table}->{'_ordinal'} }, $column;

}
$sfh->close;

my $cfh = FileHandle->new( $constraintFile, 'r' );
while (<$cfh>) {
    next if $. == 1;
    my ( $ctype, $cname, $table, $col, $reftab, $refcol, $def1, $def2 ) = csv_parse( lc($_) );
    if ( $ctype eq 'pk' ) {
        push @{ $tables->{$table}->{'_constraints'}->{'pk'} }, $col;
    } elsif ( $ctype eq 'fk' ) {
        next if $table eq 'right_types';
        my $key = "$cname:$reftab:$refcol";
        push @{ $tables->{$table}->{'_constraints'}->{'fk'}->{$key} }, $col;
    }
}
$cfh->close;

## parse constraints;
foreach my $tab ( keys %{$tables} ) {
    my $cons = defined $tables->{$tab}->{'_constraints'} ? $tables->{$tab}->{'_constraints'} : undef;
    my $_cons;
    if ( defined $cons->{'pk'} ) {
        my $id = join ',', @{ $cons->{'pk'} };
        push @{$_cons}, "primary key ($id)";
    }
    if ( defined $cons->{'fk'} ) {
        foreach my $key ( keys %{ $cons->{'fk'} } ) {
            my ( $cname, $reftab, $refcol ) = split ":", $key;
            my $i = 1;
            foreach my $col ( @{ $cons->{'fk'}->{$key} } ) {
                my $name = scalar @{ $cons->{'fk'}->{$key} } > 1 ? $cname . sprintf( "_%02d", $i++ ) : $cname;
                push @{$_cons}, "CONSTRAINT $name FOREIGN KEY ($col) REFERENCES $reftab ($refcol) ON DELETE CASCADE ON UPDATE CASCADE";
            }
        }
    }
    $tables->{$tab}->{'_constraints'} = $_cons if defined $_cons;
}

print "SET foreign_key_checks = 0;\n\n";

foreach my $table ( sort keys %{$tables} ) {
    my $cols = $tables->{$table}->{'_ordinal'};
    my $sql  = "drop table if exists $table;\n\n";
    $sql .= "create table $table (\n";
    foreach my $col ( @{$cols} ) {
        my $coldef = $tables->{$table}->{$col};
        $sql .= "    `$col` $coldef->[0]";
        if ( $coldef->[0] =~ m/text|blob|datetime|date/i ) {
            $sql .= ",\n";
        } else {
            if ( $coldef->[0] eq 'decimal' ) {
                $sql .= " ($coldef->[1],$coldef->[3])";
            } else {
                $sql .= " ($coldef->[1])";
            }
        	$sql .= ",\n";
        }
    }
    if ( defined $tables->{$table}->{'_constraints'} ) {
        $sql .= join ",\n", map { "    " . lc($_) } @{ $tables->{$table}->{'_constraints'} };
    } else {
        $sql =~ s/,$//;
    }
    $sql .= "\n);\n\n";
    print "$sql";
}

sub translate_type {
    my $type = shift;
    {
        'BINARY_DOUBLE'               => 'binary',
        'BLOB'                        => 'blob',
        'CHAR'                        => 'char',
        'CLOB'                        => 'text',
        'DATE'                        => 'date',
        'NUMBER'                      => 'integer',
        'TIMESTAMP(6) WITH TIME ZONE' => 'datetime',
        'TIMESTAMP(6)'                => 'datetime',
        'VARCHAR2'                    => 'varchar'
    }->{ uc($type) };
}