#!/usr/bin/env bash
# Resync local MySQL's data from production TiDB.
#
# Rules:
#   - Schema is never touched. Schema is driven by `alembic upgrade head`.
#   - `platform_configs`, `alembic_version`, and `asset_category_configs`
#     are skipped so local seed rows / local migration pointer / schema
#     drift don't get clobbered.
#   - Any tables that exist on TiDB but not locally (event_normalized,
#     rel_relationships, wf_*) are skipped automatically — we only copy
#     the intersection.
#
# Usage:
#   ./scripts/sync-from-tidb.sh
#
# Requires:
#   - local MySQL reachable via docker container `helix-mysql-1`
#   - a commented-out TiDB `DATABASE_URL_SYNC` line in ./.env, OR
#   - TIDB_URL env var set to a mysql+pymysql://user:pwd@host:port/db URL
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
REPO_ROOT="$(cd "$SCRIPT_DIR/.." && pwd)"
ENV_FILE="$REPO_ROOT/.env"

# ── Locate TiDB URL ──
# Priority: $TIDB_URL env var > commented line in .env > uncommented in .env
extract_url() {
  # Grab the last mysql+pymysql URL that points at *.tidbcloud.com
  # from the .env file, whether it's commented (#) or active.
  grep -E '^[#[:space:]]*DATABASE_URL_SYNC=.*tidbcloud\.com' "$ENV_FILE" 2>/dev/null \
    | sed -E 's/^[#[:space:]]*DATABASE_URL_SYNC=//' \
    | tail -1
}

TIDB_URL="${TIDB_URL:-$(extract_url)}"
if [[ -z "$TIDB_URL" ]]; then
  echo "ERROR: No TiDB URL found." >&2
  echo "Either set TIDB_URL env var, or add a commented line like:" >&2
  echo "  # DATABASE_URL_SYNC=mysql+pymysql://USER:PASS@HOST:4000/helix?ssl=true" >&2
  echo "to $ENV_FILE" >&2
  exit 1
fi

# Parse: mysql+pymysql://user:pwd@host:port/db?...
URL_BODY="${TIDB_URL#mysql+pymysql://}"
CREDS="${URL_BODY%%@*}"
HOSTPART="${URL_BODY#*@}"
TIDB_USER="${CREDS%%:*}"
TIDB_PASSWORD="${CREDS#*:}"
HOST_PORT="${HOSTPART%%/*}"
TIDB_HOST="${HOST_PORT%%:*}"
TIDB_PORT="${HOST_PORT##*:}"
DB_REST="${HOSTPART#*/}"
TIDB_DB="${DB_REST%%\?*}"

echo "==> Source:  $TIDB_USER@$TIDB_HOST:$TIDB_PORT/$TIDB_DB"

# ── Local target ──
LOCAL_CONTAINER="${LOCAL_CONTAINER:-helix-mysql-1}"
LOCAL_USER="${LOCAL_USER:-helix}"
LOCAL_PASSWORD="${LOCAL_PASSWORD:-helix}"
LOCAL_DB="${LOCAL_DB:-helix}"
echo "==> Target:  $LOCAL_USER@$LOCAL_CONTAINER/$LOCAL_DB"

# ── Skip list ──
SKIP_TABLES=(
  platform_configs        # local has its own seed feishu row
  alembic_version         # local is the source of truth for migration head
  asset_category_configs  # prod has an extra column not in local schema
)

# ── Compute sync set ──
LOCAL_TABLES=$(docker exec "$LOCAL_CONTAINER" \
  mysql -u"$LOCAL_USER" -p"$LOCAL_PASSWORD" -h127.0.0.1 "$LOCAL_DB" \
  -N -e "SHOW TABLES" 2>/dev/null | sort)

TIDB_TABLES=$(docker exec "$LOCAL_CONTAINER" \
  mysql -u"$TIDB_USER" -p"$TIDB_PASSWORD" -h"$TIDB_HOST" -P"$TIDB_PORT" \
  --ssl-mode=VERIFY_IDENTITY "$TIDB_DB" \
  -N -e "SHOW TABLES" 2>/dev/null | sort)

SKIP_REGEX=$(printf "|^%s$" "${SKIP_TABLES[@]}")
SKIP_REGEX=${SKIP_REGEX#|}

TABLES=$(comm -12 <(echo "$LOCAL_TABLES") <(echo "$TIDB_TABLES") | grep -vE "$SKIP_REGEX" || true)

if [[ -z "$TABLES" ]]; then
  echo "ERROR: no tables to sync (is local MySQL up? run: docker compose up -d mysql)" >&2
  exit 1
fi

TABLE_COUNT=$(echo "$TABLES" | wc -l | tr -d ' ')
echo "==> Syncing $TABLE_COUNT tables (skipping: ${SKIP_TABLES[*]})"

# ── Truncate local target tables ──
TRUNCATES=$(echo "$TABLES" | awk '{printf "TRUNCATE TABLE %s; ", $0}')
docker exec "$LOCAL_CONTAINER" \
  mysql -u"$LOCAL_USER" -p"$LOCAL_PASSWORD" -h127.0.0.1 "$LOCAL_DB" \
  -e "SET FOREIGN_KEY_CHECKS=0; $TRUNCATES SET FOREIGN_KEY_CHECKS=1;" 2>/dev/null

# ── Dump + load ──
TABLES_LINE=$(echo "$TABLES" | tr '\n' ' ')

docker exec "$LOCAL_CONTAINER" bash -c "
(
  echo 'SET FOREIGN_KEY_CHECKS=0;'
  echo 'SET sql_mode=\"\";'
  echo 'SET SESSION time_zone=\"+00:00\";'
  mysqldump \
    -u'$TIDB_USER' -p'$TIDB_PASSWORD' -h'$TIDB_HOST' -P'$TIDB_PORT' --ssl-mode=VERIFY_IDENTITY \
    --column-statistics=0 --no-create-info --quick --skip-triggers \
    --set-gtid-purged=OFF --no-tablespaces --skip-lock-tables \
    --compact --extended-insert --complete-insert --hex-blob \
    '$TIDB_DB' $TABLES_LINE 2>/tmp/dump.err
  echo 'SET FOREIGN_KEY_CHECKS=1;'
) | mysql --force -u'$LOCAL_USER' -p'$LOCAL_PASSWORD' -h127.0.0.1 '$LOCAL_DB' 2>/tmp/load.err
"

ERR=$(docker exec "$LOCAL_CONTAINER" sh -c 'grep -c ERROR /tmp/load.err || true')
ERR="${ERR%%$'\n'*}"  # first line only
if [[ "$ERR" != "0" ]]; then
  echo "==> Load errors: $ERR (first few):"
  docker exec "$LOCAL_CONTAINER" sh -c 'grep ERROR /tmp/load.err | head -5' 2>/dev/null
else
  echo "==> Load errors: 0"
fi

# ── Smoke ──
echo "==> Row-count sanity check:"
docker exec "$LOCAL_CONTAINER" \
  mysql -u"$LOCAL_USER" -p"$LOCAL_PASSWORD" -h127.0.0.1 "$LOCAL_DB" \
  -N -e "
    SELECT 'identity_persons', COUNT(*) FROM identity_persons UNION ALL
    SELECT 'api_key_providers', COUNT(*) FROM api_key_providers UNION ALL
    SELECT 'asset_items', COUNT(*) FROM asset_items UNION ALL
    SELECT 'cmdb_servers', COUNT(*) FROM cmdb_servers UNION ALL
    SELECT 'approval_requests', COUNT(*) FROM approval_requests UNION ALL
    SELECT 'platform_configs (preserved)', COUNT(*) FROM platform_configs
  " 2>/dev/null | awk -F'\t' '{printf "    %-30s %s\n", $1, $2}'

echo "==> Done."