apiVersion: batch/v1
kind: Job
metadata:
  name: migrate-sprint5r-telegram-message-id
  namespace: awoooi-prod
  labels:
    app: awoooi-migration
    phase: sprint5r
spec:
  ttlSecondsAfterFinished: 300
  backoffLimit: 1
  template:
    spec:
      restartPolicy: Never
      containers:
      - name: migrate
        image: postgres:15-alpine
        command:
        - /bin/sh
        - -c
        - |
          echo "=========================================="
          echo "Sprint 5R: approval_records Telegram 欄位 Migration"
          echo "=========================================="

          DB_HOST=$(echo $DATABASE_URL | sed 's/.*@\([^:]*\):.*/\1/')
          DB_PORT=$(echo $DATABASE_URL | sed 's/.*:\([0-9]*\)\/.*/\1/')
          DB_NAME=$(echo $DATABASE_URL | sed 's/.*\/\([^?]*\).*/\1/')
          DB_USER=$(echo $DATABASE_URL | sed 's/.*\/\/\([^:]*\):.*/\1/')
          DB_PASS=$(echo $DATABASE_URL | sed 's/.*:\/\/[^:]*:\([^@]*\)@.*/\1/')

          echo "Connecting to: $DB_HOST:$DB_PORT/$DB_NAME"
          export PGPASSWORD="$DB_PASS"

          psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" <<'EOSQL'
          -- Sprint 5R: 批准執行閉環修復 — 新增 Telegram 訊息持久化欄位
          -- 2026-04-09 Claude Sonnet 4.6

          DO $$
          BEGIN
              IF NOT EXISTS (
                  SELECT 1 FROM information_schema.columns
                  WHERE table_name = 'approval_records' AND column_name = 'telegram_message_id'
              ) THEN
                  ALTER TABLE approval_records ADD COLUMN telegram_message_id INTEGER;
                  COMMENT ON COLUMN approval_records.telegram_message_id IS 'Telegram message_id of approval card, used to remove inline keyboard after decision';
                  RAISE NOTICE 'Added: telegram_message_id';
              ELSE
                  RAISE NOTICE 'Exists: telegram_message_id';
              END IF;
          END $$;

          DO $$
          BEGIN
              IF NOT EXISTS (
                  SELECT 1 FROM information_schema.columns
                  WHERE table_name = 'approval_records' AND column_name = 'telegram_chat_id'
              ) THEN
                  ALTER TABLE approval_records ADD COLUMN telegram_chat_id INTEGER;
                  COMMENT ON COLUMN approval_records.telegram_chat_id IS 'Telegram chat_id where approval card was sent';
                  RAISE NOTICE 'Added: telegram_chat_id';
              ELSE
                  RAISE NOTICE 'Exists: telegram_chat_id';
              END IF;
          END $$;

          -- 驗證
          SELECT column_name, data_type
          FROM information_schema.columns
          WHERE table_name = 'approval_records'
          ORDER BY ordinal_position;
          EOSQL

          echo "=========================================="
          echo "Migration completed!"
          echo "=========================================="
        envFrom:
        - secretRef:
            name: awoooi-secrets
        resources:
          requests:
            memory: "64Mi"
            cpu: "100m"
          limits:
            memory: "128Mi"
            cpu: "200m"
