import { dirname, resolve } from "node:path";
import { fileURLToPath } from "node:url";
import { config as loadDotenv } from "dotenv";
import { Prisma } from "@prisma/client";
import { createId } from "../lib/id.js";
import { prisma } from "../lib/prisma.js";

const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);
loadDotenv({ path: resolve(__dirname, "../../../../.env") });

type Participant = {
  userId: string;
  displayName: string;
};

type MessageRow = {
  message_id: string;
  law_firm_id: string;
  team_id: string;
  team_name: string;
  thread_id: string;
  subject: string;
  topic_type: "general" | "legal_doubt" | "document_review";
  review_due_at: Date | null;
  case_number: string | null;
  case_title: string | null;
  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_file_name: string | null;
  created_at: Date;
  is_opening: number;
};

type CommentRow = {
  comment_id: string;
  law_firm_id: string;
  team_id: string;
  team_name: string;
  thread_id: string;
  subject: string;
  topic_type: "general" | "legal_doubt" | "document_review";
  review_due_at: Date | null;
  case_number: string | null;
  case_title: string | null;
  created_by_user_id: string;
  created_by_display_name: string | null;
  created_by_first_name: string | null;
  created_by_last_name: string | null;
  created_by_email: string;
  page_number: number | null;
  comment_text: string;
  created_at: Date;
};

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 = [row.first_name, row.last_name]
    .map((value) => String(value ?? "").trim())
    .filter(Boolean)
    .join(" ")
    .trim();

  if (fullName) {
    return fullName;
  }

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

function truncateInsight(value: string, limit = 220) {
  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 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 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 getChatMemorySourceType(topicType: "general" | "legal_doubt" | "document_review") {
  switch (topicType) {
    case "legal_doubt":
      return "team_message_legal_doubt";
    case "document_review":
      return "team_message_document_review";
    default:
      return "team_message_general_chat";
  }
}

function buildChatMemoryText(input: {
  teamName: string;
  topicType: "general" | "legal_doubt" | "document_review";
  subject: string;
  relatedCase?: {
    caseNumber: string;
    title: string;
  } | null;
  reviewDueAt?: Date | null;
  senderDisplayName: string;
  participants: Participant[];
  senderUserId: string;
  entryLabel: string;
  bodyText: string;
}) {
  const otherParticipantNames = input.participants
    .filter((participant) => participant.userId !== input.senderUserId)
    .map((participant) => participant.displayName);

  return [
    `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}: ${input.bodyText}`,
  ]
    .filter(Boolean)
    .join(" ");
}

async function main() {
  const agentRows = await prisma.$queryRaw<
    Array<{
      id: string;
      law_firm_id: string;
      team_id: string;
      user_id: string;
    }>
  >`
    SELECT id, law_firm_id, team_id, user_id
    FROM team_agents
  `;

  const agentIdByTeamUserKey = new Map<string, string>();
  for (const row of agentRows) {
    agentIdByTeamUserKey.set(`${row.team_id}:${row.user_id}`, row.id);
  }

  const participantRows = await prisma.$queryRaw<
    Array<{
      thread_id: string;
      user_id: string;
      display_name: string | null;
      first_name: string | null;
      last_name: string | null;
      email: string;
    }>
  >`
    SELECT
      tmtp.thread_id,
      tmtp.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
    ORDER BY tmtp.thread_id ASC, tmtp.joined_at ASC
  `;

  const participantsByThreadId = new Map<string, Participant[]>();
  for (const row of participantRows) {
    const participants = participantsByThreadId.get(row.thread_id) ?? [];
    participants.push({
      userId: row.user_id,
      displayName: getUserDisplayName(row),
    });
    participantsByThreadId.set(row.thread_id, participants);
  }

  const messageRows = await prisma.$queryRaw<MessageRow[]>`
    SELECT
      tm.id AS message_id,
      tm.law_firm_id,
      tm.team_id,
      t.name AS team_name,
      tm.thread_id,
      th.subject,
      th.topic_type,
      th.review_due_at,
      c.case_number,
      c.title AS case_title,
      tm.sender_user_id,
      su.display_name AS sender_display_name,
      su.first_name AS sender_first_name,
      su.last_name AS sender_last_name,
      su.email AS sender_email,
      tm.body_text,
      tm.replied_message_id,
      rm.body_text AS replied_body_text,
      ru.display_name AS replied_sender_display_name,
      ru.first_name AS replied_sender_first_name,
      ru.last_name AS replied_sender_last_name,
      ru.email AS replied_sender_email,
      tm.attachment_file_id,
      f.original_file_name AS attachment_file_name,
      tm.created_at,
      CASE
        WHEN tm.id = (
          SELECT tm2.id
          FROM team_messages tm2
          WHERE tm2.thread_id = tm.thread_id
          ORDER BY tm2.created_at ASC, tm2.id ASC
          LIMIT 1
        ) THEN 1
        ELSE 0
      END AS is_opening
    FROM team_messages tm
    INNER JOIN team_message_threads th ON th.id = tm.thread_id
    INNER JOIN teams t ON t.id = tm.team_id
    INNER JOIN users su ON su.id = tm.sender_user_id
    LEFT JOIN cases c ON c.id = th.case_id
    LEFT JOIN team_messages rm ON rm.id = tm.replied_message_id
    LEFT JOIN users ru ON ru.id = rm.sender_user_id
    LEFT JOIN files f ON f.id = tm.attachment_file_id
    ORDER BY tm.created_at ASC, tm.id ASC
  `;

  const commentRows = await prisma.$queryRaw<CommentRow[]>`
    SELECT
      comment.id AS comment_id,
      comment.law_firm_id,
      comment.team_id,
      t.name AS team_name,
      comment.thread_id,
      th.subject,
      th.topic_type,
      th.review_due_at,
      c.case_number,
      c.title AS case_title,
      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.page_number,
      comment.comment_text,
      comment.created_at
    FROM team_message_attachment_comments comment
    INNER JOIN team_message_threads th ON th.id = comment.thread_id
    INNER JOIN teams t ON t.id = comment.team_id
    INNER JOIN users u ON u.id = comment.created_by_user_id
    LEFT JOIN cases c ON c.id = th.case_id
    ORDER BY comment.created_at ASC, comment.id ASC
  `;

  const chatSourceTypes = [
    "team_message_general_chat",
    "team_message_legal_doubt",
    "team_message_document_review",
    "team_message_document_review_comment",
  ] as const;

  await prisma.$executeRawUnsafe(
    `DELETE FROM team_agent_memories WHERE source_type IN (${chatSourceTypes.map(() => "?").join(", ")})`,
    ...chatSourceTypes,
  );

  let insertedCount = 0;

  for (const row of messageRows) {
    const agentId = agentIdByTeamUserKey.get(`${row.team_id}:${row.sender_user_id}`);
    if (!agentId) {
      continue;
    }

    const senderDisplayName = getUserDisplayName({
      display_name: row.sender_display_name,
      first_name: row.sender_first_name,
      last_name: row.sender_last_name,
      email: row.sender_email,
    });
    const participants = participantsByThreadId.get(row.thread_id) ?? [];
    const relatedCase =
      row.case_number && row.case_title
        ? {
            caseNumber: row.case_number,
            title: row.case_title,
          }
        : null;
    const repliedSenderDisplayName = row.replied_sender_email
      ? 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,
        })
      : null;

    let entryLabel = "uma resposta no chat";
    let bodyText = row.body_text;

    if (row.is_opening) {
      if (row.topic_type === "document_review" && row.attachment_file_name) {
        entryLabel = "a abertura da revisão com documento anexado";
        bodyText = `${row.body_text} Documento enviado: ${row.attachment_file_name}.`;
      } else {
        entryLabel = "a mensagem inicial da conversa";
      }
    } else if (row.attachment_file_id) {
      entryLabel = "um arquivo na conversa";
    }

    if (row.replied_message_id && repliedSenderDisplayName && row.replied_body_text) {
      bodyText = `Em resposta a ${repliedSenderDisplayName}: ${row.replied_body_text} | ${bodyText}`;
    }

    const memoryText = buildChatMemoryText({
      teamName: row.team_name,
      topicType: row.topic_type,
      subject: row.subject,
      relatedCase,
      reviewDueAt: row.review_due_at,
      senderDisplayName,
      participants,
      senderUserId: row.sender_user_id,
      entryLabel,
      bodyText,
    });

    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()},
        ${row.law_firm_id},
        ${row.team_id},
        ${agentId},
        ${getChatMemorySourceType(row.topic_type)},
        ${row.message_id},
        ${memoryText},
        ${row.sender_user_id},
        ${row.created_at}
      )
    `;
    insertedCount += 1;
  }

  for (const row of commentRows) {
    const agentId = agentIdByTeamUserKey.get(`${row.team_id}:${row.created_by_user_id}`);
    if (!agentId) {
      continue;
    }

    const participants = participantsByThreadId.get(row.thread_id) ?? [];
    const senderDisplayName = 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,
    });
    const relatedCase =
      row.case_number && row.case_title
        ? {
            caseNumber: row.case_number,
            title: row.case_title,
          }
        : null;
    const memoryText = buildChatMemoryText({
      teamName: row.team_name,
      topicType: row.topic_type,
      subject: row.subject,
      relatedCase,
      reviewDueAt: row.review_due_at,
      senderDisplayName,
      participants,
      senderUserId: row.created_by_user_id,
      entryLabel: "uma anotação no PDF",
      bodyText: `Página ${row.page_number ?? "sem página definida"}: ${row.comment_text}`,
    });

    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()},
        ${row.law_firm_id},
        ${row.team_id},
        ${agentId},
        ${"team_message_document_review_comment"},
        ${row.comment_id},
        ${memoryText},
        ${row.created_by_user_id},
        ${row.created_at}
      )
    `;
    insertedCount += 1;
  }

  for (const agent of agentRows) {
    const memoryRows = await prisma.$queryRaw<Array<{ memory_text: string }>>`
      SELECT memory_text
      FROM team_agent_memories
      WHERE law_firm_id = ${agent.law_firm_id}
        AND team_id = ${agent.team_id}
        AND agent_id = ${agent.id}
      ORDER BY created_at DESC
      LIMIT 6
    `;

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

    await prisma.$executeRaw`
      UPDATE team_agents
      SET
        learning_summary = ${learningSummary},
        memory_count = (
          SELECT COUNT(*)
          FROM team_agent_memories
          WHERE law_firm_id = ${agent.law_firm_id}
            AND team_id = ${agent.team_id}
            AND agent_id = ${agent.id}
        ),
        last_learning_at = (
          SELECT MAX(created_at)
          FROM team_agent_memories
          WHERE law_firm_id = ${agent.law_firm_id}
            AND team_id = ${agent.team_id}
            AND agent_id = ${agent.id}
        ),
        updated_at = CURRENT_TIMESTAMP
      WHERE id = ${agent.id}
    `;
  }

  console.log(`Backfill concluído. ${insertedCount} memória(s) de chat reconstruídas.`);
}

main()
  .catch((error) => {
    console.error(error);
    process.exitCode = 1;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });