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`

// Final authoritative query contract. The legacy `queryEntities` candidate helper above remains
// exported for compatibility, but it is never used by this path and its cap cannot truncate results.
export type FieldPredicate =
  | { readonly op: 'eq' | 'neq'; readonly path: import('./schema.js').FieldPath; readonly value: string | number | boolean | null }
  | { readonly op: 'in' | 'notIn'; readonly path: import('./schema.js').FieldPath; readonly values: readonly (string | number | boolean)[] }
  | { readonly op: 'gt' | 'gte' | 'lt' | 'lte'; readonly path: import('./schema.js').FieldPath; readonly value: number | string }
  | { readonly op: 'contains'; readonly path: import('./schema.js').FieldPath; readonly value: string }
  | { readonly op: 'and' | 'or'; readonly children: readonly FieldPredicate[] }
  | { readonly op: 'not'; readonly child: FieldPredicate }

export interface FieldsEntityQuery<T> {
  readonly entityTypes: readonly string[]
  readonly predicate: FieldPredicate
  readonly orderBy?: { readonly path: import('./schema.js').FieldPath; readonly direction: 'asc' | 'desc'; readonly nulls?: 'first' | 'last' }
  readonly page: number
  readonly pageSize: number
  readonly projection?: T
}
export interface FieldsAuthorizedQueryScope {
  readonly policyVersion: string
  applyTo<T>(query: FieldsEntityQuery<T>): FieldsEntityQuery<T>
}
export interface FieldsEntityQueryAdapter<T> {
  query(input: FieldsEntityQuery<T>, authorizedScope: FieldsAuthorizedQueryScope): Promise<{ items: T[]; totalItems: number }>
}

const FINAL_MAX_DEPTH = 12
const FINAL_MAX_CHILDREN = 64
const FINAL_MAX_IN_VALUES = 256
const FINAL_MAX_TEXT = 4096
const FINAL_MAX_PAGE_SIZE = 200

type QueryFieldDefinition = import('./schema.js').AnyFieldDefinition
function queryFail(field:string,detail:string):never{throw new FieldValidationError(field,detail)}
function fieldByKey(fields:readonly QueryFieldDefinition[],key:string):QueryFieldDefinition|undefined{return fields.find((field)=>field.key===key)}
export function resolveQueryFieldPath(groups:readonly import('./schema.js').FieldGroup[],path:import('./schema.js').FieldPath):QueryFieldDefinition {
  if(!Array.isArray(path)||path.length<2||path.length>32)queryFail('query.path','must start with group key and contain a bounded field path')
  const groupKey=path[0];if(typeof groupKey!=='string')queryFail('query.path.0','must be a group key')
  const group=groups.find((item)=>item.key===groupKey);if(!group)queryFail('query.path.0',`unknown field group ${groupKey}`)
  let fields=group.fields,definition:QueryFieldDefinition|undefined
  for(let i=1;i<path.length;i++){
    const segment=path[i];if(typeof segment!=='string')queryFail(`query.path.${i}`,'definition query paths use field/layout keys, not runtime ordinals')
    definition=fieldByKey(fields,segment);if(!definition)queryFail(`query.path.${i}`,`unknown field ${segment}`)
    if(i===path.length-1)break
    if(definition.type==='group'||definition.type==='repeater')fields=(definition.settings as {fields:readonly QueryFieldDefinition[]}).fields
    else if(definition.type==='flexible'){
      const layoutKey=path[++i];if(typeof layoutKey!=='string')queryFail(`query.path.${i}`,'flexible path must name a layout key')
      const layout=(definition.settings as {layouts:readonly {key:string;fields:readonly QueryFieldDefinition[]}[]}).layouts.find((item)=>item.key===layoutKey);if(!layout)queryFail(`query.path.${i}`,`unknown flexible layout ${layoutKey}`);fields=layout.fields
    }else queryFail(`query.path.${i}`,'cannot descend through a scalar field')
  }
  if(!definition)queryFail('query.path','does not resolve a field')
  return definition
}
function queryOpAllowed(type:string,op:FieldPredicate['op']):boolean{
  if(op==='and'||op==='or'||op==='not')return true
  if(op==='contains')return ['text','textarea','email','url'].includes(type)
  if(op==='gt'||op==='gte'||op==='lt'||op==='lte')return ['number','range','date','dateTime','time'].includes(type)
  if(op==='in'||op==='notIn')return ['select','checkbox','radio','buttonGroup','entity','entityLink','relationship','taxonomy','user'].includes(type)
  if(op==='eq'||op==='neq')return !['password','richText','message','accordion','tab','group','repeater','flexible','clone','map','image','file','gallery','oembed','link','icon'].includes(type)
  return false
}
function validateScalar(value:unknown,field:string):void{if(value!==null&&typeof value!=='string'&&typeof value!=='number'&&typeof value!=='boolean')queryFail(field,'must be scalar');if(typeof value==='number'&&!Number.isFinite(value))queryFail(field,'must be finite');if(typeof value==='string'&&value.length>FINAL_MAX_TEXT)queryFail(field,`exceeds ${FINAL_MAX_TEXT} characters`)}
function validatePredicate(predicate:FieldPredicate,groups:readonly import('./schema.js').FieldGroup[],depth=0,budget={nodes:0}):void{
  if(depth>FINAL_MAX_DEPTH)queryFail('query.predicate',`exceeds depth ${FINAL_MAX_DEPTH}`);if(++budget.nodes>1_000)queryFail('query.predicate','exceeds node budget 1000')
  if('children' in predicate){if(predicate.children.length===0||predicate.children.length>FINAL_MAX_CHILDREN)queryFail('query.predicate.children',`must contain 1..${FINAL_MAX_CHILDREN} predicates`);for(const child of predicate.children)validatePredicate(child,groups,depth+1,budget);return}
  if('child' in predicate){validatePredicate(predicate.child,groups,depth+1,budget);return}
  const definition=resolveQueryFieldPath(groups,predicate.path);if(!queryOpAllowed(definition.type,predicate.op))queryFail('query.predicate.op',`${predicate.op} is not supported for ${definition.type}`)
  if('values' in predicate){if(predicate.values.length===0||predicate.values.length>FINAL_MAX_IN_VALUES)queryFail('query.predicate.values',`must contain 1..${FINAL_MAX_IN_VALUES} values`);predicate.values.forEach((value,index)=>validateScalar(value,`query.predicate.values.${index}`));return}
  validateScalar(predicate.value,'query.predicate.value')
  if(['gt','gte','lt','lte'].includes(predicate.op)&&definition.type!=='date'&&definition.type!=='dateTime'&&definition.type!=='time'&&typeof predicate.value!=='number')queryFail('query.predicate.value','numeric comparison requires a number')
}
export function validateFieldsEntityQuery<T>(input:FieldsEntityQuery<T>,groups:readonly import('./schema.js').FieldGroup[]):FieldsEntityQuery<T>{
  if(!Array.isArray(input.entityTypes)||input.entityTypes.length===0||input.entityTypes.length>64)queryFail('query.entityTypes','must contain 1..64 entity types')
  const entityTypes=[...new Set(input.entityTypes.map((value,index)=>{if(typeof value!=='string'||!value||value.length>128)queryFail(`query.entityTypes.${index}`,'must be a bounded non-empty string');return value}))].sort()
  if(!Number.isSafeInteger(input.page)||input.page<1)queryFail('query.page','must be a positive integer')
  if(!Number.isSafeInteger(input.pageSize)||input.pageSize<1||input.pageSize>FINAL_MAX_PAGE_SIZE)queryFail('query.pageSize',`must be between 1 and ${FINAL_MAX_PAGE_SIZE}`)
  validatePredicate(input.predicate,groups)
  if(input.orderBy){const definition=resolveQueryFieldPath(groups,input.orderBy.path);if(!['text','textarea','email','url','number','range','date','dateTime','time','boolean','select','radio','buttonGroup'].includes(definition.type))queryFail('query.orderBy.path',`${definition.type} cannot be ordered authoritatively`);if(input.orderBy.direction!=='asc'&&input.orderBy.direction!=='desc')queryFail('query.orderBy.direction','must be asc or desc');if(input.orderBy.nulls!==undefined&&input.orderBy.nulls!=='first'&&input.orderBy.nulls!=='last')queryFail('query.orderBy.nulls','must be first or last')}
  return Object.freeze({...input,entityTypes:Object.freeze(entityTypes)})
}
export async function queryFieldEntities<T>(adapter:FieldsEntityQueryAdapter<T>,input:FieldsEntityQuery<T>,groups:readonly import('./schema.js').FieldGroup[],authorizedScope:FieldsAuthorizedQueryScope):Promise<{items:readonly T[];totalItems:number}>{
  if(!authorizedScope||typeof authorizedScope.applyTo!=='function'||typeof authorizedScope.policyVersion!=='string'||!authorizedScope.policyVersion)queryFail('query.authorization','authorized query scope is required')
  const validated=validateFieldsEntityQuery(input,groups),scoped=authorizedScope.applyTo(validated)
  const result=await adapter.query(scoped,authorizedScope)
  if(!result||!Array.isArray(result.items)||!Number.isSafeInteger(result.totalItems)||result.totalItems<0)queryFail('query.adapter','returned an invalid result')
  if(result.items.length>validated.pageSize)queryFail('query.adapter','returned more than the requested page size')
  if(result.totalItems<result.items.length)queryFail('query.adapter','totalItems is smaller than returned page')
  return Object.freeze({items:Object.freeze([...result.items]),totalItems:result.totalItems})
}