#########################################################################
#
#  Script to create a suitable table to hold data exported from VAN
#
#########################################################################

$proot = '/alldev/john/LIBDEMS/';
$file = $proot.$ARGV[0];

open HFILE, $file or die "failed to open $file for input ($!)\n";
@line = <HFILE>;
close HFILE;

$colinit = 0;
@widths = ();
@fields = ();
$fcount = 0;

foreach $line (@line) {
  chomp $line;
  @fvalues = split(/\|/, $line);
  $w = scalar @fvalues;
  ##  if ($w != 86) { print ("w=$w\n"); }
  # first get the column names and deal with bad characters and duplicates
  if ($colinit == 0) {
    foreach $fvalue (@fvalues) { print $fvalue." ~ "; }
    %fvhash = ();
    foreach $fvalue (@fvalues) {
      $fvalue =~ s/\n|\r|\t//g;
      $fvalue =~ s/ //g;
      $fvalue =~ s/\://g;
      $fv = $fvalue;
      if (exists($fvhash{$fv})) {
        for ($i=2; $i<10; $i++) {
          $fv = $fvalue.$i;
          if (!exists($fvhash{$fv})) { last; }
        }
      }
      $fvhash{$fv} = $fv;
      push(@fields, $fv);
      $fcount++;
    }
    $colinit = 1;
  }
  else {
  
  # measure largest width of columns
  ##  if ($colinit == 1) { print "\n\n fvalues count=".scalar @fvalues; }
    $col = 0;
    foreach $fvalue (@fvalues) {
      $width = length($fvalue);
      if ($colinit == 1) { push(@widths, $width); }
      elsif ($widths[$col] < $width) { $widths[$col] = $width; }
      $col++;
    }
    $colinit = 2;
  }
}

$col = 0;
foreach $field (@fields) {
  print "$col |$field|  width=|".$widths[$col]."|\n";
  $col++;
}

##$wcount = scalar @widths;
##print "fcount="."$fcount\n";
##print "wcount="."$wcount\n";

# output the create table sql
$file = 'dbsetup.sql';
open HFILE, '>'.$file or die "failed to open $file for output ($!)\n";

$col = 0;
$n = scalar @fields;
print HFILE "CREATE TABLE vandata (\n";
foreach $field (@fields) {
  $w = $widths[$col];
  $w = int( (int($w / 5) + 1) * 5);
  
  print HFILE "$field    varchar($w)";
  if ($n - 1 > $col) { print HFILE ","; }
  print HFILE ("\n");
  $col++;
}
print HFILE ");\n\n";
close HFILE;