import { and, desc, eq, lte, ne, or, sql, type SQL } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import { contentEntries, type ContentSchema } from './schema.js'
import {
  replaceEntryTerms,
  termsForEntry,
  validateTermIdsExist,
} from './taxonomy.js'
import {
  ContentSanitizationError,
  ContentValidationError,
  UUID_RE,
  normalizeContentInput,
  type ContentEntry,
  type ContentInput,
  type ContentStatus,
  type ContentVisibility,
  type Sanitize,
} from './model.js'
import { assertCanModify, assertCanPublish, type Actor } from './authz.js'

export type EntityRef = { id: string; slug: string; type: string }

export type ListQuery = {
  type?: string
  status?: ContentStatus
  term?: string
  includeDescendants?: boolean
  limit?: number
  offset?: number
}

/**
 * Tenancy readiness seam (spec §4.4 / CLAUDE.md §5). NO-OP in the single-install public schema
 * (no scope column). The private SaaS supplies its own schema + a store variant that consumes
 * `scope`. Accepting it here keeps the public contract stable across that swap — no tenant_id leak.
 */
export type StoreOpts = { scope?: string }

export class ContentNotFoundError extends Error {
  override readonly name = 'ContentNotFoundError'
  constructor(readonly selector: string) {
    super(`content entry not found: ${selector}`)
  }
}

/**
 * A duplicate (type, slug) hits content_entries_type_slug_uq. Surface it as a TYPED, contextful
 * boundary error (coding-standard §4) instead of leaking the raw Postgres error out of the seam.
 * DB-index-enforced + caught = race-safe (no pre-check TOCTOU).
 */
export class ContentConflictError extends Error {
  override readonly name = 'ContentConflictError'
  constructor(
    readonly type: string,
    readonly slug: string,
  ) {
    super(`content entry already exists: type=${type} slug=${slug}`)
  }
}

const VISIBILITY_LITERALS: ContentVisibility[] = ['public', 'private', 'members']

function assertVisibilityLiteral(visibility: ContentVisibility): void {
  if (!VISIBILITY_LITERALS.includes(visibility)) {
    throw new ContentValidationError('visibility', 'must be public|private|members')
  }
}

/** SQL read-authz floor — never post-filter in JS (spec §3). Shared by list/getBySlug/search. */
export function visibilityPredicate(viewer?: Actor | null): SQL | undefined {
  if (viewer?.canEditAny) return undefined
  if (!viewer) {
    return and(eq(contentEntries.status, 'published'), eq(contentEntries.visibility, 'public'))
  }
  const clauses: SQL[] = [
    eq(contentEntries.author, viewer.id),
    and(eq(contentEntries.status, 'published'), eq(contentEntries.visibility, 'public'))!,
  ]
  if (viewer.canViewMembers) {
    clauses.push(and(eq(contentEntries.status, 'published'), eq(contentEntries.visibility, 'members'))!)
  }
  return or(...clauses)!
}

/** Detect a unique-constraint violation without coupling to one driver's error shape. */
function isUniqueViolation(e: unknown): boolean {
  let cur: unknown = e
  while (cur) {
    const code = (cur as { code?: unknown })?.code
    const msg = cur instanceof Error ? cur.message : String(cur)
    if (code === '23505' || /content_entries_type_slug_uq|duplicate key|unique constraint/i.test(msg)) return true
    cur = cur instanceof Error ? (cur as Error & { cause?: unknown }).cause : undefined
  }
  return false
}

async function catchConflict<T>(type: string, slug: string, fn: () => Promise<T>): Promise<T> {
  try {
    return await fn()
  } catch (e) {
    if (isUniqueViolation(e)) throw new ContentConflictError(type, slug)
    throw e
  }
}

const DEFAULT_LIMIT = 20
const MAX_LIMIT = 100

type Row = typeof contentEntries.$inferSelect

function toEntry(row: Row, terms: ContentEntry['terms']): ContentEntry {
  return {
    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,
    author: row.author,
    terms,
    createdAt: row.createdAt,
    updatedAt: row.updatedAt,
  }
}

async function entryWithTerms(db: Querier<ContentSchema>, row: Row): Promise<ContentEntry> {
  return toEntry(row, await termsForEntry(db, row.id))
}

const REF_COLS = { id: contentEntries.id, slug: contentEntries.slug, type: contentEntries.type }

async function getRow(db: Querier<ContentSchema>, id: string): Promise<Row> {
  const [row] = await db.select().from(contentEntries).where(eq(contentEntries.id, id)).limit(1)
  if (!row) throw new ContentNotFoundError(`id=${id}`)
  return row
}

export async function list(
  db: Querier<ContentSchema>,
  query: ListQuery = {},
  viewer?: Actor | null,
  _opts: StoreOpts = {},
): Promise<ContentEntry[]> {
  const limit = Math.min(Math.max(query.limit ?? DEFAULT_LIMIT, 1), MAX_LIMIT)
  const offset = Math.max(query.offset ?? 0, 0)
  const filters: (SQL | undefined)[] = []
  const vis = visibilityPredicate(viewer)
  if (vis) filters.push(vis)
  if (query.type) filters.push(eq(contentEntries.type, query.type))
  if (query.status) filters.push(eq(contentEntries.status, query.status))
  else filters.push(ne(contentEntries.status, 'trashed'))
  if (query.term) {
    if (!UUID_RE.test(query.term)) {
      throw new ContentValidationError('term', 'must be a well-formed UUID string')
    }
    if (query.includeDescendants) {
      filters.push(sql`EXISTS (
        SELECT 1 FROM content_entry_terms cet
        WHERE cet.entry_id = ${contentEntries.id}
        AND cet.term_id IN (
          WITH RECURSIVE subtree AS (
            SELECT id, 1 AS lvl FROM content_terms WHERE id = ${query.term}::uuid
            UNION ALL
            SELECT t.id, s.lvl + 1 FROM content_terms t INNER JOIN subtree s ON t.parent_id = s.id
            WHERE s.lvl < 64
          )
          SELECT id FROM subtree
        )
      )`)
    } else {
      filters.push(sql`EXISTS (
        SELECT 1 FROM content_entry_terms cet
        WHERE cet.entry_id = ${contentEntries.id} AND cet.term_id = ${query.term}::uuid
      )`)
    }
  }
  const defined = filters.filter((f): f is SQL => f !== undefined)
  const rows = await db
    .select()
    .from(contentEntries)
    .where(defined.length ? and(...defined) : undefined)
    .orderBy(desc(contentEntries.publishedAt), desc(contentEntries.createdAt))
    .limit(limit)
    .offset(offset)
  return Promise.all(rows.map((row) => entryWithTerms(db, row)))
}

export async function getBySlug(
  db: Querier<ContentSchema>,
  type: string,
  slug: string,
  viewer?: Actor | null,
  _opts: StoreOpts = {},
): Promise<ContentEntry | null> {
  const filters: SQL[] = [eq(contentEntries.type, type), eq(contentEntries.slug, slug)]
  const vis = visibilityPredicate(viewer)
  if (vis) filters.push(vis)
  const [row] = await db
    .select()
    .from(contentEntries)
    .where(and(...filters))
    .limit(1)
  return row ? await entryWithTerms(db, row) : null
}

/**
 * Read a single entry by its stable primary-key id (peer of `getBySlug`; admin edit URLs key on id,
 * not the mutable slug). Applies the IDENTICAL viewer visibility predicate in SQL — an admin
 * (`canEditAny`) loads any status/visibility; a non-permitted viewer gets `null` with no
 * existence oracle (indistinguishable from absent). Read-authz floor enforced in-store, never delegated.
 */
export async function getById(
  db: Querier<ContentSchema>,
  id: string,
  viewer?: Actor | null,
  _opts: StoreOpts = {},
): Promise<ContentEntry | null> {
  const filters: SQL[] = [eq(contentEntries.id, id)]
  const vis = visibilityPredicate(viewer)
  if (vis) filters.push(vis)
  const [row] = await db
    .select()
    .from(contentEntries)
    .where(and(...filters))
    .limit(1)
  return row ? await entryWithTerms(db, row) : null
}

/**
 * Fail-closed guard for the host-injected sanitizer (mirrors comments). A missing/non-function
 * `sanitize` throws BEFORE any DB access — a forgotten sanitizer never silently stores raw HTML.
 */
export function assertSanitize(sanitize: unknown, verb: string): asserts sanitize is Sanitize {
  if (typeof sanitize !== 'function') {
    throw new ContentSanitizationError(`${verb}: sanitize must be a function, got ${typeof sanitize}`)
  }
}

/**
 * Create or update a content entry. The HTML `body` is sanitized on store (XSS hard floor):
 * `sanitize` is REQUIRED and applied to `body` on both the insert and update paths; the SANITIZED
 * value is persisted (no raw HTML retained). `title` is plain text and is NOT sanitized — the host
 * render-escapes it. The sanitizer ENGINE is host-owned (an allowlist sanitizer appropriate to the
 * host runtime — the adopter picks one that actually strips on their runtime); this module owns the
 * contract + enforcement, never the engine.
 */
export async function put(
  db: Querier<ContentSchema>,
  raw: ContentInput,
  actor: Actor,
  sanitize: unknown,
  _opts: StoreOpts = {},
): Promise<ContentEntry> {
  assertSanitize(sanitize, 'put')
  const input = normalizeContentInput(raw)
  const safeBody = sanitize(input.body)
  if (input.termIds !== undefined) {
    await validateTermIdsExist(db, input.termIds)
  }
  if (input.id) {
    const id = input.id
    const existing = await getRow(db, id)
    assertCanModify(actor, 'update', existing.author, existing.id)
    const patch: Partial<typeof contentEntries.$inferInsert> = {
      slug: input.slug,
      type: input.type,
      title: input.title,
      body: safeBody,
      updatedAt: new Date(),
    }
    if (input.visibility !== null) patch.visibility = input.visibility
    const [updated] = await catchConflict(input.type, input.slug, () =>
      db.update(contentEntries).set(patch).where(eq(contentEntries.id, id)).returning(),
    )
    if (input.termIds !== undefined) {
      await replaceEntryTerms(db, id, input.termIds)
    }
    return entryWithTerms(db, updated!)
  }
  const [created] = await catchConflict(input.type, input.slug, () =>
    db
      .insert(contentEntries)
      .values({
        slug: input.slug,
        type: input.type,
        title: input.title,
        body: safeBody,
        author: actor.id,
        status: 'draft',
        visibility: input.visibility ?? 'public',
      })
      .returning(),
  )
  if (input.termIds !== undefined) {
    await replaceEntryTerms(db, created!.id, input.termIds)
  }
  return entryWithTerms(db, created!)
}

export async function setVisibility(
  db: Querier<ContentSchema>,
  id: string,
  visibility: ContentVisibility,
  actor: Actor,
  _opts: StoreOpts = {},
): Promise<EntityRef> {
  assertVisibilityLiteral(visibility)
  const existing = await getRow(db, id)
  assertCanModify(actor, 'update', existing.author, id)
  const now = new Date()
  const [row] = await db
    .update(contentEntries)
    .set({ visibility, updatedAt: now })
    .where(eq(contentEntries.id, id))
    .returning(REF_COLS)
  return row!
}

export async function publish(db: Querier<ContentSchema>, id: string, actor: Actor, _opts: StoreOpts = {}): Promise<EntityRef> {
  await getRow(db, id)
  assertCanPublish(actor, 'publish', id)
  const now = new Date()
  const [row] = await db
    .update(contentEntries)
    .set({ status: 'published', publishedAt: now, updatedAt: now })
    .where(eq(contentEntries.id, id))
    .returning(REF_COLS)
  return row!
}

export async function schedule(db: Querier<ContentSchema>, id: string, at: Date, actor: Actor, _opts: StoreOpts = {}): Promise<EntityRef> {
  if (!(at instanceof Date) || Number.isNaN(at.getTime())) {
    throw new ContentValidationError('at', 'must be a valid Date')
  }
  const now = new Date()
  if (at <= now) {
    throw new ContentValidationError('at', 'must be in the future; use publish() to go live now')
  }
  await getRow(db, id)
  assertCanPublish(actor, 'schedule', id)
  const [row] = await db
    .update(contentEntries)
    .set({ status: 'scheduled', publishedAt: at, updatedAt: now })
    .where(eq(contentEntries.id, id))
    .returning(REF_COLS)
  return row!
}

/** System cron runner — promotes due scheduled entries to published. No actor; authz gated at schedule() time. */
export async function promoteScheduled(
  db: Querier<ContentSchema>,
  now: Date = new Date(),
): Promise<EntityRef[]> {
  if (!(now instanceof Date) || Number.isNaN(now.getTime())) {
    throw new ContentValidationError('now', 'must be a valid Date')
  }
  const rows = await db
    .update(contentEntries)
    .set({ status: 'published', updatedAt: now })
    .where(and(eq(contentEntries.status, 'scheduled'), lte(contentEntries.publishedAt, now)))
    .returning(REF_COLS)
  return rows
}

export async function unpublish(db: Querier<ContentSchema>, id: string, actor: Actor, _opts: StoreOpts = {}): Promise<EntityRef> {
  await getRow(db, id)
  assertCanPublish(actor, 'unpublish', id)
  const now = new Date()
  const [row] = await db
    .update(contentEntries)
    .set({ status: 'draft', publishedAt: null, updatedAt: now })
    .where(eq(contentEntries.id, id))
    .returning(REF_COLS)
  return row!
}

/**
 * Take-offline floor (U1b): trashing a live (published|scheduled) entry is a publication-status transition —
 * also requires `canPublish`. Non-live (draft|trashed) → `canModify` only.
 */
export async function trash(db: Querier<ContentSchema>, id: string, actor: Actor, _opts: StoreOpts = {}): Promise<EntityRef> {
  const existing = await getRow(db, id)
  assertCanModify(actor, 'trash', existing.author, id)
  if (existing.status === 'published' || existing.status === 'scheduled') {
    assertCanPublish(actor, 'trash', id)
  }
  const now = new Date()
  const [row] = await db
    .update(contentEntries)
    .set({ status: 'trashed', updatedAt: now })
    .where(eq(contentEntries.id, id))
    .returning(REF_COLS)
  return row!
}

/**
 * Restores a TRASHED entry to draft (the inverse of `trash`). Prior status is intentionally not
 * persisted (no column) — restored content re-enters review and must be re-published by the admin.
 *
 * Scoped to `status='trashed'` ONLY: on a non-trashed entry this is a no-op that returns the entry's
 * ref unchanged. This keeps restore from being a publish-state bypass — `restore` must NOT be a path
 * for a `canModify`-but-not-`canPublish` actor to move a LIVE (published/scheduled) entry to draft
 * (that liveness transition is gated by `unpublish`/`assertCanPublish`). Idempotent: a second restore
 * sees `draft`, matches no row, and no-ops.
 */
export async function restore(db: Querier<ContentSchema>, id: string, actor: Actor, _opts: StoreOpts = {}): Promise<EntityRef> {
  const existing = await getRow(db, id)
  assertCanModify(actor, 'restore', existing.author, id)
  const now = new Date()
  const [row] = await db
    .update(contentEntries)
    .set({ status: 'draft', updatedAt: now })
    .where(and(eq(contentEntries.id, id), eq(contentEntries.status, 'trashed')))
    .returning(REF_COLS)
  return row ?? { id: existing.id, slug: existing.slug, type: existing.type }
}

/**
 * Take-offline floor (U1b): removing a live (published|scheduled) entry is a publication-status transition —
 * also requires `canPublish`. Non-live (draft|trashed) → `canModify` only.
 */
export async function remove(db: Querier<ContentSchema>, id: string, actor: Actor, _opts: StoreOpts = {}): Promise<EntityRef> {
  const existing = await getRow(db, id)
  assertCanModify(actor, 'remove', existing.author, id)
  if (existing.status === 'published' || existing.status === 'scheduled') {
    assertCanPublish(actor, 'remove', id)
  }
  const [row] = await db.delete(contentEntries).where(eq(contentEntries.id, id)).returning(REF_COLS)
  return row!
}

/** Idempotent base DDL for content_entries — v0.0.1 shape only (visibility/search are separate migrations). */
export const contentEntriesBaseMigrationSql = (table = 'content_entries') => `
CREATE TABLE IF NOT EXISTS ${table} (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug         text NOT NULL,
  type         text NOT NULL,
  title        text NOT NULL,
  body         text NOT NULL DEFAULT '',
  status       text NOT NULL DEFAULT 'draft',
  published_at timestamptz(3),
  author       text NOT NULL,
  created_at   timestamptz(3) NOT NULL DEFAULT NOW(),
  updated_at   timestamptz(3) NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS content_entries_type_slug_uq ON ${table} (type, slug);
CREATE INDEX IF NOT EXISTS content_entries_type_status_pub_idx ON ${table} (type, status, published_at DESC);
`.trim()

/** Additive, idempotent-guarded DDL for adopters migrating existing content_entries tables (spec §7). */
export const contentVisibilityMigrationSql = (table = 'content_entries') => `
ALTER TABLE ${table} ADD COLUMN IF NOT EXISTS visibility text NOT NULL DEFAULT 'public';
CREATE INDEX IF NOT EXISTS content_entries_type_status_vis_pub_idx ON ${table} (type, status, visibility, published_at DESC);
`.trim()