/**
 * Per-entity search query builders — search-completeness spec §6.
 *
 * All queries use raw sql`` template literals so they compile without needing
 * schema objects for wave-4 tables that may not exist at DB schema compile time.
 * Every query is tenant-isolated with a `tenant_id = $tenantId` predicate.
 *
 * The `highlight` field is generated via Postgres ts_headline per §6.5.
 * JOIN-augmented entities (invoice, receipt, proposal, contract) also match
 * on the joined customer name.
 */
import { sql } from 'drizzle-orm'
import type { Db } from '../client'
import type { EntityType, SearchResultItem } from '@zync/types'

// ── Types ──────────────────────────────────────────────────────────────────────

export interface EntityQueryResult {
  items: SearchResultItem[]
  total: number
}

// ── Helpers ───────────────────────────────────────────────────────────────────

/** Format a numeric/string amount as ₪N,NNN */
function formatAmount(amount: string | number | null): string | null {
  if (amount === null || amount === undefined) return null
  const n = typeof amount === 'string' ? parseFloat(amount) : amount
  if (isNaN(n)) return null
  return `₪${n.toLocaleString('he-IL')}`
}

/** Safe base64 decode for cursors (pure JS, no Buffer needed in CF workers) */
export function encodeCursor(entityType: EntityType, offset: number): string {
  const raw = `${entityType}:${offset}`
  return btoa(raw)
}

export function decodeCursor(cursor: string): { entityType: EntityType; offset: number } | null {
  try {
    const raw = atob(cursor)
    const sep = raw.indexOf(':')
    if (sep < 0) return null
    const entityType = raw.slice(0, sep) as EntityType
    const offset = parseInt(raw.slice(sep + 1), 10)
    if (isNaN(offset)) return null
    return { entityType, offset }
  } catch {
    return null
  }
}

// ── Entity query implementations ──────────────────────────────────────────────

// tasks
export async function searchTasks(
  db: Db,
  tenantId: string,
  userId: string,
  isContractor: boolean,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const contractorClause = isContractor
    ? sql` AND EXISTS (SELECT 1 FROM task_assignees ta WHERE ta.task_id = t.id AND ta.user_id = ${userId})`
    : sql``

  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM tasks t
        WHERE t.tenant_id = ${tenantId}
          AND t.search_vector @@ to_tsquery('hebrew', ${tsquery})
          ${contractorClause}`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          t.id,
          t.title,
          st.name AS status,
          p.name AS project_name,
          ts_headline('hebrew', coalesce(t.title, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM tasks t
        LEFT JOIN projects p ON p.id = t.project_id
        LEFT JOIN task_statuses st ON st.id = t.status_id
        WHERE t.tenant_id = ${tenantId}
          AND t.search_vector @@ to_tsquery('hebrew', ${tsquery})
          ${contractorClause}
        ORDER BY ts_rank_cd(t.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; title: string; status: string | null; project_name: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'task',
    label: `Task: ${r.title}`,
    primary: r.title,
    secondary: r.project_name ?? null,
    badge: r.status ?? null,
    url: `/tasks/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// projects
export async function searchProjects(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM projects p
        WHERE p.tenant_id = ${tenantId}
          AND p.search_vector @@ to_tsquery('hebrew', ${tsquery})`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          p.id,
          p.name,
          c.name AS customer_name,
          ts_headline('hebrew', coalesce(p.name, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM projects p
        LEFT JOIN customers c ON c.id = p.customer_id
        WHERE p.tenant_id = ${tenantId}
          AND p.search_vector @@ to_tsquery('hebrew', ${tsquery})
        ORDER BY ts_rank_cd(p.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; name: string; customer_name: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'project',
    label: `Project: ${r.name}`,
    primary: r.name,
    secondary: r.customer_name ?? null,
    badge: null,
    url: `/projects/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// customers
export async function searchCustomers(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM customers c
        WHERE c.tenant_id = ${tenantId}
          AND c.search_vector @@ to_tsquery('hebrew', ${tsquery})`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          c.id,
          c.name,
          c.email,
          ts_headline('hebrew', coalesce(c.name, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM customers c
        WHERE c.tenant_id = ${tenantId}
          AND c.search_vector @@ to_tsquery('hebrew', ${tsquery})
        ORDER BY ts_rank_cd(c.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; name: string; email: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'customer',
    label: `Customer: ${r.name}`,
    primary: r.name,
    secondary: r.email ?? null,
    badge: null,
    url: `/customers/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// invoices — vector on invoice_number; also join-match customer name
export async function searchInvoices(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM invoices i
        JOIN customers c ON c.id = i.customer_id
        WHERE i.tenant_id = ${tenantId}
          AND (
            i.search_vector @@ to_tsquery('hebrew', ${tsquery})
            OR to_tsvector('hebrew', coalesce(c.name, '')) @@ to_tsquery('hebrew', ${tsquery})
          )`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          i.id,
          i.invoice_number,
          i.status,
          i.total,
          c.name AS customer_name,
          ts_headline('simple', coalesce(i.invoice_number, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM invoices i
        JOIN customers c ON c.id = i.customer_id
        WHERE i.tenant_id = ${tenantId}
          AND (
            i.search_vector @@ to_tsquery('hebrew', ${tsquery})
            OR to_tsvector('hebrew', coalesce(c.name, '')) @@ to_tsquery('hebrew', ${tsquery})
          )
        ORDER BY ts_rank_cd(i.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; invoice_number: string | null; status: string | null; total: string | null; customer_name: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'invoice',
    label: `Invoice #${r.invoice_number ?? r.id}`,
    primary: r.invoice_number ?? r.id,
    secondary: formatAmount(r.total),
    badge: r.status ?? null,
    url: `/invoices/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// receipts — vector on receipt_number; also join-match customer name
export async function searchReceipts(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM receipts r
        JOIN customers c ON c.id = r.customer_id
        WHERE r.tenant_id = ${tenantId}
          AND (
            r.search_vector @@ to_tsquery('hebrew', ${tsquery})
            OR to_tsvector('hebrew', coalesce(c.name, '')) @@ to_tsquery('hebrew', ${tsquery})
          )`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          r.id,
          r.receipt_number,
          r.receipt_type,
          c.name AS customer_name,
          ts_headline('simple', coalesce(r.receipt_number, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM receipts r
        JOIN customers c ON c.id = r.customer_id
        WHERE r.tenant_id = ${tenantId}
          AND (
            r.search_vector @@ to_tsquery('hebrew', ${tsquery})
            OR to_tsvector('hebrew', coalesce(c.name, '')) @@ to_tsquery('hebrew', ${tsquery})
          )
        ORDER BY ts_rank_cd(r.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; receipt_number: string | null; receipt_type: string | null; customer_name: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'receipt',
    label: `Receipt #${r.receipt_number ?? r.id}`,
    primary: r.receipt_number ?? r.id,
    secondary: r.customer_name ?? null,
    badge: r.receipt_type ?? null,
    url: `/receipts/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// expenses
export async function searchExpenses(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM expenses e
        WHERE e.tenant_id = ${tenantId}
          AND e.search_vector @@ to_tsquery('hebrew', ${tsquery})`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          e.id,
          e.vendor_name,
          e.notes,
          ts_headline('hebrew', coalesce(e.vendor_name, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM expenses e
        WHERE e.tenant_id = ${tenantId}
          AND e.search_vector @@ to_tsquery('hebrew', ${tsquery})
        ORDER BY ts_rank_cd(e.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; vendor_name: string | null; notes: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'expense',
    label: `Expense: ${r.vendor_name ?? r.id}`,
    primary: r.vendor_name ?? r.id,
    secondary: r.notes ?? null,
    badge: null,
    url: `/expenses/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// vendors
export async function searchVendors(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM vendors v
        WHERE v.tenant_id = ${tenantId}
          AND v.search_vector @@ to_tsquery('hebrew', ${tsquery})`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          v.id,
          v.name,
          v.email,
          v.phone,
          ts_headline('hebrew', coalesce(v.name, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM vendors v
        WHERE v.tenant_id = ${tenantId}
          AND v.search_vector @@ to_tsquery('hebrew', ${tsquery})
        ORDER BY ts_rank_cd(v.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; name: string; email: string | null; phone: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'vendor',
    label: `Vendor: ${r.name}`,
    primary: r.name,
    secondary: r.email ?? r.phone ?? null,
    badge: null,
    url: `/vendors/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// leads
export async function searchLeads(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM leads l
        WHERE l.tenant_id = ${tenantId}
          AND l.search_vector @@ to_tsquery('hebrew', ${tsquery})`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          l.id,
          l.name,
          l.company,
          l.stage,
          ts_headline('hebrew', coalesce(l.name, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM leads l
        WHERE l.tenant_id = ${tenantId}
          AND l.search_vector @@ to_tsquery('hebrew', ${tsquery})
        ORDER BY ts_rank_cd(l.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; name: string; company: string | null; stage: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'lead',
    label: `Lead: ${r.name}`,
    primary: r.name,
    secondary: r.company ?? null,
    badge: r.stage ?? null,
    url: `/marketing/leads/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// proposals — vector on name; also join-match customer name
export async function searchProposals(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM proposals pr
        JOIN customers c ON c.id = pr.customer_id
        WHERE pr.tenant_id = ${tenantId}
          AND (
            pr.search_vector @@ to_tsquery('hebrew', ${tsquery})
            OR to_tsvector('hebrew', coalesce(c.name, '')) @@ to_tsquery('hebrew', ${tsquery})
          )`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          pr.id,
          pr.name,
          pr.status,
          c.name AS customer_name,
          ts_headline('hebrew', coalesce(pr.name, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM proposals pr
        JOIN customers c ON c.id = pr.customer_id
        WHERE pr.tenant_id = ${tenantId}
          AND (
            pr.search_vector @@ to_tsquery('hebrew', ${tsquery})
            OR to_tsvector('hebrew', coalesce(c.name, '')) @@ to_tsquery('hebrew', ${tsquery})
          )
        ORDER BY ts_rank_cd(pr.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; name: string; status: string | null; customer_name: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'proposal',
    label: `Proposal: ${r.name}`,
    primary: r.name,
    secondary: r.customer_name ?? null,
    badge: r.status ?? null,
    url: `/proposals/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// contracts — vector on title; also join-match customer name
export async function searchContracts(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM contracts ct
        JOIN customers c ON c.id = ct.customer_id
        WHERE ct.tenant_id = ${tenantId}
          AND (
            ct.search_vector @@ to_tsquery('hebrew', ${tsquery})
            OR to_tsvector('hebrew', coalesce(c.name, '')) @@ to_tsquery('hebrew', ${tsquery})
          )`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          ct.id,
          ct.title,
          ct.status,
          c.name AS customer_name,
          ts_headline('hebrew', coalesce(ct.title, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM contracts ct
        JOIN customers c ON c.id = ct.customer_id
        WHERE ct.tenant_id = ${tenantId}
          AND (
            ct.search_vector @@ to_tsquery('hebrew', ${tsquery})
            OR to_tsvector('hebrew', coalesce(c.name, '')) @@ to_tsquery('hebrew', ${tsquery})
          )
        ORDER BY ts_rank_cd(ct.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; title: string; status: string | null; customer_name: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'contract',
    label: `Contract: ${r.title}`,
    primary: r.title,
    secondary: r.customer_name ?? null,
    badge: r.status ?? null,
    url: `/contracts/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// contractors
export async function searchContractors(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM contractors co
        WHERE co.tenant_id = ${tenantId}
          AND co.search_vector @@ to_tsquery('hebrew', ${tsquery})`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          co.id,
          co.name,
          co.email,
          ts_headline('hebrew', coalesce(co.name, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM contractors co
        WHERE co.tenant_id = ${tenantId}
          AND co.search_vector @@ to_tsquery('hebrew', ${tsquery})
        ORDER BY ts_rank_cd(co.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; name: string; email: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'contractor',
    label: `Contractor: ${r.name}`,
    primary: r.name,
    secondary: r.email ?? null,
    badge: null,
    url: `/contractors/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// kb_articles
export async function searchKbArticles(
  db: Db,
  tenantId: string,
  role: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  // VIEWER and CONTRACTOR only see PUBLISHED articles; OWNER/ADMIN/MEMBER see all
  const publishedFilter =
    role === 'VIEWER' || role === 'CONTRACTOR'
      ? sql` AND ka.status = 'PUBLISHED'`
      : sql``

  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM kb_articles ka
        WHERE ka.tenant_id = ${tenantId}
          AND ka.search_vector @@ to_tsquery('hebrew', ${tsquery})
          ${publishedFilter}`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          ka.id,
          ka.title,
          ts_headline('hebrew', coalesce(ka.title, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM kb_articles ka
        WHERE ka.tenant_id = ${tenantId}
          AND ka.search_vector @@ to_tsquery('hebrew', ${tsquery})
          ${publishedFilter}
        ORDER BY ts_rank_cd(ka.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; title: string; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'kb_article',
    label: `Article: ${r.title}`,
    primary: r.title,
    secondary: null,
    badge: null,
    url: `/kb/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// support_tickets
export async function searchSupportTickets(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM tickets st
        WHERE st.tenant_id = ${tenantId}
          AND st.search_vector @@ to_tsquery('hebrew', ${tsquery})`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          st.id,
          st.title,
          st.status,
          c.name AS customer_name,
          ts_headline('hebrew', coalesce(st.title, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM tickets st
        LEFT JOIN customers c ON c.id = st.customer_id
        WHERE st.tenant_id = ${tenantId}
          AND st.search_vector @@ to_tsquery('hebrew', ${tsquery})
        ORDER BY ts_rank_cd(st.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; title: string; status: string | null; customer_name: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'support_ticket',
    label: `Ticket: ${r.title}`,
    primary: r.title,
    secondary: r.customer_name ?? null,
    badge: r.status ?? null,
    url: `/support/${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}

// team_members — joined via tenant_memberships
// NOTE: users.name maps to 'full_name' in spec SQL but the actual column is 'name'
export async function searchTeamMembers(
  db: Db,
  tenantId: string,
  tsquery: string,
  limit: number,
  offset: number,
): Promise<EntityQueryResult> {
  const countResult = await db.execute(
    sql`SELECT COUNT(*)::int AS total
        FROM users u
        JOIN tenant_memberships tm ON tm.user_id = u.id AND tm.tenant_id = ${tenantId}
        WHERE u.search_vector @@ to_tsquery('hebrew', ${tsquery})`,
  )
  const total = ((countResult[0] as { total?: number })?.total) ?? 0

  const rows = await db.execute(
    sql`SELECT
          u.id,
          u.name,
          u.email,
          r.name AS role,
          ts_headline('hebrew', coalesce(u.name, ''), to_tsquery('hebrew', ${tsquery}),
            'StartSel=<mark>, StopSel=</mark>, MaxWords=10, MinWords=5, ShortWord=2') AS highlight
        FROM users u
        JOIN tenant_memberships tm ON tm.user_id = u.id AND tm.tenant_id = ${tenantId}
        LEFT JOIN roles r ON r.id = tm.role_id
        WHERE u.search_vector @@ to_tsquery('hebrew', ${tsquery})
        ORDER BY ts_rank_cd(u.search_vector, to_tsquery('hebrew', ${tsquery})) DESC
        LIMIT ${limit} OFFSET ${offset}`,
  )

  type Row = { id: string; name: string | null; email: string; role: string | null; highlight: string | null }
  const items: SearchResultItem[] = (rows as unknown as Row[]).map((r) => ({
    id: r.id,
    type: 'team_member',
    label: `Member: ${r.name ?? r.email}`,
    primary: r.name ?? r.email,
    secondary: r.email,
    badge: r.role ? r.role.toUpperCase() : null,
    url: `/settings/users?highlight=${r.id}`,
    highlight: r.highlight ?? null,
  }))

  return { items, total }
}