#!/bin/bash

# Bash script to create a new postgis database and prep it with GADM

# data for geovalidation purposes.

#

# Basic workflow:

#  1. Create postgis-enabled template db

#  2. Create postgis-enabled db from the template

#  3. Create required tables in postgis-enabled db

#  4. Load gadm2 and geonames.org data into database

#

# For postgis installation see:

#   http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1204

#

# Jim Regetz

# NCEAS

# Created November 2012

#

# refactored and reorganized by

# Paul Sarando

# iPlant Collaborative

# Updated Oct 2013

DB_NAME="geoscrub"

DB_USER="bien"

DB_HOST="localhost"

SCRIPT_DIR="$(dirname $0)"

function run_sudo_sql_cmd {

    local SQL_CMD="$1"

    sudo -u postgres psql -e -h "$DB_HOST" -d "$DB_NAME" -c "$SQL_CMD"

    if [[ $? != 0 ]]; then

        echo "Error while executing SQL command."

        echo -n "You may drop the ${DB_NAME} database"

        echo " and attempt to run this $0 script again."

        exit 1

    fi

}

function run_sql_script {

    local SCRIPT="$1"

    psql -e --set ON_ERROR_STOP=1 -U "$DB_USER" -h "$DB_HOST" -d "$DB_NAME" < "$SCRIPT"

    if [[ $? != 0 ]]; then

        echo "Error while executing SQL script ${SCRIPT}"

        exit 1

    fi

}

# create postgis template if one doesn't already exist

echo "Setting up template_postgis database..."

sudo -u postgres createdb template_postgis

if [[ $? == 0 ]]; then

    # successfully created template_postgis, which means it didn't already exist

    sudo -u postgres psql -e -d template_postgis < "${SCRIPT_DIR}"/update.template_postgis.sql

    if [[ $? != 0 ]]; then

        echo "Could not create postgis extesion on template_postgis database."

        exit 1

    fi

fi

# create postgis db from template

echo "Creating the ${DB_NAME} database..."

createdb -U "$DB_USER" -h "$DB_HOST" "$DB_NAME" -T template_postgis

if [[ $? != 0 ]]; then

    echo "Could not create ${DB_NAME} database."

    exit 1

fi

run_sudo_sql_cmd "ALTER TABLE spatial_ref_sys OWNER TO ${DB_USER}"

run_sudo_sql_cmd "ALTER VIEW geography_columns OWNER TO ${DB_USER}"

run_sudo_sql_cmd "ALTER VIEW geometry_columns OWNER TO ${DB_USER}"

echo "Setting up ${DB_NAME} database tables..."

run_sql_script "${SCRIPT_DIR}"/setup.geonames-to-gadm.sql

run_sql_script "${SCRIPT_DIR}"/setup.geoscrub.sql

echo "Install successfully completed."