apiVersion: batch/v1
kind: Job
metadata:
  name: migrate-phase8-symptoms-hash
  namespace: awoooi-prod
  labels:
    app: awoooi-migration
    phase: phase25
spec:
  ttlSecondsAfterFinished: 300
  backoffLimit: 1
  template:
    spec:
      restartPolicy: Never
      containers:
      - name: migrate
        image: postgres:15-alpine
        command:
        - /bin/sh
        - -c
        - |
          echo "=========================================="
          echo "Phase 25 P1: knowledge_entries symptoms_hash 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'
          -- Phase 25 P1: Anti-Pattern symptoms_hash 欄位
          -- 2026-04-09 Claude Sonnet 4.6

          ALTER TABLE knowledge_entries
              ADD COLUMN IF NOT EXISTS symptoms_hash VARCHAR(16);

          CREATE INDEX IF NOT EXISTS idx_knowledge_anti_pattern_hash
              ON knowledge_entries (entry_type, symptoms_hash, created_at)
              WHERE entry_type = 'anti_pattern' AND symptoms_hash IS NOT NULL;

          -- 自動處理 CHECK constraint（若有）
          DO $$
          DECLARE
              v_conname text;
          BEGIN
              SELECT conname INTO v_conname
              FROM pg_constraint
              WHERE conrelid = 'knowledge_entries'::regclass AND contype = 'c' AND conname LIKE '%status%';

              IF v_conname IS NOT NULL THEN
                  EXECUTE format('ALTER TABLE knowledge_entries DROP CONSTRAINT %I', v_conname);
                  ALTER TABLE knowledge_entries ADD CONSTRAINT knowledge_entries_status_check
                      CHECK (status IN ('draft', 'review', 'approved', 'archived', 'published'));
                  RAISE NOTICE 'Updated status CHECK constraint: % → added published', v_conname;
              ELSE
                  RAISE NOTICE 'No status CHECK constraint found, skipping';
              END IF;
          END $$;

          -- 驗證
          SELECT column_name, data_type
          FROM information_schema.columns
          WHERE table_name = 'knowledge_entries'
          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"
