#!/usr/bin/perl


use Getopt::Std;
getopts('d:s');

my $file = shift;

my $prog = $0;
$prog =~ s:.*/::;

$USAGE = "usage: $prog <file>\n";

die "$USAGE\n" unless $file;

die "$file does not exist.\n" unless -f $file;

open(IN, $file)
        or die "Couldn't open $file: $!\n";

my $fn = <IN>;

$fn =~ /(\||\t)/;
my $delim = $1;

$delim = quotemeta $delim;

chomp($fn);
my @names;
my @float;
my @min;
my @max;
my @text;

@names = split /$delim/, $fn;

my $i = 0;
for(@names) {
        push @max, 0;
        push @min, 255;
        push @text, 0;
        push @unprice, 0;
        push @float, 0;
}

my $fcount = scalar @names;

while(<IN>) {
        chomp;
        @f = split /$delim/o, $_, $fcount;
        for($i = 0; $i < $fcount; $i++) {
                $len = length $f[$i];
				if($len > $max[$i]) {
					$max[$i] = $len;
					$max_est[$i] = $.;
				}
				if($len < $min[$i]) {
					$min[$i] = $len;
					$min_est[$i] = $.;
				}
                next if $text[$i];
                $text[$i] = 1, next
                        unless $f[$i] =~ /^\d*\.?\d+/;
                $float[$i] = 1 if /\D/;
                $unprice[$i] = 1 unless /^\d*\.\d\d$/;
        }
}

close IN;

$file =~ s:.*/::;
$origfile = $file;
$file =~ s:\..*::;
printf "# %-32s %6s %8s %6s %8s %3s %3s %3s\n",
                "FIELD NAME",
                "MIN",
				"<RECORD",
                "MAX",
				"<RECORD",
                "TEXT",
                "FLT",
                "NPR",
;

for($i = 0; $i < $fcount; $i++) {
        printf "# %-32s %6d %8d %6d %8d %3d %3d %3d\n",
                $names[$i],
                $min[$i],
                $min_est[$i],
                $max[$i],
                $max_est[$i],
                $text[$i],
                $float[$i],
                $unprice[$i]
        ;
}
print "# SQL TABLE\n#\n# CREATE TABLE $file (\n";
$max = $max[0];
$max++ if $max % 2;

push @column_def,
        qq{Database  $file  COLUMN_DEF   "code=char($max) NOT NULL PRIMARY KEY"};
print "#\tcode char($max) NOT NULL PRIMARY KEY,\n";


for ($i = 1; $i < @names; $i++) {
        if ($text[$i]) {
				if ($max < 1) {
						$type = 'VARCHAR(255)';
				}
                elsif ($max[$i] < 16) {
                        $type = 'CHAR';
                        $flen = $max[$i] + 1;
                        $type .= "($flen)";
                }
                elsif ($max[$i] < 40) {
                        $type = 'VARCHAR';
                        $flen = int($max[$i] * 1.1);
                        $type .= "($flen)";
                }
                elsif ($max[$i] < 50) {
                        $type = 'VARCHAR(64)';
                }
                elsif ($max[$i] < 80) {
                        $type = 'VARCHAR(96)';
                }
                elsif ($max[$i] < 100) {
                        $type = 'VARCHAR(128)';
                }
                elsif ($max[$i] < 150) {
                        $type = 'VARCHAR(192)';
                }
                elsif ($max[$i] < 232) {
                        $type = 'VARCHAR(255)';
                }
                else {
                        $type = 'BLOB';
                }
        }
        elsif (! $float[$i]) {
                $type = 'INT';
        }
        elsif (! $unprice[$i] ) {
                $type = 'FLOAT';
        }
        else {
                $type = 'DECIMAL(8,2)';
        }
        if($min[$i] != 0) {
                $nullstr = " NOT NULL";
        }
        else {
                $nullstr = '';
        }

        print "#\t$names[$i] $type$nullstr";
        print ",\n" unless $i >= $#names;
        push @column_def,
                qq{Database  $file  COLUMN_DEF   "$names[$i]=$type$nullstr"};
        push @numeric,
                qq{Database  $file  NUMERIC      $names[$i]}
            if ! $text[$i];
}

print "\n# );\n";

print "# MiniVend database definition\n";
print "Database  $file  $origfile  SQL\n";
print join "\n", @column_def, @numeric;
print "\n";

__END__