-- 1. PerformanceResult: 增加 proposed_grade_code 和 proposed_grade_name
ALTER TABLE "platform_performance"."performance_result"
  ADD COLUMN "proposed_grade_code" TEXT,
  ADD COLUMN "proposed_grade_name" TEXT;

-- 2. 初始化：现有结果的 proposed = 当前 grade（校准前后一致）
UPDATE "platform_performance"."performance_result"
  SET "proposed_grade_code" = "grade_code",
      "proposed_grade_name" = "grade_name";

-- 3. 设为 NOT NULL
ALTER TABLE "platform_performance"."performance_result"
  ALTER COLUMN "proposed_grade_code" SET NOT NULL,
  ALTER COLUMN "proposed_grade_name" SET NOT NULL;

-- 4. GradeAdjustmentLog: 增加 result_id 列
ALTER TABLE "platform_performance"."grade_adjustment_log"
  ADD COLUMN "result_id" UUID;

-- 5. 回填 result_id（通过 assessment -> assignment -> cycle+employee -> result 关联）
UPDATE "platform_performance"."grade_adjustment_log" gal
  SET "result_id" = pr.id
  FROM "platform_performance"."kpi_assessment" ka
  JOIN "platform_performance"."kpi_assignment" kai ON ka."assignment_id" = kai.id
  JOIN "platform_performance"."performance_result" pr ON pr."cycle_id" = kai."cycle_id" AND pr."employee_id" = kai."employee_id"
  WHERE gal."assessment_id" = ka.id;

-- 6. 删除无法关联的孤儿日志
DELETE FROM "platform_performance"."grade_adjustment_log" WHERE "result_id" IS NULL;

-- 7. 设为 NOT NULL + FK
ALTER TABLE "platform_performance"."grade_adjustment_log"
  ALTER COLUMN "result_id" SET NOT NULL,
  ADD CONSTRAINT "grade_adjustment_log_result_id_fkey"
    FOREIGN KEY ("result_id") REFERENCES "platform_performance"."performance_result"(id) ON DELETE CASCADE;

-- 8. 删除旧 assessment_id 列和 FK
ALTER TABLE "platform_performance"."grade_adjustment_log"
  DROP CONSTRAINT IF EXISTS "grade_adjustment_log_assessment_id_fkey",
  DROP COLUMN "assessment_id";

-- 9. 创建新索引
CREATE INDEX "grade_adjustment_log_result_id_idx" ON "platform_performance"."grade_adjustment_log"("result_id");

-- 10. KpiAssessment: 删除等级相关字段
ALTER TABLE "platform_performance"."kpi_assessment"
  DROP COLUMN IF EXISTS "proposed_grade_code",
  DROP COLUMN IF EXISTS "final_grade_code",
  DROP COLUMN IF EXISTS "grade_adjusted_by",
  DROP COLUMN IF EXISTS "grade_adjusted_at",
  DROP COLUMN IF EXISTS "grade_adjust_reason",
  DROP COLUMN IF EXISTS "confirmed_at";
