-- 大重命名:platform → host(表 + 列)+ Patient 重构 + PatientProfile 副表 + fact_type 清理

-- 1) 重命名 platforms 表
ALTER TABLE "platforms" RENAME TO "hosts";

-- 2) 重命名 platform_id 列 → host_id(子表 12 个)
ALTER TABLE "patients"               RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "patient_transactions"   RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "patient_facts"          RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "personas"               RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "persona_recompute_logs" RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "followup_plans"         RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "plan_scripts"           RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "plan_summaries"         RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "plan_executions"        RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "plan_generation_logs"   RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "agent_invocations"      RENAME COLUMN "platform_id" TO "host_id";
ALTER TABLE "sync_logs"              RENAME COLUMN "platform_id" TO "host_id";

-- 3) Patient 重构:删 status/合规/标签/merge 字段,加 active
ALTER TABLE "patients"
  DROP COLUMN IF EXISTS "status",
  DROP COLUMN IF EXISTS "merged_into_id",
  DROP COLUMN IF EXISTS "do_not_contact",
  DROP COLUMN IF EXISTS "do_not_contact_reason",
  DROP COLUMN IF EXISTS "do_not_contact_at",
  DROP COLUMN IF EXISTS "deceased",
  DROP COLUMN IF EXISTS "deceased_at",
  DROP COLUMN IF EXISTS "tags",
  DROP COLUMN IF EXISTS "primary_contact_type",
  ADD COLUMN IF NOT EXISTS "active" BOOLEAN NOT NULL DEFAULT TRUE;

-- 旧召回池索引含已删字段,重建
DROP INDEX IF EXISTS "patients_platform_id_tenant_id_status_do_not_contact_deceas_idx";
DROP INDEX IF EXISTS "patients_host_id_tenant_id_status_do_not_contact_deceased_idx";
CREATE INDEX IF NOT EXISTS "patients_host_id_tenant_id_active_idx"
  ON "patients" ("host_id", "tenant_id", "active");

-- 4) PatientProfile 副表(1:1)
CREATE TABLE IF NOT EXISTS "patient_profiles" (
  "patient_id"           UUID PRIMARY KEY REFERENCES "patients"("id") ON DELETE CASCADE,
  "do_not_contact"       BOOLEAN NOT NULL DEFAULT FALSE,
  "do_not_contact_reason" TEXT,
  "do_not_contact_at"    TIMESTAMPTZ(3),
  "deceased"             BOOLEAN NOT NULL DEFAULT FALSE,
  "deceased_at"          TIMESTAMPTZ(3),
  "tags"                 TEXT[] NOT NULL DEFAULT '{}',
  "notes"                TEXT,
  "primary_contact_type" TEXT,
  "created_at"           TIMESTAMPTZ(3) NOT NULL DEFAULT now(),
  "updated_at"           TIMESTAMPTZ(3) NOT NULL
);
CREATE INDEX IF NOT EXISTS "patient_profiles_do_not_contact_idx" ON "patient_profiles" ("do_not_contact");
CREATE INDEX IF NOT EXISTS "patient_profiles_deceased_idx"       ON "patient_profiles" ("deceased");

-- 5) fact_type 清理:删 6 个衍生类 + 重命名 charge_order_record → order_record + appointment_plan → appointment_record
-- 由于 dev mock 数据,直接删 fact 行而非 transform
DELETE FROM "patient_facts"
  WHERE "type" IN ('diagnosis_record', 'treatment_record', 'treatment_plan',
                   'review_plan', 'payment_plan');

UPDATE "patient_facts" SET "type" = 'order_record'       WHERE "type" = 'charge_order_record';
UPDATE "patient_facts" SET "type" = 'appointment_record' WHERE "type" = 'appointment_plan';

-- 6) action / subjectType 重命名
UPDATE "patient_transactions" SET "action"       = 'order_submitted' WHERE "action"       = 'charge_order_submitted';
UPDATE "patient_transactions" SET "subject_type" = 'order'           WHERE "subject_type" = 'charge_order';
