#!/bin/sh

# GRANT SELECT, INSERT, CREATE, DROP, ALTER ON `bgbilling`.* TO 'archive'@'192.168.164.7'

CONFIG=/etc/archive_tables.conf
if [ ! -f "$CONFIG" ] ; then
	echo "Config file $CONFIG not exist!"
	exit 1
fi
. "$CONFIG"
MYSQL="/usr/bin/mysql --defaults-file=$PROFILE"
MYSQLDUMP=/usr/bin/mysqldump

wait_wsrep() {
	echo "$PXC_HOSTS" \
		| sed -re 's/^\s*//g; s/#.*$//g; /^\s*$/d' \
		| while read PXC_HOST ; do
			WSREP_STATE=`$MYSQL -h $PXC_HOST -Ne 'SELECT variable_value FROM performance_schema.global_status WHERE variable_name = "wsrep_ready";'`
			if [ "$WSREP_STATE" != "ON" ] ; then
				echo "Cluster is out of sync, exiting"
				exit 2
			fi

			while true ; do
				#WSREP_CAP=`$MYSQL -h $PXC_HOST -Ne 'SELECT variable_value FROM performance_schema.global_status WHERE variable_name = "wsrep_flow_control_interval_high";'`
				CURRENT_QUEUE=`$MYSQL -h $PXC_HOST -Ne 'SELECT variable_value FROM performance_schema.global_status WHERE variable_name = "wsrep_local_recv_queue";'`
				if [ "$CURRENT_QUEUE" -eq 0 ] ; then
					break
				fi

				#echo "There still writesets in queue, sleeping..."
				sleep 30
			done
		done
}

move_archive() {
	local DB="$1"
	local TABLE="$2"

	if [ -z "$RUSER" -o -z "$RSERVER" -o -z "$RPATH" -o -z "$RKEY" ] ; then
		return 0
	fi

	local LFILE="$BACKUP_PATH$DB/$TABLE.sql.bz2"
	local RFILE="$RPATH$DB/$TABLE.sql.bz2"

	#echo "$LFILE -> $RUSER@$RSERVER:$RFILE"

	local LSIZE=`du -bs "$LFILE" | sed -r 's/^([0-9]+)[^0-9].*$/\1/g'`
	R=`ssh -n -i $RKEY $RUSER@$RSERVER du -bs "$RFILE" 2>/dev/null`
	if [ "$?" -ne "0" ] ; then
		local RSIZE="$R"
		ssh -n -i $RKEY $RUSER@$RSERVER mkdir -p "$RPATH$DB" 2>/dev/null
		rsync -e "ssh -i $RKEY" --remove-source-files "$LFILE" $RUSER@$RSERVER:"$RFILE" 2>/dev/null
		if [ "$?" -ne "0" ] ; then
			return 1
		fi
	else
		local RSIZE=`echo -n "$R" | sed -r 's/^([0-9]+)[^0-9].*$/\1/g'`
		#echo "$LSIZE" "$RSIZE"
		if [ "$LSIZE" -gt "$RSIZE" ] ; then
			rsync -e "ssh -i $RKEY" --remove-source-files "$LFILE" $RUSER@$RSERVER:"$RFILE" 2>/dev/null
			if [ "$?" -ne "0" ] ; then
				return 1
			fi
		else
			echo "$LFILE already exist!"
			rm "$LFILE"
		fi
	fi
	return 0
}

move_archives() {
	local DB="$1"

	if [ ! -d "$BACKUP_PATH/$DB" ] ; then
		return
	fi

	local O=`pwd`
	cd "$BACKUP_PATH/$DB"
	ls -1 \
		| sed -r '/\.sql\.bz2$/!d ; s/\.sql\.bz2$//g' \
		| while read TABLE ; do
			move_archive "$DB" "$TABLE"
			RET="$?"
			if [ "$RET" -ne "0" ] ; then
				echo "$DB.$TABLE: Error moving - $RET"
			fi
		done

	cd "$O"
}

archive_table() {
	local DB="$1"
	local TABLE="$2"
	local ARCHIVE="$3"
	local DROP="$4"

	local COUNT=`$MYSQL "$DB" -BNe "SELECT COUNT(*) FROM $TABLE;"`
	if [ "$COUNT" -ne "0" -a "$ARCHIVE" -ne "0" ] ; then
		if [ ! -d "$BACKUP_PATH/$DB" ] ; then
			mkdir -p "$BACKUP_PATH/$DB"
		fi

		#echo "$BACKUP_PATH/$DB/$TABLE.sql.bz2"
		$MYSQLDUMP --single-transaction  --set-gtid-purged=OFF "$DB" "$TABLE" | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' | bzip2 -9 -c > "$BACKUP_PATH/$DB/$TABLE.sql.bz2"
		local RET="$?"
		if [ "$RET" -ne "0" ] ; then
			rm "$BACKUP_PATH/$DB/$TABLE.sql.bz2"
			echo "$DB.$TABLE: Error dump - $RET"
			return 1
		fi

		bzgrep -qE "^CREATE TABLE " "$BACKUP_PATH/$DB/$TABLE.sql.bz2"
		if [ "$?" -ne "0" ] ; then
			echo "$DB.$TABLE: No CREATE TABLE in dump"
			return 1
		fi

		bzgrep -qE "^INSERT INTO " "$BACKUP_PATH/$DB/$TABLE.sql.bz2"
		if [ "$?" -ne "0" ] ; then
			echo "$DB.$TABLE: No INSERT INTO in dump"
			return 1
		fi

		move_archive "$DB" "$TABLE"
		local RET="$?"
		if [ "$RET" -ne "0" ] ; then
			echo "$DB.$TABLE: Error moving - $RET"
		fi
	fi

	if [ "$DROP" -ne "0" ] ; then
		wait_wsrep
		#echo "DROP TABLE $TABLE;"
		$MYSQL "$DB" -BNe "DROP TABLE $TABLE;"
	else
		local COUNT=`$MYSQL "$DB" -BNe "SELECT COUNT(*) FROM $TABLE;"`
		if [ "$COUNT" -ne "0" ] ; then
			case "$TRUNCATE_MODE" in
				"truncate")
					wait_wsrep
					#echo "TRUNCATE TABLE $TABLE;"
					$MYSQL "$DB" -BNe "TRUNCATE TABLE $TABLE;"
					;;
				"truncate_alter")
					wait_wsrep
					#echo "TRUNCATE TABLE $TABLE;"
					$MYSQL "$DB" -BNe "TRUNCATE TABLE $TABLE;"

					wait_wsrep
					#echo "ALTER TABLE $TABLE ENGINE=InnoDB;"
					$MYSQL "$DB" -BNe "ALTER TABLE $TABLE ENGINE=InnoDB;"
					;;
				"create_drop_rename")
					wait_wsrep
					#echo "CREATE TABLE _$TABLE LIKE $TABLE;"
					$MYSQL "$DB" -BNe "CREATE TABLE _$TABLE LIKE $TABLE;"
					#echo "DROP TABLE $TABLE;"
					$MYSQL "$DB" -BNe "DROP TABLE $TABLE;"
					#echo "RENAME TABLE _$TABLE TO $TABLE;"
					$MYSQL "$DB" -BNe "RENAME TABLE _$TABLE TO $TABLE;"
					;;
				*)
					echo "Wrong value of TRUNCATE_MODE"
					return 1
					;;
			esac
		fi
	fi
	return 0
}

archive_month_tables() {
	local DB="$1"
	local PATTERN="$2"
	local KEEP_MONTHS="$3"
	local ARCHIVE="$4"
	local DROP="$5"

	local KEEP_FROM_YYYYMM=`$MYSQL -BNe "SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL $KEEP_MONTHS MONTH), '%Y%m');"`

	$MYSQL "$DB" -BNe "SHOW TABLES LIKE '%\_______';" \
		| grep -E "$PATTERN" \
		| while read TABLE ; do
			local YYYYMM=`echo -n "$TABLE" | sed -re 's/^.*_([0-9]{6})$/\1/g'`
			if [ "$YYYYMM" -lt "$KEEP_FROM_YYYYMM" ] ; then
				archive_table "$DB" "$TABLE" "$ARCHIVE" "$DROP"
			fi
		done
}

archive_day_tables() {
	local DB="$1"
	local PATTERN="$2"
	local KEEP_DAYS="$3"
	local ARCHIVE="$4"
	local DROP="$5"

	local KEEP_FROM_YYYYMMDD=`$MYSQL -BNe "SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL $KEEP_DAYS DAY), '%Y%m%d');"`

	$MYSQL "$DB" -BNe "SHOW TABLES LIKE '%\_________';" \
		| grep -E "$PATTERN" \
		| while read TABLE ; do
			local YYYYMMDD=`echo -n "$TABLE" | sed -re 's/^.*_([0-9]{8})$/\1/g'`
			if [ "$YYYYMMDD" -lt "$KEEP_FROM_YYYYMMDD" ] ; then
				archive_table "$DB" "$TABLE" "$ARCHIVE" "$DROP"
			fi
		done
}

archive_year_parts() {
	local DB="$1"
	local TABLE="$2"
	local KEEP_YEARS="$3"
	local ARCHIVE="$4"
	local DROP="$5"

	local KEEP_FROM_YYYY=`$MYSQL -BNe "SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL $KEEP_YEARS YEAR), '%Y');"`

	$MYSQL information_schema -BNe "SELECT partition_name FROM partitions WHERE table_schema = '$DB' AND table_name = '$TABLE' AND partition_name REGEXP '[0-9]{4}$';" \
		| while read PART ; do
			local YYYY=`echo -n "$PART" | sed -re 's/^.*[^0-9]([0-9]{4})$/\1/g'`
			if [ "$YYYY" -lt "$KEEP_FROM_YYYY" ] ; then
				local COUNT=`$MYSQL information_schema -BNe "SELECT COUNT(*) FROM tables WHERE table_schema = '$DB' AND table_name = '${TABLE}_archive_$YYYY';"`
				if [ "$COUNT" -eq "0" ]; then
					$MYSQL "$DB" -BNe "CREATE TABLE ${TABLE}_archive_$YYYY LIKE $TABLE;"
					local RET="$?"
					if [ "$RET" -ne "0" ]; then
						echo "$DB.${TABLE}_archive_$YYYY: CREATE TABLE failed - $RET"
						return
					fi

					$MYSQL "$DB" -BNe "ALTER TABLE ${TABLE}_archive_$YYYY REMOVE PARTITIONING;"
					local RET="$?"
					if [ "$RET" -ne "0" ]; then
						echo "$DB.${TABLE}_archive_$YYYY: ALTER TABLE REMOVE PARTITIONING failed - $RET"
						return
					fi
				fi

				local COUNT=`$MYSQL "$DB" -BNe "SELECT COUNT(*) FROM ${TABLE}_archive_$YYYY;"`
				if [ "$COUNT" -eq "0" ]; then
					$MYSQL "$DB" -BNe "ALTER TABLE $TABLE EXCHANGE PARTITION $PART WITH TABLE ${TABLE}_archive_$YYYY;"
					local RET="$?"
					if [ "$RET" -ne "0" ]; then
						echo "$DB.${TABLE}: ALTER TABLE EXCHANGE PARTITION WITH TABLE failed - $RET"
						return
					fi

					$MYSQL "$DB" -BNe "ALTER TABLE $TABLE DROP PARTITION $PART;"
				fi

				archive_table "$DB" "${TABLE}_archive_$YYYY" "$ARCHIVE" "$DROP"
			fi
		done
}

echo "$ARCHIVE_SETS" \
	| sed -re 's/^\s*//g; s/#.*$//g; /^\s*$/d' \
	| while read SET ; do
		eval "DBS=\$DATABASES_$SET"
		eval "MONTH_TABLES=\$MONTH_TABLES_$SET"
		eval "DAY_TABLES=\$DAY_TABLES_$SET"
		eval "YEAR_PARTS=\$YEAR_PARTS_$SET"

		echo "$DBS" \
			| sed -re 's/^\s*//g; s/#.*$//g; /^\s*$/d' \
			| while read DB ; do
				echo "$MONTH_TABLES" \
					| sed -re 's/^\s*//g; s/#.*$//g; /^\s*$/d' \
					| while read PATTERN KEEP_MONTHS ARCHIVE DROP ; do
						#echo "$DB - $PATTERN"
						archive_month_tables "$DB" "$PATTERN" "$KEEP_MONTHS" "$ARCHIVE" "$DROP"
					done

				echo "$DAY_TABLES" \
					| sed -re 's/^\s*//g; s/#.*$//g; /^\s*$/d' \
					| while read PATTERN KEEP_DAYS ARCHIVE DROP ; do
						#echo "$DB - $PATTERN"
						archive_day_tables "$DB" "$PATTERN" "$KEEP_DAYS" "$ARCHIVE" "$DROP"
					done

				echo "$YEAR_PARTS" \
					| sed -re 's/^\s*//g; s/#.*$//g; /^\s*$/d' \
					| while read TABLE KEEP_YEARS ARCHIVE DROP ; do
						#echo "$DB - $PATTERN"
						archive_year_parts "$DB" "$TABLE" "$KEEP_YEARS" "$ARCHIVE" "$DROP"
					done

				move_archives "$DB"
			done
	done