import { sql } from 'drizzle-orm'
import { canonicalContentMigrationHash } from './migrations/content-model.js'
import type { ContentSchemaValue } from './schema.js'
import {
  ContentDefinitionError,
  principalScope,
  readDefinitionReceipt,
  writeDefinitionReceipt,
  writeDefinitionVersion,
  type ContentDefinitionExecutor,
} from './registry.js'
import type {
  ContentDefinitionAuthorization,
  ContentDefinitionEffects,
  ContentDefinitionEvent,
} from './definition-lifecycle.js'
import type { ContentPrincipal } from './authz.js'

export type ContentStatusDateLabel = 'published' | 'scheduled' | 'lastModified' | 'created'
export type ContentStatusTransitionInput = 'none' | 'scheduleAt' | 'publishedAt'

export interface ContentStatusDefinition {
  key: string
  label: string
  published: boolean
  internal: boolean
  excludeFromSearch: boolean
  publiclyQueryable: boolean
  showInAdminAll: boolean
  showInAdminStatusFilter: boolean
  dateLabel: ContentStatusDateLabel
  transitionInput: ContentStatusTransitionInput
  position?: number
}

export type CodeContentStatus = ContentStatusDefinition & { readonly origin: 'code' }
export interface ResolvedContentStatus extends ContentStatusDefinition {
  readonly origin: 'code' | 'db'
  readonly version: number
  readonly revision: number
  readonly active: boolean
  readonly canonicalHash: string
  readonly shadowedDbVersion?: number
}
export type ContentStatusPatch = Partial<Omit<ContentStatusDefinition, 'key'>>
export type ContentStatusChangeStrategy = { kind: 'reject' } | { kind: 'map'; targetStatusKey: string }

const STATUS_KEY = /^[a-z][a-z0-9_-]{0,63}$/
const DATE_LABELS = new Set<ContentStatusDateLabel>(['published', 'scheduled', 'lastModified', 'created'])
const TRANSITION_INPUTS = new Set<ContentStatusTransitionInput>(['none', 'scheduleAt', 'publishedAt'])

function invalid(detail: string, field?: string): never {
  throw new ContentDefinitionError('invalid-definition', detail, field)
}

function bounded(value: unknown, field: string, max = 256): string {
  if (typeof value !== 'string') invalid('must be a string', field)
  const normalized = value.trim()
  if (normalized.length === 0 || normalized.length > max) invalid(`must contain 1..${max} characters`, field)
  return normalized
}

export function normalizeContentStatusDefinition(definition: ContentStatusDefinition): ContentStatusDefinition {
  if (!STATUS_KEY.test(definition.key)) invalid('must be a stable lowercase machine key', 'key')
  if (!DATE_LABELS.has(definition.dateLabel)) invalid('unknown date label', 'dateLabel')
  if (!TRANSITION_INPUTS.has(definition.transitionInput)) invalid('unknown transition input', 'transitionInput')
  if (definition.transitionInput === 'scheduleAt' && definition.key !== 'scheduled') {
    invalid('scheduleAt is reserved for the scheduled status', 'transitionInput')
  }
  if (definition.transitionInput === 'publishedAt' && !definition.published) {
    invalid('publishedAt requires a published status', 'transitionInput')
  }
  if (definition.dateLabel === 'scheduled' && definition.transitionInput !== 'scheduleAt') {
    invalid('scheduled date label requires scheduleAt input', 'dateLabel')
  }
  if (definition.position !== undefined && (!Number.isSafeInteger(definition.position) || !Number.isFinite(definition.position))) {
    invalid('must be a safe integer', 'position')
  }
  return Object.freeze({
    key: definition.key,
    label: bounded(definition.label, 'label'),
    published: Boolean(definition.published),
    internal: Boolean(definition.internal),
    excludeFromSearch: Boolean(definition.excludeFromSearch),
    publiclyQueryable: Boolean(definition.publiclyQueryable),
    showInAdminAll: Boolean(definition.showInAdminAll),
    showInAdminStatusFilter: Boolean(definition.showInAdminStatusFilter),
    dateLabel: definition.dateLabel,
    transitionInput: definition.transitionInput,
    ...(definition.position === undefined ? {} : { position: definition.position }),
  })
}

export function defineContentStatus(definition: ContentStatusDefinition): CodeContentStatus {
  return Object.freeze({ ...normalizeContentStatusDefinition(definition), origin: 'code' as const })
}

interface StatusRow extends Record<string, unknown> {
  key: string
  origin: string
  version: number | string
  currentRevision: number | string
  canonicalHash: string
  definition: unknown
  active: boolean | number | string
  shadowedDbVersion: number | string | null
}

function bool(value: unknown): boolean {
  return value === true || value === 1 || value === '1'
}

function parseStatus(value: unknown): ContentStatusDefinition {
  const parsed = typeof value === 'string' ? JSON.parse(value) as unknown : value
  return normalizeContentStatusDefinition(parsed as ContentStatusDefinition)
}

async function statusRows(db: ContentDefinitionExecutor, key?: string): Promise<readonly StatusRow[]> {
  const where = key === undefined ? sql.raw('') : sql` WHERE key = ${key}`
  return db.execute<StatusRow>(sql`SELECT key, origin, version, current_revision AS "currentRevision", canonical_hash AS "canonicalHash", definition, active, shadowed_db_version AS "shadowedDbVersion" FROM content_status_definitions${where}`)
}

export async function resolveContentStatuses(
  db: ContentDefinitionExecutor,
  opts: { codeStatuses?: readonly CodeContentStatus[] } = {},
): Promise<ResolvedContentStatus[]> {
  const rows = await statusRows(db)
  const code = new Map((opts.codeStatuses ?? []).map((definition) => [definition.key, defineContentStatus(definition)]))
  const dbRows = new Map(rows.map((row) => [row.key, row]))
  const resolved: ResolvedContentStatus[] = []

  for (const definition of code.values()) {
    const collision = dbRows.get(definition.key)
    resolved.push(Object.freeze({
      ...definition,
      active: true,
      version: collision?.origin === 'code' ? Number(collision.version) : 1,
      revision: collision?.origin === 'code' ? Number(collision.currentRevision) : 1,
      canonicalHash: await canonicalContentMigrationHash(definition),
      ...(collision && collision.origin !== 'code' ? { shadowedDbVersion: Number(collision.version) } : {}),
    }))
  }

  for (const row of rows) {
    if (code.has(row.key)) continue
    const definition = parseStatus(row.definition)
    resolved.push(Object.freeze({
      ...definition,
      active: bool(row.active),
      origin: row.origin === 'code' ? 'code' : 'db',
      version: Number(row.version),
      revision: Number(row.currentRevision),
      canonicalHash: row.canonicalHash,
      ...(row.shadowedDbVersion === null ? {} : { shadowedDbVersion: Number(row.shadowedDbVersion) }),
    }))
  }

  return resolved.sort((left, right) =>
    (left.position ?? 0) - (right.position ?? 0)
      || left.label.localeCompare(right.label)
      || left.key.localeCompare(right.key))
}

export async function resolveContentStatus(
  db: ContentDefinitionExecutor,
  key: string,
  opts: { codeStatuses?: readonly CodeContentStatus[] } = {},
): Promise<ResolvedContentStatus> {
  const status = (await resolveContentStatuses(db, opts)).find((item) => item.key === key)
  if (!status) throw new ContentDefinitionError('definition-not-found', 'content status is not registered')
  return status
}

export async function createContentStatus(
  db: ContentDefinitionExecutor,
  principal: ContentPrincipal,
  input: { definition: ContentStatusDefinition; operationId: string },
  authorization: ContentDefinitionAuthorization,
  effects: ContentDefinitionEffects,
  opts: { codeStatuses?: readonly CodeContentStatus[] } = {},
): Promise<ResolvedContentStatus> {
  const normalized = normalizeContentStatusDefinition(input.definition)
  const { policyVersion } = await authorization.assert(db, principal, 'createStatus', normalized.key)
  if ((opts.codeStatuses ?? []).some((definition) => definition.key === normalized.key)) {
    throw new ContentDefinitionError('definition-conflict', 'database status cannot shadow a code-owned key', 'key')
  }
  const requestHash = await canonicalContentMigrationHash({ definition: normalized, operationId: input.operationId })
  const scope = principalScope(principal)
  const replay = await readDefinitionReceipt(db, input.operationId)
  if (replay) {
    if (replay.requestHash !== requestHash || replay.principalScope !== scope) {
      throw new ContentDefinitionError('operation-conflict', 'operation id was already used for different bytes or scope')
    }
    return Object.freeze(replay.result as unknown as ResolvedContentStatus)
  }
  if ((await statusRows(db, normalized.key)).length !== 0) {
    throw new ContentDefinitionError('definition-conflict', 'content status key already exists', 'key')
  }

  const canonicalHash = await canonicalContentMigrationHash(normalized)
  const now = new Date().toISOString()
  await db.execute(sql`INSERT INTO content_status_definitions (key, origin, version, active, current_revision, canonical_hash, definition, created_at, updated_at)
    VALUES (${normalized.key}, 'db', 1, ${true}, 1, ${canonicalHash}, ${JSON.stringify(normalized)}, ${now}, ${now})`)
  await writeDefinitionVersion(db, { kind: 'status', key: normalized.key, revision: 1, canonicalHash, definition: normalized as unknown as ContentSchemaValue, origin: 'db' })
  const result: ResolvedContentStatus = Object.freeze({ ...normalized, origin: 'db', version: 1, revision: 1, active: true, canonicalHash })
  await writeDefinitionReceipt(db, { operationId: input.operationId, kind: 'definition:create:status', payload: { requestHash, principalScope: scope, result: result as unknown as ContentSchemaValue } })
  const event: ContentDefinitionEvent = {
    id: crypto.randomUUID(), version: 1, kind: 'dbDefinitionCreated', operationId: input.operationId,
    principalId: principal.id, definitionKind: 'status', definitionKey: normalized.key,
    definitionHash: canonicalHash, revision: 1, authorizationPolicyVersion: policyVersion,
  }
  await effects.audit.record(db, event)
  await effects.outbox.enqueue(db, event)
  return result
}

export const __contentStatusInternals = Object.freeze({ statusRows, parseStatus, bool })