#!/bin/bash

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

# data for geovalidation purposes.

#

# Basic workflow:

#  1. Load geoscrub input data into database

#  2. Scrub geoscrub input data with the geonames.sql script

#  3. Scrub geoscrub input data with the geovalidate.sql script

# 

# Paul Sarando

# iPlant Collaborative

# 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

    echo "Geoscrub Data Options:" >&2

    echo "-i, --geoscrub-input     Geoscrub input directory (default: ${HOME}/geoscrub_input)" >&2

    echo "                         Delete this directory, or the input CSV in it," >&2

    echo "                         to re-download the data." >&2

    echo "-o, --output-file        Optional file where geoscrubbed CSV should be saved." >&2

    echo "                         If this option is given, then the final geoscrub table" >&2

    echo "                         will be dumped to the specified file in CSV format." >&2

    echo "                         No output file is saved if this option is omitted." >&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

            ;;

        -i)

            if [[ -z $2  ]];  then

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

                usage

            fi

            GEOSCRUB_INPUT_OPT="-i $2"

            shift 2

            ;;

        --geoscrub-input=*)

            GEOSCRUB_INPUT_OPT="$1"

            shift

            ;;

        -o)

            if [[ -z $2  ]];  then

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

                exit 1;

            fi

            OUTPUT_FILE="$2"

            shift 2

            ;;

        --output-file=*)

            OUTPUT_FILE="${1#*=}"

            shift

            ;;

        *)

            echo "Invalid option: $1" >&2

            usage

            ;;

    esac

done

function run_sql_script {

    local SCRIPT=$1

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

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

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

        exit 1

    fi

}

"${SCRIPT_DIR}"/load-geoscrub-input.sh -U "$DB_USER" $DB_HOST_OPT -d "$DB_NAME" $GEOSCRUB_INPUT_OPT

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

    echo "Could not load ${DB_NAME} database with geonames.org data."

    exit 1

fi

echo "Scrubbing input with geonames data..."

run_sql_script "${SCRIPT_DIR}/geonames.sql"

echo "Scrubbing input with geovalidate data..."

run_sql_script "${SCRIPT_DIR}/geovalidate.sql"

echo "Input successfully scrubbed."

if [[ -n $OUTPUT_FILE ]]; then

    echo "Saving geoscrubbed data as a CSV file to ${OUTPUT_FILE}..."

    run_sql_script "${SCRIPT_DIR}/output.geoscrub.sql" > "$OUTPUT_FILE"

    if [[ ! -w $OUTPUT_FILE ]]; then

        echo "Could not save geoscrub table contents to ${OUTPUT_FILE}"

        exit 1

    fi

else

    echo "Scrubbed input available in the geoscrub table of the ${DB_NAME} database."

fi