import { and, count, eq, gt, inArray, isNull, lte, or, sql, type SQL } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import {
  category,
  collection,
  product,
  productCategory,
  productCollection,
  variant,
  variantPrice,
  type CatalogSchema,
} from './schema.js'
import type { CatalogFilter, Page, Product, Variant, VariantPrice } from './types.js'

export const SLUG_RE = /^[a-z0-9-]+$/
export const UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i

const DEFAULT_PAGE_SIZE = 20
/** Catalog's OWN cost-justified safe-`IN` bound — never mirror a sibling module's cap. */
const MAX_ENTITY_IDS = 256

export type ReadOpts = {
  audience: 'public' | 'admin'
  vendorId?: string
  now?: Date
}

type ProductRow = typeof product.$inferSelect

/** A6 visibility floor — enforced in SQL for public reads, never post-filtered in JS. */
function publicVisibilityPredicate(now: Date): SQL {
  return and(
    eq(product.status, 'active'),
    or(isNull(product.availableFrom), lte(product.availableFrom, now))!,
    or(isNull(product.availableUntil), gt(product.availableUntil, now))!,
  )!
}

function buildProductFilters(opts: ReadOpts): SQL[] {
  const filters: SQL[] = []
  if (opts.audience === 'public') {
    filters.push(publicVisibilityPredicate(opts.now ?? new Date()))
  }
  if (opts.vendorId !== undefined) {
    filters.push(eq(product.vendorId, opts.vendorId))
  }
  return filters
}

function toVariant(row: typeof variant.$inferSelect, prices: VariantPrice[]): Variant {
  return {
    id: row.id,
    productId: row.productId,
    sku: row.sku,
    attributes: row.attributes,
    prices,
  }
}

function toProduct(row: ProductRow, variants: Variant[]): Product {
  return {
    id: row.id,
    kind: row.kind,
    vendorId: row.vendorId ?? null,
    slug: row.slug,
    title: row.title,
    description: row.description ?? undefined,
    status: row.status,
    media: row.media,
    tags: row.tags,
    availableFrom: row.availableFrom ?? null,
    availableUntil: row.availableUntil ?? null,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
    variants,
  }
}

async function hydrateProducts(db: Querier<CatalogSchema>, rows: ProductRow[]): Promise<Product[]> {
  if (rows.length === 0) return []

  const productIds = rows.map((r) => r.id)
  const variantRows = await db.select().from(variant).where(inArray(variant.productId, productIds))
  const variantIds = variantRows.map((v) => v.id)

  const priceRows =
    variantIds.length > 0
      ? await db.select().from(variantPrice).where(inArray(variantPrice.variantId, variantIds))
      : []

  const pricesByVariant = new Map<string, VariantPrice[]>()
  for (const p of priceRows) {
    const list = pricesByVariant.get(p.variantId) ?? []
    list.push({ currency: p.currency, amount: p.amount, priceMode: p.priceMode })
    pricesByVariant.set(p.variantId, list)
  }

  const variantsByProduct = new Map<string, Variant[]>()
  for (const v of variantRows) {
    const list = variantsByProduct.get(v.productId) ?? []
    list.push(toVariant(v, pricesByVariant.get(v.id) ?? []))
    variantsByProduct.set(v.productId, list)
  }

  return rows.map((row) => toProduct(row, variantsByProduct.get(row.id) ?? []))
}

async function hydrateOne(db: Querier<CatalogSchema>, row: ProductRow): Promise<Product> {
  const [hydrated] = await hydrateProducts(db, [row])
  return hydrated!
}

function listFilterClauses(
  filter: CatalogFilter,
  db: Querier<CatalogSchema>,
): { clauses: SQL[]; entityIdsCapped: boolean } {
  const clauses: SQL[] = []
  let entityIdsCapped = false
  const now = filter.now ?? new Date()

  if (filter.audience === 'public') {
    clauses.push(publicVisibilityPredicate(now))
  }
  if (filter.kind) clauses.push(eq(product.kind, filter.kind))
  if (filter.status) clauses.push(eq(product.status, filter.status))
  if (filter.vendorId !== undefined) clauses.push(eq(product.vendorId, filter.vendorId))
  if (filter.tag) {
    clauses.push(sql`${product.tags} @> ${JSON.stringify([filter.tag])}::jsonb`)
  }
  if (filter.category) {
    clauses.push(
      inArray(
        product.id,
        db
          .select({ id: productCategory.productId })
          .from(productCategory)
          .innerJoin(category, eq(category.id, productCategory.categoryId))
          .where(eq(category.slug, filter.category)),
      ),
    )
  }
  if (filter.collection) {
    clauses.push(
      inArray(
        product.id,
        db
          .select({ id: productCollection.productId })
          .from(productCollection)
          .innerJoin(collection, eq(collection.id, productCollection.collectionId))
          .where(eq(collection.slug, filter.collection)),
      ),
    )
  }
  if (filter.entityIds !== undefined) {
    const validAll = filter.entityIds.filter((id) => UUID_RE.test(id))
    const valid = validAll.slice(0, MAX_ENTITY_IDS)
    entityIdsCapped = validAll.length > MAX_ENTITY_IDS
    if (valid.length === 0) {
      clauses.push(sql`false`)
    } else {
      clauses.push(inArray(product.id, valid))
    }
  }
  return { clauses, entityIdsCapped }
}

export async function getProductBySlug(
  db: Querier<CatalogSchema>,
  slug: string,
  opts: ReadOpts,
): Promise<Product | null> {
  if (!SLUG_RE.test(slug)) return null

  const filters: SQL[] = [eq(product.slug, slug), ...buildProductFilters(opts)]
  const [row] = await db
    .select()
    .from(product)
    .where(and(...filters))
    .limit(1)

  return row ? hydrateOne(db, row) : null
}

export async function getProductById(
  db: Querier<CatalogSchema>,
  id: string,
  opts: ReadOpts,
): Promise<Product | null> {
  if (!UUID_RE.test(id)) return null

  const filters: SQL[] = [eq(product.id, id), ...buildProductFilters(opts)]
  const [row] = await db
    .select()
    .from(product)
    .where(and(...filters))
    .limit(1)

  return row ? hydrateOne(db, row) : null
}

export async function listProducts(
  db: Querier<CatalogSchema>,
  filter: CatalogFilter,
): Promise<Page<Product>> {
  const page = Math.max(filter.page ?? 1, 1)
  const pageSize = DEFAULT_PAGE_SIZE
  const offset = (page - 1) * pageSize

  const { clauses, entityIdsCapped } = listFilterClauses(filter, db)
  const where = clauses.length ? and(...clauses) : undefined

  const [totalRow] = await db.select({ value: count() }).from(product).where(where)
  const total = Number(totalRow?.value ?? 0)

  const rows = await db.select().from(product).where(where).limit(pageSize).offset(offset)

  const items = await hydrateProducts(db, rows)
  return {
    items,
    total,
    page,
    pageSize,
    ...(entityIdsCapped ? { entityIdsCapped: true } : {}),
  }
}