/**
 * Search query-param extensions — zapier-make-integration (wave-13).
 * Extends existing GET /v1/customers, /v1/projects, /v1/invoices routes with
 * ?email=, ?name=, ?number= filter parameters for Zapier/Make search modules.
 *
 * These helpers are called from the main app router when search params are present.
 */
import { customers, projects, invoices, tasks, taskStatuses } from '@zync/db'
import { eq, and, ilike, ne, desc, lt, sql } from 'drizzle-orm'
import {
  serializeCustomer,
  serializeProject,
  serializeInvoice,
  serializeTask,
  buildPaginatedResult,
  decodeCursor,
  paginationSchema,
  hasScope,
  errForbiddenScope,
} from '../index'
import type { PublicApiContext } from '../app'

/** Search customers by email (exact match) — extends GET /v1/customers */
export async function searchCustomersByEmail(
  ctx: PublicApiContext,
  email: string,
): Promise<Response> {
  if (!hasScope(ctx.scopes, 'customers:read')) return errForbiddenScope('customers:read')
  const { db, tenantId } = ctx

  const rows = await db
    .select()
    .from(customers)
    .where(and(eq(customers.tenantId, tenantId), eq(customers.email, email), ne(customers.status, 'archived')))
    .limit(20)

  const serialized = rows.map((r) =>
    serializeCustomer({
      id: r.id,
      name: r.name,
      company: r.company,
      email: r.email,
      phone: r.phone,
      address: r.address as Parameters<typeof serializeCustomer>[0]['address'],
      status: r.status,
      createdAt: r.createdAt,
      updatedAt: r.updatedAt,
    }),
  )

  return new Response(
    JSON.stringify(buildPaginatedResult(serialized.map((s) => ({ ...s, id: s.id, created_at: s.created_at })), 20, serialized.length)),
    { status: 200, headers: { 'Content-Type': 'application/json' } },
  )
}

/** Search projects by name (case-insensitive contains) — extends GET /v1/projects */
export async function searchProjectsByName(
  ctx: PublicApiContext,
  name: string,
): Promise<Response> {
  if (!hasScope(ctx.scopes, 'projects:read')) return errForbiddenScope('projects:read')
  const { db, tenantId } = ctx

  const rows = await db
    .select()
    .from(projects)
    .where(and(eq(projects.tenantId, tenantId), ilike(projects.name, `%${name}%`)))
    .limit(20)

  const serialized = rows.map((r) =>
    serializeProject({
      id: r.id,
      name: r.name,
      customerId: r.customerId,
      status: r.status,
      createdAt: r.createdAt,
    }),
  )

  return new Response(
    JSON.stringify(buildPaginatedResult(serialized.map((s) => ({ ...s, id: s.id, created_at: s.created_at })), 20, serialized.length)),
    { status: 200, headers: { 'Content-Type': 'application/json' } },
  )
}

/** Search invoices by invoice number (exact match) — extends GET /v1/invoices */
export async function searchInvoicesByNumber(
  ctx: PublicApiContext,
  number: string,
): Promise<Response> {
  if (!hasScope(ctx.scopes, 'invoices:read')) return errForbiddenScope('invoices:read')
  const { db, tenantId } = ctx

  const rows = await db
    .select()
    .from(invoices)
    .where(and(eq(invoices.tenantId, tenantId), eq(invoices.invoiceNumber, number)))
    .limit(20)

  const serialized = rows.map((r) =>
    serializeInvoice(
      {
        id: r.id,
        customerId: r.customerId,
        projectId: r.projectId,
        invoiceNumber: r.invoiceNumber,
        proformaNumber: r.proformaNumber,
        status: r.status,
        currency: r.currency,
        issueDate: r.issueDate,
        taxIssueDate: r.taxIssueDate,
        dueDate: r.dueDate,
        vatRate: r.vatRate,
        subtotal: r.subtotal,
        vatAmount: r.vatAmount,
        total: r.total,
        notes: r.notes,
        source: r.source,
        paidAt: r.paidAt,
        createdAt: r.createdAt,
        updatedAt: r.updatedAt,
      },
      [],
    ),
  )

  return new Response(
    JSON.stringify(buildPaginatedResult(serialized.map((s) => ({ ...s, id: s.id, created_at: s.created_at })), 20, serialized.length)),
    { status: 200, headers: { 'Content-Type': 'application/json' } },
  )
}

function parsePagination(request: Request): { limit: number; cursor: ReturnType<typeof decodeCursor> } | Response {
  const url = new URL(request.url)
  const parsed = paginationSchema.safeParse({
    limit: url.searchParams.get('limit') ?? '20',
    cursor: url.searchParams.get('cursor') ?? undefined,
  })
  if (!parsed.success) {
    return new Response(JSON.stringify({ error: 'validation_error', message: 'Invalid pagination params' }), {
      status: 422,
      headers: { 'Content-Type': 'application/json' },
    })
  }
  return { limit: parsed.data.limit, cursor: parsed.data.cursor ? decodeCursor(parsed.data.cursor) : null }
}

/** GET /v1/customers without a search filter — used by Zapier dynamic fields. */
export async function listCustomers(ctx: PublicApiContext): Promise<Response> {
  if (!hasScope(ctx.scopes, 'customers:read')) return errForbiddenScope('customers:read')
  const pagination = parsePagination(ctx.request)
  if (pagination instanceof Response) return pagination
  const conditions = [eq(customers.tenantId, ctx.tenantId), ne(customers.status, 'archived')]
  if (pagination.cursor) conditions.push(lt(customers.createdAt, new Date(pagination.cursor.created_at)))
  const rows = await ctx.db.select().from(customers).where(and(...conditions)).orderBy(desc(customers.createdAt)).limit(pagination.limit + 1)
  const total = (await ctx.db.select({ count: sql<number>`count(*)::int` }).from(customers).where(and(eq(customers.tenantId, ctx.tenantId), ne(customers.status, 'archived'))))[0]?.count ?? 0
  return new Response(JSON.stringify(buildPaginatedResult(rows.slice(0, pagination.limit).map((row) => serializeCustomer({
    id: row.id,
    name: row.name,
    company: row.company,
    email: row.email,
    phone: row.phone,
    address: row.address as Parameters<typeof serializeCustomer>[0]['address'],
    status: row.status,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  })), pagination.limit, total)), { status: 200, headers: { 'Content-Type': 'application/json' } })
}

/** GET /v1/projects without a search filter — used by Zapier dynamic fields. */
export async function listProjects(ctx: PublicApiContext): Promise<Response> {
  if (!hasScope(ctx.scopes, 'projects:read')) return errForbiddenScope('projects:read')
  const pagination = parsePagination(ctx.request)
  if (pagination instanceof Response) return pagination
  const conditions = [eq(projects.tenantId, ctx.tenantId), ne(projects.status, 'archived')]
  if (pagination.cursor) conditions.push(lt(projects.createdAt, new Date(pagination.cursor.created_at)))
  const rows = await ctx.db.select().from(projects).where(and(...conditions)).orderBy(desc(projects.createdAt)).limit(pagination.limit + 1)
  const total = (await ctx.db.select({ count: sql<number>`count(*)::int` }).from(projects).where(and(eq(projects.tenantId, ctx.tenantId), ne(projects.status, 'archived'))))[0]?.count ?? 0
  return new Response(JSON.stringify(buildPaginatedResult(rows.slice(0, pagination.limit).map((row) => serializeProject({
    id: row.id,
    name: row.name,
    customerId: row.customerId,
    status: row.status,
    createdAt: row.createdAt,
  })), pagination.limit, total)), { status: 200, headers: { 'Content-Type': 'application/json' } })
}

/** GET /v1/invoices without a search filter — used by Zapier dynamic fields. */
export async function listInvoices(ctx: PublicApiContext): Promise<Response> {
  if (!hasScope(ctx.scopes, 'invoices:read')) return errForbiddenScope('invoices:read')
  const pagination = parsePagination(ctx.request)
  if (pagination instanceof Response) return pagination
  const conditions = [eq(invoices.tenantId, ctx.tenantId)]
  if (pagination.cursor) conditions.push(lt(invoices.createdAt, new Date(pagination.cursor.created_at)))
  const rows = await ctx.db.select().from(invoices).where(and(...conditions)).orderBy(desc(invoices.createdAt)).limit(pagination.limit + 1)
  const total = (await ctx.db.select({ count: sql<number>`count(*)::int` }).from(invoices).where(eq(invoices.tenantId, ctx.tenantId)))[0]?.count ?? 0
  return new Response(JSON.stringify(buildPaginatedResult(rows.slice(0, pagination.limit).map((row) => serializeInvoice({
    id: row.id,
    customerId: row.customerId,
    projectId: row.projectId,
    invoiceNumber: row.invoiceNumber,
    proformaNumber: row.proformaNumber,
    status: row.status,
    currency: row.currency,
    issueDate: row.issueDate,
    taxIssueDate: row.taxIssueDate,
    dueDate: row.dueDate,
    vatRate: row.vatRate,
    subtotal: row.subtotal,
    vatAmount: row.vatAmount,
    total: row.total,
    notes: row.notes,
    source: row.source,
    paidAt: row.paidAt,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  }, [])), pagination.limit, total)), { status: 200, headers: { 'Content-Type': 'application/json' } })
}

/** GET /v1/tasks — used by Zapier dynamic fields. */
export async function listTasks(ctx: PublicApiContext): Promise<Response> {
  if (!hasScope(ctx.scopes, 'tasks:read')) return errForbiddenScope('tasks:read')
  const pagination = parsePagination(ctx.request)
  if (pagination instanceof Response) return pagination
  const conditions = [eq(tasks.tenantId, ctx.tenantId)]
  if (pagination.cursor) conditions.push(lt(tasks.createdAt, new Date(pagination.cursor.created_at)))
  const rows = await ctx.db.select().from(tasks).leftJoin(taskStatuses, eq(tasks.statusId, taskStatuses.id))
    .where(and(...conditions)).orderBy(desc(tasks.createdAt)).limit(pagination.limit + 1)
  const total = (await ctx.db.select({ count: sql<number>`count(*)::int` }).from(tasks).where(eq(tasks.tenantId, ctx.tenantId)))[0]?.count ?? 0
  return new Response(JSON.stringify(buildPaginatedResult(rows.slice(0, pagination.limit).map(({ tasks: row, task_statuses: status }) => serializeTask({
    id: row.id,
    projectId: row.projectId,
    statusId: row.statusId,
    title: row.title,
    description: row.description,
    priority: row.priority,
    assigneeId: row.assigneeId,
    dueDate: row.dueDate,
    source: row.source,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  }, status?.name ?? '')), pagination.limit, total)), { status: 200, headers: { 'Content-Type': 'application/json' } })
}