#! /bin/sh

# Rocky 9 compatible

#
#   Author: Ricardas Stoma
#   Year:   2016
#   About:  This script uses percona toolkit to update calls/calls_old/rates table without blocking

#   To alter table calls_old execute script with argument 'calls_old'
#   To alter table rates execute script with argument 'rates'

. /usr/src/m2/framework/bash_functions.sh

BACKUP_PATH="/usr/local/m2/backups"
PERCONA_LOG="/var/log/m2/percona.log"
PERCONA_DB_PASSWORD=`cat /etc/m2/system.conf | grep dbuser | awk '{print $3}'`
PERCONA_DB_USERNAME=`cat /etc/m2/system.conf | grep dbuser | awk '{print $3}'`
DB_NAME=`cat /etc/m2/system.conf | grep dbname | awk '{print $3}'`
DB_HOST=`cat /etc/m2/system.conf | grep dbhost | awk '{print $3}'`
ALTER_QUERY=""
START_DATE=`date`
TABLE=calls
FORCE=0
HOSTNAME=`hostname`

VERSION="1.1.2"
SCRIPT_NAME="Calls table update to v2"

add_column() {
    COLUMN_EXISTS=`/usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_PASSWORD" "$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
}

add_index() {
    INDEX_EXISTS=`/usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_PASSWORD" "$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
}

drop_index() {
    INDEX_EXISTS=`/usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_PASSWORD" "$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
}

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 from /etc/m2/system.conf is '$DB_HOST'" 1
        exit 1
    fi

    /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_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 $PERCONA_DB_USERNAME --password=$PERCONA_DB_PASSWORD" 1
        exit 1
    fi

    # Check DB connection for root user
    /usr/bin/mysql -h "$DB_HOST" -u "root" --password="kolmisoft" -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

}

check_process_privilege() {

    # Try query SHOW ENGINE INNODB STATUS
    # If user doesn't have PROCESS privilege, this query will fail
    report "Checking MySQL permissions" 3
    /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_PASSWORD" -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

        # In other systems root user has password 'kolmisoft'
        /usr/bin/mysql -h "$DB_HOST" -u 'root' --password='kolmisoft' -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='kolmisoft' -e "FLUSH PRIVILEGES" &> /dev/null

        # Check if privileges ok after fix
        /usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_PASSWORD" -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

}

check_replication_connection() {

    report "Checking replication" 3

    REPLICATION_IP=`/usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_PASSWORD" "$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
        /usr/bin/mysql -h "$REPLICATION_IP" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_PASSWORD" -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
        fi
    else
        report "Replication not detected" 3
    fi

}


###############################################
# PERCONA TABLE UPDATE WITHOUT DOWNTIME #
###############################################

k_start

k_config_details
# v3 check
if k_db_field_check "calls" "mos"; then
    report "It seems calls table is v3. No need to update to v2." 3
    k_exit 0
fi



# Check arguments
for arg in "$@"
do
    # if passed argument is "calls" then switch to calls table
    if [ "$arg" == "calls" ]; then
        TABLE=calls
    fi

    # if passed argument is "calls_old" then switch to calls_old table
    if [ "$arg" == "calls_old" ]; then
        TABLE=calls_old
    fi

    # if passed argument is "rates" then switch to rates table
    if [ "$arg" == "rates" ]; then
        TABLE=rates
    fi

    # if passed argument is "force" then do not ask for confirmation
    if [ "$arg" == "force" ]; then
        FORCE=1
    fi
done

# Check if we can connect to mysql
check_root_mysql_connection

# Check if mysql users has PROCESS privilege
check_process_privilege

report "Checking if $TABLE table needs update" 3

if [ "$TABLE" == "rates" ]; then
    # Add new columns
    add_column "old_id" "BIGINT(20) DEFAULT NULL"
    add_column "currently_effective" "TINYINT(4) NOT NULL DEFAULT 0"

    # Add indexes
    add_index "old_id_index" "(old_id)"
    add_index "currently_effective_index" "(currently_effective)"
else
    # Add new columns
    add_column "pdd" "DECIMAL(30,15) DEFAULT 0"
    add_column "src_user_id" "INT DEFAULT 0"
    add_column "terminated_by" "ENUM('unknown', 'originator', 'terminator', 'system') DEFAULT NULL"
    add_column "answer_time" "TIMESTAMP NULL DEFAULT NULL"
    add_column "end_time" "TIMESTAMP NULL DEFAULT NULL"
    add_column "originator_codec" "ENUM('unknown', 'G.711 A-law', 'G.711 u-law', 'G.722', 'G.723.1', 'G.726', 'G.729', 'GSM', 'Speex', 'OPUS') DEFAULT NULL"
    add_column "terminator_codec" "ENUM('unknown', 'G.711 A-law', 'G.711 u-law', 'G.722', 'G.723.1', 'G.726', 'G.729', 'GSM', 'Speex', 'OPUS') DEFAULT NULL"
    add_column "pai" "VARCHAR(40) DEFAULT NULL"

    # Drop old indexes
    drop_index "id"
    drop_index "id_2"
    drop_index "id_3"
    drop_index "src_device_id"
    drop_index "dst_device_id"
    drop_index "src"
    drop_index "provider_id"
    drop_index "card_id"
    drop_index "disposition"
    drop_index "hgcause"
    drop_index "resellerid"
    drop_index "did_id"
    drop_index "uniqueidindex"
    drop_index "calldateindex"
    drop_index "dateindex"
    drop_index "calldate"

    # Add new indexes
    #add_index "accountcode_index" "(accountcode)"
    add_index "src_device_id_index" "(src_device_id)"
    add_index "dst_device_id_index" "(dst_device_id)"
    add_index "server_id_index" "(server_id)"
    add_index "src_index" "(src(6))"
    add_index "dst_index" "(dst(6))"
    add_index "localized_dst_index" "(localized_dst(6))"
    add_index "prefix_index" "(prefix(6))"
    add_index "provider_id_index" "(provider_id)"
    add_index "user_id_index" "(user_id)"
    add_index "disposition_index" "(disposition)"
    add_index "hangupcause_index" "(hangupcause)"
    add_index "dst_user_id_index" "(dst_user_id)"
    add_index "uniqueid_index" "(uniqueid(6))"
    add_index "calldate_index" "(calldate)"
    add_index "src_user_id_index" "(src_user_id)"
    #add_index "originator_codec_index" "(originator_codec)"
    #add_index "terminator_codec_index" "(terminator_codec)"
fi

# Check if table update is required
if [ "$ALTER_QUERY" == "" ]; then
    report "$TABLE table is up to date" 0
    exit 0
else
    report "$TABLE table update is needed" 2
    if [ "$1" == "CHECK" ]; then
        exit 0
    fi
    report "Following changes will be applied" 3
    echo ""
    echo "$ALTER_QUERY"
    echo ""
fi

# Install percona toolkit
/usr/src/k_framework/helpers/percona/percona_install.sh
if [ "$?" != "0" ]; then
    exit 1
fi

# Check if replication is present and if we can connect to it
check_replication_connection

if [ "$FORCE" == "0" ]; then
    echo "Execute $TABLE update procedure? (y/n)"
    read -t 60 EXECUTE_PROCEDURE
    if [ "$EXECUTE_PROCEDURE" != "y" ]; then
        report "Update procedure was cancelled" 3
        exit 0
    fi
fi

if [ "$HOSTNAME" != "dev.kolmisoft.com" ]; then
    # Make backup (free space is checked while dumping database)
    dump_db_table "$TABLE" "$BACKUP_PATH"

    STATUS="$?"
    if [ "$STATUS" != "0" ] && [ "$STATUS" != "3" ]; then
        report "Will not proceed percona $TABLE update as backup creation failed" 1
        exit 1
    fi
fi

report "Starting $TABLE table update procedure. This may take some time..." 0

# Drop insert_date trigger
/usr/bin/mysql -h "$DB_HOST" -u "$PERCONA_DB_USERNAME" --password="$PERCONA_DB_PASSWORD" $DB_NAME -e "DROP TRIGGER insert_date" &> /dev/null

if [ "$PERCONA_DB_PASSWORD" == "" ]; then
    PERCONA_PASS_CMD=""
else
    PERCONA_PASS_CMD="--password=$PERCONA_DB_PASSWORD"
fi

# Do table update
pt-online-schema-change --progress=time,30 --execute --print --nocheck-replication-filters --user="$PERCONA_DB_USERNAME" $PERCONA_PASS_CMD --alter "$ALTER_QUERY" D="$DB_NAME",t=$TABLE > $PERCONA_LOG 2>&1

STATUS=`grep -F 'Successfully altered' $PERCONA_LOG | wc -l`

report "Script started: $START_DATE" 3
report "Script ended: `date`" 3

if [ "$STATUS" == "1" ]; then
    report "$TABLE table percona update was successful" 0
    m2 reload &> /dev/null
else
    report "Failed to update $TABLE table with percona. You can find DB backup in $BACKUP_PATH" 1
    report "Check $PERCONA_LOG for more information" 3
    exit 1
fi