#!/bin/sh

A=100
B=20

# make $A one-level partitions
for prefix in foo bar ; do
  echo "drop table if exists ${prefix};"
  echo "create table $prefix (a int, b int) partition by list (a);"
  for a in ` seq 1 $A ` ; do
    echo "create table ${prefix}_${a} partition of ${prefix} for values in ($a);"
  done
  echo "insert into $prefix select generate_series(1, $A), generate_series(1, $A);"
done

# make $A * $B two-level partitions
for prefix in foofoo barbar ; do
  echo "drop table if exists ${prefix};"
  echo "create table $prefix (a int, b int) partition by list (a);"
  for a in ` seq 1 $A ` ; do
    echo "create table ${prefix}_${a} partition of ${prefix} for values in (${a}) partition by list (b);" 
    for b in ` seq 1 $B ` ; do
      echo "create table ${prefix}_${a}_${b} partition of ${prefix}_$a for values in ($b);"
    done
  done
  echo "insert into $prefix select generate_series(0, $A * $B - 1) / $B + 1, generate_series(0, $A * $B - 1) % $B + 1;"
done

# make two-level partitions with US states and then monthly ranges
for prefix in sales purchases ; do
  echo "drop table if exists ${prefix};"
  echo "create table $prefix (state text, created date) partition by list (state);"
  for state in AK AL AR AZ CA CO CT DC DE FL GA GU HI IA ID  IL IN KS KY LA MA MD ME MH MI MN MO MS MT NC ND NE NH NJ NM NV NY  OH OK OR PA PR PW RI SC SD TN TX UT VA VI VT WA WI WV WY ; do
    echo "create table ${prefix}_${state} partition of ${prefix} for values in ('$state') partition by range (created);"
    for year in ` seq 2015 2017 ` ; do
      for month in ` seq 1 12 ` ; do
        next_year=$year
        next_month=$((month + 1))
        if [[ x$next_month = x13 ]] ; then
          next_month=01
          next_year=$((year + 1))
        fi
        if [[ $month < 10 ]] ; then month=0$month ; fi
        echo "create table ${prefix}_${state}_${year}_${month} partition of ${prefix}_${state} for values from ('$year-$month-01') to ('$next_year-$next_month-01');"
      done
    done
  done
done