import { asc, eq, inArray, sql } from 'drizzle-orm'
import type { Querier, TransactionalDatabase } from '@platform-modules/db'
import { assertCanManageTaxonomy, assertCanModify, type Actor } from './authz.js'
import { UUID_RE, type TermRef } from './model.js'
import { contentEntries, contentEntryTerms, contentTerms, type ContentSchema } from './schema.js'

export const MAX_TERM_DEPTH = 6

export class TermConflictError extends Error {
  override readonly name = 'TermConflictError'
  constructor(
    readonly taxonomy: string,
    readonly slug: string,
    readonly parentId: string | null,
  ) {
    super(`term slug conflict: taxonomy=${taxonomy} slug=${slug} parentId=${parentId ?? 'root'}`)
  }
}

export class TermCycleError extends Error {
  override readonly name = 'TermCycleError'
  constructor(readonly termId: string, readonly newParentId: string) {
    super(`term move would create cycle: term=${termId} newParent=${newParentId}`)
  }
}

export class TermHasChildrenError extends Error {
  override readonly name = 'TermHasChildrenError'
  constructor(readonly termId: string) {
    super(`term has children: id=${termId}`)
  }
}

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

export class TermValidationError extends Error {
  override readonly name = 'TermValidationError'
  constructor(
    readonly field: string,
    readonly detail: string,
  ) {
    super(`term invalid: ${field} — ${detail}`)
  }
}

/** Idempotent additive DDL for taxonomy tables (spec §3 / §5 step 1). */
export const contentTaxonomyMigrationSql = (): string =>
  `
CREATE TABLE IF NOT EXISTS content_terms (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  taxonomy   text NOT NULL,
  slug       text NOT NULL,
  name       text NOT NULL,
  parent_id  uuid REFERENCES content_terms(id) ON DELETE RESTRICT,
  depth      integer NOT NULL DEFAULT 0,
  created_at timestamptz(3) NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX IF NOT EXISTS content_terms_sibling_slug_uq
  ON content_terms (taxonomy, COALESCE(parent_id, '00000000-0000-0000-0000-000000000000'::uuid), slug);
CREATE INDEX IF NOT EXISTS content_terms_taxonomy_parent_idx ON content_terms (taxonomy, parent_id);
CREATE TABLE IF NOT EXISTS content_entry_terms (
  entry_id uuid NOT NULL REFERENCES content_entries(id) ON DELETE CASCADE,
  term_id  uuid NOT NULL REFERENCES content_terms(id) ON DELETE CASCADE,
  PRIMARY KEY (entry_id, term_id)
);
CREATE INDEX IF NOT EXISTS content_entry_terms_term_idx ON content_entry_terms (term_id);
ALTER TABLE content_revisions ADD COLUMN IF NOT EXISTS term_ids jsonb NOT NULL DEFAULT '[]'::jsonb;
`.trim()

type TermRow = typeof contentTerms.$inferSelect

function toTermRef(row: TermRow): TermRef {
  return {
    id: row.id,
    taxonomy: row.taxonomy,
    slug: row.slug,
    name: row.name,
    parentId: row.parentId ?? null,
    depth: row.depth,
  }
}

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_terms_sibling_slug_uq|duplicate key|unique constraint/i.test(msg)) return true
    cur = cur instanceof Error ? (cur as Error & { cause?: unknown }).cause : undefined
  }
  return false
}

export async function createTerm(
  db: Querier<ContentSchema>,
  input: { taxonomy: string; slug: string; name: string; parentId?: string | null },
  actor: Actor,
): Promise<TermRef> {
  assertCanManageTaxonomy(actor)

  const taxonomy = input.taxonomy.trim()
  const slug = input.slug.trim()
  const name = input.name.trim()
  if (!taxonomy) throw new TermValidationError('taxonomy', 'required')
  if (!slug) throw new TermValidationError('slug', 'required')
  if (!name) throw new TermValidationError('name', 'required')

  const parentId = input.parentId ?? null
  let depth = 0

  if (parentId) {
    const [parent] = await db.select().from(contentTerms).where(eq(contentTerms.id, parentId)).limit(1)
    if (!parent) throw new TermNotFoundError(`parent id=${parentId}`)
    if (parent.taxonomy !== taxonomy) throw new TermValidationError('parentId', 'parent must be same taxonomy')
    depth = parent.depth + 1
    if (depth > MAX_TERM_DEPTH) throw new TermValidationError('depth', `exceeds max depth ${MAX_TERM_DEPTH}`)
  }

  try {
    const [row] = await db
      .insert(contentTerms)
      .values({ taxonomy, slug, name, parentId, depth })
      .returning()
    return toTermRef(row!)
  } catch (e) {
    if (isUniqueViolation(e)) throw new TermConflictError(taxonomy, slug, parentId)
    throw e
  }
}

export async function updateTerm(
  db: Querier<ContentSchema>,
  id: string,
  patch: { name?: string; slug?: string },
  actor: Actor,
): Promise<TermRef> {
  assertCanManageTaxonomy(actor)

  const [existing] = await db.select().from(contentTerms).where(eq(contentTerms.id, id)).limit(1)
  if (!existing) throw new TermNotFoundError(`id=${id}`)

  const name = patch.name !== undefined ? patch.name.trim() : existing.name
  const slug = patch.slug !== undefined ? patch.slug.trim() : existing.slug
  if (!name) throw new TermValidationError('name', 'required')
  if (!slug) throw new TermValidationError('slug', 'required')

  try {
    const [row] = await db
      .update(contentTerms)
      .set({ name, slug })
      .where(eq(contentTerms.id, id))
      .returning()
    return toTermRef(row!)
  } catch (e) {
    if (isUniqueViolation(e)) throw new TermConflictError(existing.taxonomy, slug, existing.parentId ?? null)
    throw e
  }
}

export async function listTerms(db: Querier<ContentSchema>, taxonomy: string): Promise<TermRef[]> {
  const rows = await db
    .select()
    .from(contentTerms)
    .where(eq(contentTerms.taxonomy, taxonomy))
    .orderBy(asc(contentTerms.depth), asc(contentTerms.name))
  return rows.map(toTermRef)
}

async function isInSubtree(db: Querier<ContentSchema>, rootId: string, nodeId: string): Promise<boolean> {
  if (rootId === nodeId) return true
  const result = (await db.execute(sql`
    WITH RECURSIVE subtree AS (
      SELECT id, 1 AS lvl FROM content_terms WHERE id = ${rootId}
      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 1 AS hit FROM subtree WHERE id = ${nodeId} LIMIT 1
  `)) as { rows?: unknown[] }
  return (result.rows?.length ?? 0) > 0
}

export async function moveTerm(
  db: TransactionalDatabase<ContentSchema>,
  id: string,
  newParentId: string | null,
  actor: Actor,
): Promise<TermRef> {
  assertCanManageTaxonomy(actor)

  return db.transaction(async (tx) => {
    const [termTax] = await tx
      .select({ taxonomy: contentTerms.taxonomy })
      .from(contentTerms)
      .where(eq(contentTerms.id, id))
      .limit(1)
    if (!termTax) throw new TermNotFoundError(`id=${id}`)

    const taxonomy = termTax.taxonomy
    await tx.execute(sql`SELECT pg_advisory_xact_lock(hashtext(${'content_terms_move:' + taxonomy}))`)

    const [term] = await tx.select().from(contentTerms).where(eq(contentTerms.id, id)).limit(1)
    if (!term) throw new TermNotFoundError(`id=${id}`)

    if (newParentId === id) throw new TermCycleError(id, newParentId)

    let newDepth = 0
    if (newParentId) {
      const [parent] = await tx.select().from(contentTerms).where(eq(contentTerms.id, newParentId)).limit(1)
      if (!parent) throw new TermNotFoundError(`parent id=${newParentId}`)
      if (parent.taxonomy !== term.taxonomy) {
        throw new TermValidationError('newParentId', 'parent must be same taxonomy')
      }
      if (await isInSubtree(tx, id, newParentId)) {
        throw new TermCycleError(id, newParentId)
      }
      newDepth = parent.depth + 1
    }

    const delta = newDepth - term.depth
    const overCap = (await tx.execute(sql`
      WITH RECURSIVE subtree AS (
        SELECT id, depth, 1 AS lvl FROM content_terms WHERE id = ${id}
        UNION ALL
        SELECT t.id, t.depth, s.lvl + 1 FROM content_terms t
        INNER JOIN subtree s ON t.parent_id = s.id
        WHERE s.lvl < 64
      )
      SELECT 1 AS hit FROM subtree WHERE depth + ${delta} > ${MAX_TERM_DEPTH} LIMIT 1
    `)) as { rows?: unknown[] }
    if ((overCap.rows?.length ?? 0) > 0) {
      throw new TermValidationError('depth', `exceeds max depth ${MAX_TERM_DEPTH}`)
    }

    await tx.update(contentTerms).set({ parentId: newParentId }).where(eq(contentTerms.id, id))
    await tx.execute(sql`
      WITH RECURSIVE subtree AS (
        SELECT id, 1 AS lvl FROM content_terms WHERE id = ${id}
        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
      )
      UPDATE content_terms SET depth = depth + ${delta}
      WHERE id IN (SELECT id FROM subtree)
    `)

    const [moved] = await tx.select().from(contentTerms).where(eq(contentTerms.id, id)).limit(1)
    return toTermRef(moved!)
  })
}

// deleteTerm, assignTerms, termsForEntry — W4–W5

export async function deleteTerm(db: Querier<ContentSchema>, id: string, actor: Actor): Promise<void> {
  assertCanManageTaxonomy(actor)

  const [term] = await db.select().from(contentTerms).where(eq(contentTerms.id, id)).limit(1)
  if (!term) throw new TermNotFoundError(`id=${id}`)

  const [child] = await db.select().from(contentTerms).where(eq(contentTerms.parentId, id)).limit(1)
  if (child) throw new TermHasChildrenError(id)

  await db.delete(contentTerms).where(eq(contentTerms.id, id))
}

async function validateTermIdsExist(db: Querier<ContentSchema>, termIds: string[]): Promise<void> {
  if (termIds.length === 0) return
  for (const id of termIds) {
    if (typeof id !== 'string' || !UUID_RE.test(id)) {
      throw new TermValidationError('termIds', 'must contain only well-formed UUID strings')
    }
  }
  const found = await db
    .select({ id: contentTerms.id })
    .from(contentTerms)
    .where(inArray(contentTerms.id, termIds))
  if (found.length !== termIds.length) {
    const foundSet = new Set(found.map((r) => r.id))
    const missing = termIds.find((id) => !foundSet.has(id))
    throw new TermNotFoundError(`id=${missing}`)
  }
}

async function replaceEntryTerms(db: Querier<ContentSchema>, entryId: string, termIds: string[]): Promise<void> {
  await db.delete(contentEntryTerms).where(eq(contentEntryTerms.entryId, entryId))
  for (const termId of termIds) {
    await db.insert(contentEntryTerms).values({ entryId, termId })
  }
}

export async function termsForEntry(db: Querier<ContentSchema>, entryId: string): Promise<TermRef[]> {
  const rows = await db
    .select({ term: contentTerms })
    .from(contentEntryTerms)
    .innerJoin(contentTerms, eq(contentEntryTerms.termId, contentTerms.id))
    .where(eq(contentEntryTerms.entryId, entryId))
    .orderBy(asc(contentTerms.depth), asc(contentTerms.name))
  return rows.map((r) => toTermRef(r.term))
}

export async function assignTerms(
  db: Querier<ContentSchema>,
  entryId: string,
  termIds: string[],
  actor: Actor,
): Promise<TermRef[]> {
  const [entry] = await db
    .select({ author: contentEntries.author })
    .from(contentEntries)
    .where(eq(contentEntries.id, entryId))
    .limit(1)
  if (!entry) {
    const { ContentNotFoundError } = await import('./store.js')
    throw new ContentNotFoundError(`id=${entryId}`)
  }
  assertCanModify(actor, 'update', entry.author, entryId)
  await validateTermIdsExist(db, termIds)
  await replaceEntryTerms(db, entryId, termIds)
  return termsForEntry(db, entryId)
}

export { validateTermIdsExist, replaceEntryTerms }