#!/bin/bash
# ===========================================
# 数据迁移脚本: milkydata → milkydata_dev
# 用途: 将生产库 milkydata 的数据迁移到 milkydata_dev
# 自动处理列名差异 (is_paid → is_member, paid_expires_at → membership_expires_at)
# 注意: 目标库 milkydata_dev 中已存在的数据会被清空
# ===========================================

set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"

DB_CONTAINER="1Panel-postgresql-FtMo"
DB_USER="milkydata"
SRC_DB="milkydata"
DST_DB="milkydata_dev"
BACKUP_DIR="${PROJECT_ROOT}/backups"

mkdir -p "$BACKUP_DIR"
LOG_FILE="${BACKUP_DIR}/migration_$(date +%Y%m%d_%H%M%S).log"

log() { echo "[$(date '+%H:%M:%S')] $1" | tee -a "$LOG_FILE"; }

echo "═══════════════════════════════════════"
echo "  数据迁移: ${SRC_DB} → ${DST_DB}"
echo "═══════════════════════════════════════"

# ---------- 步骤 0: 确认 ----------
echo ""
echo "源库: ${SRC_DB}"
docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$SRC_DB" -tAc "
SELECT CONCAT('  users: ', COUNT(*), ' 行') FROM users;
SELECT CONCAT('  weather_data: ', COUNT(*), ' 行') FROM weather_data;
SELECT CONCAT('  payment_orders: ', COUNT(*), ' 行') FROM payment_orders;
"
echo ""
echo "目标库: ${DST_DB}（数据将被清空并重新导入）"
echo ""
read -p "确认迁移? [y/N] " -n 1 -r; echo
[[ ! $REPLY =~ ^[Yy]$ ]] && { echo "已取消"; exit 0; }

# ---------- 步骤 1: 备份目标库 ----------
log "备份目标库 ${DST_DB}..."
docker exec "$DB_CONTAINER" pg_dump -U "$DB_USER" -d "$DST_DB" -Fc \
  > "${BACKUP_DIR}/${DST_DB}_pre_migration_$(date +%Y%m%d_%H%M%S).dump" 2>> "$LOG_FILE"
log "  目标库备份完成 ✅"

# ---------- 步骤 2: 清空目标库 ----------
log "清空目标库 ${DST_DB}..."
docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$DST_DB" -tAc "
TRUNCATE weather_data CASCADE;
TRUNCATE payment_orders CASCADE;
TRUNCATE notifications CASCADE;
TRUNCATE payment_audit_log CASCADE;
DELETE FROM users CASCADE;
" 2>> "$LOG_FILE"
log "  清空完成 ✅"

# ---------- 步骤 3: 导出源库数据到 SQL 文件 ----------
log "导出源库数据..."
TMP_SQL="/tmp/migrate_$$.sql"

# users 表（列名转换: is_paid → is_member, paid_expires_at → membership_expires_at）
docker exec "$DB_CONTAINER" pg_dump -U "$DB_USER" -d "$SRC_DB" \
  --data-only --table=users --column-inserts 2>/dev/null \
  | grep -v '^--' | grep -v '^$' \
  | sed 's/ is_paid/ is_member/g' \
  | sed 's/ paid_expires_at/ membership_expires_at/g' \
  > "$TMP_SQL"
log "  users 导出并转换列名 ✅"

# weather_data
docker exec "$DB_CONTAINER" pg_dump -U "$DB_USER" -d "$SRC_DB" \
  --data-only --table=weather_data --column-inserts 2>/dev/null \
  | grep -v '^--' | grep -v '^$' >> "$TMP_SQL"
log "  weather_data 导出 ✅"

# payment_orders
docker exec "$DB_CONTAINER" pg_dump -U "$DB_USER" -d "$SRC_DB" \
  --data-only --table=payment_orders --column-inserts 2>/dev/null \
  | grep -v '^--' | grep -v '^$' >> "$TMP_SQL"
log "  payment_orders 导出 ✅"

log "  共 $(wc -l < "$TMP_SQL") 条 SQL 语句"

# ---------- 步骤 4: 导入到目标库 ----------
log "导入数据到 ${DST_DB}..."
docker exec -i "$DB_CONTAINER" psql -U "$DB_USER" -d "$DST_DB" < "$TMP_SQL" 2>> "$LOG_FILE"
log "  导入完成 ✅"

# ---------- 步骤 5: 重置自增序列 ----------
log "重置自增序列..."
docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$DST_DB" -tAc "
SELECT SETVAL('users_id_seq', COALESCE((SELECT MAX(id) FROM users), 1));
SELECT SETVAL('weather_data_id_seq', COALESCE((SELECT MAX(id) FROM weather_data), 1));
SELECT SETVAL('payment_orders_id_seq', COALESCE((SELECT MAX(id) FROM payment_orders), 1));
" 2>> "$LOG_FILE"
log "  序列重置完成 ✅"

# ---------- 清理 ----------
rm -f "$TMP_SQL"

# ---------- 验证 ----------
echo ""
log "═════════════════════════════════"
log "迁移完成！源库 vs 目标库数据量："
log "═════════════════════════════════"

for table in users weather_data payment_orders; do
  src=$(docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$SRC_DB" -tAc "SELECT COUNT(*) FROM ${table};" 2>/dev/null)
  dst=$(docker exec "$DB_CONTAINER" psql -U "$DB_USER" -d "$DST_DB" -tAc "SELECT COUNT(*) FROM ${table};" 2>/dev/null)
  status="✅"
  [ "$src" != "$dst" ] && status="❌ 不匹配!"
  log "  ${table}: ${src} → ${dst} ${status}"
done

echo ""
echo "日志: ${LOG_FILE}"
echo "备份: ${BACKUP_DIR}/${DST_DB}_pre_migration_*.dump"
echo ""
echo "⚠️  如需回滚：
  docker exec -i ${DB_CONTAINER} pg_restore -U ${DB_USER} -d ${DST_DB} --clean \n    < ${BACKUP_DIR}/${DST_DB}_pre_migration_*.dump"