#!/usr/bin/perl

if($#ARGV < 0) { exit; }

# file name must be TABLENAME.csv
@names = split(/\./, $ARGV[0]);

@path = split(/\//, $names[0]);
$table = $path[$#path];

@columns = ();
@pks = ();
@comments = ();
@indexes = ();

open(IN, $ARGV[0]);
while(my $line = <IN>)
{
	my $tmp = $line;
	$tmp =~ s/(?:\x0D\x0A|[\x0D\x0A])?$/,/;
	my @data = map {/^"(.*)"$/ ? scalar($_ = $1, s/""/"/g, $_) : $_}
                ($tmp =~ /("[^"]*(?:""[^"]*)*"|[^,]*),/g);

	my $field = $data[1];
	my $comment = $data[2];
	my $is_pk = $data[3];
	my $is_index = $data[5];
	my $is_null = $data[6];
	my $type = $data[7];
	my $size = $data[8];
	my $seq = $data[9];
	my $type2 = $type;
	
	if($type eq "bigserial") { $type2 = "bigint"; }
	
	my $column_def = "\t$field $type2";
	if($type ne 'date' && $type ne 'timestamp' && $type ne 'smallint' && $type ne 'bool' && $type ne 'bigserial' && $size)
	{
		$column_def .= "($size)";
	}
#	if(lc($field) eq (substr(lc($table),1) . "_id") && $is_pk)
	if($type eq 'bigserial' && $is_pk)
	{
		my $seqname;
		if(!$seq) { $seqname = $table."_".$field."_SEQ"; }
		else { $seqname = $seq; }
		$column_def .= " default nextval('".$seqname."') ";
		print "create sequence $seqname;\n";
	}
	if($is_null)
	{
		$column_def .= " NOT NULL";
	}
	if($is_pk)
	{
		push(@pks, $field);
	}
	if($is_index)
	{
		push(@indexes, $field);
	}
	if($comment)
	{
		push(@comments, "comment on column $table.$field is '$comment';");
	}
	push(@columns, $column_def);
}
close(IN);

print "create table $table\n(\n";
print join(",\n", @columns);
if(scalar(@pks) > 0)
{
	print ",\n";
	my $const = $table . "_PKEY";
	print "\tconstraint $const primary key (";
	print join(",", @pks);
	print ")\n";
}
else
{
	print "\n";
}
print ");\n";

print join("\n", @comments);
print "\n";

if(scalar(@pks) > 0)
{
	my $index = $table . "_PKEY_IDX";
	print "CREATE INDEX $index ON $table (";
	print join(",", @pks);
	print ");\n";
}

if(scalar(@indexes) > 0)
{
	foreach my $index (@indexes)
	{
		print "CREATE INDEX $table" . "_" . $index . "_IDX ON $table ($index);\n";
	}
}