#!/usr/bin/env bash

# set -o errexit          # Exit on most errors (see the manual)
# set -o errtrace         # Make sure any error trap is inherited
set -o nounset          # Disallow expansion of unset variables
# set -o pipefail         # Use last non-zero exit code in a pipeline
# set -o xtrace           # Trace the execution of the script (debug)

die () {
  echo "$1 FAILED at $(date)!"
  exit 1
}


DATE_NOW=$(date +%Y%m%d-%H%M%S)
readonly DATE_NOW

readonly DEV_DB_CLUSTER_IDENTIFIER="dev-apollo-aurora-cluster-${DATE_NOW}"
readonly DEV_DB_INSTANCE_IDENTIFIER="dev-apollo-aurora-master-${DATE_NOW}"
readonly PROD_DB_CLUSTER_IDENTIFIER="prod-apollo-aurora-enc"
readonly PROD_DB_CLUSTER_SNAPSHOT_IDENTIFIER="prod-apollo-aurora-enc-${DATE_NOW}"
readonly DEV_ACCOUNT_ID="475275892927"
readonly PROD_ACCOUNT_ID="323555055331"
readonly NUMBER_OF_SNAPSHOTS=1

readonly DB_NAME='dbSony_dbo'

TABLES_TO_MIGRATE=()
TABLES_TO_MIGRATE+=('tblBootstrap')
TABLES_TO_MIGRATE+=('tblApolloUserMarket')
TABLES_TO_MIGRATE+=('tblApolloMarket2')
TABLES_TO_MIGRATE+=('tblAppleMusicPlaylistTrackSummaryTemp')
TABLES_TO_MIGRATE+=('tblAppleMusicChartPlaylistMostPlayed')
TABLES_TO_MIGRATE+=('tblStarredContent')
TABLES_TO_MIGRATE+=('tblApolloVersionCarouselSlide')
TABLES_TO_MIGRATE+=('tblApolloUserVersionCarousel')
TABLES_TO_MIGRATE+=('tblApolloVersionCarousel')
TABLES_TO_MIGRATE+=('tblExportLogs')
TABLES_TO_MIGRATE+=('tblChartsMonitoring')
TABLES_TO_MIGRATE+=('tblApolloKeyValueStorage')
TABLES_TO_MIGRATE+=('tblAdminSearchHistory')

TABLES_TO_DELETE=()
TABLES_TO_DELETE+=('DELETED_6_08_tblPlaylistCategoryIcon')
TABLES_TO_DELETE+=('DELETED_6_08_tblSpotifyAnalyticsAccount')
TABLES_TO_DELETE+=('DELETED_6_08_tblSpotifyPlaylistTrackTopListCombination')
TABLES_TO_DELETE+=('tblAppleMusicChartSongMostPlayed')
TABLES_TO_DELETE+=('tblAppleMusicPlaylistTracklistHistoryReduced2')
TABLES_TO_DELETE+=('tblPlaylistCategoryPlaylist')
TABLES_TO_DELETE+=('tblPlaylistMarket')
TABLES_TO_DELETE+=('tblPromotedTrack')
TABLES_TO_DELETE+=('tblPromotedTrackTag')
TABLES_TO_DELETE+=('tblSonyUPC')
TABLES_TO_DELETE+=('tblSpotifyHotHitsPlaylistTrackSummary')
TABLES_TO_DELETE+=('tblSpotifyPlaylistChangeHistory')
TABLES_TO_DELETE+=('tblSpotifyPlaylistStream')
TABLES_TO_DELETE+=('tblSpotifyPlaylistTracklistHistory2Reduced2')
TABLES_TO_DELETE+=('tblSpotifyUserCountry')

echo "DB cluster snapshot take started at $(date) ..."

aws --profile gdb-delphi-prod \
  rds create-db-cluster-snapshot \
  --db-cluster-snapshot-identifier "${PROD_DB_CLUSTER_SNAPSHOT_IDENTIFIER}" \
  --db-cluster-identifier ${PROD_DB_CLUSTER_IDENTIFIER} || die "cluster snapshot take"

x=255

until [[ $x -eq 0 ]]; do
  aws --profile gdb-delphi-prod \
    rds describe-db-cluster-snapshots \
    --db-cluster-snapshot-identifier "${PROD_DB_CLUSTER_SNAPSHOT_IDENTIFIER}" | grep -q '"Status": "available"'
  x=$?
  sleep 5
done

echo -e "done at $(date)\n"


echo "DB cluster snapshot access allow started at $(date) ..."

aws --profile gdb-delphi-prod \
  rds modify-db-cluster-snapshot-attribute \
  --db-cluster-snapshot-identifier "${PROD_DB_CLUSTER_SNAPSHOT_IDENTIFIER}" \
  --attribute-name restore \
  --values-to-add '["'${DEV_ACCOUNT_ID}'"]' || die "cluster snapshot access allow"

echo -e "done at $(date)\n"


echo "DB cluster snapshot restore started at $(date) ..."

aws rds restore-db-cluster-from-snapshot \
  --db-cluster-identifier "${DEV_DB_CLUSTER_IDENTIFIER}" \
  --snapshot-identifier "arn:aws:rds:us-east-1:${PROD_ACCOUNT_ID}:cluster-snapshot:${PROD_DB_CLUSTER_SNAPSHOT_IDENTIFIER}" \
  --engine aurora-mysql \
  --engine-version '5.7.mysql_aurora.2.11.1' \
  --db-subnet-group-name dev-apollo-private_dbsubnetgroup \
  --vpc-security-group-ids 'sg-0c84ffdad0c4751c6' 'sg-05aa0d57ac71b736a' \
  --engine-mode provisioned \
  --kms-key-id arn:aws:kms:us-east-1:475275892927:key/143a560d-be5b-4e65-8f7b-6c9305e65032 \
  --db-cluster-parameter-group-name dev-apollo-s3-aurora-5-7 \
  --database-name sonyDB || die "cluster snapshot restore"

x=255

until [[ $x -eq 0 ]]; do
  aws rds describe-db-clusters \
    --db-cluster-identifier "${DEV_DB_CLUSTER_IDENTIFIER}" | grep -q '"Status": "available"'
  x=$?
  sleep 15
done

echo -e "done at $(date)\n"


echo "DB cluster add tags started at $(date) ..."

aws rds add-tags-to-resource \
  --resource-name "arn:aws:rds:us-east-1:${DEV_ACCOUNT_ID}:cluster:${DEV_DB_CLUSTER_IDENTIFIER}" \
  --tags Key=NAME,Value="${DEV_DB_CLUSTER_IDENTIFIER}" \
         Key=environment,Value=Development \
         Key=plat_env_project_service,Value=APL_DEV_DATA_AURA \
         Key=platform,Value=Apollo \
         Key=platform_with_env,Value="Apollo Development" \
         Key=project,Value=Data \
         Key=service,Value=Aurora

echo -e "done at $(date)\n"

echo "DB cluster modify started at $(date) ..."

aws rds modify-db-cluster \
  --backup-retention-period ${NUMBER_OF_SNAPSHOTS} \
  --db-cluster-identifier "${DEV_DB_CLUSTER_IDENTIFIER}" \
  --preferred-backup-window 22:00-22:30 \
  --preferred-maintenance-window sun:04:00-sun:04:30 || die "cluster modify"

echo -e "done at $(date)\n"


echo "DB cluster add role started at $(date) ..."

aws rds add-role-to-db-cluster \
  --db-cluster-identifier "${DEV_DB_CLUSTER_IDENTIFIER}" \
  --role-arn arn:aws:iam::${DEV_ACCOUNT_ID}:role/dev-apollo-aurora_s3

echo -e "done at $(date)\n"


echo "DB cluster instance creation started at $(date) ..."

aws rds create-db-instance \
  --db-instance-identifier "${DEV_DB_INSTANCE_IDENTIFIER}" \
  --db-instance-class db.r5.4xlarge \
  --engine aurora-mysql \
  --db-parameter-group-name default.aurora-mysql5.7 \
  --db-cluster-identifier "${DEV_DB_CLUSTER_IDENTIFIER}" \
  --availability-zone us-east-1c \
  --no-publicly-accessible \
  --no-auto-minor-version-upgrade \
  --preferred-maintenance-window sun:09:34-sun:10:04 || die "cluster instance creation"

x=255

until [[ $x -eq 0 ]]; do
  aws rds describe-db-instances \
    --db-instance-identifier "${DEV_DB_INSTANCE_IDENTIFIER}" | grep -q 'Status": "available"'
  x=$?
  sleep 15
done

echo -e "done at $(date)\n"


echo "DB cluster instance modify started at $(date) ..."

aws rds modify-db-instance \
  --db-instance-identifier "${DEV_DB_INSTANCE_IDENTIFIER}" \
  --ca-certificate-identifier rds-ca-rsa2048-g1 || die "cluster instance modify"

echo -e "done at $(date)\n"

echo "DB instance add tags started at $(date) ..."

aws rds add-tags-to-resource \
  --resource-name "arn:aws:rds:us-east-1:${DEV_ACCOUNT_ID}:db:${DEV_DB_INSTANCE_IDENTIFIER}" \
  --tags Key=NAME,Value="${DEV_DB_CLUSTER_IDENTIFIER}" \
         Key=environment,Value=Development \
         Key=plat_env_project_service,Value=APL_DEV_DATA_AURA \
         Key=platform,Value=Apollo \
         Key=platform_with_env,Value="Apollo Development" \
         Key=project,Value=Data \
         Key=service,Value=Aurora

echo -e "done at $(date)\n"

# SQL

src_clu='dev-apollo-aurora-cep.cluster-custom-cic9zqsxd2y6.us-east-1.rds.amazonaws.com'
dst_clu="dev-apollo-aurora-cluster-${DATE_NOW}.cluster-cic9zqsxd2y6.us-east-1.rds.amazonaws.com"

echo "dev-apollo-aurora-cluster-${DATE_NOW} MySQL cluster users/permissions adjust started at $(date) ..."

sony_prod_mysql_pass=$(aws secretsmanager get-secret-value --secret-id infra/administration/pwd/aurora_prod | jq -r '.SecretString' | jq -r '.password')

echo -e "[client]\npassword = $sony_prod_mysql_pass" > ~/.my.cnf
chmod 600 ~/.my.cnf

echo 'ALTER TABLE `tblSpotifyWeeklyTopPlaylist` MODIFY `PlaylistName` VARCHAR(1024) CHARACTER SET utf8mb4;' | mysql -h "${dst_clu}" -u sony dbSony_dbo || die "Failed to change charset for column"

sony_dev_mysql_pass=$(aws secretsmanager get-secret-value --secret-id infra/administration/pwd/aurora_dev | jq -r '.SecretString' | jq -r '.password')

echo "SET PASSWORD FOR 'sony'@'%' = '${sony_dev_mysql_pass}'" | mysql -h "${dst_clu}" -u sony mysql || die "MySQL sony user pass set"

echo -e "[client]\npassword = $sony_dev_mysql_pass" > ~/.my.cnf

mysql -h "${dst_clu}" -u sony -e "FLUSH PRIVILEGES;" mysql || die "MySQL flush privileges"

export TF_VAR_password="${$sony_dev_mysql_pass}"

export TF_VAR_host="${dst_clu}"

cd /root/Repos/infrastructure/Terraform/apollo_new/dev/databases/aurora_mysql && terraform init && terraform get && terraform apply --auto-approve

mysql -h "${dst_clu}" -u sony -e "FLUSH PRIVILEGES;" mysql || die "MySQL flush privileges"

echo -e "done at $(date)\n"

echo "DB clusters dev-apollo-aurora-cep -> dev-apollo-aurora-cluster-${DATE_NOW} dump/restore started at $(date) ..."

da_dev_mysql_pass=$(aws secretsmanager get-secret-value --secret-id apollo/dev/api/MYSQL_DB_PASS | jq -r '.SecretString')

echo -e "[client]\npassword = $da_dev_mysql_pass" > ~/.my.cnf
chmod 600 ~/.my.cnf

if [[ ! -e ./databases ]]; then
  echo "db data folder create started at $(date) ..."
  mkdir databases || die "db data folder create"
  echo -e "done at $(date)\n"
else
  rm -v ./databases/*
fi


for x in "${TABLES_TO_MIGRATE[@]}"; do

  DB_STRUCTURE="${DB_NAME}-${x}-structure-${DATE_NOW}.sql"

  DB_DATA="${DB_NAME}-${x}-data-${DATE_NOW}.sql"

  echo -n "${DB_NAME} DB ${x} table structure dump started at $(date) ... "
  mysqldump -h "${src_clu}" -u dataart -d --add-drop-table --no-tablespaces --set-gtid-purged=OFF -a ${DB_NAME} "${x}" > ./databases/"${DB_STRUCTURE}" || die "structure dump"
  echo "done at $(date)"

  echo -n "${DB_NAME} DB $x table data dump started at $(date) ... "
  mysqldump -h "${src_clu}" -u dataart -t -e --skip-triggers --no-tablespaces --set-gtid-purged=OFF ${DB_NAME} "${x}" > ./databases/"${DB_DATA}" || die "data dump"
  echo "done at $(date)"

done

# Dump elmah_error table

echo -n "elmah DB elmah_error table structure dump started at $(date) ... "
mysqldump -h "${src_clu}" -u dataart -d --add-drop-table --no-tablespaces --set-gtid-purged=OFF -a elmah elmah_error > ./databases/elmah-elmah_error-structure-${DATE_NOW}.sql || die "structure dump"
echo "done at $(date)"

echo -n "elmah DB elmah_error table data dump started at $(date) ... "
mysqldump -h "${src_clu}" -u dataart -t -e --skip-triggers --no-tablespaces --set-gtid-purged=OFF elmah elmah_error > ./databases/elmah-elmah_error-data-${DATE_NOW}.sql || die "data dump"
echo "done at $(date)"

for x in "${TABLES_TO_MIGRATE[@]}"; do

  DB_STRUCTURE="${DB_NAME}-$x-structure-${DATE_NOW}.sql"
  DB_DATA="${DB_NAME}-$x-data-${DATE_NOW}.sql"

  echo -n "${DB_NAME} DB $x table structure restore started at $(date) ... "
  mysql -h "${dst_clu}" -u dataart ${DB_NAME} < ./databases/"${DB_STRUCTURE}" || die "structure restore"
  echo "done at $(date)"

  echo -n "${DB_NAME} DB $x table data restore started at $(date) ... "
  mysql -h "${dst_clu}" -u dataart ${DB_NAME} < ./databases/"${DB_DATA}" || die "data restore"
  echo "done at $(date)"

done

# Restore elmah_error table

echo -n "elmah DB elmah_error table structure restore started at $(date) ... "
mysql -h "${dst_clu}" -u dataart elmah < ./databases/elmah-elmah_error-structure-${DATE_NOW}.sql || die "structure restore"
echo "done at $(date)"

echo -n "elmah DB elmah_error table data restore started at $(date) ... "
mysql -h "${dst_clu}" -u dataart elmah < ./databases/elmah-elmah_error-data-${DATE_NOW}.sql || die "data restore"
echo "done at $(date)"

echo -e "done at $(date)\n"

# drop unused tables
for x in "${TABLES_TO_DELETE[@]}"; do
  echo -n "drop unused tables started at $(date) ..."
  echo "DROP TABLE ${x};" | mysql -h "${dst_clu}" -u dataart ${DB_NAME} || die "data dump"
  echo -e "done at $(date)\n"
done

PREV_DEV_RDS_CLU=$(aws rds describe-db-cluster-endpoints --db-cluster-endpoint-identifier dev-apollo-aurora-cep | jq '.DBClusterEndpoints[].DBClusterIdentifier' | sed -e 's/"//g')
readonly PREV_DEV_RDS_CLU

# RDS ENDPOINT

echo "DB cluster endpoint delete started at $(date) ..."

aws rds delete-db-cluster-endpoint --db-cluster-endpoint-identifier dev-apollo-aurora-cep || die "cluster endpoint delete"

x=0

until [[ $x -ne 0 ]]; do
  aws rds describe-db-cluster-endpoints --db-cluster-endpoint-identifier dev-apollo-aurora-cep | grep -q '"Status": "deleting"'
  x=$?
  sleep 15
done

echo -e "done at $(date)\n"


echo "DB cluster endpoint create started at $(date) ..."

aws rds create-db-cluster-endpoint \
  --db-cluster-identifier "${DEV_DB_CLUSTER_IDENTIFIER}" \
  --db-cluster-endpoint-identifier dev-apollo-aurora-cep \
  --endpoint-type ANY \
  --static-members "dev-apollo-aurora-master-${DATE_NOW}" || die "cluster endpoint create"

x=255

until [[ $x -eq 0 ]]; do
  aws rds describe-db-cluster-endpoints \
    --db-cluster-endpoint-identifier dev-apollo-aurora-cep | grep -q '"Status": "available"'
  x=$?
  sleep 15
done

echo -e "done at $(date)\n"

#

echo "previous DB cluster (${PREV_DEV_RDS_CLU}) stop at $(date) ..."

aws rds stop-db-cluster --db-cluster-identifier "${PREV_DEV_RDS_CLU}"

x=255

until [[ $x -eq 0 ]]; do
  aws rds describe-db-clusters \
    --db-cluster-identifier "${PREV_DEV_RDS_CLU}" | jq '.DBClusters[].Status' | grep -q "stopped"
  x=$?
  sleep 15
done

echo -e "done at $(date)\n"

#

echo "DB cluster snapshot delete started at $(date) ..."

aws --profile gdb-delphi-prod \
  rds delete-db-cluster-snapshot \
  --db-cluster-snapshot-identifier "${PROD_DB_CLUSTER_SNAPSHOT_IDENTIFIER}" || die "cluster snapshot delete"

#x=0
#
#until [ $x -ne 0 ]; do
#  aws --profile gdb-delphi-prod rds describe-db-cluster-snapshots --db-cluster-snapshot-identifier "prod-apollo-aurora-${DATE_NOW}" | grep -q '"Status"'
#  x=$?
#  sleep 5
#done

echo "done at $(date)"