#!/bin/env bash
#8_tidb_create_database.sh

#1.根据输入参数创建database
#2.根据输入参数创建应用用户
#3.根据输入参数为应用用户授权


function f_syntax
{
    [ ! -z "$1" ] && echo ">>[$(date "+%Y%m%d.%H%M%S")] $1"
    echo " "
    echo "Syntax:"
    echo "bash ${ShellName} "
    echo "-dblist <test1,db1,db2> "
    echo "-userlist <xxx~#~xxx~#~> "
    echo "-grantlist <xxx~#~xxx~#~> "
    echo "-devicegrantlist <xxx~#~xxx~#~> "   
    echo " "
    echo " #Description:"
    echo " -dblist ,the schema(database) list, eg. db1~#~db2~#~db3"
    echo " -userlist ,the user list, eg. app1~@~%~#~user1~@~1%~#~user2~@~11%"
    echo " -grantlist ,the user list, eg. app1~@~%~@~select,insert,update~@~test,db1~#~user1~@~1.%~@~select~@~*.*"
    echo " -devicegrantlist ,the user list, eg. app1~@~%~@~select,insert,update~#~user1~@~1.%~@~select"
    echo " "
    echo " #sample:"
    echo " #sh ${ShellName} -dblist test1,db1,db2 -userlist app1~@~%~#~user1~@~1%  -grantlist app1~@~%~@~select,insert,update~@~test,db1~#~user1~@~1%~@~select~@~*.* -devicegrantlist app1~@~%~@~select,insert,show view,show databases" 
    echo " "
    exit 22
}

function f_PrintLog
{
    _LogFlag=$(echo "$1"|tr "a-z" "A-Z")
    _LogMessage=$2
    [ $(echo "$1"|wc -L) -eq 4 ] && _LogFlag=$(echo "${_LogFlag}_")
    
    if [ -w "${LogFile}" ]
    then
        echo "$(date "+%Y%m%d.%H%M%S"):[${_LogFlag}]:${UserName}@${HostName}:${2}" | tee -a ${LogFile}
    else
        echo "$(date "+%Y%m%d.%H%M%S"):[${_LogFlag}]:${UserName}@${HostName}:${2}"
    fi

    [ "${_LogFlag}" = "ERROR" ] && exit 11 || return 0
}

###############################################################################

function f_create_database
{
    # f_PrintLog "Debug" "Start to run function f_create_database"
    local _database=$1

    local _database_exist=$(mysql --login-path=-NBe "select count(1) from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='${_database}'")
    if [ ${_database_exist} -eq 1 ]
    then
        f_PrintLog "Warn" "Database ${_database} already exist, not need to create, quit!"
        return 0
    fi

    mysql --login-path=-e "create database if not exists ${_database}"
    local _rt_code=$?
    local _database_exist=$(mysql --login-path=-NBe "select count(1) from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='${_database}'")
    if [ ${_rt_code} -eq 0 -a ${_database_exist} -eq 1 ]
    then
        f_PrintLog "Succ" "Create database ${_database} successfully."
    else
        f_PrintLog "Error" "Create database ${_database} failed, return code:${_rt_code} ${_database_exist}"
        exit 1
    fi
}

function f_create_user
{
    # f_PrintLog "Debug" "Start to run function f_create_user"
    local _user=$1
    local _host=$2
    local _pswd=$3

    local _user_exist=$(mysql --login-path=-NBe "select count(1) from mysql.user where user=trim('${_user}') and host='${_host}'")
    if [ ${_user_exist} -eq 1 ]
    then
        f_PrintLog "Warn" "User ${_user}@${_host} already exist, not need to create, quit!"
        return 0
    fi

    mysql --login-path=-e "create user ${_user}@'${_host}' identified by '${_pswd}';"
    local _rt_code=$?
    local _user_exist=$(mysql --login-path=-NBe "select count(1) from mysql.user where user=trim('${_user}') and host='${_host}'")
    if [ ${_rt_code} -eq 0 -a ${_user_exist} -eq 1 ]
    then
        f_PrintLog "Succ" "Create user ${_user}@${_host} successfully."
    else
        f_PrintLog "Error" "Create user ${_user}@${_host} failed, return code:${_rt_code} ${_user_exist}"
        exit 1
    fi
}

function f_grant_user
{
    # f_PrintLog "Debug" "Start to run function f_grant_user"
    local _user=$1
    local _host=$2
    local _privs=$3
    local _dblist=$4
    
    [ "${harole}" = "STANDBY" ] && local _privs="SELECT" 

    # echo "${_dblist}" | tr "," "\n" | while read _dbname
    # do
    #     _dbname_flag=$(echo "${_dbname}"|grep "\."|wc -l)
    #     [ ${_dbname_flag} -eq 1 ] && _db="${_dbname}" || _db="${_dbname}.*"
    #     f_PrintLog "Info" "grant select,insert,update,delete,index,create,drop on ${_db} to '${_user}'@'${_host}'"
    #     mysql --login-path=-e "grant ${_privs} on ${_db} to '${_user}'@'${_host}';"
    # done
    echo "${_dblist}" | tr "," "\n" > ${LogDir}/tmp_usergrant.txt
    while read _dbname
    do
        _dbname_flag=$(echo "${_dbname}"|grep "\."|wc -l)
        [ ${_dbname_flag} -eq 1 ] && _db="${_dbname}" || _db="${_dbname}.*"
        f_PrintLog "Info" "grant ${_privs} on ${_db} to ${_user}@'${_host}'"
        mysql --login-path=-e "grant ${_privs} on ${_db} to ${_user}@'${_host}'"
        _rt_code=$?

        if [ ${_rt_code} -ne 0 ]
        then
            f_PrintLog "Error" "User grant run failed, return code: ${_rt_code}"
            exit 1
        fi
    done < ${LogDir}/tmp_usergrant.txt

    mysql --login-path=-e "show grants for ${_user}@'${_host}'"

}

###############################################################################
##Init##
export LANG=en_US
ShellName="$(echo $0|awk -F / '{print $NF}')";ShellOption="$@";
WorkDir="$(echo $0|sed s/${ShellName}//g)";[ -z "${WorkDir}" ] && WorkDir=${PWD};cd ${WorkDir};WorkDir=${PWD}
LogDir="${WorkDir}/logs";mkdir -p ${LogDir};chmod ugo+rwx ${LogDir} 2>/dev/null;
RunTs=$(date "+%Y%m%d.%H%M%S")
LogFile="${LogDir}/${ShellName}.${RunTs}.log";
touch ${LogFile} 2>/dev/null;chmod ugo+rw "${LogFile}" 2>/dev/null;
Shellcksum=$(cksum ${WorkDir%/}/${ShellName} 2>/dev/null|awk '{printf "%d",$1}')
CpuHardware=$(uname -m);CpuType=$(uname -p);OSType=$(uname -s)
declare -i UserID=$(id -u);declare UserName=$(whoami);declare HostName=$(hostname);
###############################################################################
##TiDB Var##
TIUP_LOGINPATH=""
TidbHome=$(awk -F: -v un="tidb" '{if($1==un){print $6;exit}}' /etc/passwd 2>/dev/null)
###############################################################################
###############################################################################
##Get inopt
[ -z "${ShellOption}" ] && f_syntax;
for inopt in ${ShellOption}
do
    case $(echo $inopt|tr a-z A-Z) in
    -DBLIST|-DB) CurOpt="-DBLIST";continue;;
    -USERLIST|-U) CurOpt="-USERLIST";continue;;
    -GRANTLIST|-G) CurOpt="-GRANTLIST";continue;;
    -DEVICEGRANTLIST) CurOpt="-DEVICEGRANTLIST";continue;;
    -HELP|--HELP|-H) f_syntax;;
    -*) CurOpt="";continue;;
    esac
    case "${CurOpt}" in
    -DBLIST) declare databaselst="${inopt}";continue;;
    -USERLIST) declare userlst="${inopt}";continue;;
    -GRANTLIST) declare grantlst="${inopt}";continue;;
    -DEVICEGRANTLIST) declare devicegrantlist="${devicegrantlist} ${inopt}";continue;;
    esac
done

f_PrintLog "Info" "Start to run Script ${ShellName}, the Shellcksum is ${Shellcksum}"
f_PrintLog "Info" "Script Options:${ShellOption}"

###############################################################################
####Main####

function create_database_main
{
    if [ "${UserName}" != "tidb" ]
    then
        f_PrintLog "Error" "Pls run script with user tidb, exit!!!"
        exit 1
    fi

    # create database
    f_PrintLog "Info" "start to create database: ${databaselst}"
    eval $(echo $"${databaselst}" | awk -F',' '{for(i=1;i<=NF;i++){print "SCHE_GRP["(i-1)"]=\""$i"\";"}}')
    for para in "${SCHE_GRP[@]}"
    do
        f_PrintLog "Info" "start to create database ${para}"
        f_create_database ${para}
    done
    mysql --login-path=-e "show databases"

    f_PrintLog "Succ" "Run function create_database_main finish!"
}

function create_user_main
{
    if [ "${UserName}" != "tidb" ]
    then
        f_PrintLog "Error" "Pls run script with user tidb, exit!!!"
        exit 1
    fi

    # create database
    f_PrintLog "Info" "start to create user: ${userlst}"
    eval $(echo $"${userlst}" | awk -F'~#~' '{for(i=1;i<=NF;i++){print "USER_GRP["(i-1)"]=\""$i"\";"}}')
    for para in "${USER_GRP[@]}"
    do
        f_PrintLog "Info" "start to create user ${para}"
        userName=$(echo "${para}" | sed -e 's/"//g' | awk -F'~@~' '{print $1}')
        fromHost=$(echo "${para}" | sed -e 's/"//g' | awk -F'~@~' '{print $2}')
        userPswd="${userName}_C20220527"
        # userPswd=$(echo "${para}" | sed -e 's/"//g' | awk -F'~@~' '{print $3}')
        # dbList=$(echo "${para}" | sed -e 's/"//g' | awk -F'~@~' '{print $4}')

        f_create_user "${userName}" "${fromHost}" "${userPswd}" 
        # [ -n "${dbList}" ] && f_grant_user "${userName}" "${fromHost}" "${dbList}"
    done
    mysql --login-path=-e "select user,host,authentication_string from mysql.user"

    f_PrintLog "Succ" "Run function create_user_main finish!"
}

function user_grant_main
{
    if [ "${UserName}" != "tidb" ]
    then
        f_PrintLog "Error" "Pls run script with user tidb, exit!!!"
        exit 1
    fi

    # create database
    f_PrintLog "Info" "start to grant privileges: ${grantlst}"
    eval $(echo $"${grantlst}" | awk -F'~#~' '{for(i=1;i<=NF;i++){print "GRT_GRP["(i-1)"]=\""$i"\";"}}')
    for para in "${GRT_GRP[@]}"
    do
        f_PrintLog "Info" "start to grant user ${para}"
        userName=$(echo "${para}" | awk -F'~@~' '{print $1}')
        fromHost=$(echo "${para}" | awk -F'~@~' '{print $2}')
        grantList=$(echo "${para}" | awk -F'~@~' '{print $3}')
        dbList=$(echo "${para}" | awk -F'~@~' '{print $4}')

       f_grant_user "${userName}" "${fromHost}" "${grantList}" "${dbList}"
    done

    f_PrintLog "Succ" "Run function user_grant_main finish!"
}


function user_devicegrant_main
{
    if [ "${UserName}" != "tidb" ]
    then
        f_PrintLog "Error" "Pls run script with user tidb, exit!!!"
        exit 1
    fi

    # create database
    f_PrintLog "Info" "start to grant device privileges: ${devicegrantlist}"
    eval $(echo $"${devicegrantlist}" | awk -F'~#~' '{for(i=1;i<=NF;i++){print "DVCGRT_GRP["(i-1)"]=\""$i"\";"}}')
    for para in "${DVCGRT_GRP[@]}"
    do
        f_PrintLog "Info" "start to grant user ${para}"
        userName=$(echo "${para}" | awk -F'~@~' '{print $1}')
        fromHost=$(echo "${para}" | awk -F'~@~' '{print $2}')
        grantList=$(echo "${para}" | awk -F'~@~' '{print $3}')
        # dbList=$(echo "${para}" | awk -F'~@~' '{print $4}')
        dbList="*.*"

       f_grant_user "${userName}" "${fromHost}" "${grantList}" "${dbList}"
    done

    f_PrintLog "Succ" "Run function user_devicegrant_main finish!"
}

function crt_database_appuser_main
{
    if [ ${UserID} -eq 0 ]
    then
        # run with tidb
        f_PrintLog "Debug" "su tidb -c sh ${WorkDir}/${ShellName} \"${ShellOption}\""
        su tidb -c "sh ${WorkDir}/${ShellName} ${ShellOption}" || exit $?
    elif [ "${UserName}" = "tidb" ]
    then
        # use tidb user
        source ${TidbHome}/.bash_profile

        [ "${TidbHome:2:1}" = "1" ] && harole="PRIMARY" || harole="STANDBY"

        [ -n "${databaselst}" ] && create_database_main
        [ -n "${userlst}" ] && create_user_main
        [ -n "${grantlst}" ] && user_grant_main
        [ -n "${devicegrantlist}" ] && user_devicegrant_main
    else
        f_PrintLog "Error" "Pls run script with user root or tidb, exit!!!"
    fi

    f_PrintLog "Succ" "Run script ${ShellName} finish!"
}

crt_database_appuser_main