import { sql, type SQL } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import { FieldValidationError } from './errors.js'
import type { EntityId, EntityType, Facet } from './model.js'
import type { FieldsSchema } from './schema.js'

const DEFAULT_LIMIT = 5_000
const HARD_MAX = 50_000
// Facet count is attacker-controllable (storefront filter params, §8.2). Each facet is one
// INTERSECT subquery → unbounded facets = planner-blowup DoS. Cap fail-loud at the boundary.
const MAX_FACETS = 32
// IN-list elements are equally attacker-controllable → unbounded list = PG bind-param-ceiling
// DoS (same class as MAX_FACETS). Cap fail-loud rather than fail opaque at the driver's limit.
const MAX_IN_LIST = 256

/**
 * Returns a capped candidate entity-id set for cross-module faceting (spec §5.4).
 * `capped: true` is a correctness boundary — the candidate set is intermediate; when
 * capped, the host MUST NOT present the intersected result as authoritative (degrade
 * explicitly). The cap is a v1 expedient; high-cardinality push-down is deferred.
 *
 * All facet/entity inputs are bound as query parameters (NOT string-interpolated):
 * facet values are user-facing (storefront filters, §8.2) → a trust boundary, so the
 * SQL is built from drizzle `sql` fragments with `${}` parameter placeholders only.
 */
export async function queryEntities(
  db: Querier<FieldsSchema>,
  q: { entityType: EntityType; facets: Facet[]; limit?: number },
): Promise<{ entityIds: EntityId[]; capped: boolean }> {
  const limit = Math.min(q.limit ?? DEFAULT_LIMIT, HARD_MAX)
  if (q.facets.length === 0) {
    return { entityIds: [], capped: false }
  }
  if (q.facets.length > MAX_FACETS) {
    throw new FieldValidationError('facets', `too many facets: ${q.facets.length} (max ${MAX_FACETS})`)
  }

  const subqueries = q.facets.map((facet) => facetSubquery(facet, q.entityType))
  const intersected = sql.join(subqueries, sql` INTERSECT `)
  const querySql = sql`SELECT entity_id FROM (${intersected}) AS candidates ORDER BY entity_id LIMIT ${limit + 1}`

  const result = await db.execute(querySql)
  const rows = (Array.isArray(result) ? result : (result as { rows: Array<{ entity_id: string }> }).rows)
  const ids = rows.map((r) => r.entity_id)
  const capped = ids.length > limit
  return { entityIds: capped ? ids.slice(0, limit) : ids, capped }
}

function facetSubquery(facet: Facet, entityType: EntityType): SQL {
  const scope = sql`entity_type = ${entityType} AND field_key = ${facet.field}`

  if (facet.op === 'has-ref') {
    return sql`SELECT DISTINCT entity_id FROM field_values
      WHERE ${scope} AND ref_type = ${facet.refType} AND ref_id = ${facet.refId}`
  }

  if (facet.op === 'eq' && typeof facet.value === 'string') {
    return sql`SELECT DISTINCT entity_id FROM field_values
      WHERE ${scope} AND value_text = ${facet.value}`
  }

  if (facet.op === 'in') {
    const list = (Array.isArray(facet.value) ? facet.value : [facet.value]).map((v) => String(v))
    // Empty IN-list can never match → empty candidate set (avoids invalid `IN ()`).
    if (list.length === 0) return EMPTY_CANDIDATE_SET
    if (list.length > MAX_IN_LIST) {
      throw new FieldValidationError('facet.value', `IN-list too long: ${list.length} (max ${MAX_IN_LIST})`)
    }
    return sql`SELECT DISTINCT entity_id FROM field_values
      WHERE ${scope} AND value_text IN (${sql.join(list.map((v) => sql`${v}`), sql`, `)})`
  }

  if (facet.op === 'between') {
    if (!Number.isFinite(facet.min) || !Number.isFinite(facet.max)) return EMPTY_CANDIDATE_SET
    return sql`SELECT DISTINCT entity_id FROM field_values
      WHERE ${scope} AND value_num IS NOT NULL
        AND value_num >= ${facet.min} AND value_num <= ${facet.max}`
  }

  if (
    (facet.op === 'eq' || facet.op === 'lt' || facet.op === 'lte' || facet.op === 'gt' || facet.op === 'gte') &&
    typeof facet.value === 'number'
  ) {
    if (!Number.isFinite(facet.value)) return EMPTY_CANDIDATE_SET
    const cmp = NUMERIC_CMP[facet.op]
    return sql`SELECT DISTINCT entity_id FROM field_values
      WHERE ${scope} AND value_num IS NOT NULL AND value_num ${cmp} ${facet.value}`
  }

  // Typed-lane mismatch (e.g. gt on a text field) → empty candidate set
  return EMPTY_CANDIDATE_SET
}

// Comparison operators are a fixed internal lookup (never derived from input) → safe as raw.
const NUMERIC_CMP = {
  eq: sql.raw('='),
  lt: sql.raw('<'),
  lte: sql.raw('<='),
  gt: sql.raw('>'),
  gte: sql.raw('>='),
} as const

const EMPTY_CANDIDATE_SET: SQL = sql`SELECT entity_id FROM field_values WHERE false`