import type { FastifyInstance } from "fastify";
import type { MultipartFile } from "@fastify/multipart";
import { Prisma } from "@prisma/client";
import mammoth from "mammoth";
import { z } from "zod";
import { requireSession, type SessionPayload } from "../../lib/auth.js";
import { writeAuditLog } from "../../lib/audit.js";
import { createId } from "../../lib/id.js";
import { isImageKitConfigured, uploadBinaryFileToImageKit } from "../../lib/imagekit.js";
import { stampNotesOnPdf } from "../../lib/pdf-documents.js";
import { prisma } from "../../lib/prisma.js";
import { readBinaryFile, saveBinaryFile } from "../../lib/storage.js";
import { getSessionProfile } from "../../lib/session.js";

const createThreadSchema = z.object({
  teamId: z.string().uuid(),
  recipientUserId: z.string().uuid(),
  subject: z.string().min(2).max(255),
  topicType: z.enum(["general", "legal_doubt", "document_review"]).default("general"),
  caseId: z.string().uuid().optional().nullable(),
  bodyText: z.string().min(1).max(4000),
});

const createDocumentReviewThreadSchema = z.object({
  teamId: z.string().uuid(),
  recipientUserId: z.string().uuid(),
  subject: z.string().min(2).max(255),
  caseId: z.string().uuid(),
  reviewDueDate: z
    .string()
    .regex(/^\d{4}-\d{2}-\d{2}$/, "Informe a data limite da revisão no formato AAAA-MM-DD."),
  bodyText: z.string().min(1).max(4000),
});

const sendMessageSchema = z.object({
  teamId: z.string().uuid(),
  bodyText: z.string().min(1).max(4000),
  repliedMessageId: z.string().uuid().optional().nullable(),
});

const sendAttachmentSchema = z.object({
  teamId: z.string().uuid(),
  repliedMessageId: z.string().uuid().optional().nullable(),
  bodyText: z.string().trim().min(1).max(4000).optional().nullable(),
});

const threadParamsSchema = z.object({
  threadId: z.string().uuid(),
});

const closeThreadSchema = z.object({
  teamId: z.string().uuid(),
});

const addThreadParticipantSchema = z.object({
  teamId: z.string().uuid(),
  userId: z.string().uuid(),
});

const attachmentParamsSchema = z.object({
  threadId: z.string().uuid(),
  fileId: z.string().uuid(),
});

const attachmentCommentParamsSchema = z.object({
  threadId: z.string().uuid(),
  fileId: z.string().uuid(),
  commentId: z.string().uuid(),
});

const teamScopeQuerySchema = z.object({
  teamId: z.string().uuid(),
  status: z.enum(["open", "closed"]).optional().default("open"),
  q: z.string().trim().max(255).optional(),
});

const attachmentPreviewQuerySchema = z.object({
  teamId: z.string().uuid(),
});

const attachmentCommentSchema = z.object({
  teamId: z.string().uuid(),
  selectionStart: z.coerce.number().int().min(0).optional().nullable(),
  selectionEnd: z.coerce.number().int().min(1).optional().nullable(),
  selectedText: z.string().max(1500).optional().nullable(),
  selectionContextBefore: z.string().max(1200).optional().nullable(),
  selectionContextAfter: z.string().max(1200).optional().nullable(),
  pageNumber: z.coerce.number().int().min(1).optional().nullable(),
  anchorX: z.coerce.number().min(0).max(1).optional().nullable(),
  anchorY: z.coerce.number().min(0).max(1).optional().nullable(),
  selectionRects: z
    .array(
      z.object({
        x: z.coerce.number().min(0).max(1),
        y: z.coerce.number().min(0).max(1),
        width: z.coerce.number().positive().max(1),
        height: z.coerce.number().positive().max(1),
      }),
    )
    .max(24)
    .optional()
    .nullable(),
  commentText: z.string().min(1).max(4000),
});

const attachmentCommentUpdateSchema = z.object({
  teamId: z.string().uuid(),
  pageNumber: z.coerce.number().int().min(1).optional().nullable(),
  anchorX: z.coerce.number().min(0).max(1).optional().nullable(),
  anchorY: z.coerce.number().min(0).max(1).optional().nullable(),
  commentText: z.string().min(1).max(4000),
});

type TeamMessageParticipantPayload = {
  userId: string;
  displayName: string;
  email: string;
  isCurrentUser: boolean;
};

type TeamMessageThreadDetailPayload = {
  thread: {
    id: string;
    subject: string;
    topicType: "general" | "legal_doubt" | "document_review";
    reviewDueAt: Date | null;
    relatedCase: {
      id: string;
      caseNumber: string;
      title: string;
    } | null;
    status: "open" | "closed";
    participantUserId: string | null;
    participantDisplayName: string;
    participantEmail: string | null;
    participantCount: number;
    closedByUserId: string | null;
    closedByDisplayName: string | null;
    closedAt: Date | null;
    lastMessageAt: Date;
    createdAt: Date;
    participants: TeamMessageParticipantPayload[];
  };
  messages: Array<{
    id: string;
    senderUserId: string;
    senderDisplayName: string;
    senderEmail: string;
    bodyText: string;
    replyTo: {
      messageId: string;
      senderDisplayName: string;
      bodyText: string;
    } | null;
    attachment: {
      fileId: string;
      fileName: string;
      mimeType: string;
      sizeBytes: number;
      kind: "audio" | "file";
    } | null;
    createdAt: Date;
    isCurrentUser: boolean;
  }>;
};

type TeamMessageUnreadSummaryPayload = Array<{
  teamId: string;
  teamName: string;
  unreadMessagesCount: number;
  unreadThreadsCount: number;
}>;

function buildReviewDueAtDateTime(reviewDueDate: string) {
  return `${reviewDueDate} 23:59:59`;
}

type TeamMessageAttachmentCommentPayload = {
  id: string;
  pageNumber: number | null;
  anchorX: number | null;
  anchorY: number | null;
  selectionRects: Array<{
    x: number;
    y: number;
    width: number;
    height: number;
  }>;
  selectionStart: number | null;
  selectionEnd: number | null;
  selectedText: string;
  commentText: string;
  createdByUserId: string | null;
  createdBy: string;
  createdAt: Date;
};

type TeamMessageAttachmentReviewPreviewPayload = {
  attachment: {
    fileId: string;
    fileName: string;
    mimeType: string;
    sizeBytes: number;
    previewMode: "pdf" | "docx" | "generic";
  };
  docxHtml: string | null;
  comments: TeamMessageAttachmentCommentPayload[];
};

type TeamMessageRealtimeClient = {
  userId: string;
  teamId: string;
  socket: {
    readyState: number;
    send: (payload: string) => void;
    close: (code?: number, data?: string) => void;
    on: (event: "close" | "error", listener: () => void) => void;
  };
};

type TeamMessageRealtimeEvent =
  | {
      type: "connection.ready";
      teamId: string;
      connectedAt: string;
    }
  | {
      type: "thread.created" | "message.created" | "thread.closed" | "participant.added";
      teamId: string;
      threadId: string;
      detail: TeamMessageThreadDetailPayload;
    };

const realtimeClientsByUserId = new Map<string, Set<TeamMessageRealtimeClient>>();
const WEBSOCKET_OPEN_STATE = 1;
const WS_UNAUTHORIZED_CLOSE = 4401;
const WS_FORBIDDEN_CLOSE = 4403;

function sendRealtimeEvent(
  socket: TeamMessageRealtimeClient["socket"],
  event: TeamMessageRealtimeEvent,
) {
  if (socket.readyState !== WEBSOCKET_OPEN_STATE) {
    return;
  }

  socket.send(JSON.stringify(event));
}

function registerRealtimeClient(client: TeamMessageRealtimeClient) {
  const existing = realtimeClientsByUserId.get(client.userId) ?? new Set<TeamMessageRealtimeClient>();
  existing.add(client);
  realtimeClientsByUserId.set(client.userId, existing);

  const removeClient = () => {
    const current = realtimeClientsByUserId.get(client.userId);
    if (!current) {
      return;
    }

    current.delete(client);

    if (current.size === 0) {
      realtimeClientsByUserId.delete(client.userId);
    }
  };

  client.socket.on("close", removeClient);
  client.socket.on("error", removeClient);
}

function publishRealtimeEventsToUsers(input: {
  teamId: string;
  eventsByUserId: Map<string, TeamMessageRealtimeEvent>;
}) {
  for (const [userId, event] of input.eventsByUserId.entries()) {
    const clients = realtimeClientsByUserId.get(userId);

    if (!clients?.size) {
      continue;
    }

    for (const client of clients) {
      if (client.teamId !== input.teamId) {
        continue;
      }

      sendRealtimeEvent(client.socket, event);
    }
  }
}

function getUserDisplayName(row: {
  display_name: string | null;
  first_name: string | null;
  last_name: string | null;
  email?: string | null;
}) {
  const displayName = String(row.display_name ?? "").trim();
  if (displayName) {
    return displayName;
  }

  const fullName = `${String(row.first_name ?? "").trim()} ${String(row.last_name ?? "").trim()}`.trim();
  if (fullName) {
    return fullName;
  }

  return String(row.email ?? "").trim() || "Usuário";
}

function getMultipartFieldValue(
  fields: Record<string, MultipartFile["fields"][string]>,
  key: string,
) {
  const field = fields[key];

  if (!field || Array.isArray(field) || !("value" in field)) {
    return "";
  }

  return String(field.value ?? "");
}

function getTeamMessageAttachmentKind(mimeType: string) {
  return mimeType.toLowerCase().startsWith("audio/") ? "audio" : "file";
}

function getRepositoryItemTypeCodeForAttachment(mimeType: string) {
  const normalizedMimeType = mimeType.toLowerCase();

  if (normalizedMimeType.startsWith("audio/")) {
    return "audio";
  }

  if (normalizedMimeType.startsWith("image/")) {
    return "image";
  }

  if (normalizedMimeType.startsWith("video/")) {
    return "video";
  }

  return "document";
}

function buildTeamMessageAttachmentLabel(input: {
  kind: "audio" | "file";
  fileName: string;
}) {
  return input.kind === "audio"
    ? `Áudio enviado: ${input.fileName}`
    : `Arquivo enviado: ${input.fileName}`;
}

function isAllowedDocumentReviewAttachment(input: {
  fileName: string;
  mimeType: string;
}) {
  const normalizedMimeType = input.mimeType.toLowerCase();
  const normalizedFileName = input.fileName.toLowerCase();

  if (normalizedMimeType === "application/pdf") {
    return true;
  }

  if (
    normalizedMimeType ===
    "application/vnd.openxmlformats-officedocument.wordprocessingml.document"
  ) {
    return true;
  }

  if (normalizedMimeType.startsWith("image/")) {
    return true;
  }

  return [".pdf", ".docx", ".png", ".jpg", ".jpeg", ".gif", ".webp", ".bmp", ".tif", ".tiff"].some(
    (extension) => normalizedFileName.endsWith(extension),
  );
}

function getDocumentReviewPreviewMode(input: {
  fileName: string;
  mimeType: string;
}): "pdf" | "docx" | "generic" {
  const normalizedMimeType = input.mimeType.toLowerCase();
  const normalizedFileName = input.fileName.toLowerCase();

  if (normalizedMimeType === "application/pdf" || normalizedFileName.endsWith(".pdf")) {
    return "pdf";
  }

  if (
    normalizedMimeType ===
      "application/vnd.openxmlformats-officedocument.wordprocessingml.document" ||
    normalizedFileName.endsWith(".docx")
  ) {
    return "docx";
  }

  return "generic";
}

function sanitizeDocxPreviewHtml(value: string) {
  return String(value ?? "")
    .replace(/<script[\s\S]*?<\/script>/gi, "")
    .replace(/\son[a-z]+="[^"]*"/gi, "")
    .replace(/\son[a-z]+='[^']*'/gi, "");
}

function escapeHtml(value: string) {
  return String(value ?? "")
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#39;");
}

function normalizeAttachmentCommentSelectionRects(
  rects: Array<{ x: number; y: number; width: number; height: number }> | null | undefined,
) {
  if (!Array.isArray(rects) || rects.length === 0) {
    return [];
  }

  return rects
    .map((rect) => ({
      x: Math.min(1, Math.max(0, Number(rect.x ?? 0))),
      y: Math.min(1, Math.max(0, Number(rect.y ?? 0))),
      width: Math.min(1, Math.max(0, Number(rect.width ?? 0))),
      height: Math.min(1, Math.max(0, Number(rect.height ?? 0))),
    }))
    .filter((rect) => rect.width > 0 && rect.height > 0)
    .slice(0, 24);
}

function truncateInsight(value: string, limit = 180) {
  const normalized = String(value ?? "").replace(/\s+/g, " ").trim();
  if (normalized.length <= limit) {
    return normalized;
  }

  return `${normalized.slice(0, Math.max(0, limit - 1)).trimEnd()}…`;
}

function getThreadReadMarker(detail: TeamMessageThreadDetailPayload) {
  const lastMessage = detail.messages.at(-1);

  return {
    lastReadAt: lastMessage?.createdAt ?? detail.thread.lastMessageAt ?? detail.thread.createdAt,
    lastReadMessageId: lastMessage?.id ?? null,
  };
}

async function storeTeamMessageAttachmentBinary(input: {
  lawFirmId: string;
  fileName: string;
  mimeType: string;
  bytes: Buffer;
}) {
  if (isImageKitConfigured()) {
    return uploadBinaryFileToImageKit({
      bytes: input.bytes,
      fileName: input.fileName,
      mimeType: input.mimeType,
    });
  }

  const stored = await saveBinaryFile({
    lawFirmId: input.lawFirmId,
    caseId: null,
    fileName: input.fileName,
    bytes: input.bytes,
    kind: "uploads",
  });

  return {
    storageProvider: "local_dev",
    storageBucket: "workspace",
    objectKey: stored.relativeObjectKey,
    storageRegion: "local",
    storedFileName: stored.storedFileName,
    checksumSha256: stored.checksumSha256,
    uploadUrl: null,
  };
}

async function replaceTeamMessageAttachmentBinary(input: {
  lawFirmId: string;
  fileId: string;
  fileName: string;
  mimeType: string;
  bytes: Buffer;
  caseId: string | null;
  updatedByUserId: string;
}) {
  const stored = isImageKitConfigured()
    ? await uploadBinaryFileToImageKit({
        bytes: input.bytes,
        fileName: input.fileName,
        mimeType: input.mimeType,
      })
    : await (async () => {
        const localFile = await saveBinaryFile({
          lawFirmId: input.lawFirmId,
          caseId: input.caseId,
          fileName: input.fileName,
          bytes: input.bytes,
          kind: "uploads",
        });

        return {
          storageProvider: "local_dev",
          storageBucket: "workspace",
          objectKey: localFile.relativeObjectKey,
          storageRegion: "local",
          storedFileName: localFile.storedFileName,
          checksumSha256: localFile.checksumSha256,
          uploadUrl: null,
        };
      })();

  await prisma.$executeRaw`
    UPDATE files
    SET
      storage_provider = ${stored.storageProvider},
      storage_bucket = ${stored.storageBucket},
      object_key = ${stored.objectKey},
      storage_region = ${stored.storageRegion},
      stored_file_name = ${stored.storedFileName},
      mime_type = ${input.mimeType},
      size_bytes = ${input.bytes.length},
      checksum_sha256 = ${stored.checksumSha256},
      uploaded_by_user_id = ${input.updatedByUserId},
      uploaded_at = NOW()
    WHERE id = ${input.fileId}
      AND law_firm_id = ${input.lawFirmId}
  `;
}

async function getReplyTargetMessage(input: {
  lawFirmId: string;
  teamId: string;
  threadId: string;
  messageId: string | null | undefined;
}) {
  if (!input.messageId) {
    return null;
  }

  const [row] = await prisma.$queryRaw<
    Array<{
      id: string;
      body_text: string;
      sender_display_name: string | null;
      sender_first_name: string | null;
      sender_last_name: string | null;
      sender_email: string | null;
    }>
  >`
    SELECT
      tm.id,
      tm.body_text,
      u.display_name AS sender_display_name,
      u.first_name AS sender_first_name,
      u.last_name AS sender_last_name,
      u.email AS sender_email
    FROM team_messages tm
    INNER JOIN users u ON u.id = tm.sender_user_id
    WHERE tm.id = ${input.messageId}
      AND tm.law_firm_id = ${input.lawFirmId}
      AND tm.team_id = ${input.teamId}
      AND tm.thread_id = ${input.threadId}
    LIMIT 1
  `;

  if (!row) {
    return null;
  }

  return {
    messageId: row.id,
    senderDisplayName: getUserDisplayName({
      display_name: row.sender_display_name,
      first_name: row.sender_first_name,
      last_name: row.sender_last_name,
      email: row.sender_email,
    }),
    bodyText: row.body_text,
  };
}

async function getTeamMembership(input: {
  lawFirmId: string;
  userId: string;
  teamId: string;
}) {
  const [row] = await prisma.$queryRaw<
    Array<{
      team_id: string;
      team_name: string;
      membership_role: string;
    }>
  >`
    SELECT
      tm.team_id,
      t.name AS team_name,
      tm.membership_role
    FROM team_memberships tm
    INNER JOIN teams t ON t.id = tm.team_id
    WHERE tm.law_firm_id = ${input.lawFirmId}
      AND tm.team_id = ${input.teamId}
      AND tm.user_id = ${input.userId}
    LIMIT 1
  `;

  if (!row) {
    return null;
  }

  return {
    teamId: row.team_id,
    teamName: row.team_name,
    membershipRole: row.membership_role,
  };
}

async function getTeamMember(input: {
  lawFirmId: string;
  teamId: string;
  userId: string;
}) {
  const [row] = await prisma.$queryRaw<
    Array<{
      user_id: string;
      display_name: string | null;
      first_name: string | null;
      last_name: string | null;
      email: string;
    }>
  >`
    SELECT
      u.id AS user_id,
      u.display_name,
      u.first_name,
      u.last_name,
      u.email
    FROM team_memberships tm
    INNER JOIN users u ON u.id = tm.user_id
    WHERE tm.law_firm_id = ${input.lawFirmId}
      AND tm.team_id = ${input.teamId}
      AND tm.user_id = ${input.userId}
    LIMIT 1
  `;

  if (!row) {
    return null;
  }

  return {
    userId: row.user_id,
    displayName: getUserDisplayName({
      display_name: row.display_name,
      first_name: row.first_name,
      last_name: row.last_name,
      email: row.email,
    }),
    email: row.email,
  };
}

async function getCaseSummary(input: {
  lawFirmId: string;
  caseId: string;
}) {
  const [row] = await prisma.$queryRaw<
    Array<{
      id: string;
      case_number: string;
      title: string;
    }>
  >`
    SELECT id, case_number, title
    FROM cases
    WHERE id = ${input.caseId}
      AND law_firm_id = ${input.lawFirmId}
    LIMIT 1
  `;

  if (!row) {
    return null;
  }

  return {
    id: row.id,
    caseNumber: row.case_number,
    title: row.title,
  };
}

async function createTeamMessageAttachmentRecords(input: {
  tx: Prisma.TransactionClient;
  lawFirmId: string;
  teamId: string;
  threadId: string;
  userId: string;
  fileName: string;
  mimeType: string;
  sizeBytes: number;
  checksumSha256: string;
  storageProvider: string;
  storageBucket: string;
  objectKey: string;
  storageRegion: string | null;
  storedFileName: string;
  caseId: string | null;
  repositoryItemTypeCode: string;
  summaryText: string;
  metadataJson: Record<string, unknown>;
}) {
  const repositoryItemId = createId();
  const fileId = createId();

  await input.tx.$executeRaw`
    INSERT INTO repository_items (
      id, law_firm_id, client_id, case_id, item_type_code, channel_code, source_entity_type,
      source_entity_id, direction_code, subject, body_text, summary_text, metadata_json,
      authored_by_user_id, created_by_user_id, external_reference, occurred_at, created_at, updated_at
    ) VALUES (
      ${repositoryItemId},
      ${input.lawFirmId},
      NULL,
      ${input.caseId},
      ${input.repositoryItemTypeCode},
      'internal',
      'team_message_thread',
      ${input.threadId},
      'internal',
      ${input.fileName},
      ${input.summaryText},
      'Anexo compartilhado em uma conversa interna do team.',
      ${JSON.stringify(input.metadataJson)},
      ${input.userId},
      ${input.userId},
      NULL,
      NOW(),
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
    )
  `;

  await input.tx.$executeRaw`
    INSERT INTO files (
      id, law_firm_id, client_id, case_id, repository_item_id, storage_provider, storage_bucket,
      object_key, storage_region, original_file_name, stored_file_name, mime_type, size_bytes,
      checksum_sha256, is_encrypted, uploaded_by_user_id, uploaded_at, created_at
    ) VALUES (
      ${fileId},
      ${input.lawFirmId},
      NULL,
      ${input.caseId},
      ${repositoryItemId},
      ${input.storageProvider},
      ${input.storageBucket},
      ${input.objectKey},
      ${input.storageRegion},
      ${input.fileName},
      ${input.storedFileName},
      ${input.mimeType},
      ${input.sizeBytes},
      ${input.checksumSha256},
      0,
      ${input.userId},
      NOW(),
      CURRENT_TIMESTAMP
    )
  `;

  return {
    repositoryItemId,
    fileId,
  };
}

function buildThreadParticipantLabel(input: {
  participants: TeamMessageParticipantPayload[];
  currentUserId: string;
}) {
  const otherParticipants = input.participants.filter(
    (participant) => participant.userId !== input.currentUserId,
  );

  if (otherParticipants.length === 0) {
    return {
      participantUserId: null,
      participantDisplayName: "Você",
      participantEmail: null,
      participantCount: input.participants.length,
    };
  }

  if (otherParticipants.length === 1) {
    return {
      participantUserId: otherParticipants[0].userId,
      participantDisplayName: otherParticipants[0].displayName,
      participantEmail: otherParticipants[0].email,
      participantCount: input.participants.length,
    };
  }

  if (otherParticipants.length === 2) {
    return {
      participantUserId: otherParticipants[0].userId,
      participantDisplayName: `${otherParticipants[0].displayName} e ${otherParticipants[1].displayName}`,
      participantEmail: null,
      participantCount: input.participants.length,
    };
  }

  return {
    participantUserId: otherParticipants[0].userId,
    participantDisplayName: `${otherParticipants[0].displayName}, ${otherParticipants[1].displayName} +${
      otherParticipants.length - 2
    }`,
    participantEmail: null,
    participantCount: input.participants.length,
  };
}

async function listThreadParticipantsMap(input: {
  lawFirmId: string;
  teamId: string;
  threadIds: string[];
  currentUserId: string;
}) {
  const uniqueThreadIds = Array.from(new Set(input.threadIds.filter(Boolean)));

  if (!uniqueThreadIds.length) {
    return new Map<string, TeamMessageParticipantPayload[]>();
  }

  const rows = await prisma.$queryRaw<
    Array<{
      thread_id: string;
      user_id: string;
      display_name: string | null;
      first_name: string | null;
      last_name: string | null;
      email: string;
    }>
  >(Prisma.sql`
    SELECT
      tmtp.thread_id,
      u.id AS user_id,
      u.display_name,
      u.first_name,
      u.last_name,
      u.email
    FROM team_message_thread_participants tmtp
    INNER JOIN users u ON u.id = tmtp.user_id
    WHERE tmtp.law_firm_id = ${input.lawFirmId}
      AND tmtp.team_id = ${input.teamId}
      AND tmtp.thread_id IN (${Prisma.join(uniqueThreadIds)})
    ORDER BY tmtp.joined_at ASC
  `);

  const participantsByThreadId = new Map<string, TeamMessageParticipantPayload[]>();

  for (const row of rows) {
    const nextParticipant = {
      userId: row.user_id,
      displayName: getUserDisplayName({
        display_name: row.display_name,
        first_name: row.first_name,
        last_name: row.last_name,
        email: row.email,
      }),
      email: row.email,
      isCurrentUser: row.user_id === input.currentUserId,
    } satisfies TeamMessageParticipantPayload;

    const current = participantsByThreadId.get(row.thread_id) ?? [];
    current.push(nextParticipant);
    participantsByThreadId.set(row.thread_id, current);
  }

  return participantsByThreadId;
}

async function listThreadParticipantUserIds(input: {
  lawFirmId: string;
  teamId: string;
  threadId: string;
}) {
  const rows = await prisma.$queryRaw<Array<{ user_id: string }>>`
    SELECT user_id
    FROM team_message_thread_participants
    WHERE law_firm_id = ${input.lawFirmId}
      AND team_id = ${input.teamId}
      AND thread_id = ${input.threadId}
    ORDER BY joined_at ASC
  `;

  return rows.map((row) => row.user_id);
}

async function markThreadAsRead(input: {
  lawFirmId: string;
  teamId: string;
  threadId: string;
  userId: string;
  lastReadMessageId: string | null;
  lastReadAt: Date;
}) {
  const lastReadAtValue = input.lastReadMessageId
    ? Prisma.sql`(
        SELECT tm.created_at
        FROM team_messages tm
        WHERE tm.id = ${input.lastReadMessageId}
          AND tm.law_firm_id = ${input.lawFirmId}
          AND tm.team_id = ${input.teamId}
          AND tm.thread_id = ${input.threadId}
        LIMIT 1
      )`
    : Prisma.sql`${input.lastReadAt}`;

  await prisma.$executeRaw(Prisma.sql`
    INSERT INTO team_message_thread_reads (
      id, law_firm_id, team_id, thread_id, user_id, last_read_message_id, last_read_at, created_at, updated_at
    ) VALUES (
      ${createId()},
      ${input.lawFirmId},
      ${input.teamId},
      ${input.threadId},
      ${input.userId},
      ${input.lastReadMessageId},
      ${lastReadAtValue},
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
    )
    ON DUPLICATE KEY UPDATE
      last_read_at = CASE
        WHEN team_message_thread_reads.last_read_at IS NULL
          OR team_message_thread_reads.last_read_at < VALUES(last_read_at)
        THEN VALUES(last_read_at)
        ELSE team_message_thread_reads.last_read_at
      END,
      last_read_message_id = CASE
        WHEN team_message_thread_reads.last_read_at IS NULL
          OR team_message_thread_reads.last_read_at <= VALUES(last_read_at)
        THEN VALUES(last_read_message_id)
        ELSE team_message_thread_reads.last_read_message_id
      END,
      updated_at = CURRENT_TIMESTAMP
  `);
}

async function listUnreadMessageSummaryForUser(input: {
  lawFirmId: string;
  userId: string;
}): Promise<TeamMessageUnreadSummaryPayload> {
  const rows = await prisma.$queryRaw<
    Array<{
      team_id: string;
      team_name: string;
      unread_messages_count: bigint;
      unread_threads_count: bigint;
    }>
  >`
    SELECT
      tm.team_id,
      t.name AS team_name,
      COUNT(
        CASE
          WHEN msg.id IS NOT NULL
            AND msg.sender_user_id <> ${input.userId}
            AND (
              read_state.last_read_message_id IS NULL
              OR last_read_msg.sequence_number IS NULL
              OR msg.sequence_number > last_read_msg.sequence_number
            )
          THEN 1
          ELSE NULL
        END
      ) AS unread_messages_count,
      COUNT(
        DISTINCT CASE
          WHEN msg.id IS NOT NULL
            AND msg.sender_user_id <> ${input.userId}
            AND (
              read_state.last_read_message_id IS NULL
              OR last_read_msg.sequence_number IS NULL
              OR msg.sequence_number > last_read_msg.sequence_number
            )
          THEN msg.thread_id
          ELSE NULL
        END
      ) AS unread_threads_count
    FROM team_memberships tm
    INNER JOIN teams t ON t.id = tm.team_id
    LEFT JOIN team_message_thread_participants access
      ON access.law_firm_id = tm.law_firm_id
     AND access.team_id = tm.team_id
     AND access.user_id = tm.user_id
    LEFT JOIN team_messages msg
      ON msg.law_firm_id = access.law_firm_id
     AND msg.team_id = access.team_id
     AND msg.thread_id = access.thread_id
    LEFT JOIN team_message_thread_reads read_state
      ON read_state.law_firm_id = access.law_firm_id
     AND read_state.team_id = access.team_id
     AND read_state.thread_id = access.thread_id
     AND read_state.user_id = access.user_id
    LEFT JOIN team_messages last_read_msg
      ON last_read_msg.id = read_state.last_read_message_id
     AND last_read_msg.law_firm_id = access.law_firm_id
     AND last_read_msg.team_id = access.team_id
     AND last_read_msg.thread_id = access.thread_id
    WHERE tm.law_firm_id = ${input.lawFirmId}
      AND tm.user_id = ${input.userId}
    GROUP BY tm.team_id, t.name
    ORDER BY t.name ASC
  `;

  return rows.map((row) => ({
    teamId: row.team_id,
    teamName: row.team_name,
    unreadMessagesCount: Number(row.unread_messages_count ?? 0),
    unreadThreadsCount: Number(row.unread_threads_count ?? 0),
  }));
}

async function appendAgentMemory(input: {
  lawFirmId: string;
  teamId: string;
  agentId: string;
  sourceType: string;
  sourceEntityId?: string | null;
  memoryText: string;
  createdByUserId: string;
}) {
  const memoryText = String(input.memoryText ?? "").trim();
  if (!memoryText) {
    return;
  }

  await prisma.$executeRaw`
    INSERT INTO team_agent_memories (
      id, law_firm_id, team_id, agent_id, source_type, source_entity_id,
      memory_text, created_by_user_id, created_at
    ) VALUES (
      ${createId()},
      ${input.lawFirmId},
      ${input.teamId},
      ${input.agentId},
      ${input.sourceType},
      ${input.sourceEntityId ?? null},
      ${memoryText},
      ${input.createdByUserId},
      CURRENT_TIMESTAMP
    )
  `;

  const [agentOwner] = await prisma.$queryRaw<Array<{ user_id: string }>>`
    SELECT user_id
    FROM team_agents
    WHERE law_firm_id = ${input.lawFirmId}
      AND id = ${input.agentId}
    LIMIT 1
  `;

  if (!agentOwner) {
    return;
  }

  const memoryRows = await prisma.$queryRaw<Array<{ memory_text: string }>>`
    SELECT tam.memory_text
    FROM team_agent_memories tam
    INNER JOIN team_agents ta ON ta.id = tam.agent_id
    WHERE tam.law_firm_id = ${input.lawFirmId}
      AND ta.user_id = ${agentOwner.user_id}
    ORDER BY tam.created_at DESC
    LIMIT 6
  `;

  const learningSummary = memoryRows.length
    ? memoryRows
        .map((row) => `- ${truncateInsight(row.memory_text, 220)}`)
        .join("\n")
    : null;

  const [memoryStats] = await prisma.$queryRaw<
    Array<{
      memory_count: number;
      last_learning_at: Date | null;
    }>
  >`
    SELECT
      COUNT(*) AS memory_count,
      MAX(tam.created_at) AS last_learning_at
    FROM team_agent_memories tam
    INNER JOIN team_agents ta ON ta.id = tam.agent_id
    WHERE tam.law_firm_id = ${input.lawFirmId}
      AND ta.user_id = ${agentOwner.user_id}
  `;

  await prisma.$executeRaw`
    UPDATE team_agents
    SET
      learning_summary = ${learningSummary},
      memory_count = ${Number(memoryStats?.memory_count ?? 0)},
      last_learning_at = ${memoryStats?.last_learning_at ?? null},
      updated_at = CURRENT_TIMESTAMP
    WHERE law_firm_id = ${input.lawFirmId}
      AND user_id = ${agentOwner.user_id}
  `;
}

async function getTeamAgentIdForUser(input: {
  lawFirmId: string;
  teamId: string;
  userId: string;
}) {
  const agentRows = await prisma.$queryRaw<
    Array<{
      id: string;
    }>
  >`
    SELECT
      ta.id
    FROM team_agents ta
    WHERE ta.law_firm_id = ${input.lawFirmId}
      AND ta.user_id = ${input.userId}
    ORDER BY ta.created_at ASC, ta.id ASC
    LIMIT 1
  `;

  return agentRows[0]?.id ?? null;
}

function getChatTopicLabel(topicType: "general" | "legal_doubt" | "document_review") {
  switch (topicType) {
    case "legal_doubt":
      return "dúvida jurídica";
    case "document_review":
      return "revisão de documento";
    default:
      return "conversa interna";
  }
}

function formatMemoryDate(value: Date | string | null | undefined) {
  if (!value) {
    return null;
  }

  const date = new Date(value);
  if (Number.isNaN(date.getTime())) {
    return null;
  }

  return date.toISOString().slice(0, 10);
}

function getChatMemorySourceType(input: {
  topicType: "general" | "legal_doubt" | "document_review";
  contributionType: "thread_opening" | "message" | "attachment" | "pdf_comment";
}) {
  if (input.contributionType === "pdf_comment") {
    return "team_message_document_review_comment";
  }

  switch (input.topicType) {
    case "legal_doubt":
      return "team_message_legal_doubt";
    case "document_review":
      return "team_message_document_review";
    default:
      return "team_message_general_chat";
  }
}

async function appendChatContributionMemory(input: {
  lawFirmId: string;
  teamId: string;
  teamName: string;
  senderUserId: string;
  senderDisplayName: string;
  threadId: string;
  subject: string;
  topicType: "general" | "legal_doubt" | "document_review";
  relatedCase?: {
    caseNumber: string;
    title: string;
  } | null;
  reviewDueAt?: Date | null;
  participants: Array<{
    userId: string;
    displayName: string;
  }>;
  sourceType: string;
  sourceEntityId?: string | null;
  entryLabel: string;
  bodyText: string;
}) {
  const memoryBodyText = String(input.bodyText ?? "").trim();
  if (!memoryBodyText) {
    return;
  }

  const agentId = await getTeamAgentIdForUser({
    lawFirmId: input.lawFirmId,
    teamId: input.teamId,
    userId: input.senderUserId,
  });

  if (!agentId) {
    return;
  }

  const otherParticipantNames = input.participants
    .filter((participant) => participant.userId !== input.senderUserId)
    .map((participant) => participant.displayName);

  const memoryParts = [
    `Interação registrada no chat do team ${input.teamName}.`,
    `Tipo da conversa: ${getChatTopicLabel(input.topicType)}.`,
    `Assunto: ${input.subject}.`,
    input.relatedCase
      ? `Caso relacionado: ${input.relatedCase.caseNumber} - ${input.relatedCase.title}.`
      : null,
    formatMemoryDate(input.reviewDueAt)
      ? `Prazo de revisão: ${formatMemoryDate(input.reviewDueAt)}.`
      : null,
    otherParticipantNames.length
      ? `Demais participantes: ${otherParticipantNames.join(", ")}.`
      : null,
    `${input.senderDisplayName} registrou ${input.entryLabel}: ${memoryBodyText}`,
  ].filter(Boolean);

  await appendAgentMemory({
    lawFirmId: input.lawFirmId,
    teamId: input.teamId,
    agentId,
    sourceType: input.sourceType,
    sourceEntityId: input.sourceEntityId ?? input.threadId,
    memoryText: memoryParts.join(" "),
    createdByUserId: input.senderUserId,
  });
}

async function listThreadSummaries(input: {
  lawFirmId: string;
  teamId: string;
  userId: string;
  status: "open" | "closed";
  query?: string | null;
}) {
  const normalizedQuery = String(input.query ?? "").trim();
  const rows = await prisma.$queryRaw<
    Array<{
      id: string;
      subject: string;
      topic_type: string;
      case_id: string | null;
      review_due_at: Date | null;
      case_number: string | null;
      case_title: string | null;
      status_code: string;
      created_at: Date;
      last_message_at: Date | null;
      closed_at: Date | null;
      closed_by_user_id: string | null;
    }>
  >`
    SELECT
      tmt.id,
      tmt.subject,
      tmt.topic_type,
      tmt.case_id,
      tmt.review_due_at,
      c.case_number,
      c.title AS case_title,
      tmt.status_code,
      tmt.created_at,
      tmt.last_message_at,
      tmt.closed_at,
      tmt.closed_by_user_id
    FROM team_message_threads tmt
    INNER JOIN team_message_thread_participants access
      ON access.thread_id = tmt.id
     AND access.user_id = ${input.userId}
    LEFT JOIN cases c ON c.id = tmt.case_id
    WHERE tmt.law_firm_id = ${input.lawFirmId}
      AND tmt.team_id = ${input.teamId}
      AND tmt.status_code = ${input.status}
    ORDER BY COALESCE(tmt.last_message_at, tmt.created_at) DESC
  `;

  if (!rows.length) {
    return [];
  }

  const threadIds = rows.map((row) => row.id);
  const participantsByThreadId = await listThreadParticipantsMap({
    lawFirmId: input.lawFirmId,
    teamId: input.teamId,
    threadIds,
    currentUserId: input.userId,
  });
  const messageRows = await prisma.$queryRaw<
    Array<{
      id: string;
      sequence_number: bigint;
      thread_id: string;
      body_text: string;
      created_at: Date;
    }>
  >(Prisma.sql`
    SELECT id, sequence_number, thread_id, body_text, created_at
    FROM team_messages
    WHERE law_firm_id = ${input.lawFirmId}
      AND team_id = ${input.teamId}
      AND thread_id IN (${Prisma.join(threadIds)})
    ORDER BY thread_id ASC, sequence_number DESC
  `);
  const messageCountRows = await prisma.$queryRaw<
    Array<{
      thread_id: string;
      message_count: bigint;
    }>
  >(Prisma.sql`
    SELECT thread_id, COUNT(*) AS message_count
    FROM team_messages
    WHERE law_firm_id = ${input.lawFirmId}
      AND team_id = ${input.teamId}
      AND thread_id IN (${Prisma.join(threadIds)})
    GROUP BY thread_id
  `);

  const lastMessageByThreadId = new Map<string, { bodyText: string; createdAt: Date }>();
  for (const row of messageRows) {
    if (!lastMessageByThreadId.has(row.thread_id)) {
      lastMessageByThreadId.set(row.thread_id, {
        bodyText: row.body_text,
        createdAt: row.created_at,
      });
    }
  }

  const messageCountByThreadId = new Map(
    messageCountRows.map((row) => [row.thread_id, Number(row.message_count ?? 0)]),
  );

  return rows
    .map((row) => {
      const participants = participantsByThreadId.get(row.id) ?? [];
      const participantLabel = buildThreadParticipantLabel({
        participants,
        currentUserId: input.userId,
      });
      const lastMessage = lastMessageByThreadId.get(row.id);

      return {
        id: row.id,
        subject: row.subject,
        topicType: row.topic_type as "general" | "legal_doubt" | "document_review",
        reviewDueAt: row.review_due_at,
        relatedCase:
          row.case_id && row.case_number && row.case_title
            ? {
                id: row.case_id,
                caseNumber: row.case_number,
                title: row.case_title,
              }
            : null,
        status: row.status_code as "open" | "closed",
        participantUserId: participantLabel.participantUserId,
        participantDisplayName: participantLabel.participantDisplayName,
        participantEmail: participantLabel.participantEmail,
        participantCount: participantLabel.participantCount,
        lastMessagePreview: lastMessage ? truncateInsight(lastMessage.bodyText, 140) : null,
        closedByUserId: row.closed_by_user_id,
        closedAt: row.closed_at,
        lastMessageAt: row.last_message_at ?? row.created_at,
        messageCount: messageCountByThreadId.get(row.id) ?? 0,
        createdAt: row.created_at,
      };
    })
    .filter((thread) => {
      if (!normalizedQuery) {
        return true;
      }

      const queryText = normalizedQuery.toLowerCase();
      const participants = participantsByThreadId.get(thread.id) ?? [];

      return [
        thread.subject,
        thread.relatedCase?.caseNumber ?? "",
        thread.relatedCase?.title ?? "",
        thread.participantDisplayName,
        thread.participantEmail ?? "",
        thread.lastMessagePreview ?? "",
        ...participants.map((participant) => participant.displayName),
        ...participants.map((participant) => participant.email),
      ].some((value) => value.toLowerCase().includes(queryText));
    });
}

async function getThreadDetail(input: {
  lawFirmId: string;
  teamId: string;
  userId: string;
  threadId: string;
}): Promise<TeamMessageThreadDetailPayload | null> {
  const [threadRow] = await prisma.$queryRaw<
    Array<{
      id: string;
      subject: string;
      topic_type: string;
      case_id: string | null;
      review_due_at: Date | null;
      case_number: string | null;
      case_title: string | null;
      status_code: string;
      created_at: Date;
      last_message_at: Date | null;
      closed_at: Date | null;
      closed_by_user_id: string | null;
      closed_by_display_name: string | null;
      closed_by_first_name: string | null;
      closed_by_last_name: string | null;
      closed_by_email: string | null;
    }>
  >`
    SELECT
      tmt.id,
      tmt.subject,
      tmt.topic_type,
      tmt.case_id,
      tmt.review_due_at,
      c.case_number,
      c.title AS case_title,
      tmt.status_code,
      tmt.created_at,
      tmt.last_message_at,
      tmt.closed_at,
      tmt.closed_by_user_id,
      cu.display_name AS closed_by_display_name,
      cu.first_name AS closed_by_first_name,
      cu.last_name AS closed_by_last_name,
      cu.email AS closed_by_email
    FROM team_message_threads tmt
    INNER JOIN team_message_thread_participants access
      ON access.thread_id = tmt.id
     AND access.user_id = ${input.userId}
    LEFT JOIN cases c ON c.id = tmt.case_id
    LEFT JOIN users cu ON cu.id = tmt.closed_by_user_id
    WHERE tmt.id = ${input.threadId}
      AND tmt.law_firm_id = ${input.lawFirmId}
      AND tmt.team_id = ${input.teamId}
    LIMIT 1
  `;

  if (!threadRow) {
    return null;
  }

  const participantsByThreadId = await listThreadParticipantsMap({
    lawFirmId: input.lawFirmId,
    teamId: input.teamId,
    threadIds: [input.threadId],
    currentUserId: input.userId,
  });
  const participants = participantsByThreadId.get(input.threadId) ?? [];
  const participantLabel = buildThreadParticipantLabel({
    participants,
    currentUserId: input.userId,
  });

  const messages = await prisma.$queryRaw<
    Array<{
      id: string;
      sequence_number: bigint;
      sender_user_id: string;
      sender_display_name: string | null;
      sender_first_name: string | null;
      sender_last_name: string | null;
      sender_email: string;
      body_text: string;
      replied_message_id: string | null;
      replied_body_text: string | null;
      replied_sender_display_name: string | null;
      replied_sender_first_name: string | null;
      replied_sender_last_name: string | null;
      replied_sender_email: string | null;
      attachment_file_id: string | null;
      attachment_original_file_name: string | null;
      attachment_mime_type: string | null;
      attachment_size_bytes: bigint | null;
      created_at: Date;
    }>
  >`
    SELECT
      tm.id,
      tm.sequence_number,
      tm.sender_user_id,
      u.display_name AS sender_display_name,
      u.first_name AS sender_first_name,
      u.last_name AS sender_last_name,
      u.email AS sender_email,
      tm.body_text,
      tm.replied_message_id,
      replied.body_text AS replied_body_text,
      replied_user.display_name AS replied_sender_display_name,
      replied_user.first_name AS replied_sender_first_name,
      replied_user.last_name AS replied_sender_last_name,
      replied_user.email AS replied_sender_email,
      tm.attachment_file_id,
      f.original_file_name AS attachment_original_file_name,
      f.mime_type AS attachment_mime_type,
      f.size_bytes AS attachment_size_bytes,
      tm.created_at
    FROM team_messages tm
    INNER JOIN users u ON u.id = tm.sender_user_id
    LEFT JOIN team_messages replied ON replied.id = tm.replied_message_id
    LEFT JOIN users replied_user ON replied_user.id = replied.sender_user_id
    LEFT JOIN files f ON f.id = tm.attachment_file_id
    WHERE tm.law_firm_id = ${input.lawFirmId}
      AND tm.team_id = ${input.teamId}
      AND tm.thread_id = ${input.threadId}
    ORDER BY tm.sequence_number ASC
  `;

  return {
    thread: {
      id: threadRow.id,
      subject: threadRow.subject,
      topicType: threadRow.topic_type as "general" | "legal_doubt" | "document_review",
      reviewDueAt: threadRow.review_due_at,
      relatedCase:
        threadRow.case_id && threadRow.case_number && threadRow.case_title
          ? {
              id: threadRow.case_id,
              caseNumber: threadRow.case_number,
              title: threadRow.case_title,
            }
          : null,
      status: threadRow.status_code as "open" | "closed",
      participantUserId: participantLabel.participantUserId,
      participantDisplayName: participantLabel.participantDisplayName,
      participantEmail: participantLabel.participantEmail,
      participantCount: participantLabel.participantCount,
      closedByUserId: threadRow.closed_by_user_id,
      closedByDisplayName: threadRow.closed_by_user_id
        ? getUserDisplayName({
            display_name: threadRow.closed_by_display_name,
            first_name: threadRow.closed_by_first_name,
            last_name: threadRow.closed_by_last_name,
            email: threadRow.closed_by_email,
          })
        : null,
      closedAt: threadRow.closed_at,
      lastMessageAt: threadRow.last_message_at ?? threadRow.created_at,
      createdAt: threadRow.created_at,
      participants,
    },
    messages: messages.map((row) => ({
      id: row.id,
      senderUserId: row.sender_user_id,
      senderDisplayName: getUserDisplayName({
        display_name: row.sender_display_name,
        first_name: row.sender_first_name,
        last_name: row.sender_last_name,
        email: row.sender_email,
      }),
      senderEmail: row.sender_email,
      bodyText: row.body_text,
      replyTo:
        row.replied_message_id && row.replied_body_text
          ? {
              messageId: row.replied_message_id,
              senderDisplayName: getUserDisplayName({
                display_name: row.replied_sender_display_name,
                first_name: row.replied_sender_first_name,
                last_name: row.replied_sender_last_name,
                email: row.replied_sender_email,
              }),
              bodyText: row.replied_body_text,
            }
          : null,
      attachment:
        row.attachment_file_id && row.attachment_original_file_name && row.attachment_mime_type
          ? {
              fileId: row.attachment_file_id,
              fileName: row.attachment_original_file_name,
              mimeType: row.attachment_mime_type,
              sizeBytes: Number(row.attachment_size_bytes ?? 0),
              kind: getTeamMessageAttachmentKind(row.attachment_mime_type),
            }
          : null,
      createdAt: row.created_at,
      isCurrentUser: row.sender_user_id === input.userId,
    })),
  };
}

async function getThreadAttachmentContext(input: {
  lawFirmId: string;
  teamId: string;
  userId: string;
  threadId: string;
  fileId: string;
}) {
  const [row] = await prisma.$queryRaw<
    Array<{
      message_id: string;
      attachment_file_id: string;
      review_source_file_id: string | null;
      file_name: string;
      mime_type: string;
      size_bytes: bigint | null;
      case_id: string | null;
      storage_provider: string;
      object_key: string;
      review_source_storage_provider: string | null;
      review_source_object_key: string | null;
      topic_type: string;
      status_code: string;
    }>
  >`
    SELECT
      tm.id AS message_id,
      tm.attachment_file_id,
      tm.review_source_file_id,
      f.original_file_name AS file_name,
      f.mime_type,
      f.size_bytes,
      f.case_id,
      f.storage_provider,
      f.object_key,
      source_file.storage_provider AS review_source_storage_provider,
      source_file.object_key AS review_source_object_key,
      tmt.topic_type,
      tmt.status_code
    FROM team_messages tm
    INNER JOIN team_message_threads tmt ON tmt.id = tm.thread_id
    INNER JOIN team_message_thread_participants access
      ON access.thread_id = tmt.id
     AND access.user_id = ${input.userId}
    INNER JOIN files f ON f.id = tm.attachment_file_id
    LEFT JOIN files source_file ON source_file.id = tm.review_source_file_id
    WHERE tm.law_firm_id = ${input.lawFirmId}
      AND tm.team_id = ${input.teamId}
      AND tm.thread_id = ${input.threadId}
      AND tm.attachment_file_id = ${input.fileId}
    LIMIT 1
  `;

  if (!row) {
    return null;
  }

  return {
    messageId: row.message_id,
    attachmentFileId: row.attachment_file_id,
    reviewSourceFileId: row.review_source_file_id,
    fileName: row.file_name,
    mimeType: row.mime_type,
    sizeBytes: Number(row.size_bytes ?? 0),
    caseId: row.case_id,
    storageProvider: row.storage_provider,
    objectKey: row.object_key,
    reviewSourceStorageProvider: row.review_source_storage_provider,
    reviewSourceObjectKey: row.review_source_object_key,
    topicType: row.topic_type as "general" | "legal_doubt" | "document_review",
    threadStatus: row.status_code as "open" | "closed",
    previewMode: getDocumentReviewPreviewMode({
      fileName: row.file_name,
      mimeType: row.mime_type,
    }),
  };
}

async function listAttachmentComments(input: {
  lawFirmId: string;
  teamId: string;
  threadId: string;
  attachmentFileId: string;
}) {
  const rows = await prisma.$queryRaw<
    Array<{
      id: string;
      page_number: number | null;
      anchor_x_ratio: Prisma.Decimal | null;
      anchor_y_ratio: Prisma.Decimal | null;
      selection_rects_json: unknown;
      selection_start_offset: number | null;
      selection_end_offset: number | null;
      selected_text: string;
      comment_text: string;
      created_by_user_id: string | null;
      created_by_display_name: string | null;
      created_by_first_name: string | null;
      created_by_last_name: string | null;
      created_by_email: string | null;
      created_at: Date;
    }>
  >`
    SELECT
      comment.id,
      comment.page_number,
      comment.anchor_x_ratio,
      comment.anchor_y_ratio,
      comment.selection_rects_json,
      comment.selection_start_offset,
      comment.selection_end_offset,
      comment.selected_text,
      comment.comment_text,
      comment.created_by_user_id,
      u.display_name AS created_by_display_name,
      u.first_name AS created_by_first_name,
      u.last_name AS created_by_last_name,
      u.email AS created_by_email,
      comment.created_at
    FROM team_message_attachment_comments comment
    LEFT JOIN users u ON u.id = comment.created_by_user_id
    WHERE comment.law_firm_id = ${input.lawFirmId}
      AND comment.team_id = ${input.teamId}
      AND comment.thread_id = ${input.threadId}
      AND comment.attachment_file_id = ${input.attachmentFileId}
    ORDER BY comment.created_at DESC
  `;

  return rows.map((row) => ({
    id: row.id,
    pageNumber: row.page_number,
    anchorX:
      row.anchor_x_ratio === null || row.anchor_x_ratio === undefined
        ? null
        : Number(row.anchor_x_ratio),
    anchorY:
      row.anchor_y_ratio === null || row.anchor_y_ratio === undefined
        ? null
        : Number(row.anchor_y_ratio),
    selectionRects: normalizeAttachmentCommentSelectionRects(
      Array.isArray(row.selection_rects_json)
        ? (row.selection_rects_json as Array<{ x: number; y: number; width: number; height: number }>)
        : [],
    ),
    selectionStart: row.selection_start_offset,
    selectionEnd: row.selection_end_offset,
    selectedText: row.selected_text,
    commentText: row.comment_text,
    createdByUserId: row.created_by_user_id,
    createdBy: getUserDisplayName({
      display_name: row.created_by_display_name,
      first_name: row.created_by_first_name,
      last_name: row.created_by_last_name,
      email: row.created_by_email,
    }),
    createdAt: row.created_at,
  })) satisfies TeamMessageAttachmentCommentPayload[];
}

async function ensureAttachmentReviewSourceFile(input: {
  lawFirmId: string;
  teamId: string;
  threadId: string;
  messageId: string;
  attachmentFileId: string;
  reviewSourceFileId: string | null;
  reviewSourceStorageProvider: string | null;
  reviewSourceObjectKey: string | null;
  fileName: string;
  mimeType: string;
  caseId: string | null;
  currentBytes: Buffer;
  userId: string;
}) {
  if (input.reviewSourceFileId && input.reviewSourceStorageProvider && input.reviewSourceObjectKey) {
    return {
      fileId: input.reviewSourceFileId,
      storageProvider: input.reviewSourceStorageProvider,
      objectKey: input.reviewSourceObjectKey,
    };
  }

  const stored = await storeTeamMessageAttachmentBinary({
    lawFirmId: input.lawFirmId,
    fileName: input.fileName,
    mimeType: input.mimeType,
    bytes: input.currentBytes,
  });
  const repositoryItemTypeCode = getRepositoryItemTypeCodeForAttachment(input.mimeType);
  const created = await prisma.$transaction(async (tx) => {
    const record = await createTeamMessageAttachmentRecords({
      tx,
      lawFirmId: input.lawFirmId,
      teamId: input.teamId,
      threadId: input.threadId,
      userId: input.userId,
      fileName: input.fileName,
      mimeType: input.mimeType,
      sizeBytes: input.currentBytes.length,
      checksumSha256: stored.checksumSha256,
      storageProvider: stored.storageProvider,
      storageBucket: stored.storageBucket,
      objectKey: stored.objectKey,
      storageRegion: stored.storageRegion,
      storedFileName: stored.storedFileName,
      caseId: input.caseId,
      repositoryItemTypeCode,
      summaryText: `Base original da revisão: ${input.fileName}`,
      metadataJson: {
        teamId: input.teamId,
        threadId: input.threadId,
        messageId: input.messageId,
        attachmentFileId: input.attachmentFileId,
        purpose: "team_message_review_source",
      },
    });

    await tx.$executeRaw`
      UPDATE team_messages
      SET review_source_file_id = ${record.fileId}
      WHERE id = ${input.messageId}
        AND law_firm_id = ${input.lawFirmId}
        AND team_id = ${input.teamId}
    `;

    return record;
  });

  return {
    fileId: created.fileId,
    storageProvider: stored.storageProvider,
    objectKey: stored.objectKey,
  };
}

async function rebuildAttachmentPdfFromComments(input: {
  lawFirmId: string;
  teamId: string;
  threadId: string;
  attachmentFileId: string;
  sourceStorageProvider: string;
  sourceObjectKey: string;
  outputFileName: string;
  outputMimeType: string;
  caseId: string | null;
  updatedByUserId: string;
}) {
  const sourceBytes = await readBinaryFile({
    storageProvider: input.sourceStorageProvider,
    objectKey: input.sourceObjectKey,
  });
  const comments = (await listAttachmentComments({
    lawFirmId: input.lawFirmId,
    teamId: input.teamId,
    threadId: input.threadId,
    attachmentFileId: input.attachmentFileId,
  })).slice().reverse();
  const stamped = await stampNotesOnPdf({
    bytes: sourceBytes,
    notes: comments
      .filter((comment) => comment.pageNumber && comment.anchorX !== null && comment.anchorY !== null)
      .map((comment) => ({
        pageNumber: comment.pageNumber ?? 1,
        anchorX: comment.anchorX ?? 0.5,
        anchorY: comment.anchorY ?? 0.5,
        noteText: comment.commentText,
        authorLabel: comment.createdBy,
      })),
  });

  await replaceTeamMessageAttachmentBinary({
    lawFirmId: input.lawFirmId,
    fileId: input.attachmentFileId,
    fileName: input.outputFileName,
    mimeType: input.outputMimeType,
    bytes: stamped.bytes,
    caseId: input.caseId,
    updatedByUserId: input.updatedByUserId,
  });
}

async function buildRealtimeEventsForParticipants(input: {
  eventType: "thread.created" | "message.created" | "thread.closed" | "participant.added";
  lawFirmId: string;
  teamId: string;
  threadId: string;
  participantUserIds: string[];
}) {
  const uniqueUserIds = Array.from(new Set(input.participantUserIds));
  const detailEntries = await Promise.all(
    uniqueUserIds.map(async (userId) => {
      const detail = await getThreadDetail({
        lawFirmId: input.lawFirmId,
        teamId: input.teamId,
        userId,
        threadId: input.threadId,
      });

      return [userId, detail] as const;
    }),
  );

  const eventsByUserId = new Map<string, TeamMessageRealtimeEvent>();

  for (const [userId, detail] of detailEntries) {
    if (!detail) {
      continue;
    }

    eventsByUserId.set(userId, {
      type: input.eventType,
      teamId: input.teamId,
      threadId: input.threadId,
      detail,
    });
  }

  return eventsByUserId;
}

export async function registerTeamMessageRoutes(app: FastifyInstance) {
  app.get(
    "/ws",
    {
      websocket: true,
      preValidation: async (request, reply) => {
        await requireSession(request, reply);
      },
    },
    async (socket, request) => {
      try {
        const query = teamScopeQuerySchema.parse(request.query);
        const session = await request.jwtVerify<SessionPayload>();
        const profile = await getSessionProfile(session);

        if (!profile) {
          socket.close(WS_UNAUTHORIZED_CLOSE, "Session is no longer valid");
          return;
        }

        const membership = await getTeamMembership({
          lawFirmId: profile.lawFirm.id,
          userId: profile.user.id,
          teamId: query.teamId,
        });

        if (!membership) {
          socket.close(WS_FORBIDDEN_CLOSE, "Você não faz parte deste team.");
          return;
        }

        registerRealtimeClient({
          userId: profile.user.id,
          teamId: membership.teamId,
          socket,
        });

        sendRealtimeEvent(socket, {
          type: "connection.ready",
          teamId: membership.teamId,
          connectedAt: new Date().toISOString(),
        });
      } catch {
        socket.close(WS_UNAUTHORIZED_CLOSE, "Authentication required");
      }
    },
  );

  app.get("/unread-summary", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    return listUnreadMessageSummaryForUser({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
    });
  });

  app.get("/threads", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const query = teamScopeQuerySchema.parse(request.query);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: query.teamId,
    });

    if (!membership) {
      return [];
    }

    return listThreadSummaries({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      status: query.status,
      query: query.q,
    });
  });

  app.post("/threads", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const parsedPayload = createThreadSchema.safeParse(request.body);

    if (!parsedPayload.success) {
      return reply.badRequest(
        parsedPayload.error.issues[0]?.message ?? "Dados inválidos para criar a conversa.",
      );
    }

    const payload = parsedPayload.data;

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    if (payload.topicType === "document_review") {
      return reply.badRequest(
        "Envie um documento PDF, DOCX ou imagem para iniciar uma conversa de revisão de documento.",
      );
    }

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: payload.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    if (payload.recipientUserId === profile.user.id) {
      throw reply.badRequest("Escolha outra pessoa do team para iniciar a conversa.");
    }

    const recipient = await getTeamMember({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: payload.recipientUserId,
    });

    if (!recipient) {
      throw reply.badRequest("A pessoa escolhida não faz parte do team atual.");
    }

    const threadId = createId();
    const messageId = createId();

    await prisma.$transaction(async (tx) => {
      await tx.$executeRaw`
        INSERT INTO team_message_threads (
          id, law_firm_id, team_id, case_id, subject, topic_type, status_code, participant_a_user_id,
          participant_b_user_id, created_by_user_id, last_message_at, created_at, updated_at
        ) VALUES (
          ${threadId},
          ${profile.lawFirm.id},
          ${membership.teamId},
          NULL,
          ${payload.subject.trim()},
          ${payload.topicType},
          ${"open"},
          ${profile.user.id},
          ${recipient.userId},
          ${profile.user.id},
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        INSERT INTO team_message_thread_participants (
          id, law_firm_id, team_id, thread_id, user_id, added_by_user_id, joined_at
        ) VALUES (
          ${createId()},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${profile.user.id},
          ${profile.user.id},
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        INSERT INTO team_message_thread_participants (
          id, law_firm_id, team_id, thread_id, user_id, added_by_user_id, joined_at
        ) VALUES (
          ${createId()},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${recipient.userId},
          ${profile.user.id},
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        INSERT INTO team_message_thread_reads (
          id, law_firm_id, team_id, thread_id, user_id, last_read_message_id, last_read_at, created_at, updated_at
        ) VALUES (
          ${createId()},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${profile.user.id},
          ${messageId},
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        INSERT INTO team_messages (
          id, law_firm_id, team_id, thread_id, sender_user_id, body_text, created_at
        ) VALUES (
          ${messageId},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${profile.user.id},
          ${payload.bodyText.trim()},
          CURRENT_TIMESTAMP
        )
      `;
    });

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "team_message_thread",
      entityId: threadId,
      action: "team.message_thread.create",
      afterJson: {
        teamId: membership.teamId,
        recipientUserId: recipient.userId,
        topicType: payload.topicType,
        caseId: null,
      },
      request,
    });

    const detail = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detail) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    await appendChatContributionMemory({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      teamName: membership.teamName,
      senderUserId: profile.user.id,
      senderDisplayName: profile.user.displayName,
      threadId,
      subject: detail.thread.subject,
      topicType: detail.thread.topicType,
      relatedCase: detail.thread.relatedCase,
      reviewDueAt: detail.thread.reviewDueAt,
      participants: detail.thread.participants.map((participant) => ({
        userId: participant.userId,
        displayName: participant.displayName,
      })),
      sourceType: getChatMemorySourceType({
        topicType: detail.thread.topicType,
        contributionType: "thread_opening",
      }),
      sourceEntityId: messageId,
      entryLabel: "a mensagem inicial da conversa",
      bodyText: payload.bodyText.trim(),
    });

    const readMarker = getThreadReadMarker(detail);
    await markThreadAsRead({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      userId: profile.user.id,
      lastReadMessageId: readMarker.lastReadMessageId,
      lastReadAt: readMarker.lastReadAt,
    });

    const realtimeEvents = await buildRealtimeEventsForParticipants({
      eventType: "thread.created",
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      participantUserIds: [profile.user.id, recipient.userId],
    });

    publishRealtimeEventsToUsers({
      teamId: membership.teamId,
      eventsByUserId: realtimeEvents,
    });

    return reply.code(201).send(detail);
  });

  app.post("/threads/document-review", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const multipartRequest = request as typeof request & {
      file: () => Promise<MultipartFile | undefined>;
    };
    const file = await multipartRequest.file();

    if (!file) {
      return reply.badRequest(
        "Envie um documento PDF, DOCX ou imagem para iniciar a revisão.",
      );
    }

    const parsedPayload = createDocumentReviewThreadSchema.safeParse({
      teamId: getMultipartFieldValue(file.fields, "teamId"),
      recipientUserId: getMultipartFieldValue(file.fields, "recipientUserId"),
      subject: getMultipartFieldValue(file.fields, "subject"),
      caseId: getMultipartFieldValue(file.fields, "caseId"),
      reviewDueDate: getMultipartFieldValue(file.fields, "reviewDueDate"),
      bodyText: getMultipartFieldValue(file.fields, "bodyText"),
    });

    if (!parsedPayload.success) {
      return reply.badRequest(
        parsedPayload.error.issues[0]?.message ??
          "Dados inválidos para criar a conversa de revisão.",
      );
    }

    const payload = parsedPayload.data;
    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: payload.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    if (payload.recipientUserId === profile.user.id) {
      throw reply.badRequest("Escolha outra pessoa do team para iniciar a conversa.");
    }

    const recipient = await getTeamMember({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: payload.recipientUserId,
    });

    if (!recipient) {
      throw reply.badRequest("A pessoa escolhida não faz parte do team atual.");
    }

    const relatedCase = await getCaseSummary({
      lawFirmId: profile.lawFirm.id,
      caseId: payload.caseId,
    });

    if (!relatedCase) {
      throw reply.badRequest("Selecione um caso válido para a conversa de revisão de documento.");
    }

    const buffer = await file.toBuffer();

    if (!buffer.length) {
      throw reply.badRequest("O arquivo enviado está vazio.");
    }

    const normalizedMimeType =
      String(file.mimetype || "application/octet-stream").trim() || "application/octet-stream";
    const normalizedFileName = String(file.filename || "").trim() || `documento_${Date.now()}`;

    if (
      !isAllowedDocumentReviewAttachment({
        fileName: normalizedFileName,
        mimeType: normalizedMimeType,
      })
    ) {
      return reply.badRequest(
        "O documento da revisão deve ser PDF, DOCX ou imagem.",
      );
    }

    const attachmentKind = getTeamMessageAttachmentKind(normalizedMimeType);
    const attachmentLabel = buildTeamMessageAttachmentLabel({
      kind: attachmentKind,
      fileName: normalizedFileName,
    });
    const reviewDueAt = buildReviewDueAtDateTime(payload.reviewDueDate);
    const repositoryItemTypeCode = getRepositoryItemTypeCodeForAttachment(normalizedMimeType);
    const stored = await storeTeamMessageAttachmentBinary({
      lawFirmId: profile.lawFirm.id,
      fileName: normalizedFileName,
      mimeType: normalizedMimeType,
      bytes: buffer,
    });
    const threadId = createId();
    const messageId = createId();
    let repositoryItemId = "";
    let fileId = "";

    await prisma.$transaction(async (tx) => {
      await tx.$executeRaw`
        INSERT INTO team_message_threads (
          id, law_firm_id, team_id, case_id, review_due_at, subject, topic_type, status_code, participant_a_user_id,
          participant_b_user_id, created_by_user_id, last_message_at, created_at, updated_at
        ) VALUES (
          ${threadId},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${relatedCase.id},
          ${reviewDueAt},
          ${payload.subject.trim()},
          ${"document_review"},
          ${"open"},
          ${profile.user.id},
          ${recipient.userId},
          ${profile.user.id},
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        INSERT INTO team_message_thread_participants (
          id, law_firm_id, team_id, thread_id, user_id, added_by_user_id, joined_at
        ) VALUES (
          ${createId()},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${profile.user.id},
          ${profile.user.id},
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        INSERT INTO team_message_thread_participants (
          id, law_firm_id, team_id, thread_id, user_id, added_by_user_id, joined_at
        ) VALUES (
          ${createId()},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${recipient.userId},
          ${profile.user.id},
          CURRENT_TIMESTAMP
        )
      `;

      const createdAttachment = await createTeamMessageAttachmentRecords({
        tx,
        lawFirmId: profile.lawFirm.id,
        teamId: membership.teamId,
        threadId,
        userId: profile.user.id,
        fileName: normalizedFileName,
        mimeType: normalizedMimeType,
        sizeBytes: buffer.length,
        checksumSha256: stored.checksumSha256,
        storageProvider: stored.storageProvider,
        storageBucket: stored.storageBucket,
        objectKey: stored.objectKey,
        storageRegion: stored.storageRegion,
        storedFileName: stored.storedFileName,
        caseId: relatedCase.id,
        repositoryItemTypeCode,
        summaryText: attachmentLabel,
        metadataJson: {
          teamId: membership.teamId,
          threadId,
          mimeType: normalizedMimeType,
          sizeBytes: buffer.length,
          attachmentKind,
          caseId: relatedCase.id,
          topicType: "document_review",
        },
      });
      repositoryItemId = createdAttachment.repositoryItemId;
      fileId = createdAttachment.fileId;

      await tx.$executeRaw`
        INSERT INTO team_message_thread_reads (
          id, law_firm_id, team_id, thread_id, user_id, last_read_message_id, last_read_at, created_at, updated_at
        ) VALUES (
          ${createId()},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${profile.user.id},
          ${messageId},
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        INSERT INTO team_messages (
          id, law_firm_id, team_id, thread_id, sender_user_id, body_text, attachment_file_id, created_at
        ) VALUES (
          ${messageId},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${profile.user.id},
          ${payload.bodyText.trim()},
          ${fileId},
          CURRENT_TIMESTAMP
        )
      `;
    });

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "team_message_thread",
      entityId: threadId,
      action: "team.message_thread.document_review.create",
      afterJson: {
        teamId: membership.teamId,
        recipientUserId: recipient.userId,
        topicType: "document_review",
        caseId: relatedCase.id,
        reviewDueAt,
        fileId,
        repositoryItemId,
        mimeType: normalizedMimeType,
        sizeBytes: buffer.length,
      },
      request,
    });

    const detail = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detail) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    await appendChatContributionMemory({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      teamName: membership.teamName,
      senderUserId: profile.user.id,
      senderDisplayName: profile.user.displayName,
      threadId,
      subject: detail.thread.subject,
      topicType: detail.thread.topicType,
      relatedCase: detail.thread.relatedCase,
      reviewDueAt: detail.thread.reviewDueAt,
      participants: detail.thread.participants.map((participant) => ({
        userId: participant.userId,
        displayName: participant.displayName,
      })),
      sourceType: getChatMemorySourceType({
        topicType: detail.thread.topicType,
        contributionType: "thread_opening",
      }),
      sourceEntityId: messageId,
      entryLabel: "a abertura da revisão com documento anexado",
      bodyText: `${payload.bodyText.trim()} Documento enviado: ${normalizedFileName}.`,
    });

    const readMarker = getThreadReadMarker(detail);
    await markThreadAsRead({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      userId: profile.user.id,
      lastReadMessageId: readMarker.lastReadMessageId,
      lastReadAt: readMarker.lastReadAt,
    });

    const realtimeEvents = await buildRealtimeEventsForParticipants({
      eventType: "thread.created",
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      participantUserIds: [profile.user.id, recipient.userId],
    });

    publishRealtimeEventsToUsers({
      teamId: membership.teamId,
      eventsByUserId: realtimeEvents,
    });

    return reply.code(201).send(detail);
  });

  app.get("/threads/:threadId", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { threadId } = threadParamsSchema.parse(request.params);
    const query = teamScopeQuerySchema.parse(request.query);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: query.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    const detail = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detail) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    const readMarker = getThreadReadMarker(detail);
    await markThreadAsRead({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      userId: profile.user.id,
      lastReadMessageId: readMarker.lastReadMessageId,
      lastReadAt: readMarker.lastReadAt,
    });

    return detail;
  });

  app.get("/threads/:threadId/attachments/:fileId/review-preview", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { threadId, fileId } = attachmentParamsSchema.parse(request.params);
    const query = attachmentPreviewQuerySchema.parse(request.query);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: query.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    const attachmentContext = await getThreadAttachmentContext({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
      fileId,
    });

    if (!attachmentContext) {
      throw reply.notFound("Anexo da conversa não encontrado.");
    }

    let docxHtml: string | null = null;

    if (attachmentContext.previewMode === "docx") {
      const bytes = await readBinaryFile({
        storageProvider: attachmentContext.storageProvider,
        objectKey: attachmentContext.objectKey,
      });

      try {
        const converted = await mammoth.convertToHtml({ buffer: bytes });
        const normalizedHtml = sanitizeDocxPreviewHtml(converted.value ?? "");
        docxHtml = normalizedHtml.trim()
          ? normalizedHtml
          : `<p>${escapeHtml(attachmentContext.fileName)}</p>`;
      } catch {
        try {
          const fallback = await mammoth.extractRawText({ buffer: bytes });
          const normalizedText = String(fallback.value ?? "").trim();
          docxHtml = normalizedText
            ? normalizedText
                .split(/\n{2,}/)
                .map((paragraph) => `<p>${escapeHtml(paragraph.replace(/\s+/g, " ").trim())}</p>`)
                .join("")
            : `<p>${escapeHtml(attachmentContext.fileName)}</p>`;
        } catch {
          docxHtml = `<p>Preview unavailable for ${escapeHtml(attachmentContext.fileName)}.</p>`;
        }
      }
    }

    const comments = await listAttachmentComments({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      attachmentFileId: attachmentContext.attachmentFileId,
    });

    return {
      attachment: {
        fileId: attachmentContext.attachmentFileId,
        fileName: attachmentContext.fileName,
        mimeType: attachmentContext.mimeType,
        sizeBytes: attachmentContext.sizeBytes,
        previewMode: attachmentContext.previewMode,
      },
      docxHtml,
      comments,
    } satisfies TeamMessageAttachmentReviewPreviewPayload;
  });

  app.post("/threads/:threadId/attachments/:fileId/comments", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { threadId, fileId } = attachmentParamsSchema.parse(request.params);
    const payload = attachmentCommentSchema.parse(request.body);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: payload.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    const attachmentContext = await getThreadAttachmentContext({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
      fileId,
    });

    if (!attachmentContext) {
      throw reply.notFound("Anexo da conversa não encontrado.");
    }

    if (attachmentContext.topicType !== "document_review") {
      throw reply.badRequest("Comentários ancorados estão disponíveis apenas em revisões de documento.");
    }

    if (attachmentContext.threadStatus === "closed") {
      throw reply.badRequest("Esta conversa já foi encerrada.");
    }

    if (attachmentContext.previewMode !== "pdf") {
      throw reply.badRequest("Comentários ancorados no modal estão disponíveis apenas para anexos PDF.");
    }

    const normalizedSelectedText = String(payload.selectedText ?? "").trim();
    const normalizedCommentText = String(payload.commentText ?? "").trim();
    const normalizedSelectionRects = normalizeAttachmentCommentSelectionRects(
      payload.selectionRects ?? [],
    );
    const existingComments = await listAttachmentComments({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      attachmentFileId: attachmentContext.attachmentFileId,
    });

    if (!normalizedCommentText) {
      throw reply.badRequest("Escreva a anotação que deve ser gravada no PDF.");
    }

    if (
      payload.pageNumber === null ||
      payload.pageNumber === undefined ||
      payload.anchorX === null ||
      payload.anchorX === undefined ||
      payload.anchorY === null ||
      payload.anchorY === undefined
    ) {
      throw reply.badRequest("Clique em um ponto do PDF antes de salvar a anotação.");
    }

    const currentPdfBytes = await readBinaryFile({
      storageProvider: attachmentContext.storageProvider,
      objectKey: attachmentContext.objectKey,
    });
    if (!attachmentContext.reviewSourceFileId && existingComments.length > 0) {
      throw reply.badRequest(
        "Este PDF usa anotações antigas e precisa ser reenviado para permitir edição e reposicionamento.",
      );
    }

    const reviewSource = await ensureAttachmentReviewSourceFile({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      messageId: attachmentContext.messageId,
      attachmentFileId: attachmentContext.attachmentFileId,
      reviewSourceFileId: attachmentContext.reviewSourceFileId ?? null,
      reviewSourceStorageProvider: attachmentContext.reviewSourceStorageProvider ?? null,
      reviewSourceObjectKey: attachmentContext.reviewSourceObjectKey ?? null,
      fileName: attachmentContext.fileName,
      mimeType: attachmentContext.mimeType,
      caseId: attachmentContext.caseId ?? null,
      currentBytes: currentPdfBytes,
      userId: profile.user.id,
    });

    const commentId = createId();

    await prisma.$executeRaw`
      INSERT INTO team_message_attachment_comments (
        id, law_firm_id, team_id, thread_id, message_id, attachment_file_id,
        page_number, anchor_x_ratio, anchor_y_ratio, selection_rects_json,
        selection_start_offset, selection_end_offset, selected_text, comment_text,
        created_by_user_id, created_at
      ) VALUES (
        ${commentId},
        ${profile.lawFirm.id},
        ${membership.teamId},
        ${threadId},
        ${attachmentContext.messageId},
        ${attachmentContext.attachmentFileId},
        ${payload.pageNumber ?? null},
        ${payload.anchorX ?? null},
        ${payload.anchorY ?? null},
        ${normalizedSelectionRects.length ? JSON.stringify(normalizedSelectionRects) : null},
        ${payload.selectionStart ?? null},
        ${payload.selectionEnd ?? null},
        ${normalizedSelectedText},
        ${normalizedCommentText},
        ${profile.user.id},
        CURRENT_TIMESTAMP
      )
    `;

    await rebuildAttachmentPdfFromComments({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      attachmentFileId: attachmentContext.attachmentFileId,
      sourceStorageProvider: reviewSource.storageProvider,
      sourceObjectKey: reviewSource.objectKey,
      outputFileName: attachmentContext.fileName,
      outputMimeType: attachmentContext.mimeType,
      caseId: attachmentContext.caseId ?? null,
      updatedByUserId: profile.user.id,
    });

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "team_message_attachment_comment",
      entityId: commentId,
      action: "team.message_attachment.comment.create",
      afterJson: {
        teamId: membership.teamId,
        threadId,
        messageId: attachmentContext.messageId,
        fileId: attachmentContext.attachmentFileId,
        pageNumber: payload.pageNumber ?? null,
      },
      request,
    });

    const [createdComment] = await listAttachmentComments({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      attachmentFileId: attachmentContext.attachmentFileId,
    });

    if (!createdComment) {
      throw reply.internalServerError("Não foi possível carregar o comentário criado.");
    }

    const memoryDetail = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (memoryDetail) {
      await appendChatContributionMemory({
        lawFirmId: profile.lawFirm.id,
        teamId: membership.teamId,
        teamName: membership.teamName,
        senderUserId: profile.user.id,
        senderDisplayName: profile.user.displayName,
        threadId,
        subject: memoryDetail.thread.subject,
        topicType: memoryDetail.thread.topicType,
        relatedCase: memoryDetail.thread.relatedCase,
        reviewDueAt: memoryDetail.thread.reviewDueAt,
        participants: memoryDetail.thread.participants.map((participant) => ({
          userId: participant.userId,
          displayName: participant.displayName,
        })),
        sourceType: getChatMemorySourceType({
          topicType: memoryDetail.thread.topicType,
          contributionType: "pdf_comment",
        }),
        sourceEntityId: createdComment.id,
        entryLabel: "uma anotação no PDF",
        bodyText: `Página ${createdComment.pageNumber ?? "sem página definida"}: ${createdComment.commentText}`,
      });
    }

    return reply.code(201).send(createdComment);
  });

  app.patch("/threads/:threadId/attachments/:fileId/comments/:commentId", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { threadId, fileId, commentId } = attachmentCommentParamsSchema.parse(request.params);
    const payload = attachmentCommentUpdateSchema.parse(request.body);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: payload.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    const attachmentContext = await getThreadAttachmentContext({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
      fileId,
    });

    if (!attachmentContext) {
      throw reply.notFound("Anexo da conversa não encontrado.");
    }

    if (attachmentContext.topicType !== "document_review") {
      throw reply.badRequest("Esta atualização só está disponível em revisões de documento.");
    }

    if (attachmentContext.threadStatus === "closed") {
      throw reply.badRequest("Esta conversa já foi encerrada.");
    }

    if (attachmentContext.previewMode !== "pdf") {
      throw reply.badRequest("A edição de anotações só está disponível para anexos PDF.");
    }

    const comments = await listAttachmentComments({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      attachmentFileId: attachmentContext.attachmentFileId,
    });
    const targetComment = comments.find((comment) => comment.id === commentId) ?? null;

    if (!targetComment) {
      throw reply.notFound("Anotação da revisão não encontrada.");
    }

    if (targetComment.createdByUserId && targetComment.createdByUserId !== profile.user.id) {
      throw reply.forbidden("Somente quem criou a anotação pode editá-la ou movê-la.");
    }

    if (!attachmentContext.reviewSourceFileId || !attachmentContext.reviewSourceStorageProvider || !attachmentContext.reviewSourceObjectKey) {
      throw reply.badRequest(
        "Esta anotação é de uma versão antiga do PDF e precisa ser refeita para permitir edição.",
      );
    }

    const nextCommentText = payload.commentText.trim();
    const nextPageNumber = payload.pageNumber ?? targetComment.pageNumber ?? 1;
    const nextAnchorX = payload.anchorX ?? targetComment.anchorX ?? 0.5;
    const nextAnchorY = payload.anchorY ?? targetComment.anchorY ?? 0.5;

    await prisma.$executeRaw`
      UPDATE team_message_attachment_comments
      SET
        page_number = ${nextPageNumber},
        anchor_x_ratio = ${nextAnchorX},
        anchor_y_ratio = ${nextAnchorY},
        comment_text = ${nextCommentText},
        updated_at = CURRENT_TIMESTAMP
      WHERE id = ${commentId}
        AND law_firm_id = ${profile.lawFirm.id}
        AND team_id = ${membership.teamId}
        AND thread_id = ${threadId}
        AND attachment_file_id = ${attachmentContext.attachmentFileId}
    `;

    await rebuildAttachmentPdfFromComments({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      attachmentFileId: attachmentContext.attachmentFileId,
      sourceStorageProvider: attachmentContext.reviewSourceStorageProvider,
      sourceObjectKey: attachmentContext.reviewSourceObjectKey,
      outputFileName: attachmentContext.fileName,
      outputMimeType: attachmentContext.mimeType,
      caseId: attachmentContext.caseId ?? null,
      updatedByUserId: profile.user.id,
    });

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "team_message_attachment_comment",
      entityId: commentId,
      action: "team.message_attachment.comment.update",
      afterJson: {
        teamId: membership.teamId,
        threadId,
        fileId: attachmentContext.attachmentFileId,
        pageNumber: nextPageNumber,
        anchorX: nextAnchorX,
        anchorY: nextAnchorY,
      },
      request,
    });

    const updatedComments = await listAttachmentComments({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      attachmentFileId: attachmentContext.attachmentFileId,
    });
    const updatedComment = updatedComments.find((comment) => comment.id === commentId) ?? null;

    if (!updatedComment) {
      throw reply.internalServerError("Não foi possível carregar a anotação atualizada.");
    }

    const memoryDetail = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (memoryDetail) {
      await appendChatContributionMemory({
        lawFirmId: profile.lawFirm.id,
        teamId: membership.teamId,
        teamName: membership.teamName,
        senderUserId: profile.user.id,
        senderDisplayName: profile.user.displayName,
        threadId,
        subject: memoryDetail.thread.subject,
        topicType: memoryDetail.thread.topicType,
        relatedCase: memoryDetail.thread.relatedCase,
        reviewDueAt: memoryDetail.thread.reviewDueAt,
        participants: memoryDetail.thread.participants.map((participant) => ({
          userId: participant.userId,
          displayName: participant.displayName,
        })),
        sourceType: getChatMemorySourceType({
          topicType: memoryDetail.thread.topicType,
          contributionType: "pdf_comment",
        }),
        sourceEntityId: updatedComment.id,
        entryLabel: "uma atualização de anotação no PDF",
        bodyText: `Página ${updatedComment.pageNumber ?? "sem página definida"}: ${updatedComment.commentText}`,
      });
    }

    return updatedComment;
  });

  app.post("/threads/:threadId/messages", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { threadId } = threadParamsSchema.parse(request.params);
    const payload = sendMessageSchema.parse(request.body);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: payload.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    const detailBefore = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detailBefore) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    if (detailBefore.thread.status === "closed") {
      throw reply.badRequest("Esta conversa já foi encerrada.");
    }

    const replyTarget = await getReplyTargetMessage({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      messageId: payload.repliedMessageId,
    });

    if (payload.repliedMessageId && !replyTarget) {
      throw reply.badRequest("A mensagem escolhida para responder não foi encontrada.");
    }

    const messageId = createId();

    await prisma.$transaction(async (tx) => {
      await tx.$executeRaw`
        INSERT INTO team_messages (
          id, law_firm_id, team_id, thread_id, sender_user_id, body_text, replied_message_id, created_at
        ) VALUES (
          ${messageId},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${profile.user.id},
          ${payload.bodyText.trim()},
          ${replyTarget?.messageId ?? null},
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        UPDATE team_message_threads
        SET
          last_message_at = NOW(),
          updated_at = CURRENT_TIMESTAMP
        WHERE id = ${threadId}
          AND law_firm_id = ${profile.lawFirm.id}
          AND team_id = ${membership.teamId}
      `;
    });

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "team_message",
      entityId: messageId,
      action: "team.message.create",
      afterJson: {
        teamId: membership.teamId,
        threadId,
        repliedMessageId: replyTarget?.messageId ?? null,
      },
      request,
    });

    const detail = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detail) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    const memoryBodyText = replyTarget
      ? `Em resposta a ${replyTarget.senderDisplayName}: ${replyTarget.bodyText} | ${payload.bodyText.trim()}`
      : payload.bodyText.trim();

    await appendChatContributionMemory({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      teamName: membership.teamName,
      senderUserId: profile.user.id,
      senderDisplayName: profile.user.displayName,
      threadId,
      subject: detail.thread.subject,
      topicType: detail.thread.topicType,
      relatedCase: detail.thread.relatedCase,
      reviewDueAt: detail.thread.reviewDueAt,
      participants: detail.thread.participants.map((participant) => ({
        userId: participant.userId,
        displayName: participant.displayName,
      })),
      sourceType: getChatMemorySourceType({
        topicType: detail.thread.topicType,
        contributionType: "message",
      }),
      sourceEntityId: messageId,
      entryLabel: "uma resposta no chat",
      bodyText: memoryBodyText,
    });

    const readMarker = getThreadReadMarker(detail);
    await markThreadAsRead({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      userId: profile.user.id,
      lastReadMessageId: readMarker.lastReadMessageId,
      lastReadAt: readMarker.lastReadAt,
    });

    const realtimeEvents = await buildRealtimeEventsForParticipants({
      eventType: "message.created",
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      participantUserIds: detailBefore.thread.participants.map((participant) => participant.userId),
    });

    publishRealtimeEventsToUsers({
      teamId: membership.teamId,
      eventsByUserId: realtimeEvents,
    });

    return detail;
  });

  app.post("/threads/:threadId/attachments", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { threadId } = threadParamsSchema.parse(request.params);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const multipartRequest = request as typeof request & {
      file: () => Promise<MultipartFile | undefined>;
    };
    const file = await multipartRequest.file();

    if (!file) {
      throw reply.badRequest("Selecione um arquivo para enviar.");
    }

    const payload = sendAttachmentSchema.parse({
      teamId: getMultipartFieldValue(file.fields, "teamId"),
      repliedMessageId: getMultipartFieldValue(file.fields, "repliedMessageId") || null,
      bodyText: getMultipartFieldValue(file.fields, "bodyText") || null,
    });

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: payload.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    const detailBefore = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detailBefore) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    if (detailBefore.thread.status === "closed") {
      throw reply.badRequest("Esta conversa já foi encerrada.");
    }

    const replyTarget = await getReplyTargetMessage({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      messageId: payload.repliedMessageId,
    });

    if (payload.repliedMessageId && !replyTarget) {
      throw reply.badRequest("A mensagem escolhida para responder não foi encontrada.");
    }

    const buffer = await file.toBuffer();

    if (!buffer.length) {
      throw reply.badRequest("O arquivo enviado está vazio.");
    }

    const normalizedMimeType =
      String(file.mimetype || "application/octet-stream").trim() || "application/octet-stream";
    const normalizedFileName = String(file.filename || "").trim() || `arquivo_${Date.now()}`;
    const attachmentKind = getTeamMessageAttachmentKind(normalizedMimeType);
    const attachmentLabel = buildTeamMessageAttachmentLabel({
      kind: attachmentKind,
      fileName: normalizedFileName,
    });
    const messageBodyText = payload.bodyText?.trim() || attachmentLabel;
    const repositoryItemTypeCode = getRepositoryItemTypeCodeForAttachment(normalizedMimeType);
    const stored = await storeTeamMessageAttachmentBinary({
      lawFirmId: profile.lawFirm.id,
      fileName: normalizedFileName,
      mimeType: normalizedMimeType,
      bytes: buffer,
    });
    const repositoryItemId = createId();
    const fileId = createId();
    const messageId = createId();

    await prisma.$transaction(async (tx) => {
      await tx.$executeRaw`
        INSERT INTO repository_items (
          id, law_firm_id, client_id, case_id, item_type_code, channel_code, source_entity_type,
          source_entity_id, direction_code, subject, body_text, summary_text, metadata_json,
          authored_by_user_id, created_by_user_id, external_reference, occurred_at, created_at, updated_at
        ) VALUES (
          ${repositoryItemId},
          ${profile.lawFirm.id},
          NULL,
          NULL,
          ${repositoryItemTypeCode},
          'internal',
          'team_message_thread',
          ${threadId},
          'internal',
          ${normalizedFileName},
          ${attachmentLabel},
          'Anexo compartilhado em uma conversa interna do team.',
          ${JSON.stringify({
            teamId: membership.teamId,
            threadId,
            mimeType: normalizedMimeType,
            sizeBytes: buffer.length,
            attachmentKind,
          })},
          ${profile.user.id},
          ${profile.user.id},
          NULL,
          NOW(),
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        INSERT INTO files (
          id, law_firm_id, client_id, case_id, repository_item_id, storage_provider, storage_bucket,
          object_key, storage_region, original_file_name, stored_file_name, mime_type, size_bytes,
          checksum_sha256, is_encrypted, uploaded_by_user_id, uploaded_at, created_at
        ) VALUES (
          ${fileId},
          ${profile.lawFirm.id},
          NULL,
          NULL,
          ${repositoryItemId},
          ${stored.storageProvider},
          ${stored.storageBucket},
          ${stored.objectKey},
          ${stored.storageRegion},
          ${normalizedFileName},
          ${stored.storedFileName},
          ${normalizedMimeType},
          ${buffer.length},
          ${stored.checksumSha256},
          0,
          ${profile.user.id},
          NOW(),
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        INSERT INTO team_messages (
          id, law_firm_id, team_id, thread_id, sender_user_id, body_text, replied_message_id, attachment_file_id, created_at
        ) VALUES (
          ${messageId},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${profile.user.id},
          ${messageBodyText},
          ${replyTarget?.messageId ?? null},
          ${fileId},
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        UPDATE team_message_threads
        SET
          last_message_at = NOW(),
          updated_at = CURRENT_TIMESTAMP
        WHERE id = ${threadId}
          AND law_firm_id = ${profile.lawFirm.id}
          AND team_id = ${membership.teamId}
      `;
    });

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "team_message",
      entityId: messageId,
      action: "team.message.attachment.create",
      afterJson: {
        teamId: membership.teamId,
        threadId,
        fileId,
        repositoryItemId,
        mimeType: normalizedMimeType,
        sizeBytes: buffer.length,
        repliedMessageId: replyTarget?.messageId ?? null,
        bodyText: messageBodyText,
      },
      request,
    });

    const detail = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detail) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    const attachmentMemoryText = replyTarget
      ? `Em resposta a ${replyTarget.senderDisplayName}: ${replyTarget.bodyText} | ${messageBodyText}`
      : messageBodyText;

    await appendChatContributionMemory({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      teamName: membership.teamName,
      senderUserId: profile.user.id,
      senderDisplayName: profile.user.displayName,
      threadId,
      subject: detail.thread.subject,
      topicType: detail.thread.topicType,
      relatedCase: detail.thread.relatedCase,
      reviewDueAt: detail.thread.reviewDueAt,
      participants: detail.thread.participants.map((participant) => ({
        userId: participant.userId,
        displayName: participant.displayName,
      })),
      sourceType: getChatMemorySourceType({
        topicType: detail.thread.topicType,
        contributionType: "attachment",
      }),
      sourceEntityId: messageId,
      entryLabel: "um arquivo na conversa",
      bodyText: attachmentMemoryText,
    });

    const readMarker = getThreadReadMarker(detail);
    await markThreadAsRead({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      userId: profile.user.id,
      lastReadMessageId: readMarker.lastReadMessageId,
      lastReadAt: readMarker.lastReadAt,
    });

    const realtimeEvents = await buildRealtimeEventsForParticipants({
      eventType: "message.created",
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      participantUserIds: detailBefore.thread.participants.map((participant) => participant.userId),
    });

    publishRealtimeEventsToUsers({
      teamId: membership.teamId,
      eventsByUserId: realtimeEvents,
    });

    return detail;
  });

  app.post("/threads/:threadId/participants", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { threadId } = threadParamsSchema.parse(request.params);
    const payload = addThreadParticipantSchema.parse(request.body);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: payload.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    const detailBefore = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detailBefore) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    if (detailBefore.thread.status === "closed") {
      throw reply.badRequest("Esta conversa já foi encerrada.");
    }

    if (detailBefore.thread.participants.some((participant) => participant.userId === payload.userId)) {
      throw reply.badRequest("Essa pessoa já participa da conversa.");
    }

    const participant = await getTeamMember({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: payload.userId,
    });

    if (!participant) {
      throw reply.badRequest("A pessoa escolhida não faz parte do team atual.");
    }

    const systemMessageId = createId();
    const systemMessageBody = `${profile.user.displayName} adicionou ${participant.displayName} à conversa.`;
    const participantReadMarker = getThreadReadMarker(detailBefore);
    const participantLastReadAtValue = participantReadMarker.lastReadMessageId
      ? Prisma.sql`(
          SELECT tm.created_at
          FROM team_messages tm
          WHERE tm.id = ${participantReadMarker.lastReadMessageId}
            AND tm.law_firm_id = ${profile.lawFirm.id}
            AND tm.team_id = ${membership.teamId}
            AND tm.thread_id = ${threadId}
          LIMIT 1
        )`
      : Prisma.sql`NULL`;

    await prisma.$transaction(async (tx) => {
      await tx.$executeRaw`
        INSERT INTO team_message_thread_participants (
          id, law_firm_id, team_id, thread_id, user_id, added_by_user_id, joined_at
        ) VALUES (
          ${createId()},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${participant.userId},
          ${profile.user.id},
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw(Prisma.sql`
        INSERT INTO team_message_thread_reads (
          id, law_firm_id, team_id, thread_id, user_id, last_read_message_id, last_read_at, created_at, updated_at
        ) VALUES (
          ${createId()},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${participant.userId},
          ${participantReadMarker.lastReadMessageId},
          ${participantLastReadAtValue},
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `);

      await tx.$executeRaw`
        INSERT INTO team_messages (
          id, law_firm_id, team_id, thread_id, sender_user_id, body_text, created_at
        ) VALUES (
          ${systemMessageId},
          ${profile.lawFirm.id},
          ${membership.teamId},
          ${threadId},
          ${profile.user.id},
          ${systemMessageBody},
          CURRENT_TIMESTAMP
        )
      `;

      await tx.$executeRaw`
        UPDATE team_message_threads
        SET
          last_message_at = NOW(),
          updated_at = CURRENT_TIMESTAMP
        WHERE id = ${threadId}
          AND law_firm_id = ${profile.lawFirm.id}
          AND team_id = ${membership.teamId}
      `;
    });

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "team_message_thread",
      entityId: threadId,
      action: "team.message_thread.add_participant",
      afterJson: {
        teamId: membership.teamId,
        addedUserId: participant.userId,
      },
      request,
    });

    const participantUserIds = await listThreadParticipantUserIds({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
    });
    const detail = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detail) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    const readMarker = getThreadReadMarker(detail);
    await markThreadAsRead({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      userId: profile.user.id,
      lastReadMessageId: readMarker.lastReadMessageId,
      lastReadAt: readMarker.lastReadAt,
    });

    const realtimeEvents = await buildRealtimeEventsForParticipants({
      eventType: "participant.added",
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      participantUserIds,
    });

    publishRealtimeEventsToUsers({
      teamId: membership.teamId,
      eventsByUserId: realtimeEvents,
    });

    return detail;
  });

  app.post("/threads/:threadId/close", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { threadId } = threadParamsSchema.parse(request.params);
    const payload = closeThreadSchema.parse(request.body);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const membership = await getTeamMembership({
      lawFirmId: profile.lawFirm.id,
      userId: profile.user.id,
      teamId: payload.teamId,
    });

    if (!membership) {
      throw reply.notFound("Você não faz parte de um team neste workspace.");
    }

    const detailBefore = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detailBefore) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    if (detailBefore.thread.status === "closed") {
      return detailBefore;
    }

    await prisma.$executeRaw`
      UPDATE team_message_threads
      SET
        status_code = 'closed',
        closed_by_user_id = ${profile.user.id},
        closed_at = NOW(),
        updated_at = CURRENT_TIMESTAMP
      WHERE id = ${threadId}
        AND law_firm_id = ${profile.lawFirm.id}
        AND team_id = ${membership.teamId}
    `;

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "team_message_thread",
      entityId: threadId,
      action: "team.message_thread.close",
      afterJson: {
        teamId: membership.teamId,
      },
      request,
    });

    const detail = await getThreadDetail({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      userId: profile.user.id,
      threadId,
    });

    if (!detail) {
      throw reply.notFound("Conversa do team não encontrada.");
    }

    const readMarker = getThreadReadMarker(detail);
    await markThreadAsRead({
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      userId: profile.user.id,
      lastReadMessageId: readMarker.lastReadMessageId,
      lastReadAt: readMarker.lastReadAt,
    });

    const realtimeEvents = await buildRealtimeEventsForParticipants({
      eventType: "thread.closed",
      lawFirmId: profile.lawFirm.id,
      teamId: membership.teamId,
      threadId,
      participantUserIds: detailBefore.thread.participants.map((participant) => participant.userId),
    });

    publishRealtimeEventsToUsers({
      teamId: membership.teamId,
      eventsByUserId: realtimeEvents,
    });

    return detail;
  });
}