#!/bin/bash

table=$1
start_year=$2
end_year=$3

for year in `seq $start_year $end_year`
do
    for qtr in "q1" "q2" "q3" "q4"
    do
        printf "CREATE TABLE IF NOT EXISTS xonstat.%s_%s%s ( \n" $table $year $qtr
        if [[ $qtr = "q1" ]]
        then
            printf "\tCHECK ( create_dt >= DATE '%s-01-01' AND create_dt < DATE '%s-04-01' ) \n" $year $year
        elif [[ $qtr = "q2" ]]
        then
            printf "\tCHECK ( create_dt >= DATE '%s-04-01' AND create_dt < DATE '%s-07-01' ) \n" $year $year
        elif [[ $qtr = "q3" ]]
        then
            printf "\tCHECK ( create_dt >= DATE '%s-07-01' AND create_dt < DATE '%s-10-01' ) \n" $year $year
        elif [[ $qtr = "q4" ]]
        then
            next_year=$[year + 1]
            printf "\tCHECK ( create_dt >= DATE '%s-10-01' AND create_dt < DATE '%s-01-01' ) \n" $year $next_year
        fi

        printf ") INHERITS (%s);\n\n" $table

        # indexes
        printf "CREATE INDEX %s_%s%s_ix001 on %s_%s%s(create_dt);\n" $table $year $qtr $table $year $qtr

        # conditional indexes that depend on the table
        if [[ $table = "games" ]]
        then
            printf "CREATE INDEX %s_%s%s_ix002 on %s_%s%s using gin(players);\n" $table $year $qtr $table $year $qtr
            printf "CREATE UNIQUE INDEX %s_%s%s_pk on %s_%s%s(game_id);\n" $table $year $qtr $table $year $qtr
            printf "ALTER TABLE %s_%s%s ADD PRIMARY KEY USING INDEX %s_%s%s_pk;\n" $table $year $qtr $table $year $qtr
        fi
        
        if [[ $table = "player_game_stats" || $table = "player_weapon_stats" || $table = "player_game_nonparticipants" ]]
        then
            printf "CREATE INDEX %s_%s%s_ix002 on %s_%s%s(game_id);\n" $table $year $qtr $table $year $qtr
            printf "CREATE INDEX %s_%s%s_ix003 on %s_%s%s(player_id);\n" $table $year $qtr $table $year $qtr
        fi

        if [[ $table = "team_game_stats" ]]
        then
            printf "CREATE INDEX %s_%s%s_ix002 on %s_%s%s(game_id);\n" $table $year $qtr $table $year $qtr
        fi

        printf "\n"
    done
done
printf "\n"

printf "CREATE OR REPLACE FUNCTION %s_ins()\n" $table
printf "RETURNS TRIGGER AS \$\$\n"
printf "BEGIN\n"

for i in `seq $start_year $end_year`
do

    if [[ start_year -eq i ]]
    then
        printf "\tIF (NEW.create_dt >= DATE '%s-01-01' AND NEW.create_dt < DATE '%s-04-01') THEN\n" $i $i
    else
        printf "\tELSIF (NEW.create_dt >= DATE '%s-01-01' AND NEW.create_dt < DATE '%s-04-01') THEN\n" $i $i
    fi
    printf "\t\tINSERT INTO %s_%sQ1 VALUES (NEW.*);\n" $table $i

    printf "\tELSIF (NEW.create_dt >= DATE '%s-04-01' AND NEW.create_dt < DATE '%s-07-01') THEN\n" $i $i
    printf "\t\tINSERT INTO %s_%sQ2 VALUES (NEW.*);\n" $table $i

    printf "\tELSIF (NEW.create_dt >= DATE '%s-07-01' AND NEW.create_dt < DATE '%s-10-01') THEN\n" $i $i
    printf "\t\tINSERT INTO %s_%sQ3 VALUES (NEW.*);\n" $table $i

    next_year=$[i + 1]
    printf "\tELSIF (NEW.create_dt >= DATE '%s-10-01' AND NEW.create_dt < DATE '%s-01-01') THEN\n" $i $next_year
    printf "\t\tINSERT INTO %s_%sQ4 VALUES (NEW.*);\n" $table $i

done

printf "\tELSE\n"
printf "\t\tRAISE EXCEPTION 'Date out of range. Fix the %s_ins() trigger!';\n" $table
printf "\tEND IF;\n"
printf "\tRETURN NULL;\n"

printf "END\n"
printf "\$\$\n"
printf "LANGUAGE plpgsql;\n\n"

printf "DROP TRIGGER IF EXISTS %s_ins_trg ON xonstat.%s;\n" $table $table
printf "CREATE TRIGGER %s_ins_trg\n" $table
printf "BEFORE INSERT on xonstat.%s\n" $table
printf "FOR EACH ROW EXECUTE PROCEDURE %s_ins();\n" $table