-- CreateSchema
CREATE SCHEMA IF NOT EXISTS "platform_ai_usage";

-- CreateEnum
CREATE TYPE "platform_ai_usage"."AiUsageTool" AS ENUM ('claude-code', 'codex-cli');

-- CreateEnum
CREATE TYPE "platform_ai_usage"."AiUsageOsPlatform" AS ENUM ('LINUX', 'DARWIN', 'WINDOWS');

-- CreateEnum
CREATE TYPE "platform_ai_usage"."AiUsageDlqReason" AS ENUM ('BAD_FORMAT', 'TS_OUT_OF_WINDOW', 'BLOCKED_DEVICE', 'OVER_LIMIT');

-- CreateTable
CREATE TABLE "platform_ai_usage"."ai_usage_tokens" (
    "id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "name" VARCHAR(64) NOT NULL,
    "prefix" VARCHAR(16) NOT NULL,
    "token_hash" TEXT NOT NULL,
    "last_used_at" TIMESTAMPTZ(3),
    "last_used_ip" VARCHAR(64),
    "revoked_at" TIMESTAMPTZ(3),
    "revoked_by_id" UUID,
    "organization_id" UUID NOT NULL,
    "created_by_id" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "platform_ai_usage"."ai_usage_devices" (
    "id" UUID NOT NULL,
    "device_id" VARCHAR(64) NOT NULL,
    "user_id" UUID NOT NULL,
    "hostname" VARCHAR(255) NOT NULL,
    "os_user" VARCHAR(64),
    "os_platform" "platform_ai_usage"."AiUsageOsPlatform" NOT NULL,
    "agent_version" VARCHAR(32),
    "first_seen_at" TIMESTAMPTZ(3) NOT NULL,
    "first_seen_ip" VARCHAR(64),
    "last_seen_at" TIMESTAMPTZ(3) NOT NULL,
    "blocked_at" TIMESTAMPTZ(3),
    "blocked_by_id" UUID,
    "blocked_reason" VARCHAR(500),
    "organization_id" UUID NOT NULL,
    "created_by_id" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "platform_ai_usage"."ai_usage_events" (
    "id" UUID NOT NULL,
    "raw_message_id" VARCHAR(128) NOT NULL,
    "device_id" UUID NOT NULL,
    "user_id" UUID NOT NULL,
    "tool" "platform_ai_usage"."AiUsageTool" NOT NULL,
    "session_id" VARCHAR(128) NOT NULL,
    "project_path" TEXT NOT NULL,
    "project_basename" VARCHAR(255) NOT NULL,
    "model" VARCHAR(128) NOT NULL,
    "ts" TIMESTAMPTZ(3) NOT NULL,
    "received_at" TIMESTAMPTZ(3) NOT NULL,
    "input_tokens" INTEGER NOT NULL DEFAULT 0,
    "output_tokens" INTEGER NOT NULL DEFAULT 0,
    "cache_creation_tokens" INTEGER NOT NULL DEFAULT 0,
    "cache_read_tokens" INTEGER NOT NULL DEFAULT 0,
    -- total_tokens 是 GENERATED 列，由 4 个 token 字段自动求和；Prisma schema 中声明为普通 Int 仅为读取方便
    "total_tokens" INTEGER NOT NULL GENERATED ALWAYS AS ("input_tokens" + "output_tokens" + "cache_creation_tokens" + "cache_read_tokens") STORED,
    "estimated_cost_usd" DECIMAL(12,6) NOT NULL,
    -- 富 metadata（v1.1）：全部可空，向后兼容老 agent；隐私铁律仅枚举/计数/路径 basename/公开版本号
    "git_branch" VARCHAR(255),
    "agent_version_event" VARCHAR(32),
    "worktree_label" VARCHAR(64),
    "cwd_basename" VARCHAR(255),
    "turn_index" INTEGER,
    "tool_use_count" INTEGER,
    "tool_names" JSONB,
    "stop_reason" VARCHAR(32),
    "service_tier" VARCHAR(32),
    "organization_id" UUID NOT NULL,
    "created_by_id" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "platform_ai_usage"."ai_usage_event_dlq" (
    "id" UUID NOT NULL,
    "device_id" UUID,
    "reason" "platform_ai_usage"."AiUsageDlqReason" NOT NULL,
    "raw_payload" JSONB NOT NULL,
    "organization_id" UUID NOT NULL,
    "created_by_id" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "platform_ai_usage"."ai_usage_daily_rollups" (
    "id" UUID NOT NULL,
    "date" DATE NOT NULL,
    "user_id" UUID NOT NULL,
    "project_basename" VARCHAR(255) NOT NULL,
    "tool" "platform_ai_usage"."AiUsageTool" NOT NULL,
    "model" VARCHAR(128) NOT NULL,
    "total_tokens" BIGINT NOT NULL,
    "input_tokens" BIGINT NOT NULL,
    "output_tokens" BIGINT NOT NULL,
    "cache_creation_tokens" BIGINT NOT NULL,
    "cache_read_tokens" BIGINT NOT NULL,
    "total_cost_usd" DECIMAL(14,6) NOT NULL,
    "event_count" INTEGER NOT NULL,
    "organization_id" UUID NOT NULL,
    "created_by_id" UUID NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

-- CreateIndex
CREATE UNIQUE INDEX "ai_usage_tokens_prefix_key" ON "platform_ai_usage"."ai_usage_tokens"("prefix");

-- CreateIndex
CREATE INDEX "ai_usage_tokens_user_id_revoked_at_idx" ON "platform_ai_usage"."ai_usage_tokens"("user_id", "revoked_at");

-- CreateIndex
CREATE UNIQUE INDEX "ai_usage_devices_device_id_key" ON "platform_ai_usage"."ai_usage_devices"("device_id");

-- CreateIndex
CREATE INDEX "ai_usage_devices_user_id_last_seen_at_idx" ON "platform_ai_usage"."ai_usage_devices"("user_id", "last_seen_at" DESC);

-- CreateIndex
CREATE INDEX "ai_usage_devices_hostname_os_user_idx" ON "platform_ai_usage"."ai_usage_devices"("hostname", "os_user");

-- CreateIndex
CREATE UNIQUE INDEX "ai_usage_events_raw_message_id_key" ON "platform_ai_usage"."ai_usage_events"("raw_message_id");

-- CreateIndex
CREATE INDEX "ai_usage_events_user_id_ts_idx" ON "platform_ai_usage"."ai_usage_events"("user_id", "ts" DESC);

-- CreateIndex
CREATE INDEX "ai_usage_events_organization_id_ts_idx" ON "platform_ai_usage"."ai_usage_events"("organization_id", "ts" DESC);

-- CreateIndex
CREATE INDEX "ai_usage_events_project_basename_ts_idx" ON "platform_ai_usage"."ai_usage_events"("project_basename", "ts" DESC);

-- CreateIndex
CREATE INDEX "ai_usage_events_tool_model_ts_idx" ON "platform_ai_usage"."ai_usage_events"("tool", "model", "ts" DESC);

-- CreateIndex
CREATE INDEX "ai_usage_events_device_id_ts_idx" ON "platform_ai_usage"."ai_usage_events"("device_id", "ts" DESC);

-- CreateIndex（v1.1 富 metadata 索引）
CREATE INDEX "ai_usage_events_session_id_ts_idx" ON "platform_ai_usage"."ai_usage_events"("session_id", "ts");
CREATE INDEX "ai_usage_events_git_branch_ts_idx" ON "platform_ai_usage"."ai_usage_events"("git_branch", "ts" DESC);
CREATE INDEX "ai_usage_events_org_service_tier_ts_idx" ON "platform_ai_usage"."ai_usage_events"("organization_id", "service_tier", "ts" DESC);

-- CreateIndex
CREATE INDEX "ai_usage_event_dlq_reason_created_at_idx" ON "platform_ai_usage"."ai_usage_event_dlq"("reason", "created_at" DESC);
CREATE INDEX "ai_usage_event_dlq_org_created_at_idx" ON "platform_ai_usage"."ai_usage_event_dlq"("organization_id", "created_at" DESC);
CREATE INDEX "ai_usage_tokens_org_idx" ON "platform_ai_usage"."ai_usage_tokens"("organization_id");
CREATE INDEX "ai_usage_devices_org_idx" ON "platform_ai_usage"."ai_usage_devices"("organization_id");
CREATE INDEX "ai_usage_daily_rollups_org_idx" ON "platform_ai_usage"."ai_usage_daily_rollups"("organization_id");

-- CreateIndex
CREATE INDEX "ai_usage_daily_rollups_date_project_basename_idx" ON "platform_ai_usage"."ai_usage_daily_rollups"("date", "project_basename");

-- CreateIndex
CREATE UNIQUE INDEX "ai_usage_daily_rollups_date_user_id_project_basename_tool_m_key" ON "platform_ai_usage"."ai_usage_daily_rollups"("date", "user_id", "project_basename", "tool", "model");

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_tokens" ADD CONSTRAINT "ai_usage_tokens_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_tokens" ADD CONSTRAINT "ai_usage_tokens_revoked_by_id_fkey" FOREIGN KEY ("revoked_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_tokens" ADD CONSTRAINT "ai_usage_tokens_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "corp_hr"."organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_devices" ADD CONSTRAINT "ai_usage_devices_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_devices" ADD CONSTRAINT "ai_usage_devices_blocked_by_id_fkey" FOREIGN KEY ("blocked_by_id") REFERENCES "platform_iam"."users"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_devices" ADD CONSTRAINT "ai_usage_devices_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "corp_hr"."organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_events" ADD CONSTRAINT "ai_usage_events_device_id_fkey" FOREIGN KEY ("device_id") REFERENCES "platform_ai_usage"."ai_usage_devices"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_events" ADD CONSTRAINT "ai_usage_events_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_events" ADD CONSTRAINT "ai_usage_events_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "corp_hr"."organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_daily_rollups" ADD CONSTRAINT "ai_usage_daily_rollups_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "platform_iam"."users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "platform_ai_usage"."ai_usage_daily_rollups" ADD CONSTRAINT "ai_usage_daily_rollups_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "corp_hr"."organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
