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

# 用法示例：
# SOURCE_DATABASE_URL="postgresql://user:pass@127.0.0.1:5432/old_db" \
# TARGET_DATABASE_URL="postgresql://pmflow:pmflow_pass@127.0.0.1:5435/pmflow" \
# BACKUP_PATH="./backup/pmflow_$(date +%Y%m%d_%H%M%S).sql" \
# bash scripts/migrate_postgres_to_docker.sh

SOURCE_DATABASE_URL="${SOURCE_DATABASE_URL:-}"
TARGET_DATABASE_URL="${TARGET_DATABASE_URL:-postgresql://pmflow:pmflow_pass@127.0.0.1:5435/pmflow}"
BACKUP_PATH="${BACKUP_PATH:-./backup/pmflow_migration.sql}"

if [[ -z "${SOURCE_DATABASE_URL}" ]]; then
  echo "[ERROR] SOURCE_DATABASE_URL 未设置"
  exit 1
fi

mkdir -p "$(dirname "${BACKUP_PATH}")"

echo "[1/7] 导出本地 PostgreSQL -> ${BACKUP_PATH}"
pg_dump --no-owner --no-privileges "${SOURCE_DATABASE_URL}" > "${BACKUP_PATH}"

echo "[2/7] 等待 Docker PostgreSQL 可用"
until pg_isready -d "${TARGET_DATABASE_URL}" >/dev/null 2>&1; do
  sleep 1
done

echo "[3/7] 在 Docker PostgreSQL 启用 pgvector"
psql "${TARGET_DATABASE_URL}" -c "CREATE EXTENSION IF NOT EXISTS vector;"

echo "[4/7] 导入备份到 Docker PostgreSQL"
psql "${TARGET_DATABASE_URL}" < "${BACKUP_PATH}"

echo "[5/7] 统计关键表行数（源库）"
SOURCE_COUNTS="$(psql "${SOURCE_DATABASE_URL}" -At -F',' -c \
  "SELECT 'projects',count(*) FROM projects
    UNION ALL SELECT 'tasks',count(*) FROM tasks
    UNION ALL SELECT 'signals',count(*) FROM signals
    UNION ALL SELECT 'users',count(*) FROM users
    UNION ALL SELECT 'qa_records',count(*) FROM qa_records;")"

echo "[6/7] 统计关键表行数（目标库）"
TARGET_COUNTS="$(psql "${TARGET_DATABASE_URL}" -At -F',' -c \
  "SELECT 'projects',count(*) FROM projects
    UNION ALL SELECT 'tasks',count(*) FROM tasks
    UNION ALL SELECT 'signals',count(*) FROM signals
    UNION ALL SELECT 'users',count(*) FROM users
    UNION ALL SELECT 'qa_records',count(*) FROM qa_records;")"

echo "[7/7] 行数对比"
echo "---- source ----"
echo "${SOURCE_COUNTS}"
echo "---- target ----"
echo "${TARGET_COUNTS}"
echo "[DONE] 迁移完成。请将 backend DATABASE_URL 切到 Docker PostgreSQL。"