#!/bin/bash
# A menu driven shell script sample template
# src: https://bash.cyberciti.biz/guide/Menu_driven_scripts
#
# Purpose: Sync MySQL Databases for Websites across different stages (live, dev, testing)
## ----------------------------------
# Step #1: Define global variables
# ----------------------------------
 
RED='\033[0;41;30m'
STD='\033[0;0;39m'
TODAY=$(date +"%Y-%m-%d")

## ----------------------------------
# Define the arrays
# indexes as follows:
#	0 = type : wordpress | magento
#	1 = host : my.database-ho.st
#	2 = user : username
#	3 = database name : name of the database
#	4 = password
#	5 = subdomain / prefix : for Magento's core_config_data change and to compare $SRC with $DEST
# ----------------------------------
 
declare -a live_magento=(magento my-live.database-ho.st user database password www)
declare -a live_wordpress=(wordpress my-live.database-ho.st user database password www)
declare -a dev_magento=(magento my-dev.database-ho.st user database password dev)
declare -a dev_wordpress=(wordpress my-dev.database-ho.st user database password dev)
declare -a testing_magento=(magento my-test.database-ho.st user database password testing)
declare -a testing_wordpress=(wordpress my-test.database-ho.st user database password testing)
declare -a dev2_magento=(magento my-dev.database-ho.st user database password dev2)
declare -a dev2_wordpress=(wordpress my-dev.database-ho.st user database password dev2)

# ----------------------------------
# Step #2: User defined function
# ----------------------------------
 
init() {
	clear
	echo "##################################"
	echo "## A simple script to keep your ##"
	echo "## MySQL Databases in sync.     ##"
	echo "##################################"
}
 
select_source_menu() {
	echo ""
	echo "Select a SOURCE database."
	echo ""
	echo "1. Live"
	echo "2. Dev"
	echo "3. Testing"
	echo "4. Nav"
	echo "~~~~~~~~~~~~~~~~~~~~~"
	echo "0. Exit"
	read_options_1
}
 
select_destination_menu() {
	init
	echo ""
	echo "Select a DESTINATION database."
	echo ""
	echo "5. Live"
	echo "6. Dev"
	echo "7. Testing"
	echo "8. Nav"
	echo "~~~~~~~~~~~~~~~~~~~~~"
	echo "0. Exit"
	read_options_2
}
 
select_confirmation_menu() {
	get_db_variables
	init
	echo ""
	echo "### ATTENTION!!! ###"
	echo "This will delete all information in the $DEST-database."
	echo ""
	echo "Are you sure?"
	echo ""
	echo "(y)es"
	echo "(n)ot really"
	echo "~~~~~~~~~~~~~~~~~~~~~"
	echo "0. Exit"
	read_options_2
}
 
select_remains_menu() {
	init
	echo ""
	echo "What about the created SQL dumpfiles?"
	echo "What are we gonna do with them?"
	echo ""
	echo "(k)eep them as zipped backup-file."
	echo "(d)elete them."
	read_options_2
}
 
select_restart_menu() {
	init
	echo ""
	echo "All done. What now?"
	echo ""
	echo "r) Restart sync."
	echo "0) Exit."
	read_options_2
}

# read input from the keyboard and take a action
# Exit when user the user select 0 form the menu option.
read_options_1(){
	local choice
	read -p "Enter choice: " choice
	case $choice in
		1) SRC=live;select_destination_menu ;;
		2) SRC=dev;select_destination_menu ;;
		3) SRC=testing;select_destination_menu ;;
		4) SRC=nav;select_destination_menu ;;
		0) exit 1 ;;
		*) echo -e "${RED}Error...${STD}" && sleep 2 && start
	esac
}
 
read_options_2(){
	local choice
	read -p "Enter choice: " choice
	case $choice in
		5) DEST=live;select_confirmation_menu ;;
		6) DEST=dev;select_confirmation_menu ;;
		7) DEST=testing;select_confirmation_menu ;;
		8) DEST=nav;select_confirmation_menu ;;
		y) sync_db ;;
		n) start ;;
		k) backup ;;
		d) delete_files ;;
		r) start ;;
		82517) sync_db ;;
		0) exit 1 ;;
		*) echo -e "${RED}Error...${STD}" && sleep 2 && select_destination_menu
	esac
}

get_db_variables(){
	case $SRC in
		live) SRC_MAGE=("${live_magento[@]}");SRC_WP=("${live_wordpress[@]}") ;;
		dev) SRC_MAGE=("${dev_magento[@]}");SRC_WP=("${dev_wordpress[@]}") ;;
		testing) SRC_MAGE=("${testing_magento[@]}");SRC_WP=("${testing_wordpress[@]}") ;;
		nav) SRC_MAGE=("${nav_magento[@]}");SRC_WP=("${nav_wordpress[@]}") ;;
	esac
 
	case $DEST in
		live) DEST_MAGE=("${live_magento[@]}");DEST_WP=("${live_wordpress[@]}") ;;
		dev) DEST_MAGE=("${dev_magento[@]}");DEST_WP=("${dev_wordpress[@]}") ;;
		testing) DEST_MAGE=("${testing_magento[@]}");DEST_WP=("${testing_wordpress[@]}") ;;
		nav) DEST_MAGE=("${nav_magento[@]}");DEST_WP=("${nav_wordpress[@]}") ;;
	esac
 
	if [ ${SRC_MAGE[5]} == ${DEST_MAGE[5]} ]; then
		echo -e "${RED}Error: source and destination must not be the same.${STD}"
		echo -e "${RED}Source is: ${SRC_MAGE[5]}.${STD}"
		echo -e "${RED}Destination is: ${DEST_MAGE[5]}.${STD}" && sleep 5
		start
	fi

	if [ ${DEST_MAGE[5]} == www ]; then
		init
		echo ""
		echo "##################################"
		echo "########## ATTENTION!!!! #########"
		echo "##################################"
		echo ""
		echo "This will delete all information in the $DEST-database and"
		echo "will overwrite it with information from $SRC"
		echo ""
		echo "##################################"
		echo "There is NO WAY of restoring the data from $DEST"
		echo "unless you made a backup once."
		echo ""
		echo "##################################"
		echo ""
		echo "So: Are you REALLY DEFINITELY sure?"
		echo ""
		echo "##################################"
		echo ""
		echo "Enter 82517 for yes."
		echo "(n) to abort."
		echo "~~~~~~~~~~~~~~~~~~~~~"
		echo "0. Exit"
		read_options_2
	fi
}

sync_db(){
	## The real purpose of this bash script
	echo "Now copying MySQL databases from $SRC to $DEST..."
	# indexes as follows:
	#	0 = type : wordpress | magento
	#	1 = host : db13.variomedia.de
	#	2 = user : u36052 | u36065 ..
	#	3 = database name : db36052 | db36065 ..
	#	4 = password
	#	5 = subdomain / prefix : for Magento's core_config_data change and to compare $SRC with $DEST
 
	mysqldump -h ${SRC_MAGE[1]} -u ${SRC_MAGE[2]} -p${SRC_MAGE[4]} ${SRC_MAGE[3]} > ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql
 
	echo "Magento DB dumped to ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql"
 
	mysqldump -h ${SRC_WP[1]} -u ${SRC_WP[2]} -p${SRC_WP[4]} ${SRC_WP[3]} > ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql
 
	echo "WordPress DB dumped to ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql"
	echo "Now importing into $DEST database..."
 
	mysql -h ${DEST_MAGE[1]} -u ${DEST_MAGE[2]} -p${DEST_MAGE[4]} ${DEST_MAGE[3]} < ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql
 
	echo "Magento DB imported from ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql"
 
	mysql -h ${DEST_WP[1]} -u ${DEST_WP[2]} -p${DEST_WP[4]} ${DEST_WP[3]} < ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql
 
	echo "WordPress DB imported from ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql"
	echo "Now changing the 'base_url's for the destination Magento-DB."
 
	mysql -h ${DEST_MAGE[1]} -u ${DEST_MAGE[2]} -p${DEST_MAGE[4]} ${DEST_MAGE[3]} -e 'UPDATE `core_config_data` SET `value` = "http://'${DEST_MAGE[5]}'.my-website.com/" WHERE `core_config_data`.`config_id` = 7; UPDATE `core_config_data` SET `value` = "http://'${DEST_MAGE[5]}'.my-website.com/" WHERE `core_config_data`.`config_id` = 8;'
 
	echo "Copying databases done. Now clearing ${DEST_MAGE[5]} Magento cache" && sleep 2
 
	rm -rfv ~/${DEST_MAGE[5]}/var/cache/
	select_remains_menu
}

backup(){
	echo "Zipping SQL dumpfiles and saving them as backup..."
	cat ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql | gzip -v > ~/sql-backup/$TODAY-sql-backup-${SRC_MAGE[5]}.gz
	delete_files
}
 
delete_files(){
	echo "Deleting sql-files..."
	rm -fv ~/sql-backup/$TODAY-${SRC_MAGE[0]}-${SRC_MAGE[5]}.sql ~/sql-backup/$TODAY-${SRC_WP[0]}-${SRC_WP[5]}.sql
	select_restart_menu
}

# ----------------------------------------------
# Step #3: Trap CTRL+C, CTRL+Z and quit singles
# ----------------------------------------------
trap '' SIGINT SIGQUIT SIGTSTP
 
# -----------------------------------
# Step #4: Main logic - infinite loop
# ------------------------------------
 
 
start() {
	unset -v SRC
	unset -v DEST
	unset -v SRC_MAGE
	unset -v SRC_WP
	unset -v DEST_MAGE
	unset -v DEST_WP
	init
	select_source_menu
}
 
start