import { sql } from 'drizzle-orm'
import { authorizeContentAction, ContentAuthorizationError, type ContentPrincipal } from './authz.js'
import { ContentHierarchyError, preparePermanentContentDelete, validateContentParent, type ContentPermanentDeletePlan } from './hierarchy.js'
import { canonicalContentMigrationHash } from './migrations/content-model.js'
import type {
  ContentEntry,
  ContentMediaRef,
  ContentPasswordAdapter,
  ContentRevision,
  ContentTemplateRegistry,
  ContentUpdatePatch,
  ContentVisibility,
  ContentWriteInput,
  ProtectedContentRead,
  Sanitize,
  TermRef,
} from './model.js'
import { ContentDefinitionError, resolveContentType, type ResolvedContentType } from './registry.js'
import { assertActiveContentTransaction, type ContentSchemaValue, type ContentTransaction } from './schema.js'
import { resolveContentStatus, resolveContentStatuses, type ResolvedContentStatus } from './status.js'

export type ContentLifecycleErrorCode =
  | 'validation'
  | 'access-denied'
  | 'not-found'
  | 'conflict'
  | 'capability-unavailable'
  | 'stale-version'
  | 'operation-conflict'
  | 'integrity'

export class ContentLifecycleError extends Error {
  override readonly name = 'ContentLifecycleError'
  constructor(readonly code: ContentLifecycleErrorCode, readonly detail: string, readonly field?: string) {
    super(`content lifecycle ${code}: ${detail}${field ? ` (${field})` : ''}`)
  }
}

export interface ContentAuthorAuthority {
  assertAssign(principal: ContentPrincipal, targetAuthorId: string): Promise<void> | void
  assertTransfer(principal: ContentPrincipal, fromAuthorId: string, targetAuthorId: string): Promise<void> | void
}

export interface ContentMutationContext {
  operationId: string
  principal: ContentPrincipal
  type: ResolvedContentType
  before: Readonly<ContentEntry | null>
  proposed: Readonly<ContentEntry | null>
}
export interface ContentHooks {
  beforeValidate?(ctx: ContentMutationContext): Promise<void> | void
  afterValidate?(ctx: ContentMutationContext): Promise<void> | void
  beforeCommit?(ctx: ContentMutationContext): Promise<void> | void
}
export interface ContentOutbox {
  enqueue(tx: ContentTransaction, event: {
    id: string
    version: 1
    operationId: string
    kind: 'written' | 'transitioned' | 'deleted' | 'reparented'
    entryId: string
    occurredAt: Date
  }): Promise<void>
}
export interface ContentCapabilityAdapters {
  validateMediaRef?(ref: ContentMediaRef, principal: ContentPrincipal): Promise<void>
}
export interface ContentWriteDependencies {
  sanitize: Sanitize
  templates: ContentTemplateRegistry
  authors?: ContentAuthorAuthority
  capabilities?: ContentCapabilityAdapters
  hooks?: ContentHooks
  outbox: ContentOutbox
}
export interface ContentTransitionRule {
  from: string | '*'
  to: string
  capability: keyof ResolvedContentType['capabilities']
  requiresScheduleAt?: boolean
}
export interface ContentTransitionDependencies {
  types: readonly ResolvedContentType[]
  statuses: readonly ResolvedContentStatus[]
  rules: readonly ContentTransitionRule[]
  hooks?: ContentHooks
  outbox: ContentOutbox
}
export interface ContentPermanentDeleteDependencies {
  hooks?: ContentHooks
  outbox: ContentOutbox
}
export interface ContentPermanentDeleteResult { id: string; parentDeletionRevisionId: string }
export interface ContentAutosave {
  id: string
  entryId: string
  parentRevisionId: string
  snapshot: ContentUpdatePatch
  createdAt: Date
  createdBy: string
}

interface EntryRow extends Record<string, unknown> {
  id: string
  slug: string
  type: string
  title: string
  body: string
  status: string
  visibility: ContentVisibility
  publishedAt: string | Date | null
  author: string
  createdAt: string | Date
  updatedAt: string | Date
  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: string | Date | null
  lastEditedBy: string
  typeDefinitionRevision: number | string
  statusDefinitionRevision: number | string
  passwordProtected: boolean | number | string
}
interface ReceiptRow extends Record<string, unknown> { payload: unknown }
interface LifecycleReceipt {
  requestHash: string
  principalScope: string
  result: ContentSchemaValue
}

function principalScope(principal: ContentPrincipal): string {
  return `${principal.tenantId ?? ''}:${principal.id}`
}
function bool(value: boolean | number | string): boolean {
  return value === true || value === 1 || value === '1' || value === 'true'
}
function asDate(value: string | Date, field: string): Date {
  const parsed = value instanceof Date ? new Date(value.getTime()) : new Date(value)
  if (Number.isNaN(parsed.getTime())) throw new ContentLifecycleError('integrity', 'stored timestamp is invalid', field)
  return parsed
}
function parseJson(value: unknown): ContentSchemaValue | null {
  if (value === null) return null
  if (typeof value === 'string') {
    try { return JSON.parse(value) as ContentSchemaValue } catch { return value }
  }
  return value as ContentSchemaValue
}
function plainObject(value: unknown): Record<string, unknown> {
  if (typeof value !== 'object' || value === null || Array.isArray(value)) throw new ContentLifecycleError('integrity', 'stored object is malformed')
  return value as Record<string, unknown>
}
function nonEmptyString(value: unknown, field: string, max: number): string {
  if (typeof value !== 'string') throw new ContentLifecycleError('validation', 'must be a string', field)
  const normalized = value.trim()
  if (!normalized || normalized.length > max) throw new ContentLifecycleError('validation', `must contain 1..${max} characters`, field)
  return normalized
}
function nullableString(value: unknown, field: string, max: number): string | null {
  if (value === null) return null
  return nonEmptyString(value, field, max)
}
function exactDate(value: Date, field: string): Date {
  if (!(value instanceof Date) || Number.isNaN(value.getTime())) throw new ContentLifecycleError('validation', 'must be a valid Date', field)
  return new Date(value.getTime())
}
function uniqueStrings(value: unknown, field: string, max = 10_000): string[] {
  if (!Array.isArray(value) || value.length > max) throw new ContentLifecycleError('validation', `must be an array with at most ${max} items`, field)
  const out: string[] = []
  const seen = new Set<string>()
  for (const item of value) {
    const string = nonEmptyString(item, field, 256)
    if (!seen.has(string)) { seen.add(string); out.push(string) }
  }
  return out
}
function entryBase(row: EntryRow, terms: readonly TermRef[]): ContentEntry {
  const media = parseJson(row.featuredMedia)
  return 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 : asDate(row.publishedAt, 'publishedAt'),
    author: row.author,
    terms: Object.freeze([...terms]) as TermRef[],
    createdAt: asDate(row.createdAt, 'createdAt'),
    updatedAt: asDate(row.updatedAt, 'updatedAt'),
    parentId: row.parentId,
    menuOrder: Number(row.menuOrder),
    templateKey: row.templateKey,
    excerpt: row.excerpt,
    featuredMedia: media === null ? null : plainObject(media) as unknown as ContentMediaRef,
    commentStatus: row.commentStatus,
    pingStatus: row.pingStatus,
    passwordProtected: bool(row.passwordProtected),
    sticky: bool(row.sticky),
    format: row.format,
    deletedAt: row.deletedAt === null ? null : asDate(row.deletedAt, 'deletedAt'),
    lastEditedBy: row.lastEditedBy,
    typeDefinitionRevision: Number(row.typeDefinitionRevision),
    statusDefinitionRevision: Number(row.statusDefinitionRevision),
  })
}

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 termsForEntry(tx: ContentTransaction, entryId: string): Promise<TermRef[]> {
  const rows = await tx.execute<{ id: string; taxonomy: string; slug: string; name: string; parentId: string | null; depth: number | string }>(sql`
    SELECT t.id, t.taxonomy, t.slug, t.name, t.parent_id AS "parentId", t.depth
    FROM content_entry_terms et INNER JOIN content_terms t ON t.id = et.term_id
    WHERE et.entry_id = ${entryId} ORDER BY t.depth, t.name, t.id`)
  return rows.map((row) => Object.freeze({ id: row.id, taxonomy: row.taxonomy, slug: row.slug, name: row.name, parentId: row.parentId, depth: Number(row.depth) }))
}
async function readEntry(tx: ContentTransaction, id: string): Promise<ContentEntry | undefined> {
  const rows = await tx.execute<EntryRow>(sql`SELECT ${ENTRY_SELECT} FROM content_entries WHERE id = ${id}`)
  if (!rows[0]) return undefined
  return entryBase(rows[0], await termsForEntry(tx, id))
}
async function requireEntry(tx: ContentTransaction, id: string): Promise<ContentEntry> {
  const entry = await readEntry(tx, id)
  if (!entry) throw new ContentLifecycleError('access-denied', 'content is unavailable')
  return entry
}

function schemaValue(value: unknown): ContentSchemaValue {
  return JSON.parse(JSON.stringify(value)) as ContentSchemaValue
}
async function requestHash(value: unknown): Promise<string> {
  return canonicalContentMigrationHash(schemaValue(value))
}
async function readReceipt(tx: ContentTransaction, operationId: string): Promise<LifecycleReceipt | undefined> {
  const rows = await tx.execute<ReceiptRow>(sql`SELECT payload FROM content_lifecycle_journal WHERE operation_id = ${operationId}`)
  if (!rows[0]) return undefined
  const raw = typeof rows[0].payload === 'string' ? JSON.parse(rows[0].payload) : rows[0].payload
  const receipt = raw as Partial<LifecycleReceipt>
  if (!receipt || typeof receipt.requestHash !== 'string' || typeof receipt.principalScope !== 'string' || receipt.result === undefined) {
    throw new ContentLifecycleError('integrity', 'operation receipt is malformed')
  }
  return receipt as LifecycleReceipt
}
async function writeReceipt(tx: ContentTransaction, input: { operationId: string; kind: string; entryId?: string; requestHash: string; principal: ContentPrincipal; result: unknown }): Promise<void> {
  const now = new Date().toISOString()
  const payload: LifecycleReceipt = { requestHash: input.requestHash, principalScope: principalScope(input.principal), result: schemaValue(input.result) }
  await tx.execute(sql`INSERT INTO content_lifecycle_journal (id, operation_id, kind, entry_id, state, payload, created_at, updated_at)
    VALUES (${crypto.randomUUID()}, ${input.operationId}, ${input.kind}, ${input.entryId ?? null}, 'committed', ${JSON.stringify(payload)}, ${now}, ${now})`)
}
async function replayEntry(tx: ContentTransaction, receipt: LifecycleReceipt, expectedHash: string, principal: ContentPrincipal): Promise<ContentEntry | undefined> {
  if (receipt.requestHash !== expectedHash || receipt.principalScope !== principalScope(principal)) throw new ContentLifecycleError('operation-conflict', 'operation id was already used for different input or principal')
  const result = plainObject(receipt.result)
  if (typeof result.id !== 'string') return undefined
  return readEntry(tx, result.id)
}
async function eventId(operationId: string, kind: string, entryId: string): Promise<string> {
  return canonicalContentMigrationHash({ operationId, kind, entryId })
}
async function enqueue(tx: ContentTransaction, outbox: ContentOutbox, operationId: string, kind: 'written'|'transitioned'|'deleted'|'reparented', entryId: string, occurredAt: Date): Promise<void> {
  await outbox.enqueue(tx, { id: await eventId(operationId, kind, entryId), version: 1, operationId, kind, entryId, occurredAt })
}
function mutationContext(operationId: string, principal: ContentPrincipal, type: ResolvedContentType, before: ContentEntry | null, proposed: ContentEntry | null): ContentMutationContext {
  return Object.freeze({ operationId, principal, type, before: before ? Object.freeze(before) : null, proposed: proposed ? Object.freeze(proposed) : null })
}
async function runValidateHooks(hooks: ContentHooks | undefined, ctx: ContentMutationContext): Promise<void> {
  await hooks?.beforeValidate?.(ctx)
  await hooks?.afterValidate?.(ctx)
}
async function runBeforeCommit(hooks: ContentHooks | undefined, ctx: ContentMutationContext): Promise<void> {
  await hooks?.beforeCommit?.(ctx)
}
function assertFeature(type: ResolvedContentType, feature: ResolvedContentType['supports'][number], field: string): void {
  if (!type.supports.includes(feature)) throw new ContentLifecycleError('validation', `${field} is not supported by content type ${type.key}`, field)
}
function assertTypeActive(type: ResolvedContentType): void {
  if (!type.active) throw new ContentLifecycleError('validation', 'content type is inactive', 'type')
}
function assertVisibility(value: unknown): ContentVisibility {
  if (value !== 'public' && value !== 'private' && value !== 'members') throw new ContentLifecycleError('validation', 'must be public, private, or members', 'visibility')
  return value
}
function assertMediaRef(value: unknown): ContentMediaRef | null {
  if (value === null) return null
  if (typeof value !== 'object' || value === null || Array.isArray(value)) throw new ContentLifecycleError('validation', 'must be an opaque media reference', 'featuredMedia')
  const ref = value as Record<string, unknown>
  const id = nonEmptyString(ref.id, 'featuredMedia.id', 256)
  const keys = Object.keys(ref)
  if (keys.some((key) => key !== 'id' && key !== 'kind')) throw new ContentLifecycleError('validation', 'contains unsupported media reference keys', 'featuredMedia')
  const kind = ref.kind === undefined ? undefined : nonEmptyString(ref.kind, 'featuredMedia.kind', 128)
  return Object.freeze({ id, ...(kind ? { kind } : {}) })
}
async function validateTerms(tx: ContentTransaction, type: ResolvedContentType, termIds: readonly string[]): Promise<void> {
  if (termIds.length === 0) return
  const rows = await tx.execute<{ id: string; taxonomy: string }>(sql`SELECT id, taxonomy FROM content_terms WHERE id IN (${sql.join(termIds.map((id) => sql`${id}`), sql`, `)})`)
  if (rows.length !== termIds.length) throw new ContentLifecycleError('validation', 'one or more terms are unavailable', 'termIds')
  const found = new Set(rows.map((row) => row.id))
  if (termIds.some((id) => !found.has(id))) throw new ContentLifecycleError('validation', 'one or more terms are unavailable', 'termIds')
  for (const row of rows) if (!type.taxonomies.includes(row.taxonomy)) throw new ContentLifecycleError('validation', `taxonomy ${row.taxonomy} is not assigned to content type ${type.key}`, 'termIds')
}
async function replaceTerms(tx: ContentTransaction, entryId: string, termIds: readonly string[]): Promise<void> {
  await tx.execute(sql`DELETE FROM content_entry_terms WHERE entry_id = ${entryId}`)
  for (const termId of termIds) await tx.execute(sql`INSERT INTO content_entry_terms (entry_id, term_id) VALUES (${entryId}, ${termId})`)
}
async function initialStatus(tx: ContentTransaction, type: ResolvedContentType): Promise<ResolvedContentStatus> {
  const statuses = (await resolveContentStatuses(tx)).filter((status) => status.active && (type.statusKeys === undefined || type.statusKeys.includes(status.key)))
  const found = statuses.find((status) => status.key === 'draft') ?? statuses.find((status) => !status.published && !status.internal) ?? statuses[0]
  if (!found) throw new ContentLifecycleError('integrity', 'content type has no active status')
  return found
}
async function resolveExactTypeStatus(tx: ContentTransaction, entry: ContentEntry): Promise<{ type: ResolvedContentType; status: ResolvedContentStatus }> {
  try {
    const [type, status] = await Promise.all([resolveContentType(tx, entry.type), resolveContentStatus(tx, entry.status)])
    if (entry.typeDefinitionRevision !== type.revision || entry.statusDefinitionRevision !== status.revision) {
      throw new ContentLifecycleError('integrity', 'entry references stale definition revisions')
    }
    return { type, status }
  } catch (error) {
    if (error instanceof ContentLifecycleError) throw error
    throw new ContentLifecycleError('integrity', 'entry definition metadata is unavailable')
  }
}
function sanitizeHtml(sanitize: Sanitize, value: unknown, field: string): string {
  if (typeof sanitize !== 'function') throw new ContentLifecycleError('capability-unavailable', 'HTML sanitizer is required', field)
  if (typeof value !== 'string') throw new ContentLifecycleError('validation', 'must be a string', field)
  const safe = sanitize(value)
  if (typeof safe !== 'string') throw new ContentLifecycleError('integrity', 'sanitizer must return a string', field)
  return safe
}
function validateTemplate(type: ResolvedContentType, key: string | null | undefined, templates: ContentTemplateRegistry): string | null | undefined {
  if (key === undefined) return undefined
  assertFeature(type, 'pageAttributes', 'templateKey')
  if (key === null) return null
  const normalized = nonEmptyString(key, 'templateKey', 256)
  const descriptor = templates.resolve(type.key, normalized)
  if (!descriptor || !descriptor.typeKeys.includes(type.key)) throw new ContentLifecycleError('validation', 'template is unavailable for this content type', 'templateKey')
  return normalized
}
async function validateParent(tx: ContentTransaction, type: ResolvedContentType, entryId: string | undefined, parentId: string | null | undefined): Promise<string | null | undefined> {
  if (parentId === undefined) return undefined
  if (parentId !== null) nonEmptyString(parentId, 'parentId', 256)
  if (!type.hierarchical && parentId !== null) throw new ContentLifecycleError('validation', 'flat content types cannot have parents', 'parentId')
  await validateContentParent(tx, { ...(entryId ? { entryId } : {}), type, parentId })
  return parentId
}
function normalizeMenuOrder(type: ResolvedContentType, value: unknown): number | undefined {
  if (value === undefined) return undefined
  assertFeature(type, 'pageAttributes', 'menuOrder')
  if (typeof value !== 'number' || !Number.isSafeInteger(value) || value < -2_147_483_648 || value > 2_147_483_647) throw new ContentLifecycleError('validation', 'must be a 32-bit integer', 'menuOrder')
  return value
}
function normalizeCommentStatus(type: ResolvedContentType, value: unknown): 'open'|'closed'|undefined {
  if (value === undefined) return undefined
  assertFeature(type, 'comments', 'commentStatus')
  if (value !== 'open' && value !== 'closed') throw new ContentLifecycleError('validation', 'must be open or closed', 'commentStatus')
  return value
}
function normalizePingStatus(type: ResolvedContentType, value: unknown): 'open'|'closed'|undefined {
  if (value === undefined) return undefined
  assertFeature(type, 'trackbacks', 'pingStatus')
  if (value !== 'open' && value !== 'closed') throw new ContentLifecycleError('validation', 'must be open or closed', 'pingStatus')
  return value
}

function isUniqueViolation(error: unknown): boolean {
  let current: unknown = error
  while (current) {
    const code = (current as { code?: unknown }).code
    const message = current instanceof Error ? current.message : String(current)
    if (code === '23505' || /content_entries_type_slug_uq|duplicate key|unique constraint/i.test(message)) return true
    current = current instanceof Error ? (current as Error & { cause?: unknown }).cause : undefined
  }
  return false
}
async function termRefsByIds(tx: ContentTransaction, termIds: readonly string[]): Promise<TermRef[]> {
  if (termIds.length === 0) return []
  const rows = await tx.execute<{ id: string; taxonomy: string; slug: string; name: string; parentId: string | null; depth: number | string }>(sql`
    SELECT id, taxonomy, slug, name, parent_id AS "parentId", depth FROM content_terms
    WHERE id IN (${sql.join(termIds.map((id) => sql`${id}`), sql`, `)}) ORDER BY depth, name, id`)
  return rows.map((row) => Object.freeze({ id: row.id, taxonomy: row.taxonomy, slug: row.slug, name: row.name, parentId: row.parentId, depth: Number(row.depth) }))
}
function revisionSnapshot(entry: ContentEntry): ContentSchemaValue {
  return schemaValue({
    id: entry.id, slug: entry.slug, type: entry.type, title: entry.title, body: entry.body,
    status: entry.status, visibility: entry.visibility, publishedAt: entry.publishedAt?.toISOString() ?? null,
    author: entry.author, termIds: entry.terms.map((term) => term.id), parentId: entry.parentId,
    menuOrder: entry.menuOrder, templateKey: entry.templateKey, excerpt: entry.excerpt,
    featuredMedia: entry.featuredMedia, commentStatus: entry.commentStatus, pingStatus: entry.pingStatus,
    passwordProtected: entry.passwordProtected, sticky: entry.sticky, format: entry.format,
    deletedAt: entry.deletedAt?.toISOString() ?? null, lastEditedBy: entry.lastEditedBy,
    typeDefinitionRevision: entry.typeDefinitionRevision, statusDefinitionRevision: entry.statusDefinitionRevision,
    createdAt: entry.createdAt.toISOString(), updatedAt: entry.updatedAt.toISOString(),
  })
}
async function snapshotRevision(tx: ContentTransaction, entry: ContentEntry, editor: string): Promise<string> {
  const id = crypto.randomUUID()
  await tx.execute(sql`INSERT INTO content_revisions
    (id, entry_id, title, body, slug, type, term_ids, snapshot, editor, created_at)
    VALUES (${id}, ${entry.id}, ${entry.title}, ${entry.body}, ${entry.slug}, ${entry.type},
      ${JSON.stringify(entry.terms.map((term) => term.id))}, ${JSON.stringify(revisionSnapshot(entry))}, ${editor}, ${new Date().toISOString()})`)
  return id
}

interface NormalizedWrite {
  slug: string
  title: string
  body: string
  excerpt?: string
  visibility?: ContentVisibility
  author?: string
  termIds?: string[]
  parentId?: string | null
  menuOrder?: number
  templateKey?: string | null
  featuredMedia?: ContentMediaRef | null
  commentStatus?: 'open'|'closed'
  pingStatus?: 'open'|'closed'
  sticky?: boolean
  format?: string | null
}

async function normalizeWrite(
  tx: ContentTransaction,
  type: ResolvedContentType,
  raw: ContentWriteInput | ContentUpdatePatch,
  deps: ContentWriteDependencies,
  principal: ContentPrincipal,
  mode: 'create'|'update',
  entryId?: string,
): Promise<NormalizedWrite> {
  if (typeof raw !== 'object' || raw === null || Array.isArray(raw)) throw new ContentLifecycleError('validation', 'input must be an object')
  const candidate = raw as Record<string, unknown>
  const allowed = new Set(['slug','type','title','body','excerpt','visibility','author','termIds','parentId','menuOrder','templateKey','featuredMedia','commentStatus','pingStatus','sticky','format'])
  for (const key of Object.keys(candidate)) if (!allowed.has(key)) throw new ContentLifecycleError('validation', 'field is owned by another lifecycle operation', key)
  if (mode === 'update' && Object.prototype.hasOwnProperty.call(candidate, 'type')) throw new ContentLifecycleError('validation', 'content type reassignment requires definition migration machinery', 'type')
  if (mode === 'update' && Object.keys(candidate).length === 0) throw new ContentLifecycleError('validation', 'empty update patches are not allowed')

  const out: Partial<NormalizedWrite> = {}
  if (mode === 'create' || Object.prototype.hasOwnProperty.call(candidate, 'slug')) out.slug = nonEmptyString(candidate.slug, 'slug', 200)
  if (mode === 'create' || Object.prototype.hasOwnProperty.call(candidate, 'title')) {
    if (type.supports.includes('title')) out.title = nonEmptyString(candidate.title, 'title', 300)
    else {
      if (candidate.title !== '') throw new ContentLifecycleError('validation', 'title is not supported by this content type', 'title')
      out.title = ''
    }
  }
  if (mode === 'create' || Object.prototype.hasOwnProperty.call(candidate, 'body')) {
    if (type.supports.includes('editor') || type.supports.includes('blocks')) out.body = sanitizeHtml(deps.sanitize, candidate.body, 'body')
    else {
      if (candidate.body !== '') throw new ContentLifecycleError('validation', 'body is not supported by this content type', 'body')
      out.body = ''
    }
  }
  if (Object.prototype.hasOwnProperty.call(candidate, 'excerpt')) {
    assertFeature(type, 'excerpt', 'excerpt')
    if (typeof candidate.excerpt !== 'string' || candidate.excerpt.length > 100_000) throw new ContentLifecycleError('validation', 'must be a string up to 100000 characters', 'excerpt')
    out.excerpt = candidate.excerpt
  }
  if (Object.prototype.hasOwnProperty.call(candidate, 'visibility')) out.visibility = assertVisibility(candidate.visibility)
  if (Object.prototype.hasOwnProperty.call(candidate, 'author')) {
    assertFeature(type, 'author', 'author')
    const target = nonEmptyString(candidate.author, 'author', 256)
    if (!deps.authors) throw new ContentLifecycleError('capability-unavailable', 'author authority adapter is required', 'author')
    await deps.authors.assertAssign(principal, target)
    out.author = target
  }
  if (Object.prototype.hasOwnProperty.call(candidate, 'termIds')) {
    const termIds = uniqueStrings(candidate.termIds, 'termIds')
    authorizeContentAction({ principal, type, operation: 'manageTerms' })
    await validateTerms(tx, type, termIds)
    out.termIds = termIds
  }
  if (Object.prototype.hasOwnProperty.call(candidate, 'parentId')) out.parentId = await validateParent(tx, type, entryId, candidate.parentId as string|null)
  if (Object.prototype.hasOwnProperty.call(candidate, 'menuOrder')) out.menuOrder = normalizeMenuOrder(type, candidate.menuOrder)
  if (Object.prototype.hasOwnProperty.call(candidate, 'templateKey')) out.templateKey = validateTemplate(type, candidate.templateKey as string|null, deps.templates)
  if (Object.prototype.hasOwnProperty.call(candidate, 'featuredMedia')) {
    assertFeature(type, 'featuredImage', 'featuredMedia')
    const ref = assertMediaRef(candidate.featuredMedia)
    if (ref !== null) {
      if (!deps.capabilities?.validateMediaRef) throw new ContentLifecycleError('capability-unavailable', 'media validation adapter is required', 'featuredMedia')
      await deps.capabilities.validateMediaRef(ref, principal)
    }
    out.featuredMedia = ref
  }
  if (Object.prototype.hasOwnProperty.call(candidate, 'commentStatus')) out.commentStatus = normalizeCommentStatus(type, candidate.commentStatus)
  if (Object.prototype.hasOwnProperty.call(candidate, 'pingStatus')) out.pingStatus = normalizePingStatus(type, candidate.pingStatus)
  if (Object.prototype.hasOwnProperty.call(candidate, 'sticky')) {
    if (typeof candidate.sticky !== 'boolean') throw new ContentLifecycleError('validation', 'must be a boolean', 'sticky')
    out.sticky = candidate.sticky
  }
  if (Object.prototype.hasOwnProperty.call(candidate, 'format')) {
    assertFeature(type, 'postFormats', 'format')
    out.format = nullableString(candidate.format, 'format', 128)
  }
  return out as NormalizedWrite
}

export async function createContentEntry(
  tx: ContentTransaction,
  input: ContentWriteInput & { operationId: string },
  principal: ContentPrincipal,
  deps: ContentWriteDependencies,
): Promise<ContentEntry> {
  assertActiveContentTransaction(tx)
  const operationId = nonEmptyString(input.operationId, 'operationId', 256)
  const typeKey = nonEmptyString(input.type, 'type', 128)
  let type: ResolvedContentType
  try { type = await resolveContentType(tx, typeKey) } catch { throw new ContentLifecycleError('access-denied', 'content type is unavailable') }
  assertTypeActive(type)
  authorizeContentAction({ principal, type, operation: 'create' })
  const { operationId: _operationId, ...writeInput } = input
  const normalized = await normalizeWrite(tx, type, writeInput, deps, principal, 'create')
  const hash = await requestHash({ typeKey, input: normalized })
  const replay = await readReceipt(tx, operationId)
  if (replay) {
    const entry = await replayEntry(tx, replay, hash, principal)
    if (!entry) throw new ContentLifecycleError('operation-conflict', 'recorded create result is no longer available')
    return entry
  }
  const status = await initialStatus(tx, type)
  const now = new Date()
  const id = crypto.randomUUID()
  const termIds = normalized.termIds ?? []
  const termRefs = await termRefsByIds(tx, termIds)
  const proposed: ContentEntry = Object.freeze({
    id, slug: normalized.slug, type: type.key, title: normalized.title, body: normalized.body,
    status: status.key, visibility: normalized.visibility ?? 'public', publishedAt: null,
    author: normalized.author ?? principal.id, terms: termRefs, createdAt: now, updatedAt: now,
    parentId: normalized.parentId ?? null, menuOrder: normalized.menuOrder ?? 0,
    templateKey: normalized.templateKey ?? null, excerpt: normalized.excerpt ?? '',
    featuredMedia: normalized.featuredMedia ?? null, commentStatus: normalized.commentStatus ?? 'open',
    pingStatus: normalized.pingStatus ?? 'open', passwordProtected: false, sticky: normalized.sticky ?? false,
    format: normalized.format ?? null, deletedAt: null, lastEditedBy: principal.id,
    typeDefinitionRevision: type.revision, statusDefinitionRevision: status.revision,
  })
  const ctx = mutationContext(operationId, principal, type, null, proposed)
  await runValidateHooks(deps.hooks, ctx)
  await runBeforeCommit(deps.hooks, ctx)
  try {
    await tx.execute(sql`INSERT INTO content_entries
      (id, slug, type, title, body, status, visibility, published_at, author, created_at, updated_at,
       parent_id, menu_order, template_key, excerpt, featured_media, comment_status, ping_status, sticky,
       format, deleted_at, last_edited_by, type_definition_revision, status_definition_revision)
      VALUES (${id}, ${proposed.slug}, ${proposed.type}, ${proposed.title}, ${proposed.body}, ${proposed.status}, ${proposed.visibility}, NULL,
       ${proposed.author}, ${now.toISOString()}, ${now.toISOString()}, ${proposed.parentId}, ${proposed.menuOrder}, ${proposed.templateKey}, ${proposed.excerpt},
       ${proposed.featuredMedia === null ? null : JSON.stringify(proposed.featuredMedia)}, ${proposed.commentStatus}, ${proposed.pingStatus}, ${proposed.sticky},
       ${proposed.format}, NULL, ${principal.id}, ${type.revision}, ${status.revision})`)
  } catch (error) {
    if (isUniqueViolation(error)) throw new ContentLifecycleError('conflict', 'slug already exists for this content type', 'slug')
    throw error
  }
  await replaceTerms(tx, id, termIds)
  await writeReceipt(tx, { operationId, kind: 'content:create', entryId: id, requestHash: hash, principal, result: { id } })
  await enqueue(tx, deps.outbox, operationId, 'written', id, now)
  return (await readEntry(tx, id))!
}

export async function updateContentEntry(
  tx: ContentTransaction,
  id: string,
  input: { patch: ContentUpdatePatch; expectedUpdatedAt: Date; operationId: string },
  principal: ContentPrincipal,
  deps: ContentWriteDependencies,
): Promise<ContentEntry> {
  assertActiveContentTransaction(tx)
  const operationId = nonEmptyString(input.operationId, 'operationId', 256)
  const expectedUpdatedAt = exactDate(input.expectedUpdatedAt, 'expectedUpdatedAt')
  const before = await requireEntry(tx, id)
  const { type, status } = await resolveExactTypeStatus(tx, before)
  authorizeContentAction({ principal, type, status, operation: 'edit', entry: before })
  const normalized = await normalizeWrite(tx, type, input.patch, deps, principal, 'update', id)
  if (normalized.author !== undefined && normalized.author !== before.author) {
    if (!deps.authors) throw new ContentLifecycleError('capability-unavailable', 'author authority adapter is required', 'author')
    await deps.authors.assertTransfer(principal, before.author, normalized.author)
  }
  const hash = await requestHash({ id, expectedUpdatedAt: expectedUpdatedAt.toISOString(), patch: normalized })
  const replay = await readReceipt(tx, operationId)
  if (replay) {
    const entry = await replayEntry(tx, replay, hash, principal)
    if (!entry) throw new ContentLifecycleError('operation-conflict', 'recorded update result is no longer available')
    return entry
  }
  if (before.updatedAt.getTime() !== expectedUpdatedAt.getTime()) throw new ContentLifecycleError('stale-version', 'content changed after caller read it')

  const now = new Date()
  const termIds = normalized.termIds ?? before.terms.map((term) => term.id)
  const proposed = Object.freeze({
    ...before,
    ...(normalized.slug === undefined ? {} : { slug: normalized.slug }),
    ...(normalized.title === undefined ? {} : { title: normalized.title }),
    ...(normalized.body === undefined ? {} : { body: normalized.body }),
    ...(normalized.excerpt === undefined ? {} : { excerpt: normalized.excerpt }),
    ...(normalized.visibility === undefined ? {} : { visibility: normalized.visibility }),
    ...(normalized.author === undefined ? {} : { author: normalized.author }),
    terms: normalized.termIds === undefined ? before.terms : await termRefsByIds(tx, normalized.termIds),
    ...(normalized.parentId === undefined ? {} : { parentId: normalized.parentId }),
    ...(normalized.menuOrder === undefined ? {} : { menuOrder: normalized.menuOrder }),
    ...(normalized.templateKey === undefined ? {} : { templateKey: normalized.templateKey }),
    ...(normalized.featuredMedia === undefined ? {} : { featuredMedia: normalized.featuredMedia }),
    ...(normalized.commentStatus === undefined ? {} : { commentStatus: normalized.commentStatus }),
    ...(normalized.pingStatus === undefined ? {} : { pingStatus: normalized.pingStatus }),
    ...(normalized.sticky === undefined ? {} : { sticky: normalized.sticky }),
    ...(normalized.format === undefined ? {} : { format: normalized.format }),
    updatedAt: now,
    lastEditedBy: principal.id,
  }) satisfies ContentEntry
  const ctx = mutationContext(operationId, principal, type, before, proposed)
  await runValidateHooks(deps.hooks, ctx)
  await runBeforeCommit(deps.hooks, ctx)
  if (type.supports.includes('revisions')) await snapshotRevision(tx, before, principal.id)
  try {
    const updated = await tx.execute<{ id: string }>(sql`UPDATE content_entries SET
      slug = ${proposed.slug}, title = ${proposed.title}, body = ${proposed.body}, visibility = ${proposed.visibility},
      author = ${proposed.author}, parent_id = ${proposed.parentId}, menu_order = ${proposed.menuOrder}, template_key = ${proposed.templateKey},
      excerpt = ${proposed.excerpt}, featured_media = ${proposed.featuredMedia === null ? null : JSON.stringify(proposed.featuredMedia)},
      comment_status = ${proposed.commentStatus}, ping_status = ${proposed.pingStatus}, sticky = ${proposed.sticky}, format = ${proposed.format},
      last_edited_by = ${principal.id}, updated_at = ${now.toISOString()}
      WHERE id = ${id} AND updated_at = ${expectedUpdatedAt.toISOString()} RETURNING id`)
    if (updated.length !== 1) throw new ContentLifecycleError('stale-version', 'content changed during update')
  } catch (error) {
    if (isUniqueViolation(error)) throw new ContentLifecycleError('conflict', 'slug already exists for this content type', 'slug')
    throw error
  }
  if (normalized.termIds !== undefined) await replaceTerms(tx, id, termIds)
  await writeReceipt(tx, { operationId, kind: 'content:update', entryId: id, requestHash: hash, principal, result: { id } })
  await enqueue(tx, deps.outbox, operationId, 'written', id, now)
  return (await readEntry(tx, id))!
}

function resolvedFrom<T extends { key: string }>(values: readonly T[], key: string, field: string): T {
  const found = values.find((item) => item.key === key)
  if (!found) throw new ContentLifecycleError('validation', `${field} is unavailable`, field)
  return found
}
function checkMappedCapability(principal: ContentPrincipal, type: ResolvedContentType, key: keyof ResolvedContentType['capabilities']): void {
  const capability = type.capabilities[key]
  if (!principal.capabilities.has(capability)) throw new ContentLifecycleError('access-denied', 'content is unavailable')
}
function transitionRule(rules: readonly ContentTransitionRule[], from: string, to: string): ContentTransitionRule {
  const rule = rules.find((candidate) => candidate.to === to && (candidate.from === from || candidate.from === '*'))
  if (!rule) throw new ContentLifecycleError('validation', 'status transition is not allowed', 'to')
  return rule
}

export async function transitionContent(
  tx: ContentTransaction,
  id: string,
  input: { to: string; scheduleAt?: Date; publishedAt?: Date; expectedUpdatedAt: Date; operationId: string },
  principal: ContentPrincipal,
  deps: ContentTransitionDependencies,
): Promise<ContentEntry> {
  assertActiveContentTransaction(tx)
  const operationId = nonEmptyString(input.operationId, 'operationId', 256)
  const to = nonEmptyString(input.to, 'to', 128)
  const expectedUpdatedAt = exactDate(input.expectedUpdatedAt, 'expectedUpdatedAt')
  const before = await requireEntry(tx, id)
  const type = resolvedFrom(deps.types, before.type, 'type')
  const currentStatus = resolvedFrom(deps.statuses, before.status, 'status')
  if (before.typeDefinitionRevision !== type.revision || before.statusDefinitionRevision !== currentStatus.revision) throw new ContentLifecycleError('integrity', 'entry definition revisions do not match supplied registries')
  authorizeContentAction({ principal, type, status: currentStatus, operation: 'edit', entry: before })
  const target = resolvedFrom(deps.statuses, to, 'to')
  if (!target.active || (type.statusKeys !== undefined && !type.statusKeys.includes(target.key))) throw new ContentLifecycleError('validation', 'target status is not active for this content type', 'to')
  const rule = transitionRule(deps.rules, before.status, target.key)
  checkMappedCapability(principal, type, rule.capability)
  if (target.published) authorizeContentAction({ principal, type, status: currentStatus, operation: 'publish', entry: before })

  const scheduleAt = input.scheduleAt === undefined ? undefined : exactDate(input.scheduleAt, 'scheduleAt')
  const publishedAtInput = input.publishedAt === undefined ? undefined : exactDate(input.publishedAt, 'publishedAt')
  if (scheduleAt && publishedAtInput) throw new ContentLifecycleError('validation', 'scheduleAt and publishedAt are mutually exclusive')
  const now = new Date()
  let publicationDate: Date | null = null
  if (target.transitionInput === 'scheduleAt') {
    if (!scheduleAt || scheduleAt.getTime() <= now.getTime()) throw new ContentLifecycleError('validation', 'scheduled transition requires a future scheduleAt', 'scheduleAt')
    publicationDate = scheduleAt
  } else if (scheduleAt) throw new ContentLifecycleError('validation', 'scheduleAt is not accepted by the target status', 'scheduleAt')
  if (target.transitionInput === 'publishedAt') {
    publicationDate = publishedAtInput ?? now
  } else if (publishedAtInput) throw new ContentLifecycleError('validation', 'publishedAt is not accepted by the target status', 'publishedAt')
  if (rule.requiresScheduleAt && !scheduleAt) throw new ContentLifecycleError('validation', 'transition requires scheduleAt', 'scheduleAt')
  if (before.status === target.key && !(target.published && publishedAtInput)) throw new ContentLifecycleError('validation', 'same-status transition is not a lifecycle change', 'to')

  const hash = await requestHash({ id, to, scheduleAt: scheduleAt?.toISOString(), publishedAt: publishedAtInput?.toISOString(), expectedUpdatedAt: expectedUpdatedAt.toISOString() })
  const replay = await readReceipt(tx, operationId)
  if (replay) {
    const entry = await replayEntry(tx, replay, hash, principal)
    if (!entry) throw new ContentLifecycleError('operation-conflict', 'recorded transition result is no longer available')
    return entry
  }
  if (before.updatedAt.getTime() !== expectedUpdatedAt.getTime()) throw new ContentLifecycleError('stale-version', 'content changed after caller read it')
  const proposed: ContentEntry = Object.freeze({ ...before, status: target.key, statusDefinitionRevision: target.revision, publishedAt: publicationDate, updatedAt: now, lastEditedBy: principal.id })
  const ctx = mutationContext(operationId, principal, type, before, proposed)
  await runValidateHooks(deps.hooks, ctx)
  await runBeforeCommit(deps.hooks, ctx)
  if (type.supports.includes('revisions')) await snapshotRevision(tx, before, principal.id)
  const rows = await tx.execute<{ id: string }>(sql`UPDATE content_entries SET status = ${target.key}, status_definition_revision = ${target.revision},
    published_at = ${publicationDate?.toISOString() ?? null}, updated_at = ${now.toISOString()}, last_edited_by = ${principal.id}
    WHERE id = ${id} AND updated_at = ${expectedUpdatedAt.toISOString()} RETURNING id`)
  if (rows.length !== 1) throw new ContentLifecycleError('stale-version', 'content changed during transition')
  await writeReceipt(tx, { operationId, kind: 'content:transition', entryId: id, requestHash: hash, principal, result: { id } })
  await enqueue(tx, deps.outbox, operationId, 'transitioned', id, now)
  return (await readEntry(tx, id))!
}

async function simpleLifecycleStatus(
  tx: ContentTransaction,
  id: string,
  input: { expectedUpdatedAt: Date; operationId: string },
  principal: ContentPrincipal,
  deps: Omit<ContentTransitionDependencies, 'rules'>,
  targetKey: string,
  authorizationOperation: 'edit' | 'delete',
): Promise<ContentEntry> {
  const before = await requireEntry(tx, id)
  const type = resolvedFrom(deps.types, before.type, 'type')
  const currentStatus = resolvedFrom(deps.statuses, before.status, 'status')
  if (before.typeDefinitionRevision !== type.revision || before.statusDefinitionRevision !== currentStatus.revision) throw new ContentLifecycleError('integrity', 'entry definition revisions do not match supplied registries')
  authorizeContentAction({ principal, type, status: currentStatus, operation: authorizationOperation, entry: before })
  const target = resolvedFrom(deps.statuses, targetKey, 'status')
  if (!target.active || (type.statusKeys !== undefined && !type.statusKeys.includes(target.key))) throw new ContentLifecycleError('validation', 'lifecycle status is not active for this type')
  const operationId = nonEmptyString(input.operationId, 'operationId', 256)
  const expected = exactDate(input.expectedUpdatedAt, 'expectedUpdatedAt')
  const hash = await requestHash({ id, targetKey, expectedUpdatedAt: expected.toISOString() })
  const replay = await readReceipt(tx, operationId)
  if (replay) {
    const entry = await replayEntry(tx, replay, hash, principal)
    if (!entry) throw new ContentLifecycleError('operation-conflict', 'recorded lifecycle result is unavailable')
    return entry
  }
  if (before.updatedAt.getTime() !== expected.getTime()) throw new ContentLifecycleError('stale-version', 'content changed after caller read it')
  const now = new Date()
  const proposed: ContentEntry = Object.freeze({ ...before, status: target.key, statusDefinitionRevision: target.revision, publishedAt: null, updatedAt: now, lastEditedBy: principal.id })
  const ctx = mutationContext(operationId, principal, type, before, proposed)
  await runValidateHooks(deps.hooks, ctx)
  await runBeforeCommit(deps.hooks, ctx)
  if (type.supports.includes('revisions')) await snapshotRevision(tx, before, principal.id)
  const rows = await tx.execute<{ id: string }>(sql`UPDATE content_entries SET status = ${target.key}, status_definition_revision = ${target.revision}, published_at = NULL,
    updated_at = ${now.toISOString()}, last_edited_by = ${principal.id} WHERE id = ${id} AND updated_at = ${expected.toISOString()} RETURNING id`)
  if (rows.length !== 1) throw new ContentLifecycleError('stale-version', 'content changed during lifecycle mutation')
  await writeReceipt(tx, { operationId, kind: `content:${targetKey}`, entryId: id, requestHash: hash, principal, result: { id } })
  await enqueue(tx, deps.outbox, operationId, 'transitioned', id, now)
  return (await readEntry(tx, id))!
}
export function trashContent(tx: ContentTransaction, id: string, input: { expectedUpdatedAt: Date; operationId: string }, principal: ContentPrincipal, deps: Omit<ContentTransitionDependencies,'rules'>): Promise<ContentEntry> {
  return simpleLifecycleStatus(tx, id, input, principal, deps, 'trashed', 'delete')
}
export function restoreContent(tx: ContentTransaction, id: string, input: { expectedUpdatedAt: Date; operationId: string }, principal: ContentPrincipal, deps: Omit<ContentTransitionDependencies,'rules'>): Promise<ContentEntry> {
  return simpleLifecycleStatus(tx, id, input, principal, deps, 'draft', 'edit')
}

export interface ContentPasswordDependencies {
  passwords: ContentPasswordAdapter
  hooks?: ContentHooks
  outbox: ContentOutbox
}
export async function changeContentPassword(
  tx: ContentTransaction,
  id: string,
  input: { operation: 'set'; password: string; expectedUpdatedAt: Date; operationId: string } | { operation: 'clear'; expectedUpdatedAt: Date; operationId: string },
  principal: ContentPrincipal,
  deps: ContentPasswordDependencies,
): Promise<ContentEntry> {
  assertActiveContentTransaction(tx)
  const before = await requireEntry(tx, id)
  const { type, status } = await resolveExactTypeStatus(tx, before)
  authorizeContentAction({ principal, type, status, operation: 'edit', entry: before })
  const expected = exactDate(input.expectedUpdatedAt, 'expectedUpdatedAt')
  const operationId = nonEmptyString(input.operationId, 'operationId', 256)
  const requestIdentity = await requestHash({ id, operation: input.operation, expectedUpdatedAt: expected.toISOString() })
  const replay = await readReceipt(tx, operationId)
  if (replay) {
    const entry = await replayEntry(tx, replay, requestIdentity, principal)
    if (!entry) throw new ContentLifecycleError('operation-conflict', 'recorded password result is unavailable')
    return entry
  }
  if (before.updatedAt.getTime() !== expected.getTime()) throw new ContentLifecycleError('stale-version', 'content changed after caller read it')
  const now = new Date()
  let nextCredential: { credential: string; version: string } | null = null
  if (input.operation === 'set') {
    const password = nonEmptyString(input.password, 'password', 4096)
    const hashed = await deps.passwords.hash(password)
    if (!hashed || typeof hashed.credential !== 'string' || !hashed.credential || typeof hashed.version !== 'string' || !hashed.version) throw new ContentLifecycleError('integrity', 'password adapter returned an invalid credential')
    const current = await tx.execute<{ credentialVersion: string }>(sql`SELECT credential_version AS "credentialVersion" FROM content_password_credentials WHERE entry_id = ${id}`)
    if (current[0]?.credentialVersion === hashed.version) throw new ContentLifecycleError('integrity', 'password replacement must advance the credential version')
    nextCredential = hashed
  }
  const proposed: ContentEntry = Object.freeze({ ...before, passwordProtected: input.operation === 'set', updatedAt: now, lastEditedBy: principal.id })
  const ctx = mutationContext(operationId, principal, type, before, proposed)
  await runValidateHooks(deps.hooks, ctx)
  await runBeforeCommit(deps.hooks, ctx)
  if (nextCredential) {
    await tx.execute(sql`INSERT INTO content_password_credentials (entry_id, credential_version, credential, created_at, updated_at)
      VALUES (${id}, ${nextCredential.version}, ${nextCredential.credential}, ${now.toISOString()}, ${now.toISOString()})
      ON CONFLICT (entry_id) DO UPDATE SET credential_version = excluded.credential_version, credential = excluded.credential, updated_at = excluded.updated_at`)
  } else {
    await tx.execute(sql`DELETE FROM content_password_credentials WHERE entry_id = ${id}`)
  }
  const rows = await tx.execute<{ id: string }>(sql`UPDATE content_entries SET updated_at = ${now.toISOString()}, last_edited_by = ${principal.id}
    WHERE id = ${id} AND updated_at = ${expected.toISOString()} RETURNING id`)
  if (rows.length !== 1) throw new ContentLifecycleError('stale-version', 'content changed during password mutation')
  await writeReceipt(tx, { operationId, kind: `content:password:${input.operation}`, entryId: id, requestHash: requestIdentity, principal, result: { id } })
  await enqueue(tx, deps.outbox, operationId, 'written', id, now)
  return (await readEntry(tx, id))!
}

export async function verifyContentPassword(
  tx: ContentTransaction,
  id: string,
  password: string,
  adapter: ContentPasswordAdapter,
): Promise<string | null> {
  assertActiveContentTransaction(tx)
  const value = nonEmptyString(password, 'password', 4096)
  const rows = await tx.execute<{ credentialVersion: string; credential: string }>(sql`SELECT credential_version AS "credentialVersion", credential FROM content_password_credentials WHERE entry_id = ${id}`)
  const credential = rows[0]
  if (!credential || !await adapter.verify(value, credential.credential)) return null
  return adapter.issueProof(id, credential.credentialVersion)
}

export async function readProtectedContent(
  tx: ContentTransaction,
  id: string,
  principal: ContentPrincipal,
  adapter: ContentPasswordAdapter,
  proof?: string,
): Promise<ProtectedContentRead | null> {
  assertActiveContentTransaction(tx)
  const entry = await readEntry(tx, id)
  if (!entry || entry.deletedAt !== null) return null
  let type: ResolvedContentType, status: ResolvedContentStatus
  try { ({ type, status } = await resolveExactTypeStatus(tx, entry)) } catch { return null }
  try { authorizeContentAction({ principal, type, status, operation: 'read', entry }) } catch { return null }
  if (!entry.passwordProtected) return Object.freeze({ access: 'granted', entry })
  try {
    authorizeContentAction({ principal, type, status, operation: 'readProtected', entry })
    return Object.freeze({ access: 'granted', entry })
  } catch (error) {
    if (!(error instanceof ContentAuthorizationError)) throw error
  }
  const rows = await tx.execute<{ credentialVersion: string }>(sql`SELECT credential_version AS "credentialVersion" FROM content_password_credentials WHERE entry_id = ${id}`)
  const credential = rows[0]
  if (credential && typeof proof === 'string' && proof && await adapter.verifyProof(id, credential.credentialVersion, proof)) return Object.freeze({ access: 'granted', entry })
  const { body: _body, ...metadata } = entry
  return Object.freeze({ access: 'passwordRequired', entry: Object.freeze(metadata) })
}

function tombstoneEntry(snapshot: unknown): Pick<ContentEntry,'id'|'author'|'status'|'visibility'> & { type: string; typeDefinitionRevision: number; statusDefinitionRevision: number } {
  const value = plainObject(typeof snapshot === 'string' ? JSON.parse(snapshot) : snapshot)
  if (typeof value.id !== 'string' || typeof value.author !== 'string' || typeof value.status !== 'string' || typeof value.type !== 'string') throw new ContentLifecycleError('integrity', 'tombstone snapshot is malformed')
  if (value.visibility !== 'public' && value.visibility !== 'private' && value.visibility !== 'members') throw new ContentLifecycleError('integrity', 'tombstone visibility is malformed')
  return {
    id: value.id,
    author: value.author,
    status: value.status,
    visibility: value.visibility,
    type: value.type,
    typeDefinitionRevision: Number(value.typeDefinitionRevision),
    statusDefinitionRevision: Number(value.statusDefinitionRevision),
  }
}

export async function applyPermanentContentDelete(
  tx: ContentTransaction,
  plan: ContentPermanentDeletePlan,
  operationIdInput: string,
  principal: ContentPrincipal,
  deps: ContentPermanentDeleteDependencies,
): Promise<readonly ContentPermanentDeleteResult[]> {
  assertActiveContentTransaction(tx)
  const operationId = nonEmptyString(operationIdInput, 'operationId', 256)
  const hash = await requestHash({ planToken: plan.token })
  const replay = await readReceipt(tx, operationId)
  if (replay) {
    if (replay.requestHash !== hash || replay.principalScope !== principalScope(principal)) throw new ContentLifecycleError('operation-conflict', 'operation id was already used for another delete')
    if (!Array.isArray(replay.result)) throw new ContentLifecycleError('integrity', 'delete receipt is malformed')
    const tombstones = await tx.execute<{ entryId: string; snapshot: unknown }>(sql`
      SELECT entry_id AS "entryId", snapshot FROM content_tombstones WHERE operation_id = ${operationId} ORDER BY entry_id`)
    const byEntry = new Map(tombstones.map((item) => [item.entryId, item]))
    const results: ContentPermanentDeleteResult[] = []
    for (const raw of replay.result) {
      const item = plainObject(raw)
      if (typeof item.id !== 'string' || typeof item.parentDeletionRevisionId !== 'string') throw new ContentLifecycleError('integrity', 'delete receipt item is malformed')
      const tombstone = byEntry.get(item.id)
      if (!tombstone) throw new ContentLifecycleError('integrity', 'delete receipt lost its tombstone')
      const entry = tombstoneEntry(tombstone.snapshot)
      try {
        const [type, status] = await Promise.all([resolveContentType(tx, entry.type), resolveContentStatus(tx, entry.status)])
        authorizeContentAction({ principal, type, status, operation: 'delete', entry })
      } catch {
        throw new ContentLifecycleError('access-denied', 'content is unavailable')
      }
      results.push({ id: item.id, parentDeletionRevisionId: item.parentDeletionRevisionId })
    }
    return Object.freeze(results)
  }

  const current = await preparePermanentContentDelete(tx, plan.selected, principal)
  if (current.token !== plan.token || current.corpusVersion !== plan.corpusVersion || current.authorizationVersion !== plan.authorizationVersion) {
    throw new ContentLifecycleError('stale-version', 'delete hierarchy or authorization changed after preparation')
  }
  if (current.reparent.length !== plan.reparent.length || current.reparent.some((item, index) => {
    const expected = plan.reparent[index]
    return !expected || item.childId !== expected.childId || item.fromParentId !== expected.fromParentId || item.toParentId !== expected.toParentId || item.expectedUpdatedAt.getTime() !== expected.expectedUpdatedAt.getTime()
  })) throw new ContentLifecycleError('stale-version', 'delete reparent plan changed after preparation')

  const selectedEntries = new Map<string, ContentEntry>()
  for (const item of plan.selected) selectedEntries.set(item.id, await requireEntry(tx, item.id))
  const childEntries = new Map<string, ContentEntry>()
  for (const item of plan.reparent) childEntries.set(item.childId, await requireEntry(tx, item.childId))

  for (const item of plan.selected) {
    const before = selectedEntries.get(item.id)!
    const type = await resolveContentType(tx, before.type)
    const ctx = mutationContext(operationId, principal, type, before, null)
    await runValidateHooks(deps.hooks, ctx)
    await runBeforeCommit(deps.hooks, ctx)
  }
  for (const item of plan.reparent) {
    const before = childEntries.get(item.childId)!
    const type = await resolveContentType(tx, before.type)
    const proposed: ContentEntry = Object.freeze({ ...before, parentId: item.toParentId })
    const ctx = mutationContext(operationId, principal, type, before, proposed)
    await runValidateHooks(deps.hooks, ctx)
    await runBeforeCommit(deps.hooks, ctx)
  }

  const now = new Date()
  for (const item of plan.reparent) {
    const rows = await tx.execute<{ id: string }>(sql`UPDATE content_entries SET parent_id = ${item.toParentId}, updated_at = ${now.toISOString()}, last_edited_by = ${principal.id}
      WHERE id = ${item.childId} AND parent_id = ${item.fromParentId} AND updated_at = ${item.expectedUpdatedAt.toISOString()} RETURNING id`)
    if (rows.length !== 1) throw new ContentLifecycleError('stale-version', 'reparented child changed during delete')
  }

  const results: ContentPermanentDeleteResult[] = []
  for (const item of plan.selected) {
    const before = selectedEntries.get(item.id)!
    const tombstoneId = crypto.randomUUID()
    await tx.execute(sql`INSERT INTO content_tombstones
      (id, entry_id, operation_id, type_key, type_definition_revision, status_key, status_definition_revision, snapshot, deleted_at)
      VALUES (${tombstoneId}, ${before.id}, ${operationId}, ${before.type}, ${before.typeDefinitionRevision}, ${before.status}, ${before.statusDefinitionRevision},
        ${JSON.stringify(revisionSnapshot(before))}, ${now.toISOString()})`)
    const deleted = await tx.execute<{ id: string }>(sql`DELETE FROM content_entries WHERE id = ${before.id} AND updated_at = ${item.expectedUpdatedAt.toISOString()} RETURNING id`)
    if (deleted.length !== 1) throw new ContentLifecycleError('stale-version', 'selected content changed during delete')
    results.push({ id: before.id, parentDeletionRevisionId: tombstoneId })
  }
  for (const item of plan.reparent) await enqueue(tx, deps.outbox, operationId, 'reparented', item.childId, now)
  for (const result of results) await enqueue(tx, deps.outbox, operationId, 'deleted', result.id, now)
  await writeReceipt(tx, { operationId, kind: 'content:permanent-delete', requestHash: hash, principal, result: results })
  return Object.freeze(results)
}

function revisionFromRow(row: Record<string, unknown>): ContentRevision {
  const termIdsValue = parseJson(row.termIds)
  const snapshotValue = parseJson(row.snapshot)
  return Object.freeze({
    id: String(row.id), entryId: String(row.entryId), seq: Number(row.seq), title: String(row.title), body: String(row.body),
    slug: String(row.slug), type: String(row.type), termIds: Array.isArray(termIdsValue) ? termIdsValue.map(String) : [],
    ...(snapshotValue && typeof snapshotValue === 'object' && !Array.isArray(snapshotValue) ? { snapshot: snapshotValue as Readonly<Record<string, unknown>> } : {}),
    editor: String(row.editor), createdAt: asDate(row.createdAt as string|Date, 'createdAt'),
  })
}

export async function listContentRevisions(
  tx: ContentTransaction,
  entryId: string,
  principal: ContentPrincipal,
  input: { before?: number; limit?: number } = {},
): Promise<{ items: readonly ContentRevision[]; nextCursor: number | null }> {
  assertActiveContentTransaction(tx)
  const entry = await requireEntry(tx, entryId)
  const { type, status } = await resolveExactTypeStatus(tx, entry)
  authorizeContentAction({ principal, type, status, operation: 'edit', entry })
  if (!type.supports.includes('revisions')) throw new ContentLifecycleError('capability-unavailable', 'revisions are disabled for this content type')
  const limit = input.limit === undefined ? 20 : input.limit
  if (!Number.isSafeInteger(limit) || limit < 1 || limit > 100) throw new ContentLifecycleError('validation', 'limit must be 1..100', 'limit')
  if (input.before !== undefined && (!Number.isSafeInteger(input.before) || input.before < 1)) throw new ContentLifecycleError('validation', 'before must be a positive revision sequence', 'before')
  const rows = await tx.execute<Record<string, unknown>>(input.before === undefined
    ? sql`SELECT id, entry_id AS "entryId", seq, title, body, slug, type, term_ids AS "termIds", snapshot, editor, created_at AS "createdAt"
        FROM content_revisions WHERE entry_id = ${entryId} ORDER BY seq DESC LIMIT ${limit + 1}`
    : sql`SELECT id, entry_id AS "entryId", seq, title, body, slug, type, term_ids AS "termIds", snapshot, editor, created_at AS "createdAt"
        FROM content_revisions WHERE entry_id = ${entryId} AND seq < ${input.before} ORDER BY seq DESC LIMIT ${limit + 1}`)
  const more = rows.length > limit
  const page = more ? rows.slice(0, limit) : rows
  const items = page.map(revisionFromRow)
  return Object.freeze({ items: Object.freeze(items), nextCursor: more ? items[items.length - 1]!.seq : null })
}

export async function getContentRevision(tx: ContentTransaction, entryId: string, revisionId: string, principal: ContentPrincipal): Promise<ContentRevision> {
  assertActiveContentTransaction(tx)
  const entry = await requireEntry(tx, entryId)
  const { type, status } = await resolveExactTypeStatus(tx, entry)
  authorizeContentAction({ principal, type, status, operation: 'edit', entry })
  const rows = await tx.execute<Record<string, unknown>>(sql`SELECT id, entry_id AS "entryId", seq, title, body, slug, type, term_ids AS "termIds", snapshot, editor, created_at AS "createdAt"
    FROM content_revisions WHERE id = ${revisionId} AND entry_id = ${entryId}`)
  if (!rows[0]) throw new ContentLifecycleError('access-denied', 'revision is unavailable')
  return revisionFromRow(rows[0])
}

export async function restoreContentRevision(
  tx: ContentTransaction,
  entryId: string,
  revisionId: string,
  input: { expectedUpdatedAt: Date; operationId: string },
  principal: ContentPrincipal,
  deps: ContentWriteDependencies,
): Promise<ContentEntry> {
  const revision = await getContentRevision(tx, entryId, revisionId, principal)
  if (!revision.snapshot) throw new ContentLifecycleError('integrity', 'revision predates full snapshot support')
  const snapshot = revision.snapshot
  const patch: ContentUpdatePatch = {
    slug: String(snapshot.slug), title: String(snapshot.title), body: String(snapshot.body), excerpt: String(snapshot.excerpt ?? ''),
    visibility: snapshot.visibility as ContentVisibility,
    author: String(snapshot.author), termIds: Array.isArray(snapshot.termIds) ? snapshot.termIds.map(String) : [],
    parentId: snapshot.parentId === null ? null : String(snapshot.parentId), menuOrder: Number(snapshot.menuOrder ?? 0),
    templateKey: snapshot.templateKey === null ? null : String(snapshot.templateKey),
    featuredMedia: snapshot.featuredMedia === null ? null : snapshot.featuredMedia as ContentMediaRef,
    commentStatus: snapshot.commentStatus as 'open'|'closed', pingStatus: snapshot.pingStatus as 'open'|'closed',
    sticky: Boolean(snapshot.sticky), format: snapshot.format === null ? null : String(snapshot.format),
  }
  return updateContentEntry(tx, entryId, { patch, expectedUpdatedAt: input.expectedUpdatedAt, operationId: input.operationId }, principal, deps)
}

function autosaveFromValue(value: ContentSchemaValue): ContentAutosave {
  const raw = plainObject(value)
  if (typeof raw.id !== 'string' || typeof raw.entryId !== 'string' || typeof raw.parentRevisionId !== 'string' || typeof raw.createdBy !== 'string' || typeof raw.createdAt !== 'string') throw new ContentLifecycleError('integrity', 'autosave payload is malformed')
  return Object.freeze({ id: raw.id, entryId: raw.entryId, parentRevisionId: raw.parentRevisionId, snapshot: Object.freeze(plainObject(raw.snapshot)) as ContentUpdatePatch, createdAt: asDate(raw.createdAt, 'createdAt'), createdBy: raw.createdBy })
}

export async function createContentAutosave(
  tx: ContentTransaction,
  entryId: string,
  input: { snapshot: ContentUpdatePatch; parentRevisionId: string; operationId: string },
  principal: ContentPrincipal,
  deps: ContentWriteDependencies,
): Promise<ContentAutosave> {
  assertActiveContentTransaction(tx)
  const entry = await requireEntry(tx, entryId)
  const { type, status } = await resolveExactTypeStatus(tx, entry)
  authorizeContentAction({ principal, type, status, operation: 'edit', entry })
  if (type.rest === false || !type.rest.autosaves || !type.rest.revisions || !type.supports.includes('revisions')) throw new ContentLifecycleError('capability-unavailable', 'autosaves are disabled for this content type')
  const normalized = await normalizeWrite(tx, type, input.snapshot, deps, principal, 'update', entryId)
  if (normalized.author !== undefined && normalized.author !== entry.author) {
    if (!deps.authors) throw new ContentLifecycleError('capability-unavailable', 'author authority adapter is required', 'author')
    await deps.authors.assertTransfer(principal, entry.author, normalized.author)
  }
  const revisions = await tx.execute<{ id: string }>(sql`SELECT id FROM content_revisions WHERE id = ${input.parentRevisionId} AND entry_id = ${entryId}`)
  if (!revisions[0]) throw new ContentLifecycleError('validation', 'parent revision is unavailable', 'parentRevisionId')
  const operationId = nonEmptyString(input.operationId, 'operationId', 256)
  const hash = await requestHash({ entryId, parentRevisionId: input.parentRevisionId, snapshot: normalized })
  const replay = await readReceipt(tx, operationId)
  if (replay) {
    if (replay.requestHash !== hash || replay.principalScope !== principalScope(principal)) throw new ContentLifecycleError('operation-conflict', 'autosave operation id was reused')
    return autosaveFromValue(replay.result)
  }
  const now = new Date()
  const autosave: ContentAutosave = Object.freeze({ id: crypto.randomUUID(), entryId, parentRevisionId: input.parentRevisionId, snapshot: Object.freeze(normalized), createdAt: now, createdBy: principal.id })
  const payload: LifecycleReceipt = { requestHash: hash, principalScope: principalScope(principal), result: schemaValue({ ...autosave, createdAt: now.toISOString() }) }
  await tx.execute(sql`INSERT INTO content_lifecycle_journal (id, operation_id, kind, entry_id, state, payload, created_at, updated_at)
    VALUES (${autosave.id}, ${operationId}, 'content:autosave', ${entryId}, 'committed', ${JSON.stringify(payload)}, ${now.toISOString()}, ${now.toISOString()})`)
  return autosave
}

export async function getContentAutosave(tx: ContentTransaction, entryId: string, autosaveId: string, principal: ContentPrincipal): Promise<ContentAutosave> {
  assertActiveContentTransaction(tx)
  const entry = await requireEntry(tx, entryId)
  const { type, status } = await resolveExactTypeStatus(tx, entry)
  authorizeContentAction({ principal, type, status, operation: 'edit', entry })
  const rows = await tx.execute<ReceiptRow>(sql`SELECT payload FROM content_lifecycle_journal WHERE id = ${autosaveId} AND entry_id = ${entryId} AND kind = 'content:autosave'`)
  if (!rows[0]) throw new ContentLifecycleError('access-denied', 'autosave is unavailable')
  const payload = typeof rows[0].payload === 'string' ? JSON.parse(rows[0].payload) : rows[0].payload
  const receipt = plainObject(payload)
  return autosaveFromValue(receipt.result as ContentSchemaValue)
}

export async function restoreContentAutosave(
  tx: ContentTransaction,
  entryId: string,
  autosaveId: string,
  input: { expectedUpdatedAt: Date; operationId: string },
  principal: ContentPrincipal,
  deps: ContentWriteDependencies,
): Promise<ContentEntry> {
  const autosave = await getContentAutosave(tx, entryId, autosaveId, principal)
  return updateContentEntry(tx, entryId, { patch: autosave.snapshot, expectedUpdatedAt: input.expectedUpdatedAt, operationId: input.operationId }, principal, deps)
}