use strict;
use lib '/app/tools/common/lib';
use Common::RSDB;
use Common::RSApp;

my $singleton = Common::RSApp->new( clientID => 0 );

# Get the dbo for the common DB
#
my $dbo = Common::RSApp::GetCommonDB();

my $gInsertString;
my $gNumFields;
my $createdTable = 0;

my $file;
opendir( DIR, '.' ) or die "cannot open directory: $!";
while ( defined( $file = readdir DIR ) ) {
    next unless $file =~ /.?\.md$/;
    next if '112.md' eq $file;

    print $file . "\n";

    open FILE, $file or die "cannot open file $file: $!";

    my $linenum = 1;
    while ( my $line = <FILE> ) {
        $line =~ s/\n//;
        if ( 1 == $linenum ) {
            if ( !$createdTable ) {
                createTable( $dbo, $line );
                $createdTable = 1;
            }
        } else {
            eval { insertLine( $dbo, $line ); };
            if ($@) {
                die "ERROR FILE $file LINE $linenum: $@";
            }
        }
        $linenum++;
    }
    close FILE;
}

sub createTable {
    my ( $dbo, $line ) = @_;

    my @bits = split( /\t/, $line );

    $gNumFields = scalar(@bits);

    my @fields;
    my @keys;
    my @insert;
    $dbo->DoCmd('DROP TABLE IF EXISTS zzall_metadata');
    my $sql = 'CREATE TABLE `zzall_metadata` (';
    foreach my $field (@bits) {
        $field =~ s/-/_/g;

        push @fields, "`$field` varchar(255) NOT NULL default ''";
        push @insert, "$field = ?";

        #        if ($field =~ /.?_id$/)
        #        {
        push @keys, "KEY `$field` (`$field`)";

        #        }
    }
    $sql .= join( ',', @fields ) . ',' . join( ',', @keys ) . ');';

    #    $sql .= join(',', @fields) .  ');';

    print $sql. "\n";
    $dbo->DoCmd($sql);

    $gInsertString = join( ',', @insert );
    print "$gInsertString\n";
}

sub insertLine {
    my ( $dbo, $line ) = @_;

    my @bits = split( /\t/, $line );
    while ( scalar @bits < $gNumFields ) {
        push @bits, '';
    }

    $dbo->DoCmdWithPlaceholders( "INSERT INTO zzall_metadata SET $gInsertString", \@bits );
}