#! /bin/sh
#
#   Author: Ricardas Stoma
#   Year:   2017
#   About:  Script checks if AWS aurora database is accessible

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

LOG_FILE=/var/log/m2/m2_aws_connection.log
AUTH_SERVER=https://support.kolmisoft.com/api/aws_aurora_credentials
AUTH_SERVER_RESPONSE=/tmp/m2/aws_response

# AWS aurora database variables
AWS_RO_HOSTNAME=
AWS_HOSTNAME=
AWS_USERNAME=
AWS_PASSWORD=
AWS_DATABASE=
AWS_PORT=

log_message()
{
    echo $1
    echo "`date` $1" >> $LOG_FILE
}

delete_aws_details_from_conflines()
{
    /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "DELETE FROM conflines WHERE name = 'AWS_DB_Host_read'" &> /dev/null
    /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "DELETE FROM conflines WHERE name = 'AWS_DB_Host_write'" &> /dev/null
    /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "DELETE FROM conflines WHERE name = 'AWS_DB_Username'" &> /dev/null
    /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "DELETE FROM conflines WHERE name = 'AWS_DB_Password'" &> /dev/null
    /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "DELETE FROM conflines WHERE name = 'AWS_DB_Name'" &> /dev/null
    /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "DELETE FROM conflines WHERE name = 'AWS_DB_Port'" &> /dev/null
}

# set mysql database connection variables
set_database_variables

# get aws variables from conflines
AWS_RO_HOSTNAME=`/usr/bin/mysql -sN -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "SELECT value FROM conflines WHERE name = 'AWS_DB_Host_read' LIMIT 1"`
AWS_HOSTNAME=`/usr/bin/mysql -sN -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "SELECT value FROM conflines WHERE name = 'AWS_DB_Host_write' LIMIT 1"`
AWS_USERNAME=`/usr/bin/mysql -sN -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "SELECT value FROM conflines WHERE name = 'AWS_DB_Username' LIMIT 1"`
AWS_PASSWORD=`/usr/bin/mysql -sN -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "SELECT value FROM conflines WHERE name = 'AWS_DB_Password' LIMIT 1"`
AWS_DATABASE=`/usr/bin/mysql -sN -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "SELECT value FROM conflines WHERE name = 'AWS_DB_Name' LIMIT 1"`
AWS_PORT=`/usr/bin/mysql -sN -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "SELECT value FROM conflines WHERE name = 'AWS_DB_Port' LIMIT 1"`

# check if connection to aws aurora database is successful
/usr/bin/mysql --connect-timeout=10 -h $AWS_HOSTNAME --port=$AWS_PORT $AWS_DATABASE -u $AWS_USERNAME --password="$AWS_PASSWORD" -e "USE $AWS_DATABASE" &> /dev/null
if [ "$?" != "1" ]; then
    log_message "Successfully connected to AWS Aurora database" 0
    # create calls_old structure (just to be sure)
    CALLS_OLD_STRUCTURE=`/usr/bin/mysql -sN -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e 'SHOW CREATE TABLE calls_old' | sed 's|calls_old||'`
    /usr/bin/mysql --connect-timeout=10 -h $AWS_HOSTNAME --port=$AWS_PORT $AWS_DATABASE -u $AWS_USERNAME --password="$AWS_PASSWORD" -e "$CALLS_OLD_STRUCTURE" &> /dev/null
else
    # failed to connect, try to retrieve connection details from authetincation server
    log_message "Could not connect to AWS Aurora database" 1
    log_message "Login details" 3
    log_message "Read hostname: $AWS_RO_HOSTNAME"
    log_message "Write Hostname: $AWS_HOSTNAME"
    log_message "Username: $AWS_USERNAME"
    log_message "Database: $AWS_DATABASE"
    log_message "Port: $AWS_PORT"
    log_message "Trying to retrieve login information from $AUTH_SERVER"

    # get data
    echo "" > $AUTH_SERVER_RESPONSE
    wget "$AUTH_SERVER" -q -O $AUTH_SERVER_RESPONSE

    # parse data
    ERROR=`cat $AUTH_SERVER_RESPONSE | jq '.error_message' | sed 's|"||g'`
    AWS_RO_HOSTNAME=`cat $AUTH_SERVER_RESPONSE | jq '.host' | sed 's|"||g'`
    AWS_HOSTNAME=`cat $AUTH_SERVER_RESPONSE | jq '.write_host' | sed 's|"||g'`
    AWS_USERNAME=`cat $AUTH_SERVER_RESPONSE | jq '.username' | sed 's|"||g'`
    AWS_PASSWORD=`cat $AUTH_SERVER_RESPONSE | jq '.password' | sed 's|"||g'`
    AWS_DATABASE=`cat $AUTH_SERVER_RESPONSE | jq '.database' | sed 's|"||g'`
    AWS_PORT=`cat $AUTH_SERVER_RESPONSE | jq '.port' | sed 's|"||g'`

    # clear file
    echo "" > $AUTH_SERVER_RESPONSE

    if [ "$ERROR" != "null" ]; then
        log_message "Can't retrieve AWS Aurora database login details:"
        log_message "$ERROR"
        # remove records from conflines
        delete_aws_details_from_conflines
        exit 1
    fi

    log_message "Retrieved login details:"
    log_message "Read hostname: $AWS_RO_HOSTNAME"
    log_message "Write hostname: $AWS_HOSTNAME"
    log_message "Username: $AWS_USERNAME"
    log_message "Database: $AWS_DATABASE"
    log_message "Port: $AWS_PORT"

    # check if connection to aws aurora database is successful
    log_message "Trying to connect to to AWS Aurora database"
    log_message "/usr/bin/mysql --connect-timeout=10 -h $AWS_HOSTNAME --port=$AWS_PORT $AWS_DATABASE -u $AWS_USERNAME --password=xxxxxxxx -e 'USE $AWS_DATABASE'"
    /usr/bin/mysql --connect-timeout=10 -h $AWS_HOSTNAME --port=$AWS_PORT $AWS_DATABASE -u $AWS_USERNAME --password="$AWS_PASSWORD" -e "USE $AWS_DATABASE" &> /dev/null
    if [ "$?" != "1" ]; then
        log_message "Successfully connected to AWS Aurora database" 0
        # remove records from conflines
        delete_aws_details_from_conflines
        # insert new records
        /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "INSERT INTO conflines(value, name) VALUES('$AWS_RO_HOSTNAME', 'AWS_DB_Host_read')" &> /dev/null
        /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "INSERT INTO conflines(value, name) VALUES('$AWS_HOSTNAME', 'AWS_DB_Host_write')" &> /dev/null
        /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "INSERT INTO conflines(value, name) VALUES('$AWS_USERNAME', 'AWS_DB_Username')" &> /dev/null
        /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "INSERT INTO conflines(value, name) VALUES('$AWS_PASSWORD', 'AWS_DB_Password')" &> /dev/null
        /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "INSERT INTO conflines(value, name) VALUES('$AWS_DATABASE', 'AWS_DB_Name')" &> /dev/null
        /usr/bin/mysql -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e "INSERT INTO conflines(value, name) VALUES('$AWS_PORT', 'AWS_DB_Port')" &> /dev/null
        # create calls_old structure
        CALLS_OLD_STRUCTURE=`/usr/bin/mysql -sN -h $DB_HOST -u $DB_USERNAME --password="$DB_PASSWORD" $DB_NAME -e 'SHOW CREATE TABLE calls_old' | sed 's|calls_old||'`
        /usr/bin/mysql --connect-timeout=10 -h $AWS_HOSTNAME --port=$AWS_PORT $AWS_DATABASE -u $AWS_USERNAME --password="$AWS_PASSWORD" -e "$CALLS_OLD_STRUCTURE" &> /dev/null
    else
        # still can't connect to AWS aurora database
        log_message "Could not connect to AWS Aurora database" 1
        # remove records from conflines
        delete_aws_details_from_conflines
    fi
fi