use strict;
use File::Slurp qw(:std);
use IO::Handle;
use File::Path qw(make_path);
use Data::Printer;

my $datei = 'admin.sql';

my @lines = read_file($datei);

write_file('admin2.sql', '');


my %spalten;
my $spaltenbestimmen = 0;
my $inserts = 0;
my $ignoreline = 0;
my $noreturn = 0;

for my $l ( @lines ) {
    $noreturn = 0;
    chomp($l);
    if ( $l =~ /CREATE INDEX|CREATE UNIQUE/ ) {
        $ignoreline = 1;
    }

    if ( $l eq "/" ) {
        $inserts = 0;
    }

    if ( $inserts == 1 ) {
        $l =~ s/" "/""/g;
        $l =~ s/" /"/g;
        $l =~ s/ "/"/g;
        $l =~ s/'/\\'/g;
        $l =~ s/,,,/,NULL,NULL,/g;
        $l =~ s/,,/,NULL,/g;
        $l =~ s/^,"/NULL,"/g;
        $l = $l =~ s/,$//r;
        $l = "($l),";
    }

    $l =~ s/~ME~/Ä/g;
    $l =~ s/~NM~/Ü/g;
    $l =~ s/~NG~/Ö/g;
    $l =~ s/~OE~/ä/g;
    $l =~ s/~PM~/ü/g;
    $l =~ s/~PG~/ö/g;
    $l =~ s/~NP~/ß/g;
    $l =~ s/~ANAK~/\\n/g;
    $l =~ s/ä/ä/g;
    $l =~ s/~LF~/\\/g;
    $l =~ s/ü/ü/g;
    $l =~ s/ö/ö/g;

    $l =~ s/"\\n"/""/g;

# if ( $l =~ /~..~/ ) {
#     p $l;
# }

    $l =~ s/\$long/""/g;

    next if ( $l =~ /SET LOADVERSION|PCTFREE|^\\|~/ );
    next if ( $l eq '/' );


    if ( $l =~ /\$DATATYPES/ ) {
        $l = "VALUES";
        $inserts = 1;
    }

    if ( $l =~ /INSERT INTO/ ) {
        $noreturn = 1;
        $l = $l =~ s/VALUES//r;
        $spaltenbestimmen = 0;
    }

    if ( $spaltenbestimmen > 0 ) {
        if ( $l =~ /\)$/ ) {
            $l = "$l;\n";
        };

        my @sp = split(/ /, $l);

        $spalten{$spaltenbestimmen}= $sp[2];
        $spaltenbestimmen += 1;
    }

    for my $z ( 0 .. 20 ) {
        if ( $l =~ /  :$z/ ) {
            # print "$z  $spalten{$z}\n";
            $noreturn = 1;
            $l = $l =~ s/:$z/$spalten{$z}/r;
        }
    }

    if ( $l =~ /CREATE TABLE/ ) {
        # p $l;
        my $tabname = $l =~ s/CREATE TABLE SYSADM.//r;
        $tabname =~ s/ \(//;

        $l = "DROP TABLE IF EXISTS `$tabname`;\n" . $l;
        p $l;

        $l = $l =~ s/CREATE TABLE/CREATE TABLE IF NOT EXISTS/r;
        $spaltenbestimmen = 1;
        #if ( $l =~ /SYSADM.TEL_ALIASES|SYSADM.DATEIEN|SYSADM.PC_NET|SYSADM.PC_LOGIN|SYSADM.MMAC|SYSADM.PC_FILE|SYSADM.PC_RPRN|SYSADM.INV_INI|SYSADM.PC_KARTEN|SYSADM.HD_LOESUNG|SYSADM.HD_STICHWORT|SYSADM.HD_MELDG|SYSADM.TEL_TELEFON|SYSADM.TEL_NAMEN|SYSADM.PROXYMAILLOG/ ) {
        if ( $l =~ /SYSADM.CX_/ ) {
            if ( $l =~ /CX_IPOLD/ ) {
                $ignoreline = 1;
            } else {
                $ignoreline = 0;
            }
        } else {
            $ignoreline = 1;
        }
    }
    if ( $ignoreline == 1 ) {
        next;
    };

    next if ( $l eq '(//),' );

    $l =~ s/SYSADM.//g;
    append_file("admin2.sql", $l.( $noreturn == 1 ? "": "\n") );
}

my $lin = read_file("admin2.sql");

$lin =~ s/\),\nDROP/\);\n\nDROP/g; #
$lin =~ s/,  OBJ0/,  I2/g;
$lin =~ s/\),$/\);/;

write_file('cx.sql', $lin);

1;