-- ============================================================
-- FFAI Agent Phase 2 综合迁移
-- 合并 8 个独立迁移（PR #408 累计）为单一迁移，满足 CI per-PR 单迁移规则。
-- 包含：5 新表（projects/personas/memories/crons/mcp_servers）
--      + 1 enum (MemoryScope)
--      + 1 字段 (agent_sessions.parentSessionId)
--      + 4 FK 关系
-- ============================================================

-- CreateEnum
CREATE TYPE "platform_ai"."MemoryScope" AS ENUM ('GLOBAL', 'PROJECT', 'PERSONA');

-- CreateTable: agent_projects (ChatGPT-style 用户自创项目工作区)
CREATE TABLE "platform_ai"."agent_projects" (
    "id" UUID NOT NULL,
    "organizationId" UUID NOT NULL,
    "createdById" UUID NOT NULL,
    "name" VARCHAR(120) NOT NULL,
    "icon" VARCHAR(60),
    "color" VARCHAR(20),
    "instructions" TEXT,
    "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMPTZ(3) NOT NULL,

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

CREATE INDEX "agent_projects_organizationId_createdById_updatedAt_idx" ON "platform_ai"."agent_projects"("organizationId", "createdById", "updatedAt" DESC);

-- CreateTable: agent_personas (智能体角色：系统预设 + 用户自创)
CREATE TABLE "platform_ai"."agent_personas" (
    "id" UUID NOT NULL,
    "organizationId" UUID NOT NULL,
    "createdById" UUID,
    "systemKey" VARCHAR(60),
    "name" VARCHAR(120) NOT NULL,
    "icon" VARCHAR(60),
    "description" VARCHAR(280),
    "instructions" TEXT,
    "allowedTools" TEXT[] DEFAULT ARRAY[]::TEXT[],
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMPTZ(3) NOT NULL,

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

CREATE UNIQUE INDEX "agent_personas_organizationId_systemKey_key" ON "platform_ai"."agent_personas"("organizationId", "systemKey");
CREATE INDEX "agent_personas_organizationId_createdById_enabled_updatedAt_idx" ON "platform_ai"."agent_personas"("organizationId", "createdById", "enabled", "updatedAt" DESC);

-- CreateTable: agent_memories (per-user 持久记忆；FK 后置)
CREATE TABLE "platform_ai"."agent_memories" (
    "id" UUID NOT NULL,
    "organizationId" UUID NOT NULL,
    "createdById" UUID NOT NULL,
    "content" TEXT NOT NULL,
    "scope" "platform_ai"."MemoryScope" NOT NULL DEFAULT 'GLOBAL',
    "projectId" UUID,
    "personaId" UUID,
    "source" VARCHAR(60),
    "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMPTZ(3) NOT NULL,

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

CREATE INDEX "agent_memories_organizationId_createdById_scope_updatedAt_idx" ON "platform_ai"."agent_memories"("organizationId", "createdById", "scope", "updatedAt" DESC);
CREATE INDEX "agent_memories_projectId_idx" ON "platform_ai"."agent_memories"("projectId");
CREATE INDEX "agent_memories_personaId_idx" ON "platform_ai"."agent_memories"("personaId");

-- CreateTable: agent_mcp_servers (外部 MCP server 配置)
CREATE TABLE "platform_ai"."agent_mcp_servers" (
    "id" UUID NOT NULL,
    "organizationId" UUID NOT NULL,
    "createdById" UUID NOT NULL,
    "name" VARCHAR(120) NOT NULL,
    "transport" VARCHAR(20) NOT NULL,
    "endpoint" VARCHAR(500) NOT NULL,
    "args" TEXT,
    "env" TEXT,
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "lastConnectedAt" TIMESTAMPTZ(3),
    "lastError" VARCHAR(500),
    "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMPTZ(3) NOT NULL,

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

CREATE INDEX "agent_mcp_servers_organizationId_enabled_idx" ON "platform_ai"."agent_mcp_servers"("organizationId", "enabled");

-- AlterTable: agent_sessions ADD parentSessionId (多 Agent 拓扑)
ALTER TABLE "platform_ai"."agent_sessions" ADD COLUMN "parentSessionId" UUID;
CREATE INDEX "agent_sessions_parentSessionId_idx" ON "platform_ai"."agent_sessions"("parentSessionId");

-- CreateTable: agent_crons (定时任务；sessionId FK 后置)
CREATE TABLE "platform_ai"."agent_crons" (
    "id" UUID NOT NULL,
    "organizationId" UUID NOT NULL,
    "createdById" UUID NOT NULL,
    "sessionId" UUID NOT NULL,
    "name" VARCHAR(120) NOT NULL,
    "cronExpr" VARCHAR(60) NOT NULL,
    "prompt" TEXT NOT NULL,
    "enabled" BOOLEAN NOT NULL DEFAULT true,
    "lastRunAt" TIMESTAMPTZ(3),
    "nextRunAt" TIMESTAMPTZ(3) NOT NULL,
    "runCount" INTEGER NOT NULL DEFAULT 0,
    "failCount" INTEGER NOT NULL DEFAULT 0,
    "lastError" VARCHAR(500),
    "createdAt" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMPTZ(3) NOT NULL,

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

CREATE INDEX "agent_crons_enabled_nextRunAt_idx" ON "platform_ai"."agent_crons"("enabled", "nextRunAt");
CREATE INDEX "agent_crons_organizationId_createdById_updatedAt_idx" ON "platform_ai"."agent_crons"("organizationId", "createdById", "updatedAt" DESC);

-- ============================================================
-- FK 关系（所有相关表已建好后统一加）
-- ============================================================

-- agent_memories.projectId → agent_projects (删 project 时 memory.projectId SET NULL)
ALTER TABLE "platform_ai"."agent_memories"
  ADD CONSTRAINT "agent_memories_projectId_fkey"
  FOREIGN KEY ("projectId") REFERENCES "platform_ai"."agent_projects"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;

-- agent_memories.personaId → agent_personas (删 persona 时 memory.personaId SET NULL)
ALTER TABLE "platform_ai"."agent_memories"
  ADD CONSTRAINT "agent_memories_personaId_fkey"
  FOREIGN KEY ("personaId") REFERENCES "platform_ai"."agent_personas"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;

-- agent_sessions.parentSessionId 自引用 (父硬删时 child.parentSessionId SET NULL，不级联删 child)
ALTER TABLE "platform_ai"."agent_sessions"
  ADD CONSTRAINT "agent_sessions_parentSessionId_fkey"
  FOREIGN KEY ("parentSessionId") REFERENCES "platform_ai"."agent_sessions"("id")
  ON DELETE SET NULL ON UPDATE CASCADE;

-- agent_crons.sessionId → agent_sessions (session 硬删时 cron CASCADE 删，避免脏数据)
ALTER TABLE "platform_ai"."agent_crons"
  ADD CONSTRAINT "agent_crons_sessionId_fkey"
  FOREIGN KEY ("sessionId") REFERENCES "platform_ai"."agent_sessions"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;
