/**
 * Wave-aware FTS source registry — app-shell (Task 9).
 *
 * Each source wraps its query in a 42P01 guard (undefined_table) so the route
 * degrades gracefully when a later-wave table doesn't exist yet.
 * At wave 3, only `customers` is guaranteed present.
 *
 * IMPORTANT: Do NOT import Drizzle schema objects for tables that may not exist
 * at wave 3 (tasks, projects, invoices, tickets, kb_articles). All queries
 * use raw sql`` with table names as string literals so they compile regardless
 * of whether those schema files exist. The 42P01 guard handles the runtime case.
 *
 * FTS configuration: 'simple' (no stemming) so Hebrew tokens match literally.
 * This matches the GIN index expression on customers — the planner will use it.
 */
import { sql } from '@zync/db'
import type { Db } from '@zync/db/queries'

export interface SearchResult {
  id: string
  label: string
  description?: string
  url: string
}

export interface SearchResponse {
  tasks: SearchResult[]
  projects: SearchResult[]
  invoices: SearchResult[]
  customers: SearchResult[]
  tickets: SearchResult[]
  articles: SearchResult[]
}

interface SearchSource {
  key: keyof SearchResponse
  query: (db: Db, tenantId: string, q: string) => Promise<SearchResult[]>
}

// ── 42P01 guard ───────────────────────────────────────────────────────────────

async function guardedQuery<T>(fn: () => Promise<T[]>): Promise<T[]> {
  try {
    return await fn()
  } catch (err: unknown) {
    // Postgres SQLSTATE 42P01: undefined_table
    if (
      err instanceof Error &&
      (err.message.includes('42P01') || err.message.includes('undefined_table') ||
       // node-postgres surfaces it under .code
       ('code' in err && (err as { code?: string }).code === '42P01'))
    ) {
      return []
    }
    throw err
  }
}

// ── Source: customers (wave 2 — guaranteed present) ───────────────────────────

async function searchCustomers(db: Db, tenantId: string, q: string): Promise<SearchResult[]> {
  const result = await db.execute(
    sql`
      SELECT id, name, email, company
      FROM customers
      WHERE tenant_id = ${tenantId}
        AND to_tsvector('simple',
              coalesce(name, '') || ' ' ||
              coalesce(email, '') || ' ' ||
              coalesce(company, ''))
            @@ plainto_tsquery('simple', ${q})
      LIMIT 5
    `,
  )
  const rows = result as unknown as Array<{ id: string; name: string; email: string | null; company: string | null }>
  return rows.map((r) => ({
    id: r.id,
    label: r.name,
    description: r.company ?? r.email ?? undefined,
    url: `/customers/${r.id}`,
  }))
}

// ── Source: tasks (wave 3+ — guarded) ────────────────────────────────────────

async function searchTasks(db: Db, tenantId: string, q: string): Promise<SearchResult[]> {
  return guardedQuery(async () => {
    const result = await db.execute(
      sql`
        SELECT id, title
        FROM tasks
        WHERE tenant_id = ${tenantId}
          AND to_tsvector('simple', coalesce(title, ''))
              @@ plainto_tsquery('simple', ${q})
        LIMIT 5
      `,
    )
    const rows = result as unknown as Array<{ id: string; title: string }>
    return rows.map((r) => ({
      id: r.id,
      label: r.title,
      url: `/tasks/${r.id}`,
    }))
  })
}

// ── Source: projects (wave 3+ — guarded) ─────────────────────────────────────

async function searchProjects(db: Db, tenantId: string, q: string): Promise<SearchResult[]> {
  return guardedQuery(async () => {
    const result = await db.execute(
      sql`
        SELECT id, name, description
        FROM projects
        WHERE tenant_id = ${tenantId}
          AND to_tsvector('simple',
                coalesce(name, '') || ' ' ||
                coalesce(description, ''))
              @@ plainto_tsquery('simple', ${q})
        LIMIT 5
      `,
    )
    const rows = result as unknown as Array<{ id: string; name: string; description: string | null }>
    return rows.map((r) => ({
      id: r.id,
      label: r.name,
      description: r.description ?? undefined,
      url: `/projects/${r.id}`,
    }))
  })
}

// ── Source: invoices (later wave — guarded) ───────────────────────────────────

async function searchInvoices(db: Db, tenantId: string, q: string): Promise<SearchResult[]> {
  return guardedQuery(async () => {
    const result = await db.execute(
      sql`
        SELECT id, invoice_number, client_name
        FROM invoices
        WHERE tenant_id = ${tenantId}
          AND to_tsvector('simple',
                coalesce(invoice_number, '') || ' ' ||
                coalesce(client_name, ''))
              @@ plainto_tsquery('simple', ${q})
        LIMIT 5
      `,
    )
    const rows = result as unknown as Array<{ id: string; invoice_number: string; client_name: string | null }>
    return rows.map((r) => ({
      id: r.id,
      label: r.invoice_number,
      description: r.client_name ?? undefined,
      url: `/invoices/${r.id}`,
    }))
  })
}

// ── Source: tickets (later wave — guarded) ────────────────────────────────────

async function searchTickets(db: Db, tenantId: string, q: string): Promise<SearchResult[]> {
  return guardedQuery(async () => {
    const result = await db.execute(
      sql`
        SELECT id, subject
        FROM tickets
        WHERE tenant_id = ${tenantId}
          AND to_tsvector('simple', coalesce(subject, ''))
              @@ plainto_tsquery('simple', ${q})
        LIMIT 5
      `,
    )
    const rows = result as unknown as Array<{ id: string; subject: string }>
    return rows.map((r) => ({
      id: r.id,
      label: r.subject,
      url: `/crm/tickets/${r.id}`,
    }))
  })
}

// ── Source: kb_articles (later wave — guarded) ────────────────────────────────

async function searchArticles(db: Db, tenantId: string, q: string): Promise<SearchResult[]> {
  return guardedQuery(async () => {
    const result = await db.execute(
      sql`
        SELECT id, title
        FROM kb_articles
        WHERE tenant_id = ${tenantId}
          AND to_tsvector('simple', coalesce(title, ''))
              @@ plainto_tsquery('simple', ${q})
        LIMIT 5
      `,
    )
    const rows = result as unknown as Array<{ id: string; title: string }>
    return rows.map((r) => ({
      id: r.id,
      label: r.title,
      url: `/kb/${r.id}`,
    }))
  })
}

// ── Registry ──────────────────────────────────────────────────────────────────

export const SEARCH_SOURCES: SearchSource[] = [
  { key: 'customers', query: searchCustomers },
  { key: 'tasks',     query: searchTasks },
  { key: 'projects',  query: searchProjects },
  { key: 'invoices',  query: searchInvoices },
  { key: 'tickets',   query: searchTickets },
  { key: 'articles',  query: searchArticles },
]

/**
 * Run all sources in parallel. Each source is independently guarded —
 * a missing table returns [] without affecting other groups.
 */
export async function runSearch(
  db: Db,
  tenantId: string,
  q: string,
): Promise<SearchResponse> {
  const [customers, tasks, projects, invoices, tickets, articles] = await Promise.all(
    SEARCH_SOURCES.map((s) => s.query(db, tenantId, q)),
  )
  return {
    customers: customers ?? [],
    tasks: tasks ?? [],
    projects: projects ?? [],
    invoices: invoices ?? [],
    tickets: tickets ?? [],
    articles: articles ?? [],
  }
}