#!/usr/bin/perl
use strict;
use warnings;

my $sql_file = $ARGV[0];
my $out_file = $ARGV[1];

die("NO INFILE\n") unless($sql_file);
die("NO OUTDIR\n") unless($out_file);

unless($out_file=~/\%s/) {
  $out_file .= '/' unless($out_file=~m![\\/]$!);
  $out_file .= '%s.csv';
}

my $sql = SQL::parse_dump->new($sql_file, 'UTF-8');
$|=1;

$sql->add_handler(CREATE => sub{
  my @data = @{shift(@_)};
  my $name=shift(@data);
  while(@data) {
    last if(ref $data[0]);
    $name=shift(@data);
  }
  @data=@{$data[0]};

  print "CREATE $name\n";

  while(@data) {
    if( $data[-1][0] =~/KEY|PRIMARY|UNIQUE|FULLTEXT/) {
      print "  REMOVE KEY: $data[-1][0] $data[-1][1]\n";
      pop(@data);
      next;
    }

    last;
  }

  my $file=sprintf($out_file,$name);
  open(my $fh, '>:unix:encoding(UTF-8)', $file) or die("ERROR open $file ($!)");
  my @d;
  for(@data) {
    my $var=$_->[0];
    if($var=~/[,\r\n"']/) {
      $var=~s/"/\\"/gs;
      $var="\"$var\"";
    }
    push(@d,$var);
  }
  print $fh join(',',@d)."\n";
  close($fh);

});

$sql->add_handler(INSERT => sub{
  my $data = shift;
  print "INSERT $data->[2]\n";

  my $file=sprintf($out_file,$data->[2]);
  open(my $fh, '>>:unix:encoding(UTF-8)', $file) or die("ERROR open $file ($!)");
  my @elm=@{$data->[5]};

  # single entry
  if(ref $elm[0] and !ref $elm[0][0]){
    @elm=[[@elm]];
  }
  #else{ die Dumper(\@elm); }

  for my $set ( @elm ) {
    next unless(ref $set);
    next unless(ref $set->[0]);
    my @line;
    for (@{$set->[0]}) {
      my $var = $_->[0];
      if($var=~/[,\r\n'"]/) {
        $var=~s/"/""/gs;
        $var="\"$var\"";
      }
      push( @line,$var );
    }
    print $fh join(',',@line)."\n";
    print '.';
  }
  print "\n";
  close($fh);
});

$sql->run();


#######################################################################
#######################################################################

package SQL::parse_dump;
use strict;
use warnings;

sub new {
  my ($class,$file,$enc)=@_;
  return bless( { file => $file, enc => $enc, handler => {} }, $class );
}

sub add_handler{
  my ($self,$name,$code)=@_;
  $self->{handler}->{$name}=$code;
  return $self;
}

sub run {
  my ($self) = @_;
  open( my $fh, '<:unix:encoding('.$self->{enc}.')', $self->{file} ) or die("ERROR open $self->{file} ($!)\n");

  my @stack=({type => 'root'});
  while(my $line=<$fh>) {
    while(length($line)) {

      if($self->{monitor}) {
        if($self->{last_line} and $self->{last_line} eq $line) {
          if($self->{count} > 3) {
            use Data::Dumper; print Dumper(\@stack);
            exit();
          }
        }
        else {
          $self->{count}=0;
        }
        $self->{last_line}=$line;
        $self->{count}++;
      }


      if($stack[0]->{type} ne 'string') {
        $line=~s/^\s*--.+$//s;
        $line=~s/[\r\n]+/ /gs;
        last if($line=~/^\s*$/s);

        if($line=~s!^\s*/\*!!s) {
          unshift(@stack, { type => 'comment' } );
        }

        elsif($stack[0]->{type} eq 'root') {
          if($line=~s/^\s*(\w+)//s) {
            my $cmd=uc($1);
            if($cmd eq 'CREATE' or $cmd eq 'INSERT') {
              if( $self->{handler}->{$cmd} ) {
                unshift(@stack, { type => 'command', key => $cmd, line => [$cmd] } );
              }
              else {
                unshift(@stack, { type => 'command' } );
              }
            }
            else {
              last;
            }
          }
          else {
            last;
          }
        }

      }

      if($stack[0]->{type} eq 'comment') {
        if( $line=~s!^.*\*/!!s ) {
          shift(@stack);
        }
        else
        { $line = '' }
      }

      if($stack[0]->{type} eq 'command') {
        if($line=~s/^\s*([_\+\-\w]+)//s) {
          push(@{$stack[0]->{line}},$1) if($stack[0]->{line});
        }
        elsif($line=~s/^\s*=//s) {
          my $key=pop(@{$stack[0]->{line}});
          unshift(@stack,{ type => 'pair', key => $key, line => [] });
        }
        elsif($line=~s/^\s*\(//s) {
          unshift(@stack,{ type => 'group', line => [] });
          unshift(@stack,{ type => 'subset', line => [] });
        }
        elsif($line=~s/^\s*(['"`])//s) {
          unshift(@stack,{ type => 'string', sep => $1, line=>'' });
        }
        elsif($line=~s/^\s*,//) {
          my $last=pop(@{$stack[0]->{line}});
          unshift(@stack,{ type => 'group', line => [ [ $last ] ] });
          unshift(@stack,{ type => 'subset', line => [] });
        }
        elsif($line=~s/^\s*;//s) {
          if(@stack > 1) {
            my $data=shift(@stack);
            if( $data->{key} and $self->{handler}->{ $data->{key} } ) {
              $self->{handler}->{ $data->{key} }->( $data->{line} );
            }
          }
        }
      }

      elsif($stack[0]->{type} eq 'string') {
        my $sep=$stack[0]->{sep};
        if($line=~s/^(.*?)$sep//s) {
          $stack[0]->{line} .= $1;
          if($line =~s!^$sep!!s) {
            $stack[0]->{line} .= $sep;
          }
          else {
            my $data = shift(@stack);
            $data->{line}=~s!(?:\\r)?\\n?!\n!gs;
            $data->{line}=~s!\\(.)!$1!gs;
            if( $stack[0]->{line} ) {
              if(ref($stack[0]->{line}))
              { push( @{$stack[0]->{line}} , $data->{line} ); }
              else
              { $stack[0]->{line} .= $data->{line}; }
            }
          }
        }
        else {
          $stack[0]->{line} .= $line;
        }
      }

      elsif($stack[0]->{type} eq 'pair') {
        if(@{$stack[0]->{line}}) {
          my $data = shift(@stack);
          push(@{$stack[0]->{line}},{key => $data->{key}, value => $data->{line}->[0]}) if($stack[0]->{line});
        }
        else {
          if($line=~s/^\s*([_\+\-\w]+)//s) {
            push(@{$stack[0]->{line}},$1);
          }
          elsif($line=~s/^\s*\(//s) {
            unshift(@stack,{ type => 'group', line => [] });
            unshift(@stack,{ type => 'subset', line => [] });
          }
          elsif($line=~s/^\s*(['"`])//s) {
            unshift(@stack,{ type => 'string', sep => $1, line=>'' });
          }
        }
      }

      elsif($stack[0]->{type} eq 'group') {
        if( $line=~s/^\s*,//s ) {
          unshift(@stack,{ type => 'subset', line => [] });
        }
        elsif( $line=~s/^\s*\)//s or $line=~/^\s*[\w;]/s ) {
          my $data = shift(@stack);
          push(@{$stack[0]->{line}},$data->{line}) if($stack[0]->{line});
        }
        else {
          unshift(@stack,{ type => 'subset', line => [] });
        }
      }

      elsif($stack[0]->{type} eq 'subset') {
        if($line=~s/^\s*([_\+\-\w\.]+)//s) {
          push(@{$stack[0]->{line}},$1) if($stack[0]->{line});
        }
        elsif($line=~s/^\s*\(//s) {
          unshift(@stack,{ type => 'group', line => [] });
          unshift(@stack,{ type => 'subset', line => [] });
        }
        elsif($line=~s/^\s*(['"`])//s) {
          unshift(@stack,{ type => 'string', sep => $1, line=>'' });
        }
        elsif($line=~/^\s*,/s or $line=~/^\s*\)/s or $line=~/^\s*;/s) {
          my $data=shift(@stack);
          push(@{$stack[0]->{line}},$data->{line});
        }
      }
    }
  }
  close($fh);
  return $self;
}

1;