#!/usr/bin/perl -w

use HTML::Parser ();
use File::Basename;
use Data::Dumper;

die if !$ARGV[0];
$noexit = $ARGV[1];

my ($fname,$path,$suffix) = fileparse($ARGV[0], qw(.htm));
my %names;
$tablename = $fname;
$tablename =~ s/2//;
my @options  = ();
my $inoption = 0;
my $selname = 0;
my $inserts = '';

sub start {
  my($tag, $aref, $text) = @_;
  my %attrs = %$aref;
  $name = $attrs{'name'} || '';
  $type = $attrs{'type'} || '';
  $size = 255;

  $name =~ s/\s+/_/g;			# no whitespace
  $name =~ s/[^A-Za-z0-9_]//g;		# no junk chars, no fun

  if ($type eq 'radio' && exists $names{$name}) {
    return;
  }
  if ($name && exists $names{$name}) {
    return if $noexit;
    print "duplicate ``$name''\n";
    exit 1;
  }
  if ($name) {
    $names{$name} = 1;
  }
  if ($tag eq 'textarea') {
    print "  $name TEXT,\n";
  }
  elsif ($tag eq 'input') {
    if ($type eq 'checkbox') {
      print "  $name BIT,\n";
    }
    else {
      print "  $name VARCHAR($size),\n";
    }
  }
  elsif ($tag eq 'option') {
    $inoption = 1;
  }
  elsif ($tag eq 'select') {
    $selname = $name;
  }
}

sub end
{
  my($tag, $aref, $text) = @_;
  if ($tag eq 'select') {
    print "  $selname SMALLINT,\n";

    # store an enum table
    $inserts .= "\nCREATE TABLE ${tablename}_${selname}_enum (
  id INT NOT NULL auto_increment,
  name VARCHAR(80),
  PRIMARY KEY (id)
);

INSERT INTO ${tablename}_${selname}_enum VALUES ";

    $inserts .= '(0,' . $options[0] . ')';
    for ($i=1; $i<=$#options; $i++) {
      $inserts .= ',(0,' . $options[$i] . ')';
    }

    $inserts .= ";\n";
    $selname = '';
    @options = ();
  }
}

sub text
{
  my($tag, $text) = @_;
  if ($selname && $inoption && $text =~ /^\s*(.*)\s*$/s && length($1) > 0) {
    $tmp = $1;
    $tmp =~ s/\'/&#146;/g;  # any quotes will hose SQL
    $tmp =~ s/\"/&#148;/g;  # any quotes will hose SQL
    push(@options, "'${tmp}'");
  }
}

# Create parser object
$p = HTML::Parser->new( api_version => 3,
			handlers => [
                          start => [\&start, "tagname, attr, dtext"],
                          end   => [\&end,   "tagname, attr, dtext"],
			  text  => [\&text, "tagname, text"]
		         ],
                         marked_sections => 1,
                       );

print "CREATE TABLE $tablename (\n";
print "  id INT NOT NULL auto_increment,\n";

$p->parse_file($ARGV[0]);

print "  PRIMARY KEY(id)\n";
print ");\n${inserts}\n";

