/**
 * Catalog queries — marketing-catalogs-campaigns (wave-9 leaf 3).
 * Product/service catalog CRUD + search.
 *
 * All functions take (db, tenantId, ...) — tenant isolation enforced here.
 * No raw Drizzle from routes (no-raw-drizzle-from-routes ESLint rule).
 */
import { eq, and, ilike, or, asc } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { catalogItems } from '../schema/catalog'

// ── Zod schemas ───────────────────────────────────────────────────────────────

export const createCatalogItemSchema = z.object({
  name: z.string().min(1).max(255),
  description: z.string().max(2000).optional(),
  sku: z.string().max(100).optional(),
  unitPrice: z.string().regex(/^\d+(\.\d{1,2})?$/, 'Must be a valid decimal'),
  currency: z.string().length(3).optional(),
  unit: z.string().max(50).optional(),
  category: z.string().max(100).optional(),
  isActive: z.boolean().optional(),
  metadata: z.record(z.unknown()).optional(),
})

export const updateCatalogItemSchema = createCatalogItemSchema.partial()

export type CreateCatalogItemInput = z.infer<typeof createCatalogItemSchema>
export type UpdateCatalogItemInput = z.infer<typeof updateCatalogItemSchema>

// ── Serialization ─────────────────────────────────────────────────────────────

export interface CatalogItemObject {
  id: string
  tenantId: string
  name: string
  description: string | null
  sku: string | null
  unitPrice: string
  currency: string
  unit: string | null
  category: string | null
  isActive: boolean
  metadata: Record<string, unknown> | null
  createdAt: string
}

function serializeCatalogItem(row: typeof catalogItems.$inferSelect): CatalogItemObject {
  return {
    id: row.id,
    tenantId: row.tenantId,
    name: row.name,
    description: row.description ?? null,
    sku: row.sku ?? null,
    unitPrice: row.unitPrice,
    currency: row.currency,
    unit: row.unit ?? null,
    category: row.category ?? null,
    isActive: row.isActive,
    metadata: row.metadata as Record<string, unknown> | null,
    createdAt: row.createdAt.toISOString(),
  }
}

// ── Query functions ───────────────────────────────────────────────────────────

export async function listCatalogItems(
  db: Db,
  tenantId: string,
  opts: { category?: string; activeOnly?: boolean } = {},
): Promise<CatalogItemObject[]> {
  const conditions = [eq(catalogItems.tenantId, tenantId)]

  if (opts.activeOnly !== false) {
    conditions.push(eq(catalogItems.isActive, true))
  }
  if (opts.category) {
    conditions.push(eq(catalogItems.category, opts.category))
  }

  const rows = await db
    .select()
    .from(catalogItems)
    .where(and(...conditions))
    .orderBy(asc(catalogItems.name))

  return rows.map(serializeCatalogItem)
}

export async function searchCatalogItems(
  db: Db,
  tenantId: string,
  query: string,
): Promise<CatalogItemObject[]> {
  const pattern = `%${query}%`
  const rows = await db
    .select()
    .from(catalogItems)
    .where(
      and(
        eq(catalogItems.tenantId, tenantId),
        or(
          ilike(catalogItems.name, pattern),
          ilike(catalogItems.description, pattern),
          ilike(catalogItems.sku, pattern),
          ilike(catalogItems.category, pattern),
        ),
      ),
    )
    .orderBy(asc(catalogItems.name))
    .limit(50)

  return rows.map(serializeCatalogItem)
}

export async function getCatalogItem(
  db: Db,
  tenantId: string,
  id: string,
): Promise<CatalogItemObject | null> {
  const [row] = await db
    .select()
    .from(catalogItems)
    .where(and(eq(catalogItems.tenantId, tenantId), eq(catalogItems.id, id)))
  return row ? serializeCatalogItem(row) : null
}

export async function createCatalogItem(
  db: Db,
  tenantId: string,
  input: CreateCatalogItemInput,
): Promise<CatalogItemObject> {
  const [row] = await db
    .insert(catalogItems)
    .values({
      tenantId,
      name: input.name,
      description: input.description ?? null,
      sku: input.sku ?? null,
      unitPrice: input.unitPrice,
      currency: input.currency ?? 'ILS',
      unit: input.unit ?? 'unit',
      category: input.category ?? null,
      isActive: input.isActive ?? true,
      metadata: input.metadata ?? null,
    })
    .returning()
  if (!row) throw new Error('Catalog item not found after insert')
  return serializeCatalogItem(row)
}

export async function updateCatalogItem(
  db: Db,
  tenantId: string,
  id: string,
  input: UpdateCatalogItemInput,
): Promise<CatalogItemObject> {
  const updates: Partial<typeof catalogItems.$inferInsert> = {}
  if (input.name !== undefined) updates.name = input.name
  if (input.description !== undefined) updates.description = input.description ?? null
  if (input.sku !== undefined) updates.sku = input.sku ?? null
  if (input.unitPrice !== undefined) updates.unitPrice = input.unitPrice
  if (input.currency !== undefined) updates.currency = input.currency
  if (input.unit !== undefined) updates.unit = input.unit ?? null
  if (input.category !== undefined) updates.category = input.category ?? null
  if (input.isActive !== undefined) updates.isActive = input.isActive
  if (input.metadata !== undefined) updates.metadata = input.metadata ?? null

  const [row] = await db
    .update(catalogItems)
    .set(updates)
    .where(and(eq(catalogItems.tenantId, tenantId), eq(catalogItems.id, id)))
    .returning()
  if (!row) throw new Error('Catalog item not found')
  return serializeCatalogItem(row)
}

export async function deleteCatalogItem(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .delete(catalogItems)
    .where(and(eq(catalogItems.tenantId, tenantId), eq(catalogItems.id, id)))
}

export async function listCatalogCategories(
  db: Db,
  tenantId: string,
): Promise<string[]> {
  const rows = await db
    .selectDistinct({ category: catalogItems.category })
    .from(catalogItems)
    .where(and(eq(catalogItems.tenantId, tenantId), eq(catalogItems.isActive, true)))
    .orderBy(asc(catalogItems.category))

  return rows.map((r) => r.category).filter(Boolean) as string[]
}