#!/bin/bash

USER="root"
PASSWORD="1000omc@kp!"
HOST="127.0.0.1"
PORT="33066"
DBNAME="tenants_db"
UpgradeSQLDir=/usr/local/omc/etc/db/upgrade
Upgvue3SQLDir=/usr/local/omc/etc/db/upgvue3
InstallSQLDir=/usr/local/omc/etc/db/install

drop_db_sql="drop database IF EXISTS ${DBNAME}"
create_db_sql="create database IF NOT EXISTS ${DBNAME}"

case "$1" in
    upgrade)
        echo "Upgrade database ${DBNAME}"
        for SQL in ${UpgradeSQLDir}/*.sql; do 
            echo -n "Execute SQL script: ${SQL} ..."
            mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} < ${SQL}; 
            if [ $? = 0 ]; then
                echo "done" 
            fi 					
        done
        ;;

    upgvue3)
        echo "Upgrade to vue3 database ${DBNAME}"
        for SQL in ${Upgvue3SQLDir}/*.sql; do 
            echo -n "Execute SQL script: ${SQL} ..."
            mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} < ${SQL};
            if [ $? = 0 ]; then
                echo "done" 
            fi 					 
        done
        ;;

    install)
        echo "Drop database ${DBNAME} ...!!!"
        mysql -u${USER} -p${PASSWORD} -P $PORT -h ${HOST} --protocol tcp -e "${drop_db_sql}"
        echo "Create database ${DBNAME} if not exist"
        mysql -u${USER} -p${PASSWORD} -P $PORT -h ${HOST} --protocol tcp -e "${create_db_sql}"
        for SQL in ${InstallSQLDir}/*.sql; do 
            echo -n "Execute SQL script: ${SQL} ..."
            mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} < ${SQL}; 
            if [ $? = 0 ]; then
                echo "done" 
            fi 				
        done
       ;;
    
    *)
        while true
            do
                read -r -p "Do you upgrade or upgrade from layui to vue3 or fresh install database ${DBNAME}? [Upgrade/upgVue3/Install/Quit] " input

                case $input in
                    [uU][pP][gG][rR][aA][dD][eE]|[uU])
                        echo "Skip to drop database ${DBNAME}"
                        echo "Upgrade database ${DBNAME}"
                            
                        for SQL in ${UpgradeSQLDir}/*.sql; do 
                            echo -n "Execute SQL script: ${SQL} ..."
                            mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} < ${SQL};
                            if [ $? = 0 ]; then
                                echo "done" 
                            fi 							 
                        done

                        break
                        ;;

                    [uU][pP][gG][vV][uU][eE][3]|[vV]])
                        echo "Skip to drop database ${DBNAME}"
                        echo "Upgrade to vue3 database ${DBNAME}"

                        for SQL in ${Upgvue3SQLDir}/*.sql; do 
                            echo -n "Execute SQL script: ${SQL} ..."
                            mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} < ${SQL};
                            if [ $? = 0 ]; then
                                echo "done" 
                            fi 							 
                        done

                        break
                        ;;

                    [iI][nN][sS][tT][aA][lL][lL]|[iI])
                        echo "Drop database ${DBNAME} ...!!!"

                        mysql -u${USER} -p${PASSWORD} -P $PORT -h ${HOST} --protocol tcp -e "${drop_db_sql}"
                        echo "Create database ${DBNAME} if not exist"
                        mysql -u${USER} -p${PASSWORD} -P $PORT -h ${HOST} --protocol tcp -e "${create_db_sql}"

                        for SQL in ${InstallSQLDir}/*.sql; do 
                            echo -n "Execute SQL script: ${SQL} ..."
                            mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} < ${SQL};
                            if [ $? = 0 ]; then
                                echo "done" 
                            fi 
                        done

                        break           
                        ;;

                    [qQ][uU][iI][tT]|[qQ])
                        echo "Nothing to be done! GOOD BYE"
                        exit 1;
                        ;;

                    *)
                        echo "Invalid input..."
                        ;;
                esac
            done
        ;;
esac 

# create kpi_report table with ne_type, exp: kpi_report_amf
ne_types=$(mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} -se "SELECT DISTINCT LOWER(ne_type) FROM kpi_title")
for ne_type in ${ne_types}; do
    TABLE_NAME="kpi_report_${ne_type}"
    SQL="CREATE TABLE IF NOT EXISTS ${TABLE_NAME} LIKE \`kpi_report\`;"
    echo -n "Create table: ${TABLE_NAME} ..."
    mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} -e "${SQL}"
    if [ $? = 0 ]; then
        echo "done" 
    fi
    SQL="ALTER TABLE ${TABLE_NAME} ADD INDEX IF NOT EXISTS \`idx_timestamp\`(\`created_at\`) USING BTREE, ADD INDEX IF NOT EXISTS \`idx_uid_datetime\`(\`rm_uid\`, \`date\`, \`start_time\`) USING BTREE;"
    echo -n "Create index of ${TABLE_NAME} ..."
    mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} -e "${SQL}"
    if [ $? = 0 ]; then
        echo "done" 
    fi
    SQL="ALTER TABLE ${TABLE_NAME} ADD INDEX IF NOT EXISTS \`idx_timestamp\`(\`created_at\`) USING BTREE,ADD INDEX IF NOT EXISTS \`idx_uid_datetime\`(\`rm_uid\`, \`date\`, \`start_time\`) USING BTREE;"
    echo -n "Create index of ${TABLE_NAME} ..."
    mysql -u${USER} -p${PASSWORD} -P ${PORT} --protocol tcp -D ${DBNAME} -e "${SQL}"
    if [ $? = 0 ]; then
        echo "done" 
    fi
    SQL="ALTER TABLE ${TABLE_NAME} ADD COLUMN IF NOT EXISTS \`tenant_id\` varchar(32) NULL DEFAULT NULL AFTER \`created_at\`;"
    echo -n "Alter table ${TABLE_NAME} ..."
    mysql -u${USER} -p${PASSWORD} -P ${PORT} --protocol tcp -D ${DBNAME} -e "${SQL}"
    if [ $? = 0 ]; then
        echo "done" 
    fi					
    TABLE_NAME="kpi_c_report_${ne_type}"
    SQL="CREATE TABLE IF NOT EXISTS ${TABLE_NAME} LIKE \`kpi_c_report\`;"
    echo -n "Create table: ${TABLE_NAME} ..."
    mysql -u${USER} -p${PASSWORD} -P ${PORT} -h ${HOST} --protocol tcp -D ${DBNAME} -e "${SQL}"
    if [ $? = 0 ]; then
        echo "done" 
    fi	
done