import { db } from '@/lib/db'
import { Prisma } from '@prisma/client'

export interface SearchResult {
  id: string
  name: string
  description: string | null
  category: string | null
  status: string
  qty: number
  locationId: string
  locationName: string
  locationPath: string
  thumbPath: string | null
  needsAIReview: boolean
  similarity: number
}

interface SearchOpts {
  q?: string
  locationId?: string
  tag?: string
  status?: string
  limit?: number
}

// Fuzzy + prefix search via pg_trgm. Falls back to a recent-items listing
// when the query is empty so the home page has something to render.
export async function searchItems(opts: SearchOpts): Promise<SearchResult[]> {
  const q = (opts.q || '').trim()
  const limit = Math.min(opts.limit || 50, 200)

  // Build filter conditions that apply in both query modes.
  const filters: Prisma.Sql[] = []
  if (opts.locationId) {
    // Match the location itself OR any descendant by path prefix.
    const loc = await db.location.findUnique({
      where: { id: opts.locationId },
      select: { path: true },
    })
    if (loc) {
      filters.push(Prisma.sql`l.path = ${loc.path} OR l.path LIKE ${loc.path + ' > %'}`)
    }
  }
  if (opts.status) {
    filters.push(Prisma.sql`i.status::text = ${opts.status}`)
  }
  if (opts.tag) {
    filters.push(Prisma.sql`EXISTS (
      SELECT 1 FROM "_ItemTags" it
      JOIN "Tag" t ON t.id = it."B"
      WHERE it."A" = i.id AND t.name = ${opts.tag}
    )`)
  }

  const whereExtra = filters.length
    ? Prisma.sql`AND ${Prisma.join(filters, ' AND ')}`
    : Prisma.empty

  if (q.length === 0) {
    return db.$queryRaw<SearchResult[]>(Prisma.sql`
      SELECT
        i.id, i.name, i.description, i.category, i.status::text AS status, i.qty,
        i."locationId", l.name AS "locationName", l.path AS "locationPath",
        (SELECT p."thumbPath" FROM "Photo" p
          WHERE p."itemId" = i.id
          ORDER BY p."isPrimary" DESC, p."createdAt" ASC
          LIMIT 1) AS "thumbPath",
        i."needsAIReview" AS "needsAIReview",
        0::float AS similarity
      FROM "Item" i
      JOIN "Location" l ON l.id = i."locationId"
      WHERE 1=1 ${whereExtra}
      ORDER BY i."needsAIReview" DESC, i."updatedAt" DESC
      LIMIT ${limit}
    `)
  }

  // Multi-word queries: AND each token so "excavator filter" matches an item
  // whose searchText contains both words anywhere (not necessarily adjacent).
  // Per token, accept substring OR trigram similarity to be typo-tolerant.
  const tokens = q.split(/\s+/).filter(t => t.length > 0)
  const tokenConditions = tokens.map(
    t => Prisma.sql`(i."searchText" ILIKE ${'%' + t + '%'} OR i."searchText" % ${t})`,
  )
  const tokenWhere = Prisma.join(tokenConditions, ' AND ')

  return db.$queryRaw<SearchResult[]>(Prisma.sql`
    SELECT
      i.id, i.name, i.description, i.category, i.status::text AS status, i.qty,
      i."locationId", l.name AS "locationName", l.path AS "locationPath",
      (SELECT p."thumbPath" FROM "Photo" p
        WHERE p."itemId" = i.id
        ORDER BY p."isPrimary" DESC, p."createdAt" ASC
        LIMIT 1) AS "thumbPath",
      i."needsAIReview" AS "needsAIReview",
      GREATEST(
        similarity(i."searchText", ${q}),
        similarity(i.name, ${q})
      ) AS similarity
    FROM "Item" i
    JOIN "Location" l ON l.id = i."locationId"
    WHERE (${tokenWhere}) ${whereExtra}
    ORDER BY i."needsAIReview" DESC, similarity DESC, i."updatedAt" DESC
    LIMIT ${limit}
  `)
}

// Rebuild the denormalized searchText haystack from related rows. Call this
// inside the same transaction that creates/updates an item so the haystack
// always reflects the latest tags/location/name.
export async function rebuildSearchText(
  tx: Prisma.TransactionClient,
  itemId: string,
): Promise<void> {
  const item = await tx.item.findUnique({
    where: { id: itemId },
    include: { tags: true, location: true },
  })
  if (!item) return
  const parts = [
    item.name,
    item.description,
    item.category,
    item.brand,
    item.serialNumber,
    item.notes,
    item.caption,
    item.location.path,
    item.tags.map(t => t.name).join(' '),
  ].filter(Boolean)
  const searchText = parts.join(' ').toLowerCase()
  await tx.item.update({
    where: { id: itemId },
    data: { searchText },
  })
}