#!/bin/bash

set -o errexit
install_script_dir="/usr/lib/idste-server"
mysql_passwd=$1
database=nccs
tmp_database=nccs_temp
user_script="$install_script_dir/users.sql"
version_path="$install_script_dir/server_ver.txt"
socket_file="/var/run/mysqld/mysqld.sock"


# delete all Trigger Procedure Event Function View
delete_database_TPEFV(){
    echo "DELETE ALL VIEWS"
    #delete all views
    viewlist=`mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -N -e "select TABLE_NAME from information_schema.views where TABLE_SCHEMA='nccs';" `
    for view in $viewlist
    do
        sql_dropview=$sql_dropview"DROP VIEW $view; "
    done
    mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -e "$sql_dropview"

    #delete all triggers
    echo "DELETE ALL TRIGGERS"
    triggerlist=`mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -N -e "SELECT TRIGGER_NAME FROM INFORMATION_SCHEMA.triggers where TRIGGER_SCHEMA='nccs';" `
    for trigger in $triggerlist
    do
        sql_droptrigger=$sql_droptrigger"DROP TRIGGER $trigger; "
    done
    mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -e "$sql_droptrigger"

    #get mysql version, create procedure function event SELECT SQL
    sqlProcedure="SELECT SPECIFIC_NAME FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'nccs' AND ROUTINE_TYPE = 'PROCEDURE';"
    sqlFunction="SELECT SPECIFIC_NAME FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'nccs' AND ROUTINE_TYPE = 'FUNCTION';"
    sqlEvent="SELECT EVENT_NAME FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'nccs';"
    mysqlVersion=`mysql -uroot -p$mysql_passwd --socket=${socket_file} -N -e "select version();"`
    echo "MYSQL VERSION : $mysqlVersion"
    if [ "${mysqlVersion:0:1}" == "5" ]; then
      sqlProcedure="select name from mysql.proc where db = 'nccs' and type='PROCEDURE';"
      sqlFunction="select name from mysql.proc where db = 'nccs' and type='FUNCTION';"
      sqlEvent="select name from mysql.event where db = 'nccs';"
    fi

    #delete all procudures and functons
    echo "DELETE ALL PROCEDURES AND FUNCTIONS"
    proclist=`mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -N -e "$sqlProcedure"`
    for procedure in $proclist
    do
        sql_dropproc=$sql_dropproc"DROP PROCEDURE $procedure; "
    done
    mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -e "$sql_dropproc"

    funclist=`mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -N -e "$sqlFunction"`
    for func in $funclist
    do
        sql_dropfunc=$sql_dropfunc"DROP FUNCTION $func; "
    done
    mysql  -uroot -p$mysql_passwd --socket=${socket_file} $database -e "$sql_dropfunc"

    #delete all event
    echo "DELETE ALL EVENTS"
    eventlist=`mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -N -e "$sqlEvent"`
    for event in $eventlist
    do
        sql_dropevent=$sql_dropevent"DROP EVENTS $event; "
    done
    mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -e "$sql_dropevent"

    #delete tmp database
    echo "DELETE TMP DATABASE"
    mysql -uroot -p$mysql_passwd --socket=${socket_file} -e "DROP SCHEMA IF EXISTS $tmp_database; "
}


upgrade_database_v2()
{
    echo "UPGRADE DATABASE V2"
    dataStr=`date +%F-%T`
    mysqldump -uroot -p$mysql_passwd --socket=${socket_file} --add-drop-table nccs > /home/nccs.backup.${dataStr}
    vernow=$(mysql -uroot -p$mysql_passwd --socket=${socket_file} $database -N -e "SELECT Description from nccs.BaseInfo where InfomationID=512" 2>/dev/null) || vernow=""
    echo "IDSTE-SERVER NOW VERSION IS : $vernow"

    
    IFS='.' read -r maxVersion middleVersion minVersion <<< ${vernow}
    maxVersion=${maxVersion:1}
    maxVersion=$((10#$maxVersion))
    middleVersion=$((10#$middleVersion))

    # 8.9 之后的版本，不在处理历史的已经删除的数据
    if (( ${maxVersion} > 8 )); then
         return 0
    elif (( ${maxVersion} == 8 )); then
        if (( ${middleVersion} >= 9 )); then
            return 0
        fi
    fi
    
    
    delete_database_TPEFV
}


if mysql -uroot -p$mysql_passwd --socket=${socket_file} -e "use $database;" > /dev/null 2>&1; then
    echo "UPGRADE nccs CURRENT TABLES START"
    upgrade_database_v2
else
    echo "CREATE ${database} DATABASE"
    mysql -uroot -p$mysql_passwd --socket=${socket_file} -e "CREATE SCHEMA IF NOT EXISTS $database DEFAULT CHARACTER SET utf8mb4;" > /dev/null
fi

dataStr=`date +%F-%T`
for tmpDataBase in baize nvr ; do
    if mysql -uroot -p$mysql_passwd --socket=${socket_file} -e "use ${tmpDataBase};" > /dev/null 2>&1; then
        echo "${tmpDataBase} has existed, backup it"
        mysqldump -uroot -p$mysql_passwd --socket=${socket_file} --add-drop-table ${tmpDataBase} > /home/${tmpDataBase}.backup.${dataStr}
    else
        echo "CREATE ${tmpDataBase} DATABASE"
        mysql -uroot -p$mysql_passwd --socket=${socket_file} -e "CREATE SCHEMA IF NOT EXISTS ${tmpDataBase} DEFAULT CHARACTER SET utf8mb4;" > /dev/null
    fi
done



#develop version update mysql bind address
idsteVer=$(xargs < ${version_path})
if [[ "$idsteVer" != "" && ${idsteVer:0:1} == "D" ]]; then
    if [ -f "/etc/mysql/mysql.conf.d/mysqld.cnf.dev" ]; then
        if [ -f "/etc/mysql/mysql.conf.d/mysqld.cnf" ]; then
            mv /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.back
        fi
        cp /etc/mysql/mysql.conf.d/mysqld.cnf.dev /etc/mysql/mysql.conf.d/mysqld.cnf
        echo "IDSTE SERVER VERSION IS $idsteVer, USE MYSQL DEVELOP CONFIG FILE"
    elif [ -f "/etc/mysql/mysql.conf.d/mysqld.cnf" ]; then
      sed -i 's/127.0.0.1/0.0.0.0/g' /etc/mysql/mysql.conf.d/mysqld.cnf
      echo "IDSTE SERVER VERSION IS $idsteVer, UPDATE MYSQL BIND ADDRESS 0.0.0.0"
    fi
fi

#excute user script
if [ -f $user_script ]; then
    echo "CONFIGURE IDSTE-SERVER ENVIRONMENT"
    mysql -uroot -p$mysql_passwd --socket=${socket_file} $database < $user_script > /dev/null
fi

echo "UPGRADE DATABASE SQL PROCESS SUCCESS ........................"
