import {
  and,
  asc,
  desc,
  eq,
  exists,
  gte,
  inArray,
  isNull,
  lte,
  notInArray,
  or,
  sql,
  type SQL,
} from 'drizzle-orm'
import type { ContentPrincipal } from './authz.js'
import type { ContentEntry, ContentMediaRef, ContentVisibility, TermRef } from './model.js'
import type { ContentCapabilityAdapters } from './lifecycle.js'
import type { ResolvedContentType } from './registry.js'
import { assertActiveContentTransaction, contentEntries, type ContentTransaction } from './schema.js'
import type { ResolvedContentStatus } from './status.js'

const MAX_PAGE_SIZE = 200
const MAX_ARRAY_FILTER = 500
const MAX_TAXONOMY_FILTERS = 32
const MAX_SEARCH_LENGTH = 2_000
const UUID_RE = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i

export type ContentListOrder = 'title' | 'author' | 'createdAt' | 'updatedAt' | 'publishedAt' | 'menuOrder'
export interface ContentListQuery {
  types?: string[]
  statuses?: string[]
  authors?: string[]
  parentId?: string | null
  search?: string
  taxonomy?: Array<{ taxonomy: string; termIds: string[]; operator?: 'and' | 'or' }>
  date?: { after?: Date; before?: Date; field?: 'createdAt' | 'updatedAt' | 'publishedAt' }
  visibility?: ContentVisibility[]
  includeIds?: string[]
  excludeIds?: string[]
  orderBy?: ContentListOrder
  direction?: 'asc' | 'desc'
  page?: number
  pageSize?: number
}
export interface ContentPage<T = ContentEntry> {
  items: T[]
  page: number
  pageSize: number
  totalItems: number
  totalPages: number
}
export interface ContentQueryContext {
  principal: ContentPrincipal
  types: readonly ResolvedContentType[]
  statuses: readonly ResolvedContentStatus[]
  capabilities?: ContentCapabilityAdapters & { search?: ContentSearchAdapter }
}
export type ContentCountByStatusQuery = Omit<ContentListQuery, 'statuses'> & { statuses?: never }
export interface ContentSearchAdapter {
  searchPage(
    db: ContentTransaction,
    input: ContentListQuery,
    authorizedPredicate: SQL,
    principal: ContentPrincipal,
  ): Promise<{ ids: string[]; totalItems: number; rank: Record<string, number> }>
}

export type ContentQueryErrorCode = 'validation' | 'capability-unavailable' | 'integrity'
export class ContentQueryError extends Error {
  override readonly name = 'ContentQueryError'
  constructor(readonly code: ContentQueryErrorCode, readonly detail: string, readonly field?: string) {
    super(`content query ${code}: ${detail}${field ? ` (${field})` : ''}`)
  }
}

function fail(detail: string, field?: string): never {
  throw new ContentQueryError('validation', detail, field)
}
function positiveInteger(value: unknown, field: string, max: number): number {
  if (typeof value !== 'number' || !Number.isSafeInteger(value) || value < 1 || value > max) fail(`must be an integer in 1..${max}`, field)
  return value
}
function normalizedStrings(value: unknown, field: string, max = MAX_ARRAY_FILTER): string[] | undefined {
  if (value === undefined) return undefined
  if (!Array.isArray(value) || value.length > max) fail(`must be an array with at most ${max} items`, field)
  const seen = new Set<string>()
  const output: string[] = []
  for (const item of value) {
    if (typeof item !== 'string' || item.trim().length === 0 || item.length > 256) fail('contains an invalid string', field)
    const normalized = item.trim()
    if (!seen.has(normalized)) { seen.add(normalized); output.push(normalized) }
  }
  return output
}
function normalizedUuidStrings(value: unknown, field: string): string[] | undefined {
  const values = normalizedStrings(value, field)
  if (values?.some((item) => !UUID_RE.test(item))) fail('contains an invalid UUID', field)
  return values
}
function validDate(value: unknown, field: string): Date | undefined {
  if (value === undefined) return undefined
  if (!(value instanceof Date) || Number.isNaN(value.getTime())) fail('must be a valid Date', field)
  return new Date(value.getTime())
}
function normalizedQuery(query: ContentListQuery): Required<Pick<ContentListQuery, 'page' | 'pageSize' | 'orderBy' | 'direction'>> & ContentListQuery {
  if (typeof query !== 'object' || query === null || Array.isArray(query)) fail('must be an object', 'query')
  const allowed=new Set(['types','statuses','authors','parentId','search','taxonomy','date','visibility','includeIds','excludeIds','orderBy','direction','page','pageSize'])
  for(const key of Object.keys(query))if(!allowed.has(key))fail('contains an unsupported property',`query.${key}`)
  const page = query.page === undefined ? 1 : positiveInteger(query.page, 'page', 1_000_000)
  const pageSize = query.pageSize === undefined ? 20 : positiveInteger(query.pageSize, 'pageSize', MAX_PAGE_SIZE)
  const orderBy = query.orderBy ?? 'updatedAt'
  if (!['title','author','createdAt','updatedAt','publishedAt','menuOrder'].includes(orderBy)) fail('has an unsupported value', 'orderBy')
  const direction = query.direction ?? 'desc'
  if (direction !== 'asc' && direction !== 'desc') fail('must be asc or desc', 'direction')
  const search = query.search === undefined ? undefined : query.search.trim()
  if (search !== undefined && (search.length === 0 || search.length > MAX_SEARCH_LENGTH)) fail(`must contain 1..${MAX_SEARCH_LENGTH} characters`, 'search')
  const visibility = normalizedStrings(query.visibility, 'visibility') as ContentVisibility[] | undefined
  if (visibility?.some((item) => !['public','private','members'].includes(item))) fail('contains an invalid visibility', 'visibility')
  const parentId = query.parentId
  if (parentId !== undefined && parentId !== null && !UUID_RE.test(parentId)) fail('must be null or a UUID', 'parentId')
  const date = query.date === undefined ? undefined : {
    after: validDate(query.date.after, 'date.after'),
    before: validDate(query.date.before, 'date.before'),
    field: query.date.field ?? 'publishedAt',
  }
  if (date && !['createdAt','updatedAt','publishedAt'].includes(date.field)) fail('has an unsupported value', 'date.field')
  if (date?.after && date.before && date.after.getTime() > date.before.getTime()) fail('after must not be later than before', 'date')
  if (query.taxonomy !== undefined && (!Array.isArray(query.taxonomy) || query.taxonomy.length > MAX_TAXONOMY_FILTERS)) fail(`must contain at most ${MAX_TAXONOMY_FILTERS} filters`, 'taxonomy')
  const taxonomy = query.taxonomy?.map((item, index) => {
    if (!item || typeof item !== 'object' || Array.isArray(item)) fail('must be an object', `taxonomy.${index}`)
    if (typeof item.taxonomy !== 'string' || !/^[a-z][a-z0-9_-]{0,63}$/.test(item.taxonomy)) fail('has an invalid taxonomy key', `taxonomy.${index}.taxonomy`)
    const termIds = normalizedUuidStrings(item.termIds, `taxonomy.${index}.termIds`) ?? []
    if (termIds.length === 0) fail('must contain at least one term id', `taxonomy.${index}.termIds`)
    const operator = item.operator ?? 'or'
    if (operator !== 'and' && operator !== 'or') fail('must be and or or', `taxonomy.${index}.operator`)
    return { taxonomy: item.taxonomy, termIds, operator }
  })
  return {
    ...query,
    page,
    pageSize,
    orderBy,
    direction,
    ...(search === undefined ? {} : { search }),
    types: normalizedStrings(query.types, 'types'),
    statuses: normalizedStrings(query.statuses, 'statuses'),
    authors: normalizedStrings(query.authors, 'authors'),
    visibility,
    includeIds: normalizedUuidStrings(query.includeIds, 'includeIds'),
    excludeIds: normalizedUuidStrings(query.excludeIds, 'excludeIds'),
    ...(date === undefined ? {} : { date }),
    ...(taxonomy === undefined ? {} : { taxonomy }),
  }
}

function statusMap(context: ContentQueryContext): Map<string, ResolvedContentStatus> {
  const map = new Map<string, ResolvedContentStatus>()
  for (const status of context.statuses) {
    if (map.has(status.key)) throw new ContentQueryError('integrity', `duplicate resolved status ${status.key}`)
    map.set(status.key, status)
  }
  return map
}
function typeMap(context: ContentQueryContext): Map<string, ResolvedContentType> {
  const map = new Map<string, ResolvedContentType>()
  for (const type of context.types) {
    if (map.has(type.key)) throw new ContentQueryError('integrity', `duplicate resolved type ${type.key}`)
    map.set(type.key, type)
  }
  return map
}
function assertKnownFilters(query: ContentListQuery, context: ContentQueryContext): void {
  const types = typeMap(context)
  const statuses = statusMap(context)
  for (const key of query.types ?? []) if (!types.has(key)) fail(`unknown content type ${key}`, 'types')
  for (const key of query.statuses ?? []) if (!statuses.has(key)) fail(`unknown content status ${key}`, 'statuses')
}

function authorizedTypeStatusPredicate(context: ContentQueryContext): SQL {
  if (!context.principal || typeof context.principal.id !== 'string' || !context.principal.capabilities) fail('principal is required', 'principal')
  const statuses = statusMap(context)
  const branches: SQL[] = []
  for (const type of context.types) {
    const readCapability = type.capabilities.read
    if (!context.principal.capabilities.has(readCapability)) continue
    const allowedVisibility: SQL[] = [eq(contentEntries.visibility, 'public')]
    if (context.principal.member) allowedVisibility.push(eq(contentEntries.visibility, 'members'))
    if (context.principal.capabilities.has(type.capabilities.readPrivate)) allowedVisibility.push(eq(contentEntries.visibility, 'private'))
    const statusBranches: SQL[] = []
    const allowedStatusKeys = type.statusKeys ?? [...statuses.keys()]
    for (const key of allowedStatusKeys) {
      const status = statuses.get(key)
      if (!status) continue
      statusBranches.push(and(eq(contentEntries.status, key), eq(contentEntries.statusDefinitionRevision, status.revision))!)
    }
    if (statusBranches.length === 0) continue
    branches.push(and(
      eq(contentEntries.type, type.key),
      eq(contentEntries.typeDefinitionRevision, type.revision),
      or(...statusBranches)!,
      or(...allowedVisibility)!,
      isNull(contentEntries.deletedAt),
    )!)
  }
  // Never return an unscoped predicate.
  return branches.length === 0 ? sql`FALSE` : or(...branches)!
}


export function decodeContentListQuery(value: unknown): ContentListQuery {
  return normalizedQuery(value as ContentListQuery)
}

export function buildContentQueryPredicate(queryInput: ContentListQuery, context: ContentQueryContext): SQL {
  const query = normalizedQuery(queryInput)
  assertKnownFilters(query, context)
  const conditions: SQL[] = [authorizedTypeStatusPredicate(context)]
  if (query.types?.length) conditions.push(inArray(contentEntries.type, query.types))
  if (query.statuses?.length) conditions.push(inArray(contentEntries.status, query.statuses))
  if (query.authors?.length) conditions.push(inArray(contentEntries.author, query.authors))
  if (query.parentId !== undefined) conditions.push(query.parentId === null ? isNull(contentEntries.parentId) : eq(contentEntries.parentId, query.parentId))
  if (query.visibility?.length) conditions.push(inArray(contentEntries.visibility, query.visibility))
  if (query.includeIds?.length) conditions.push(inArray(contentEntries.id, query.includeIds))
  if (query.excludeIds?.length) conditions.push(notInArray(contentEntries.id, query.excludeIds))
  if (query.date) {
    const column = query.date.field === 'createdAt' ? contentEntries.createdAt : query.date.field === 'updatedAt' ? contentEntries.updatedAt : contentEntries.publishedAt
    if (query.date.after) conditions.push(gte(column, query.date.after))
    if (query.date.before) conditions.push(lte(column, query.date.before))
  }
  for (const filter of query.taxonomy ?? []) {
    if (filter.operator === 'or') {
      conditions.push(exists(sql`SELECT 1 FROM content_entry_terms cet JOIN content_terms ct ON ct.id = cet.term_id
        WHERE cet.entry_id = ${contentEntries.id} AND ct.taxonomy = ${filter.taxonomy} AND ct.id IN (${sql.join(filter.termIds.map((id) => sql`${id}::uuid`), sql`, `)})`))
    } else {
      for (const termId of filter.termIds) {
        conditions.push(exists(sql`SELECT 1 FROM content_entry_terms cet JOIN content_terms ct ON ct.id = cet.term_id
          WHERE cet.entry_id = ${contentEntries.id} AND ct.taxonomy = ${filter.taxonomy} AND ct.id = ${termId}::uuid`))
      }
    }
  }
  return and(...conditions)!
}

interface QueryRow extends Record<string, unknown> {
  id: string; slug: string; type: string; title: string; body: string; status: string; visibility: ContentVisibility
  publishedAt: Date | string | null; author: string; createdAt: Date | string; updatedAt: Date | string
  parentId: string | null; menuOrder: number | string; templateKey: string | null; excerpt: string; featuredMedia: unknown
  commentStatus: 'open'|'closed'; pingStatus: 'open'|'closed'; sticky: boolean|number|string; format: string|null
  deletedAt: Date|string|null; lastEditedBy: string; typeDefinitionRevision: number|string; statusDefinitionRevision: number|string
  passwordProtected: boolean|number|string
}
function bool(value: unknown): boolean { return value === true || value === 1 || value === '1' || value === 'true' }
function date(value: Date|string, field: string): Date {
  const parsed = value instanceof Date ? new Date(value.getTime()) : new Date(value)
  if (Number.isNaN(parsed.getTime())) throw new ContentQueryError('integrity', `stored ${field} timestamp is invalid`)
  return parsed
}
function json(value: unknown): unknown {
  if (typeof value !== 'string') return value
  try { return JSON.parse(value) as unknown } catch { return value }
}
const ENTRY_SELECT = sql.raw(`id, slug, type, title, body, status, visibility,
  published_at AS "publishedAt", author, created_at AS "createdAt", updated_at AS "updatedAt",
  parent_id AS "parentId", menu_order AS "menuOrder", template_key AS "templateKey", excerpt,
  featured_media AS "featuredMedia", comment_status AS "commentStatus", ping_status AS "pingStatus",
  sticky, format, deleted_at AS "deletedAt", last_edited_by AS "lastEditedBy",
  type_definition_revision AS "typeDefinitionRevision", status_definition_revision AS "statusDefinitionRevision",
  EXISTS (SELECT 1 FROM content_password_credentials c WHERE c.entry_id = content_entries.id) AS "passwordProtected"`)
async function termsByEntries(tx: ContentTransaction, ids: readonly string[]): Promise<Map<string, TermRef[]>> {
  const map = new Map<string, TermRef[]>()
  for (const id of ids) map.set(id, [])
  if (ids.length === 0) return map
  const rows = await tx.execute<{ entryId:string; id:string; taxonomy:string; slug:string; name:string; parentId:string|null; depth:number|string }>(sql`
    SELECT et.entry_id AS "entryId", t.id, t.taxonomy, t.slug, t.name, t.parent_id AS "parentId", t.depth
    FROM content_entry_terms et JOIN content_terms t ON t.id = et.term_id
    WHERE et.entry_id IN (${sql.join(ids.map((id) => sql`${id}::uuid`), sql`, `)})
    ORDER BY et.entry_id, t.depth, t.name, t.id`)
  for (const row of rows) map.get(row.entryId)?.push(Object.freeze({id:row.id,taxonomy:row.taxonomy,slug:row.slug,name:row.name,parentId:row.parentId,depth:Number(row.depth)}))
  return map
}
async function projectRows(tx: ContentTransaction, rows: readonly QueryRow[]): Promise<ContentEntry[]> {
  const terms = await termsByEntries(tx, rows.map((row) => row.id))
  return rows.map((row) => Object.freeze({
    id:row.id,slug:row.slug,type:row.type,title:row.title,body:row.body,status:row.status,visibility:row.visibility,
    publishedAt:row.publishedAt===null?null:date(row.publishedAt,'publishedAt'),author:row.author,terms:terms.get(row.id) ?? [],
    createdAt:date(row.createdAt,'createdAt'),updatedAt:date(row.updatedAt,'updatedAt'),parentId:row.parentId,menuOrder:Number(row.menuOrder),
    templateKey:row.templateKey,excerpt:row.excerpt,featuredMedia:(json(row.featuredMedia) ?? null) as ContentMediaRef|null,
    commentStatus:row.commentStatus,pingStatus:row.pingStatus,passwordProtected:bool(row.passwordProtected),sticky:bool(row.sticky),format:row.format,
    deletedAt:row.deletedAt===null?null:date(row.deletedAt,'deletedAt'),lastEditedBy:row.lastEditedBy,
    typeDefinitionRevision:Number(row.typeDefinitionRevision),statusDefinitionRevision:Number(row.statusDefinitionRevision),
  }))
}
function orderExpression(query: ReturnType<typeof normalizedQuery>): SQL[] {
  const column = query.orderBy === 'title' ? contentEntries.title
    : query.orderBy === 'author' ? contentEntries.author
    : query.orderBy === 'createdAt' ? contentEntries.createdAt
    : query.orderBy === 'publishedAt' ? contentEntries.publishedAt
    : query.orderBy === 'menuOrder' ? contentEntries.menuOrder
    : contentEntries.updatedAt
  const direction = query.direction === 'asc' ? asc : desc
  return [direction(column), asc(contentEntries.id)]
}

async function countWithoutSearch(tx: ContentTransaction, predicate: SQL): Promise<number> {
  const rows = await tx.execute<{ total:number|string }>(sql`SELECT COUNT(*) AS total FROM content_entries WHERE ${predicate}`)
  return Number(rows[0]?.total ?? 0)
}

export async function count(db: ContentTransaction, queryInput: ContentListQuery, context: ContentQueryContext): Promise<number> {
  assertActiveContentTransaction(db)
  const query = normalizedQuery(queryInput)
  const predicate = buildContentQueryPredicate(query, context)
  if (query.search !== undefined) {
    const adapter = context.capabilities?.search
    if (!adapter) throw new ContentQueryError('capability-unavailable', 'authoritative search adapter is required', 'search')
    const result = await adapter.searchPage(db, { ...query, page:1, pageSize:1 }, predicate, context.principal)
    if (!Number.isSafeInteger(result.totalItems) || result.totalItems < 0) throw new ContentQueryError('integrity', 'search adapter returned an invalid total')
    return result.totalItems
  }
  return countWithoutSearch(db, predicate)
}

export async function listPage(db: ContentTransaction, queryInput: ContentListQuery, context: ContentQueryContext): Promise<ContentPage> {
  assertActiveContentTransaction(db)
  const query = normalizedQuery(queryInput)
  const predicate = buildContentQueryPredicate(query, context)
  let rows: readonly QueryRow[]
  let totalItems: number
  if (query.search !== undefined) {
    const adapter = context.capabilities?.search
    if (!adapter) throw new ContentQueryError('capability-unavailable', 'authoritative search adapter is required', 'search')
    const result = await adapter.searchPage(db, query, predicate, context.principal)
    if (!Array.isArray(result.ids) || result.ids.length > query.pageSize || !Number.isSafeInteger(result.totalItems) || result.totalItems < 0 || result.ids.some((id) => !UUID_RE.test(id)) || new Set(result.ids).size !== result.ids.length) {
      throw new ContentQueryError('integrity', 'search adapter returned an invalid authoritative page')
    }
    totalItems = result.totalItems
    if (result.ids.length === 0) rows = []
    else {
      const selected = await db.execute<QueryRow>(sql`SELECT ${ENTRY_SELECT} FROM content_entries
        WHERE ${predicate} AND content_entries.id IN (${sql.join(result.ids.map((id) => sql`${id}::uuid`), sql`, `)})`)
      const byId = new Map(selected.map((row) => [row.id,row]))
      if (result.ids.some((id) => !byId.has(id))) throw new ContentQueryError('integrity', 'search page no longer matches its authorization predicate')
      rows = result.ids.map((id) => byId.get(id)!)
    }
  } else {
    totalItems = await countWithoutSearch(db, predicate)
    const offset = (query.page - 1) * query.pageSize
    const [firstOrder,secondOrder] = orderExpression(query)
    rows = await db.execute<QueryRow>(sql`SELECT ${ENTRY_SELECT} FROM content_entries
      WHERE ${predicate} ORDER BY ${firstOrder}, ${secondOrder} LIMIT ${query.pageSize} OFFSET ${offset}`)
  }
  return Object.freeze({
    items: await projectRows(db, rows),
    page: query.page,
    pageSize: query.pageSize,
    totalItems,
    totalPages: totalItems === 0 ? 0 : Math.ceil(totalItems / query.pageSize),
  })
}

export async function countByStatus(db: ContentTransaction, queryInput: ContentCountByStatusQuery, context: ContentQueryContext): Promise<Record<string, number>> {
  assertActiveContentTransaction(db)
  if (Object.prototype.hasOwnProperty.call(queryInput as object, 'statuses')) fail('statuses is forbidden for countByStatus', 'statuses')
  const base = normalizedQuery(queryInput)
  const output: Record<string, number> = Object.create(null) as Record<string, number>
  for (const status of context.statuses) output[status.key] = await count(db, { ...base, statuses:[status.key] }, context)
  return Object.freeze(output)
}