#!/usr/bin/env bash

# Quick and dirty script to automate the user deletion process.
#
# This script expects .aws/(config|credentials), .my.cnf, and .pgpass files
# to be configured for the user running this script.
#
# AWS config files should specify 'dev' and 'prod' profiles
#
# .pgpass files should have host and cluster-specific credentials as needed
#
# .my.cnf files should follow the following syntax, with one entry per host
# in which $DBHOSTNAME is replaced by each of the MySQL hosts specified below:
#
# Syntax:
#    [mysql{$DBHOSTNAME}]
#    user=user
#    password="password"
#
# Example:
#   [mysqlprodserver.theorchard.com]
#    user=orchardproduser
#    password="prodpassword123"
#
#   [mysqlqaserver.qaorch.com]
#    user=orchardqauser
#    password="qapassword123"

# ~/.snowsql/config is used for Snowflake, and should at a minimum have the following fields populated:
# [conections]
#
# accountname = orchard
# username = some_username
# password = some_password123
# dbname = some_db (ideally something safe like DEMO_DB)
# rolename = role_name_with_appropriate_privileges
# schemaname = schema_name (ideally something safe like public)
#


# Global Variables
USERNAME=''
AWS_PROFILE=''

# Specify binary locations
PSQL=$(/usr/bin/which psql)
MYSQL=$(/usr/bin/which mysql)
SNOWSQL=$(which snowsql)

# Redshift info
REDSHIFT_DEV_HOSTS='dev-db02.redshift.theorchard.com'
REDSHIFT_PROD_HOSTS=('db04.redshift.theorchard.com' 'db07.redshift.theorchard.com' 'db10.redshift.theorchard.com')
REDSHIFT_PORT='5439'
REDSHIFT_DEFAULT_DB='dev'

# MySQL info
MYSQL_DEV_SERVER_HOST=('accounting.db.devorch.com' \
'build.db.devorch.com' \
'data.db.devorch.com' \
'digital.db.devorch.com' \
'jess.db.devorch.com' \
'mr.db.devorch.com' \
'phys.db.devorch.com' \
'red.db.devorch.com' \
'vector.db.devorch.com' \
'vr.db.devorch.com' \
'vs.db.devorch.com')
MYSQL_REPORTSAR_HOST=('reportsar.theorchard.com' 'reportsar02.theorchard.com')
MYSQL_REPORTSDD_HOST='reportsdd.theorchard.com'
MYSQL_PROD_OWS_RDS_HOSTS=('prod-ows.rds.theorchard.com' 'prod-ows02.rds.theorchard.com')
MYSQL_QA_AR_SERVER_HOST='qa.db.qaorch.com'
MYSQL_QA_DD_SERVER_HOST='qadddb.qaorch.com'
MYSQL_QA_OWS_RDS_HOST='qa-ows.rds.theorchard.com'

# Service Flags
DELETE_AWS_DEV=false
DELETE_AWS_PROD=false
DELETE_AWS_SONGWHIP=false
DELETE_MYSQL_AR_DEV=false
DELETE_MYSQL_REPORTSAR=false
DELETE_MYSQL_REPORTSDD=false
DELETE_REDSHIFT_PROD=false
DELETE_MYSQL_AR_QA=false
DELETE_MYSQL_DD_QA=false
DELETE_MYSQL_OWS_RDS_PROD=false
DELETE_MYSQL_OWS_RDS_QA=false
DELETE_REDSHIFT_DEV=false
DELETE_SNOWFLAKE=false

VERBOSE=false

function verify_username {
  # Check to make sure username is present and does not contain unsupported characters.

  [ "${VERBOSE}" = true ] && printf '%s\n' "Verifying username"

  DISALLOWED_CHARS='[[:punct:]][[:space:]]'

  if [ -z "${USERNAME}" ]; then
    printf '%s\n' "Username cannot be empty. Please submit with the -u parameter." && exit 1
  elif [[ ${USERNAME} =~ ${DISALLOWED_CHARS} ]]; then
    printf '%s\n' "Username contains unsupported characters." && exit 1
  else
    return 0
  fi
}

function delete_aws_dev_user {
  # Delete an AWS dev IAM user

  export AWS_PROFILE='dev'

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing AWS dev IAM user"

  verify_username

  if $(aws iam get-user --user-name ${USERNAME} 2>/dev/null | grep -i ${USERNAME} &>/dev/null); then
    printf '%s\n' "${USERNAME} is an active AWS dev user. Moving forward with deletion..."
    if $(aws iam get-login-profile --user-name ${USERNAME} 2>/dev/null | grep -i ${USERNAME} &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS dev login profile found. Moving forward with deletion..."
      aws iam delete-login-profile --user-name ${USERNAME}
      printf '%s\n' "AWS dev login profile deleted."
    fi
    if $(aws iam list-access-keys --user-name ${USERNAME} 2>/dev/null | grep -i ${USERNAME} &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS dev access keys found. Moving forward with deletion..."
      for key in $(aws iam list-access-keys --user-name ${USERNAME} --query 'AccessKeyMetadata[*].AccessKeyId' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Deleting ${key}..."
        aws iam delete-access-key --user-name ${USERNAME} --access-key ${key}
      done
      printf '%s\n' "AWS dev keypairs deleted."
    fi
    if $(aws iam list-user-policies --user-name ${USERNAME} 2>/dev/null | grep -i "PolicyNames" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS dev inline policies found. Moving forward with deletion..."
      for policy in $(aws iam list-user-policies --user-name ${USERNAME} --query 'PolicyNames[*]' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Deleting ${policy}..."
        aws iam delete-user-policy --user-name ${USERNAME} --policy-name ${policy}
      done
      printf '%s\n' "AWS inline policies deleted."
    fi
    if $(aws iam list-attached-user-policies --user-name ${USERNAME} 2>/dev/null | grep -i "AttachedPolicies" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS dev managed policies found. Moving forward with detachment..."
      for managed_policy in $(aws iam list-attached-user-policies --user-name ${USERNAME} --query 'AttachedPolicies[*].PolicyArn' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Detaching ${managed_policy} from ${USERNAME}..."
        aws iam detach-user-policy --user-name ${USERNAME} --policy-arn ${managed_policy}
      done
      printf '%s\n' "AWS managed policies detached."
    fi
    if $(aws iam list-groups-for-user --user-name ${USERNAME} 2>/dev/null | grep -i "GroupName" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "Groups found for AWS dev user. Removing from groups..."
      for group in $(aws iam list-groups-for-user --user-name ${USERNAME} --query 'Groups[*].GroupName' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME} from ${group}..."
        aws iam remove-user-from-group --user-name ${USERNAME} --group-name ${group}
      done
      printf '%s\n' "Removed user from all groups."
    fi
    if $(aws iam list-mfa-devices --user-name ${USERNAME} 2>/dev/null | grep -i "SerialNumber" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "MFA device found on AWS dev user. Deactivating MFA Device..."
      for mfa_device in $(aws iam list-mfa-devices --user-name ${USERNAME} --query 'MFADevices[*].SerialNumber' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MFA Device ${mfa_device}..."
        aws iam deactivate-mfa-device --user-name ${USERNAME} --serial-number ${mfa_device}
      done
      printf '%s\n' "MFA device has been deactivated."
    fi

    aws iam delete-user --user-name ${USERNAME}
    printf '%s\n' "AWS dev user deleted."
  else
    printf '%s\n' "${USERNAME} is not an active AWS dev user. Skipping this step..."
  fi
}

function delete_aws_prod_user {
  # Delete an AWS prod IAM user

  export AWS_PROFILE='prod'

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing AWS prod IAM user"

  verify_username

  if $(aws iam get-user --user-name ${USERNAME} 2>/dev/null | grep -i ${USERNAME} &>/dev/null); then
    printf '%s\n' "${USERNAME} is an active AWS prod user. Moving forward with deletion..."
    if $(aws iam get-login-profile --user-name ${USERNAME} 2>/dev/null | grep -i ${USERNAME} &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS prod login profile found. Moving forward with deletion..."
      aws iam delete-login-profile --user-name ${USERNAME}
      printf '%s\n' "AWS prod login profile deleted."
    fi
    if $(aws iam list-access-keys --user-name ${USERNAME} 2>/dev/null | grep -i ${USERNAME} &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS prod access keys found. Moving forward with deletion..."
      for key in $(aws iam list-access-keys --user-name ${USERNAME} --query 'AccessKeyMetadata[*].AccessKeyId' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Deleting ${key}..."
        aws iam delete-access-key --user-name ${USERNAME} --access-key ${key}
      done
      printf '%s\n' "AWS prod keypairs deleted."
    fi
    if $(aws iam list-user-policies --user-name ${USERNAME} 2>/dev/null | grep -i "PolicyNames" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS prod inline policies found. Moving forward with deletion..."
      for policy in $(aws iam list-user-policies --user-name ${USERNAME} --query 'PolicyNames[*]' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Deleting ${policy}..."
        aws iam delete-user-policy --user-name ${USERNAME} --policy-name ${policy}
      done
      printf '%s\n' "AWS inline policies deleted."
    fi
    if $(aws iam list-attached-user-policies --user-name ${USERNAME} 2>/dev/null | grep -i "AttachedPolicies" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS prod managed policies found. Moving forward with detachment..."
      for managed_policy in $(aws iam list-attached-user-policies --user-name ${USERNAME} --query 'AttachedPolicies[*].PolicyArn' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Detaching ${managed_policy} from ${USERNAME}..."
        aws iam detach-user-policy --user-name ${USERNAME} --policy-arn ${managed_policy}
      done
      printf '%s\n' "AWS managed policies detached."
    fi
    if $(aws iam list-groups-for-user --user-name ${USERNAME} 2>/dev/null | grep -i "GroupName" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "Groups found for AWS prod user. Removing from groups..."
      for group in $(aws iam list-groups-for-user --user-name ${USERNAME} --query 'Groups[*].GroupName' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME} from ${group}..."
        aws iam remove-user-from-group --user-name ${USERNAME} --group-name ${group}
      done
      printf '%s\n' "Removed user from all groups."
    fi
    if $(aws iam list-mfa-devices --user-name ${USERNAME} 2>/dev/null | grep -i "SerialNumber" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "MFA device found on AWS prod user. Deactivating MFA Device..."
      for mfa_device in $(aws iam list-mfa-devices --user-name ${USERNAME} --query 'MFADevices[*].SerialNumber' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MFA Device ${mfa_device}..."
        aws iam deactivate-mfa-device --user-name ${USERNAME} --serial-number ${mfa_device}
      done
      printf '%s\n' "MFA device has been deactivated."
    fi

    aws iam delete-user --user-name ${USERNAME}
    printf '%s\n' "AWS prod user deleted."
  else
    printf '%s\n' "${USERNAME} is not an active AWS prod user. Skipping this step..."
  fi
}

function delete_aws_songwhip_user {
  # Delete an AWS Songwhip IAM user

  export AWS_PROFILE='songwhip'

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing AWS Songwhip IAM user"

  verify_username
  if $(aws iam get-user --user-name ${USERNAME} 2>/dev/null | grep -i ${USERNAME} &>/dev/null); then
    printf '%s\n' "${USERNAME} is an active AWS songwhip user. Moving forward with deletion..."
    if $(aws iam get-login-profile --user-name ${USERNAME} 2>/dev/null | grep -i ${USERNAME} &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS songwhip login profile found. Moving forward with deletion..."
      aws iam delete-login-profile --user-name ${USERNAME}
      printf '%s\n' "AWS songwhip login profile deleted."
    fi
    if $(aws iam list-access-keys --user-name ${USERNAME} 2>/dev/null | grep -i ${USERNAME} &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS songwhip access keys found. Moving forward with deletion..."
      for key in $(aws iam list-access-keys --user-name ${USERNAME} --query 'AccessKeyMetadata[*].AccessKeyId' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Deleting ${key}..."
        aws iam delete-access-key --user-name ${USERNAME} --access-key ${key}
      done
      printf '%s\n' "AWS songwhip keypairs deleted."
    fi
    if $(aws iam list-user-policies --user-name ${USERNAME} 2>/dev/null | grep -i "PolicyNames" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS songwhip inline policies found. Moving forward with deletion..."
      for policy in $(aws iam list-user-policies --user-name ${USERNAME} --query 'PolicyNames[*]' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Deleting ${policy}..."
        aws iam delete-user-policy --user-name ${USERNAME} --policy-name ${policy}
      done
      printf '%s\n' "AWS inline policies deleted."
    fi
    if $(aws iam list-attached-user-policies --user-name ${USERNAME} 2>/dev/null | grep -i "AttachedPolicies" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "AWS songwhip managed policies found. Moving forward with detachment..."
      for managed_policy in $(aws iam list-attached-user-policies --user-name ${USERNAME} --query 'AttachedPolicies[*].PolicyArn' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Detaching ${managed_policy} from ${USERNAME}..."
        aws iam detach-user-policy --user-name ${USERNAME} --policy-arn ${managed_policy}
      done
      printf '%s\n' "AWS managed policies detached."
    fi
    if $(aws iam list-groups-for-user --user-name ${USERNAME} 2>/dev/null | grep -i "GroupName" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "Groups found for AWS songwhip user. Removing from groups..."
      for group in $(aws iam list-groups-for-user --user-name ${USERNAME} --query 'Groups[*].GroupName' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME} from ${group}..."
        aws iam remove-user-from-group --user-name ${USERNAME} --group-name ${group}
      done
      printf '%s\n' "Removed user from all groups."
    fi
    if $(aws iam list-mfa-devices --user-name ${USERNAME} 2>/dev/null | grep -i "SerialNumber" &>/dev/null); then
      [ "${VERBOSE}" = true ] && printf '%s\n' "MFA device found on AWS songwhip user. Deactivating MFA Device..."
      for mfa_device in $(aws iam list-mfa-devices --user-name ${USERNAME} --query 'MFADevices[*].SerialNumber' --output text); do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MFA Device ${mfa_device}..."
        aws iam deactivate-mfa-device --user-name ${USERNAME} --serial-number ${mfa_device}
      done
      printf '%s\n' "MFA device has been deactivated."
    fi

    aws iam delete-user --user-name ${USERNAME}
    printf '%s\n' "AWS songwhip user deleted."
  else
    printf '%s\n' "${USERNAME} is not an active AWS songwhip user. Skipping this step..."
  fi
}

function delete_mysql_ar_dev_user {
  # Delete a MySQL dev art_relations db user

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MySQL dev art_relations db user"

  verify_username

  for DEV_DB_HOST in "${MYSQL_DEV_SERVER_HOST[@]}"; do
    [ "${VERBOSE}" = true ] && printf '%s\n' "Removing mysql user on ${DEV_DB_HOST}"

    MYSQL_COMMAND_STRING="${MYSQL} --defaults-group-suffix=${DEV_DB_HOST} -h ${DEV_DB_HOST} -N"

    # Check to see if there are multiple user/host pairs, and remove all of them.
    unset host_line
    unset mysql_ar_dev_hosts
    while read -r host_line; do
      mysql_ar_dev_hosts+=("${host_line}");
    done < <(${MYSQL_COMMAND_STRING} -e "select host from mysql.user where user = '${USERNAME}'")

    if [ ${#mysql_ar_dev_hosts[@]} = '0' ]; then
      printf '%s\n' "No MySQL art_relations dev users found on ${DEV_DB_HOST}."
    else
      for dev_host in "${mysql_ar_dev_hosts[@]}"; do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME}@'${dev_host}'..."
        echo "drop user '${USERNAME}'@'${dev_host}';" | ${MYSQL_COMMAND_STRING}
      done
      printf '%s\n' "MySQL dev art_relations users deleted from ${DEV_DB_HOST}."
    fi
  done
}

function delete_mysql_reportsar_user {
  # Delete a MySQL reportsar db user

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MySQL reportsar user"

  verify_username

  for DB_HOST in "${MYSQL_REPORTSAR_HOST[@]}"; do
    [ "${VERBOSE}" = true ] && printf '%s\n' "Removing mysql user on ${DB_HOST}"

    MYSQL_COMMAND_STRING="${MYSQL} --defaults-group-suffix=${DB_HOST} -h ${DB_HOST} -N"

    # Check to see if there are multiple user/host pairs, and remove all of them.
    unset host_line
    unset mysql_reportsar_hosts
    while read -r host_line; do
      mysql_reportsar_hosts+=("${host_line}");
    done < <(${MYSQL_COMMAND_STRING} -e "select host from mysql.user where user = '${USERNAME}'")

    if [ ${#mysql_reportsar_hosts[@]} = '0' ]; then
      printf '%s\n' "No MySQL reportsar users found on ${DB_HOST}."
    else
      for host in "${mysql_reportsar_hosts[@]}"; do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME}@'${host}'..."
        echo "drop user '${USERNAME}'@'${host}';" | ${MYSQL_COMMAND_STRING}
      done
      printf '%s\n' "MySQL reportsar users deleted from ${DB_HOST}."
    fi
  done
}

function delete_mysql_reportsdd_user {
  # Delete a MySQL reportsdd db user

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MySQL reportsdd user"

  verify_username

  MYSQL_COMMAND_STRING="${MYSQL} --defaults-group-suffix=${MYSQL_REPORTSDD_HOST} -h ${MYSQL_REPORTSDD_HOST} -N"

  # Check to see if there are multiple user/host pairs, and remove all of them.
  unset host_line
  while read -r host_line; do
    mysql_reportsdd_hosts+=("${host_line}");
  done < <(${MYSQL_COMMAND_STRING} -e "select host from mysql.user where user = '${USERNAME}'")

  if [ ${#mysql_reportsdd_hosts[@]} = '0' ]; then
    printf '%s\n' "No MySQL reportsdd user found."
    return 0
  fi

  for host in "${mysql_reportsdd_hosts[@]}"; do
    [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME}@'${host}'..."
    echo "drop user '${USERNAME}'@'${host}';" | ${MYSQL_COMMAND_STRING}
  done

  printf '%s\n' "MySQL reportsdd user deleted."
}

function delete_mysql_ar_qa_user {
  # Delete a MySQL qa art_relations db user

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MySQL qa art_relations user"

  verify_username

  MYSQL_COMMAND_STRING="${MYSQL} --defaults-group-suffix=${MYSQL_QA_AR_SERVER_HOST} -h ${MYSQL_QA_AR_SERVER_HOST} -N"

  # Check to see if there are multiple user/host pairs, and remove all of them.
  unset host_line
  while read -r host_line; do
    mysql_ar_qa_hosts+=("${host_line}");
  done < <(${MYSQL_COMMAND_STRING} -e "select host from mysql.user where user = '${USERNAME}'")

  if [ ${#mysql_ar_qa_hosts[@]} = '0' ]; then
    printf '%s\n' "No MySQL qa art_relations user found."
    return 0
  fi

  for host in "${mysql_ar_qa_hosts[@]}"; do
    [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME}@'${host}'..."
    echo "drop user '${USERNAME}'@'${host}';" | ${MYSQL_COMMAND_STRING}
  done

  printf '%s\n' "MySQL qa art_relations user deleted."
}

function delete_mysql_dd_qa_user {
  # Delete a MySQL qa direct_delivery db user

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MySQL qa direct_delivery user"

  verify_username

  MYSQL_COMMAND_STRING="${MYSQL} --defaults-group-suffix=${MYSQL_QA_DD_SERVER_HOST} -h ${MYSQL_QA_DD_SERVER_HOST} -N"

  # Check to see if there are multiple user/host pairs, and remove all of them.
  unset host_line
  while read -r host_line; do
    mysql_dd_qa_hosts+=("${host_line}");
  done < <(${MYSQL_COMMAND_STRING} -e "select host from mysql.user where user = '${USERNAME}'")

  if [ ${#mysql_dd_qa_hosts[@]} = '0' ]; then
    printf '%s\n' "No MySQL qa direct_delivery user found."
    return 0
  fi

  for host in "${mysql_dd_qa_hosts[@]}"; do
    [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME}@'${host}'..."
    echo "drop user '${USERNAME}'@'${host}';" | ${MYSQL_COMMAND_STRING}
  done

  printf '%s\n' "MySQL qa direct_delivery user deleted."
}

function delete_mysql_ows_rds_prod_user {
  # Delete a MySQL prod ows rds db user

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MySQL prod ows rds user"

  verify_username

  for DB_HOST in "${MYSQL_PROD_OWS_RDS_HOSTS[@]}"; do
    [ "${VERBOSE}" = true ] && printf '%s\n' "Removing mysql user on ${DB_HOST}"

    MYSQL_COMMAND_STRING="${MYSQL} --defaults-group-suffix=${DB_HOST} -h ${DB_HOST} -N"

    # Check to see if there are multiple user/host pairs, and remove all of them.
    unset host_line
    unset mysql_prod_ows_rds_hosts
    while read -r host_line; do
      mysql_prod_ows_rds_hosts+=("${host_line}");
    done < <(${MYSQL_COMMAND_STRING} -e "select host from mysql.user where user = '${USERNAME}'")

    if [ ${#mysql_prod_ows_rds_hosts[@]} = '0' ]; then
      printf '%s\n' "No MySQL Prod ows rds users found on ${DB_HOST}."
    else
      for host in "${mysql_prod_ows_rds_hosts[@]}"; do
        [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME}@'${host}'..."
        echo "drop user '${USERNAME}'@'${host}';" | ${MYSQL_COMMAND_STRING}
      done
      printf '%s\n' "MySQL Prod ows rds users deleted from ${DB_HOST}."
    fi
  done

}

function delete_mysql_ows_rds_qa_user {
  # Delete a MySQL qa ows rds db user

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing MySQL qa ows rds user"

  verify_username

  MYSQL_COMMAND_STRING="${MYSQL} --defaults-group-suffix=${MYSQL_QA_OWS_RDS_HOST} -h ${MYSQL_QA_OWS_RDS_HOST} -N"

  # Check to see if there are multiple user/host pairs, and remove all of them.
  unset host_line
  while read -r host_line; do
    mysql_qa_ows_rds_hosts+=("${host_line}");
  done < <(${MYSQL_COMMAND_STRING} -e "select host from mysql.user where user = '${USERNAME}'")

  if [ ${#mysql_qa_ows_rds_hosts[@]} = '0' ]; then
    printf '%s\n' "No MySQL qa ows rds user found."
    return 0
  fi

  for host in "${mysql_qa_ows_rds_hosts[@]}"; do
    [ "${VERBOSE}" = true ] && printf '%s\n' "Removing ${USERNAME}@'${host}'..."
    echo "drop user '${USERNAME}'@'${host}';" | ${MYSQL_COMMAND_STRING}
  done

  printf '%s\n' "MySQL qa ows rds user deleted."
}

function delete_redshift_dev_user {
  # Delete a Redshift dev user.

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing Redshift dev user"

  verify_username

  PSQL_COMMAND_STRING="${PSQL} -h ${REDSHIFT_DEV_HOSTS} -d ${REDSHIFT_DEFAULT_DB} -p ${REDSHIFT_PORT}"

  if $(echo "select usename from pg_user;" | ${PSQL_COMMAND_STRING} | grep -i ${USERNAME} > /dev/null); then
    printf '%s\n' "${USERNAME} is an active Redshift dev user. Moving forward with deletion..."

    echo "revoke all on all tables in schema staging from ${USERNAME};" | ${PSQL_COMMAND_STRING}
    echo "revoke all on all tables in schema production from ${USERNAME};" | ${PSQL_COMMAND_STRING}
    echo "revoke usage on schema production from ${USERNAME};" | ${PSQL_COMMAND_STRING}
    echo "alter group dw_queriers drop user ${USERNAME};" | ${PSQL_COMMAND_STRING}
    echo "revoke usage on schema staging from ${USERNAME};" | ${PSQL_COMMAND_STRING}
    echo "DROP USER ${USERNAME};" | ${PSQL_COMMAND_STRING}

    printf '%s\n' "Redshift dev user deleted successfully"
  else
    printf '%s\n' "Redshift dev user not found"
  fi
}

function delete_redshift_prod_user {
  # Delete a Redshift prod user.
  # All production clusters will have the user removed.

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing Redshift prod user"

  verify_username

  for HOST in "${REDSHIFT_PROD_HOSTS[@]}"; do
    [ "${VERBOSE}" = true ] && printf '%s\n' "Removing user on ${HOST}"

    PSQL_COMMAND_STRING="${PSQL} -h ${HOST} -d ${REDSHIFT_DEFAULT_DB} -p ${REDSHIFT_PORT}"

    if $(echo "select usename from pg_user;" | ${PSQL_COMMAND_STRING} | grep -i ${USERNAME} > /dev/null); then
      printf '%s\n' "${USERNAME} is an active Redshift prod user. Moving forward with deletion..."

      echo "revoke all on all tables in schema qa from ${USERNAME};" | ${PSQL_COMMAND_STRING}
      echo "revoke all on all tables in schema staging from ${USERNAME};" | ${PSQL_COMMAND_STRING}
      echo "revoke all on all tables in schema production from ${USERNAME};" | ${PSQL_COMMAND_STRING}
      echo "revoke usage on schema production from ${USERNAME};" | ${PSQL_COMMAND_STRING}
      echo "alter group dw_queriers drop user ${USERNAME};" | ${PSQL_COMMAND_STRING}
      echo "revoke usage on schema staging from ${USERNAME};" | ${PSQL_COMMAND_STRING}
      echo "revoke usage on schema qa from ${USERNAME};" | ${PSQL_COMMAND_STRING}
      echo "DROP USER ${USERNAME};" | ${PSQL_COMMAND_STRING}

      printf '%s\n' "Redshift prod user deleted successfully"
    else
      printf '%s\n' "Redshift prod user not found"
    fi
  done
}

function delete_snowflake_user {
  # Delete a Snowflake user. Roles and grants are removed automatically.

  [ "${VERBOSE}" = true ] && printf '%s\n' "Removing Snowflake user"

  verify_username

  echo "DROP USER ${USERNAME};" | ${SNOWSQL}
}

while getopts 'abcdefhmnopqrsu:v' flag; do
  case "${flag}" in
  a) DELETE_AWS_DEV=true ;;
  b) DELETE_AWS_PROD=true ;;
  c) DELETE_AWS_SONGWHIP=true ;;
  d) DELETE_MYSQL_DD_QA=true ;;
  e) DELETE_MYSQL_OWS_RDS_PROD=true ;;
  f) DELETE_MYSQL_OWS_RDS_QA=true ;;
  h) printf '%s\n'
     printf '%s\n' "This script automates the user deletion process"
     printf '%s\n' "Specify services to be deleted with the following options:"
     printf '%s\n'
     printf '%s\n' "-a: Delete an AWS Dev account"
     printf '%s\n' "-b: Delete an AWS Prod account"
     printf '%s\n' "-c: Delete an AWS Songwhip account"
     printf '%s\n' "-d: Delete a MySQL user from QA direct_delivery"
     printf '%s\n' "-e: Delete a read-only MySQL user from Prod ows RDS hosts"
     printf '%s\n' "-f: Delete a read-only MySQL user from QA ows RDS hosts"
     printf '%s\n' "-h: Print this message"
     printf '%s\n' "-m: Delete a MySQL user from specified squad dev dbs"
     printf '%s\n' "-n: Delete a MySQL user from reports_ar"
     printf '%s\n' "-o: Delete a MySQL user from reports_dd"
     printf '%s\n' "-p: Delete a Redshift user from production"
     printf '%s\n' "-q: Delete a MySQL user from QA art_relations"
     printf '%s\n' "-r: Delete a Redshift user from dev"
     printf '%s\n' "-s: Delete a Snowflake user"
     printf '%s\n' "-u: The username to be deleted e.g. ./user_deletion.sh -u some_user"
     printf '%s\n'
     printf '%s\n' "NOTE: this option must be specified first."
     printf '%s\n'
     printf '%s\n' "-v: Set verbose output"
     printf '%s\n'
     printf '%s\n' "Example: /user_deletion.sh -u orcharduser -a -d -m -n"
     exit 0;;
  m) DELETE_MYSQL_AR_DEV=true ;;
  n) DELETE_MYSQL_REPORTSAR=true ;;
  o) DELETE_MYSQL_REPORTSDD=true ;;
  p) DELETE_REDSHIFT_PROD=true ;;
  q) DELETE_MYSQL_AR_QA=true ;;
  r) DELETE_REDSHIFT_DEV=true ;;
  s) DELETE_SNOWFLAKE=true ;;
  u) USERNAME="${OPTARG}" ;;
  v) VERBOSE=true && printf '%s\n' "Verbose output enabled" ;;
  *) printf "Unexpected option ${flag}\n"
     exit 1 ;;
  esac
done

[ "${DELETE_AWS_DEV}" = true ] && delete_aws_dev_user
[ "${DELETE_AWS_PROD}" = true ] && delete_aws_prod_user
[ "${DELETE_MYSQL_DD_QA}" = true ] && delete_mysql_dd_qa_user
[ "${DELETE_MYSQL_AR_DEV}" = true ] && delete_mysql_ar_dev_user
[ "${DELETE_MYSQL_REPORTSAR}" = true ] && delete_mysql_reportsar_user
[ "${DELETE_MYSQL_REPORTSDD}" = true ] && delete_mysql_reportsdd_user
[ "${DELETE_MYSQL_OWS_RDS_PROD}" = true ] && delete_mysql_ows_rds_prod_user
[ "${DELETE_MYSQL_OWS_RDS_QA}" = true ] && delete_mysql_ows_rds_qa_user
[ "${DELETE_REDSHIFT_PROD}" = true ] && delete_redshift_prod_user
[ "${DELETE_MYSQL_AR_QA}" = true ] && delete_mysql_ar_qa_user
[ "${DELETE_REDSHIFT_DEV}" = true ] && delete_redshift_dev_user
[ "${DELETE_SNOWFLAKE}" = true ] && delete_snowflake_user