#!/bin/bash
############################################################################
# (C) Copyright 2016-2017 Hewlett Packard Enterprise Development LP.
# @(#) Serviceguard Easy Deployment for Microsoft SQL Server on Linux.
# @(#) Product Name                :  HPE Serviceguard extension for Microsoft SQL Server. SGeMSSQL
# @(#) Product Version             :  A.12.20.00
# @(#) Patch Name                  :  
#%%DO_NOT_MODIFY_TEXT%%
#
############################################################################

# Globals Used in Discovering and Building package configuration.
typeset MSSQL_ADMIN=""
typeset MSSQL_PASSWORD=""
typeset DATABASE_NAME=""
typeset SPECIFIED_DATABASE_NAME_ARRAY=""
typeset IP_ADDRESS=""
typeset IP_ADDRESS_ARRAY=""
typeset IP_SUBNET=""
typeset IP_SUBNET_ARRAY=""
typeset EMAIL_ID=""
typeset DEP_MODEL=""
typeset MNP_PKG_NAME=""
typeset DB_INSTANCE_PKG_NAME=""
typeset FOUND_DATABASE_NAME_ARRAY=""
typeset AOFI_ACTIVE_PASSIVE_PKG_NAME=""
typeset MSSQL_PORT=""
typeset HOST_PORT=""
typeset STANDARD_WORKLOAD_NAME=""
typeset STANDARD_WORKLOAD_TYPE=""
typeset AOAI_IN_FILE_NAME=""

# Globals used for AOFI Active / Passive
typeset CRITICAL_DATABASE_NAME_ARRAY
typeset NON_CRITICAL_DATABASE_NAME_ARRAY
typeset FS_NAME_ARRAY
typeset FS_DIRECTORY_ARRAY
typeset FS_TYPE_ARRAY
typeset VG_ARRAY
typeset AOFI_DEP_CMD_ARGS
typeset INSTANCE_NAME_ARRAY
typeset -a mssql_nodes
typeset -a valid_aofi_nodes
typeset -a invalid_aofi_nodes
typeset -a invalid_availability_group

typeset DATA_FILE=""
typeset TRANSACTION_LOG_FILE=""
typeset VG_NAME_DATA=""
typeset VG_NAME_LOG=""
typeset LVOL_NAME_DATA=""
typeset LVOL_NAME_LOG=""
typeset FS_DIR_DATA=""
typeset FS_DIR_LOG=""
typeset VG_NAME_DATA_FS_TYPE=""
typeset VG_NAME_LOG_FS_TYPE=""

# Globals used for AG
typeset AG_NAME_ARRAY
typeset AG_NAME
typeset AG_ID
typeset REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT
typeset AG_DB_NAME_ARRAY
typeset AG_DB_NAME
typeset AG_NODE_NAME_ARRAY
typeset AG_NODE_NAME_REPLICA_ID
typeset ENDPOINT_URL
typeset AVAILABILITY_MODE
typeset SEEDING_MODE
typeset MASTER_NODE
typeset query_output_for_deployment
typeset commit_string="required_synchronized_secondaries_to_commit"
typeset C_COMMIT_STRING="REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT"
typeset AG_MNP_PKG_NAME
typeset AOAI_WRITE_PKG
typeset REPLICA_ROLE_GR_NAME
typeset REPLICA_ROLE_SERVICE_NAME
typeset AG_MNP_PKG_DEPENDENCY_NAME
typeset AOAI_WRITE_PKG_DEPENDENCY_NAME
typeset AG_MNP_CONF_FILE
typeset IP_CONF_FILE


# Globals used for reading values from a file for AOAI deployment
typeset IN_FILE_SC_COMMIT
typeset IN_AG_NAME
typeset IN_FILE_NODE_NAMES
typeset IN_FILE_AVAILABILITY_MODE

# Globals used for error message format
SGMGR_ERROR_MSG_PREFIX="msgtype:ERROR|message="
SGMGR_WARNING_MSG_PREFIX="msgtype:WARNING|message="

typeset -i uflag=0
typeset -i pflag=0
typeset -i dflag=0
typeset -i iflag=0
typeset -i sflag=0
typeset -i eflag=0
typeset -i zflag=0
typeset -i mflag=0
typeset -i wflag=0
typeset -i lflag=0
typeset -i gflag=0
typeset -i fflag=0
typeset -i aflag=0
typeset -i number_flag=1

typeset VCLVIEW_CACHE

. /etc/cmcluster.conf
typeset ED_CONF_FILE_DIR=$SGCONF/mssql_easy
typeset MAIL_TEXT=$ED_CONF_FILE_DIR/mail
typeset MAIL_TEXT_AG=$ED_CONF_FILE_DIR/mail_ag
typeset ED_ERROR_FILE=$ED_CONF_FILE_DIR/ed_errors.log
typeset MSSQL_HOME="/var/opt/mssql"

SGEMSSQL_UTILS=${SGCONF}/scripts/sgemssql/mssql_utils.sh
export SGEMSSQL_UTILS
if [[ -f ${SGEMSSQL_UTILS} ]]; then
    . ${SGEMSSQL_UTILS}
    if (( $? != 0 ))
    then
        echo "ERROR: Unable to source SGeMSSQL utility functions file: ${SGEMSSQL_UTILS}"
        exit 1
    fi
else
    echo "ERROR: Unable to find SGeMSSQL utility functions file: ${SGEMSSQL_UTILS}"
    exit 1
fi

###############################################################################
# Function: cache_clview
# Description: Cache the cmviewcl s config output for later use.
# If the cmviewcl command fails, try three times.
###############################################################################
function cache_clview
{
   typeset -i counter=3
   typeset -i clview_retry_interval=30

   if [[ -n "$VCLVIEW_CACHE" ]]; then
        return
   fi

   VCLVIEW_CACHE=`${SGSBIN}/cmviewcl -v -f line -s config 2>&1`
   if [[ $? -ne 0 ]]; then
      while [[ $counter -gt 0 ]]
      do
         #sleep for retry interval
         sleep $clview_retry_interval
         VCLVIEW_CACHE=`${SGSBIN}/cmviewcl -v -f line -s config 2>&1`
         if [[ $? -eq 0 ]]; then
            break
         fi
         counter=`expr $counter - 1`
      done
      if [[ $counter -eq 0 ]]; then
         echo "Execution of cmviewcl -v -f line failed"
         echo "$VCLVIEW_CACHE"
         echo "Exiting"
         exit 1
      fi
   fi
}

# Get the list of all the nodes in which SQL Server is Installed. 
function get_mssql_node_names
{
    typeset _output=$mssql_tmp_dir/cmexec.${0##*/}.get_mssql_node_names_$$
    typeset _stderr=$mssql_tmp_dir/cmexec.err.${0##*/}.get_mssql_node_names_$$

    typeset -a cluster_nodes
    #Get all the cluster node names
    cluster_nodes=`echo "${VCLVIEW_CACHE}" | awk -F= '/^node:[^|]*\|name=/ {print $2}'`
    cluster_nodes=($(printf "%s\n" "${cluster_nodes[@]}"));

    #Check if mssql is installed on the nodes obtained
    for node in "${cluster_nodes[@]}"
    do
        do_cmexec $node $_output $_stderr rpm -qa | grep mssql-server >> /dev/null
        if [[ $? -eq 0 ]]; then
            mssql_nodes+=("$node")
        fi
    done
}

function usage {

    echo "Usage: cmdeploymssql -U login -P password -M Deployment model -D Database Name -I IP-Address -S Subnet -E E-Mail ID"
    echo "      -U login            : Specify the User name of Microsoft SQL Server Login"
    echo "      -P password         : Specify the Password for the user "-U" to login to Microsoft SQL Server"
    echo "      -M Deployment model : Specify the Deployment Model"
    echo "                            1: Always on Availability Instance Active / Active"
    echo "                            2: Always on Failover Instance Active / Passive"
    echo "      -D Database Name    : Specify the Database Name in case of Deployment Model 2"
    echo "                            Specify the list of critical database's Name in case of Deployment Model 2"
    echo "      -I IP-Address       : Specify the IP Address to access the Database"
    echo "      -S Subnet           : Specify the Subnet for the IP Address"
    echo "      -E E-Mail ID        : Specify the E-Mail ID for notifications"
    echo "      -A AG_NAME          : Specify the name of Availability Group in case of Deployment Model 1"
    echo "      -h Help             : Help"
    echo ""
    echo "Ex: cmdeploymssql -U sa -P mssql -M 1 -I 10.213.96.234" \
          "-S 10.213.96.0 -I 10.213.96.233 -S 10.213.96.0 -A AG_NAME -E notifications@myorg.com "
    echo "Ex: cmdeploymssql -U sa -P mssql -M 2 -D HR_DB -D SALES_DB -I 10.213.96.234 -S 10.213.96.0 -E notifications@myorg.com "
    exit 1
}

function display_aofi {

    echo "sgemssql/dbinstance/MSSQL_HOME=C:\data"
    echo "sgemssql/dbinstance/MSSQL_ADMIN=$MSSQL_ADMIN"
    echo "sgemssql/dbinstance/MSSQL_PASSWORD=$MSSQL_PASSWORD"
    echo "sgemssql/dbinstance/MSSQL_PORT=$MSSQL_PORT"

    for I in ${INSTANCE_NAME_ARRAY[@]}
    do
        echo "name=$I"_Instance""
        echo "package_description=$I" Database""
        echo "sgemssql/dbinstance/INSTANCE_NAME:$INSTANCE_NAME|sgemssql/dbinstance/INSTANCE_NAME=$I"
    done
}

function populate_instance
{
    instance_name=$1
    found=0

    if [ -z $INSTANCE_NAME_ARRAY ]
    then
        INSTANCE_NAME_ARRAY=($instance_name)
        return
    fi

    for I in ${INSTANCE_NAME_ARRAY[@]}
    do
        if [[ $I = $instance_name ]]
        then
            # Instance is already present. Do nothing
            found=1
            break
        fi
    done

    if ((found == 0 ))
    then
        INSTANCE_NAME_ARRAY+=($instance_name)
    fi
}

function get_vgname_log {

    mnt_output=`df -P "$FS_DIR_LOG" | tail -1 | awk '{print $NF}'`
    VG_NAMELVOL_LOG=`df -P $mnt_output 2>> /dev/null | tail -1 | cut -d " " -f 1`

    # If Database is not online or is not present on shared disk,
    # VG_NAMELVOL_DATA will not be populated. Or it could be in a directory behind
    # so loop through until we exhaust the path.
    while [[ -z $VG_NAMELVOL_LOG ]]
    do
        FS_DIR_LOG=$(echo $FS_DIR_LOG | sed 's/\/[^\/]*$//')
        mnt_output=`df -P "$FS_DIR_LOG" | tail -1 | awk '{print $NF}'`
        VG_NAMELVOL_LOG=`df -P $mnt_output 2>> /dev/null | tail -1 | cut -d " " -f 1`

        if [[ -z $FS_DIR_LOG ]]
        then
            return;
        fi
    done

    VG_NAME_LOG=`lvs --noheadings --separator : $VG_NAMELVOL_LOG | cut -d ":" -f 2`
    VG_NAME_LOG_FS_TYPE=`mount -v | grep "$VG_NAMELVOL_LOG" | cut -d " " -f 5`
    LVOL_NAME_LOG=`lvdisplay -c $VG_NAMELVOL_LOG | cut -d ":" -f 1 | sed 's/ //g'`

}

function get_vgname_data {

    mnt_output=`df -P "$FS_DIR_DATA" | tail -1 | awk '{print $NF}'`
    VG_NAMELVOL_DATA=`df -P $mnt_output 2>> /dev/null | tail -1 | cut -d " " -f 1`

    # If Database is not online or is not present on shared disk,
    # VG_NAMELVOL_DATA will not be populated. Or it could be in a directory behind
    # so loop through until we exhaust the path.
    while [[ -z $VG_NAMELVOL_DATA ]]
    do
        FS_DIR_DATA=$(echo $FS_DIR_DATA | sed 's/\/[^\/]*$//')
        mnt_output=`df -P "$FS_DIR_DATA" | tail -1 | awk '{print $NF}'`
        VG_NAMELVOL_DATA=`df -P $mnt_output 2>> /dev/null | tail -1 | cut -d " " -f 1`

        if [[ -z $FS_DIR_DATA ]]
        then
            return;
        fi
    done

    VG_NAME_DATA=`lvs --noheadings --separator : $VG_NAMELVOL_DATA | cut -d ":" -f 2`
    LVOL_NAME_DATA=`lvdisplay -c $VG_NAMELVOL_DATA | cut -d ":" -f 1 | sed 's/ //g'`
    VG_NAME_DATA_FS_TYPE=`mount -v | grep "$VG_NAMELVOL_DATA" | cut -d " " -f 5`

}

function discover_information_aofi {

    #Reading the database name in to a variable
    DBNAME="$1"

    if [[ -z $DBNAME ]]; then
        output=`$SQLCMD -S $HOST_PORT -U $MSSQL_ADMIN -P $MSSQL_PASSWORD -Q "set nocount on select d.name as 'database', \
             mdf.physical_name as 'mdf_file', ldf.physical_name as 'log_file' from sys.databases d inner \
             join sys.master_files mdf on d.database_id = mdf.database_id and mdf.[type] = 0 inner join \
             sys.master_files ldf on d.database_id = ldf.database_id and ldf.[type] = 1 WHERE d.state = 0 AND d.name \
             NOT IN ('master', 'tempdb', 'model', 'msdb');" -W -h -1`
    else
        output=`$SQLCMD -S $HOST_PORT -U $MSSQL_ADMIN -P $MSSQL_PASSWORD -Q "set nocount on select d.name as 'database', \
                 mdf.physical_name as 'mdf_file', ldf.physical_name as 'log_file' from sys.databases d inner \
                 join sys.master_files mdf on d.database_id = mdf.database_id and mdf.[type] = 0 inner join \
                 sys.master_files ldf on d.database_id = ldf.database_id and ldf.[type] = 1 WHERE d.state = 0 AND d.name \
                  NOT IN ('master', 'tempdb', 'model', 'msdb') AND d.name IN ('$DBNAME');" -W -h -1`
    fi

    while read -r line
    do
        INSTANCE_NAME=`echo $line | awk '{print $1}'`

        # Code to discover only what you want
        if [[ $# = 1 ]] && [[ $1 != $INSTANCE_NAME ]]
        then
            continue;
        fi

        NUM_ENTRIES=`echo $line | wc -w`

        DATA_FILE=`echo $line | awk '{print $2}'`
        data_file_lx=$(echo $DATA_FILE | sed -e 's/C://g; s/c://g; s/\\/\//g')
        FS_DIR_DATA=$(echo $data_file_lx | sed 's/\/[^\/]*$//')

        get_vgname_data $FS_DIR_DATA
        if [[ -z $FS_DIR_DATA ]]
        then
            continue;
        fi

        TRANSACTION_LOG_FILE=`echo $line | awk '{print $3}'`
        transaction_log_lx=$(echo $TRANSACTION_LOG_FILE | sed -e 's/C://g; s/c://g; s/\\/\//g')
        FS_DIR_LOG=$(echo $transaction_log_lx | sed 's/\/[^\/]*$//')

        get_vgname_log $FS_DIR_LOG

        populate_instance $INSTANCE_NAME

    done <<<"$output"

    # If no DBs were found on shared storage, we will not display any information
    # and also exit with 1. This exit value is required for SGManager as it relies
    # on exit value of cmdeploymssql with -z option
    if [[ -z $INSTANCE_NAME_ARRAY ]]
    then
        echo $SGMGR_ERROR_MSG_PREFIX"No databases are available or online on the SQL Server."
        exit 1
    fi

    if [[ $zflag = 1 ]];
    then
        display_aofi
    fi
}

#This function is used to retrieve the valid AG names from the DB and
#filters out the AGs which are invalid.
function get_valid_availability_groups_from_db
{
  output="$1"
  while read -r line
  do
     AG_NAME=`echo $line | awk '{print $1}'`
     if ! `echo "${invalid_availability_group[@]}" | grep -qw $AG_NAME`
     then
         if ! `echo "${AG_NAME_ARRAY[@]}" | grep -qw $AG_NAME`
         then
             AG_NAME_ARRAY+=("$AG_NAME")
         fi
      fi
   done <<<"$output"
}

function display_ag
{
    typeset _in_AG_NAME=$1

    echo "AG_NAME=$_in_AG_NAME"
    echo "AG_NAME:$_in_AG_NAME|AG_ID=$AG_ID"
    echo "AG_NAME:$_in_AG_NAME|$C_COMMIT_STRING=$REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT"
    echo "AG_NAME:$_in_AG_NAME|SEEDING_MODE=$SEEDING_MODE"
    echo "AG_NAME:$_in_AG_NAME|MASTER_NODE=$MASTER_NODE"

    for name in ${AG_DB_NAME_ARRAY[@]}
    do
        echo "AG_NAME:$_in_AG_NAME|Database_name=$name"
    done

    R=0
    while (($R < ${#AG_NODE_NAME_ARRAY[*]}))
    do
        echo "AG_NAME:$_in_AG_NAME|NODE_NAME=${AG_NODE_NAME_ARRAY[$R]}"
        echo "AG_NAME:$_in_AG_NAME|NODE_NAME:${AG_NODE_NAME_ARRAY[$R]}|REPLICA_ID=${AG_NODE_NAME_REPLICA_ID_ARRAY[$R]}"
        echo "AG_NAME:$_in_AG_NAME|NODE_NAME:${AG_NODE_NAME_ARRAY[$R]}|ENDPOINT_URL=${ENDPOINT_URL_ARRAY[$R]}"
        echo "AG_NAME:$_in_AG_NAME|NODE_NAME:${AG_NODE_NAME_ARRAY[$R]}|AVAILABILITY_MODE=${AVAILABILITY_MODE_ARRAY[$R]}"
        (( R = R + 1 ))
    done
}

function process_aoai_cmd_output
{
    typeset -i capture_once=0
    typeset output=$1
    typeset ag_name=$2
    AG_NAME=$ag_name
    # Get the Master or Primary replica node name.
    MASTER_NODE=`echo "$output" | grep -w 'PRIMARY' | grep -w $AG_NAME | tail -1 | awk '{print $5}'`

    if [[ -n $MASTER_NODE ]]
    then
        while read -r line
        do
            temp_AG_NAME=`echo $line | awk '{print $1}'`
            if [[ "$AG_NAME" != "$temp_AG_NAME" ]]; then
                 continue;
            fi

            if [[ $capture_once = 0 ]]; then
                AG_NAME=`echo $line | awk '{print $1}'`
                AG_ID=`echo $line | awk '{print $2}'`
                REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT=`echo $line | awk '{print $3}'`
                SEEDING_MODE=`echo $line | awk '{print $9}'`
                capture_once=1
            fi

            DB_NAME=(`echo $line | awk '{print $4}'`)
            if ! `echo "${AG_DB_NAME_ARRAY[@]}" | grep -qw $DB_NAME`
                then
                        AG_DB_NAME_ARRAY+=("$DB_NAME")
            fi
            NODE_NAME=(`echo $line | awk '{print $5}'`)
            if ! `echo "${AG_NODE_NAME_ARRAY[@]}" | grep -qw $NODE_NAME`
                then
                    AG_NODE_NAME_ARRAY+=("$NODE_NAME")
                    ENDPOINT_URL_ARRAY+=(`echo $line | awk '{print $7}'`)
                    AVAILABILITY_MODE_ARRAY+=(`echo $line | awk '{print $8}'`)
                    REPLICA_ID=(`echo $line | awk '{print $6}'`)
                    if ! `echo "${AG_NODE_NAME_REPLICA_ID_ARRAY[@]}" | grep -qw $REPLICA_ID`
                    then
                        AG_NODE_NAME_REPLICA_ID_ARRAY+=("$REPLICA_ID")
                    fi
            fi
        done <<<"$output"
    fi
    
}

###############################################################################
# Function: find_if_any_invalid_availability_groups
# Description: This function marks any invalid AGs configured in the cluster.
# The function first gathers a list of AG names which are not already packaged.
# From this list, an AG is marked invalid if
# 1. all the replica nodes of the AG are not part of the cluster [OR]
# 2. AG does not meet the criteria of minimum two Synchronous replicas.
###############################################################################
function find_if_any_invalid_availability_groups
{
    agoutput="$1"
    # Get availability_groups from configured packages
    invalid_availability_group=`echo "${VCLVIEW_CACHE}" | grep "sgemssql/mssqlaoai/availability_group=" \
    | awk -F= '{print $2}'`

    #Get all the cluster node names
    cluster_nodes=`echo "${VCLVIEW_CACHE}" | awk -F= '/^node:[^|]*\|name=/ {print $2}'`
    cluster_nodes=($(printf "%s\n" "${cluster_nodes[@]}" | sort -u));

    # AG's whose nodes are not part of the cluster.
    while read -r line
    do
        agName=`echo $line | awk '{print $1}'`
        if ! `echo "${invalid_availability_group[@]}" | grep -qw $agName`
        then

            # Finding AG's whose nodes are not part of the cluster.
            ag_node=(`echo $line | awk '{print $5}'`)
            if ! `echo "${cluster_nodes[@]}" | grep -qw $ag_node`
            then
                invalid_availability_group+=("$agName")
            fi

            # Verifying min 2 SYNCHRONOUS replica is configured
            if `echo $line | grep -qw 'PRIMARY'`
            then
                TEMP_DB_NAME=`echo $line | awk '{print $4}'`
                NO_SYNCH_NODES=`echo "$agoutput" | grep -w "$agName" | grep -w "$TEMP_DB_NAME" \
                | grep -w "SYNCHRONOUS_COMMIT"| wc -l`
                if [[ $NO_SYNCH_NODES -lt 2 ]]
                then
                    invalid_availability_group+=("$agName")
                fi
            fi

        fi

    done <<<"$agoutput"

}

function validate_ag_output
{
    output=$1
    find_if_any_invalid_availability_groups "$output"
    get_valid_availability_groups_from_db "$output"

    if [[ -n $IN_AG_NAME ]];
    then
        if ! `echo "${invalid_availability_group[@]}" | grep -qw $IN_AG_NAME`
        then
            if ! `echo "${AG_NAME_ARRAY[@]}" | grep -qw $IN_AG_NAME`
            then
                echo "ERROR:The availability group $IN_AG_NAME does not exist in SQL Server."
                exit 5
            fi
        fi

        if `echo "${invalid_availability_group[@]}" | grep -qw $IN_AG_NAME`
        then
            echo "ERROR: Invalid configuration. A minimum of two SYNCHRONOUS_COMMIT "\
                 "replicas must be configured in Availability Group $IN_AG_NAME"
            exit 5
        fi

        if ! `echo "${AG_NAME_ARRAY[@]}" | grep -qw $IN_AG_NAME`
        then
            echo "ERROR:The availability group $IN_AG_NAME is deployed already."
            exit 5
        fi
    fi

    if [ ${#AG_NAME_ARRAY[@]} -eq 0 ];
    then
        echo "ERROR:No Availability Groups are configured on the SQL Server."
        exit 4
    fi
}

function discover_information_ag
{

    displayed_info=0
    add_ag_to_array=1
    add_ag_db_to_array=1
    typeset -i ret=0
    typeset -i i=0

    # If i'm not PRIMARY exit, let the discovery happen from PRIMARY Replica
    check_if_local_node_is_primary_replica
    ret=$?
    if (( $ret != 0 )); then
        # Passing 2 here to distinguish the reason for failure in the caller.
        exit 2
    fi

    output=`$SQLCMD -U $MSSQL_ADMIN -P $MSSQL_PASSWORD -Q \
            "set nocount on SELECT ag.name, ag.group_id, ag.$commit_string, \
            d.database_name, r.replica_server_name, r. replica_id, r.endpoint_url, \
            r.availability_mode_desc, r.seeding_mode_desc, m.role_desc \
            from master.sys.availability_groups ag inner \
            join master.sys.availability_replicas r on ag.group_id = r.group_id \
            inner join master.sys.availability_databases_cluster d on ag.group_id = d.group_id \
            inner join master.sys.dm_hadr_availability_replica_states m on r.replica_id = m.replica_id;" -W -h -1`


    if [[ $gflag = 1 ]];
    then
        echo "$output"
        exit 0
    fi

    validate_ag_output "$output"
    while (($i < ${#AG_NAME_ARRAY[*]}))
    do
       process_aoai_cmd_output "$output" ${AG_NAME_ARRAY[$i]}
       if [[ $zflag = 1 && -n $MASTER_NODE ]];
       then
            display_ag ${AG_NAME_ARRAY[$i]}
            unset_aoai_params
       fi
       (( i = i + 1 ))
    done
}

function unset_aoai_params
{
   unset AG_DB_NAME_ARRAY
   unset AG_NODE_NAME_ARRAY
   unset ENDPOINT_URL_ARRAY
   unset AVAILABILITY_MODE_ARRAY
   unset AG_NODE_NAME_REPLICA_ID_ARRAY
}

function discover_information {

    typeset -i aoai_enabled

    # Check to see if AG is enabled.
    is_aoai_enabled
    aoai_enabled=$?
    if [ $aoai_enabled = 0 ]
    then
        discover_information_ag
    else
        discover_information_aofi
    fi
}

function discover_information_from_all_nodes {

    typeset -i _ret=0
    typeset -i _ret_final=1
    typeset is_PRIMARY_FOUND="NO"

    touch "$ED_ERROR_FILE"

    _node_name=`echo "${VCLVIEW_CACHE}" | awk -F= '/^node:[^|]*\|name=/ {print $2}'`
    for node in $_node_name
    do
        out=$($SGSBIN/cmexec $node $SGSBIN/cmdeploymssql -z -U $MSSQL_ADMIN -P $MSSQL_PASSWORD -l 2>&1)
        _ret=$?
        if [[ $_ret -eq 0 && -n "$out" ]]; then
            is_PRIMARY_FOUND="YES"
            _ret_final=0
            echo "$out"
        else
            case "$_ret" in
                1)
                    is_PRIMARY_FOUND="YES"
                    echo $SGMGR_ERROR_MSG_PREFIX"$node:Microsoft SQL Server is not running." >> $ED_ERROR_FILE
                ;;
                3)
                    is_PRIMARY_FOUND="YES"
                    echo $SGMGR_ERROR_MSG_PREFIX"$node:Invalid credentials for the user $MSSQL_ADMIN."\
                         "Provide valid username and password." >> $ED_ERROR_FILE
                ;;
                4)
                    is_PRIMARY_FOUND="YES"
                    echo $SGMGR_ERROR_MSG_PREFIX"$node:No Valid Availability Groups are configured"\
                        "on the SQL Server." >> $ED_ERROR_FILE
                ;;
            esac
        fi
    done

    if (( $_ret_final == 1 )); then
        if [[ $is_PRIMARY_FOUND == "NO" ]]
        then
            echo $SGMGR_ERROR_MSG_PREFIX"Currently there is no valid Availability Groups"\
                "with PRIMARY replica running to deploy." >> $ED_ERROR_FILE
        fi

        cat $ED_ERROR_FILE
        rm -f $ED_ERROR_FILE
        exit 1
    fi

    rm -f $ED_ERROR_FILE
}

function discover_deployment_model {

    typeset -i aoai_enabled

    # Check to see if AG is enabled.
    is_aoai_enabled
    aoai_enabled=$?

    if [ $aoai_enabled = 0 ]
    then
        DEPLOYMENT_MODEL="Microsoft_SQL_Server_Availability_Group"
    else
        DEPLOYMENT_MODEL="Microsoft_SQL_Server_Failover_instance"
    fi
    echo "DEPLOYMENT_MODEL=$DEPLOYMENT_MODEL"
}

function parse_input_params {

    if [ "x$*" = "x" ];
    then
        usage
    fi

    while getopts :U:P:D:I:S:E:M:h:W:F:A:zlg opt
    do
        case $opt in
            U)
              MSSQL_ADMIN="$OPTARG"
              (( uflag = uflag + 1 ))
              ;;
            P)
              MSSQL_PASSWORD="$OPTARG"
              (( pflag = pflag + 1 ))
              ;;
            D)
              DATABASE_NAME="$OPTARG"
              if [ -z $SPECIFIED_DATABASE_NAME_ARRAY ]
              then
                  SPECIFIED_DATABASE_NAME_ARRAY=("$OPTARG")
              else
                  SPECIFIED_DATABASE_NAME_ARRAY+=("$OPTARG")
              fi
              (( dflag = dflag + 1 ))
              ;;
            I)
              IP_ADDRESS="$OPTARG"
              if [ -z $IP_ADDRESS_ARRAY ]
              then
                  IP_ADDRESS_ARRAY=("$OPTARG")
              else
                  IP_ADDRESS_ARRAY+=("$OPTARG")
              fi
              (( iflag = iflag + 1 ))
              ;;
            S)
              IP_SUBNET="$OPTARG"
              if [ -z $IP_SUBNET_ARRAY ]
              then
                  IP_SUBNET_ARRAY=("$OPTARG")
              else
                  IP_SUBNET_ARRAY+=("$OPTARG")
              fi
              (( sflag = sflag + 1 ))
              ;;
            E)
              EMAIL_ID="$OPTARG"
              (( eflag = eflag + 1 ))
              ;;
            M)
              DEP_MODEL="$OPTARG"
              (( mflag = mflag + 1 ))
              ;;
            W)
              STANDARD_WORKLOAD_NAME="$OPTARG"
              (( wflag = wflag + 1 ))
              ;;
            F)
               #This is a case of AOAI deployment from SG Manager and the parameters are
               #read from File input. Hence the appropriate flags are incremented.
               DEP_MODEL=1
               (( mflag = mflag +1 ))
               AOAI_IN_FILE_NAME="$OPTARG"
               (( fflag = fflag + 1))
               ;;
            A)
               IN_AG_NAME="$OPTARG"
               (( aflag = aflag + 1 ))
               ;;
            z)
              (( zflag = zflag + 1 ))
              ;;
            l)
              (( lflag = lflag + 1 ))
              ;;
            g)
              # Used during deployment of AOAI package.
              # This is an internal flag used to get the DB output from a primary node
              (( gflag = gflag + 1 ))
              ;;
            h)
              usage
              ;;
            *)
              usage
              echo "$OPTARG"
              ;;
        esac
    done
    ((switch_pos = OPTIND - 1))
    shift $switch_pos

    # This is used for SGMGR deployment of AOAI. Ensure that no other condition is checked before this.
    if [[ $fflag = 1 ]]
    then
        read_aoai_file_params
    fi

    if [[ $uflag = 0 ]] && [[ $zflag = 0 ]] ||
       [[ $uflag = 0 ]] && [[ $pflag = 1 ]];
    then
        echo "-U Parameter not specified:";
        echo "-U login : Specify the User name of Microsoft SQL Server Login"
        exit 1
    fi

    if [[ $uflag > 1 ]];
    then
        echo "-U Parameter can be specified only once.";
        exit 1
    fi

    if [[ $pflag = 0 ]] && [[ $zflag = 0 ]] ||
       [[ $pflag = 0 ]] && [[ $uflag = 1 ]];
    then
        echo "-P Parameter not specified:";
        echo "-P password : Specify the Password for the user "-U" to login to Microsoft SQL Server"
        exit 1
    fi

    if [[ $pflag > 1 ]];
    then
        echo "-P Parameter can be specified only once.";
        exit 1
    fi

    if [[ $iflag = 0 ]] && [[ $zflag = 0 ]];
    then
        echo "-I Parameter not specified:";
        echo "-I IP-Address : Specify the IP Address to access the Database"
        exit 1
    fi

    if [[ $sflag = 0 ]] && [[ $zflag = 0 ]];
    then
        echo "-S Parameter not specified:";
        echo "-S Subnet : Specify the Subnet for the IP Address"
        exit 1
    fi

    if [[ $eflag > 1 ]];
    then
        echo "-E Parameter can be specified only once.";
        exit 1
    fi

    if [[ $mflag = 0 ]] && [[ $zflag = 0 ]];
    then
        echo "-M Parameter not specified:";
        echo "-M Deployment model : Specify the Deployment Model"
        exit 1
    fi

    if [[ $mflag > 1 ]];
    then
        echo "-M Parameter can be specified only once.";
        exit 1
    fi

    if [[ $aflag = 0 ]] && [[ $DEP_MODEL = 1 ]];
    then
        echo "-A Parameter not specified:"
        echo "Specify the name of the availability group for deployment"
        exit 1
    fi

}

# Check if any of the package is configured with module sgemssql/mssqlinstance
function check_if_mnp_configured
{

    MNP_PKG_NAME=`get_mssql_server_package_name`
}

# check if MSSQL server DB package is configured
function check_if_aofi_pkg_configured
{
    DB_PKG_OUT=`$SGSBIN/cmviewcl -vf line -s config | grep -w \
                "module_name:sgemssql/mssqldbinstance|module_name=sgemssql/mssqldbinstance"`
    retval=$?
    if [[ $retval -eq 0 ]];
    then
        DB_INSTANCE_PKG_NAME=$(echo "$DB_PKG_OUT" | awk -F"|" '{print $1}' | awk -F: '{print $2}')
    fi
}

function add_fs_data
{
    echo "fs_name  /dev/$1/$2" >> $ED_CONF_FILE_DIR/$DB_INSTANCE_PKG_NAME.conf
    echo "fs_server "\"\" >> $ED_CONF_FILE_DIR/$DB_INSTANCE_PKG_NAME.conf
    echo "fs_directory $3" >> $ED_CONF_FILE_DIR/$DB_INSTANCE_PKG_NAME.conf
    echo "fs_type $4" >> $ED_CONF_FILE_DIR/$DB_INSTANCE_PKG_NAME.conf
    echo "fs_mount_opt "\"\" >> $ED_CONF_FILE_DIR/$DB_INSTANCE_PKG_NAME.conf
    echo "fs_umount_opt "\"\" >> $ED_CONF_FILE_DIR/$DB_INSTANCE_PKG_NAME.conf
    echo "fs_fsck_opt "\"\" >> $ED_CONF_FILE_DIR/$DB_INSTANCE_PKG_NAME.conf
}


function create_mssql_pkg
{
    MNP_PKG_NAME=Microsoft_SQL_Server

    rm -f $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
    output=`$SGSBIN/cmmakepkg -v 0 -m sgemssql/mssqlserver $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf`
    retval=$?

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to create $1"
        echo "$output"
        exit 1;
    fi

    # Search and replace Package Name Use Double Quotes to Expand Variable
    sed -i "/^package_name/c\package_name $MNP_PKG_NAME" $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf

    # Search and replace Package Description
    sed -i '/^package_description/c\package_description "Microsoft SQL Server"' $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf

    # Search and replace MSSQL Parameter MSSQL_PORT

    sed -i "/sgemssql\/mssqlserver\/MSSQL_PORT/c\sgemssql/mssqlserver/MSSQL_PORT $MSSQL_PORT" \
             $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf

    sed -i '/node_name/d' $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
    # Search and replace node_names

    for node in ${mssql_nodes[@]}
    do
        echo "node_name        $node" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
    done

    # Add E-Mail Address Field
    if [[ "X$EMAIL_ID" != "X" ]]; then
        echo "email_id $EMAIL_ID" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
    fi

    if [ -z $VG_ARRAY ]
    then
        return 0
    fi
    
    # Add LVM VG's
    for i in "${VG_ARRAY[@]}"; do
        echo "vg $i" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
    done

    # Add File System Data
    j=0
    for i in "${FS_NAME_ARRAY[@]}"; do
        echo "fs_name ${i}" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
        echo "fs_server "\"\" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
        echo "fs_directory ${FS_DIRECTORY_ARRAY[$j]}" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
        echo "fs_type ${FS_TYPE_ARRAY[$j]}" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
        echo "fs_mount_opt "\"\" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
        echo "fs_umount_opt "\"\" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
        echo "fs_fsck_opt "\"\" >> $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
        (( j = j + 1 ))
    done
    
    return 0
}

function validate_pkg_force
{
    output=`$SGSBIN/cmcheckconf -v -k -P $1`
    retval=$?

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to validate the $2"
        echo "$output"
        exit 1;
    fi
}

function apply_pkg_force
{
    output=`$SGSBIN/cmapplyconf -vfk -P $1`
    retval=$?

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to Configure the $2"
        echo "$output"
        exit 1;
    fi
}

function validate_pkg
{
    output=`$SGSBIN/cmcheckconf -v -P $1`
    retval=$?

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to validate the $2"
        echo "$output"
        exit 1;
    fi
}

function apply_pkg
{
    output=`$SGSBIN/cmapplyconf -vf -P $1`
    retval=$?

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to Configure the $2"
        echo "$output"
        exit 1;
    fi
}

function start_pkg
{
    output=`$SGSBIN/cmrunpkg $1`
    retval=$?

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to Start the $1"
        echo "$output"
        exit 1;
    fi
}

function enable_pkg
{
    output=`$SGSBIN/cmmodpkg -e $1`
    retval=$?

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to Enable global switching for $1"
        echo "$output"
        exit 1;
    fi
}

function discover_all_info_for_always_on_availability_groups
{
    get_mssql_node_names
    distribute_login_credentials_file $CREDENTIALS_FILE "${mssql_nodes[@]}"

    _node_name=`echo "${VCLVIEW_CACHE}" | awk -F= '/^node:[^|]*\|name=/ {print $2}'`
    for node in ${_node_name[@]}
    do
        query_output_for_deployment=`$SGSBIN/cmexec $node $SGSBIN/cmdeploymssql -z -U $MSSQL_ADMIN -P $MSSQL_PASSWORD -l -g`
        _ret=$?

        if (( $_ret == 0 )); then
            MASTER_NODE=`echo "$query_output_for_deployment" | grep -w 'PRIMARY' | grep -w $IN_AG_NAME | tail -1 | awk '{print $5}'`
            if [[ -n $MASTER_NODE ]]
            then
                break
            fi
        fi
    done
    case "$_ret" in
    1)
        echo $SGMGR_ERROR_MSG_PREFIX"Microsoft SQL Server should be running to Deploy MSSQL"\
             "Availability Group Workload."
        exit 1
    ;;
    2)
        echo $SGMGR_ERROR_MSG_PREFIX"Currently there is no PRIMARY replica running. Cannot deploy"\
             "MSSQL Availability Group Workload."
        exit 1
    ;;
    esac

    validate_ag_output "$query_output_for_deployment"
    # Now that we have the DB output, read it into variables
    process_aoai_cmd_output "$query_output_for_deployment" $IN_AG_NAME

    # Discover home of mssql and populate the VG & FS Arrays
    discover_home_mssql_ag
    
    # Discover Storage info for all DB in the Given AG
    discover_storage_info_for_all_db_in_ag 
       
}

function deploy_mssql_pkg
{

    msg="Microsoft SQL Server Multi Node Package"

    echo "##################################################################"
    echo "##################################################################" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "   Deployment Status of $msg"
    echo "   Deployment Status of $msg" >> $MAIL_TEXT
    echo "##################################################################"
    echo "" >> $MAIL_TEXT
    echo "##################################################################" >> $MAIL_TEXT

    create_mssql_pkg $msg
    echo "$number_flag. Created $msg               :    [OK]"
    echo "$number_flag. Created $msg               :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    (( number_flag = number_flag + 1 ))

    validate_pkg_force $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf $msg
    apply_pkg_force $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf $msg
    echo "$number_flag. Configured $msg            :    [OK]"
    echo "$number_flag. Configured $msg            :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    (( number_flag = number_flag + 1 ))

    start_pkg $MNP_PKG_NAME
    echo "$number_flag. Started $msg               :    [OK]"
    echo "$number_flag. Started $msg               :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    (( number_flag = number_flag + 1 ))
}

function send_results_aofi
{
    typeset -a DATABASE_NAME_ARRAY
    if [[ ${#CRITICAL_DATABASE_NAME_ARRAY[@]} -eq 0 ]];
    then
        DATABASE_NAME_ARRAY=("${NON_CRITICAL_DATABASE_NAME_ARRAY[@]}")
    else
        DATABASE_NAME_ARRAY=("${CRITICAL_DATABASE_NAME_ARRAY[@]}")
    fi

    typeset TMP="`mktemp`"
    trap "rm $TMP 2> /dev/null;exit" 0 2 3

    ALERT_SUBJECT="HA Protection for SQL Server Failover Instance Deployment Status"

    echo "" >> $MAIL_TEXT
    echo "Discovered Information for Database \"${DATABASE_NAME_ARRAY[@]}\"" >> $MAIL_TEXT
    echo "##################################################################" >> $MAIL_TEXT


    echo "" >> $MAIL_TEXT
    echo "\"${DATABASE_NAME_ARRAY[@]}\" is/are configured in the Volume Groups : \"${VG_ARRAY[@]}\"" >> $MAIL_TEXT

    echo "##################################################################" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "Monitoring configuration for \"${DATABASE_NAME_ARRAY[@]}\" resources" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "##################################################################" >> $MAIL_TEXT

    echo "" >> $MAIL_TEXT
    echo "Configured Storage Monitoring for : \"${VG_ARRAY[@]}\"" >> $MAIL_TEXT

    echo "" >> $MAIL_TEXT
    echo "Configured Subnet Monitoring for  : $IP_SUBNET" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "Configured Virtual IP for SQL Server Failover Instance : $IP_ADDRESS" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "Configured sp_health_diagnostics monitoring for SQL Server on Linux" >> $MAIL_TEXT
    echo "Parameters monitored as part of sp_health_diagnostics:" >> $MAIL_TEXT
    echo "    a) System" >> $MAIL_TEXT
    echo "    b) Resource" >> $MAIL_TEXT
    echo "    c) Query_Processing" >> $MAIL_TEXT
    echo "    d) IO_Subsystem" >> $MAIL_TEXT
    echo "    e) Instance_Events" >> $MAIL_TEXT

    if [ -f "$MAIL_TEXT" ];then
        STATUS_CMD="cat $MAIL_TEXT"
    else
        STATUS_CMD=""
    fi

    cat <<=EOD= > $TMP

Hi,

Here is the Summary of deployment for Microsoft SQL Server on Linux Failover Instance.

   Database Name                     : ${DATABASE_NAME_ARRAY[@]}
   Node name                         : `hostname`
   Location of package configuration : $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
   Microsoft SQL Server Package Name : $AOFI_ACTIVE_PASSIVE_PKG_NAME

   `$STATUS_CMD`

   SQL Server on Linux Failover Instance package can be administered via Serviceguard Manager Graphical user interface:
   https://`hostname -f`:5522

Thanks
=EOD=

    cat $TMP | mailx -r SGLX_MSSQL_Deploy@hpe.com -s "$ALERT_SUBJECT" $EMAIL_ID
}

function send_results_aoai
{
    typeset SQL_SERVER_MNP_PKG="Microsoft SQL Server Multi Node Package"
    typeset AG_MNP_PKG="Microsoft SQL Server Availability Group Multi Node Package"
    typeset AG_WRITE_INTENT_PKG="Microsoft SQL Server Availability Group Write Intent Package"
    typeset TMP="`mktemp`"
    trap "rm $TMP 2> /dev/null;exit" 0 2 3

    ALERT_SUBJECT="HA/DR Protection for Availability Group $AG_NAME Deployment Status"


    echo "" >> $MAIL_TEXT
    echo "##################################################################" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo  "    Monitoring configuration for resources   " >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "##################################################################" >> $MAIL_TEXT
    echo "Configured Subnet Monitoring for $AG_NAME : ${IP_SUBNET_ARRAY[@]}" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "Configured Virtual IP for $AG_NAME : ${IP_ADDRESS_ARRAY[@]}" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "Configured sp_health_diagnostics monitoring for $AG_NAME" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "Parameters monitored as part of sp_health_diagnostics:" >> $MAIL_TEXT
    echo "    a) System" >> $MAIL_TEXT
    echo "    b) Resource" >> $MAIL_TEXT
    echo "    c) Query_Processing" >> $MAIL_TEXT
    echo "    d) IO_Subsystem" >> $MAIL_TEXT
    echo "    e) Instance_Events" >> $MAIL_TEXT

    typeset gen_res=$AG_NAME"_Replica_Role"
    echo "" >> $MAIL_TEXT
    echo "The $AG_MNP_PKG tracks the monitoring and automatic role management of Microsoft SQL Server Availability Group $AG_NAME." >> $MAIL_TEXT


  for (( i=0;i<${#AG_NODE_NAME_ARRAY[@]};i++ ))
    do
      typeset ROLE="SECONDARY"

          if [ ${AG_NODE_NAME_ARRAY[i]} = $MASTER_NODE ];
          then
              ROLE="PRIMARY"
           fi
           echo "     |    ${AG_NODE_NAME_ARRAY[i]}   |   $ROLE   |    ${AVAILABILITY_MODE_ARRAY[i]}    | " >> $MAIL_TEXT_AG
     done

    cat <<=EOD= > $TMP

   Hi,
        The deployment of workload for $AG_NAME is successful. Here is the Summary of workload deployment
        for Microsoft SQL AlwaysOn Availability Groups on Linux Database.


     Availability Group Name           : $AG_NAME
     Database Names                        : ${AG_DB_NAME_ARRAY[@]}
     Primary Replica                           : $MASTER_NODE
     $C_COMMIT_STRING                    : $REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT
     $SQL_SERVER_MNP_PKG Name                                     : $MNP_PKG_NAME
     $AG_MNP_PKG Name    : $AG_MNP_PKG_NAME
     $AG_WRITE_INTENT_PKG Name  : $AOAI_WRITE_PKG
     Location of $SQL_SERVER_MNP_PKG configuration                                     : $ED_CONF_FILE_DIR/$MNP_PKG_NAME.conf
     Location of $AG_MNP_PKG configuration    : $AG_MNP_CONF_FILE
     Location of $AG_WRITE_INTENT_PKG configuration  : $IP_CONF_FILE


     Availability group details:
     ##########################################################
     |   Server Name   |    Role     |      Availability mode  |
     ##########################################################

`cat $MAIL_TEXT_AG`
`cat $MAIL_TEXT`

Thanks
=EOD=

    cat $TMP | mailx -r SGLX_MSSQL_Deploy@hpe.com -s "$ALERT_SUBJECT" $EMAIL_ID
}

function get_list_of_available_db
{
    db_output=`$SQLCMD -S $HOST_PORT -U $MSSQL_ADMIN -P $MSSQL_PASSWORD -Q "SET NOCOUNT ON SELECT name, state, \
               state_desc FROM sys.databases WHERE state = 0 and name NOT IN ('master', 'tempdb', 'model', 'msdb')" -W -h -1`

    while read -r line
    do
        dbname=`echo $line | awk '{print $1}'`
        if [ -z $FOUND_DATABASE_NAME_ARRAY ]
        then
            FOUND_DATABASE_NAME_ARRAY=("$dbname")
        else
            FOUND_DATABASE_NAME_ARRAY+=("$dbname")
        fi
    done <<<"$db_output"
}

function get_state_of_specified_db
{
    db_state=""
    db_state=`$SQLCMD -S $HOST_PORT -U $MSSQL_ADMIN -P $MSSQL_PASSWORD -Q "SET NOCOUNT ON SELECT name, state, \
              state_desc FROM sys.databases WHERE name IN ('$1')" -W -h -1 | awk '{print $3}'`
    if [ -z $db_state ]
    then
        echo "The database name specified $1 is not available on the SQL Server"
        exit 1
    else
        echo "The database name specified $1 is in $db_state state"
        exit 1
    fi
}

function validate_available_db_with_specified_db
{
    for I in ${SPECIFIED_DATABASE_NAME_ARRAY[@]}
    do
        for J in ${FOUND_DATABASE_NAME_ARRAY[@]}
        do
            if [[ $I = $J ]]
            then
                if [ -z $CRITICAL_DATABASE_NAME_ARRAY ]
                then
                    CRITICAL_DATABASE_NAME_ARRAY=("$I")
                else
                    CRITICAL_DATABASE_NAME_ARRAY+=("$I")
                fi
                found=1
                break
            fi
        done
        # One of the Specified Database is not found.
        if ((found == 0 ))
        then
            get_state_of_specified_db "$I"
        fi
        # Reset Found
        found=0
    done

    for I in ${FOUND_DATABASE_NAME_ARRAY[@]}
    do
        for J in ${SPECIFIED_DATABASE_NAME_ARRAY[@]}
        do
            if [[ $I = $J ]]
            then
                found=1
                break
            fi
        done
        # An unspecified database add this to non critical list
        if ((found == 0 ))
        then
            if [ -z $NON_CRITICAL_DATABASE_NAME_ARRAY ]
            then
            NON_CRITICAL_DATABASE_NAME_ARRAY=("$I")
            else
            NON_CRITICAL_DATABASE_NAME_ARRAY+=("$I")
            fi
        fi
        # Reset Found
        found=0
    done
}

function check_for_atleast_one_available_db
{
    if [ -z $CRITICAL_DATABASE_NAME_ARRAY ] && [ -z $NON_CRITICAL_DATABASE_NAME_ARRAY ]
    then
        # No critical or non critical DB is available
        echo "No databases are available or online on the SQL Server"
        exit 1
    fi
}

function reset_vg_fs_variables
{
    DATA_FILE=""
    TRANSACTION_LOG_FILE=""
    VG_NAME_DATA=""
    VG_NAME_LOG=""
    LVOL_NAME_DATA=""
    LVOL_NAME_LOG=""
    FS_DIR_DATA=""
    FS_DIR_LOG=""
    VG_NAME_DATA_FS_TYPE=""
    VG_NAME_LOG_FS_TYPE=""
}

function populate_vg
{
    vgname=$1
    found=0

    if [ -z $VG_ARRAY ]
    then
        VG_ARRAY=("$vgname")
        return
    fi

    for I in ${VG_ARRAY[@]}
    do
        if [[ $I = $vgname ]]
        then
            # VG is already present nothing to do
            found=1
            break
        fi
    done

    if ((found == 0 ))
    then
        VG_ARRAY+=("$vgname")
    fi
}

function populate_fs
{
    fs_dir=$1
    vg_name=$2
    lvol_name=$3
    fs_type=$4
    found=0

    fs_name="$lvol_name"

    if [ -z $FS_NAME_ARRAY ]
    then
        FS_NAME_ARRAY=("$fs_name")
        FS_DIRECTORY_ARRAY=("$fs_dir")
        FS_TYPE_ARRAY=("$fs_type")
        return
    fi

    for I in ${FS_NAME_ARRAY[@]}
    do
        if [[ $I = $fs_name ]]
        then
            # FS Details is already present nothing to do
            found=1
            break
        fi
    done

    if ((found == 0 ))
    then
        FS_NAME_ARRAY+=("$fs_name")
        FS_DIRECTORY_ARRAY+=("$fs_dir")
        FS_TYPE_ARRAY+=("$fs_type")
    fi

}

function discover_home_mssql
{
    mnt_output=`df -P "$MSSQL_HOME" | tail -1 | cut -d " " -f 1`

    if [[ -z $mnt_output ]]
    then
        # Do nothing there is no specific mount point for Home of MSSQL
        return
    fi

    HOME_VG_NAMELVOL=$(echo "$mnt_output" | awk '{print $1}')
    HOME_VG_FS_TYPE=`mount -v | grep "$HOME_VG_NAMELVOL" | cut -d " " -f 5`
    HOME_VG_NAME=`lvs --noheadings --separator : $HOME_VG_NAMELVOL | cut -d ":" -f 2`
    HOME_LVOL_NAME=`lvdisplay -c $HOME_VG_NAMELVOL | cut -d ":" -f 1 | sed 's/ //g'`

    populate_vg $HOME_VG_NAME
    populate_fs $MSSQL_HOME $HOME_VG_NAME $HOME_LVOL_NAME $HOME_VG_FS_TYPE
    reset_vg_fs_variables

}

function discover_home_mssql_ag
{
    mnt_output=`df -P "$MSSQL_HOME" | tail -1 | grep -v -w "/" | cut -d " " -f 1`

    if [[ -z $mnt_output ]]
    then
        # Do nothing there is no specific mount point for Home of MSSQL
        return
    fi

    HOME_VG_NAMELVOL=$(echo "$mnt_output" | awk '{print $1}')
    HOME_VG_FS_TYPE=`mount -v | grep "$HOME_VG_NAMELVOL" | cut -d " " -f 5`
    HOME_VG_NAME=`lvs --noheadings --separator : $HOME_VG_NAMELVOL | cut -d ":" -f 2`
    HOME_LVOL_NAME=`lvdisplay -c $HOME_VG_NAMELVOL | cut -d ":" -f 1 | sed 's/ //g'`

    populate_vg $HOME_VG_NAME
    populate_fs $MSSQL_HOME $HOME_VG_NAME $HOME_LVOL_NAME $HOME_VG_FS_TYPE
    reset_vg_fs_variables

}

#This function is used to discover system DBs
#and populate the corresponding VG information
function discover_sys_dbs
{
    output=`$SQLCMD -S $HOST_PORT -U $MSSQL_ADMIN -P $MSSQL_PASSWORD -Q "set nocount on select d.name as 'database', \
             mdf.physical_name as 'mdf_file', ldf.physical_name as 'log_file' from sys.databases d inner \
             join sys.master_files mdf on d.database_id = mdf.database_id and mdf.[type] = 0 inner join \
             sys.master_files ldf on d.database_id = ldf.database_id and ldf.[type] = 1 WHERE d.state = 0 AND d.name \
             IN ('master');" -W -h -1`

    while read -r line
    do
        DATA_FILE=`echo $line | awk '{print $2}'`
        data_file_lx=$(echo $DATA_FILE | sed -e 's/C://g; s/c://g; s/\\/\//g')
        FS_DIR_DATA=$(echo $data_file_lx | sed 's/\/[^\/]*$//')

        get_vgname_data $FS_DIR_DATA
        if [[ -z $FS_DIR_DATA ]]
        then
            continue;
        fi

        TRANSACTION_LOG_FILE=`echo $line | awk '{print $3}'`
        transaction_log_lx=$(echo $TRANSACTION_LOG_FILE | sed -e 's/C://g; s/c://g; s/\\/\//g')
        FS_DIR_LOG=$(echo $transaction_log_lx | sed 's/\/[^\/]*$//')

        get_vgname_log $FS_DIR_LOG

    done <<<"$output"

    if [ -n "$VG_NAME_DATA" ]
    then
        populate_vg $VG_NAME_DATA
        populate_fs $FS_DIR_DATA $VG_NAME_DATA $LVOL_NAME_DATA $VG_NAME_DATA_FS_TYPE
    fi

    if [ -n "$VG_NAME_LOG" ]
    then
        populate_vg $VG_NAME_LOG
        populate_fs $FS_DIR_LOG $VG_NAME_LOG $LVOL_NAME_LOG $VG_NAME_LOG_FS_TYPE
    fi


    reset_vg_fs_variables

}

# Function discover_storage_info_for_all_db_in_ag
#  Discover Storage Information for all the DB part of AG

function discover_storage_info_for_all_db_in_ag
{
    typeset I

    for I in ${AG_DB_NAME_ARRAY[@]}
    do
        discover_information_aofi $I

        if [[ -z $VG_NAME_DATA ]] || [[ -z $VG_NAME_LOG ]]
        then
            echo "ERROR: Unable to discover the LVM VG Information for Database $I"
            exit 1
        fi
        populate_vg $VG_NAME_DATA
        populate_vg $VG_NAME_LOG

        populate_fs $FS_DIR_DATA $VG_NAME_DATA $LVOL_NAME_DATA $VG_NAME_DATA_FS_TYPE
        populate_fs $FS_DIR_LOG $VG_NAME_LOG $LVOL_NAME_LOG $VG_NAME_LOG_FS_TYPE

        reset_vg_fs_variables
    done
}


# Function discover_storage_info_for_all_db
# This function succeeds only:
#      a) If all Critical Databases specified by user are present on Shared Disks
#      or
#      b) At-least one non-critical Database is present on Shared disks.

function discover_storage_info_for_all_db
{
    typeset AVAILABLE_NON_CRITICAL_DATABASE_NAME_ARRAY
    typeset I
    typeset J

    for I in ${CRITICAL_DATABASE_NAME_ARRAY[@]}
    do
        discover_information_aofi $I

        if [[ -z $VG_NAME_DATA ]] || [[ -z $VG_NAME_LOG ]]
        then
            echo "ERROR: Unable to discover the LVM VG Information for Database $I"
            exit 1
        fi
        populate_vg $VG_NAME_DATA
        populate_vg $VG_NAME_LOG

        populate_fs $FS_DIR_DATA $VG_NAME_DATA $LVOL_NAME_DATA $VG_NAME_DATA_FS_TYPE
        populate_fs $FS_DIR_LOG $VG_NAME_LOG $LVOL_NAME_LOG $VG_NAME_LOG_FS_TYPE

        reset_vg_fs_variables
    done

    for J in ${NON_CRITICAL_DATABASE_NAME_ARRAY[@]}
    do
        discover_information_aofi $J

        if [[ -z $VG_NAME_DATA ]] || [[ -z $VG_NAME_LOG ]]
        then
            continue;
        fi

        AVAILABLE_NON_CRITICAL_DATABASE_NAME_ARRAY+=("$J")
        populate_vg $VG_NAME_DATA
        populate_vg $VG_NAME_LOG

        populate_fs $FS_DIR_DATA $VG_NAME_DATA $LVOL_NAME_DATA $VG_NAME_DATA_FS_TYPE
        populate_fs $FS_DIR_LOG $VG_NAME_LOG $LVOL_NAME_LOG $VG_NAME_LOG_FS_TYPE

        reset_vg_fs_variables
    done

    if [ -z $CRITICAL_DATABASE_NAME_ARRAY ]
    then
        if [ ${#NON_CRITICAL_DATABASE_NAME_ARRAY[@]} > 0 ] \
            && [ -z $AVAILABLE_NON_CRITICAL_DATABASE_NAME_ARRAY ]
        then
            # There are no user specified critical DBs and
            # non-critical databases are not present on shared disk
            echo "ERROR: Unable to discover the LVM VG Information"
            exit 1
        fi
    fi

    NON_CRITICAL_DATABASE_NAME_ARRAY=("${AVAILABLE_NON_CRITICAL_DATABASE_NAME_ARRAY[@]}")
}

function add_valid_aofi_nodes_to_pkg
{
    typeset node
    for node in ${valid_aofi_nodes[*]}
    do
        echo "node_name ${node}" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
    done
}

function create_mssql_db_pkg
{
    AOFI_ACTIVE_PASSIVE_PKG_NAME=Microsoft_SQL_Server_Database

    rm -rf $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf

    output=`$SGSBIN/cmmakepkg -v 0 -m sgemssql/mssqldbinstance $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf`
    retval=$?

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to create $1"
        echo "$output"
        exit 1;
    fi

    # Search and replace Package Name Use Double Quotes to Expand Variable
    sed -i "/^package_name/c\package_name $AOFI_ACTIVE_PASSIVE_PKG_NAME" \
              $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf

    # Search and replace Package Description
    sed -i '/^package_description/c\package_description "Microsoft SQL Server Database"' \
              $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf

    #Search and replace the node names with aofi specific nodes
    sed -i '/^node_name/d' $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
    add_valid_aofi_nodes_to_pkg

    # Search and replace MSSQL Parameters
    # MSSQL_PORT, INSTANCE_NAME, data_file, transaction_log_file

    sed -i "/sgemssql\/mssqldbinstance\/MSSQL_PORT/c\sgemssql/mssqldbinstance/MSSQL_PORT $MSSQL_PORT" \
             $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf

    # Add Instance_name and Critical at the End
    for i in "${CRITICAL_DATABASE_NAME_ARRAY[@]}"; do
        echo "sgemssql/mssqldbinstance/instance_name $i" \
               >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "sgemssql/mssqldbinstance/critical true" \
               >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
    done

    for i in "${NON_CRITICAL_DATABASE_NAME_ARRAY[@]}"; do
        echo "sgemssql/mssqldbinstance/instance_name $i" \
               >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "sgemssql/mssqldbinstance/critical false" \
               >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
    done

    # Add standard_workload_name and standard_workload_type at the END
    if [[ $wflag = 1 ]];
    then
        echo "standard_workload_name $STANDARD_WORKLOAD_NAME" \
               >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "standard_workload_type $STANDARD_WORKLOAD_TYPE" \
               >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
    fi

    # Add IP Address
    j=0
    for i in "${IP_SUBNET_ARRAY[@]}"; do
        echo "ip_subnet $i" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "ip_address ${IP_ADDRESS_ARRAY[$j]}" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        (( j = j + 1 ))
    done

    # Add LVM VG's
    for i in "${VG_ARRAY[@]}"; do
        echo "vg $i" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
    done

    # Add File System Data
    j=0
    for i in "${FS_NAME_ARRAY[@]}"; do
        echo "fs_name ${i}" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "fs_server "\"\" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "fs_directory ${FS_DIRECTORY_ARRAY[$j]}" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "fs_type ${FS_TYPE_ARRAY[$j]}" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "fs_mount_opt "\"\" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "fs_umount_opt "\"\" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        echo "fs_fsck_opt "\"\" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
        (( j = j + 1 ))
    done

    # Search and replace for Disk Monitoring
    vg_list_string=${VG_ARRAY[@]}
    sed -i -e "s/cmresserviced/cmresserviced $vg_list_string/g" $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf

    # Add E-Mail Address Field
    if [[ "X$EMAIL_ID" != "X" ]]; then
        echo "email_id $EMAIL_ID" >> $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf
    fi

    return 0
}

function deploy_mssql_db_pkg
{
    msg="Microsoft SQL Server Database Package"

    echo "#######################################################################"
    echo "#######################################################################" >> $MAIL_TEXT
    echo "   Deployment Status of $msg"
    echo "   Deployment Status of $msg" >> $MAIL_TEXT
    echo "#######################################################################"
    echo "#######################################################################" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT

    echo "1. Discovered the following Database's                         :    [OK]"
    echo "1. Discovered the following Database's                         :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "   Critical Database's:"
    echo "   Critical Database's:" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    for i in "${CRITICAL_DATABASE_NAME_ARRAY[@]}"; do
        echo "    ${i}"
        echo "    ${i}" >> $MAIL_TEXT
        echo "" >> $MAIL_TEXT
    done
    echo "   Non-Critical Database's:"
    echo "   Non-Critical Database's:" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    for i in "${NON_CRITICAL_DATABASE_NAME_ARRAY[@]}"; do
        echo "    ${i}"
        echo "    ${i}" >> $MAIL_TEXT
        echo "" >> $MAIL_TEXT
    done

    create_mssql_db_pkg $msg
    echo ""
    echo "2. Created $msg               :    [OK]"
    echo "2. Created $msg               :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT

    validate_pkg $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf $msg
    apply_pkg $ED_CONF_FILE_DIR/$AOFI_ACTIVE_PASSIVE_PKG_NAME.conf $msg
    echo ""
    echo "3. Configured $msg            :    [OK]"
    echo "3. Configured $msg            :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT

    start_pkg $AOFI_ACTIVE_PASSIVE_PKG_NAME
    echo ""
    echo "4. Started $msg               :    [OK]"
    echo "4. Started $msg               :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT

    enable_pkg $AOFI_ACTIVE_PASSIVE_PKG_NAME
    echo ""
    echo "5. Enable Failover for $msg   :    [OK]"
    echo "" >> $MAIL_TEXT
    echo "5. Enable Failover for $msg   :    [OK]" >> $MAIL_TEXT
    echo "##################################################################" >> $MAIL_TEXT

}

function discover_and_deploy_on_a_node_with_sql_server_running
{
    typeset -i _ret=0
    _node_name=`echo "${VCLVIEW_CACHE}" | awk -F= '/^node:[^|]*\|name=/ {print $2}'`
    for node in $_node_name
    do
        $SGSBIN/cmexec $node $SGSBIN/cmdeploymssql -z -U $MSSQL_ADMIN -P $MSSQL_PASSWORD -l > /dev/null 2>&1
        _ret=$?
        if (( $_ret == 0 )); then
            break
        fi
    done
    case "$_ret" in
    0)
        $SGSBIN/cmexec $node $SGSBIN/cmdeploymssql $AOFI_DEP_CMD_ARGS -l
        _ret=$?
        exit $_ret
    ;;
    1)
        echo $SGMGR_ERROR_MSG_PREFIX"Microsoft SQL Server is not running."
        exit 1
    ;;
    3)
        echo $SGMGR_ERROR_MSG_PREFIX"Invalid credentials for the user $MSSQL_ADMIN."\
             "Provide valid username and password."
    ;;
    esac
}

function discover_aofi_node_names
{
    typeset _output=$mssql_tmp_dir/cmexec.${0##*/}.discover_aofi_node_names_$$
    typeset _stderr=$mssql_tmp_dir/cmexec.err.${0##*/}.discover_aofi_node_names_$$

    #Get the name of the nods on which mssql is installed.
    get_mssql_node_names

    #Get the valid aofi nodenames based on the presence of vg
    #required for the DB created for this instance.
    #Verify if vg are present on mssql nodes and
    #obtain the invalid nodes.
    if [[ -n ${mssql_nodes[*]} ]]; then
       for node in "${mssql_nodes[@]}"
       do
          for vg in "${VG_ARRAY[@]}"
          do
             do_cmexec $node $_output $_stderr vgs $vg > /dev/null 2>&1
             if [[ $? -ne 0 ]]; then
                invalid_aofi_nodes[${#invalid_aofi_nodes[*]}]="$node"
             fi
          done
       done
       if [[ -z ${invalid_aofi_nodes[*]} ]]; then
           valid_aofi_nodes=("${mssql_nodes[@]}")
       else
           #If mssql_nodes and invalid nodes are different then we need to identify valid nodes.
           for node in "${mssql_nodes[@]}"
           do
               echo "${invalid_aofi_nodes[*]}" | grep -w "$node" >> /dev/null
               if [[ $? -ne 0 ]]; then
                   valid_aofi_nodes+=("$node")
               fi
           done
       fi
       if [[ ${#valid_aofi_nodes[@]} -eq 1 ]]; then
           echo "ERROR: Minimum two nodes should be avaiable for configuring MSSQL DB Package"
           exit 1
       fi

    else
       echo "ERROR: No valid mssql installed nodes are found"
       exit 1
    fi

}

function deploy_always_on_failover_instance_active_passive
{
    # Check if package already exists for DB specified. If not then deploy.
    check_if_aofi_pkg_configured
    if [[ -n $DB_INSTANCE_PKG_NAME ]]
    then
        echo "INFO: MSSQL Server database instance is already configured as Serviceguard Package"
        echo "INFO: MSSQL Server database Instance is already configured as Serviceguard Package" >> $MAIL_TEXT
        echo "Name of the Package : $DB_INSTANCE_PKG_NAME"
        echo "Name of the Package : $DB_INSTANCE_PKG_NAME" >> $MAIL_TEXT
        exit 0
    else
        # Get the list of all online available database by probing SQL Server
        get_list_of_available_db

        # Validate this list with list specified by user
        validate_available_db_with_specified_db

        # Check if we have atleast one Critical or No critical DB found.
        check_for_atleast_one_available_db

        # Discover home of mssql and populate the VG & FS Arrays
        discover_home_mssql

        # Discover System DBs and populate the VG & FS Arrays
        discover_sys_dbs
        # Iterate on all DB and add the information to independent arrays for all
        # required parameters for a package, Ensure to retain the Index Number Correctly.
        discover_storage_info_for_all_db

        #discover information for node names
        discover_aofi_node_names

        # Distribute the login credentials to the valid aofi nodes.
        distribute_login_credentials_file $CREDENTIALS_FILE ${valid_aofi_nodes[@]}

        # Create, Validate, Apply, Start the package.
        deploy_mssql_db_pkg
    fi
}

function add_mssql_server_pkg_dependency
{
    echo "dependency_name         $AG_MNP_PKG_DEPENDENCY_NAME" >> $AG_MNP_CONF_FILE
    echo "dependency_condition    $MNP_PKG_NAME = up" >> $AG_MNP_CONF_FILE
    echo "dependency_location     same_node" >> $AG_MNP_CONF_FILE
}

function configure_services_and_generic_resources
{
    # Need to add logic to TRIM the AG_NAME so that we do not exceed 39 char length for
    # Generic resources.

    # Search and replace all the GR names
    GR_NAMES="SQL_Availability_Replica_Role"
    for gr in $GR_NAMES
    do
        case "$gr" in
            'SQL_Availability_Replica_Role') gr_str=$REPLICA_ROLE_GR_NAME ;;
        esac
        sed -i "s/\b$gr/$gr_str/" "$AG_MNP_CONF_FILE"
    done

    # Search and replace all the service names
    SRV_NAMES="Microsoft_SQL_Availability_Replica_Role"
    for srv in $SRV_NAMES
    do
        case "$srv" in
            'Microsoft_SQL_Availability_Replica_Role') srv_str=$REPLICA_ROLE_SERVICE_NAME ;;
        esac
        sed -i "s/\b$srv/$srv_str/" "$AG_MNP_CONF_FILE"
    done
}

function create_mssql_aoai_pkg
{
    output=`$SGSBIN/cmmakepkg -v 0 -m sgemssql/mssqlaoai $AG_MNP_CONF_FILE`
    retval=$?
    mode=""

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to create $1"
        echo "$output"
        exit 1;
    fi

    # Search and replace Package Name Use Double Quotes to Expand Variable
    sed -i "/^package_name/c\package_name        $AG_MNP_PKG_NAME"  $AG_MNP_CONF_FILE

    # Search and replace Package Description
    sed -i '/^package_description/c\package_description "Microsoft SQL Server Availability Instance"' \
              $AG_MNP_CONF_FILE

    sed -i '/node_name/d' $AG_MNP_CONF_FILE

    for node in ${AG_NODE_NAME_ARRAY[@]}
    do
        echo "node_name        $node" >> $AG_MNP_CONF_FILE
    done

    # AOAI specific parameters
    # First remove the default lines added by cmmakepkg
    patterns="availability_group aoai_database $commit_string
              nodename availability_mode dependency_name
              dependency_condition dependency_location"
    for p in $patterns
    do
        sed -i "/sgemssql\/mssqlaoai\/$p/d" $AG_MNP_CONF_FILE
        sed -i "/^$p/d" $AG_MNP_CONF_FILE
    done

    # Add the AG name
    echo "sgemssql/mssqlaoai/availability_group        $AG_NAME" >> $AG_MNP_CONF_FILE
    # Add aoai_database
    for db in ${AG_DB_NAME_ARRAY[@]}
    do
        echo "sgemssql/mssqlaoai/aoai_database        $db" >> $AG_MNP_CONF_FILE
    done
    # Add required_synchronized_secondaries_to_commit
    echo "sgemssql/mssqlaoai/$commit_string $REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT" \
          >> $AG_MNP_CONF_FILE
    # Add the node level attribs.
    R=0
    while (($R < ${#AG_NODE_NAME_ARRAY[*]}))
    do
        echo "sgemssql/mssqlaoai/nodename        ${AG_NODE_NAME_ARRAY[$R]}" >> $AG_MNP_CONF_FILE
        if `echo "${AVAILABILITY_MODE_ARRAY[$R]}" | grep -qw SYNCHRONOUS_COMMIT`
        then
            mode="SYNCHRONOUS"
        elif `echo "${AVAILABILITY_MODE_ARRAY[$R]}" | grep -qw ASYNCHRONOUS_COMMIT`
        then
            mode="ASYNCHRONOUS"
        fi
        echo "sgemssql/mssqlaoai/availability_mode        $mode" >> $AG_MNP_CONF_FILE
        (( R = R + 1 ))
    done

    # Add standard_workload_name and standard_workload_type at the END
    if [[ $wflag = 1 ]];
    then
        echo "standard_workload_name $STANDARD_WORKLOAD_NAME" >> $AG_MNP_CONF_FILE
        echo "standard_workload_type $STANDARD_WORKLOAD_TYPE" >> $AG_MNP_CONF_FILE
    fi

    if [[ "X$EMAIL_ID" != "X" ]]; then
        echo "email_id $EMAIL_ID" >> $AG_MNP_CONF_FILE
    fi

    configure_services_and_generic_resources

    add_mssql_server_pkg_dependency
    
    return 0
}

function deploy_ag_mnp_pkg
{
    initialize_names_for_pkg_and_conf_file

    if [[ $fflag = 1 ]];
    then
        validate_aoai_file_params
    fi

    msg="Microsoft SQL Server Availability Group Multi Node Package"
    rm -f $AG_MNP_CONF_FILE
    rm -f $IP_CONF_FILE

    echo "####################################################################################"
    echo "####################################################################################"\
          >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "   Deployment Status of $msg"
    echo "   Deployment Status of $msg" >> $MAIL_TEXT
    echo "####################################################################################"
    echo "" >> $MAIL_TEXT
    echo "####################################################################################"\
         >> $MAIL_TEXT

    create_mssql_aoai_pkg $msg
    echo "$number_flag. Created $msg              :    [OK]"
    echo "$number_flag. Created $msg              :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    (( number_flag = number_flag + 1 ))

    validate_pkg $AG_MNP_CONF_FILE $msg

    apply_pkg $AG_MNP_CONF_FILE $msg
    echo "$number_flag. Configured $msg           :    [OK]"
    echo "$number_flag. Configured $msg           :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    (( number_flag = number_flag + 1 ))

    start_pkg $AG_MNP_PKG_NAME
    echo "$number_flag. Started $msg              :    [OK]"
    echo "$number_flag. Started $msg              :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    (( number_flag = number_flag + 1 ))
}

function create_ag_read_write_ip_pkg
{
    output=`$SGSBIN/cmmakepkg -v 0 -m sgemssql/mssqlwriteintent $IP_CONF_FILE`
    retval=$?
    typeset -i j=0
    typeset i

    if [[ $retval != 0 ]];
    then
        echo "ERROR: Failed to create $1"
        echo "$output"
        exit 1;
    fi
    # Search and replace Package Name Use Double Quotes to Expand Variable
    sed -i "/^package_name/c\package_name        $AOAI_WRITE_PKG"  $IP_CONF_FILE

    # Search and replace Package Description
    sed -i '/^package_description/c\package_description "Microsoft SQL Server Write Intent Package"' \
              $IP_CONF_FILE

    sed -i '/node_name/d' $IP_CONF_FILE

    for node in ${AG_NODE_NAME_ARRAY[@]}
    do
        echo "node_name        $node" >> $IP_CONF_FILE
    done

    # Replace the default MNP package dependency name, dependency condition and dependency location
    # with user provided values.
    sed -i "s/^dependency_name[[:space:]]*AOAI_AG_NAME_PKG_DEP/dependency_name $AOAI_WRITE_PKG_DEPENDENCY_NAME/" \
           $IP_CONF_FILE
    sed -i "s/^dependency_condition[[:space:]]*AOAI_AG_NAME_PKG = up/dependency_condition $AG_MNP_PKG_NAME = up/" \
           $IP_CONF_FILE

    # Add IP Address, IP SUBNET
    for i in "${IP_SUBNET_ARRAY[@]}"; do
        echo "ip_subnet $i" >> $IP_CONF_FILE
        echo "ip_address ${IP_ADDRESS_ARRAY[$j]}" >> $IP_CONF_FILE
        (( j = j + 1 ))
    done

    # Add generic resource
    GEN_RES_ROLE="SQL_Availability_Replica_Role"
    sed -i "s/\b$GEN_RES_ROLE/$REPLICA_ROLE_GR_NAME/" "$IP_CONF_FILE"

    # Add workload
    if [[ $wflag = 1 ]];
    then
        echo "standard_workload_name $STANDARD_WORKLOAD_NAME" >> $IP_CONF_FILE
        echo "standard_workload_type $STANDARD_WORKLOAD_TYPE" >> $IP_CONF_FILE
    fi

}

function deploy_ag_read_write_ip_pkg
{
    msg="Microsoft SQL Server Availability Group Write Intent Package"
    echo "####################################################################################"
    echo "####################################################################################"\
          >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    echo "   Deployment Status of $msg"
    echo "   Deployment Status of $msg" >> $MAIL_TEXT
    echo "####################################################################################"
    echo "" >> $MAIL_TEXT
    echo "####################################################################################"\
         >> $MAIL_TEXT

    create_ag_read_write_ip_pkg $msg
    echo "$number_flag. Created $msg            :    [OK]"
    echo "$number_flag. Created $msg            :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    (( number_flag = number_flag + 1 ))

    validate_pkg $IP_CONF_FILE $msg

    apply_pkg $IP_CONF_FILE $msg
    echo "$number_flag. Configured $msg         :    [OK]"
    echo "$number_flag. Configured $msg         :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    (( number_flag = number_flag + 1 ))

    enable_pkg $AOAI_WRITE_PKG
    echo "$number_flag. Enabled $msg            :    [OK]"
    echo "$number_flag. Enabled $msg            :    [OK]" >> $MAIL_TEXT
    echo "" >> $MAIL_TEXT
    (( number_flag = number_flag + 1 ))
}

###############################################################################
# Function: initialize_names_for_pkg_and_conf_file
# Description: Make the unique package Names
# AG_MNP_PKGS and WRITE INTENT_PKG
# PKG NAME FORMAT : AOAI<0-999><AG_NAME>_PKG<0-999>
# AOAI<0-999><AG_NAME>_WRITE_PKG<0-999>
###############################################################################
function initialize_names_for_pkg_and_conf_file
{
    typeset serial   # Will have all the configured package names
    typeset prefix   # MSSQL pkgs will be prefixed with this value
    typeset suffix   # Used to make sure after cut names are not ended with special characters
    typeset PKG_NAME

    serial=`echo "$VCLVIEW_CACHE" | awk -F "[:|=]" '(($1 == "package") \
        && ($3 == "name")) {print $4}'`
    for i in {1..999}; do
        prefix=AOAI"$i"
        suffix=$i
        echo "$serial" | grep "^$prefix" > /dev/null 2>&1
        if [[ $? -ne 0 ]]; then
            break
        else
            continue
        fi
    done

    # Trimming the names to 38 characters

    AG_MNP_PKG_NAME=`echo "$prefix"_$AG_NAME"_PKG" | cut -c 1-35`"$suffix"
    AOAI_WRITE_PKG=`echo "$prefix"_$AG_NAME"_WRITE_PKG" | cut -c 1-35`"$suffix"
    REPLICA_ROLE_GR_NAME=`echo "$prefix"_$AG_NAME"_Replica_Role" | cut -c 1-35`"$suffix"
    REPLICA_ROLE_SERVICE_NAME=`echo "$prefix"_$AG_NAME"_Service_Replica_Role" | cut -c 1-35`"$suffix"
    AG_MNP_PKG_DEPENDENCY_NAME=`echo "$prefix"_$AG_NAME"_MSSQL_SERVER_DEP" | cut -c 1-35`"$suffix"
    AOAI_WRITE_PKG_DEPENDENCY_NAME=`echo "$prefix"_$AG_NAME"_PKG_DEP" | cut -c 1-35`"$suffix"

    # Make sure the packages do not exist already
    PKG_NAME=`echo "$VCLVIEW_CACHE" | awk -F "[:|=]" '(($1 == "package") \
        && ($3 == "name") && ($4 == "'$AG_MNP_PKG_NAME'")){print $4}' > /dev/null 2>&1`
    if [[ -n $PKG_NAME ]]; then
      echo "Error: Package $AG_MNP_PKG_NAME already exists"
      exit 1
    fi

    PKG_NAME=`echo "$VCLVIEW_CACHE" | awk -F "[:|=]" '(($1 == "package") \
        && ($3 == "name") && ($4 == "'$AOAI_WRITE_PKG'")){print $4}' > /dev/null 2>&1`
    if [[ -n $PKG_NAME ]]; then
        echo "Error: Package $AOAI_WRITE_PKG already exists"
        exit 1
    fi

    AG_MNP_CONF_FILE=$ED_CONF_FILE_DIR/$AG_MNP_PKG_NAME.conf
    IP_CONF_FILE=$ED_CONF_FILE_DIR/$AOAI_WRITE_PKG.conf
}

function deploy_always_on_availability_groups
{
    discover_all_info_for_always_on_availability_groups
    
    check_if_mnp_configured
    if [[ -z $MNP_PKG_NAME ]]
    then
        deploy_mssql_pkg
    else
        echo "INFO: Microsoft SQL Server on Linux is already Configured as Serviceguard Package"
        echo "INFO: Microsoft SQL Server on Linux is already Configured as Serviceguard Package" >> $MAIL_TEXT
        echo "Name of the Package : $MNP_PKG_NAME"
        echo "Name of the Package : $MNP_PKG_NAME" >> $MAIL_TEXT
    fi

    # Deploy the Availability Group Package for the AG specified by user.
    deploy_ag_mnp_pkg $AG_NAME

    deploy_ag_read_write_ip_pkg $AG_NAME
}

function read_aoai_file_params
{
  typeset IP_SUBNET_VAL
  typeset IP_ADDRESS_VAL

  while read -r line
  do
    if [ -z $IN_AG_NAME ]
    then
        IN_AG_NAME=`echo $line| awk -F '[=]' ' (($1=="AG_NAME"))  {print $2}'`
        if [ ! -z $IN_AG_NAME ]
        then
             (( aflag = aflag + 1 ))
        fi
    fi

    if [ -z $IN_FILE_SC_COMMIT ]
    then
        IN_FILE_SC_COMMIT=`echo $line| awk -F '[|=]' \
        '(($2=="'$C_COMMIT_STRING'")) {print $3}'`
    fi

    if [ -z $MSSQL_ADMIN ]
    then
        MSSQL_ADMIN=`echo $line| awk -F '[|=]' '(($2=="USERNAME")) {print $3}'`
        if [ ! -z $MSSQL_ADMIN ]
        then
             (( uflag = uflag + 1 ))
        fi
    fi

    if [ -z $MSSQL_PASSWORD ]
    then
        MSSQL_PASSWORD=`echo $line| awk -F '[|=]' '(($2=="PASSWORD")) {print $3}'`
        if [ ! -z $MSSQL_PASSWORD ]
        then
             (( pflag = pflag + 1 ))
        fi
    fi

    if [ -z $STANDARD_WORKLOAD_NAME ]
    then
        STANDARD_WORKLOAD_NAME=`echo $line| awk -F '[|=]' '(($2=="WORKLOAD_NAME")) {print $3}'`
        if [ ! -z $STANDARD_WORKLOAD_NAME ]
        then
            (( wflag = wflag + 1 ))
        fi
    fi

    if [ -z $EMAIL_ID ]
    then
        EMAIL_ID=`echo $line| awk -F '[|=]' '(($2=="EMAIL")) {print $3}'`
        if [ ! -z $EMAIL_ID ]
        then
            (( eflag = eflag + 1 ))
        fi
    fi


    IN_FILE_NODE_NAME=`echo $line| awk -F '[|=]' '(($2=="NODE_NAME")) {print $3}'`
    if [ ! -z $IN_FILE_NODE_NAME ]
    then
        if [ -z $IN_FILE_NODE_NAMES ]
        then
             IN_FILE_NODE_NAMES=($IN_FILE_NODE_NAME)
        else
             IN_FILE_NODE_NAMES+=($IN_FILE_NODE_NAME)
        fi
    fi

    IN_FILE_AVAILABILITY_MODE_VAL=`echo $line| awk -F '[|=]' '(($3=="AVAILABILITY_MODE")) {print $4}'`
    if [ ! -z $IN_FILE_AVAILABILITY_MODE_VAL ]
    then
        if [ -z $IN_FILE_AVAILABILITY_MODE ]
        then
             IN_FILE_AVAILABILITY_MODE=($IN_FILE_AVAILABILITY_MODE_VAL)
        else
             IN_FILE_AVAILABILITY_MODE+=($IN_FILE_AVAILABILITY_MODE_VAL)
        fi
    fi

    IP_SUBNET_VAL=`echo $line| awk -F '[|:=]' '(($5=="ip_subnet") && ($7=="ip_address")){print $6}'`
    if [ ! -z $IP_SUBNET_VAL ]
    then
         if [ -z $IP_SUBNET_ARRAY ]
              then
                  IP_SUBNET_ARRAY=($IP_SUBNET_VAL)
              else
                  IP_SUBNET_ARRAY+=($IP_SUBNET_VAL)
              fi
        (( sflag = sflag + 1 ))
    fi

    IP_ADDRESS_VAL=`echo $line| awk -F '[|:=]' '(($7=="ip_address")) {print $8}'`
    if [ ! -z $IP_ADDRESS_VAL ]
    then
        if [ -z $IP_ADDRESS_ARRAY ]
           then
               IP_ADDRESS_ARRAY=($IP_ADDRESS_VAL)
           else
               IP_ADDRESS_ARRAY+=($IP_ADDRESS_VAL)
           fi
        (( iflag = iflag + 1 ))
    fi

  done <$AOAI_IN_FILE_NAME

  #Delete the input file passed as an argument with -F option for AOAI deployment via SGMGR
  rm -f $AOAI_IN_FILE_NAME

  return 0
}

function validate_aoai_file_params
{
  typeset -i i=0
  typeset -i j=0

  if [[ $REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT != $IN_FILE_SC_COMMIT ]]
  then
      update_req_sync_secondary_to_commit $IN_AG_NAME $IN_FILE_SC_COMMIT $MASTER_NODE
      REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT=$IN_FILE_SC_COMMIT
  fi

    while (($i < ${#AG_NODE_NAME_ARRAY[*]}))
    do
        while (($j < ${#IN_FILE_NODE_NAMES[*]}))
        do
            if [[ ${AG_NODE_NAME_ARRAY[$i]} == ${IN_FILE_NODE_NAMES[$j]} ]];
            then
                if [[ ${AVAILABILITY_MODE_ARRAY[$i]} != ${IN_FILE_AVAILABILITY_MODE[$j]} ]];
                then
                    update_availability_mode_for_the_ag $IN_AG_NAME ${AG_NODE_NAME_ARRAY[$i]} \
                                             ${IN_FILE_AVAILABILITY_MODE[$j]} $MASTER_NODE
                    AVAILABILITY_MODE_ARRAY[$i]=${IN_FILE_AVAILABILITY_MODE[$j]}
                fi
                break
            fi
            (( j = j + 1 ))
        done
        j=0
        (( i = i + 1 ))
    done
}

function create_login_credentials_file
{
      if [ -f $CREDENTIALS_FILE ]
      then
          rm -f $CREDENTIALS_FILE
      fi

      touch "$CREDENTIALS_FILE"
      echo "$MSSQL_ADMIN" >> $CREDENTIALS_FILE
      echo "$MSSQL_PASSWORD" >> $CREDENTIALS_FILE
      chmod 600 $CREDENTIALS_FILE
}

function check_if_mssql_is_running
{
    if ! pgrep -x "sqlservr" > /dev/null
    then
        if [ $zflag = 1 ]; then
            echo $SGMGR_ERROR_MSG_PREFIX"Microsoft SQL Server is not running."
        else
            echo "Microsoft SQL Server is not running."
        fi
        exit 1
    fi
}

function initial_checks
{
    mkdir $ED_CONF_FILE_DIR  2> /dev/null
    rm -rf $MAIL_TEXT 2> /dev/null
    touch $MAIL_TEXT

    check_if_mssql_is_running

    if [[ $uflag = 1 ]] && [[ $pflag = 1 ]];
    then
        check_login_credentials
    fi

    if [[ $mflag = 1 ]];
    then
        create_login_credentials_file
    fi

    MSSQL_PORT=`get_mssql_tcp_port`
    HOST_PORT=localhost,$MSSQL_PORT
}

function check_login_credentials
{
   typeset _node_name
   typeset -i _ret=0
   # This will be changed once the utility function to retrieve the node names is checked-in.
   # So based on the deployment model node names will be passed to "validate_login_credentials"
   # function.
   _node_name=`$SGSBIN/cmviewcl -f line -s config -l node | awk -F= '/^node:[^|]*\|name=/ {print $2}'`
   for node in $_node_name
   do
      validate_login_credentials $MSSQL_ADMIN $MSSQL_PASSWORD $_node_name
      if (( $? != 0));
      then
          _ret=1
          echo " ERROR: Invalid Credentials for the user $MSSQL_ADMIN on the node $node."\
               "Provide valid Username and Password."
      fi
   done

   if (( $_ret == 1));
   then
       exit 3
   fi
}

function send_results
{
  if [[ $DEP_MODEL = 1 ]];
  then
    send_results_aoai
  else
    send_results_aofi
  fi
}

################################################################################
# Here is the Start block for Main code execution.
################################################################################

# Parse the Input Parameters Specified.
parse_input_params $*
# In mssql_utils.sh the variables used are different. Redefine for the same
MSSQL_MSSQL_ADMIN=$MSSQL_ADMIN
MSSQL_MSSQL_PASSWORD=$MSSQL_PASSWORD

configured_nodes=`${SGSBIN}/cmviewcl -vf line -s config | awk '/^node:[^|]*\|name=/' | wc -l`
up_nodes=`${SGSBIN}/cmviewcl -f line -l node | awk '/^node:[^|]*\|status=up/' | wc -l`
if [ $up_nodes -lt $configured_nodes ]; then
    if [ $zflag = 1 ]; then
        echo $SGMGR_ERROR_MSG_PREFIX"All the cluster nodes must be up and running."
    else
        echo "ERROR: All the cluster nodes must be up and running."
    fi
    exit 1
fi

cache_clview

if [[ $DEP_MODEL = 1 ]] ;
then
    initial_checks
    STANDARD_WORKLOAD_TYPE="Microsoft_SQL_Server_Availability_Group"
    deploy_always_on_availability_groups
elif [[ $DEP_MODEL = 2 ]] && [[ $lflag = 0 ]];
then
    AOFI_DEP_CMD_ARGS="$*"
    discover_and_deploy_on_a_node_with_sql_server_running
elif [[ $DEP_MODEL = 2 ]] && [[ $lflag = 1 ]];
then
    initial_checks
    STANDARD_WORKLOAD_TYPE="Microsoft_SQL_Server_Failover_instance"
    deploy_always_on_failover_instance_active_passive
elif [[ $zflag = 1 ]] && [[ $uflag = 0 ]] && [[ $pflag = 0 ]];
then
    # Used by Serviceguard Manager to show the Appropriate workload in dropdown
    initial_checks
    discover_deployment_model
    exit 0
elif [[ $zflag = 1 ]] && [[ $uflag = 1 ]] && [[ $pflag = 1 ]] && [[ $lflag = 0 ]];
then
    discover_information_from_all_nodes
    exit 0
elif [[ $zflag = 1 ]] && [[ $lflag = 1 ]];
then
    initial_checks
    discover_information
    exit 0
else
    exit 0
fi

if [[ "X$EMAIL_ID" != "X" ]]; then
    send_results
fi

rm -rf $MAIL_TEXT
rm -rf $MAIL_TEXT_AG

