#!/usr/bin/perl -w
use strict;
$|=1;

my $opt_d = 'dbo';
my $opt_v = 0;

my $opt_e = 'MyISAM';
my $opt_c = 'utf8';

my ($opt_D, $opt_T, $opt_I, $opt_C,);
use Getopt::Long qw(:config no_ignore_case bundling);
GetOptions(
	'T'   => \$opt_T, # tables-only mode, exit on INSERT
	'I'   => \$opt_I, # tables-only mode, ignore INSERT
	'D'   => \$opt_D, # generate full drops
	'C'   => \$opt_C, # generate commits
	'v=i' => \$opt_v, # verbose
	'd=s' => \$opt_d, # db name
	'e=s' => \$opt_e, # mysql engine
	'c=s' => \$opt_c, # mysql charset 
	);

$opt_d .= "." if $opt_d;

my $state='idle';
my @stack = ();
my $curtable = "";
while (<>) {
#	chomp;
	s/[\n\r]+$//g; # fucking DOS 
	unless ($state =~ /^ins[23]$/) {
		s/\s+/ /g;
		s/(^ | $)//g;
		next unless $_;
	}

	printf STDERR "STATE: %s -> %s -> ", $state, $_ if $opt_v>8;

# DATE fixups
 	if ('ins2' eq $state) {
		#'12/13/2005 6:18:00 PM'
		# '1/1/1900'
		my $q = "'";
		s/$q([1-9]|1[0-2])\/([1-9]|[12]\d|3[01])\/(\d{4})([$q ])/sprintf("'%04i-%02i-%02i%s",$3,$1,$2,$4)/eg;
		s/ (\d|1[01])(:[0-5]\d:[0-5]\d) ([AP]M)([$q])/sprintf(" %02i%s'",$1+($3 eq 'PM' ? 12 : 0),$2,$3)/eg; 
	}

	if (/^--( |$)/ ||
		/^\s*\/\*.*\*\/\s*$/) {
		printf STDERR "DBG: %s/comment '%s'\n", $state, $_ if $opt_v>5;

	} elsif ('idle' eq $state && /^EXEC .* N'CREATE ([^']+)$/) {
		printf STDERR "DBG: %s/create '%s'\n", $state, $1 if $opt_v>1;
		$state = 'creating';
	} elsif ('creating' eq $state) {
		$state = 'idle' if /(^|[^'])'([^']|$)/;

	} elsif ('idle' eq $state && /^(IF|DROP|BEGIN|END|EXEC|SET|CREATE ROLE|print .*)(\s|$)/) {
		printf STDERR "DBG: %s/prepfluff '%s'\n", $state, $_ if $opt_v>5;

## TABLES
	} elsif ('idle' eq $state && /^CREATE TABLE (\[?dbo\]?\.)?\[?(\w+)\]?(\(?)$/) {
		printf STDERR "DBG: %s/newtable '%s'\n", $state, $2 if $opt_v>1;
		$state=$3?"table2":"table1";
		if ($opt_D) {
			printf "DROP TABLE IF EXISTS %s`%s`;\n", $opt_d, $2;
		}
		die "leftover stack" if @stack;
		printf "CREATE TABLE %s`%s` (\n", $opt_d, $2;
		$curtable = $2;
	} elsif ('table1' eq $state && /^\($/) {
		$state="table2";
	} elsif ('table2' eq $state && /NULL/) { # old export row defs
		s/[\[\]]/`/g;
		s/^(\S+\s+)`(\S+)`(.*)$/$1$2$3/;
		s/ IDENTITY\(\d+,\s*\d+\)//;
		s/^\[(\S+)\]/`$1`/;
		s/ COLLATE \S+ / /;
		s/ smalldatetime/ datetime/;
		s/, --.*$//;
		s/,$//;
		s/varchar\(max\)/varchar(16000)/i;
		s/^(`?\w+ID`? `?tinyint`?)/$1 UNSIGNED/i;
		s/^(`?\w+`?) `?bit`?($| )/$1 boolean$2/i;
		s/^(usage) /`$1` /i;
		s/ (money|float) / double /i;
		push @stack, sprintf "\t%s", $_;
	} elsif ('table2' eq $state && /^CONSTRAINT \[?(\w+)\]? PRIMARY KEY CLUSTERED( \(([\w'`, ]+)\))?$/) {
		s/[\[\]]/`/g;
		$state=$2?"table2":"table3";
		s/ CLUSTERED(\s+|\s*$)/ /;
		push @stack, sprintf "\t%s", $_;
	} elsif ($state =~ /^(table|idx)3/ && /^\($/) {
		$stack[$#stack] .= " ("
	} elsif ($state =~ /^(table|idx)3/ && /^\[\w+\] [A-Z]+,?$/) {
		s/,$//;
		s/[\[\]]/`/g;
		my $l = sprintf "\t\t%s", $_;
		if ($stack[$#stack] =~ / \($/) {
			$stack[$#stack] .= "\n".$l;
		} else {
			push @stack, $l;
		}
	} elsif ($state =~ /^(?:table|idx)3/ && /^\)(\s*WITH\s*\([A-Z_,= ]*\))?(,)?$/) {
		if ('table3' eq $state) {
			$stack[$#stack] .= "\n\t)";
			$state="table2";
		} elsif ('idx3' eq $state) {
			goto DUMPSTACK;
		} else {
			die "WTF: $state";
		}
	} elsif ('table2' eq $state && /^(\w+ [\w\(\)]+( [A-Z]+)?)(,|\);)$/) { # new export row defs
		die "style mix!" if @stack;
		s/[\[\]]/`/g;
		s/ID UNKNOWN/ID INTEGER/; # this one needs to be fixed by ccp!
		s/CHAR/VARCHAR/ if / CHAR\((\d+)\)/ && $1 > 255;
		s/(\s)DATE([,\)])/$1DATETIME$2/;
		printf "  %s\n", $_;
		$state="idle" if /\);$/;
	} elsif ('table2' eq $state && /^\)(;)?$/) {
		my $details = sprintf " ENGINE=%s DEFAULT CHARSET=%s", $opt_e, $opt_c;
		DUMPSTACK:
		printf "%s\n)%s;\n\n", 
			join(",\n", @stack),
			$details||"";
		@stack = ();
		$state="idle";

	} elsif ('idle' eq $state && /^CREATE NONCLUSTERED INDEX \[(\w+)\] ON (\[?\w+\]?\.)?\[?(\w+)\]$/) {
		die "idle but stack not empty ?!" if @stack;
		my ($idx, $tab,) = ($1, $3,);
		printf STDERR "DBG: %s/newidx '%s' on '%s'\n", $state, $idx, $tab if $opt_v>1;
		die "index late, expected '$curtable', got '$tab'" unless $curtable eq $tab;
		push @stack, sprintf "ALTER TABLE %s`%s` ADD KEY `%s`", $opt_d, $tab, $idx;
		$state = "idx3";





	} elsif ($opt_T && /^INSERT/) {
		exit 23;
	} elsif ($opt_I && /^INSERT/) {
		next;

## INSERTS
	} elsif ('idle' eq $state && /^INSERT (\[?dbo\]?[\._])?\[?(\w+)\] (\([\w, \[\]]+\)) VALUES (\(.*)$/) { # single-line inserts
		printf STDERR "DBG: %s/shortinsert '%s'\n", $state, $2 if $opt_v>3;
		my ($table, $cols, $vals,) = ($2, $3, $4,);
		$cols =~ s/[\[\]]/`/g;
		$vals =~ s/('|, (NULL|[-+E\d.]+))\)$/$1\);/;
		$state = "ins3" unless $vals =~ /\);$/;
		printf "INSERT INTO %s`%s` %s VALUES %s\n", $opt_d, $table, $cols, $vals;
	} elsif ('idle' eq $state && /^INSERT INTO (dbo[\._])?(\w+)$/) {
		printf STDERR "DBG: %s/newinsert '%s'\n", $state, $2 if $opt_v>3;
		$state='ins1';
		printf "INSERT INTO %s`%s` ", $opt_d, $2;
	} elsif ('ins1' eq $state && /^\(([\w, ]+)\)$/) {
		printf "%s ", $_;
		$state="ins2";
	} elsif ('ins2' eq $state && /^VALUES\(.*\);$/) {
		printf "%s\n", $_;
		$state="idle";
	} elsif ('ins2' eq $state && /^VALUES\(.*[^;]$/) {
		printf "%s\n", $_;
		$state="ins3";
	} elsif ('ins3' eq $state) {
		die "runaway multiline string" if /^(INSERT|CREATE) /;
		s/('((, .*), ([-+E\d.]+|NULL))?\))$/$1;/;
		printf "%s\n", $_;
		$state="idle" if /\);$/;

	} elsif ('idle' eq $state && /^(COMMIT;|GO)$/) {
		print "COMMIT;\n" if $opt_C;

	} elsif ('idle' eq $state && $opt_I) {
		# ignoreme

## WTF
	} elsif ('idle' eq $state && /^(AUTHORIZATION|WITH PERMISSION_SET|(ALTER|CREATE) ASSEMBLY|(ADD FILE )?FROM 0x[\dA-F]{10,}|AS N)/) {
    # wtf++

	} else {
		printf STDERR "DBG: %s/unknown '%s'\n", $state, $_;
	}


	printf "%s\n", $state if $opt_v>8;
}

print "COMMIT;\n" if $opt_C;