#!/usr/bin/perl

use warnings;
use strict;
my @chr = (1..22,
	   qw(AltOnly MT NewSs NotOn PAR Un X Y)
	  );
my %chr;
@chr{@chr} = @chr;
$chr{NewSs} = 'ss';

my %args;
@args{map {my $a = $_; lc($a)} @ARGV} = 1;

if ($args{drop}) {
    for my $chr (@chr) {
	print "DROP TABLE SubInd_ch${chr};\n";
    }
    print "DROP TABLE SubInd;\n";
}
if ($args{create}) {
    print <<END;
CREATE TABLE SubInd (
	chr VARCHAR NOT NULL ,
	batch_id int NOT NULL ,
	subsnp_id int NOT NULL ,
	submitted_ind_id int NOT NULL ,
	submitted_strand_code int NULL ,
	allele_flag int NULL ,
	gty_id int NULL ,
	submitted_rs int NULL
);
END
    for my $chr (@chr) {
	print "CREATE TABLE SubInd_ch${chr} (CHECK (chr = '$chr{${chr}}')) INHERITS (SubInd);\n";
    }
}
if ($args{trigger}) {
    print <<EOF;
CREATE OR REPLACE FUNCTION subind_insert_trigger()
RETURNS TRIGGER AS \$\$
BEGIN
EOF
    for my $chr (@chr) {
	print <<EOF;
IF (NEW.chr = '${chr}') THEN
   INSERT INTO SubInd_ch${chr} VALUES (NEW.*);
EOF
	print "ELS";
    }
    print <<EOF;
E
RAISE EXCEPTION 'chr out of range!';
END IF;
RETURN NULL;
END;
\$\$
LANGUAGE plpgsql;
EOF
}
if ($args{index}) {
    for my $chr (@chr) {
	print "CREATE INDEX ON SubInd_ch${chr}(subsnp_id);\n";
	print "CREATE INDEX ON SubInd_ch${chr}(submitted_ind_id,subsnp_id,gty_id);\n";
    }
}

