import { and, asc, desc, eq, inArray, sql } from 'drizzle-orm'
import { NotFoundError } from '@znet/shared'
import type { Db } from './db.js'
import { effectivePrice } from './pricing.js'
import {
  attributes, categories, categoryStoreData, inventorySalable, inventorySourceItems, productCategories, productImages,
  productLinks, productRelations, products, productStoreData, stores, syncRuns, syncState, type AttributeValues,
} from './schema.js'

export type Store = typeof stores.$inferSelect

/** Admin/default scope stand-in used before the first reference sync, or when no code is given. */
const DEFAULT_SCOPE: Store = {
  storeId: 0, code: 'admin', name: 'Default scope', websiteId: 0, websiteCode: null, groupId: 0, locale: null, baseCurrency: null,
  displayCurrency: null, timezone: null, baseUrl: null, mediaUrl: null, isActive: true, syncedAt: new Date(0),
}

export async function resolveStore(db: Db, code: string | undefined): Promise<Store> {
  if (!code) return DEFAULT_SCOPE
  const [row] = await db.select().from(stores).where(eq(stores.code, code))
  if (!row) throw new NotFoundError(`Store view "${code}"`)
  return row
}

export async function listStores(db: Db) {
  return db.select().from(stores).orderBy(asc(stores.storeId))
}

const VISIBILITY_LABEL: Record<number, string> = { 1: 'Not Visible Individually', 2: 'Catalog', 3: 'Search', 4: 'Catalog, Search' }
const STATUS_LABEL: Record<number, string> = { 1: 'Enabled', 2: 'Disabled' }

/** Escape LIKE wildcards in user input. */
const likeEscape = (s: string) => s.replace(/[\\%_]/g, m => `\\${m}`)

// ── Search ─────────────────────────────────────────────────────────────────

export interface ProductSearch {
  q?: string
  categoryId?: number
  typeId?: string
  status?: 'enabled' | 'disabled'
  inStock?: boolean
  limit: number
  offset: number
}

export interface ProductSummary {
  sku: string
  name: string
  typeId: string
  status: string
  visibility: string
  price: number | null
  specialPrice: number | null
  effectivePrice: number | null
  totalQty: number | null
  inStock: boolean | null
}

export async function searchProducts(db: Db, store: Store, s: ProductSearch) {
  const conds = [sql`p.deleted_at IS NULL`]
  let candidates = sql``
  if (s.q) {
    const pattern = `%${likeEscape(s.q)}%`
    // Narrow via the trigram indexes first; resolving store rows for every product is the slow part.
    candidates = sql`WITH cand AS (
      SELECT product_id FROM catalog.products WHERE sku ILIKE ${pattern}
      UNION
      SELECT product_id FROM catalog.product_store_data WHERE store_id IN (${store.storeId}, 0) AND name ILIKE ${pattern}
    ) `
    conds.push(sql`p.product_id IN (SELECT product_id FROM cand)`, sql`(p.sku ILIKE ${pattern} OR sd.name ILIKE ${pattern})`)
  }
  if (s.categoryId) conds.push(sql`EXISTS (SELECT 1 FROM catalog.product_categories pc WHERE pc.product_id = p.product_id AND pc.category_id = ${s.categoryId})`)
  if (s.typeId) conds.push(sql`p.type_id = ${s.typeId}`)
  if (s.status) conds.push(sql`sd.status = ${s.status === 'enabled' ? 1 : 2}`)
  if (s.inStock !== undefined) conds.push(s.inStock ? sql`inv.in_stock` : sql`NOT coalesce(inv.in_stock, false)`)
  const where = sql.join(conds, sql` AND `)
  const rank = s.q ? sql`(p.sku = ${s.q}) DESC, greatest(similarity(p.sku, ${s.q}), similarity(sd.name, ${s.q})) DESC,` : sql``

  const from = sql`
    FROM catalog.products p
    JOIN LATERAL (
      SELECT x.name, x.status, x.visibility FROM catalog.product_store_data x
      WHERE x.product_id = p.product_id AND x.store_id IN (${store.storeId}, 0) ORDER BY x.store_id DESC LIMIT 1
    ) sd ON true
    LEFT JOIN LATERAL (
      SELECT sum(quantity)::float8 AS qty, bool_or(in_stock AND quantity > 0) AS in_stock
      FROM catalog.inventory_source_items i WHERE i.sku = p.sku
    ) inv ON true
    WHERE ${where}`

  const [rows, [count]] = await Promise.all([
    db.execute<{
      sku: string; name: string; type_id: string; status: number; visibility: number; price: string | null
      special_price: string | null; special_from: string | null; special_to: string | null; qty: number | null; in_stock: boolean | null
    }>(sql`${candidates}SELECT p.sku, sd.name, p.type_id, sd.status, sd.visibility, p.price, p.special_price, p.special_from, p.special_to,
                  inv.qty, inv.in_stock
           ${from} ORDER BY ${rank} p.sku LIMIT ${s.limit} OFFSET ${s.offset}`),
    db.execute<{ n: number }>(sql`${candidates}SELECT count(*)::int AS n ${from}`),
  ])

  const items: ProductSummary[] = rows.map(r => {
    const price = effectivePrice({ price: num(r.price), specialPrice: num(r.special_price), specialFrom: r.special_from, specialTo: r.special_to })
    return {
      sku: r.sku,
      name: r.name,
      typeId: r.type_id,
      status: STATUS_LABEL[r.status] ?? String(r.status),
      visibility: VISIBILITY_LABEL[r.visibility] ?? String(r.visibility),
      price: price.price,
      specialPrice: price.specialActive ? price.specialPrice : null,
      effectivePrice: price.effectivePrice,
      totalQty: r.qty,
      inStock: r.in_stock,
    }
  })
  return { total: count?.n ?? 0, items }
}

const num = (v: string | number | null): number | null => (v == null ? null : Number(v))

// ── Product detail ─────────────────────────────────────────────────────────

/** Store-view row with fallback to default scope. */
async function storeDataFor(db: Db, productId: number, storeId: number) {
  const [row] = await db
    .select()
    .from(productStoreData)
    .where(and(eq(productStoreData.productId, productId), inArray(productStoreData.storeId, [storeId, 0])))
    .orderBy(desc(productStoreData.storeId))
    .limit(1)
  return row ?? null
}

/** Translate raw option ids in custom attributes to labels, keyed by attribute label. */
async function labelAttributes(db: Db, raw: AttributeValues) {
  const codes = Object.keys(raw)
  if (!codes.length) return []
  const defs = await db.select().from(attributes).where(inArray(attributes.attributeCode, codes))
  const byCode = new Map(defs.map(d => [d.attributeCode, d]))
  return codes.sort().map(code => {
    const def = byCode.get(code)
    let value: unknown = raw[code]
    if (def?.options.length && value != null) {
      const ids = Array.isArray(value) ? value : String(value).split(',')
      const labels = ids.map(id => def.options.find(o => o.value === String(id))?.label ?? id)
      value = def.frontendInput === 'multiselect' ? labels : labels[0]
    }
    return { code, label: def?.label ?? code, value }
  })
}

export async function getProductRow(db: Db, sku: string) {
  const [p] = await db.select().from(products).where(eq(products.sku, sku))
  if (!p) throw new NotFoundError(`Product "${sku}"`)
  return p
}

export async function getProduct(db: Db, store: Store, sku: string) {
  const p = await getProductRow(db, sku)
  const sd = await storeDataFor(db, p.productId, store.storeId)

  const [images, cats, children, parents, links, inventory, attrs] = await Promise.all([
    db.select().from(productImages).where(eq(productImages.productId, p.productId)).orderBy(asc(productImages.position)),
    productCategoriesFor(db, p.productId, store.storeId),
    db
      .select({ sku: productRelations.childSku, kind: productRelations.kind })
      .from(productRelations)
      .where(eq(productRelations.parentId, p.productId)),
    db
      .select({ sku: products.sku, kind: productRelations.kind })
      .from(productRelations)
      .innerJoin(products, eq(products.productId, productRelations.parentId))
      .where(eq(productRelations.childSku, p.sku)),
    db.select().from(productLinks).where(eq(productLinks.productId, p.productId)).orderBy(asc(productLinks.linkType), asc(productLinks.position)),
    getInventory(db, sku),
    labelAttributes(db, sd?.attributes ?? {}),
  ])

  const mediaBase = store.mediaUrl ? `${store.mediaUrl.replace(/\/$/, '')}/catalog/product` : null
  return {
    sku: p.sku,
    productId: p.productId,
    store: store.code,
    deleted: p.deletedAt != null,
    typeId: p.typeId,
    attributeSetId: p.attributeSetId,
    name: sd?.name ?? null,
    status: sd ? STATUS_LABEL[sd.status] : null,
    visibility: sd ? VISIBILITY_LABEL[sd.visibility] : null,
    urlKey: sd?.urlKey ?? null,
    description: sd?.description ?? null,
    shortDescription: sd?.shortDescription ?? null,
    metaTitle: sd?.metaTitle ?? null,
    metaDescription: sd?.metaDescription ?? null,
    websiteIds: p.websiteIds,
    weight: p.weight,
    pricing: { ...effectivePrice(p), tierPrices: p.tierPrices, currency: store.baseCurrency },
    inventory,
    categories: cats,
    images: images.map(i => ({ ...i, url: mediaBase ? `${mediaBase}${i.file}` : null })),
    variants: children.filter(c => c.kind === 'configurable').map(c => c.sku),
    bundleOrGroupedChildren: children.filter(c => c.kind !== 'configurable'),
    parents,
    links,
    attributes: attrs,
    magentoUpdatedAt: p.magentoUpdatedAt,
    syncedAt: p.syncedAt,
  }
}

async function productCategoriesFor(db: Db, productId: number, storeId: number) {
  const rows = await db.execute<{ category_id: number; name: string | null; is_active: boolean | null; path: string }>(sql`
    SELECT pc.category_id, cs.name, cs.is_active, c.path
    FROM catalog.product_categories pc
    LEFT JOIN catalog.categories c ON c.category_id = pc.category_id
    LEFT JOIN LATERAL (
      SELECT name, is_active FROM catalog.category_store_data x
      WHERE x.category_id = pc.category_id AND x.store_id IN (${storeId}, 0) ORDER BY x.store_id DESC LIMIT 1
    ) cs ON true
    WHERE pc.product_id = ${productId}
    ORDER BY pc.position, pc.category_id`)
  return rows.map(r => ({ categoryId: r.category_id, name: r.name, isActive: r.is_active, path: r.path }))
}

// ── Pricing & inventory ────────────────────────────────────────────────────

export async function getPricing(db: Db, store: Store, sku: string) {
  const p = await getProductRow(db, sku)
  return { sku, store: store.code, currency: store.baseCurrency, ...effectivePrice(p), tierPrices: p.tierPrices, syncedAt: p.syncedAt }
}

export async function getInventory(db: Db, sku: string) {
  const [sources, salable] = await Promise.all([
    db.select().from(inventorySourceItems).where(eq(inventorySourceItems.sku, sku)).orderBy(asc(inventorySourceItems.sourceCode)),
    db.select().from(inventorySalable).where(eq(inventorySalable.sku, sku)),
  ])
  const totalQty = sources.reduce((n, s) => n + Number(s.quantity), 0)
  return {
    sku,
    totalQty: sources.length ? totalQty : null,
    inStock: sources.length ? sources.some(s => s.inStock && Number(s.quantity) > 0) : null,
    sources: sources.map(s => ({ sourceCode: s.sourceCode, quantity: Number(s.quantity), inStock: s.inStock, syncedAt: s.syncedAt })),
    salable: salable.map(s => ({ stockId: s.stockId, salableQty: s.salableQty, isSalable: s.isSalable, checkedAt: s.checkedAt })),
  }
}

// ── Categories & attributes ────────────────────────────────────────────────

export async function listCategories(db: Db, store: Store, parentId?: number) {
  const rows = await db.execute<{
    category_id: number; parent_id: number; level: number; path: string; position: number
    name: string | null; is_active: boolean | null; include_in_menu: boolean | null; product_count: number
  }>(sql`
    SELECT c.category_id, c.parent_id, c.level, c.path, c.position, cs.name, cs.is_active, cs.include_in_menu,
           (SELECT count(*)::int FROM catalog.product_categories pc WHERE pc.category_id = c.category_id) AS product_count
    FROM catalog.categories c
    LEFT JOIN LATERAL (
      SELECT name, is_active, include_in_menu FROM catalog.category_store_data x
      WHERE x.category_id = c.category_id AND x.store_id IN (${store.storeId}, 0) ORDER BY x.store_id DESC LIMIT 1
    ) cs ON true
    ${parentId !== undefined ? sql`WHERE c.parent_id = ${parentId}` : sql``}
    ORDER BY c.level, c.parent_id, c.position`)
  return rows.map(r => ({
    categoryId: r.category_id, parentId: r.parent_id, level: r.level, path: r.path, position: r.position,
    name: r.name, isActive: r.is_active, includeInMenu: r.include_in_menu, productCount: r.product_count,
  }))
}

export async function getCategory(db: Db, store: Store, categoryId: number) {
  const [row] = (await listCategories(db, store)).filter(c => c.categoryId === categoryId)
  if (!row) throw new NotFoundError(`Category ${categoryId}`)
  const children = await listCategories(db, store, categoryId)
  const breadcrumb = await db
    .select({ categoryId: categoryStoreData.categoryId, name: categoryStoreData.name })
    .from(categoryStoreData)
    .where(and(inArray(categoryStoreData.categoryId, row.path.split('/').map(Number)), eq(categoryStoreData.storeId, 0)))
  const names = new Map(breadcrumb.map(b => [b.categoryId, b.name]))
  return { ...row, breadcrumb: row.path.split('/').map(Number).map(id => names.get(id) ?? String(id)), children }
}

export async function getAttribute(db: Db, code: string) {
  const [row] = await db.select().from(attributes).where(eq(attributes.attributeCode, code))
  if (!row) throw new NotFoundError(`Attribute "${code}"`)
  return row
}

export async function categoryExists(db: Db, categoryId: number) {
  const [row] = await db.select({ id: categories.categoryId }).from(categories).where(eq(categories.categoryId, categoryId))
  return Boolean(row)
}

/** Facts needed to explain why a product might not be visible (see catalog-api visibility service). */
export async function visibilityFacts(db: Db, store: Store, sku: string) {
  const product = await getProduct(db, store, sku)
  const [{ hasStoreRow } = { hasStoreRow: false }] = await db.execute<{ hasStoreRow: boolean }>(
    sql`SELECT EXISTS (SELECT 1 FROM catalog.product_store_data WHERE product_id = ${product.productId} AND store_id = ${store.storeId}) AS "hasStoreRow"`,
  )
  const variantStates = product.variants.length
    ? await db.execute<{ sku: string; status: number; in_stock: boolean | null }>(sql`
        SELECT p.sku, sd.status,
               (SELECT bool_or(in_stock AND quantity > 0) FROM catalog.inventory_source_items i WHERE i.sku = p.sku) AS in_stock
        FROM catalog.products p
        JOIN LATERAL (SELECT status FROM catalog.product_store_data x WHERE x.product_id = p.product_id AND x.store_id IN (${store.storeId}, 0)
                      ORDER BY x.store_id DESC LIMIT 1) sd ON true
        WHERE p.sku = ANY(${sql.param(product.variants)}::text[]) AND p.deleted_at IS NULL`)
    : []
  return { product, store, hasStoreOverride: hasStoreRow, variantStates: [...variantStates] }
}

// ── Sync status ────────────────────────────────────────────────────────────

export async function syncStatus(db: Db) {
  const [streams, recent] = await Promise.all([
    db.select().from(syncState).orderBy(asc(syncState.stream)),
    db.execute<{ job: string; status: string; started_at: Date; finished_at: Date | null; stats: Record<string, number>; error: string | null }>(sql`
      SELECT DISTINCT ON (job) job, status, started_at, finished_at, stats, error
      FROM ops.sync_runs ORDER BY job, started_at DESC`),
  ])
  const [lastFailure] = await db.select().from(syncRuns).where(eq(syncRuns.status, 'failed')).orderBy(desc(syncRuns.startedAt)).limit(1)
  return {
    streams: streams.map(s => ({ stream: s.stream, cursor: s.cursor, lastSuccessAt: s.lastSuccessAt })),
    lastRunPerJob: [...recent],
    lastFailure: lastFailure ?? null,
  }
}

export async function ping(db: Db) {
  await db.execute(sql`select 1`)
}