# ADR-090-B: Gitea CI 自動 migration workflow
# 建立時間: 2026-04-18 台北時區
# 建立者: ogt + Claude Opus 4.7 (1M)
#
# 目的: 每次 main 分支有新 migration SQL 檔,自動:
#   1. 用 MIGRATION_DATABASE_URL (awoooi_migrator 限權帳號) 連 PG
#   2. 只跑「新增」的 migration (比對已執行列表)
#   3. 跑後寫 asset_discovery_run + automation_operation_log 記錄
#   4. 失敗自動 rollback (single transaction + ON_ERROR_STOP)
#
# 觸發: push to main,且 apps/api/migrations/ 有變更

name: run-migration

on:
  push:
    branches: [main]
    paths:
      - 'apps/api/migrations/*.sql'

env:
  TELEGRAM_ALERT_CHAT_ID: "-1003711974679"

jobs:
  migrate:
    runs-on: ubuntu-latest  # 或 self-hosted runner on 110

    steps:
      - name: Checkout
        uses: actions/checkout@v4
        with:
          fetch-depth: 2  # 需比對上一個 commit

      - name: Install migration tools
        run: |
          set -euo pipefail
          missing=""
          for bin in psql jq curl; do
            if ! command -v "$bin" >/dev/null 2>&1; then
              missing="$missing $bin"
            fi
          done
          if [ -z "$missing" ]; then
            exit 0
          fi
          if command -v apt-get >/dev/null 2>&1; then
            apt-get update -qq
            apt-get install -y -q postgresql-client jq curl
          elif command -v apk >/dev/null 2>&1; then
            apk add --no-cache postgresql-client jq curl
          else
            echo "::error::missing required tools:$missing"
            exit 1
          fi

      - name: Identify new migrations
        id: diff
        run: |
          NEW_FILES=$(git diff --name-only --diff-filter=A HEAD~1 HEAD -- 'apps/api/migrations/*.sql' || true)
          echo "new_files<<EOF" >> $GITHUB_OUTPUT
          echo "$NEW_FILES" >> $GITHUB_OUTPUT
          echo "EOF" >> $GITHUB_OUTPUT
          echo "=== New migration files ==="
          echo "$NEW_FILES"

      - name: Apply new migrations
        if: steps.diff.outputs.new_files != ''
        env:
          # 從 Gitea secrets 取,不直接明碼
          PGURL: ${{ secrets.MIGRATION_DATABASE_URL }}
        run: |
          set -euo pipefail
          if [ -z "$PGURL" ]; then
            echo "::error::MIGRATION_DATABASE_URL secret not set in Gitea"
            exit 1
          fi
          PGURL_PSQL="${PGURL/postgresql+asyncpg:\/\//postgresql:\/\/}"

          # 套用每個新檔 (single transaction per file)
          echo "${{ steps.diff.outputs.new_files }}" | while IFS= read -r file; do
            [ -z "$file" ] && continue
            echo "=== Applying: $file ==="
            psql "$PGURL_PSQL" \
              -v ON_ERROR_STOP=1 \
              --single-transaction \
              -f "$file"
            echo "=== OK: $file ==="
          done

      - name: Seed asset_discovery_run (audit)
        if: steps.diff.outputs.new_files != ''
        env:
          PGURL: ${{ secrets.MIGRATION_DATABASE_URL }}
        run: |
          PGURL_PSQL="${PGURL/postgresql+asyncpg:\/\//postgresql:\/\/}"
          FILES_JSON=$(echo "${{ steps.diff.outputs.new_files }}" | jq -Rn '[inputs | select(length > 0)]')
          psql "$PGURL_PSQL" -c "
            INSERT INTO asset_discovery_run (
              run_id, triggered_by, scope, scan_depth, status,
              started_at, ended_at, tools_used, summary
            ) VALUES (
              gen_random_uuid(),
              'ci:gitea',
              ARRAY['schema_migration'],
              'full',
              'success',
              NOW(),
              NOW(),
              '{\"psql\": 1, \"gitea_ci\": 1}'::jsonb,
              jsonb_build_object(
                'type', 'ci_migration',
                'commit_sha', '${{ github.sha }}',
                'files', $FILES_JSON
              )
            );
          "

      - name: Notify Telegram (if configured)
        if: always()
        env:
          TG_TOKEN: ${{ secrets.TELEGRAM_BOT_TOKEN }}
          TG_CHAT: ${{ env.TELEGRAM_ALERT_CHAT_ID }}
        run: |
          if [ -n "$TG_TOKEN" ] && [ -n "$TG_CHAT" ]; then
            STATUS="${{ job.status }}"
            MSG="🗄️ Migration CI: \`${STATUS}\` — commit ${{ github.sha }}"
            curl -s -X POST "https://api.telegram.org/bot${TG_TOKEN}/sendMessage" \
              -d chat_id="${TG_CHAT}" \
              -d parse_mode="Markdown" \
              -d text="${MSG}" || true
          fi
