Skip to content

Open-source manufacturing ERP, MES and QMS. Quoting, MRP, inventory, shop floor, quality and lot/serial traceability on one Postgres schema, with a REST API and MCP server. Self-host or use Carbon Cloud.

31.8 KB
Raw Blame
// SPDX-License-Identifier: LicenseRef-Carbon-Commercial
// Carbon Enterprise file, licensed only under the Carbon Commercial License
// (packages/ee/LICENSE). Not AGPL. Running, modifying, or copying it beyond those terms requires a commercial license.

import type { Database } from "@carbon/database";
import { isUniqueViolation, quoteFilterValue } from "@carbon/utils";
import type { SupabaseClient } from "@supabase/supabase-js";
import { SYNC_OPERATION_ALLOWED_TRANSITIONS } from "./models";
import type {
  SyncOperation,
  SyncOperationDirection,
  SyncOperationStatus,
  SyncOperationTrigger
} from "./types";

/**
 * Service for the "accountingSyncOperation" ledger: one row per attempted
 * sync of one entity in one direction. Enqueue absorbs re-triggers into the
 * live row, drains claim Pending work, and the UI retries/skips/re-sends
 * through guarded transitions.
 */

/**
 * A Completed operation absorbs event/webhook re-triggers for this long.
 */
export const SYNC_OPERATION_COOLDOWN_MS = 60_000;

/**
 * "In Flight" rows older than this are considered abandoned (e.g. a drain
 * that crashed mid-run) and become claimable again.
 */
export const SYNC_OPERATION_STALE_IN_FLIGHT_MS = 10 * 60_000;

const DEFAULT_CLAIM_LIMIT = 20;
const DEFAULT_PAGE_SIZE = 25;

/**
 * Live operations hold the partial unique index on
 * (companyId, integration, entityType, entityId, direction): a new enqueue
 * for the same tuple is absorbed instead of creating a duplicate row.
 */
export function isLiveSyncOperationStatus(
  status: SyncOperationStatus
): boolean {
  return status === "Pending" || status === "In Flight";
}

/**
 * Only machine re-triggers (event/webhook) respect the completed-row
 * cooldown; backfill/manual/posting/retry always enqueue.
 */
export function isCooldownTrigger(trigger: SyncOperationTrigger): boolean {
  return trigger === "event" || trigger === "webhook";
}

/**
 * Decide whether an enqueue should be skipped because a Completed operation
 * for the same tuple finished inside the cooldown window.
 */
export function shouldSkipForCooldown(args: {
  trigger: SyncOperationTrigger;
  completedAt: string | null | undefined;
  now?: Date;
  cooldownMs?: number;
}): boolean {
  if (!isCooldownTrigger(args.trigger)) return false;
  if (!args.completedAt) return false;

  const completedAtMs = new Date(args.completedAt).getTime();
  if (Number.isNaN(completedAtMs)) return false;

  const nowMs = (args.now ?? new Date()).getTime();
  return (
    nowMs - completedAtMs < (args.cooldownMs ?? SYNC_OPERATION_COOLDOWN_MS)
  );
}

/**
 * Guard table for UI-driven transitions. Returns null when the transition
 * is allowed, otherwise a descriptive error string.
 */
export function getSyncOperationTransitionError(
  from: SyncOperationStatus,
  to: SyncOperationStatus
): string | null {
  return SYNC_OPERATION_ALLOWED_TRANSITIONS[from].includes(to)
    ? null
    : `invalid transition ${from} → ${to}`;
}

/**
 * PostgREST list literal for excluding entity types from a claim (used
 * with `.not("entityType", "in", ...)`), or null when there is nothing to
 * exclude. Values are double-quoted per PostgREST list syntax so an entity
 * type containing a reserved character cannot corrupt the list.
 */
export function getClaimEntityTypeExclusion(
  excludeEntityTypes: string[] | undefined
): string | null {
  if (!excludeEntityTypes || excludeEntityTypes.length === 0) return null;
  return `(${excludeEntityTypes.map((type) => `"${type}"`).join(",")})`;
}

/**
 * A claim can be scoped by an include filter (`entityTypes`, e.g. the
 * daily-consolidation cron claims only journalEntry operations) OR an
 * exclude filter (`excludeEntityTypes`, e.g. drains hold journalEntry for
 * the cron), never both — combining them invites contradictory filters.
 * Returns the error string for an invalid combination, else null.
 */
export function getClaimEntityTypeFilterError(args: {
  entityTypes?: string[];
  excludeEntityTypes?: string[];
}): string | null {
  const hasInclude = !!args.entityTypes && args.entityTypes.length > 0;
  const hasExclude =
    !!args.excludeEntityTypes && args.excludeEntityTypes.length > 0;
  return hasInclude && hasExclude
    ? "entityTypes and excludeEntityTypes are mutually exclusive claim filters"
    : null;
}

// Generated types now include "accountingSyncOperation" (regenerated with
// the v3 work), but dropping this cast trades it for Json-vs-Record friction
// on every metadata write in this file — a standalone cleanup. Until then
// the query builder stays untyped; row payloads are typed locally via
// SyncOperation (core/models.ts).
function syncOperationTable(client: SupabaseClient<Database>): any {
  return client.from("accountingSyncOperation" as any);
}

export type EnqueueSyncOperationInput = {
  companyId: string;
  integration: string;
  entityType: string;
  entityId: string;
  direction: SyncOperationDirection;
  trigger: SyncOperationTrigger;
  idempotencyKey: string;
  createdBy: string;
  metadata?: Record<string, unknown>;
};

async function getLiveOperation(
  client: SupabaseClient<Database>,
  op: Pick<
    EnqueueSyncOperationInput,
    "companyId" | "integration" | "entityType" | "entityId" | "direction"
  >
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const result = await syncOperationTable(client)
    .select("*")
    .eq("companyId", op.companyId)
    .eq("integration", op.integration)
    .eq("entityType", op.entityType)
    .eq("entityId", op.entityId)
    .eq("direction", op.direction)
    .in("status", ["Pending", "In Flight"])
    .maybeSingle();

  if (result.error) return { data: null, error: result.error.message };
  return { data: (result.data as SyncOperation | null) ?? null, error: null };
}

async function getOperationByIdempotencyKey(
  client: SupabaseClient<Database>,
  op: Pick<
    EnqueueSyncOperationInput,
    "companyId" | "integration" | "idempotencyKey"
  >
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const result = await syncOperationTable(client)
    .select("*")
    .eq("companyId", op.companyId)
    .eq("integration", op.integration)
    .eq("idempotencyKey", op.idempotencyKey)
    .maybeSingle();

  if (result.error) return { data: null, error: result.error.message };
  return { data: (result.data as SyncOperation | null) ?? null, error: null };
}

/**
 * Enqueue a sync operation.
 *
 * - Same (companyId, integration, idempotencyKey) already exists → returns
 *   the existing row (idempotent).
 * - A Pending/In Flight row exists for the same (entityType, entityId,
 *   direction) → returns it (absorbed). A concurrent insert losing the race
 *   on either unique index (23505) is absorbed the same way.
 * - A Completed row for the same tuple finished < 60s ago and the trigger
 *   is event/webhook → returns { data: null, error: null } (skipped).
 */
export async function enqueueSyncOperation(
  client: SupabaseClient<Database>,
  op: EnqueueSyncOperationInput
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const existingByKey = await getOperationByIdempotencyKey(client, op);
  if (existingByKey.error) return existingByKey;
  if (existingByKey.data) return existingByKey;

  const live = await getLiveOperation(client, op);
  if (live.error) return live;
  if (live.data) return live;

  if (isCooldownTrigger(op.trigger)) {
    const lastCompleted = await syncOperationTable(client)
      .select("completedAt")
      .eq("companyId", op.companyId)
      .eq("integration", op.integration)
      .eq("entityType", op.entityType)
      .eq("entityId", op.entityId)
      .eq("direction", op.direction)
      .eq("status", "Completed")
      .order("completedAt", { ascending: false })
      .limit(1)
      .maybeSingle();

    if (lastCompleted.error) {
      return { data: null, error: lastCompleted.error.message };
    }

    const completedAt = (
      lastCompleted.data as Pick<SyncOperation, "completedAt"> | null
    )?.completedAt;

    if (shouldSkipForCooldown({ trigger: op.trigger, completedAt })) {
      return { data: null, error: null };
    }
  }

  const inserted = await syncOperationTable(client)
    .insert({
      companyId: op.companyId,
      integration: op.integration,
      entityType: op.entityType,
      entityId: op.entityId,
      direction: op.direction,
      trigger: op.trigger,
      status: "Pending",
      idempotencyKey: op.idempotencyKey,
      metadata: op.metadata ?? null,
      createdBy: op.createdBy
    })
    .select("*")
    .single();

  if (inserted.error) {
    if (isUniqueViolation(inserted.error)) {
      // A concurrent enqueue won the race on one of the unique indexes —
      // absorb by returning whichever row now holds it
      const byKey = await getOperationByIdempotencyKey(client, op);
      if (byKey.data) return byKey;

      const liveRetry = await getLiveOperation(client, op);
      if (liveRetry.data) return liveRetry;
    }

    return { data: null, error: inserted.error.message };
  }

  return { data: inserted.data as SyncOperation, error: null };
}

export type InsertTerminalSyncOperationInput = EnqueueSyncOperationInput & {
  /** Decision-time disposition: Excluded (policy) or Warning (config hole). */
  status: "Excluded" | "Warning";
  errorCode: string;
  errorMessage: string;
};

async function getLatestOperationForTuple(
  client: SupabaseClient<Database>,
  op: Pick<
    EnqueueSyncOperationInput,
    "companyId" | "integration" | "entityType" | "entityId" | "direction"
  >
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const result = await syncOperationTable(client)
    .select("*")
    .eq("companyId", op.companyId)
    .eq("integration", op.integration)
    .eq("entityType", op.entityType)
    .eq("entityId", op.entityId)
    .eq("direction", op.direction)
    .order("createdAt", { ascending: false })
    .limit(1)
    .maybeSingle();

  if (result.error) return { data: null, error: result.error.message };
  return { data: (result.data as SyncOperation | null) ?? null, error: null };
}

/**
 * Record a decision-time disposition directly in a terminal status
 * (spec I1: every posted journal gets exactly one recorded disposition —
 * `Excluded` for policy exclusions, `Warning` for config holes like
 * DOC_SYNC_DISABLED). Idempotent:
 *
 * - same (companyId, integration, idempotencyKey) → returns the existing row;
 * - ANY existing operation for the (entityType, entityId, direction) tuple →
 *   returns it untouched (the journal already has a disposition or a live
 *   push — re-deliveries and backfills never churn new terminal rows);
 * - a concurrent insert losing the unique-index race (23505) is absorbed.
 *
 * `completedAt` is stamped as the disposition-recorded time so the inbox
 * sorts terminal rows consistently with Completed pushes.
 */
export async function insertTerminalSyncOperation(
  client: SupabaseClient<Database>,
  op: InsertTerminalSyncOperationInput
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const existingByKey = await getOperationByIdempotencyKey(client, op);
  if (existingByKey.error) return existingByKey;
  if (existingByKey.data) return existingByKey;

  const existingForTuple = await getLatestOperationForTuple(client, op);
  if (existingForTuple.error) return existingForTuple;
  if (existingForTuple.data) return existingForTuple;

  const inserted = await syncOperationTable(client)
    .insert({
      companyId: op.companyId,
      integration: op.integration,
      entityType: op.entityType,
      entityId: op.entityId,
      direction: op.direction,
      trigger: op.trigger,
      status: op.status,
      idempotencyKey: op.idempotencyKey,
      errorCode: op.errorCode,
      errorMessage: op.errorMessage,
      completedAt: new Date().toISOString(),
      metadata: op.metadata ?? null,
      createdBy: op.createdBy
    })
    .select("*")
    .single();

  if (inserted.error) {
    if (isUniqueViolation(inserted.error)) {
      const byKey = await getOperationByIdempotencyKey(client, op);
      if (byKey.data) return byKey;

      const forTuple = await getLatestOperationForTuple(client, op);
      if (forTuple.data) return forTuple;
    }

    return { data: null, error: inserted.error.message };
  }

  return { data: inserted.data as SyncOperation, error: null };
}

/**
 * Delete terminal disposition rows for entities that have since synced
 * successfully. Accounting journals get a permanent disposition, but a
 * re-evaluating INBOUND family (Ramp) can fail an entity one run — a charge
 * coded to an unrecognized account records a Warning — and succeed the next,
 * once it is recoded. Without this the resolved failure lingers in the Sync
 * Activity inbox and keeps the tab badge lit, so on every successful sync the
 * caller clears any prior disposition for those (entityType, entityId,
 * direction) tuples. Defaults to `Warning` (the only status the Ramp recorder
 * writes); a no-op when `entityIds` is empty. Service-role only (the table has
 * no DELETE policy — jobs delete via service role).
 */
export async function clearResolvedSyncOperations(
  client: SupabaseClient<Database>,
  args: {
    companyId: string;
    integration: string;
    entityType: string;
    direction: SyncOperationDirection;
    entityIds: string[];
    statuses?: SyncOperationStatus[];
  }
): Promise<{ error: string | null }> {
  if (args.entityIds.length === 0) return { error: null };
  const statuses = args.statuses ?? ["Warning"];
  const { error } = await syncOperationTable(client)
    .delete()
    .eq("companyId", args.companyId)
    .eq("integration", args.integration)
    .eq("entityType", args.entityType)
    .eq("direction", args.direction)
    .in("entityId", args.entityIds)
    .in("status", statuses);
  return { error: error ? (error as { message: string }).message : null };
}

/**
 * Claim up to `limit` operations for a drain: Pending rows plus "In Flight"
 * rows whose lastAttemptAt is older than 10 minutes (abandoned by a crashed
 * drain). Claimed rows move to "In Flight" with lastAttemptAt = now and
 * attemptCount incremented. Rows claimed by a concurrent drain in between
 * are skipped (optimistic lock on status + attemptCount).
 *
 * `excludeEntityTypes` leaves matching rows un-claimed (still Pending) —
 * the exclusion is applied inside the queries so held rows never consume
 * the claim limit. The daily-consolidation hold uses this: journalEntry
 * operations for companies whose posting-sync settings resolve to
 * consolidation "daily" are drained by the consolidation cron, never
 * pushed individually.
 *
 * `entityTypes` is the include-only counterpart (the consolidation cron
 * claims ONLY journalEntry operations). Mutually exclusive with
 * `excludeEntityTypes` — see getClaimEntityTypeFilterError.
 *
 * `direction` claims only operations going that way (a master-data import
 * claims only its own pulls).
 */
export async function claimPendingOperations(
  client: SupabaseClient<Database>,
  args: {
    companyId: string;
    integration: string;
    limit?: number;
    entityTypes?: string[];
    excludeEntityTypes?: string[];
    /**
     * Leave journalEntry operations stamped `metadata.granularity =
     * "daily-summary"` un-claimed (still Pending) — they belong to the
     * daily-consolidation cron. Individual-granularity (and legacy
     * unstamped) journal operations claim normally. The event-path drain
     * sets this whenever it is not holding ALL journal operations via the
     * transitional consolidation flag.
     */
    holdDailySummaryJournalEntries?: boolean;
    direction?: SyncOperationDirection;
  }
): Promise<{ data: SyncOperation[]; error: string | null }> {
  const filterError = getClaimEntityTypeFilterError(args);
  if (filterError) return { data: [], error: filterError };

  const limit = args.limit ?? DEFAULT_CLAIM_LIMIT;
  const inclusion =
    args.entityTypes && args.entityTypes.length > 0 ? args.entityTypes : null;
  const exclusion = getClaimEntityTypeExclusion(args.excludeEntityTypes);
  // NOT (entityType = journalEntry AND metadata->>granularity = daily-summary)
  const dailySummaryHold =
    "entityType.neq.journalEntry,metadata->>granularity.is.null,metadata->>granularity.neq.daily-summary";
  const now = new Date();
  const staleBefore = new Date(
    now.getTime() - SYNC_OPERATION_STALE_IN_FLIGHT_MS
  ).toISOString();

  let pendingQuery = syncOperationTable(client)
    .select("*")
    .eq("companyId", args.companyId)
    .eq("integration", args.integration)
    .eq("status", "Pending");

  if (inclusion) {
    pendingQuery = pendingQuery.in("entityType", inclusion);
  } else if (exclusion) {
    pendingQuery = pendingQuery.not("entityType", "in", exclusion);
  }
  if (args.holdDailySummaryJournalEntries) {
    pendingQuery = pendingQuery.or(dailySummaryHold);
  }
  if (args.direction) {
    pendingQuery = pendingQuery.eq("direction", args.direction);
  }

  const pending = await pendingQuery
    .order("createdAt", { ascending: true })
    .limit(limit);

  if (pending.error) return { data: [], error: pending.error.message };

  let staleQuery = syncOperationTable(client)
    .select("*")
    .eq("companyId", args.companyId)
    .eq("integration", args.integration)
    .eq("status", "In Flight")
    .lt("lastAttemptAt", staleBefore);

  if (inclusion) {
    staleQuery = staleQuery.in("entityType", inclusion);
  } else if (exclusion) {
    staleQuery = staleQuery.not("entityType", "in", exclusion);
  }
  if (args.holdDailySummaryJournalEntries) {
    staleQuery = staleQuery.or(dailySummaryHold);
  }
  if (args.direction) {
    staleQuery = staleQuery.eq("direction", args.direction);
  }

  const stale = await staleQuery
    .order("createdAt", { ascending: true })
    .limit(limit);

  if (stale.error) return { data: [], error: stale.error.message };

  const candidates = [
    ...((pending.data ?? []) as SyncOperation[]),
    ...((stale.data ?? []) as SyncOperation[])
  ]
    .sort((a, b) => a.createdAt.localeCompare(b.createdAt))
    .slice(0, limit);

  const claimed: SyncOperation[] = [];

  for (const candidate of candidates) {
    const updated = await syncOperationTable(client)
      .update({
        status: "In Flight",
        lastAttemptAt: now.toISOString(),
        attemptCount: candidate.attemptCount + 1,
        updatedAt: now.toISOString()
      })
      .eq("id", candidate.id)
      .eq("companyId", candidate.companyId)
      .eq("status", candidate.status)
      .eq("attemptCount", candidate.attemptCount)
      .select("*");

    if (updated.error) return { data: claimed, error: updated.error.message };

    const claimedRow = ((updated.data ?? []) as SyncOperation[])[0];
    if (claimedRow) claimed.push(claimedRow);
  }

  return { data: claimed, error: null };
}

/**
 * Fetch operations by id, companyId-scoped. Claim state lives in the
 * ledger, not in caller memory, so stateless callers can re-load the
 * operations they hold by id.
 */
export async function getSyncOperationsByIds(
  client: SupabaseClient<Database>,
  args: { companyId: string; ids: string[] }
): Promise<{ data: SyncOperation[]; error: string | null }> {
  if (args.ids.length === 0) return { data: [], error: null };

  const result = await syncOperationTable(client)
    .select("*")
    .eq("companyId", args.companyId)
    .in("id", args.ids);

  if (result.error) return { data: [], error: result.error.message };
  return { data: (result.data ?? []) as SyncOperation[], error: null };
}

/**
 * Replace an operation's metadata without touching its status — for
 * callers that need to persist progression on an "In Flight" operation
 * where completeOperation/failOperation don't apply (they always
 * transition the status). Same replace-not-merge metadata contract as
 * those two.
 */
export async function updateOperationMetadata(
  client: SupabaseClient<Database>,
  args: {
    id: string;
    companyId: string;
    metadata: Record<string, unknown>;
  }
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const updated = await syncOperationTable(client)
    .update({
      metadata: args.metadata,
      updatedAt: new Date().toISOString()
    })
    .eq("id", args.id)
    .eq("companyId", args.companyId)
    .select("*")
    .single();

  if (updated.error) return { data: null, error: updated.error.message };
  return { data: updated.data as SyncOperation, error: null };
}

/**
 * Mark an operation Completed (clears any previous error fields).
 *
 * `metadata`, when provided, replaces the operation's metadata (same
 * contract as failOperation) — the daily-consolidation cron passes the
 * operation's existing metadata merged with `consolidatedInto` so member
 * operations record which batch absorbed them.
 */
export async function completeOperation(
  client: SupabaseClient<Database>,
  args: {
    id: string;
    companyId: string;
    externalId?: string;
    metadata?: Record<string, unknown>;
  }
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const now = new Date().toISOString();

  const updated = await syncOperationTable(client)
    .update({
      status: "Completed",
      completedAt: now,
      errorCode: null,
      errorMessage: null,
      updatedAt: now,
      ...(args.externalId ? { externalId: args.externalId } : {}),
      ...(args.metadata !== undefined ? { metadata: args.metadata } : {})
    })
    .eq("id", args.id)
    .eq("companyId", args.companyId)
    .select("*")
    .single();

  if (updated.error) return { data: null, error: updated.error.message };
  return { data: updated.data as SyncOperation, error: null };
}

/**
 * Mark an operation Failed — or Warning when `warning: true` (pre-flight
 * conditions the user can fix, e.g. unmapped accounts).
 *
 * `metadata`, when provided, replaces the operation's metadata — the drain
 * passes a structured failure's metadata (e.g. unmappedAccountIds) merged
 * over the operation's existing metadata.
 */
export async function failOperation(
  client: SupabaseClient<Database>,
  args: {
    id: string;
    companyId: string;
    errorCode?: string;
    errorMessage: string;
    warning?: boolean;
    metadata?: Record<string, unknown>;
  }
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const now = new Date().toISOString();

  const updated = await syncOperationTable(client)
    .update({
      status: args.warning ? "Warning" : "Failed",
      errorCode: args.errorCode ?? null,
      errorMessage: args.errorMessage,
      updatedAt: now,
      ...(args.metadata !== undefined ? { metadata: args.metadata } : {})
    })
    .eq("id", args.id)
    .eq("companyId", args.companyId)
    .select("*")
    .single();

  if (updated.error) return { data: null, error: updated.error.message };
  return { data: updated.data as SyncOperation, error: null };
}

/**
 * Accounts currently blocking a sync: the distinct account ids named by
 * parked UNMAPPED_ACCOUNTS journal operations (metadata.unmappedAccountIds),
 * minus any that have since been mapped. Feeds the "Blocking sync" rows in the
 * Account Mapping tab so an arbitrary (non-posting-default) account that held
 * up a journal is surfaced with a mapping control.
 */
export async function getAccountsBlockingSync(
  client: SupabaseClient<Database>,
  args: { companyId: string; integration: string }
): Promise<{
  data: Array<{ id: string; number: string | null; name: string }> | null;
  error: string | null;
}> {
  const ops = await syncOperationTable(client)
    .select("metadata")
    .eq("companyId", args.companyId)
    .eq("integration", args.integration)
    .eq("status", "Warning")
    .eq("errorCode", "UNMAPPED_ACCOUNTS");
  if (ops.error) return { data: null, error: ops.error.message };

  const accountIds = new Set<string>();
  for (const op of (ops.data ?? []) as Array<{
    metadata: { unmappedAccountIds?: unknown } | null;
  }>) {
    const ids = op.metadata?.unmappedAccountIds;
    if (Array.isArray(ids)) {
      for (const id of ids) {
        if (typeof id === "string" && id.length > 0) accountIds.add(id);
      }
    }
  }
  if (accountIds.size === 0) return { data: [], error: null };

  const mapped = await client
    .from("externalIntegrationMapping")
    .select("entityId")
    .eq("entityType", "account")
    .eq("integration", args.integration)
    .eq("companyId", args.companyId);
  if (mapped.error) return { data: null, error: mapped.error.message };
  const mappedSet = new Set((mapped.data ?? []).map((row) => row.entityId));

  const remaining = [...accountIds].filter((id) => !mappedSet.has(id));
  if (remaining.length === 0) return { data: [], error: null };

  const accounts = await client
    .from("account")
    .select("id, number, name")
    .in("id", remaining)
    .order("number", { ascending: true });
  if (accounts.error) return { data: null, error: accounts.error.message };
  return { data: accounts.data ?? [], error: null };
}

/**
 * Mark an operation Skipped — the drain's close-out for a syncer no-op
 * with NO remote copy behind it (shouldSync gate, config-disabled entity,
 * parked payment). Truthful-ledger rule (v4 spec, Pillar C): such a no-op
 * must never read Completed; the reason lands in errorMessage (errorCode
 * stays null so the inbox renders it neutrally, not as an error badge).
 * `completedAt` is stamped so terminal rows sort consistently. A no-op
 * whose result carries a remoteId (fast-bailout, already-linked) still
 * closes Completed — the remote copy exists, so Completed is the truth.
 */
export async function skipOperation(
  client: SupabaseClient<Database>,
  args: {
    id: string;
    companyId: string;
    reason: string;
  }
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const now = new Date().toISOString();

  const updated = await syncOperationTable(client)
    .update({
      status: "Skipped",
      errorCode: null,
      errorMessage: args.reason,
      completedAt: now,
      updatedAt: now
    })
    .eq("id", args.id)
    .eq("companyId", args.companyId)
    .select("*")
    .single();

  if (updated.error) return { data: null, error: updated.error.message };
  return { data: updated.data as SyncOperation, error: null };
}

/**
 * UI-driven status transition (Retry / Skip / Re-send) with the guard
 * table: Failed|Warning|Skipped → Pending, Failed|Warning|Pending →
 * Skipped, Completed → Pending. Anything else returns an error string.
 * Stamps updatedBy.
 */
export async function transitionOperation(
  client: SupabaseClient<Database>,
  args: {
    id: string;
    companyId: string;
    to: SyncOperationStatus;
    userId: string;
  }
): Promise<{ data: SyncOperation | null; error: string | null }> {
  const existing = await syncOperationTable(client)
    .select("*")
    .eq("id", args.id)
    .eq("companyId", args.companyId)
    .maybeSingle();

  if (existing.error) return { data: null, error: existing.error.message };
  if (!existing.data) {
    return { data: null, error: `Sync operation ${args.id} not found` };
  }

  const from = (existing.data as SyncOperation).status;
  const transitionError = getSyncOperationTransitionError(from, args.to);
  if (transitionError) return { data: null, error: transitionError };

  const updated = await syncOperationTable(client)
    .update({
      status: args.to,
      updatedBy: args.userId,
      updatedAt: new Date().toISOString()
    })
    .eq("id", args.id)
    .eq("companyId", args.companyId)
    .eq("status", from) // guard against a concurrent transition
    .select("*")
    .single();

  if (updated.error) return { data: null, error: updated.error.message };
  return { data: updated.data as SyncOperation, error: null };
}

/**
 * The rows after a cursor of the sync operation lists: newest first, `id`
 * ascending breaking `createdAt` ties, matching the index. Quoted: a
 * timestamp carries `.`, `:` and `+`, all reserved in an or-filter, and the
 * cursor comes from the URL.
 */
function afterSyncOperation(after: { createdAt: string; id: string }) {
  const createdAt = quoteFilterValue(after.createdAt);
  const id = quoteFilterValue(after.id);
  return `createdAt.lt.${createdAt},and(createdAt.eq.${createdAt},id.gt.${id})`;
}

/**
 * List sync operations for the activity/error-inbox UI, newest first, with
 * an optional status filter and offset pagination. `count` is the total for
 * the current filter. `entityType` narrows to one entity type — the
 * consolidation and reconciliation crons page journalEntry operations
 * without wading through every other entity's history. `after` reads the
 * rows after a previous page's last row instead (keyset on createdAt DESC,
 * id), like `getSyncOperationsExportPage`.
 */
export async function getSyncOperations(
  client: SupabaseClient<Database>,
  args: {
    companyId: string;
    integration: string;
    status?: SyncOperationStatus | SyncOperationStatus[];
    entityType?: string;
    limit?: number;
    offset?: number;
    after?: SyncOperationCursor;
  }
): Promise<{
  data: SyncOperation[];
  count: number | null;
  error: string | null;
}> {
  const limit = args.limit ?? DEFAULT_PAGE_SIZE;
  const offset = args.offset ?? 0;

  let query = syncOperationTable(client)
    // Only the first page counts: a later page (`after`, or an offset) does
    // not need the total again.
    .select("*", {
      count: args.after || offset > 0 ? undefined : "exact"
    })
    .eq("companyId", args.companyId)
    .eq("integration", args.integration);

  if (args.status) {
    query = Array.isArray(args.status)
      ? query.in("status", args.status)
      : query.eq("status", args.status);
  }

  if (args.entityType) {
    query = query.eq("entityType", args.entityType);
  }

  if (args.after) query = query.or(afterSyncOperation(args.after));

  // `id` ascending breaks createdAt ties, so every row has one place.
  const result = await query
    .order("createdAt", { ascending: false })
    .order("id", { ascending: true })
    .range(offset, offset + limit - 1);

  if (result.error) {
    return { data: [], count: null, error: result.error.message };
  }

  return {
    data: (result.data ?? []) as SyncOperation[],
    count: result.count ?? null,
    error: null
  };
}

/** The columns the Sync Activity CSV export reads, and nothing heavier. */
const SYNC_OPERATION_EXPORT_COLUMNS =
  "id, integration, entityType, entityId, direction, trigger, status, attemptCount, lastAttemptAt, completedAt, errorCode, errorMessage, externalId, createdAt";

export type SyncOperationExportRow = Pick<
  SyncOperation,
  | "id"
  | "integration"
  | "entityType"
  | "entityId"
  | "direction"
  | "trigger"
  | "status"
  | "attemptCount"
  | "lastAttemptAt"
  | "completedAt"
  | "errorCode"
  | "errorMessage"
  | "externalId"
  | "createdAt"
>;

/** Where the previous page ended: its last row's sort key. */
export type SyncOperationCursor = Pick<SyncOperation, "createdAt" | "id">;

/**
 * One page of sync operations for the Sync Activity CSV export, newest first,
 * under the same filters as `getSyncOperations`. Keyset-paged on
 * (createdAt DESC, id) — served by `accountingSyncOperation_createdAt_idx` —
 * so the caller can stream an unbounded history one page at a time instead
 * of holding it in memory, and page N costs the same as page 1. Pass the
 * previous page's last row as `after`; a page shorter than `limit` is the
 * last one.
 */
export async function getSyncOperationsExportPage(
  client: SupabaseClient<Database>,
  args: {
    companyId: string;
    integration: string;
    status?: SyncOperationStatus | SyncOperationStatus[];
    after?: SyncOperationCursor;
    limit: number;
  }
): Promise<{ data: SyncOperationExportRow[]; error: string | null }> {
  let query = syncOperationTable(client)
    .select(SYNC_OPERATION_EXPORT_COLUMNS)
    .eq("companyId", args.companyId)
    .eq("integration", args.integration);

  if (args.status) {
    query = Array.isArray(args.status)
      ? query.in("status", args.status)
      : query.eq("status", args.status);
  }

  if (args.after) query = query.or(afterSyncOperation(args.after));

  const result = await query
    .order("createdAt", { ascending: false })
    .order("id", { ascending: true })
    .limit(args.limit);

  if (result.error) return { data: [], error: result.error.message };
  return { data: (result.data ?? []) as SyncOperationExportRow[], error: null };
}