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);
}