import { createHmac, timingSafeEqual } from "node:crypto";
import type { FastifyRequest } from "fastify";
import { env } from "../env.js";
import { writeAuditLog } from "./audit.js";
import { decryptSecret, encryptSecret } from "./crypto.js";
import { createId } from "./id.js";
import {
  createConversation,
  storeConversationMessage,
  updateConversationMessageStatus,
} from "./repository.js";
import { prisma } from "./prisma.js";

const KOMMO_LEAD_REF_PREFIX = "kommo:lead:";
const KOMMO_PROVIDER = "kommo";
const KOMMO_SALESBOT_PROVIDER = "kommo_salesbot";

type LawFirmSummary = {
  id: string;
  slug: string;
  name: string;
};

type KommoWorkspaceSettingsRecord = {
  id: string;
  lawFirmId: string;
  subdomain: string | null;
  accessToken: string | null;
  accessTokenLast4: string | null;
  webhookSecret: string | null;
  webhookSecretLast4: string | null;
  integrationSecretKey: string | null;
  integrationSecretKeyLast4: string | null;
  isActive: boolean;
  createdAt: Date;
  updatedAt: Date;
};

type KommoLeadLike = {
  id: string;
  name?: string | null;
  custom_fields?: unknown;
  custom_fields_values?: unknown;
  _embedded?: {
    contacts?: Array<{
      id?: string | number | null;
      name?: string | null;
    }>;
  } | null;
};

type KommoContactLike = {
  id?: string | null;
  name?: string | null;
  custom_fields?: unknown;
  custom_fields_values?: unknown;
};

type KommoLeadSyncResult = {
  leadRecordId: string;
  leadId: string;
  leadName: string;
  clientId: string | null;
  caseId: string | null;
  conversationId: string | null;
  isLinked: boolean;
};

type KommoConversationBinding = {
  id: string;
  clientId: string;
  caseId: string | null;
  subject: string | null;
  participantsJson: Record<string, unknown>;
  externalThreadId: string | null;
};

type KommoLeadRow = {
  id: string;
  law_firm_id: string;
  kommo_lead_id: string;
  kommo_contact_id: string | null;
  linked_client_id: string | null;
  linked_case_id: string | null;
  conversation_id: string | null;
  display_name: string;
  email: string | null;
  phone: string | null;
  status_code: string;
  source_code: string | null;
  external_thread_id: string | null;
  salesbot_return_url: string | null;
  last_message_preview: string | null;
  last_message_at: Date | null;
  unread_message_count: number;
  metadata_json: unknown;
  created_at: Date;
  updated_at: Date;
  linked_at: Date | null;
};

type KommoLeadRecord = {
  id: string;
  lawFirmId: string;
  leadId: string;
  contactId: string | null;
  linkedClientId: string | null;
  linkedCaseId: string | null;
  conversationId: string | null;
  displayName: string;
  email: string | null;
  phone: string | null;
  statusCode: string;
  sourceCode: string | null;
  externalThreadId: string | null;
  salesbotReturnUrl: string | null;
  lastMessagePreview: string | null;
  lastMessageAt: Date | null;
  unreadMessageCount: number;
  metadataJson: Record<string, unknown>;
  createdAt: Date;
  updatedAt: Date;
  linkedAt: Date | null;
};

type KommoLeadMessageRow = {
  id: string;
  kommo_lead_record_id: string;
  conversation_id: string | null;
  repository_item_id: string | null;
  external_message_id: string | null;
  direction_code: string;
  body_text: string;
  sender_name: string | null;
  sender_address: string | null;
  recipient_address: string | null;
  message_status: string | null;
  occurred_at: Date;
  raw_payload_json: unknown;
  created_at: Date;
};

type KommoOutboundDeliveryRow = {
  id: string;
  law_firm_id: string;
  kommo_lead_record_id: string | null;
  conversation_id: string;
  repository_item_id: string | null;
  delivery_channel: string;
  return_url: string | null;
  payload_json: unknown;
  status_code: string;
  attempt_count: number;
  max_attempts: number;
  next_retry_at: Date | null;
  last_attempt_at: Date | null;
  delivered_at: Date | null;
  last_error_code: string | null;
  last_error_message: string | null;
  created_at: Date;
  updated_at: Date;
};

type KommoOutboundDeliveryRecord = {
  id: string;
  lawFirmId: string;
  leadRecordId: string | null;
  conversationId: string;
  repositoryItemId: string | null;
  deliveryChannel: string;
  returnUrl: string | null;
  payloadJson: Record<string, unknown>;
  statusCode: string;
  attemptCount: number;
  maxAttempts: number;
  nextRetryAt: Date | null;
  lastAttemptAt: Date | null;
  deliveredAt: Date | null;
  lastErrorCode: string | null;
  lastErrorMessage: string | null;
  createdAt: Date;
  updatedAt: Date;
};

type KommoDeliveryAuditContext = {
  officeId?: string | null;
  actorUserId?: string | null;
  request?: FastifyRequest;
};

type KommoConversationIssueSummary = {
  conversationId: string;
  clientId: string;
  clientName: string;
  leadRecordId: string | null;
  leadDisplayName: string | null;
  stateCode: "retry_scheduled" | "failed";
  retryScheduledCount: number;
  failedDeliveryCount: number;
  nextRetryAt: Date | null;
  lastAttemptAt: Date | null;
  lastErrorMessage: string | null;
};

type KommoLeadSummary = {
  id: string;
  leadId: string;
  displayName: string;
  email: string | null;
  phone: string | null;
  statusCode: string;
  linkedClientId: string | null;
  linkedClientName: string | null;
  conversationId: string | null;
  lastMessagePreview: string | null;
  lastMessageAt: Date | null;
  unreadMessageCount: number;
  createdAt: Date;
};

type KommoLeadListResponse = {
  _embedded?: {
    leads?: KommoLeadLike[] | null;
  } | null;
  _links?: {
    next?: {
      href?: string | null;
    } | null;
  } | null;
};

type KommoLeadStats = {
  totalCount: number;
  linkedCount: number;
  unlinkedCount: number;
  conversationCount: number;
  mirroredMessageCount: number;
};

type KommoLeadImportResult = {
  fetchedCount: number;
  syncedCount: number;
  createdCount: number;
  updatedCount: number;
  linkedCount: number;
  unlinkedCount: number;
  pagesProcessed: number;
};

export type KommoApiLeadProbeResult = {
  configured: boolean;
  checkedAt: Date;
  requestPath: string | null;
  reachable: boolean;
  sampleLeadCount: number;
  nextPageAvailable: boolean;
  sampleLeadIds: string[];
  sampleLeadNames: string[];
  errorMessage: string | null;
};

function normalizeOptionalString(value: unknown) {
  const normalized = String(value ?? "").trim();
  return normalized ? normalized : null;
}

function sleep(milliseconds: number) {
  return new Promise((resolve) => {
    setTimeout(resolve, milliseconds);
  });
}

function getKommoReturnUrlMaxAttempts() {
  return Math.max(1, Math.min(10, env.KOMMO_RETURN_URL_MAX_ATTEMPTS));
}

function getKommoReturnUrlImmediateAttempts() {
  return Math.max(
    1,
    Math.min(getKommoReturnUrlMaxAttempts(), env.KOMMO_RETURN_URL_IMMEDIATE_ATTEMPTS),
  );
}

function getKommoReturnUrlTimeoutMs() {
  return Math.max(1000, env.KOMMO_RETURN_URL_TIMEOUT_MS);
}

function getKommoImmediateRetryDelayMs(attemptNumber: number) {
  return attemptNumber <= 1 ? 300 : 900;
}

function getKommoDeferredRetryDelayMs(attemptNumber: number) {
  if (attemptNumber <= 1) {
    return 2 * 60_000;
  }

  if (attemptNumber === 2) {
    return 10 * 60_000;
  }

  if (attemptNumber === 3) {
    return 30 * 60_000;
  }

  return 60 * 60_000;
}

class KommoReturnUrlError extends Error {
  readonly errorCode: string;
  readonly statusCode: number | null;
  readonly responseBody: string | null;
  readonly retriable: boolean;

  constructor(input: {
    message: string;
    errorCode: string;
    retriable: boolean;
    statusCode?: number | null;
    responseBody?: string | null;
  }) {
    super(input.message);
    this.name = "KommoReturnUrlError";
    this.errorCode = input.errorCode;
    this.retriable = input.retriable;
    this.statusCode = input.statusCode ?? null;
    this.responseBody = input.responseBody ?? null;
  }
}

function normalizeKommoReturnUrlError(error: unknown) {
  if (error instanceof KommoReturnUrlError) {
    return error;
  }

  const message = error instanceof Error ? error.message : String(error ?? "Unknown Kommo error");
  return new KommoReturnUrlError({
    message,
    errorCode: "unexpected_error",
    retriable: true,
  });
}

function decodeBase64Url(value: string) {
  const normalized = value.replace(/-/g, "+").replace(/_/g, "/");
  const padded = normalized.padEnd(normalized.length + ((4 - (normalized.length % 4)) % 4), "=");
  return Buffer.from(padded, "base64");
}

function encodeBase64Url(value: Buffer) {
  return value
    .toString("base64")
    .replace(/\+/g, "-")
    .replace(/\//g, "_")
    .replace(/=+$/g, "");
}

function normalizePhone(value: string | null) {
  if (!value) {
    return null;
  }

  const digits = value.replace(/[^\d+]/g, "").trim();
  return digits || value;
}

function normalizeKeySegment(segment: string) {
  return segment.replace(/\]/g, "").trim();
}

function parseBracketKey(key: string) {
  return key
    .split("[")
    .map(normalizeKeySegment)
    .filter(Boolean);
}

function assignNestedValue(target: Record<string, unknown>, segments: string[], value: string) {
  if (!segments.length) {
    return;
  }

  let cursor: Record<string, unknown> = target;

  for (const segment of segments.slice(0, -1)) {
    const current = cursor[segment];

    if (!current || typeof current !== "object" || Array.isArray(current)) {
      cursor[segment] = {};
    }

    cursor = cursor[segment] as Record<string, unknown>;
  }

  const finalSegment = segments.at(-1);

  if (!finalSegment) {
    return;
  }

  const existing = cursor[finalSegment];

  if (existing === undefined) {
    cursor[finalSegment] = value;
    return;
  }

  if (Array.isArray(existing)) {
    existing.push(value);
    return;
  }

  cursor[finalSegment] = [existing, value];
}

export function parseKommoFormBody(rawBody: string) {
  const result: Record<string, unknown> = {};

  for (const pair of rawBody.split("&")) {
    if (!pair) {
      continue;
    }

    const separatorIndex = pair.indexOf("=");
    const keyPart = separatorIndex >= 0 ? pair.slice(0, separatorIndex) : pair;
    const valuePart = separatorIndex >= 0 ? pair.slice(separatorIndex + 1) : "";
    const key = decodeURIComponent(keyPart.replace(/\+/g, " "));
    const value = decodeURIComponent(valuePart.replace(/\+/g, " "));
    const segments = parseBracketKey(key);

    assignNestedValue(result, segments, value);
  }

  return result;
}

function toArray<T>(value: unknown) {
  if (Array.isArray(value)) {
    return value as T[];
  }

  if (value && typeof value === "object") {
    return Object.keys(value as Record<string, unknown>)
      .sort((left, right) => Number(left) - Number(right))
      .map((key) => (value as Record<string, unknown>)[key] as T);
  }

  if (value === undefined || value === null) {
    return [];
  }

  return [value as T];
}

function splitDisplayName(name: string | null) {
  const normalized = normalizeOptionalString(name) ?? "Lead Kommo";
  const parts = normalized.split(/\s+/).filter(Boolean);
  const firstName = parts[0] ?? "Lead";
  const lastName = parts.slice(1).join(" ") || "Kommo";

  return {
    firstName,
    lastName,
    fullName: normalized,
  };
}

function formatClientNumber(sequence: number) {
  return `CL-${String(sequence).padStart(6, "0")}`;
}

function formatCaseNumber(sequence: number) {
  return `CASE-${String(sequence).padStart(6, "0")}`;
}

function getKommoLeadExternalReference(leadId: string) {
  return `${KOMMO_LEAD_REF_PREFIX}${leadId}`;
}

function extractKommoCustomFieldValues(source: unknown, codes: string[]) {
  const wantedCodes = new Set(codes.map((code) => code.toUpperCase()));
  const fields = toArray<Record<string, unknown>>(source);
  const values: string[] = [];

  for (const field of fields) {
    const code = String(field.code ?? field.name ?? "").toUpperCase();

    if (!wantedCodes.has(code)) {
      continue;
    }

    const rawValues = toArray<Record<string, unknown>>(field.values);

    for (const item of rawValues) {
      const value = normalizeOptionalString(item.value);

      if (value) {
        values.push(value);
      }
    }
  }

  return values;
}

function extractKommoLeadContactData(input: {
  lead: KommoLeadLike;
  contact?: KommoContactLike | null;
}) {
  const contact = input.contact ?? null;
  const leadFieldSource = input.lead.custom_fields_values ?? input.lead.custom_fields ?? null;
  const contactFieldSource = contact?.custom_fields_values ?? contact?.custom_fields ?? null;
  const phone =
    normalizePhone(
      extractKommoCustomFieldValues(contactFieldSource, ["PHONE", "Phone"])[0] ??
        extractKommoCustomFieldValues(leadFieldSource, ["PHONE", "Phone"])[0] ??
        null,
    ) ?? null;
  const email =
    normalizeOptionalString(
      extractKommoCustomFieldValues(contactFieldSource, ["EMAIL", "Email"])[0] ??
        extractKommoCustomFieldValues(leadFieldSource, ["EMAIL", "Email"])[0] ??
        null,
    ) ?? null;
  const leadName = normalizeOptionalString(input.lead.name);
  const displayName =
    normalizeOptionalString(contact?.name) ??
    (!isGenericKommoLeadDisplayName(leadName) ? leadName : null) ??
    leadName ??
    "Lead Kommo";

  return {
    displayName,
    email,
    phone,
  };
}

function isGenericKommoLeadDisplayName(value: string | null | undefined) {
  const normalized = String(value ?? "")
    .trim()
    .toLowerCase();

  if (!normalized) {
    return true;
  }

  return (
    /^lead\s*#?\s*\d+$/.test(normalized) ||
    /^lead\s+kommo\s+\d+$/.test(normalized) ||
    /^\d+$/.test(normalized)
  );
}

async function resolveKommoLawFirm(workspaceKey: string) {
  const normalizedKey = workspaceKey.trim();

  if (!normalizedKey) {
    return null;
  }

  const rows = await prisma.$queryRaw<LawFirmSummary[]>`
    SELECT id, slug, name
    FROM law_firms
    WHERE deleted_at IS NULL
      AND (id = ${normalizedKey} OR slug = ${normalizedKey})
    LIMIT 1
  `;

  return rows[0] ?? null;
}

function normalizeKommoSubdomain(value: string | null | undefined) {
  const normalized = normalizeOptionalString(value)?.toLowerCase() ?? null;

  if (!normalized) {
    return null;
  }

  return normalized
    .replace(/^https?:\/\//i, "")
    .replace(/\.kommo\.com$/i, "")
    .replace(/\/+$/g, "");
}

function decryptNullableSecret(value: Uint8Array | null | undefined) {
  if (!value || value.length === 0) {
    return null;
  }

  return decryptSecret(Buffer.from(value));
}

function mapKommoWorkspaceSettingsRecord(
  record: {
    id: string;
    law_firm_id: string;
    subdomain: string | null;
    encrypted_access_token: Uint8Array | null;
    access_token_last4: string | null;
    encrypted_webhook_secret: Uint8Array | null;
    webhook_secret_last4: string | null;
    encrypted_integration_secret_key: Uint8Array | null;
    integration_secret_key_last4: string | null;
    is_active: boolean;
    created_at: Date;
    updated_at: Date;
  } | null,
) {
  if (!record) {
    return null;
  }

  return {
    id: record.id,
    lawFirmId: record.law_firm_id,
    subdomain: normalizeKommoSubdomain(record.subdomain),
    accessToken: decryptNullableSecret(record.encrypted_access_token),
    accessTokenLast4: normalizeOptionalString(record.access_token_last4),
    webhookSecret: decryptNullableSecret(record.encrypted_webhook_secret),
    webhookSecretLast4: normalizeOptionalString(record.webhook_secret_last4),
    integrationSecretKey: decryptNullableSecret(record.encrypted_integration_secret_key),
    integrationSecretKeyLast4: normalizeOptionalString(record.integration_secret_key_last4),
    isActive: Boolean(record.is_active),
    createdAt: record.created_at,
    updatedAt: record.updated_at,
  } satisfies KommoWorkspaceSettingsRecord;
}

let kommoWorkspaceSettingsSchemaPromise: Promise<void> | null = null;

export async function ensureKommoWorkspaceSettingsSchema() {
  if (kommoWorkspaceSettingsSchemaPromise) {
    return kommoWorkspaceSettingsSchemaPromise;
  }

  kommoWorkspaceSettingsSchemaPromise = (async () => {
    await prisma.$executeRaw`
      CREATE TABLE IF NOT EXISTS law_firm_kommo_settings (
        id CHAR(36) NOT NULL,
        law_firm_id CHAR(36) NOT NULL,
        subdomain VARCHAR(120) NULL,
        encrypted_access_token VARBINARY(4096) NULL,
        access_token_last4 CHAR(4) NULL,
        encrypted_webhook_secret VARBINARY(4096) NULL,
        webhook_secret_last4 CHAR(4) NULL,
        encrypted_integration_secret_key VARBINARY(4096) NULL,
        integration_secret_key_last4 CHAR(4) NULL,
        encryption_key_version VARCHAR(50) NOT NULL DEFAULT 'v1',
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_by_user_id CHAR(36) NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY uq_kommo_settings_law_firm (law_firm_id),
        KEY idx_kommo_settings_law_firm (law_firm_id, is_active),
        CONSTRAINT fk_kommo_settings_law_firm
          FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
        CONSTRAINT fk_kommo_settings_created_by
          FOREIGN KEY (created_by_user_id) REFERENCES users (id)
      )
      ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `;
  })().catch((error) => {
    kommoWorkspaceSettingsSchemaPromise = null;
    throw error;
  });

  return kommoWorkspaceSettingsSchemaPromise;
}

async function getKommoWorkspaceSettingsRecord(lawFirmId: string) {
  await ensureKommoWorkspaceSettingsSchema();

  const record = await prisma.lawFirmKommoSetting.findUnique({
    where: {
      law_firm_id: lawFirmId,
    },
  });

  return mapKommoWorkspaceSettingsRecord(record);
}

export async function getKommoWorkspaceSettings(lawFirmId: string) {
  return getKommoWorkspaceSettingsRecord(lawFirmId);
}

export async function saveKommoWorkspaceSettings(input: {
  lawFirmId: string;
  actorUserId: string;
  subdomain: string;
  accessToken?: string | null;
  webhookSecret?: string | null;
  integrationSecretKey?: string | null;
}) {
  await ensureKommoWorkspaceSettingsSchema();

  const existing = await prisma.lawFirmKommoSetting.findUnique({
    where: {
      law_firm_id: input.lawFirmId,
    },
  });
  const normalizedSubdomain = normalizeKommoSubdomain(input.subdomain);

  if (!normalizedSubdomain) {
    throw new Error("Kommo subdomain is required");
  }

  const normalizedAccessToken = normalizeOptionalString(input.accessToken);
  const normalizedWebhookSecret = normalizeOptionalString(input.webhookSecret);
  const normalizedIntegrationSecretKey = normalizeOptionalString(input.integrationSecretKey);

  if (!existing && !normalizedAccessToken) {
    throw new Error("Kommo access token is required on first save");
  }

  const saved = existing
    ? await prisma.lawFirmKommoSetting.update({
        where: {
          law_firm_id: input.lawFirmId,
        },
        data: {
          subdomain: normalizedSubdomain,
          encrypted_access_token:
            normalizedAccessToken != null ? encryptSecret(normalizedAccessToken) : undefined,
          access_token_last4:
            normalizedAccessToken != null ? normalizedAccessToken.slice(-4) : undefined,
          encrypted_webhook_secret:
            normalizedWebhookSecret != null ? encryptSecret(normalizedWebhookSecret) : undefined,
          webhook_secret_last4:
            normalizedWebhookSecret != null ? normalizedWebhookSecret.slice(-4) : undefined,
          encrypted_integration_secret_key:
            normalizedIntegrationSecretKey != null ?
            encryptSecret(normalizedIntegrationSecretKey) :
            undefined,
          integration_secret_key_last4:
            normalizedIntegrationSecretKey != null ?
            normalizedIntegrationSecretKey.slice(-4) :
            undefined,
          encryption_key_version: "v1",
          is_active: true,
        },
      })
    : await prisma.lawFirmKommoSetting.create({
        data: {
          id: createId(),
          law_firm_id: input.lawFirmId,
          subdomain: normalizedSubdomain,
          encrypted_access_token:
            normalizedAccessToken != null ? encryptSecret(normalizedAccessToken) : null,
          access_token_last4:
            normalizedAccessToken != null ? normalizedAccessToken.slice(-4) : null,
          encrypted_webhook_secret:
            normalizedWebhookSecret != null ? encryptSecret(normalizedWebhookSecret) : null,
          webhook_secret_last4:
            normalizedWebhookSecret != null ? normalizedWebhookSecret.slice(-4) : null,
          encrypted_integration_secret_key:
            normalizedIntegrationSecretKey != null ?
            encryptSecret(normalizedIntegrationSecretKey) :
            null,
          integration_secret_key_last4:
            normalizedIntegrationSecretKey != null ?
            normalizedIntegrationSecretKey.slice(-4) :
            null,
          encryption_key_version: "v1",
          is_active: true,
          created_by_user_id: input.actorUserId,
        },
      });

  return mapKommoWorkspaceSettingsRecord(saved);
}

function fixedLengthSecret(value: string) {
  return Buffer.from(value, "utf8");
}

export function verifyKommoWidgetRequestToken(input: {
  token: string;
  secretKey: string;
  now?: Date;
}) {
  const token = input.token.trim();
  const secretKey = input.secretKey.trim();

  if (!token || !secretKey) {
    throw new Error("Kommo Salesbot token or secret key is missing");
  }

  const parts = token.split(".");

  if (parts.length !== 3) {
    throw new Error("Kommo Salesbot token is malformed");
  }

  const [encodedHeader, encodedPayload, encodedSignature] = parts;
  const headerJson = decodeBase64Url(encodedHeader).toString("utf8");
  const payloadJson = decodeBase64Url(encodedPayload).toString("utf8");
  const header = JSON.parse(headerJson) as { alg?: string; typ?: string };
  const payload = JSON.parse(payloadJson) as {
    exp?: number;
    nbf?: number;
    iat?: number;
    account_id?: number;
    user_id?: number;
    client_uuid?: string;
    subdomain?: string;
    iss?: string;
  };

  if (header.alg !== "HS256") {
    throw new Error(`Unsupported Kommo Salesbot token algorithm: ${header.alg ?? "unknown"}`);
  }

  const dataToSign = `${encodedHeader}.${encodedPayload}`;
  const expectedSignature = encodeBase64Url(
    createHmac("sha256", secretKey).update(dataToSign).digest(),
  );
  const expectedBuffer = fixedLengthSecret(expectedSignature);
  const providedBuffer = fixedLengthSecret(encodedSignature);

  if (
    providedBuffer.length !== expectedBuffer.length ||
    !timingSafeEqual(expectedBuffer, providedBuffer)
  ) {
    throw new Error("Invalid Kommo Salesbot token signature");
  }

  const nowUnix = Math.floor((input.now ?? new Date()).getTime() / 1000);

  if (typeof payload.nbf === "number" && nowUnix < payload.nbf) {
    throw new Error("Kommo Salesbot token is not valid yet");
  }

  if (typeof payload.exp === "number" && nowUnix >= payload.exp) {
    throw new Error("Kommo Salesbot token has expired");
  }

  return payload;
}

export async function resolveAuthorizedKommoLawFirm(input: {
  workspaceKey: string;
  providedSecret?: string | null;
}) {
  const lawFirm = await resolveKommoLawFirm(input.workspaceKey);

  if (!lawFirm) {
    return null;
  }

  const configuredSecret =
    normalizeOptionalString((await getKommoWorkspaceSettingsRecord(lawFirm.id))?.webhookSecret) ?? "";

  if (configuredSecret) {
    const providedSecret = String(input.providedSecret ?? "");
    const expectedBuffer = fixedLengthSecret(configuredSecret);
    const providedBuffer = fixedLengthSecret(providedSecret);

    if (
      providedBuffer.length !== expectedBuffer.length ||
      !timingSafeEqual(expectedBuffer, providedBuffer)
    ) {
      throw new Error("Invalid Kommo webhook secret");
    }
  }

  return lawFirm;
}

let kommoStorageSchemaPromise: Promise<void> | null = null;

function mapKommoLeadRow(row: KommoLeadRow): KommoLeadRecord {
  return {
    id: row.id,
    lawFirmId: row.law_firm_id,
    leadId: row.kommo_lead_id,
    contactId: row.kommo_contact_id,
    linkedClientId: row.linked_client_id,
    linkedCaseId: row.linked_case_id,
    conversationId: row.conversation_id,
    displayName: row.display_name,
    email: row.email,
    phone: row.phone,
    statusCode: row.status_code,
    sourceCode: row.source_code,
    externalThreadId: row.external_thread_id,
    salesbotReturnUrl: row.salesbot_return_url,
    lastMessagePreview: row.last_message_preview,
    lastMessageAt: row.last_message_at,
    unreadMessageCount: Number(row.unread_message_count ?? 0),
    metadataJson: parseJsonObject(row.metadata_json),
    createdAt: row.created_at,
    updatedAt: row.updated_at,
    linkedAt: row.linked_at,
  };
}

function mapKommoLeadMessageRow(row: KommoLeadMessageRow) {
  return {
    id: row.id,
    leadRecordId: row.kommo_lead_record_id,
    conversationId: row.conversation_id,
    repositoryItemId: row.repository_item_id,
    externalMessageId: row.external_message_id,
    directionCode: row.direction_code,
    bodyText: row.body_text,
    senderName: row.sender_name,
    senderAddress: row.sender_address,
    recipientAddress: row.recipient_address,
    messageStatus: row.message_status,
    occurredAt: row.occurred_at,
    rawPayloadJson: parseJsonObject(row.raw_payload_json),
    createdAt: row.created_at,
  };
}

function mapKommoOutboundDeliveryRow(row: KommoOutboundDeliveryRow): KommoOutboundDeliveryRecord {
  return {
    id: row.id,
    lawFirmId: row.law_firm_id,
    leadRecordId: row.kommo_lead_record_id,
    conversationId: row.conversation_id,
    repositoryItemId: row.repository_item_id,
    deliveryChannel: row.delivery_channel,
    returnUrl: row.return_url,
    payloadJson: parseJsonObject(row.payload_json),
    statusCode: row.status_code,
    attemptCount: Number(row.attempt_count ?? 0),
    maxAttempts: Number(row.max_attempts ?? 0),
    nextRetryAt: row.next_retry_at,
    lastAttemptAt: row.last_attempt_at,
    deliveredAt: row.delivered_at,
    lastErrorCode: row.last_error_code,
    lastErrorMessage: row.last_error_message,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

export async function ensureKommoStorageSchema() {
  if (kommoStorageSchemaPromise) {
    return kommoStorageSchemaPromise;
  }

  kommoStorageSchemaPromise = (async () => {
    await prisma.$executeRawUnsafe(`
      CREATE TABLE IF NOT EXISTS kommo_leads (
        id CHAR(36) NOT NULL,
        law_firm_id CHAR(36) NOT NULL,
        kommo_lead_id VARCHAR(191) NOT NULL,
        kommo_contact_id VARCHAR(191) NULL,
        linked_client_id CHAR(36) NULL,
        linked_case_id CHAR(36) NULL,
        conversation_id CHAR(36) NULL,
        display_name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NULL,
        phone VARCHAR(50) NULL,
        status_code VARCHAR(30) NOT NULL DEFAULT 'unlinked',
        source_code VARCHAR(50) NULL,
        external_thread_id VARCHAR(191) NULL,
        salesbot_return_url VARCHAR(2048) NULL,
        last_message_preview TEXT NULL,
        last_message_at DATETIME NULL,
        unread_message_count INT NOT NULL DEFAULT 0,
        metadata_json JSON NULL,
        linked_at DATETIME NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY uq_kommo_leads_law_firm_lead (law_firm_id, kommo_lead_id),
        KEY idx_kommo_leads_status (law_firm_id, status_code, last_message_at),
        KEY idx_kommo_leads_client (linked_client_id),
        KEY idx_kommo_leads_conversation (conversation_id),
        CONSTRAINT fk_kommo_leads_law_firm FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
        CONSTRAINT fk_kommo_leads_client FOREIGN KEY (linked_client_id) REFERENCES clients (id),
        CONSTRAINT fk_kommo_leads_case FOREIGN KEY (linked_case_id) REFERENCES cases (id),
        CONSTRAINT fk_kommo_leads_conversation FOREIGN KEY (conversation_id) REFERENCES conversations (id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    await prisma.$executeRawUnsafe(`
      CREATE TABLE IF NOT EXISTS kommo_lead_messages (
        id CHAR(36) NOT NULL,
        law_firm_id CHAR(36) NOT NULL,
        kommo_lead_record_id CHAR(36) NOT NULL,
        conversation_id CHAR(36) NULL,
        repository_item_id CHAR(36) NULL,
        external_message_id VARCHAR(191) NULL,
        direction_code VARCHAR(30) NOT NULL,
        body_text LONGTEXT NOT NULL,
        sender_name VARCHAR(255) NULL,
        sender_address VARCHAR(255) NULL,
        recipient_address VARCHAR(255) NULL,
        message_status VARCHAR(50) NULL,
        raw_payload_json JSON NULL,
        occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        UNIQUE KEY uq_kommo_lead_messages_external (law_firm_id, external_message_id),
        KEY idx_kommo_lead_messages_lead (kommo_lead_record_id, occurred_at),
        KEY idx_kommo_lead_messages_conversation (conversation_id, occurred_at),
        CONSTRAINT fk_kommo_lead_messages_law_firm FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
        CONSTRAINT fk_kommo_lead_messages_lead FOREIGN KEY (kommo_lead_record_id) REFERENCES kommo_leads (id),
        CONSTRAINT fk_kommo_lead_messages_conversation FOREIGN KEY (conversation_id) REFERENCES conversations (id),
        CONSTRAINT fk_kommo_lead_messages_repository_item FOREIGN KEY (repository_item_id) REFERENCES repository_items (id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    await prisma.$executeRawUnsafe(`
      CREATE TABLE IF NOT EXISTS kommo_outbound_deliveries (
        id CHAR(36) NOT NULL,
        law_firm_id CHAR(36) NOT NULL,
        kommo_lead_record_id CHAR(36) NULL,
        conversation_id CHAR(36) NOT NULL,
        repository_item_id CHAR(36) NULL,
        delivery_channel VARCHAR(30) NOT NULL DEFAULT 'salesbot_return_url',
        return_url VARCHAR(2048) NULL,
        payload_json JSON NOT NULL,
        status_code VARCHAR(40) NOT NULL DEFAULT 'pending',
        attempt_count INT NOT NULL DEFAULT 0,
        max_attempts INT NOT NULL DEFAULT 5,
        next_retry_at DATETIME NULL,
        last_attempt_at DATETIME NULL,
        delivered_at DATETIME NULL,
        last_error_code VARCHAR(50) NULL,
        last_error_message TEXT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY idx_kommo_outbound_deliveries_status (law_firm_id, status_code, next_retry_at),
        KEY idx_kommo_outbound_deliveries_conversation (conversation_id, created_at),
        KEY idx_kommo_outbound_deliveries_repository (repository_item_id),
        CONSTRAINT fk_kommo_outbound_deliveries_law_firm FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
        CONSTRAINT fk_kommo_outbound_deliveries_lead FOREIGN KEY (kommo_lead_record_id) REFERENCES kommo_leads (id),
        CONSTRAINT fk_kommo_outbound_deliveries_conversation FOREIGN KEY (conversation_id) REFERENCES conversations (id),
        CONSTRAINT fk_kommo_outbound_deliveries_repository_item FOREIGN KEY (repository_item_id) REFERENCES repository_items (id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
  })().catch((error) => {
    kommoStorageSchemaPromise = null;
    throw error;
  });

  return kommoStorageSchemaPromise;
}

async function getKommoLeadRecordById(input: {
  lawFirmId: string;
  leadRecordId: string;
}) {
  await ensureKommoStorageSchema();

  const rows = await prisma.$queryRaw<KommoLeadRow[]>`
    SELECT
      id,
      law_firm_id,
      kommo_lead_id,
      kommo_contact_id,
      linked_client_id,
      linked_case_id,
      conversation_id,
      display_name,
      email,
      phone,
      status_code,
      source_code,
      external_thread_id,
      salesbot_return_url,
      last_message_preview,
      last_message_at,
      unread_message_count,
      metadata_json,
      created_at,
      updated_at,
      linked_at
    FROM kommo_leads
    WHERE law_firm_id = ${input.lawFirmId}
      AND id = ${input.leadRecordId}
    LIMIT 1
  `;

  return rows[0] ? mapKommoLeadRow(rows[0]) : null;
}

async function getKommoLeadRecordByLeadId(input: {
  lawFirmId: string;
  leadId: string;
}) {
  await ensureKommoStorageSchema();

  const rows = await prisma.$queryRaw<KommoLeadRow[]>`
    SELECT
      id,
      law_firm_id,
      kommo_lead_id,
      kommo_contact_id,
      linked_client_id,
      linked_case_id,
      conversation_id,
      display_name,
      email,
      phone,
      status_code,
      source_code,
      external_thread_id,
      salesbot_return_url,
      last_message_preview,
      last_message_at,
      unread_message_count,
      metadata_json,
      created_at,
      updated_at,
      linked_at
    FROM kommo_leads
    WHERE law_firm_id = ${input.lawFirmId}
      AND kommo_lead_id = ${input.leadId}
    LIMIT 1
  `;

  return rows[0] ? mapKommoLeadRow(rows[0]) : null;
}

function buildKommoConversationMetadata(record: KommoLeadRecord) {
  return {
    ...record.metadataJson,
    provider: record.salesbotReturnUrl ? KOMMO_SALESBOT_PROVIDER : KOMMO_PROVIDER,
    leadId: record.leadId,
    contactId: record.contactId,
    salesbotReturnUrl: record.salesbotReturnUrl,
    lastInboundMessageAt: record.lastMessageAt?.toISOString() ?? record.metadataJson.lastInboundMessageAt ?? null,
  };
}

async function updateKommoLeadRecordPointers(input: {
  leadRecordId: string;
  conversationId?: string | null;
  linkedClientId?: string | null;
  linkedCaseId?: string | null;
  statusCode?: string | null;
  linkedAt?: Date | null;
}) {
  await prisma.$executeRaw`
    UPDATE kommo_leads
    SET
      conversation_id = COALESCE(${input.conversationId ?? null}, conversation_id),
      linked_client_id = COALESCE(${input.linkedClientId ?? null}, linked_client_id),
      linked_case_id = COALESCE(${input.linkedCaseId ?? null}, linked_case_id),
      status_code = COALESCE(${input.statusCode ?? null}, status_code),
      linked_at = COALESCE(${input.linkedAt ?? null}, linked_at),
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${input.leadRecordId}
  `;
}

async function upsertKommoLeadRecord(input: {
  lawFirmId: string;
  leadId: string;
  lead?: KommoLeadLike | null;
  contact?: KommoContactLike | null;
  sourceCode?: string | null;
  externalThreadId?: string | null;
  salesbotReturnUrl?: string | null;
  metadata?: Record<string, unknown>;
}) {
  await ensureKommoStorageSchema();

  const existing = await getKommoLeadRecordByLeadId({
    lawFirmId: input.lawFirmId,
    leadId: input.leadId,
  });
  const inputLeadName = normalizeOptionalString(input.lead?.name);
  const shouldFetchLeadDetails = !input.lead || isGenericKommoLeadDisplayName(inputLeadName);
  const fetchedLead =
    shouldFetchLeadDetails ?
    await fetchKommoLeadDetails({
      lawFirmId: input.lawFirmId,
      leadId: input.leadId,
    }) :
    null;
  const lead =
    fetchedLead ??
    input.lead ?? {
      id: input.leadId,
      name: existing?.displayName ?? `Lead Kommo ${input.leadId}`,
    };
  const embeddedContactId =
    normalizeOptionalString(lead._embedded?.contacts?.[0]?.id) ??
    normalizeOptionalString(input.contact?.id) ??
    existing?.contactId ??
    null;
  const embeddedContactName = normalizeOptionalString(lead._embedded?.contacts?.[0]?.name);
  const contact =
    input.contact ??
    (embeddedContactId &&
    !input.contact &&
    (!existing || !embeddedContactName || isGenericKommoLeadDisplayName(normalizeOptionalString(lead.name)))
      ? await fetchKommoContactDetails({
          lawFirmId: input.lawFirmId,
          contactId: embeddedContactId,
        })
      : null);
  const contactData = extractKommoLeadContactData({
    lead,
    contact,
  });
  const displayName =
    normalizeOptionalString(contact?.name) ??
    (!isGenericKommoLeadDisplayName(normalizeOptionalString(lead.name)) ?
    normalizeOptionalString(lead.name) :
    null) ??
    normalizeOptionalString(lead.name) ??
    existing?.displayName ??
    `Lead Kommo ${input.leadId}`;
  const metadataJson = {
    ...(existing?.metadataJson ?? {}),
    ...(input.metadata ?? {}),
    leadName: normalizeOptionalString(lead.name) ?? existing?.metadataJson.leadName ?? null,
    contactName: normalizeOptionalString(contact?.name) ?? existing?.metadataJson.contactName ?? null,
    fetchedFromKommoApi: shouldFetchLeadDetails,
  };

  if (!existing) {
    const id = createId();

    await prisma.$executeRaw`
      INSERT INTO kommo_leads (
        id,
        law_firm_id,
        kommo_lead_id,
        kommo_contact_id,
        linked_client_id,
        linked_case_id,
        conversation_id,
        display_name,
        email,
        phone,
        status_code,
        source_code,
        external_thread_id,
        salesbot_return_url,
        last_message_preview,
        last_message_at,
        unread_message_count,
        metadata_json,
        linked_at,
        created_at,
        updated_at
      ) VALUES (
        ${id},
        ${input.lawFirmId},
        ${input.leadId},
        ${embeddedContactId},
        NULL,
        NULL,
        NULL,
        ${displayName},
        ${contactData.email},
        ${contactData.phone},
        'unlinked',
        ${normalizeOptionalString(input.sourceCode)},
        ${normalizeOptionalString(input.externalThreadId)},
        ${normalizeOptionalString(input.salesbotReturnUrl)},
        NULL,
        NULL,
        0,
        ${JSON.stringify(metadataJson)},
        NULL,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
      )
    `;

    const created = await getKommoLeadRecordById({
      lawFirmId: input.lawFirmId,
      leadRecordId: id,
    });

    if (!created) {
      throw new Error("Kommo lead could not be created locally");
    }

    return created;
  }

  await prisma.$executeRaw`
    UPDATE kommo_leads
    SET
      kommo_contact_id = COALESCE(${embeddedContactId}, kommo_contact_id),
      display_name = ${displayName},
      email = COALESCE(${contactData.email}, email),
      phone = COALESCE(${contactData.phone}, phone),
      source_code = COALESCE(${normalizeOptionalString(input.sourceCode)}, source_code),
      external_thread_id = COALESCE(${normalizeOptionalString(input.externalThreadId)}, external_thread_id),
      salesbot_return_url = COALESCE(${normalizeOptionalString(input.salesbotReturnUrl)}, salesbot_return_url),
      metadata_json = ${JSON.stringify(metadataJson)},
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${existing.id}
  `;

  const updated = await getKommoLeadRecordById({
    lawFirmId: input.lawFirmId,
    leadRecordId: existing.id,
  });

  if (!updated) {
    throw new Error("Kommo lead could not be refreshed locally");
  }

  return updated;
}

async function fetchKommoEntity<T>(input: { lawFirmId: string; path: string }) {
  const settings = await getKommoWorkspaceSettingsRecord(input.lawFirmId);

  if (!settings?.subdomain || !settings.accessToken) {
    return null;
  }

  const response = await fetch(`https://${settings.subdomain}.kommo.com${input.path}`, {
    headers: {
      accept: "application/json",
      authorization: `Bearer ${settings.accessToken}`,
    },
  });

  if (!response.ok) {
    return null;
  }

  return (await response.json()) as T;
}

async function requestKommoEntity<T>(input: { lawFirmId: string; pathOrUrl: string }) {
  const settings = await getKommoWorkspaceSettingsRecord(input.lawFirmId);

  if (!settings?.subdomain || !settings.accessToken) {
    throw new Error("Kommo API is not configured.");
  }

  const url = /^https?:\/\//i.test(input.pathOrUrl)
    ? input.pathOrUrl
    : `https://${settings.subdomain}.kommo.com${input.pathOrUrl}`;
  const response = await fetch(url, {
    headers: {
      accept: "application/json",
      authorization: `Bearer ${settings.accessToken}`,
    },
  });

  if (!response.ok) {
    const errorBody = await response.text().catch(() => "");
    throw new Error(
      `Kommo API request failed with status ${response.status}${errorBody ? `: ${errorBody}` : ""}`,
    );
  }

  return (await response.json()) as T;
}

async function fetchKommoLeadDetails(input: { lawFirmId: string; leadId: string }) {
  return fetchKommoEntity<KommoLeadLike>({
    lawFirmId: input.lawFirmId,
    path: `/api/v4/leads/${encodeURIComponent(input.leadId)}?with=contacts`,
  });
}

async function fetchKommoContactDetails(input: { lawFirmId: string; contactId: string }) {
  return fetchKommoEntity<KommoContactLike>({
    lawFirmId: input.lawFirmId,
    path: `/api/v4/contacts/${encodeURIComponent(input.contactId)}`,
  });
}

function normalizeKommoNextPageHref(value: string | null) {
  const href = normalizeOptionalString(value);

  if (!href) {
    return null;
  }

  if (/^https?:\/\//i.test(href) || href.startsWith("/")) {
    return href;
  }

  return `/${href.replace(/^\/+/, "")}`;
}

export async function syncKommoLead(input: {
  lawFirmId: string;
  leadId: string;
  lead?: KommoLeadLike | null;
  contact?: KommoContactLike | null;
  sourceCode?: string | null;
  externalThreadId?: string | null;
  salesbotReturnUrl?: string | null;
  metadata?: Record<string, unknown>;
}) {
  const leadRecord = await upsertKommoLeadRecord({
    lawFirmId: input.lawFirmId,
    leadId: input.leadId,
    lead: input.lead,
    contact: input.contact,
    sourceCode: input.sourceCode ?? null,
    externalThreadId: input.externalThreadId ?? null,
    salesbotReturnUrl: input.salesbotReturnUrl ?? null,
    metadata: input.metadata,
  });
  const conversationId = leadRecord.linkedClientId
    ? await ensureLinkedKommoConversation(leadRecord)
    : null;

  return {
    leadRecordId: leadRecord.id,
    leadId: leadRecord.leadId,
    leadName: leadRecord.displayName,
    clientId: leadRecord.linkedClientId,
    caseId: leadRecord.linkedCaseId,
    conversationId,
    isLinked: Boolean(leadRecord.linkedClientId),
  } satisfies KommoLeadSyncResult;
}

async function findExistingKommoLeadMessageByExternalId(input: {
  lawFirmId: string;
  externalMessageId: string;
}) {
  await ensureKommoStorageSchema();

  const rows = await prisma.$queryRaw<KommoLeadMessageRow[]>`
    SELECT
      id,
      kommo_lead_record_id,
      conversation_id,
      repository_item_id,
      external_message_id,
      direction_code,
      body_text,
      sender_name,
      sender_address,
      recipient_address,
      message_status,
      raw_payload_json,
      occurred_at,
      created_at
    FROM kommo_lead_messages
    WHERE law_firm_id = ${input.lawFirmId}
      AND external_message_id = ${input.externalMessageId}
    LIMIT 1
  `;

  return rows[0] ? mapKommoLeadMessageRow(rows[0]) : null;
}

async function backfillKommoLeadMessagesToRepository(input: {
  leadRecord: KommoLeadRecord;
  conversationId: string;
}) {
  if (!input.leadRecord.linkedClientId) {
    return;
  }

  const rows = await prisma.$queryRaw<KommoLeadMessageRow[]>`
    SELECT
      id,
      kommo_lead_record_id,
      conversation_id,
      repository_item_id,
      external_message_id,
      direction_code,
      body_text,
      sender_name,
      sender_address,
      recipient_address,
      message_status,
      raw_payload_json,
      occurred_at,
      created_at
    FROM kommo_lead_messages
    WHERE law_firm_id = ${input.leadRecord.lawFirmId}
      AND kommo_lead_record_id = ${input.leadRecord.id}
      AND repository_item_id IS NULL
    ORDER BY occurred_at ASC, created_at ASC
  `;

  for (const row of rows) {
    const storedMessage = await storeConversationMessage({
      lawFirmId: input.leadRecord.lawFirmId,
      clientId: input.leadRecord.linkedClientId,
      caseId: input.leadRecord.linkedCaseId,
      conversationId: input.conversationId,
      channelCode: "whatsapp",
      itemTypeCode: getItemTypeCodeForKommoMessage("whatsapp"),
      directionCode: row.direction_code,
      subject: "Mensagem Kommo",
      bodyText: row.body_text,
      senderType: row.direction_code === "outbound" ? "staff" : "person",
      senderName: row.sender_name,
      senderAddress: row.sender_address,
      recipientAddress: row.recipient_address,
      externalMessageId: row.external_message_id,
      messageStatus: row.message_status ?? "stored",
      occurredAt: row.occurred_at,
    });

    await prisma.$executeRaw`
      UPDATE kommo_lead_messages
      SET
        conversation_id = ${input.conversationId},
        repository_item_id = ${storedMessage.repositoryItemId}
      WHERE id = ${row.id}
    `;
  }

  await prisma.$executeRaw`
    UPDATE kommo_leads
    SET
      unread_message_count = 0,
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${input.leadRecord.id}
  `;
}

async function ensureLinkedKommoConversation(leadRecord: KommoLeadRecord) {
  if (!leadRecord.linkedClientId) {
    return null;
  }

  const conversationId =
    leadRecord.conversationId ??
    (await upsertKommoConversation({
      lawFirmId: leadRecord.lawFirmId,
      clientId: leadRecord.linkedClientId,
      caseId: leadRecord.linkedCaseId,
      leadId: leadRecord.leadId,
      externalThreadId: leadRecord.externalThreadId,
      subject: leadRecord.displayName,
      metadata: buildKommoConversationMetadata(leadRecord),
    }));

  await updateKommoLeadRecordPointers({
    leadRecordId: leadRecord.id,
    conversationId,
    linkedClientId: leadRecord.linkedClientId,
    linkedCaseId: leadRecord.linkedCaseId,
    statusCode: "linked",
    linkedAt: leadRecord.linkedAt ?? new Date(),
  });
  await backfillKommoLeadMessagesToRepository({
    leadRecord: {
      ...leadRecord,
      conversationId,
      statusCode: "linked",
      linkedAt: leadRecord.linkedAt ?? new Date(),
    },
    conversationId,
  });

  return conversationId;
}

async function storeKommoLeadMessage(input: {
  leadRecord: KommoLeadRecord;
  directionCode: "inbound" | "outbound";
  bodyText: string;
  senderName?: string | null;
  senderAddress?: string | null;
  recipientAddress?: string | null;
  externalMessageId?: string | null;
  messageStatus?: string | null;
  externalThreadId?: string | null;
  salesbotReturnUrl?: string | null;
  sourceCode?: string | null;
  rawPayloadJson?: unknown;
  occurredAt?: Date;
}) {
  await ensureKommoStorageSchema();

  const externalMessageId = normalizeOptionalString(input.externalMessageId);

  if (externalMessageId) {
    const existingMessage = await findExistingKommoLeadMessageByExternalId({
      lawFirmId: input.leadRecord.lawFirmId,
      externalMessageId,
    });

    if (existingMessage) {
      return {
        stored: false,
        conversationId: existingMessage.conversationId,
        repositoryItemId: existingMessage.repositoryItemId,
      };
    }
  }

  const occurredAt = input.occurredAt ?? new Date();
  const messageId = createId();

  await prisma.$executeRaw`
    INSERT INTO kommo_lead_messages (
      id,
      law_firm_id,
      kommo_lead_record_id,
      conversation_id,
      repository_item_id,
      external_message_id,
      direction_code,
      body_text,
      sender_name,
      sender_address,
      recipient_address,
      message_status,
      raw_payload_json,
      occurred_at,
      created_at
    ) VALUES (
      ${messageId},
      ${input.leadRecord.lawFirmId},
      ${input.leadRecord.id},
      ${null},
      ${null},
      ${externalMessageId},
      ${input.directionCode},
      ${input.bodyText},
      ${normalizeOptionalString(input.senderName)},
      ${normalizeOptionalString(input.senderAddress)},
      ${normalizeOptionalString(input.recipientAddress)},
      ${normalizeOptionalString(input.messageStatus) ?? "stored"},
      ${input.rawPayloadJson ? JSON.stringify(input.rawPayloadJson) : null},
      ${occurredAt},
      CURRENT_TIMESTAMP
    )
  `;

  const refreshedLeadRecord = await getKommoLeadRecordById({
    lawFirmId: input.leadRecord.lawFirmId,
    leadRecordId: input.leadRecord.id,
  });

  if (!refreshedLeadRecord) {
    throw new Error("Kommo lead is no longer available for message storage");
  }

  let conversationId: string | null = refreshedLeadRecord.conversationId;
  let repositoryItemId: string | null = null;

  if (refreshedLeadRecord.linkedClientId) {
    const nextLeadRecord = await upsertKommoLeadRecord({
      lawFirmId: refreshedLeadRecord.lawFirmId,
      leadId: refreshedLeadRecord.leadId,
      sourceCode: input.sourceCode ?? refreshedLeadRecord.sourceCode,
      externalThreadId: input.externalThreadId ?? refreshedLeadRecord.externalThreadId,
      salesbotReturnUrl: input.salesbotReturnUrl ?? refreshedLeadRecord.salesbotReturnUrl,
      metadata: {
        ...refreshedLeadRecord.metadataJson,
      },
    });

    conversationId =
      (await ensureLinkedKommoConversation({
        ...nextLeadRecord,
        externalThreadId: input.externalThreadId ?? nextLeadRecord.externalThreadId,
        salesbotReturnUrl: input.salesbotReturnUrl ?? nextLeadRecord.salesbotReturnUrl,
      })) ?? nextLeadRecord.conversationId;

    if (!conversationId || !nextLeadRecord.linkedClientId) {
      throw new Error("Kommo linked lead conversation could not be created");
    }

    const storedMessage = await storeConversationMessage({
      lawFirmId: nextLeadRecord.lawFirmId,
      clientId: nextLeadRecord.linkedClientId,
      caseId: nextLeadRecord.linkedCaseId,
      conversationId,
      channelCode: "whatsapp",
      itemTypeCode: getItemTypeCodeForKommoMessage("whatsapp"),
      directionCode: input.directionCode,
      subject: "Mensagem Kommo",
      bodyText: input.bodyText,
      senderType: input.directionCode === "outbound" ? "staff" : "person",
      senderName: normalizeOptionalString(input.senderName),
      senderAddress: normalizeOptionalString(input.senderAddress),
      recipientAddress: normalizeOptionalString(input.recipientAddress),
      externalMessageId,
      messageStatus: normalizeOptionalString(input.messageStatus) ?? "stored",
      occurredAt,
    });

    repositoryItemId = storedMessage.repositoryItemId;

    await prisma.$executeRaw`
      UPDATE kommo_lead_messages
      SET
        conversation_id = ${conversationId},
        repository_item_id = ${repositoryItemId}
      WHERE id = ${messageId}
    `;
  }

  const nextUnreadCount =
    refreshedLeadRecord.linkedClientId || input.directionCode !== "inbound"
      ? 0
      : refreshedLeadRecord.unreadMessageCount + 1;
  const nextMetadata = {
    ...refreshedLeadRecord.metadataJson,
    ...(input.sourceCode ? { sourceCode: input.sourceCode } : {}),
    ...(input.externalThreadId ? { externalThreadId: input.externalThreadId } : {}),
    ...(input.salesbotReturnUrl ? { salesbotReturnUrl: input.salesbotReturnUrl } : {}),
  };

  await prisma.$executeRaw`
    UPDATE kommo_leads
    SET
      source_code = COALESCE(${normalizeOptionalString(input.sourceCode)}, source_code),
      external_thread_id = COALESCE(${normalizeOptionalString(input.externalThreadId)}, external_thread_id),
      salesbot_return_url = COALESCE(${normalizeOptionalString(input.salesbotReturnUrl)}, salesbot_return_url),
      last_message_preview = ${input.bodyText},
      last_message_at = ${occurredAt},
      unread_message_count = ${nextUnreadCount},
      status_code = ${refreshedLeadRecord.linkedClientId ? "linked" : "unlinked"},
      metadata_json = ${JSON.stringify(nextMetadata)},
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${refreshedLeadRecord.id}
  `;

  return {
    stored: true,
    conversationId,
    repositoryItemId,
  };
}

function parseJsonObject(value: unknown) {
  if (!value) {
    return {};
  }

  if (typeof value === "string") {
    try {
      const parsed = JSON.parse(value) as unknown;
      return parsed && typeof parsed === "object" && !Array.isArray(parsed)
        ? (parsed as Record<string, unknown>)
        : {};
    } catch {
      return {};
    }
  }

  if (typeof value === "object" && !Array.isArray(value)) {
    return value as Record<string, unknown>;
  }

  return {};
}

async function findConversationByExternalThreadId(lawFirmId: string, externalThreadId: string) {
  const rows = await prisma.$queryRaw<
    Array<{
      id: string;
      client_id: string;
      case_id: string | null;
      subject: string | null;
      participants_json: unknown;
      external_thread_id: string | null;
    }>
  >`
    SELECT id, client_id, case_id, subject, participants_json, external_thread_id
    FROM conversations
    WHERE law_firm_id = ${lawFirmId}
      AND external_thread_id = ${externalThreadId}
    ORDER BY updated_at DESC
    LIMIT 1
  `;

  const row = rows[0];

  if (!row) {
    return null;
  }

  return {
    id: row.id,
    clientId: row.client_id,
    caseId: row.case_id,
    subject: row.subject,
    participantsJson: parseJsonObject(row.participants_json),
    externalThreadId: row.external_thread_id,
  } satisfies KommoConversationBinding;
}

async function findConversationByLeadId(input: {
  lawFirmId: string;
  clientId: string;
  caseId: string | null;
  leadId: string;
}) {
  const rows = await prisma.$queryRaw<
    Array<{
      id: string;
      client_id: string;
      case_id: string | null;
      subject: string | null;
      participants_json: unknown;
      external_thread_id: string | null;
    }>
  >`
    SELECT id, client_id, case_id, subject, participants_json, external_thread_id
    FROM conversations
    WHERE law_firm_id = ${input.lawFirmId}
      AND client_id = ${input.clientId}
      AND (${input.caseId} IS NULL OR case_id = ${input.caseId})
      AND JSON_UNQUOTE(JSON_EXTRACT(participants_json, '$.leadId')) = ${input.leadId}
    ORDER BY updated_at DESC
    LIMIT 1
  `;

  const row = rows[0];

  if (!row) {
    return null;
  }

  return {
    id: row.id,
    clientId: row.client_id,
    caseId: row.case_id,
    subject: row.subject,
    participantsJson: parseJsonObject(row.participants_json),
    externalThreadId: row.external_thread_id,
  } satisfies KommoConversationBinding;
}

async function saveConversationBinding(input: {
  conversationId: string;
  externalThreadId?: string | null;
  subject?: string | null;
  participantsJson: Record<string, unknown>;
}) {
  await prisma.$executeRaw`
    UPDATE conversations
    SET
      external_thread_id = ${input.externalThreadId ?? null},
      subject = COALESCE(${input.subject ?? null}, subject),
      participants_json = ${JSON.stringify(input.participantsJson)},
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${input.conversationId}
  `;
}

async function upsertKommoConversation(input: {
  lawFirmId: string;
  clientId: string;
  caseId: string | null;
  leadId: string | null;
  externalThreadId: string | null;
  subject: string | null;
  metadata: Record<string, unknown>;
}) {
  const existing =
    (input.externalThreadId
      ? await findConversationByExternalThreadId(input.lawFirmId, input.externalThreadId)
      : null) ??
    (input.leadId
      ? await findConversationByLeadId({
          lawFirmId: input.lawFirmId,
          clientId: input.clientId,
          caseId: input.caseId,
          leadId: input.leadId,
        })
      : null);

  if (!existing) {
    return createConversation({
      lawFirmId: input.lawFirmId,
      clientId: input.clientId,
      caseId: input.caseId,
      channelCode: "whatsapp",
      subject: input.subject ?? "Conversa Kommo",
      participantsJson: input.metadata,
      externalThreadId: input.externalThreadId,
    });
  }

  const mergedParticipantsJson = {
    ...existing.participantsJson,
    ...input.metadata,
  };

  await saveConversationBinding({
    conversationId: existing.id,
    externalThreadId: input.externalThreadId ?? existing.externalThreadId,
    subject: input.subject ?? existing.subject,
    participantsJson: mergedParticipantsJson,
  });

  return existing.id;
}

function getItemTypeCodeForKommoMessage(channelCode: string) {
  return channelCode === "whatsapp" ? "whatsapp_message" : "note";
}

export function collectKommoLeadAddEvents(payload: Record<string, unknown>) {
  return toArray<Record<string, unknown>>((payload.leads as Record<string, unknown> | undefined)?.add);
}

export function collectKommoMessageAddEvents(payload: Record<string, unknown>) {
  return toArray<Record<string, unknown>>((payload.message as Record<string, unknown> | undefined)?.add);
}

export async function ingestKommoWebhookMessage(input: {
  lawFirmId: string;
  message: Record<string, unknown>;
}) {
  const leadId =
    normalizeOptionalString(input.message.entity_id) ??
    normalizeOptionalString(input.message.lead_id) ??
    null;

  if (!leadId) {
    return {
      stored: false,
      conversationId: null,
    };
  }

  const leadSync = await syncKommoLead({
    lawFirmId: input.lawFirmId,
    leadId,
    sourceCode: "message_webhook",
    externalThreadId:
      normalizeOptionalString(input.message.chat_id) ??
      normalizeOptionalString(input.message.talk_id) ??
      null,
    metadata: {
      provider: KOMMO_PROVIDER,
      leadId,
      contactId: normalizeOptionalString(input.message.contact_id),
      origin: normalizeOptionalString(input.message.origin),
      talkId: normalizeOptionalString(input.message.talk_id),
      chatId: normalizeOptionalString(input.message.chat_id),
    },
  });
  const leadRecord = await getKommoLeadRecordById({
    lawFirmId: input.lawFirmId,
    leadRecordId: leadSync.leadRecordId,
  });

  if (!leadRecord) {
    throw new Error("Kommo lead is no longer available");
  }

  const bodyText =
    normalizeOptionalString(input.message.text) ??
    normalizeOptionalString(input.message.message) ??
    "[Mensagem recebida do Kommo sem corpo de texto.]";
  const externalThreadId =
    normalizeOptionalString(input.message.chat_id) ??
    normalizeOptionalString(input.message.talk_id) ??
    null;
  const senderName =
    normalizeOptionalString(input.message.author_name) ??
    normalizeOptionalString(input.message.author) ??
    "Lead Kommo";
  const storedMessage = await storeKommoLeadMessage({
    leadRecord,
    directionCode: "inbound",
    bodyText,
    senderName,
    senderAddress:
      normalizeOptionalString(input.message.contact_id) ??
      normalizeOptionalString(input.message.chat_id) ??
      `kommo:lead:${leadId}`,
    externalMessageId: normalizeOptionalString(input.message.id),
    messageStatus: "received",
    externalThreadId,
    sourceCode: "message_webhook",
    rawPayloadJson: input.message,
  });

  return {
    stored: storedMessage.stored,
    conversationId: storedMessage.conversationId,
  };
}

export async function ingestKommoSalesbotMessage(input: {
  lawFirmId: string;
  payload: Record<string, unknown>;
}) {
  const data = parseJsonObject(input.payload.data);
  const leadId =
    normalizeOptionalString(data.lead) ??
    normalizeOptionalString(data.leadId) ??
    normalizeOptionalString(data.lead_id) ??
    null;

  if (!leadId) {
    throw new Error("Kommo Salesbot message is missing lead id");
  }

  const leadSync = await syncKommoLead({
    lawFirmId: input.lawFirmId,
    leadId,
    sourceCode: "salesbot",
    externalThreadId:
      normalizeOptionalString(data.chatId) ??
      normalizeOptionalString(data.chat_id) ??
      normalizeOptionalString(data.talkId) ??
      normalizeOptionalString(data.talk_id) ??
      null,
    salesbotReturnUrl: normalizeOptionalString(input.payload.return_url),
    metadata: {
      provider: KOMMO_SALESBOT_PROVIDER,
      leadId,
      contactId:
        normalizeOptionalString(data.contactId) ?? normalizeOptionalString(data.contact_id),
      chatId: normalizeOptionalString(data.chatId) ?? normalizeOptionalString(data.chat_id),
      talkId: normalizeOptionalString(data.talkId) ?? normalizeOptionalString(data.talk_id),
      salesbotToken: normalizeOptionalString(input.payload.token),
    },
  });
  const leadRecord = await getKommoLeadRecordById({
    lawFirmId: input.lawFirmId,
    leadRecordId: leadSync.leadRecordId,
  });

  if (!leadRecord) {
    throw new Error("Kommo lead is no longer available");
  }

  const bodyText =
    normalizeOptionalString(data.message) ??
    normalizeOptionalString(data.bodyText) ??
    "[Mensagem recebida do Salesbot sem corpo de texto.]";
  const externalThreadId =
    normalizeOptionalString(data.chatId) ??
    normalizeOptionalString(data.chat_id) ??
    normalizeOptionalString(data.talkId) ??
    normalizeOptionalString(data.talk_id) ??
    null;
  const storedMessage = await storeKommoLeadMessage({
    leadRecord,
    directionCode: "inbound",
    bodyText,
    senderName:
      normalizeOptionalString(data.senderName) ??
      normalizeOptionalString(data.authorName) ??
      "Lead Kommo",
    senderAddress:
      normalizeOptionalString(data.contactId) ??
      normalizeOptionalString(data.contact_id) ??
      normalizeOptionalString(data.chatId) ??
      `kommo:lead:${leadId}`,
    externalMessageId:
      normalizeOptionalString(data.messageId) ??
      normalizeOptionalString(data.message_id) ??
      normalizeOptionalString(input.payload.token),
    messageStatus: "received",
    externalThreadId,
    salesbotReturnUrl: normalizeOptionalString(input.payload.return_url),
    sourceCode: "salesbot",
    rawPayloadJson: input.payload,
  });

  return {
    leadRecordId: leadSync.leadRecordId,
    conversationId: storedMessage.conversationId,
    clientId: leadSync.clientId,
    caseId: leadSync.caseId,
    isLinked: leadSync.isLinked,
  };
}

export async function getKommoConversationBinding(input: {
  lawFirmId: string;
  conversationId: string;
}) {
  const rows = await prisma.$queryRaw<
    Array<{
      id: string;
      client_id: string;
      case_id: string | null;
      subject: string | null;
      participants_json: unknown;
      external_thread_id: string | null;
    }>
  >`
    SELECT id, client_id, case_id, subject, participants_json, external_thread_id
    FROM conversations
    WHERE id = ${input.conversationId}
      AND law_firm_id = ${input.lawFirmId}
    LIMIT 1
  `;

  const row = rows[0];

  if (!row) {
    return null;
  }

  return {
    id: row.id,
    clientId: row.client_id,
    caseId: row.case_id,
    subject: row.subject,
    participantsJson: parseJsonObject(row.participants_json),
    externalThreadId: row.external_thread_id,
  } satisfies KommoConversationBinding;
}

async function getKommoLeadRecordByConversationId(input: {
  lawFirmId: string;
  conversationId: string;
}) {
  await ensureKommoStorageSchema();

  const rows = await prisma.$queryRaw<KommoLeadRow[]>`
    SELECT
      id,
      law_firm_id,
      kommo_lead_id,
      kommo_contact_id,
      linked_client_id,
      linked_case_id,
      conversation_id,
      display_name,
      email,
      phone,
      status_code,
      source_code,
      external_thread_id,
      salesbot_return_url,
      last_message_preview,
      last_message_at,
      unread_message_count,
      metadata_json,
      created_at,
      updated_at,
      linked_at
    FROM kommo_leads
    WHERE law_firm_id = ${input.lawFirmId}
      AND conversation_id = ${input.conversationId}
    ORDER BY updated_at DESC
    LIMIT 1
  `;

  return rows[0] ? mapKommoLeadRow(rows[0]) : null;
}

async function getKommoOutboundDeliveryById(input: {
  lawFirmId: string;
  deliveryId: string;
}) {
  await ensureKommoStorageSchema();

  const rows = await prisma.$queryRaw<KommoOutboundDeliveryRow[]>`
    SELECT
      id,
      law_firm_id,
      kommo_lead_record_id,
      conversation_id,
      repository_item_id,
      delivery_channel,
      return_url,
      payload_json,
      status_code,
      attempt_count,
      max_attempts,
      next_retry_at,
      last_attempt_at,
      delivered_at,
      last_error_code,
      last_error_message,
      created_at,
      updated_at
    FROM kommo_outbound_deliveries
    WHERE law_firm_id = ${input.lawFirmId}
      AND id = ${input.deliveryId}
    LIMIT 1
  `;

  return rows[0] ? mapKommoOutboundDeliveryRow(rows[0]) : null;
}

async function listDueKommoOutboundDeliveries(input: {
  lawFirmId: string;
  limit?: number;
}) {
  await ensureKommoStorageSchema();

  const limit = Math.max(1, Math.min(20, input.limit ?? 5));
  const rows = await prisma.$queryRaw<KommoOutboundDeliveryRow[]>`
    SELECT
      id,
      law_firm_id,
      kommo_lead_record_id,
      conversation_id,
      repository_item_id,
      delivery_channel,
      return_url,
      payload_json,
      status_code,
      attempt_count,
      max_attempts,
      next_retry_at,
      last_attempt_at,
      delivered_at,
      last_error_code,
      last_error_message,
      created_at,
      updated_at
    FROM kommo_outbound_deliveries
    WHERE law_firm_id = ${input.lawFirmId}
      AND status_code = 'retry_scheduled'
      AND next_retry_at IS NOT NULL
      AND next_retry_at <= CURRENT_TIMESTAMP
    ORDER BY next_retry_at ASC, created_at ASC
    LIMIT ${limit}
  `;

  return rows.map(mapKommoOutboundDeliveryRow);
}

async function createKommoOutboundDelivery(input: {
  lawFirmId: string;
  leadRecordId?: string | null;
  conversationId: string;
  returnUrl: string;
  payloadJson: Record<string, unknown>;
}) {
  await ensureKommoStorageSchema();

  const id = createId();
  const maxAttempts = getKommoReturnUrlMaxAttempts();

  await prisma.$executeRaw`
    INSERT INTO kommo_outbound_deliveries (
      id,
      law_firm_id,
      kommo_lead_record_id,
      conversation_id,
      repository_item_id,
      delivery_channel,
      return_url,
      payload_json,
      status_code,
      attempt_count,
      max_attempts,
      next_retry_at,
      last_attempt_at,
      delivered_at,
      last_error_code,
      last_error_message,
      created_at,
      updated_at
    ) VALUES (
      ${id},
      ${input.lawFirmId},
      ${input.leadRecordId ?? null},
      ${input.conversationId},
      NULL,
      'salesbot_return_url',
      ${input.returnUrl},
      ${JSON.stringify(input.payloadJson)},
      'pending',
      0,
      ${maxAttempts},
      NULL,
      NULL,
      NULL,
      NULL,
      NULL,
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
    )
  `;

  const created = await getKommoOutboundDeliveryById({
    lawFirmId: input.lawFirmId,
    deliveryId: id,
  });

  if (!created) {
    throw new Error("Kommo outbound delivery could not be created");
  }

  return created;
}

function mapKommoDeliveryStatusToMessageStatus(statusCode: string) {
  switch (statusCode) {
    case "sent":
      return "sent_via_kommo_salesbot";
    case "retry_scheduled":
      return "kommo_retry_scheduled";
    case "failed":
      return "kommo_delivery_failed";
    default:
      return "stored";
  }
}

async function syncKommoDeliveryMessageStatus(input: {
  lawFirmId: string;
  repositoryItemId?: string | null;
  messageStatus: string;
}) {
  if (!input.repositoryItemId) {
    return;
  }

  await updateConversationMessageStatus({
    lawFirmId: input.lawFirmId,
    repositoryItemId: input.repositoryItemId,
    messageStatus: input.messageStatus,
  });

  await prisma.$executeRaw`
    UPDATE kommo_lead_messages
    SET
      message_status = ${input.messageStatus}
    WHERE law_firm_id = ${input.lawFirmId}
      AND repository_item_id = ${input.repositoryItemId}
  `;
}

async function writeKommoDeliveryAuditLog(input: {
  lawFirmId: string;
  conversationId: string;
  deliveryId?: string | null;
  action: string;
  auditContext?: KommoDeliveryAuditContext;
  afterJson?: unknown;
}) {
  await writeAuditLog({
    lawFirmId: input.lawFirmId,
    officeId: input.auditContext?.officeId ?? null,
    actorUserId: input.auditContext?.actorUserId ?? null,
    entityType: input.deliveryId ? "kommo_outbound_delivery" : "conversation",
    entityId: input.deliveryId ?? input.conversationId,
    action: input.action,
    afterJson: input.afterJson,
    request: input.auditContext?.request,
  });
}

function truncateKommoErrorMessage(value: string | null | undefined, maxLength = 1500) {
  const normalized = String(value ?? "").trim();

  if (!normalized) {
    return null;
  }

  return normalized.length > maxLength ? normalized.slice(0, maxLength).trimEnd() : normalized;
}

async function setKommoOutboundDeliveryRepositoryItem(input: {
  lawFirmId: string;
  deliveryId: string;
  repositoryItemId: string;
  messageStatus: string;
}) {
  await ensureKommoStorageSchema();

  await prisma.$executeRaw`
    UPDATE kommo_outbound_deliveries
    SET
      repository_item_id = ${input.repositoryItemId},
      updated_at = CURRENT_TIMESTAMP
    WHERE law_firm_id = ${input.lawFirmId}
      AND id = ${input.deliveryId}
  `;

  await syncKommoDeliveryMessageStatus({
    lawFirmId: input.lawFirmId,
    repositoryItemId: input.repositoryItemId,
    messageStatus: input.messageStatus,
  });
}

async function postKommoSalesbotReturnUrl(input: {
  returnUrl: string;
  payloadJson: Record<string, unknown>;
}) {
  let response: Response;

  try {
    response = await fetch(input.returnUrl, {
      method: "POST",
      headers: {
        "content-type": "application/json",
      },
      body: JSON.stringify(input.payloadJson),
      signal: AbortSignal.timeout(getKommoReturnUrlTimeoutMs()),
    });
  } catch (error) {
    const message = error instanceof Error ? error.message : String(error ?? "network_error");
    throw new KommoReturnUrlError({
      message: `Kommo Salesbot return_url request failed: ${message}`,
      errorCode: "network_error",
      retriable: true,
    });
  }

  if (response.ok) {
    return;
  }

  const errorBody = truncateKommoErrorMessage(await response.text().catch(() => ""));
  const retriable =
    response.status === 408 ||
    response.status === 425 ||
    response.status === 429 ||
    response.status >= 500;

  throw new KommoReturnUrlError({
    message: `Kommo Salesbot return_url failed with status ${response.status}${errorBody ? `: ${errorBody}` : ""}`,
    errorCode: `http_${response.status}`,
    retriable,
    statusCode: response.status,
    responseBody: errorBody,
  });
}

async function markKommoOutboundDeliverySent(input: {
  delivery: KommoOutboundDeliveryRecord;
  attemptNumber: number;
  auditContext?: KommoDeliveryAuditContext;
}) {
  const deliveredAt = new Date();
  const messageStatus = mapKommoDeliveryStatusToMessageStatus("sent");

  await prisma.$executeRaw`
    UPDATE kommo_outbound_deliveries
    SET
      status_code = 'sent',
      attempt_count = ${input.attemptNumber},
      next_retry_at = NULL,
      last_attempt_at = ${deliveredAt},
      delivered_at = ${deliveredAt},
      last_error_code = NULL,
      last_error_message = NULL,
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${input.delivery.id}
  `;

  await syncKommoDeliveryMessageStatus({
    lawFirmId: input.delivery.lawFirmId,
    repositoryItemId: input.delivery.repositoryItemId,
    messageStatus,
  });

  if (input.delivery.statusCode === "retry_scheduled" || input.attemptNumber > 1) {
    await writeKommoDeliveryAuditLog({
      lawFirmId: input.delivery.lawFirmId,
      conversationId: input.delivery.conversationId,
      deliveryId: input.delivery.id,
      action: "kommo.delivery.retry_succeeded",
      auditContext: input.auditContext,
      afterJson: {
        attemptCount: input.attemptNumber,
        repositoryItemId: input.delivery.repositoryItemId,
        deliveredAt: deliveredAt.toISOString(),
      },
    });
  }

  return {
    messageStatus,
    deliveryId: input.delivery.id,
    attemptCount: input.attemptNumber,
    nextRetryAt: null,
    lastErrorMessage: null,
  };
}

async function markKommoOutboundDeliveryRetryScheduled(input: {
  delivery: KommoOutboundDeliveryRecord;
  attemptNumber: number;
  failure: KommoReturnUrlError;
  auditContext?: KommoDeliveryAuditContext;
}) {
  const attemptAt = new Date();
  const nextRetryAt = new Date(attemptAt.getTime() + getKommoDeferredRetryDelayMs(input.attemptNumber));
  const messageStatus = mapKommoDeliveryStatusToMessageStatus("retry_scheduled");
  const lastErrorMessage = truncateKommoErrorMessage(
    input.failure.responseBody ? `${input.failure.message}` : input.failure.message,
  );

  await prisma.$executeRaw`
    UPDATE kommo_outbound_deliveries
    SET
      status_code = 'retry_scheduled',
      attempt_count = ${input.attemptNumber},
      next_retry_at = ${nextRetryAt},
      last_attempt_at = ${attemptAt},
      delivered_at = NULL,
      last_error_code = ${input.failure.errorCode},
      last_error_message = ${lastErrorMessage},
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${input.delivery.id}
  `;

  await syncKommoDeliveryMessageStatus({
    lawFirmId: input.delivery.lawFirmId,
    repositoryItemId: input.delivery.repositoryItemId,
    messageStatus,
  });

  await writeKommoDeliveryAuditLog({
    lawFirmId: input.delivery.lawFirmId,
    conversationId: input.delivery.conversationId,
    deliveryId: input.delivery.id,
    action: "kommo.delivery.retry_scheduled",
    auditContext: input.auditContext,
    afterJson: {
      attemptCount: input.attemptNumber,
      maxAttempts: input.delivery.maxAttempts,
      nextRetryAt: nextRetryAt.toISOString(),
      errorCode: input.failure.errorCode,
      errorMessage: lastErrorMessage,
      repositoryItemId: input.delivery.repositoryItemId,
    },
  });

  return {
    messageStatus,
    deliveryId: input.delivery.id,
    attemptCount: input.attemptNumber,
    nextRetryAt,
    lastErrorMessage,
  };
}

async function recordKommoOutboundDeliveryImmediateRetry(input: {
  delivery: KommoOutboundDeliveryRecord;
  attemptNumber: number;
  failure: KommoReturnUrlError;
}) {
  const attemptAt = new Date();
  const lastErrorMessage = truncateKommoErrorMessage(input.failure.message);

  await prisma.$executeRaw`
    UPDATE kommo_outbound_deliveries
    SET
      status_code = 'pending',
      attempt_count = ${input.attemptNumber},
      next_retry_at = NULL,
      last_attempt_at = ${attemptAt},
      delivered_at = NULL,
      last_error_code = ${input.failure.errorCode},
      last_error_message = ${lastErrorMessage},
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${input.delivery.id}
  `;

  return {
    outcome: "retry_immediately" as const,
    messageStatus: "stored",
    deliveryId: input.delivery.id,
    attemptCount: input.attemptNumber,
    nextRetryAt: null,
    lastErrorMessage,
  };
}

async function markKommoOutboundDeliveryFailed(input: {
  delivery: KommoOutboundDeliveryRecord;
  attemptNumber: number;
  failure: KommoReturnUrlError;
  auditContext?: KommoDeliveryAuditContext;
}) {
  const attemptAt = new Date();
  const messageStatus = mapKommoDeliveryStatusToMessageStatus("failed");
  const lastErrorMessage = truncateKommoErrorMessage(
    input.failure.responseBody ? `${input.failure.message}` : input.failure.message,
  );

  await prisma.$executeRaw`
    UPDATE kommo_outbound_deliveries
    SET
      status_code = 'failed',
      attempt_count = ${input.attemptNumber},
      next_retry_at = NULL,
      last_attempt_at = ${attemptAt},
      delivered_at = NULL,
      last_error_code = ${input.failure.errorCode},
      last_error_message = ${lastErrorMessage},
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${input.delivery.id}
  `;

  await syncKommoDeliveryMessageStatus({
    lawFirmId: input.delivery.lawFirmId,
    repositoryItemId: input.delivery.repositoryItemId,
    messageStatus,
  });

  await writeKommoDeliveryAuditLog({
    lawFirmId: input.delivery.lawFirmId,
    conversationId: input.delivery.conversationId,
    deliveryId: input.delivery.id,
    action: "kommo.delivery.failed",
    auditContext: input.auditContext,
    afterJson: {
      attemptCount: input.attemptNumber,
      maxAttempts: input.delivery.maxAttempts,
      errorCode: input.failure.errorCode,
      errorMessage: lastErrorMessage,
      repositoryItemId: input.delivery.repositoryItemId,
    },
  });

  return {
    messageStatus,
    deliveryId: input.delivery.id,
    attemptCount: input.attemptNumber,
    nextRetryAt: null,
    lastErrorMessage,
  };
}

async function attemptKommoOutboundDelivery(input: {
  delivery: KommoOutboundDeliveryRecord;
  auditContext?: KommoDeliveryAuditContext;
  scheduleRetryOnFailure?: boolean;
}) {
  const attemptNumber = input.delivery.attemptCount + 1;

  try {
    await postKommoSalesbotReturnUrl({
      returnUrl: input.delivery.returnUrl ?? "",
      payloadJson: input.delivery.payloadJson,
    });

    return markKommoOutboundDeliverySent({
      delivery: input.delivery,
      attemptNumber,
      auditContext: input.auditContext,
    }).then((result) => ({
      ...result,
      outcome: "sent" as const,
    }));
  } catch (error) {
    const failure = normalizeKommoReturnUrlError(error);

    if (
      input.scheduleRetryOnFailure === false &&
      failure.retriable &&
      attemptNumber < input.delivery.maxAttempts
    ) {
      return recordKommoOutboundDeliveryImmediateRetry({
        delivery: input.delivery,
        attemptNumber,
        failure,
      });
    }

    if (failure.retriable && attemptNumber < input.delivery.maxAttempts) {
      return markKommoOutboundDeliveryRetryScheduled({
        delivery: input.delivery,
        attemptNumber,
        failure,
        auditContext: input.auditContext,
      }).then((result) => ({
        ...result,
        outcome: "retry_scheduled" as const,
      }));
    }

    return markKommoOutboundDeliveryFailed({
      delivery: input.delivery,
      attemptNumber,
      failure,
      auditContext: input.auditContext,
    }).then((result) => ({
      ...result,
      outcome: "failed" as const,
    }));
  }
}

export async function processDueKommoOutboundDeliveries(input: {
  lawFirmId: string;
  limit?: number;
}) {
  const dueDeliveries = await listDueKommoOutboundDeliveries({
    lawFirmId: input.lawFirmId,
    limit: input.limit,
  });

  let processedCount = 0;
  let sentCount = 0;
  let retryScheduledCount = 0;
  let failedCount = 0;

  for (const delivery of dueDeliveries) {
    processedCount += 1;
    const result = await attemptKommoOutboundDelivery({
      delivery,
    });

    if (result.outcome === "sent") {
      sentCount += 1;
      continue;
    }

    if (result.outcome === "retry_scheduled") {
      retryScheduledCount += 1;
      continue;
    }

    if (result.outcome === "failed") {
      failedCount += 1;
    }
  }

  return {
    processedCount,
    sentCount,
    retryScheduledCount,
    failedCount,
  };
}

export async function dispatchKommoConversationReply(input: {
  lawFirmId: string;
  conversationId: string;
  bodyText: string;
  auditContext?: KommoDeliveryAuditContext;
}) {
  const binding = await getKommoConversationBinding({
    lawFirmId: input.lawFirmId,
    conversationId: input.conversationId,
  });

  if (!binding) {
    return {
      isKommoConversation: false,
      messageStatus: "stored",
      externalMessageId: null,
    };
  }

  const provider = normalizeOptionalString(binding.participantsJson.provider);
  const salesbotReturnUrl = normalizeOptionalString(binding.participantsJson.salesbotReturnUrl);

  if (![KOMMO_PROVIDER, KOMMO_SALESBOT_PROVIDER].includes(provider ?? "")) {
    return {
      isKommoConversation: false,
      messageStatus: "stored",
      externalMessageId: null,
    };
  }

  if (!salesbotReturnUrl) {
    await writeKommoDeliveryAuditLog({
      lawFirmId: input.lawFirmId,
      conversationId: input.conversationId,
      action: "kommo.delivery.reply_unavailable",
      auditContext: input.auditContext,
      afterJson: {
        provider,
        reason: "missing_salesbot_return_url",
      },
    });

    return {
      isKommoConversation: true,
      messageStatus: "kommo_reply_unavailable",
      externalMessageId: null,
      clientId: binding.clientId,
      caseId: binding.caseId,
      subject: binding.subject,
    };
  }

  await processDueKommoOutboundDeliveries({
    lawFirmId: input.lawFirmId,
    limit: 3,
  });
  const leadRecord = await getKommoLeadRecordByConversationId({
    lawFirmId: input.lawFirmId,
    conversationId: input.conversationId,
  });
  const payloadJson = {
    data: {
      message: input.bodyText,
    },
    execute_handlers: [
      {
        handler: "show",
        params: {
          type: "text",
          value: input.bodyText,
        },
      },
    ],
  } satisfies Record<string, unknown>;
  const delivery = await createKommoOutboundDelivery({
    lawFirmId: input.lawFirmId,
    leadRecordId: leadRecord?.id ?? null,
    conversationId: input.conversationId,
    returnUrl: salesbotReturnUrl,
    payloadJson,
  });
  const immediateAttempts = Math.min(delivery.maxAttempts, getKommoReturnUrlImmediateAttempts());

  for (let currentAttempt = 0; currentAttempt < immediateAttempts; currentAttempt += 1) {
    const currentDelivery =
      currentAttempt === 0
        ? delivery
        : await getKommoOutboundDeliveryById({
            lawFirmId: input.lawFirmId,
            deliveryId: delivery.id,
          });

    if (!currentDelivery) {
      throw new Error("Kommo outbound delivery disappeared before dispatch completed");
    }

    const result = await attemptKommoOutboundDelivery({
      delivery: currentDelivery,
      auditContext: input.auditContext,
      scheduleRetryOnFailure: currentAttempt >= immediateAttempts - 1,
    });

    if (result.outcome === "sent") {
      return {
        isKommoConversation: true,
        messageStatus: result.messageStatus,
        externalMessageId: null,
        clientId: binding.clientId,
        caseId: binding.caseId,
        subject: binding.subject,
        deliveryId: delivery.id,
        attemptCount: result.attemptCount,
        nextRetryAt: null,
        lastErrorMessage: null,
      };
    }

    if (result.outcome === "failed") {
      return {
        isKommoConversation: true,
        messageStatus: result.messageStatus,
        externalMessageId: null,
        clientId: binding.clientId,
        caseId: binding.caseId,
        subject: binding.subject,
        deliveryId: delivery.id,
        attemptCount: result.attemptCount,
        nextRetryAt: null,
        lastErrorMessage: result.lastErrorMessage,
      };
    }

    if (result.outcome === "retry_scheduled") {
      return {
        isKommoConversation: true,
        messageStatus: result.messageStatus,
        externalMessageId: null,
        clientId: binding.clientId,
        caseId: binding.caseId,
        subject: binding.subject,
        deliveryId: delivery.id,
        attemptCount: result.attemptCount,
        nextRetryAt: result.nextRetryAt,
        lastErrorMessage: result.lastErrorMessage,
      };
    }

    await sleep(getKommoImmediateRetryDelayMs(result.attemptCount));
  }

  return {
    isKommoConversation: true,
    messageStatus: "kommo_retry_scheduled",
    externalMessageId: null,
    clientId: binding.clientId,
    caseId: binding.caseId,
    subject: binding.subject,
    deliveryId: delivery.id,
    attemptCount: delivery.attemptCount,
    nextRetryAt: null,
    lastErrorMessage: null,
  };
}

export async function attachKommoOutboundDeliveryToRepositoryItem(input: {
  lawFirmId: string;
  deliveryId: string;
  repositoryItemId: string;
  messageStatus: string;
}) {
  await setKommoOutboundDeliveryRepositoryItem({
    lawFirmId: input.lawFirmId,
    deliveryId: input.deliveryId,
    repositoryItemId: input.repositoryItemId,
    messageStatus: input.messageStatus,
  });
}

export async function countUnlinkedKommoLeads(lawFirmId: string) {
  await ensureKommoStorageSchema();

  const rows = await prisma.$queryRaw<Array<{ total: number }>>`
    SELECT COUNT(*) AS total
    FROM kommo_leads
    WHERE law_firm_id = ${lawFirmId}
      AND linked_client_id IS NULL
  `;

  return Number(rows[0]?.total ?? 0);
}

export async function getKommoLeadStats(lawFirmId: string) {
  await ensureKommoStorageSchema();

  const [leadRow] = await prisma.$queryRaw<
    Array<{
      total_count: number;
      linked_count: number;
      unlinked_count: number;
      conversation_count: number;
    }>
  >`
    SELECT
      COUNT(*) AS total_count,
      SUM(CASE WHEN linked_client_id IS NOT NULL THEN 1 ELSE 0 END) AS linked_count,
      SUM(CASE WHEN linked_client_id IS NULL THEN 1 ELSE 0 END) AS unlinked_count,
      SUM(CASE WHEN conversation_id IS NOT NULL THEN 1 ELSE 0 END) AS conversation_count
    FROM kommo_leads
    WHERE law_firm_id = ${lawFirmId}
  `;
  const [messageRow] = await prisma.$queryRaw<Array<{ total: number }>>`
    SELECT COUNT(*) AS total
    FROM kommo_lead_messages
    WHERE law_firm_id = ${lawFirmId}
      AND repository_item_id IS NOT NULL
  `;

  return {
    totalCount: Number(leadRow?.total_count ?? 0),
    linkedCount: Number(leadRow?.linked_count ?? 0),
    unlinkedCount: Number(leadRow?.unlinked_count ?? 0),
    conversationCount: Number(leadRow?.conversation_count ?? 0),
    mirroredMessageCount: Number(messageRow?.total ?? 0),
  } satisfies KommoLeadStats;
}

export async function countKommoRetryScheduledDeliveries(lawFirmId: string) {
  await ensureKommoStorageSchema();

  const rows = await prisma.$queryRaw<Array<{ total: number }>>`
    SELECT COUNT(*) AS total
    FROM kommo_outbound_deliveries
    WHERE law_firm_id = ${lawFirmId}
      AND status_code = 'retry_scheduled'
  `;

  return Number(rows[0]?.total ?? 0);
}

export async function countKommoFailedDeliveries(lawFirmId: string) {
  await ensureKommoStorageSchema();

  const rows = await prisma.$queryRaw<Array<{ total: number }>>`
    SELECT COUNT(*) AS total
    FROM kommo_outbound_deliveries
    WHERE law_firm_id = ${lawFirmId}
      AND status_code = 'failed'
  `;

  return Number(rows[0]?.total ?? 0);
}

export async function countKommoConversationsNeedingAttention(lawFirmId: string) {
  await ensureKommoStorageSchema();

  const rows = await prisma.$queryRaw<Array<{ total: number }>>`
    SELECT COUNT(DISTINCT conversation_id) AS total
    FROM kommo_outbound_deliveries
    WHERE law_firm_id = ${lawFirmId}
      AND status_code IN ('retry_scheduled', 'failed')
  `;

  return Number(rows[0]?.total ?? 0);
}

export async function listKommoConversationIssues(input: {
  lawFirmId: string;
  limit?: number;
}) {
  await ensureKommoStorageSchema();

  const limit = Math.max(1, Math.min(20, input.limit ?? 6));
  const rows = await prisma.$queryRaw<
    Array<{
      conversation_id: string;
      client_id: string;
      client_name: string | null;
      lead_record_id: string | null;
      lead_display_name: string | null;
      retry_scheduled_count: number;
      failed_delivery_count: number;
      next_retry_at: Date | null;
      last_attempt_at: Date | null;
      last_error_message: string | null;
    }>
  >`
    SELECT
      d.conversation_id,
      conv.client_id,
      NULLIF(TRIM(CONCAT(COALESCE(client.first_name, ''), ' ', COALESCE(client.last_name, ''))), '') AS client_name,
      MAX(kl.id) AS lead_record_id,
      MAX(kl.display_name) AS lead_display_name,
      SUM(CASE WHEN d.status_code = 'retry_scheduled' THEN 1 ELSE 0 END) AS retry_scheduled_count,
      SUM(CASE WHEN d.status_code = 'failed' THEN 1 ELSE 0 END) AS failed_delivery_count,
      MIN(CASE WHEN d.status_code = 'retry_scheduled' THEN d.next_retry_at ELSE NULL END) AS next_retry_at,
      MAX(d.last_attempt_at) AS last_attempt_at,
      SUBSTRING_INDEX(
        GROUP_CONCAT(COALESCE(d.last_error_message, '') ORDER BY COALESCE(d.last_attempt_at, d.created_at) DESC SEPARATOR '\n'),
        '\n',
        1
      ) AS last_error_message
    FROM kommo_outbound_deliveries d
    INNER JOIN conversations conv
      ON conv.id = d.conversation_id
    LEFT JOIN clients client
      ON client.id = conv.client_id
    LEFT JOIN kommo_leads kl
      ON kl.law_firm_id = d.law_firm_id
      AND kl.conversation_id = d.conversation_id
    WHERE d.law_firm_id = ${input.lawFirmId}
      AND d.status_code IN ('retry_scheduled', 'failed')
    GROUP BY d.conversation_id, conv.client_id, client.first_name, client.last_name
    ORDER BY
      CASE WHEN SUM(CASE WHEN d.status_code = 'failed' THEN 1 ELSE 0 END) > 0 THEN 0 ELSE 1 END ASC,
      COALESCE(MAX(d.last_attempt_at), MIN(d.next_retry_at), CURRENT_TIMESTAMP) DESC
    LIMIT ${limit}
  `;

  return rows.map(
    (row) =>
      ({
        conversationId: row.conversation_id,
        clientId: row.client_id,
        clientName: row.client_name ?? "Cliente",
        leadRecordId: row.lead_record_id,
        leadDisplayName: row.lead_display_name,
        stateCode:
          Number(row.failed_delivery_count ?? 0) > 0 ? "failed" : "retry_scheduled",
        retryScheduledCount: Number(row.retry_scheduled_count ?? 0),
        failedDeliveryCount: Number(row.failed_delivery_count ?? 0),
        nextRetryAt: row.next_retry_at,
        lastAttemptAt: row.last_attempt_at,
        lastErrorMessage: truncateKommoErrorMessage(row.last_error_message, 280),
      }) satisfies KommoConversationIssueSummary,
  );
}

export async function listKommoLeads(input: {
  lawFirmId: string;
  status?: "all" | "linked" | "unlinked";
  limit?: number;
}) {
  await ensureKommoStorageSchema();

  const status = input.status ?? "all";
  const limit = Math.max(1, Math.min(200, input.limit ?? 100));
  const rows = await prisma.$queryRaw<
    Array<{
      id: string;
      kommo_lead_id: string;
      display_name: string;
      email: string | null;
      phone: string | null;
      status_code: string;
      linked_client_id: string | null;
      linked_client_name: string | null;
      conversation_id: string | null;
      last_message_preview: string | null;
      last_message_at: Date | null;
      unread_message_count: number;
      created_at: Date;
    }>
  >`
    SELECT
      kl.id,
      kl.kommo_lead_id,
      kl.display_name,
      kl.email,
      kl.phone,
      kl.status_code,
      kl.linked_client_id,
      NULLIF(TRIM(CONCAT(COALESCE(c.first_name, ''), ' ', COALESCE(c.last_name, ''))), '') AS linked_client_name,
      kl.conversation_id,
      kl.last_message_preview,
      kl.last_message_at,
      kl.unread_message_count,
      kl.created_at
    FROM kommo_leads kl
    LEFT JOIN clients c
      ON c.id = kl.linked_client_id
    WHERE kl.law_firm_id = ${input.lawFirmId}
      AND (
        ${status} = 'all'
        OR (${status} = 'linked' AND kl.linked_client_id IS NOT NULL)
        OR (${status} = 'unlinked' AND kl.linked_client_id IS NULL)
      )
    ORDER BY COALESCE(kl.last_message_at, kl.created_at) DESC, kl.created_at DESC
    LIMIT ${limit}
  `;

  return rows.map(
    (row) =>
      ({
        id: row.id,
        leadId: row.kommo_lead_id,
        displayName: row.display_name,
        email: row.email,
        phone: row.phone,
        statusCode: row.status_code,
        linkedClientId: row.linked_client_id,
        linkedClientName: row.linked_client_name,
        conversationId: row.conversation_id,
        lastMessagePreview: row.last_message_preview,
        lastMessageAt: row.last_message_at,
        unreadMessageCount: Number(row.unread_message_count ?? 0),
        createdAt: row.created_at,
      }) satisfies KommoLeadSummary,
  );
}

export async function importKommoLeads(input: {
  lawFirmId: string;
  limit?: number;
}) {
  await ensureKommoStorageSchema();

  const settings = await getKommoWorkspaceSettingsRecord(input.lawFirmId);

  if (!settings?.subdomain || !settings.accessToken) {
    throw new Error("Configure the Kommo subdomain and access token in Integrations before importing leads.");
  }

  const importLimit = Math.max(1, Math.min(5000, input.limit ?? 1000));
  const pageSize = Math.min(250, importLimit);
  let nextPageHref: string | null = `/api/v4/leads?with=contacts&limit=${pageSize}&page=1`;
  let fetchedCount = 0;
  let syncedCount = 0;
  let createdCount = 0;
  let updatedCount = 0;
  let linkedCount = 0;
  let unlinkedCount = 0;
  let pagesProcessed = 0;
  const existingLeadRows = await prisma.$queryRaw<Array<{ kommo_lead_id: string }>>`
    SELECT kommo_lead_id
    FROM kommo_leads
    WHERE law_firm_id = ${input.lawFirmId}
  `;
  const existingLeadIds = new Set(
    existingLeadRows
      .map((item) => normalizeOptionalString(item.kommo_lead_id))
      .filter((item): item is string => Boolean(item)),
  );

  while (nextPageHref && fetchedCount < importLimit) {
    const response: KommoLeadListResponse =
      await requestKommoEntity<KommoLeadListResponse>({
        lawFirmId: input.lawFirmId,
        pathOrUrl: nextPageHref,
      });
    const remaining = importLimit - fetchedCount;
    const leads = toArray<KommoLeadLike>(response._embedded?.leads).slice(0, remaining);

    if (!leads.length) {
      break;
    }

    pagesProcessed += 1;

    for (const lead of leads) {
      const leadId = normalizeOptionalString(lead.id);

      if (!leadId) {
        continue;
      }

      const alreadyExisted = existingLeadIds.has(leadId);
      const syncResult = await syncKommoLead({
        lawFirmId: input.lawFirmId,
        leadId,
        lead,
        sourceCode: "api_import",
        metadata: {
          importedFromKommoApi: true,
        },
      });

      fetchedCount += 1;
      syncedCount += 1;

      if (alreadyExisted) {
        updatedCount += 1;
      } else {
        createdCount += 1;
        existingLeadIds.add(leadId);
      }

      if (syncResult.isLinked) {
        linkedCount += 1;
      } else {
        unlinkedCount += 1;
      }
    }

    nextPageHref =
      fetchedCount >= importLimit
        ? null
        : normalizeKommoNextPageHref(response._links?.next?.href ?? null);
  }

  return {
    fetchedCount,
    syncedCount,
    createdCount,
    updatedCount,
    linkedCount,
    unlinkedCount,
    pagesProcessed,
  } satisfies KommoLeadImportResult;
}

export async function probeKommoLeadApi(input: {
  lawFirmId: string;
}): Promise<KommoApiLeadProbeResult> {
  await ensureKommoStorageSchema();

  const settings = await getKommoWorkspaceSettingsRecord(input.lawFirmId);
  const checkedAt = new Date();
  const requestPath = "/api/v4/leads?with=contacts&limit=5&page=1";

  if (!settings?.subdomain || !settings.accessToken) {
    return {
      configured: false,
      checkedAt,
      requestPath: null,
      reachable: false,
      sampleLeadCount: 0,
      nextPageAvailable: false,
      sampleLeadIds: [],
      sampleLeadNames: [],
      errorMessage: "Configure the Kommo subdomain and access token to query leads.",
    };
  }

  try {
    const response = await requestKommoEntity<KommoLeadListResponse>({
      lawFirmId: input.lawFirmId,
      pathOrUrl: requestPath,
    });
    const leads = toArray<KommoLeadLike>(response._embedded?.leads);

    return {
      configured: true,
      checkedAt,
      requestPath,
      reachable: true,
      sampleLeadCount: leads.length,
      nextPageAvailable: Boolean(normalizeKommoNextPageHref(response._links?.next?.href ?? null)),
      sampleLeadIds: leads
        .map((lead) => normalizeOptionalString(lead.id))
        .filter((leadId): leadId is string => Boolean(leadId)),
      sampleLeadNames: leads
        .map((lead) => normalizeOptionalString(lead.name) ?? normalizeOptionalString(lead.id))
        .filter((leadName): leadName is string => Boolean(leadName)),
      errorMessage: null,
    };
  } catch (error) {
    return {
      configured: true,
      checkedAt,
      requestPath,
      reachable: false,
      sampleLeadCount: 0,
      nextPageAvailable: false,
      sampleLeadIds: [],
      sampleLeadNames: [],
      errorMessage: error instanceof Error ? error.message : String(error ?? "Unknown Kommo error"),
    };
  }
}

export async function linkKommoLeadToClient(input: {
  lawFirmId: string;
  leadRecordId: string;
  clientId: string;
}) {
  await ensureKommoStorageSchema();

  const [leadRecord, client] = await Promise.all([
    getKommoLeadRecordById({
      lawFirmId: input.lawFirmId,
      leadRecordId: input.leadRecordId,
    }),
    prisma.client.findFirst({
      where: {
        id: input.clientId,
        law_firm_id: input.lawFirmId,
        deleted_at: null,
      },
    }),
  ]);

  if (!leadRecord) {
    return null;
  }

  if (!client) {
    throw new Error("Client not found for Kommo lead binding");
  }

  if (leadRecord.linkedClientId && leadRecord.linkedClientId !== input.clientId) {
    throw new Error("Kommo lead already linked to another client");
  }

  const linkedAt = leadRecord.linkedAt ?? new Date();

  await prisma.$executeRaw`
    UPDATE kommo_leads
    SET
      linked_client_id = ${input.clientId},
      status_code = 'linked',
      linked_at = COALESCE(linked_at, ${linkedAt}),
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${leadRecord.id}
  `;

  const refreshed = await getKommoLeadRecordById({
    lawFirmId: input.lawFirmId,
    leadRecordId: leadRecord.id,
  });

  if (!refreshed) {
    throw new Error("Kommo lead could not be linked locally");
  }

  const conversationId = await ensureLinkedKommoConversation({
    ...refreshed,
    linkedClientId: input.clientId,
    statusCode: "linked",
    linkedAt,
  });

  return getKommoLeadRecordById({
    lawFirmId: input.lawFirmId,
    leadRecordId: refreshed.id,
  }).then((finalRecord) =>
    finalRecord
      ? {
          ...finalRecord,
          conversationId: conversationId ?? finalRecord.conversationId,
        }
      : null,
  );
}

export async function mirrorRepositoryMessageToKommoLead(input: {
  lawFirmId: string;
  conversationId: string;
  repositoryItemId: string;
  bodyText: string;
  senderName?: string | null;
  senderAddress?: string | null;
  recipientAddress?: string | null;
  messageStatus?: string | null;
  occurredAt?: Date | null;
}) {
  await ensureKommoStorageSchema();

  const binding = await getKommoConversationBinding({
    lawFirmId: input.lawFirmId,
    conversationId: input.conversationId,
  });

  if (!binding) {
    return null;
  }

  const provider = normalizeOptionalString(binding.participantsJson.provider);
  const leadId = normalizeOptionalString(binding.participantsJson.leadId);

  if (!leadId || ![KOMMO_PROVIDER, KOMMO_SALESBOT_PROVIDER].includes(provider ?? "")) {
    return null;
  }

  const leadRecord = await getKommoLeadRecordByLeadId({
    lawFirmId: input.lawFirmId,
    leadId,
  });

  if (!leadRecord) {
    return null;
  }

  const occurredAt = input.occurredAt ?? new Date();

  await prisma.$executeRaw`
    INSERT INTO kommo_lead_messages (
      id,
      law_firm_id,
      kommo_lead_record_id,
      conversation_id,
      repository_item_id,
      external_message_id,
      direction_code,
      body_text,
      sender_name,
      sender_address,
      recipient_address,
      message_status,
      raw_payload_json,
      occurred_at,
      created_at
    ) VALUES (
      ${createId()},
      ${input.lawFirmId},
      ${leadRecord.id},
      ${input.conversationId},
      ${input.repositoryItemId},
      NULL,
      'outbound',
      ${input.bodyText},
      ${normalizeOptionalString(input.senderName)},
      ${normalizeOptionalString(input.senderAddress)},
      ${normalizeOptionalString(input.recipientAddress)},
      ${normalizeOptionalString(input.messageStatus) ?? "stored"},
      ${JSON.stringify({
        source: "repository_message",
        repositoryItemId: input.repositoryItemId,
      })},
      ${occurredAt},
      CURRENT_TIMESTAMP
    )
  `;

  await prisma.$executeRaw`
    UPDATE kommo_leads
    SET
      last_message_preview = ${input.bodyText},
      last_message_at = ${occurredAt},
      unread_message_count = 0,
      updated_at = CURRENT_TIMESTAMP
    WHERE id = ${leadRecord.id}
  `;

  return {
    leadRecordId: leadRecord.id,
  };
}