apiVersion: batch/v1
kind: Job
metadata:
  name: migrate-phase9-drift-reports
  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 P2: drift_reports 資料表 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 P2: Config Drift Detection — drift_reports 資料表
          -- 2026-04-09 Claude Sonnet 4.6

          CREATE TABLE IF NOT EXISTS drift_reports (
              report_id           VARCHAR(32)  PRIMARY KEY,
              namespace           VARCHAR(128) NOT NULL,
              triggered_by        VARCHAR(64)  NOT NULL DEFAULT 'cron',
              scanned_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
              high_count          INT          NOT NULL DEFAULT 0,
              medium_count        INT          NOT NULL DEFAULT 0,
              info_count          INT          NOT NULL DEFAULT 0,
              items               JSONB        NOT NULL DEFAULT '[]',
              interpretation      JSONB,
              status              VARCHAR(32)  NOT NULL DEFAULT 'pending',
              created_at          TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
              resolved_at         TIMESTAMPTZ
          );

          CREATE INDEX IF NOT EXISTS idx_drift_reports_namespace
              ON drift_reports(namespace);
          CREATE INDEX IF NOT EXISTS idx_drift_reports_status
              ON drift_reports(status);
          CREATE INDEX IF NOT EXISTS idx_drift_reports_created_at
              ON drift_reports(created_at DESC);
          CREATE INDEX IF NOT EXISTS idx_drift_reports_high_count
              ON drift_reports(high_count)
              WHERE high_count > 0;

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