#!/usr/bin/env bash
set -euo pipefail

readonly MODE="${1:-precheck}"
readonly TARGET_DB="zhct_school"
readonly PHP_BIN="/usr/local/php7.3/bin/php"
readonly PROJECT_ROOT="/workspace/wwwroot/zhct_school/project"
readonly CONFIG_FILE="$PROJECT_ROOT/application/config/prod/zhct_school/database.php"
readonly MENU_SQL="/tmp/school-nutrition-menu-bootstrap-4bfc3f40.sql"
readonly BACKUP_DIR="/workspace/wwwroot/log/zhct_school/database-backups"
readonly EXECUTION_LOG_DIR="/workspace/wwwroot/log/zhct_school/database-migrations"

case "$MODE" in
  precheck|execute|postcheck) ;;
  *) echo "Unsupported mode: $MODE" >&2; exit 64 ;;
esac

declare -ar SQL_FILES=(
  "$PROJECT_ROOT/db/v2/v2.36.4.sql"
  "$PROJECT_ROOT/db/v2/v2.36.5.sql"
  "$PROJECT_ROOT/db/v2/v2.36.6.sql"
  "$PROJECT_ROOT/db/v2/v2.36.7.sql"
  "$PROJECT_ROOT/db/v2/v2.36.8.sql"
  "$PROJECT_ROOT/db/v2/v2.36.9.sql"
)

declare -ar EXPECTED_SHA256=(
  "ae759e66b2bda9b124f9ee22a44847e606717d9e129f480562ca474e90f4b4cc"
  "4ed291a893706c0b19609d4c99c28bce2d94b71a330666260d1b7136f4a2d051"
  "807d81ab391e4dff6404852b8e53f9d035ffd89966e21679a5a7e78df8ec11d7"
  "b067559042208e652fb83112cc32865f5f99e216a60c31aefe1f5cf2b8154269"
  "bd48006c6f3454cce5b2fa3aef4f0eb05bb555048dc5c5d713fa1b4370645896"
  "d64ecd52ca4912d06927e4333f2e69d90eb39eef9e86335c9cd909de1f780291"
)

test -f "$CONFIG_FILE"
test -f "$MENU_SQL"
command -v mysql >/dev/null
command -v mysqldump >/dev/null
command -v gzip >/dev/null

for index in "${!SQL_FILES[@]}"; do
  test -f "${SQL_FILES[$index]}"
  actual_sha="$(sha256sum "${SQL_FILES[$index]}" | awk '{print $1}')"
  test "$actual_sha" = "${EXPECTED_SHA256[$index]}"
done

test "$(sha256sum "$MENU_SQL" | awk '{print $1}')" = "4bfc3f4090e1ed4476ae25ecfc0d45be0ccff47188fbc3d1f7201f4289322467"

eval "$("$PHP_BIN" -r '
$config = [];
require $argv[1];
foreach (["hostname", "username", "password"] as $required) {
    if (!isset($config[$required]) || $config[$required] === "") {
        fwrite(STDERR, "Missing Store database configuration: {$required}\n");
        exit(1);
    }
}
echo "STORE_DB_HOST=" . escapeshellarg($config["hostname"]) . PHP_EOL;
echo "STORE_DB_PORT=" . escapeshellarg($config["hostport"] ?? "3306") . PHP_EOL;
echo "STORE_DB_USER=" . escapeshellarg($config["username"]) . PHP_EOL;
echo "STORE_DB_PASS=" . escapeshellarg($config["password"]) . PHP_EOL;
' "$CONFIG_FILE")"

trap 'unset STORE_DB_HOST STORE_DB_PORT STORE_DB_USER STORE_DB_PASS MYSQL_PWD' EXIT

mysql_query() {
  MYSQL_PWD="$STORE_DB_PASS" mysql \
    --protocol=TCP \
    --connect-timeout=10 \
    --default-character-set=utf8mb4 \
    -h "$STORE_DB_HOST" \
    -P "$STORE_DB_PORT" \
    -u "$STORE_DB_USER" \
    "$TARGET_DB" \
    -N -B \
    -e "$1"
}

run_sql_file() {
  local phase="$1"
  local sql_file="$2"
  echo "PHASE_${phase}_START=$(date '+%F %T %z')"
  MYSQL_PWD="$STORE_DB_PASS" mysql \
    --protocol=TCP \
    --connect-timeout=10 \
    --default-character-set=utf8mb4 \
    -h "$STORE_DB_HOST" \
    -P "$STORE_DB_PORT" \
    -u "$STORE_DB_USER" \
    "$TARGET_DB" < "$sql_file"
  echo "PHASE_${phase}_END=$(date '+%F %T %z')"
}

precheck() {
  echo "PRECHECK_AT=$(date '+%F %T %z')"
  mysql_query "
SELECT CONCAT('CURRENT_DB|', DATABASE());
SELECT CONCAT('MYSQL_VERSION|', VERSION());
SELECT CONCAT('SCHOOL_TABLES|', COUNT(*))
FROM information_schema.tables
WHERE table_schema=DATABASE() AND table_name LIKE 'ydy_school_%';
SELECT CONCAT('SCHOOL_MENUS|', COUNT(*), '|DISTINCT|', COUNT(DISTINCT uuid))
FROM ydy_menu
WHERE uuid LIKE 'B1366000-%';
SELECT CONCAT('EGG_DAIRY_NUT_COLUMN|', COUNT(*))
FROM information_schema.columns
WHERE table_schema=DATABASE()
  AND table_name='ydy_school_meal_template'
  AND column_name='egg_dairy_nut_count';"
}

postcheck() {
  echo "POSTCHECK_AT=$(date '+%F %T %z')"
  mysql_query "
SELECT CONCAT('SCHOOL_TABLES|', COUNT(*))
FROM information_schema.tables
WHERE table_schema=DATABASE() AND table_name LIKE 'ydy_school_%';
SELECT CONCAT('SCHOOL_MENUS|', COUNT(*), '|DISTINCT|', COUNT(DISTINCT uuid))
FROM ydy_menu
WHERE uuid LIKE 'B1366000-%';
SELECT CONCAT('SCHOOL_MENU_LEVELS|', level, '|', COUNT(*))
FROM ydy_menu
WHERE uuid LIKE 'B1366000-%'
GROUP BY level
ORDER BY level;
SELECT CONCAT('EGG_DAIRY_NUT_COLUMN|', COUNT(*))
FROM information_schema.columns
WHERE table_schema=DATABASE()
  AND table_name='ydy_school_meal_template'
  AND column_name='egg_dairy_nut_count';
SELECT CONCAT('MISSING_REQUIRED_TABLES|', COUNT(*))
FROM (
  SELECT 'ydy_school_nutrition_plan' table_name UNION ALL
  SELECT 'ydy_school_nutrition_plan_version' UNION ALL
  SELECT 'ydy_school_nutrition_plan_meal' UNION ALL
  SELECT 'ydy_school_nutrition_plan_dish' UNION ALL
  SELECT 'ydy_school_nutrition_plan_ingredient' UNION ALL
  SELECT 'ydy_school_nutrition_plan_log' UNION ALL
  SELECT 'ydy_school_nutrition_target_group' UNION ALL
  SELECT 'ydy_school_meal_template' UNION ALL
  SELECT 'ydy_school_purchase_rule_version' UNION ALL
  SELECT 'ydy_school_purchase_rule_item' UNION ALL
  SELECT 'ydy_school_purchase_demand' UNION ALL
  SELECT 'ydy_school_purchase_demand_item' UNION ALL
  SELECT 'ydy_school_purchase_demand_trace' UNION ALL
  SELECT 'ydy_school_purchase_demand_log' UNION ALL
  SELECT 'ydy_school_special_meal_verification' UNION ALL
  SELECT 'ydy_school_menu_rule_version' UNION ALL
  SELECT 'ydy_school_menu_rule_item' UNION ALL
  SELECT 'ydy_school_nutrition_recommendation' UNION ALL
  SELECT 'ydy_school_nutrition_recommendation_task' UNION ALL
  SELECT 'ydy_school_special_meal_verification_item'
) required
LEFT JOIN information_schema.tables actual
  ON actual.table_schema=DATABASE()
 AND actual.table_name=required.table_name
WHERE actual.table_name IS NULL;
SELECT CONCAT('DUPLICATE_SCHOOL_MENU_UUIDS|', COUNT(*))
FROM (
  SELECT uuid FROM ydy_menu
  WHERE uuid LIKE 'B1366000-%'
  GROUP BY uuid HAVING COUNT(*) > 1
) duplicates;"
}

precheck

if [ "$MODE" = "precheck" ]; then
  exit 0
fi

if [ "$MODE" = "postcheck" ]; then
  postcheck
  exit 0
fi

test "$(mysql_query "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name LIKE 'ydy_school_%';")" = "0"
test "$(mysql_query "SELECT COUNT(*) FROM ydy_menu WHERE uuid LIKE 'B1366000-%';")" = "0"

mkdir -p "$BACKUP_DIR" "$EXECUTION_LOG_DIR"
chmod 700 "$BACKUP_DIR" "$EXECUTION_LOG_DIR"
readonly EXECUTION_STAMP="$(date '+%Y%m%d-%H%M%S')"
readonly BACKUP_FILE="$BACKUP_DIR/zhct_school-before-nutrition-${EXECUTION_STAMP}.sql.gz"
readonly EXECUTION_LOG="$EXECUTION_LOG_DIR/school-nutrition-${EXECUTION_STAMP}.log"
touch "$EXECUTION_LOG"
chmod 600 "$EXECUTION_LOG"
exec > >(tee -a "$EXECUTION_LOG") 2>&1

echo "BACKUP_STARTED_AT=$(date '+%F %T %z')"
MYSQL_PWD="$STORE_DB_PASS" mysqldump \
  --protocol=TCP \
  --host="$STORE_DB_HOST" \
  --port="$STORE_DB_PORT" \
  --user="$STORE_DB_USER" \
  --single-transaction \
  --quick \
  --skip-lock-tables \
  --routines \
  --triggers \
  --events \
  --hex-blob \
  --default-character-set=utf8mb4 \
  --set-gtid-purged=OFF \
  --databases "$TARGET_DB" | gzip -1 > "$BACKUP_FILE"
gzip -t "$BACKUP_FILE"
test -s "$BACKUP_FILE"
chmod 600 "$BACKUP_FILE"
echo "BACKUP_FINISHED_AT=$(date '+%F %T %z')"
echo "BACKUP_FILE=$BACKUP_FILE"
echo "BACKUP_BYTES=$(stat -c %s "$BACKUP_FILE")"
echo "BACKUP_SHA256=$(sha256sum "$BACKUP_FILE" | awk '{print $1}')"

run_sql_file "01_V2_36_4" "${SQL_FILES[0]}"
echo "READBACK_01_SCHOOL_TABLES=$(mysql_query "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name LIKE 'ydy_school_%';")"

run_sql_file "02_MENU_BOOTSTRAP" "$MENU_SQL"
echo "READBACK_02_SCHOOL_MENUS=$(mysql_query "SELECT COUNT(*) FROM ydy_menu WHERE uuid LIKE 'B1366000-%';")"

run_sql_file "03_V2_36_5" "${SQL_FILES[1]}"
echo "READBACK_03_RECOMMENDATION_TABLE=$(mysql_query "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='ydy_school_nutrition_recommendation';")"

run_sql_file "04_V2_36_6" "${SQL_FILES[2]}"
echo "READBACK_04_TASK_TABLE=$(mysql_query "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='ydy_school_nutrition_recommendation_task';")"

run_sql_file "05_V2_36_7" "${SQL_FILES[3]}"
echo "READBACK_05_VERIFY_ITEM_TABLE=$(mysql_query "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name='ydy_school_special_meal_verification_item';")"

run_sql_file "06_V2_36_8" "${SQL_FILES[4]}"
echo "READBACK_06_EXPORT_MENU=$(mysql_query "SELECT COUNT(*) FROM ydy_menu WHERE uuid='B1366000-0000-0000-0000-000000000027';")"

run_sql_file "07_V2_36_9" "${SQL_FILES[5]}"
echo "READBACK_07_EGG_DAIRY_NUT_COLUMN=$(mysql_query "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='ydy_school_meal_template' AND column_name='egg_dairy_nut_count';")"

postcheck
echo "MIGRATION_VERDICT=PASS"