-- Plan 模型重构:patient-级 plan + scenario-级 plan_reasons 子表
-- 对应 db-review-confirm §1:plan 1:N reason
--
-- 旧:followup_plans 含 scenario / inclusion_reason / evidence 字段,
--     partial UNIQUE on (platform_id, tenant_id, patient_id, scenario) WHERE status active/assigned
-- 新:followup_plans 只剩触达决策字段,scenario 维度移到 plan_reasons,
--     partial UNIQUE on (platform_id, tenant_id, patient_id) WHERE status active/assigned

-- 1) FollowupPlan: 移除字段 + 改 UNIQUE
ALTER TABLE "followup_plans"
  DROP COLUMN IF EXISTS "scenario",
  DROP COLUMN IF EXISTS "inclusion_reason",
  DROP COLUMN IF EXISTS "evidence",
  ADD COLUMN IF NOT EXISTS "contact_attempts" INT NOT NULL DEFAULT 0;

-- 删旧 UNIQUE/索引(若存在),换新 UNIQUE 不含 scenario
DROP INDEX IF EXISTS "followup_plans_platform_id_tenant_id_patient_id_scenario_ver_key";
DROP INDEX IF EXISTS "followup_plans_scenario_status_idx";
DROP INDEX IF EXISTS "followup_plans_active_per_patient_scenario_idx";

-- 新 UNIQUE: (platform_id, tenant_id, patient_id, version)
ALTER TABLE "followup_plans"
  ADD CONSTRAINT "followup_plans_platform_id_tenant_id_patient_id_version_key"
  UNIQUE ("platform_id", "tenant_id", "patient_id", "version");

-- 新 partial UNIQUE:patient 级 single-active(不再含 scenario)
CREATE UNIQUE INDEX IF NOT EXISTS "followup_plans_active_per_patient_idx"
  ON "followup_plans" ("platform_id", "tenant_id", "patient_id")
  WHERE "status" IN ('active', 'assigned');

-- 2) PlanReason 子表
CREATE TABLE IF NOT EXISTS "plan_reasons" (
  "id"                UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  "plan_id"           UUID         NOT NULL REFERENCES "followup_plans"("id") ON DELETE CASCADE,
  "scenario"          TEXT         NOT NULL,
  "priority_score"    DOUBLE PRECISION NOT NULL,
  "reason"            TEXT         NOT NULL,
  "evidence_fact_ids" UUID[]       NOT NULL DEFAULT '{}',
  "created_at"        TIMESTAMPTZ(3) NOT NULL DEFAULT now(),
  "updated_at"        TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "plan_reasons_plan_id_scenario_key" UNIQUE ("plan_id", "scenario")
);

CREATE INDEX IF NOT EXISTS "plan_reasons_scenario_idx" ON "plan_reasons" ("scenario");
CREATE INDEX IF NOT EXISTS "plan_reasons_evidence_gin_idx" ON "plan_reasons" USING GIN ("evidence_fact_ids");
