-- UserActivity —— 通用行为埋点表(PV/UV + 漏斗 + 登录审计,见 schema.prisma 头注)
CREATE TABLE "user_activities" (
    "id" UUID NOT NULL,
    "host_id" UUID NOT NULL,
    "tenant_id" TEXT NOT NULL,
    "surface" TEXT NOT NULL,
    "action" TEXT NOT NULL,
    "user_id" TEXT NOT NULL,
    "role" TEXT NOT NULL,
    "org_scope" TEXT[],
    "host_origin" TEXT,
    "session_id" TEXT,
    "payload" JSONB,
    "occurred_at" TIMESTAMPTZ(3),
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- PV/UV 主查询:按 host+tenant 圈定后按天扫
CREATE INDEX "user_activities_host_id_tenant_id_created_at_idx"
    ON "user_activities"("host_id", "tenant_id", "created_at");
-- 漏斗:按 (surface, action) 聚合不回表
CREATE INDEX "user_activities_host_id_tenant_id_surface_action_created_at_idx"
    ON "user_activities"("host_id", "tenant_id", "surface", "action", "created_at");
-- 单人行为回放 / 登录审计按人查
CREATE INDEX "user_activities_user_id_created_at_idx"
    ON "user_activities"("user_id", "created_at");

ALTER TABLE "user_activities" ADD CONSTRAINT "user_activities_host_id_fkey"
    FOREIGN KEY ("host_id") REFERENCES "hosts"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
