-- CreateTable
CREATE TABLE "platforms" (
    "id" UUID NOT NULL,
    "name" TEXT NOT NULL,
    "app_id" TEXT NOT NULL,
    "app_secret_hash" TEXT NOT NULL,
    "pull_config" JSONB,
    "push_secret_hashes" TEXT[],
    "field_mapping" JSONB NOT NULL DEFAULT '{}',
    "action_urls" JSONB NOT NULL DEFAULT '{}',
    "active" BOOLEAN NOT NULL DEFAULT true,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

    CONSTRAINT "platforms_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "patients" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "external_id" TEXT NOT NULL,
    "name" TEXT,
    "phone" TEXT,
    "gender" TEXT,
    "birth_date" DATE,
    "preferences" JSONB,
    "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),
    "status" TEXT NOT NULL DEFAULT 'active',
    "merged_into_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

    CONSTRAINT "patients_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "patient_transactions" (
    "id" UUID NOT NULL,
    "event_seq" BIGSERIAL NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "patient_id" UUID,
    "source_event_id" TEXT,
    "clinic_id" TEXT NOT NULL,
    "action" TEXT NOT NULL,
    "subject_type" TEXT NOT NULL,
    "subject_id" TEXT NOT NULL,
    "raw_payload" JSONB NOT NULL,
    "payload_hash" TEXT NOT NULL,
    "occurred_at" TIMESTAMPTZ(3) NOT NULL,
    "received_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "sync_log_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "patient_transactions_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "patient_facts" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "patient_id" UUID NOT NULL,
    "subject_id" TEXT NOT NULL,
    "kind" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "status" TEXT NOT NULL,
    "version" INTEGER NOT NULL,
    "clinic_id" TEXT,
    "occurred_at" TIMESTAMPTZ(3),
    "planned_for" TIMESTAMPTZ(3),
    "valid_from" TIMESTAMPTZ(3),
    "valid_until" TIMESTAMPTZ(3),
    "title" TEXT,
    "summary" TEXT,
    "content" JSONB NOT NULL,
    "transaction_ids" UUID[],
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "superseded_at" TIMESTAMPTZ(3),

    CONSTRAINT "patient_facts_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "personas" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "patient_id" UUID NOT NULL,
    "version" INTEGER NOT NULL,
    "triggered_by" TEXT NOT NULL,
    "computed_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "superseded_at" TIMESTAMPTZ(3),
    "event_watermark" BIGINT,

    CONSTRAINT "personas_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "persona_features" (
    "id" UUID NOT NULL,
    "persona_id" UUID NOT NULL,
    "key" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "score" DOUBLE PRECISION,
    "evidence" JSONB NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "persona_features_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "persona_recompute_logs" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "patient_id" UUID NOT NULL,
    "triggered_by" TEXT NOT NULL,
    "event_watermark" BIGINT,
    "status" TEXT NOT NULL,
    "produced_persona_id" UUID,
    "feature_count" INTEGER NOT NULL DEFAULT 0,
    "rule_count" INTEGER NOT NULL DEFAULT 0,
    "agent_count" INTEGER NOT NULL DEFAULT 0,
    "fallback_count" INTEGER NOT NULL DEFAULT 0,
    "error_message" TEXT,
    "started_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "ended_at" TIMESTAMPTZ(3),

    CONSTRAINT "persona_recompute_logs_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "followup_plans" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "patient_id" UUID NOT NULL,
    "persona_id" UUID,
    "target_clinic_id" TEXT,
    "scenario" TEXT NOT NULL,
    "version" INTEGER NOT NULL,
    "priority_score" DOUBLE PRECISION NOT NULL,
    "inclusion_reason" TEXT NOT NULL,
    "recommended_at" TIMESTAMPTZ(3),
    "recommended_role" TEXT,
    "recommended_channel" TEXT,
    "evidence" JSONB NOT NULL,
    "status" TEXT NOT NULL,
    "recycle_at" TIMESTAMPTZ(3),
    "assignee_user_id" TEXT,
    "assigned_at" TIMESTAMPTZ(3),
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,
    "superseded_at" TIMESTAMPTZ(3),

    CONSTRAINT "followup_plans_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "plan_scripts" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "plan_id" UUID NOT NULL,
    "content" TEXT,
    "status" TEXT NOT NULL DEFAULT 'pending',
    "source" TEXT,
    "agent_invocation_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

    CONSTRAINT "plan_scripts_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "plan_summaries" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "plan_id" UUID NOT NULL,
    "type" TEXT NOT NULL,
    "content" TEXT,
    "status" TEXT NOT NULL DEFAULT 'pending',
    "source" TEXT,
    "agent_invocation_id" UUID,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

    CONSTRAINT "plan_summaries_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "plan_executions" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "executor_clinic_id" TEXT NOT NULL,
    "plan_id" UUID NOT NULL,
    "operator_user_id" TEXT NOT NULL,
    "channel" TEXT NOT NULL,
    "outcome" TEXT NOT NULL,
    "notes" TEXT,
    "abandon_reasons" TEXT[],
    "abandon_other" TEXT,
    "scheduled_next_at" TIMESTAMPTZ(3),
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "plan_executions_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "plan_generation_logs" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "patient_id" UUID NOT NULL,
    "triggered_by" TEXT NOT NULL,
    "input_persona_id" UUID,
    "status" TEXT NOT NULL,
    "scenario_run_count" INTEGER NOT NULL DEFAULT 0,
    "plans_created" INTEGER NOT NULL DEFAULT 0,
    "assets_created" INTEGER NOT NULL DEFAULT 0,
    "agent_count" INTEGER NOT NULL DEFAULT 0,
    "fallback_count" INTEGER NOT NULL DEFAULT 0,
    "error_message" TEXT,
    "started_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "ended_at" TIMESTAMPTZ(3),

    CONSTRAINT "plan_generation_logs_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "agent_invocations" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "kind" TEXT NOT NULL,
    "agent_version" TEXT,
    "model_name" TEXT,
    "gateway_provider" TEXT NOT NULL,
    "workflow_id" TEXT,
    "input_snapshot" JSONB NOT NULL,
    "prompt_template" TEXT,
    "output" JSONB,
    "output_text" TEXT,
    "linked_patient_id" UUID,
    "linked_persona_id" UUID,
    "linked_plan_id" UUID,
    "prompt_tokens" INTEGER NOT NULL DEFAULT 0,
    "completion_tokens" INTEGER NOT NULL DEFAULT 0,
    "total_tokens" INTEGER NOT NULL DEFAULT 0,
    "cost_cents" INTEGER NOT NULL DEFAULT 0,
    "status" TEXT NOT NULL,
    "error_message" TEXT,
    "started_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "ended_at" TIMESTAMPTZ(3),
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

    CONSTRAINT "agent_invocations_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "sync_logs" (
    "id" UUID NOT NULL,
    "platform_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "patient_id" UUID,
    "direction" TEXT NOT NULL,
    "resource" TEXT NOT NULL,
    "triggered_by" TEXT,
    "status" TEXT NOT NULL,
    "cursor_before" TEXT,
    "cursor_after" TEXT,
    "fetched" INTEGER NOT NULL DEFAULT 0,
    "transactions_written" INTEGER NOT NULL DEFAULT 0,
    "facts_emitted" INTEGER NOT NULL DEFAULT 0,
    "duplicates" INTEGER NOT NULL DEFAULT 0,
    "failed" INTEGER NOT NULL DEFAULT 0,
    "error_message" TEXT,
    "started_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "ended_at" TIMESTAMPTZ(3),

    CONSTRAINT "sync_logs_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "platforms_name_key" ON "platforms"("name");

-- CreateIndex
CREATE UNIQUE INDEX "platforms_app_id_key" ON "platforms"("app_id");

-- CreateIndex
CREATE INDEX "patients_platform_id_tenant_id_idx" ON "patients"("platform_id", "tenant_id");

-- CreateIndex
CREATE INDEX "patients_phone_idx" ON "patients"("phone");

-- CreateIndex
CREATE INDEX "patients_platform_id_tenant_id_status_do_not_contact_deceas_idx" ON "patients"("platform_id", "tenant_id", "status", "do_not_contact", "deceased");

-- CreateIndex
CREATE UNIQUE INDEX "patients_platform_id_tenant_id_external_id_key" ON "patients"("platform_id", "tenant_id", "external_id");

-- CreateIndex
CREATE INDEX "patient_transactions_platform_id_tenant_id_clinic_id_idx" ON "patient_transactions"("platform_id", "tenant_id", "clinic_id");

-- CreateIndex
CREATE INDEX "patient_transactions_patient_id_occurred_at_idx" ON "patient_transactions"("patient_id", "occurred_at");

-- CreateIndex
CREATE INDEX "patient_transactions_subject_type_subject_id_idx" ON "patient_transactions"("subject_type", "subject_id");

-- CreateIndex
CREATE INDEX "patient_transactions_action_idx" ON "patient_transactions"("action");

-- CreateIndex
CREATE INDEX "patient_transactions_received_at_idx" ON "patient_transactions"("received_at");

-- CreateIndex
CREATE INDEX "patient_transactions_event_seq_idx" ON "patient_transactions"("event_seq");

-- CreateIndex
CREATE INDEX "patient_facts_platform_id_tenant_id_idx" ON "patient_facts"("platform_id", "tenant_id");

-- CreateIndex
CREATE INDEX "patient_facts_patient_id_status_idx" ON "patient_facts"("patient_id", "status");

-- CreateIndex
CREATE INDEX "patient_facts_patient_id_occurred_at_idx" ON "patient_facts"("patient_id", "occurred_at");

-- CreateIndex
CREATE INDEX "patient_facts_patient_id_planned_for_idx" ON "patient_facts"("patient_id", "planned_for");

-- CreateIndex
CREATE INDEX "patient_facts_type_status_valid_until_idx" ON "patient_facts"("type", "status", "valid_until");

-- CreateIndex
CREATE UNIQUE INDEX "patient_facts_platform_id_tenant_id_subject_id_version_key" ON "patient_facts"("platform_id", "tenant_id", "subject_id", "version");

-- CreateIndex
CREATE INDEX "personas_platform_id_tenant_id_idx" ON "personas"("platform_id", "tenant_id");

-- CreateIndex
CREATE INDEX "personas_patient_id_superseded_at_idx" ON "personas"("patient_id", "superseded_at");

-- CreateIndex
CREATE UNIQUE INDEX "personas_patient_id_version_key" ON "personas"("patient_id", "version");

-- CreateIndex
CREATE INDEX "persona_features_key_idx" ON "persona_features"("key");

-- CreateIndex
CREATE UNIQUE INDEX "persona_features_persona_id_key_key" ON "persona_features"("persona_id", "key");

-- CreateIndex
CREATE INDEX "persona_recompute_logs_patient_id_started_at_idx" ON "persona_recompute_logs"("patient_id", "started_at");

-- CreateIndex
CREATE INDEX "persona_recompute_logs_platform_id_tenant_id_idx" ON "persona_recompute_logs"("platform_id", "tenant_id");

-- CreateIndex
CREATE INDEX "persona_recompute_logs_status_idx" ON "persona_recompute_logs"("status");

-- CreateIndex
CREATE INDEX "followup_plans_platform_id_tenant_id_idx" ON "followup_plans"("platform_id", "tenant_id");

-- CreateIndex
CREATE INDEX "followup_plans_platform_id_tenant_id_target_clinic_id_idx" ON "followup_plans"("platform_id", "tenant_id", "target_clinic_id");

-- CreateIndex
CREATE INDEX "followup_plans_scenario_status_idx" ON "followup_plans"("scenario", "status");

-- CreateIndex
CREATE INDEX "followup_plans_assignee_user_id_idx" ON "followup_plans"("assignee_user_id");

-- CreateIndex
CREATE INDEX "followup_plans_status_assignee_user_id_priority_score_idx" ON "followup_plans"("status", "assignee_user_id", "priority_score");

-- CreateIndex
CREATE UNIQUE INDEX "followup_plans_platform_id_tenant_id_patient_id_scenario_ve_key" ON "followup_plans"("platform_id", "tenant_id", "patient_id", "scenario", "version");

-- CreateIndex
CREATE UNIQUE INDEX "plan_scripts_plan_id_key" ON "plan_scripts"("plan_id");

-- CreateIndex
CREATE INDEX "plan_scripts_platform_id_tenant_id_idx" ON "plan_scripts"("platform_id", "tenant_id");

-- CreateIndex
CREATE INDEX "plan_summaries_platform_id_tenant_id_idx" ON "plan_summaries"("platform_id", "tenant_id");

-- CreateIndex
CREATE UNIQUE INDEX "plan_summaries_plan_id_type_key" ON "plan_summaries"("plan_id", "type");

-- CreateIndex
CREATE INDEX "plan_executions_platform_id_tenant_id_executor_clinic_id_idx" ON "plan_executions"("platform_id", "tenant_id", "executor_clinic_id");

-- CreateIndex
CREATE INDEX "plan_executions_plan_id_created_at_idx" ON "plan_executions"("plan_id", "created_at");

-- CreateIndex
CREATE INDEX "plan_executions_operator_user_id_created_at_idx" ON "plan_executions"("operator_user_id", "created_at");

-- CreateIndex
CREATE INDEX "plan_generation_logs_patient_id_started_at_idx" ON "plan_generation_logs"("patient_id", "started_at");

-- CreateIndex
CREATE INDEX "plan_generation_logs_platform_id_tenant_id_idx" ON "plan_generation_logs"("platform_id", "tenant_id");

-- CreateIndex
CREATE INDEX "plan_generation_logs_status_idx" ON "plan_generation_logs"("status");

-- CreateIndex
CREATE INDEX "agent_invocations_platform_id_tenant_id_idx" ON "agent_invocations"("platform_id", "tenant_id");

-- CreateIndex
CREATE INDEX "agent_invocations_kind_idx" ON "agent_invocations"("kind");

-- CreateIndex
CREATE INDEX "agent_invocations_linked_patient_id_idx" ON "agent_invocations"("linked_patient_id");

-- CreateIndex
CREATE INDEX "agent_invocations_linked_persona_id_idx" ON "agent_invocations"("linked_persona_id");

-- CreateIndex
CREATE INDEX "agent_invocations_linked_plan_id_idx" ON "agent_invocations"("linked_plan_id");

-- CreateIndex
CREATE INDEX "agent_invocations_started_at_idx" ON "agent_invocations"("started_at");

-- CreateIndex
CREATE INDEX "sync_logs_platform_id_tenant_id_resource_idx" ON "sync_logs"("platform_id", "tenant_id", "resource");

-- CreateIndex
CREATE INDEX "sync_logs_started_at_idx" ON "sync_logs"("started_at");

-- CreateIndex
CREATE INDEX "sync_logs_status_idx" ON "sync_logs"("status");

-- CreateIndex
CREATE INDEX "sync_logs_patient_id_started_at_idx" ON "sync_logs"("patient_id", "started_at");

-- AddForeignKey
ALTER TABLE "patients" ADD CONSTRAINT "patients_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "patients" ADD CONSTRAINT "patients_merged_into_id_fkey" FOREIGN KEY ("merged_into_id") REFERENCES "patients"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "patient_transactions" ADD CONSTRAINT "patient_transactions_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "patient_transactions" ADD CONSTRAINT "patient_transactions_patient_id_fkey" FOREIGN KEY ("patient_id") REFERENCES "patients"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "patient_transactions" ADD CONSTRAINT "patient_transactions_sync_log_id_fkey" FOREIGN KEY ("sync_log_id") REFERENCES "sync_logs"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "patient_facts" ADD CONSTRAINT "patient_facts_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "patient_facts" ADD CONSTRAINT "patient_facts_patient_id_fkey" FOREIGN KEY ("patient_id") REFERENCES "patients"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "personas" ADD CONSTRAINT "personas_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "personas" ADD CONSTRAINT "personas_patient_id_fkey" FOREIGN KEY ("patient_id") REFERENCES "patients"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "persona_features" ADD CONSTRAINT "persona_features_persona_id_fkey" FOREIGN KEY ("persona_id") REFERENCES "personas"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "persona_recompute_logs" ADD CONSTRAINT "persona_recompute_logs_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "followup_plans" ADD CONSTRAINT "followup_plans_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "followup_plans" ADD CONSTRAINT "followup_plans_patient_id_fkey" FOREIGN KEY ("patient_id") REFERENCES "patients"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "followup_plans" ADD CONSTRAINT "followup_plans_persona_id_fkey" FOREIGN KEY ("persona_id") REFERENCES "personas"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "plan_scripts" ADD CONSTRAINT "plan_scripts_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "plan_scripts" ADD CONSTRAINT "plan_scripts_plan_id_fkey" FOREIGN KEY ("plan_id") REFERENCES "followup_plans"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "plan_summaries" ADD CONSTRAINT "plan_summaries_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "plan_summaries" ADD CONSTRAINT "plan_summaries_plan_id_fkey" FOREIGN KEY ("plan_id") REFERENCES "followup_plans"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "plan_executions" ADD CONSTRAINT "plan_executions_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "plan_executions" ADD CONSTRAINT "plan_executions_plan_id_fkey" FOREIGN KEY ("plan_id") REFERENCES "followup_plans"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "plan_generation_logs" ADD CONSTRAINT "plan_generation_logs_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "agent_invocations" ADD CONSTRAINT "agent_invocations_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "sync_logs" ADD CONSTRAINT "sync_logs_platform_id_fkey" FOREIGN KEY ("platform_id") REFERENCES "platforms"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- ============================================================================
-- 以下 4 项 Prisma schema 表达不了,这里用 raw SQL 补齐(对应 schema.prisma 头部 MIGRATION 注释)
-- ============================================================================

-- 1) patient_transactions 幂等键:partial UNIQUE on source_event_id (WHERE NOT NULL)
CREATE UNIQUE INDEX "patient_transactions_idempotency"
  ON "patient_transactions" ("platform_id", "tenant_id", "source_event_id")
  WHERE "source_event_id" IS NOT NULL;

-- 2) patient_facts 单一活跃版本:partial UNIQUE on subject_id (WHERE status='active')
CREATE UNIQUE INDEX "patient_facts_single_active_version"
  ON "patient_facts" ("platform_id", "tenant_id", "subject_id")
  WHERE "status" = 'active';

-- 3) patient_facts 证据链反查:GIN on transaction_ids 数组
CREATE INDEX "patient_facts_transaction_ids_gin"
  ON "patient_facts" USING GIN ("transaction_ids");

-- 4) followup_plans 单一活跃计划:partial UNIQUE per (patient, scenario)
--    WHERE status IN ('active','assigned') — 同 patient × scenario 只能有一个未结案 plan
CREATE UNIQUE INDEX "followup_plans_one_open_per_scenario"
  ON "followup_plans" ("platform_id", "tenant_id", "patient_id", "scenario")
  WHERE "status" IN ('active', 'assigned');
