#!/bin/bash

DIRNAME=`dirname $0`

#################################
# Set the following variables
#################################
DBHOST=localhost
DBPORT=3306
DBNAME=mole

DBMOLEHOST=localhost
# DBMOLEHOST=%.int.ingv.it
DBMOLEUSER=moleuser
DBMOLEPASS=molepass

DBROOTUSER=root
DBROOTPASS=$1

MYSQL_CMD=mysql
#################################

echo ""
echo "* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * "
echo "* * * Master script for initializing a mole-database  * * * "
echo "* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * "
echo ""


########################################################
# Create database
########################################################
CHOICE=A
printf "* Do you want to initialize a mole-database with name '${DBNAME}' ?\n  WARNING: All existing data will be removed.          [y/n] : "
while [ "$CHOICE" != "y" ] && [ "$CHOICE" != "n" ]; do
    read -s -n 1 CHOICE
    if [ "$CHOICE" == "y" ]  ||  [ "$CHOICE" == "Y" ]; then
	CHOICE="y"
    fi
    if [ "$CHOICE" == "n" ]  ||  [ "$CHOICE" == "N" ]; then
	CHOICE="n"
    fi
done
echo ""

if [ "$CHOICE" == "y" ]; then
    echo ""
    echo "Database '${DBNAME}' -- Creating database with name '${DBNAME}'..."
    ${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBROOTUSER} -p${DBROOTPASS}<<EOF
    DROP DATABASE IF EXISTS ${DBNAME};
    CREATE DATABASE ${DBNAME};
EOF
else
    echo "Aborted."
    echo ""
    exit
fi


########################################################
# Create main user and grant
########################################################
echo "Database '${DBNAME}' -- Creating user '${DBMOLEUSER}'..."
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBROOTUSER} -p${DBROOTPASS} ${DBNAME}<<EOF
-- DELETE FROM mysql.user WHERE User='${DBMOLEUSER}' AND Host='${DBMOLEHOST}';
-- DROP USER IF EXISTS '${DBMOLEUSER}'@'${DBMOLEHOST}';
-- CREATE USER '${DBMOLEUSER}'@'${DBMOLEHOST}' IDENTIFIED BY '${DBMOLEPASS}';
-- REVOKE ALL PRIVILEGES, GRANT OPTION FROM '${DBMOLEUSER}'@'${DBMOLEHOST}';
GRANT ALL ON ${DBNAME}.* TO '${DBMOLEUSER}'@'${DBMOLEHOST}' IDENTIFIED BY '${DBMOLEPASS}' WITH GRANT OPTION;
GRANT SUPER ON *.* TO '${DBMOLEUSER}'@'${DBMOLEHOST}';
GRANT ALTER ROUTINE ON *.* TO '${DBMOLEUSER}'@'${DBMOLEHOST}';
FLUSH PRIVILEGES;
EOF


########################################################
# Create main tables, functions, stored procedures,
# views and triggers
########################################################
echo "Database '${DBNAME}' -- Creating tables..."
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_tables.sql
echo "Database '${DBNAME}' -- Creating region table..."
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_ew_mole_regions.sql 
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < ew_mole_regions/out_regions.sql
echo "Database '${DBNAME}' -- Creating functions..."
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_functions.sql
echo "Database '${DBNAME}' -- Creating stored procedures..."
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_procedures.sql
echo "Database '${DBNAME}' -- Creating views..."
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_views.sql
echo "Database '${DBNAME}' -- Creating triggers..."
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_triggers.sql
echo "Database '${DBNAME}' -- Creating tables for picks vs phases ..."
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_ew_params_picks_vs_phases.sql
echo "Database '${DBNAME}' -- Creating procedures for picks vs phases ..."
${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_ew_params_picks_vs_phases_sp.sql


########################################################
# Create table and stored procedure for storing
# pick_ew configuration into the db.
########################################################
echo ""
CHOICEEWPARAMSPICK=A
printf "* Do you want to create table 'ew_params_pick'?\n"
printf "  WARNING: you should have privileges for executing SELECT\n"
printf "           on mysql database 'information_schema'.   [y/n] "
while [ "$CHOICEEWPARAMSPICK" != "y" ] && [ "$CHOICEEWPARAMSPICK" != "n" ]; do
    read -s -n 1 CHOICEEWPARAMSPICK
    if [ "$CHOICEEWPARAMSPICK" == "y" ]  ||  [ "$CHOICEEWPARAMSPICK" == "Y" ]; then
	CHOICEEWPARAMSPICK="y"
    fi
    if [ "$CHOICEEWPARAMSPICK" == "n" ]  ||  [ "$CHOICEEWPARAMSPICK" == "N" ]; then
	CHOICEEWPARAMSPICK="n"
    fi
done
echo ""
if [ "$CHOICEEWPARAMSPICK" == "y" ]; then
    ${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_ew_params_pick_sta_table.sql
    ${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_ew_params_pick_sta_sp.sql
else
    echo "Skipped!"
fi


########################################################
# INGV Stuff for SisMap and SisPick
########################################################
echo ""
CHOICEEWSIS=A
printf "* INGV optional. Create other tables, view, functions, stored procedures, triggers? [y/n] "
while [ "$CHOICEEWSIS" != "y" ] && [ "$CHOICEEWSIS" != "n" ]; do
    read -s -n 1 CHOICEEWSIS
    if [ "$CHOICEEWSIS" == "y" ]  ||  [ "$CHOICEEWSIS" == "Y" ]; then
	CHOICEEWSIS="y"
    fi
    if [ "$CHOICEEWSIS" == "n" ]  ||  [ "$CHOICEEWSIS" == "N" ]; then
	CHOICEEWSIS="n"
    fi
done
echo ""
if [ "$CHOICEEWSIS" == "y" ]; then
    echo "Database '${DBNAME}' -- Creating stored procedures for INGV ..."
    ${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} < moledb_create_procedures_sis.sql
    ${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBROOTUSER} -p${DBROOTPASS} ${DBNAME} < moledb_create_grants_sis.sql
else
    echo "Skipped!"
fi

########################################################
# INGV Stuff for populating/updating table  ew_scnl
# with information from database seisnet ( latitude,
# longitude, elevation, etc.)
########################################################
echo ""
CHOICE=A
printf "* INGV optional. Populate/update table ew_scnl with station information from db seisnet? [y/n] "
while [ "$CHOICE" != "y" ] && [ "$CHOICE" != "n" ]; do
    read -s -n 1 CHOICE
    if [ "$CHOICE" == "y" ]  ||  [ "$CHOICE" == "Y" ]; then
	CHOICE="y"
    fi
    if [ "$CHOICE" == "n" ]  ||  [ "$CHOICE" == "N" ]; then
	CHOICE="n"
    fi
done
echo ""
if [ "$CHOICE" == "y" ]; then
    echo "Database '${DBNAME}' -- Populate/update table ew_scnl from seisnet  ..."
    ${DIRNAME}/moledb_update_scnl_from_seisnet.sh
else
    echo "Skipped!"
fi



########################################################
# Populate database by stored procedures
########################################################
echo ""
CHOICE=A
printf "* Do you want to populate the mole-database '${DBNAME}' for testing it? [y/n] "
while [ "$CHOICE" != "y" ] && [ "$CHOICE" != "n" ]; do
    read -s -n 1 CHOICE
    if [ "$CHOICE" == "y" ]  ||  [ "$CHOICE" == "Y" ]; then
	CHOICE="y"
    fi
    if [ "$CHOICE" == "n" ]  ||  [ "$CHOICE" == "N" ]; then
	CHOICE="n"
    fi
done
echo ""

LIST_POPULATE_FILES="\
moledb_populate_test_from_pickring_hew1.sql.gz \
moledb_populate_test_from_pickring_hew2.sql.gz \
moledb_populate_test_from_pickring_endeavour.sql.gz \
moledb_populate_test_from_hyporing_hew1.sql.gz \
moledb_populate_test_from_hyporing_hew2.sql.gz \
moledb_populate_test_from_hyporing_endeavour.sql.gz"

LIST_POPULATE_FILES="\
moledb_populate_test_from_hyporing_hew1.sql.gz \
moledb_populate_test_from_hyporing_hew2.sql.gz \
moledb_populate_test_from_hyporing_endeavour.sql.gz"

if [ "$CHOICEEWPARAMSPICK" == "y" ]; then
    LIST_POPULATE_FILES="${LIST_POPULATE_FILES} moledb_populate_test_from_ew_params_pick_sta.sql.gz"
fi

if [ "$CHOICE" == "y" ]; then
    for f in ${LIST_POPULATE_FILES}; do
	echo "Database '${DBNAME}' -- Populating from ${f}..."
	zcat $f | ${MYSQL_CMD} -h ${DBHOST} -P ${DBPORT} -u ${DBMOLEUSER} -p${DBMOLEPASS} ${DBNAME} > $f.log
    done
fi


