/**
 * Customer query helpers — customers module.
 *
 * All helpers are tenant-filtered: every statement carries a `tenant_id`
 * WHERE clause. Route files MUST NOT import raw Drizzle tables; they import
 * from this module via `@zync/db/queries`.
 *
 * Cursor encoding: base64url of JSON `{ created_at: string, id: string }`.
 * Lists ordered `created_at DESC, id DESC` for stable pagination.
 * `listCustomers` and `listCommunications` clamp `limit` to ≤100 (hard max).
 */
import { and, eq, lt, or, sql, asc, desc, count } from 'drizzle-orm'
import type { Db } from '../client'
import { ilikeSubstringPattern } from '../utils/escape-like'
import {
  customers,
  customerContacts,
  customerPortalUsers,
  customerCommunications,
  invoices,
  projects,
} from '../schema'
import type { NewCustomer, NewCustomerContact } from '../schema'
import { auditLog } from './_audit-forward'
import { captureEntityChange, computeDiff } from './entity-history'

/** Optional actor context for operational-audit-trail diff capture. */
export interface CustomerActorContext {
  actorName?: string | null
  actorEmail?: string | null
  ipAddress?: string | null
}

// ── Public interfaces ─────────────────────────────────────────────────────────

export interface Address {
  street?: string
  city?: string
  state?: string
  zip?: string
  country?: string
}

export interface Customer {
  id: string
  tenantId: string
  name: string
  taxId: string | null
  company: string | null
  email: string | null
  phone: string | null
  address: Address | null
  notes: string | null
  status: 'active' | 'archived'
  createdAt: string
  updatedAt: string
}

export interface CustomerContact {
  id: string
  customerId: string
  tenantId: string
  name: string
  email: string
  phone: string | null
  role: string | null
  isPrimary: boolean
  createdAt: string
}

export interface CustomerPortalUser {
  id: string
  customerId: string
  tenantId: string
  contactId: string
  userId: string
  portalRole: string
  status: 'pending' | 'active' | 'frozen'
  invitedAt: string | null
  acceptedAt: string | null
}

export interface CustomerCommunication {
  id: string
  tenantId: string
  customerId: string
  direction: 'outbound' | 'inbound' | 'internal'
  channel: 'email' | 'telegram' | 'ticket' | 'note' | 'system'
  subject: string | null
  body: string | null
  fromAddress: string | null
  toAddress: string | null
  relatedId: string | null
  relatedType: string | null
  sentAt: string
  createdBy: string | null
}

export interface CustomerStats {
  totalInvoices: number
  totalPaid: number
  outstandingBalance: number
  openProjects: number
  activeProjects: number
  openInvoices: number
}

export interface CustomerListPage {
  items: Customer[]
  nextCursor: string | null
  total: number
}

export class OpenInvoicesError extends Error {
  constructor() {
    super('Customer has open invoices and cannot be archived')
    this.name = 'OpenInvoicesError'
  }
}

// ── Cursor helpers ─────────────────────────────────────────────────────────────

function encodeCursor(createdAt: string | Date, id: string): string {
  const payload = JSON.stringify({ created_at: String(createdAt), id })
  return Buffer.from(payload).toString('base64url')
}

function decodeCursor(cursor: string): { created_at: string; id: string } | null {
  try {
    const raw = Buffer.from(cursor, 'base64url').toString('utf8')
    const parsed = JSON.parse(raw) as { created_at: string; id: string }
    if (typeof parsed.created_at !== 'string' || typeof parsed.id !== 'string') return null
    return parsed
  } catch {
    return null
  }
}

// ── Row mappers ───────────────────────────────────────────────────────────────

function mapCustomer(row: typeof customers.$inferSelect): Customer {
  return {
    id: row.id,
    tenantId: row.tenantId,
    name: row.name,
    taxId: row.taxId ?? null,
    company: row.company ?? null,
    email: row.email ?? null,
    phone: row.phone ?? null,
    address: row.address ?? null,
    notes: row.notes ?? null,
    status: row.status as 'active' | 'archived',
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
  }
}

function mapContact(row: typeof customerContacts.$inferSelect): CustomerContact {
  return {
    id: row.id,
    customerId: row.customerId,
    tenantId: row.tenantId,
    name: row.name,
    email: row.email,
    phone: row.phone ?? null,
    role: row.role ?? null,
    isPrimary: row.isPrimary,
    createdAt: row.createdAt.toISOString(),
  }
}

function mapPortalUser(row: typeof customerPortalUsers.$inferSelect): CustomerPortalUser {
  const status =
    row.status === 'active' && !row.acceptedAt
      ? 'pending'
      : (row.status as 'active' | 'frozen')

  return {
    id: row.id,
    customerId: row.customerId,
    tenantId: row.tenantId,
    contactId: row.contactId,
    userId: row.userId,
    portalRole: row.portalRole,
    status,
    invitedAt: row.invitedAt?.toISOString() ?? null,
    acceptedAt: row.acceptedAt?.toISOString() ?? null,
  }
}

function mapCommunication(row: typeof customerCommunications.$inferSelect): CustomerCommunication {
  return {
    id: row.id,
    tenantId: row.tenantId,
    customerId: row.customerId,
    direction: row.direction as 'outbound' | 'inbound' | 'internal',
    channel: row.channel as 'email' | 'telegram' | 'ticket' | 'note' | 'system',
    subject: row.subject ?? null,
    body: row.body ?? null,
    fromAddress: row.fromAddress ?? null,
    toAddress: row.toAddress ?? null,
    relatedId: row.relatedId ?? null,
    relatedType: row.relatedType ?? null,
    sentAt: row.sentAt.toISOString(),
    createdBy: row.createdBy ?? null,
  }
}

// ── Customer queries ──────────────────────────────────────────────────────────

export async function listCustomers(
  db: Db,
  tenantId: string,
  opts: {
    cursor?: string
    limit?: number
    status?: 'active' | 'archived'
    search?: string
  } = {},
): Promise<CustomerListPage> {
  const limit = Math.min(opts.limit ?? 50, 100)

  // Total count (no cursor applied for total)
  let totalQuery = db
    .select({ count: count() })
    .from(customers)
    .where(
      and(
        eq(customers.tenantId, tenantId),
        opts.status ? eq(customers.status, opts.status) : undefined,
        opts.search
          ? sql`(${customers.name} ILIKE ${ilikeSubstringPattern(opts.search)} OR ${customers.company} ILIKE ${ilikeSubstringPattern(opts.search)} OR ${customers.email} ILIKE ${ilikeSubstringPattern(opts.search)})`
          : undefined,
      ),
    )

  const totalRows = await totalQuery
  const total = totalRows[0]?.count ?? 0

  // Build cursor condition
  let cursorCondition = undefined
  if (opts.cursor) {
    const decoded = decodeCursor(opts.cursor)
    if (decoded) {
      cursorCondition = or(
        lt(customers.createdAt, new Date(decoded.created_at)),
        and(
          eq(customers.createdAt, new Date(decoded.created_at)),
          lt(customers.id, decoded.id),
        ),
      )
    }
  }

  const rows = await db
    .select()
    .from(customers)
    .where(
      and(
        eq(customers.tenantId, tenantId),
        opts.status ? eq(customers.status, opts.status) : undefined,
        opts.search
          ? sql`(${customers.name} ILIKE ${ilikeSubstringPattern(opts.search)} OR ${customers.company} ILIKE ${ilikeSubstringPattern(opts.search)} OR ${customers.email} ILIKE ${ilikeSubstringPattern(opts.search)})`
          : undefined,
        cursorCondition,
      ),
    )
    .orderBy(desc(customers.createdAt), desc(customers.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const items = hasMore ? rows.slice(0, limit) : rows
  const lastItem = items[items.length - 1]
  const nextCursor =
    hasMore && lastItem ? encodeCursor(lastItem.createdAt, lastItem.id) : null

  return {
    items: items.map(mapCustomer),
    nextCursor,
    total: Number(total),
  }
}

export async function getCustomerWithStats(
  db: Db,
  tenantId: string,
  id: string,
): Promise<{ customer: Customer; stats: CustomerStats } | null> {
  const [row] = await db
    .select()
    .from(customers)
    .where(and(eq(customers.tenantId, tenantId), eq(customers.id, id)))
    .limit(1)

  if (!row) return null

  const [invoiceStats, projectStats] = await Promise.all([
    db
      .select({
        totalInvoices: count(),
        totalPaid: sql<string>`coalesce(sum(${invoices.amountPaid}), 0)`,
        outstandingBalance: sql<string>`coalesce(sum(greatest(${invoices.total} - ${invoices.amountPaid}, 0)), 0)`,
        openInvoices:
          sql<number>`count(*) filter (where ${invoices.status} not in ('PAID', 'VOID', 'WRITTEN_OFF', 'BAD_DEBT'))`,
      })
      .from(invoices)
      .where(and(eq(invoices.tenantId, tenantId), eq(invoices.customerId, id))),
    db
      .select({
        openProjects:
          sql<number>`count(*) filter (where ${projects.status} in ('active', 'on_hold'))`,
        activeProjects:
          sql<number>`count(*) filter (where ${projects.status} = 'active')`,
      })
      .from(projects)
      .where(and(eq(projects.tenantId, tenantId), eq(projects.customerId, id))),
  ])

  const stats: CustomerStats = {
    totalInvoices: Number(invoiceStats?.[0]?.totalInvoices ?? 0),
    totalPaid: Number(invoiceStats?.[0]?.totalPaid ?? 0),
    outstandingBalance: Number(invoiceStats?.[0]?.outstandingBalance ?? 0),
    openProjects: Number(projectStats?.[0]?.openProjects ?? 0),
    activeProjects: Number(projectStats?.[0]?.activeProjects ?? 0),
    openInvoices: Number(invoiceStats?.[0]?.openInvoices ?? 0),
  }

  return { customer: mapCustomer(row), stats }
}

export async function createCustomer(
  db: Db,
  tenantId: string,
  input: Omit<NewCustomer, 'id' | 'tenantId' | 'status' | 'createdAt' | 'updatedAt'> & {
    status?: 'active'
  },
  actorId?: string,
): Promise<Customer> {
  return db.transaction(async (tx) => {
    const [row] = await tx
      .insert(customers)
      .values({
        tenantId,
        name: input.name,
        company: input.company ?? null,
        email: input.email ?? null,
        phone: input.phone ?? null,
        address: input.address ?? null,
        notes: input.notes ?? null,
        status: input.status ?? 'active',
      })
      .returning()

    if (!row) throw new Error('Customer not found')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'customer',
      entityId: row.id,
      action: 'customer.created',
      changes: null,
    })

    return mapCustomer(row)
  })
}

export async function updateCustomer(
  db: Db,
  tenantId: string,
  id: string,
  patch: Partial<Pick<Customer, 'name' | 'company' | 'email' | 'phone' | 'address' | 'notes'>>,
  actorId?: string,
  actorCtx?: CustomerActorContext,
): Promise<Customer> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(customers)
      .where(and(eq(customers.tenantId, tenantId), eq(customers.id, id)))
      .limit(1)

    if (!existing) throw new Error('Customer not found')

    const [row] = await tx
      .update(customers)
      .set({
        ...(patch.name !== undefined && { name: patch.name }),
        ...(patch.company !== undefined && { company: patch.company }),
        ...(patch.email !== undefined && { email: patch.email }),
        ...(patch.phone !== undefined && { phone: patch.phone }),
        ...(patch.address !== undefined && { address: patch.address }),
        ...(patch.notes !== undefined && { notes: patch.notes }),
        updatedAt: new Date(),
      })
      .where(and(eq(customers.tenantId, tenantId), eq(customers.id, id)))
      .returning()

    if (!row) throw new Error('Customer not found')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'customer',
      entityId: id,
      action: 'customer.updated',
      changes: null,
    })

    // operational-audit-trail: field diff
    const beforeRecord: Record<string, unknown> = {
      name: existing.name, company: existing.company, email: existing.email,
      phone: existing.phone, notes: existing.notes,
    }
    const afterRecord: Record<string, unknown> = {
      name: row.name, company: row.company, email: row.email,
      phone: row.phone, notes: row.notes,
    }
    const diff = computeDiff(beforeRecord, afterRecord)
    if (diff) {
      await captureEntityChange({
        tx, tenantId, userId: actorId ?? null,
        actorName: actorCtx?.actorName ?? null,
        actorEmail: actorCtx?.actorEmail ?? null,
        eventType: 'customer.field_updated',
        entityType: 'customer', entityId: id,
        entityLabel: row.name,
        beforeState: diff.before, afterState: diff.after,
        ipAddress: actorCtx?.ipAddress ?? null,
      })
    }

    return mapCustomer(row)
  })
}

export async function archiveCustomer(
  db: Db,
  tenantId: string,
  id: string,
  actorId?: string,
  actorCtx?: CustomerActorContext,
): Promise<Customer> {
  const openInvoiceRows = await db
    .select({ count: count() })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.customerId, id),
        sql`${invoices.status} not in ('PAID', 'VOID', 'WRITTEN_OFF', 'BAD_DEBT')`,
      ),
    )
  const openCount = Number(openInvoiceRows[0]?.count ?? 0)
  if (openCount > 0) throw new OpenInvoicesError()

  return db.transaction(async (tx) => {
    const [row] = await tx
      .update(customers)
      .set({ status: 'archived', updatedAt: new Date() })
      .where(and(eq(customers.tenantId, tenantId), eq(customers.id, id)))
      .returning()

    if (!row) throw new Error('Customer not found')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'customer',
      entityId: id,
      action: 'customer.archived',
      changes: null,
    })

    // operational-audit-trail: status change
    await captureEntityChange({
      tx, tenantId, userId: actorId ?? null,
      actorName: actorCtx?.actorName ?? null,
      actorEmail: actorCtx?.actorEmail ?? null,
      eventType: 'customer.status_changed',
      entityType: 'customer', entityId: id,
      entityLabel: row.name,
      beforeState: { status: 'active' },
      afterState: { status: 'archived' },
      ipAddress: actorCtx?.ipAddress ?? null,
    })

    return mapCustomer(row)
  })
}

// ── Contact queries ───────────────────────────────────────────────────────────

export async function getContactById(
  db: Db,
  tenantId: string,
  contactId: string,
): Promise<CustomerContact | null> {
  const [row] = await db
    .select()
    .from(customerContacts)
    .where(and(eq(customerContacts.tenantId, tenantId), eq(customerContacts.id, contactId)))
    .limit(1)

  return row ? mapContact(row) : null
}

export async function listContacts(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<CustomerContact[]> {
  const rows = await db
    .select()
    .from(customerContacts)
    .where(
      and(eq(customerContacts.tenantId, tenantId), eq(customerContacts.customerId, customerId)),
    )
    .orderBy(desc(customerContacts.isPrimary), asc(customerContacts.createdAt))

  return rows.map(mapContact)
}

export async function addContact(
  db: Db,
  tenantId: string,
  customerId: string,
  input: Omit<NewCustomerContact, 'id' | 'customerId' | 'tenantId' | 'createdAt'>,
  actorId?: string,
  actorCtx?: CustomerActorContext,
): Promise<CustomerContact> {
  return db.transaction(async (tx) => {
    // If marking as primary, clear previous primary first
    if (input.isPrimary) {
      await tx
        .update(customerContacts)
        .set({ isPrimary: false })
        .where(
          and(
            eq(customerContacts.tenantId, tenantId),
            eq(customerContacts.customerId, customerId),
            eq(customerContacts.isPrimary, true),
          ),
        )
    }

    const [row] = await tx
      .insert(customerContacts)
      .values({
        customerId,
        tenantId,
        name: input.name,
        email: input.email,
        phone: input.phone ?? null,
        role: input.role ?? null,
        isPrimary: input.isPrimary ?? false,
      })
      .returning()

    if (!row) throw new Error('Contact not found')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'customer_contact',
      entityId: row.id,
      action: 'customer_contact.created',
      changes: null,
    })

    // operational-audit-trail: contact create
    await captureEntityChange({
      tx, tenantId, userId: actorId ?? null,
      actorName: actorCtx?.actorName ?? null,
      actorEmail: actorCtx?.actorEmail ?? null,
      eventType: 'contact.created',
      entityType: 'customer_contact', entityId: row.id,
      entityLabel: row.name,
      beforeState: null,
      afterState: { name: row.name, email: row.email, customerId },
      ipAddress: actorCtx?.ipAddress ?? null,
    })

    return mapContact(row)
  })
}

export async function updateContact(
  db: Db,
  tenantId: string,
  customerId: string,
  contactId: string,
  patch: Partial<Omit<CustomerContact, 'id' | 'customerId' | 'tenantId' | 'createdAt'>>,
  actorId?: string,
  actorCtx?: CustomerActorContext,
): Promise<CustomerContact> {
  return db.transaction(async (tx) => {
    // If marking as primary, clear previous primary atomically
    if (patch.isPrimary === true) {
      await tx
        .update(customerContacts)
        .set({ isPrimary: false })
        .where(
          and(
            eq(customerContacts.tenantId, tenantId),
            eq(customerContacts.customerId, customerId),
            eq(customerContacts.isPrimary, true),
          ),
        )
    }

    const setValues: Partial<typeof customerContacts.$inferInsert> = {}
    if (patch.name !== undefined) setValues.name = patch.name
    if (patch.email !== undefined) setValues.email = patch.email
    if (patch.phone !== undefined) setValues.phone = patch.phone
    if (patch.role !== undefined) setValues.role = patch.role
    if (patch.isPrimary !== undefined) setValues.isPrimary = patch.isPrimary

    const [row] = await tx
      .update(customerContacts)
      .set(setValues)
      .where(
        and(
          eq(customerContacts.tenantId, tenantId),
          eq(customerContacts.customerId, customerId),
          eq(customerContacts.id, contactId),
        ),
      )
      .returning()

    if (!row) throw new Error('Contact not found')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'customer_contact',
      entityId: contactId,
      action: 'customer_contact.updated',
      changes: null,
    })

    // operational-audit-trail: contact field diff
    await captureEntityChange({
      tx, tenantId, userId: actorId ?? null,
      actorName: actorCtx?.actorName ?? null,
      actorEmail: actorCtx?.actorEmail ?? null,
      eventType: 'contact.updated',
      entityType: 'customer_contact', entityId: contactId,
      entityLabel: row.name,
      beforeState: null,
      afterState: { name: row.name, email: row.email, phone: row.phone },
      ipAddress: actorCtx?.ipAddress ?? null,
    })

    return mapContact(row)
  })
}

export async function removeContact(
  db: Db,
  tenantId: string,
  customerId: string,
  contactId: string,
  actorId?: string,
  actorCtx?: CustomerActorContext,
): Promise<void> {
  return db.transaction(async (tx) => {
    await tx
      .delete(customerContacts)
      .where(
        and(
          eq(customerContacts.tenantId, tenantId),
          eq(customerContacts.customerId, customerId),
          eq(customerContacts.id, contactId),
        ),
      )

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'customer_contact',
      entityId: contactId,
      action: 'customer_contact.deleted',
      changes: null,
    })

    // operational-audit-trail: contact delete
    await captureEntityChange({
      tx, tenantId, userId: actorId ?? null,
      actorName: actorCtx?.actorName ?? null,
      actorEmail: actorCtx?.actorEmail ?? null,
      eventType: 'contact.deleted',
      entityType: 'customer_contact', entityId: contactId,
      entityLabel: null,
      beforeState: { customerId },
      afterState: null,
      ipAddress: actorCtx?.ipAddress ?? null,
    })
  })
}

// ── Portal user queries ───────────────────────────────────────────────────────

export async function getPortalUserById(
  db: Db,
  tenantId: string,
  portalUserId: string,
): Promise<CustomerPortalUser | null> {
  const rows = await db
    .select()
    .from(customerPortalUsers)
    .where(
      and(
        eq(customerPortalUsers.tenantId, tenantId),
        eq(customerPortalUsers.id, portalUserId),
      ),
    )
    .limit(1)

  const row = rows[0]
  return row ? mapPortalUser(row) : null
}

export async function listPortalUsers(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<CustomerPortalUser[]> {
  const rows = await db
    .select()
    .from(customerPortalUsers)
    .where(
      and(
        eq(customerPortalUsers.tenantId, tenantId),
        eq(customerPortalUsers.customerId, customerId),
      ),
    )
    .orderBy(asc(customerPortalUsers.createdAt))

  return rows.map(mapPortalUser)
}

export async function createPortalUser(
  db: Db,
  tenantId: string,
  customerId: string,
  input: {
    contactId: string
    userId: string
    portalRole?: string
    invitedAt?: Date
  },
  actorId?: string,
): Promise<CustomerPortalUser> {
  return db.transaction(async (tx) => {
    const [row] = await tx
      .insert(customerPortalUsers)
      .values({
        customerId,
        tenantId,
        contactId: input.contactId,
        userId: input.userId,
        portalRole: input.portalRole ?? 'customer_viewer',
        status: 'active',
        invitedAt: input.invitedAt ?? new Date(),
      })
      .returning()

    if (!row) throw new Error('Portal user not found')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'customer_portal_user',
      entityId: row.id,
      action: 'customer_portal_user.created',
      changes: null,
    })

    return mapPortalUser(row)
  })
}

export async function setPortalUserStatus(
  db: Db,
  tenantId: string,
  customerId: string,
  portalUserId: string,
  status: 'active' | 'frozen',
  actorId?: string,
): Promise<CustomerPortalUser> {
  return db.transaction(async (tx) => {
    const [row] = await tx
      .update(customerPortalUsers)
      .set({ status })
      .where(
        and(
          eq(customerPortalUsers.tenantId, tenantId),
          eq(customerPortalUsers.customerId, customerId),
          eq(customerPortalUsers.id, portalUserId),
        ),
      )
      .returning()

    if (!row) throw new Error('Portal user not found')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: actorId ?? null,
      actorType: actorId ? 'user' : 'system',
      entityType: 'customer_portal_user',
      entityId: portalUserId,
      action: status === 'frozen' ? 'customer_portal_user.frozen' : 'customer_portal_user.unfrozen',
      changes: null,
    })

    return mapPortalUser(row)
  })
}

// ── Communications queries ────────────────────────────────────────────────────

export async function listCommunications(
  db: Db,
  tenantId: string,
  customerId: string,
  opts: { cursor?: string; limit?: number } = {},
): Promise<{ items: CustomerCommunication[]; nextCursor: string | null }> {
  const limit = Math.min(opts.limit ?? 50, 100)

  let cursorCondition = undefined
  if (opts.cursor) {
    const decoded = decodeCursor(opts.cursor)
    if (decoded) {
      cursorCondition = or(
        lt(customerCommunications.sentAt, new Date(decoded.created_at)),
        and(
          eq(customerCommunications.sentAt, new Date(decoded.created_at)),
          lt(customerCommunications.id, decoded.id),
        ),
      )
    }
  }

  const rows = await db
    .select()
    .from(customerCommunications)
    .where(
      and(
        eq(customerCommunications.tenantId, tenantId),
        eq(customerCommunications.customerId, customerId),
        cursorCondition,
      ),
    )
    .orderBy(desc(customerCommunications.sentAt), desc(customerCommunications.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const items = hasMore ? rows.slice(0, limit) : rows
  const lastItem = items[items.length - 1]
  const nextCursor =
    hasMore && lastItem ? encodeCursor(lastItem.sentAt, lastItem.id) : null

  return { items: items.map(mapCommunication), nextCursor }
}

export async function appendCustomerCommunication(
  db: Db,
  tenantId: string,
  customerId: string,
  input: Omit<CustomerCommunication, 'id' | 'tenantId' | 'customerId' | 'sentAt'> & {
    sentAt?: string
  },
): Promise<CustomerCommunication> {
  const [row] = await db
    .insert(customerCommunications)
    .values({
      tenantId,
      customerId,
      direction: input.direction,
      channel: input.channel,
      subject: input.subject ?? null,
      body: input.body ?? null,
      fromAddress: input.fromAddress ?? null,
      toAddress: input.toAddress ?? null,
      relatedId: input.relatedId ?? null,
      relatedType: input.relatedType ?? null,
      sentAt: input.sentAt ? new Date(input.sentAt) : new Date(),
      createdBy: input.createdBy ?? null,
    })
    .returning()

  if (!row) throw new Error('Communication not found')

  return mapCommunication(row)
}