#!/bin/bash

# :mode=transact-sql:

cd "$(dirname "$0")"

. ../../../lib/import.sh

psql <<'EOF'

SELECT functions.to_global_col_names('"SUBPLOT"');

SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.STATECD"  TO ".STATECD" $$);

SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.UNITCD"   TO ".UNITCD"  $$);

SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.COUNTYCD" TO ".COUNTYCD"$$);

SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.PLOT"     TO ".PLOT"    $$);

SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.INVYR"    TO ".INVYR"   $$);

SELECT functions.rename_if_exists($$ALTER TABLE "SUBPLOT" RENAME "SUBPLOT.SUBP"     TO ".SUBP"    $$);

SELECT functions.set_col_types('"SUBPLOT"', ARRAY[

  ('.STATECD' , 'integer')

, ('.UNITCD'  , 'integer')

, ('.COUNTYCD', 'integer')

, ('.PLOT'    , 'integer')

, ('.INVYR'   , 'integer')

, ('.SUBP'    , 'integer')

]::functions.col_cast[]);

SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.ID"     UNIQUE ("SUBPLOT.CN")                                                  $$);

SELECT functions.create_if_not_exists($$ALTER TABLE "SUBPLOT" ADD CONSTRAINT "SUBPLOT.unique" UNIQUE (".STATECD", ".UNITCD", ".COUNTYCD", ".PLOT", ".INVYR", ".SUBP")$$);

SELECT functions.cluster_once('"SUBPLOT"', '"SUBPLOT.unique"');