#! /bin/bash

. /usr/src/k_framework/main.sh


VERSION="1.0.6"


# $1 - column name
# $2 - column type
# $3 - default value
percona_change_column() {

    if [[ $1 == "" ]]; then report "column name is missing" 1; fi

    if [[ $PERCONA_VERBOSE == 1 ]]; then
        report "Change Column: $1 $2 DEFAULT $3" 3
    fi

    if [[ $P_OPT != "" ]]; then
        local PP_OPT="--password=$PERCONA_DB_PASSWORD"
    fi

    COLUMN_EXISTS=`MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT "$DB_NAME" -e "DESC $TABLE" | grep -w "$1" | wc -l`
    if [ "$COLUMN_EXISTS" != "0" ]; then

        NEW_COLUMN=$(echo "$1$2$3" | tr '[:upper:]' '[:lower:]' | tr -d ' ' | sed -E "s|int\([0-9]+\)|int|g")

        SQL="SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$SYSTEM_CONFIG_DIR' AND TABLE_NAME = '$TABLE' AND COLUMN_NAME = '$1';"
        COLUMN_TYPE=`MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT "$DB_NAME" --silent -e "$SQL" | grep -v value`

        SQL="SELECT COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$SYSTEM_CONFIG_DIR' AND TABLE_NAME = '$TABLE' AND COLUMN_NAME = '$1';"
        COLUMN_DEFAULT=`MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT "$DB_NAME" --silent -e "$SQL" | grep -v value`

        #report "$1 $COLUMN_TYPE $COLUMN_DEFAULT" 5
        OLD_COLUMN=$(echo "$1$COLUMN_TYPE$COLUMN_DEFAULT" | tr '[:upper:]' '[:lower:]' | tr -d ' ' | sed -E "s|int\([0-9]+\)|int|g")

        if [ "$NEW_COLUMN" != "$OLD_COLUMN" ]; then
            #report "Mismatch: old [$OLD_COLUMN] new [$NEW_COLUMN]" 1
            if [ "$ALTER_QUERY" != "" ]; then
                ALTER_QUERY="$ALTER_QUERY, CHANGE $1 $1 $2 DEFAULT $3"
            else
                ALTER_QUERY="CHANGE $1 $1 $2 DEFAULT $3"
            fi
        fi

    fi

}


percona_drop_column() {

    if [[ $PERCONA_VERBOSE == 1 ]]; then
        report "Drop Column: $1 $2" 0
    fi

    if [[ $P_OPT != "" ]]; then
        local PP_OPT="--password=$PERCONA_DB_PASSWORD"
    fi

    COLUMN_EXISTS=`MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT "$DB_NAME" -e "DESC $TABLE" | grep -w "$1" | wc -l`
    if [ "$COLUMN_EXISTS" != "0" ]; then
        if [ "$ALTER_QUERY" != "" ]; then
            ALTER_QUERY="$ALTER_QUERY, DROP $1"
        else
            ALTER_QUERY="DROP $1"
        fi
    fi
}

percona_add_column() {

    if [[ $PERCONA_VERBOSE == 1 ]]; then
        report "Add Column: $1 $2" 0
    fi

    if [[ $P_OPT != "" ]]; then
        local PP_OPT="--password=$PERCONA_DB_PASSWORD"
    fi

    COLUMN_EXISTS=`MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT "$DB_NAME" -e "DESC $TABLE" | grep -w "$1" | wc -l`
    if [ "$COLUMN_EXISTS" == "0" ]; then
        if [ "$ALTER_QUERY" != "" ]; then
            ALTER_QUERY="$ALTER_QUERY, ADD $1 $2"
        else
            ALTER_QUERY="ADD $1 $2"
        fi
    fi
}

percona_add_index() {

    if [[ $PERCONA_VERBOSE == 1 ]]; then
        report "Add Index: $1 $2" 0
    fi

    if [[ $P_OPT != "" ]]; then
        local PP_OPT="--password=$PERCONA_DB_PASSWORD"
    fi

    INDEX_EXISTS=`MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT "$DB_NAME" -e "SHOW INDEX IN $TABLE" | awk '{print $3}' | grep -w "$1" | wc -l`
    if [ "$INDEX_EXISTS" == "0" ]; then
        if [ "$ALTER_QUERY" != "" ]; then
            ALTER_QUERY="$ALTER_QUERY, ADD INDEX $1 $2"
        else
            ALTER_QUERY="ADD INDEX $1 $2"
        fi
    fi
}

percona_drop_index() {

    if [[ $PERCONA_VERBOSE == 1 ]]; then
        report "Drop Index: $1 $2" 0
    fi

    if [[ $P_OPT != "" ]]; then
        local PP_OPT="--password=$PERCONA_DB_PASSWORD"
    fi

    INDEX_EXISTS=`MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT "$DB_NAME" -e "SHOW INDEX IN $TABLE" | awk '{print $3}' | grep -w "$1" | wc -l`
    if [ "$INDEX_EXISTS" != "0" ]; then
        if [ "$ALTER_QUERY" != "" ]; then
            ALTER_QUERY="$ALTER_QUERY, DROP INDEX $1"
        else
            ALTER_QUERY="DROP INDEX $1"
        fi
    fi
}

percona_check_root_mysql_connection() {

    # Check DB connection for non root user
    report "Checking MySQL connection" 3

    if [ "$DB_HOST" != "localhost" ] && [ "$DB_HOST" != "127.0.0.1" ]; then
        report "This script can only connect to local dabatase! Database hostname is '$DB_HOST'" 1
        exit 1
    fi

    if [[ $P_OPT != "" ]]; then
        local PP_OPT="--password=$PERCONA_DB_PASSWORD"
    fi

    MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT -e "use $DB_NAME" &> /dev/null
    if [ "$?" != "0" ]; then
        report "Failed to connect to database using these details: /usr/bin/mysql -h $DB_HOST -u $PERCONA_DB_USERNAME --password=$PERCONA_DB_PASSWORD" 1
        exit 1
    fi

    if [[ $P_OPT != "" ]]; then
        local PP_OPT="--password=kolmisoft"
    fi

    # Check DB connection for root user
    MYSQL_PWD=kolmisoft /usr/bin/mysql -h "$DB_HOST" -u "root" $PP_OPT -e "use $DB_NAME" &> /dev/null
    if [ "$?" != "0" ]; then
        report "Failed to connect to database using these details: /usr/bin/mysql -h '$DB_HOST' -u 'root' --password='kolmisoft'" 2
        report "Trying to connect without password" 3
        /usr/bin/mysql -h "$DB_HOST" -u "root" --password="" -e "use $DB_NAME" &> /dev/null
        if [ "$?" != "0" ]; then
            report "Failed to connect to database using these details: /usr/bin/mysql -h '$DB_HOST' -u 'root' --password=''" 1
            exit 1
        fi
    fi

    report "Connected successfully" 0

}

percona_check_process_privilege() {

    if [[ $P_OPT != "" ]]; then
        local PP_OPT="--password=$PERCONA_DB_PASSWORD"
    fi

    # Try query SHOW ENGINE INNODB STATUS
    # If user doesn't have PROCESS privilege, this query will fail
    report "Checking MySQL permissions" 3
    MYSQL_PWD=$DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT -e "SHOW ENGINE INNODB STATUS" &> /dev/null
    if [ "$?" != "0" ]; then
        report "MySQL user '$PERCONA_DB_USERNAME' doesn't have PROCESS privilege" 2

        # Try to fix privileges

        # In some systems root user doesn't have password
        /usr/bin/mysql -h "$DB_HOST" -u 'root' --password='' -e "GRANT PROCESS ON *.* to '$PERCONA_DB_USERNAME'@'$DB_HOST' IDENTIFIED BY '$PERCONA_DB_PASSWORD'" &> /dev/null
        /usr/bin/mysql -h "$DB_HOST" -u 'root' --password='' -e "FLUSH PRIVILEGES" &> /dev/null


        if [[ $P_OPT != "" ]]; then
            local PP_OPT="--password=kolmisoft"
        fi

        # In other systems root user has password 'kolmisoft'
        MYSQL_PWD=kolmisoft /usr/bin/mysql -h "$DB_HOST" -u 'root' $PP_OPT -e "GRANT PROCESS ON *.* to '$PERCONA_DB_USERNAME'@'$DB_HOST' IDENTIFIED BY '$PERCONA_DB_PASSWORD'" &> /dev/null
        MYSQL_PWD=kolmisoft /usr/bin/mysql -h "$DB_HOST" -u 'root' $PP_OPT -e "FLUSH PRIVILEGES" &> /dev/null

        if [[ $P_OPT != "" ]]; then
            local PP_OPT="--password=$PERCONA_DB_PASSWORD"
        fi

        # Check if privileges ok after fix
        MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT -e "SHOW ENGINE INNODB STATUS" &> /dev/null
        if [ "$?" != "0" ]; then
            report "Failed to grant PROCESS privilege to MySQL user '$PERCONA_DB_USERNAME'" 1
            exit 1
        else
            report "MySQL privileges fixed" 0
        fi
    fi

    report "Permissions are correct" 0

}

percona_check_replication_connection() {

    if [[ $P_OPT != "" ]]; then
        local PP_OPT="--password=$PERCONA_DB_PASSWORD"
    fi

    report "Checking replication" 3

    REPLICATION_IP=`MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" $PP_OPT "$DB_NAME" -e "show slave status\G" | grep -w 'Master_Host' | grep -Eo '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}'`
    if [ "$REPLICATION_IP" != "" ]; then
        report "Found replication database $REPLICATION_IP" 3
        # If replication is found, try to connect to it
        MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$REPLICATION_IP" -u "$PERCONA_DB_USERNAME" $PP_OPT -e "use $DB_NAME" &> /dev/null
        if [ "$?" != "0" ]; then
            report "Failed to connect to replication database using these details: /usr/bin/mysql -h $REPLICATION_IP -u $PERCONA_DB_USERNAME --password=$PERCONA_DB_PASSWORD" 1
            exit 1
        else
            report "Successfully connected to replication database" 0

            report "Checking MySQL permissions on Replication server: $REPLICATION_IP" 3
            GRANTS=`MYSQL_PWD=$PERCONA_DB_PASSWORD /usr/bin/mysql -h "$REPLICATION_IP" -u "$PERCONA_DB_USERNAME" $PP_OPT -e "SHOW GRANTS FOR CURRENT_USER();"`

            #echo $GRANTS;

            k_default_interface_ip
            GRANTS_OK=1

            if echo $GRANTS | grep -q PROCESS; then
                report "MySQL user '$PERCONA_DB_USERNAME' has PROCESS privilege" 0
            else
                report "MySQL user '$PERCONA_DB_USERNAME' doesn't have PROCESS privilege on the replication server: $REPLICATION_IP" 1
                GRANTS_OK=0
            fi

            if echo $GRANTS | grep -q SUPER; then
                report "MySQL user '$PERCONA_DB_USERNAME' has SUPER privilege" 0
            else
                report "MySQL user '$PERCONA_DB_USERNAME' doesn't have SUPER privilege on the replication server: $REPLICATION_IP" 1
                GRANTS_OK=0
            fi

            if [[ $GRANTS_OK == 0 ]]; then
                report "  Execute these commands as root on replication server: $REPLICATION_IP to fix GRANT privileges" 1
                report "  GRANT PROCESS, SUPER ON *.* to '$PERCONA_DB_USERNAME'@'$DEFAULT_IP' IDENTIFIED BY '$PERCONA_DB_PASSWORD';" 1
                report "  FLUSH PRIVILEGES;" 1
                exit 1
            fi

            report "All OK with Replication on $REPLICATION_IP" 0

        fi
    else
        report "Replication not detected" 3
    fi

}