#!/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_OPT=""

SCRIPT_DIR="$(dirname $0)"

function usage {

    echo "Usage: $0 [OPTIONS]" >&2

    echo "Valid Options:" >&2

    echo "-d, --dbname=DBNAME      database name psql commands will connect to" >&2

    echo "-h, --host=HOSTNAME      database server host or socket directory" >&2

    echo "-U, --username=USERNAME  database user name" >&2

    exit 1;

}

while [[ $# -gt 0  ]]; do

    case "$1" in

        -\? | --help)

            usage

            ;;

        -h)

            if [[ -z $2  ]];  then

                echo "Option $1 requires an argument." >&2

                usage

            fi

            DB_HOST_OPT="-h $2"

            shift 2

            ;;

        --host=*)

            DB_HOST_OPT="-h ${1#*=}"

            shift

            ;;

        -U)

            if [[ -z $2  ]];  then

                echo "Option $1 requires an argument." >&2

                usage

            fi

            DB_USER="$2"

            shift 2

            ;;

        --username=*)

            DB_USER="${1#*=}"

            shift

            ;;

        -d)

            if [[ -z $2  ]];  then

                echo "Option $1 requires an argument." >&2

                usage

            fi

            DB_NAME="$2"

            shift 2

            ;;

        --dbname=*)

            DB_NAME="${1#*=}"

            shift

            ;;

        *)

            echo "Invalid option: $1" >&2

            usage

            ;;

    esac

done

function run_sudo_sql_cmd {

    local SQL_CMD="$1"

    sudo -u postgres psql -e $DB_HOST_OPT -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" $DB_HOST_OPT -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 --no-password -U "$DB_USER" $DB_HOST_OPT "$DB_NAME" -T template_postgis

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

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

    exit 1

fi

echo "Updating ${DB_NAME} table permissions..."

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.sql

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

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

echo "Install successfully completed."