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

TS=$(date +%Y%m%d%H%M%S)
WORKDIR=/workspace/city-subcenter/db-migration
mkdir -p "$WORKDIR"
cd "$WORKDIR"

RAW="aizhct_init_to_aizhct_csfzx_${TS}.raw.sql"
CLEAN="aizhct_init_to_aizhct_csfzx_${TS}.sql"
LOG="aizhct_init_to_aizhct_csfzx_${TS}.log"

{
  echo "## start $(date)"

  echo "## create target database"
  mysql -h127.0.0.1 -P2883 \
    -uroot@sys#city_subcenter_ob \
    -p"QjsL5wozeBKxc2tyLmTf" \
    -Doceanbase \
    -e "DROP DATABASE IF EXISTS \`aizhct_csfzx\`;
        CREATE DATABASE \`aizhct_csfzx\` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
        GRANT ALL PRIVILEGES ON \`aizhct_csfzx\`.* TO 'zhct_user';"

  echo "## dump source aizhct_init"
  MYSQL_PWD="cpt2018@)!(" mysqldump \
    -h rds3nh30726o5tko5b9gro.mysql.rds.aliyuncs.com \
    -urooot \
    --default-character-set=utf8mb4 \
    --single-transaction \
    --skip-lock-tables \
    --quick \
    --hex-blob \
    --skip-extended-insert \
    --routines=false \
    --events=false \
    --triggers \
    --set-gtid-purged=OFF \
    aizhct_init > "$RAW"
  echo "raw bytes: $(wc -c < "$RAW")"

  echo "## clean sql"
  python3 - "$RAW" "$CLEAN" <<'PY'
from pathlib import Path
import re
import sys

raw = Path(sys.argv[1])
clean = Path(sys.argv[2])
s = raw.read_text(errors="ignore")
s = s.replace("CREATE DATABASE /*!32312 IF NOT EXISTS*/ `aizhct_init`", "CREATE DATABASE /*!32312 IF NOT EXISTS*/ `aizhct_csfzx`")
s = s.replace("USE `aizhct_init`;", "USE `aizhct_csfzx`;")
s = re.sub(r"^SET @@SESSION\.SQL_LOG_BIN=.*?;\n", "", s, flags=re.M)
s = re.sub(r"^SET @@GLOBAL\.GTID_PURGED=.*?;\n", "", s, flags=re.M)
s = re.sub(r"/\*!50013 DEFINER=`[^`]+`@`[^`]+` SQL SECURITY DEFINER \*/", "", s)
s = re.sub(r"DEFINER=`[^`]+`@`[^`]+`", "", s)
s = re.sub(r" ROW_FORMAT=\w+", "", s)
s = "SET SESSION ob_query_timeout=600000000;\nSET SESSION ob_trx_timeout=600000000;\n" + s
clean.write_text(s)
PY
  echo "clean bytes: $(wc -c < "$CLEAN")"

  echo "## import target"
  mysql -h127.0.0.1 -P2883 \
    -uzhct_user@sys#city_subcenter_ob \
    -p"Zhct_OB_20260615" \
    aizhct_csfzx < "$CLEAN"

  echo "## init business fields and local storage"
  mysql -h127.0.0.1 -P2883 \
    -uzhct_user@sys#city_subcenter_ob \
    -p"Zhct_OB_20260615" \
    aizhct_csfzx <<'SQL'
UPDATE yoshop_dishes SET business = 'aizhct_csfzx';
UPDATE yoshop_dishes_relation SET business = 'aizhct_csfzx';
UPDATE yoshop_foods SET business = 'aizhct_csfzx';
UPDATE yoshop_foods_relation SET business = 'aizhct_csfzx';
UPDATE yoshop_store_setting
SET `values` = '{"default":"local","engine":{"qiniu":{"bucket":"","access_key":"","secret_key":"","domain":"http:\/\/"},"aliyun":{"bucket":"","access_key_id":"","access_key_secret":"","domain":"http:\/\/"},"qcloud":{"bucket":"","region":"","secret_id":"","secret_key":"","domain":"http:\/\/"}}}'
WHERE `key` = 'storage';
SQL

  echo "## verify"
  mysql -h127.0.0.1 -P2883 \
    -uzhct_user@sys#city_subcenter_ob \
    -p"Zhct_OB_20260615" \
    -N <<'SQL'
SELECT 'table_count', COUNT(*) FROM information_schema.tables WHERE table_schema='aizhct_csfzx';
SELECT 'store_setting_count', COUNT(*) FROM aizhct_csfzx.yoshop_store_setting;
SELECT 'dishes', business, COUNT(*) FROM aizhct_csfzx.yoshop_dishes GROUP BY business;
SELECT 'dishes_relation', business, COUNT(*) FROM aizhct_csfzx.yoshop_dishes_relation GROUP BY business;
SELECT 'foods', business, COUNT(*) FROM aizhct_csfzx.yoshop_foods GROUP BY business;
SELECT 'foods_relation', business, COUNT(*) FROM aizhct_csfzx.yoshop_foods_relation GROUP BY business;
SELECT `key`, `values` FROM aizhct_csfzx.yoshop_store_setting WHERE `key`='storage';
SQL

  echo "## done $(date)"
} 2>&1 | tee "$LOG"

printf "MIGRATION_LOG=%s\nCLEAN_SQL=%s\n" "$WORKDIR/$LOG" "$WORKDIR/$CLEAN"