import { sql } from 'drizzle-orm'
import { afterAll, beforeAll, beforeEach, describe, expect, it } from 'vitest'
import type { Querier, TransactionalDatabase } from '@platform-modules/db'
import { createPgliteClient } from '../../db/src/postgres/pglite.js'
import { type Actor } from './authz.js'
import { startPg } from './pg-harness.js'
import { contentRevisionsMigrationSql } from './revisions.js'
import { contentSchema, type ContentSchema } from './schema.js'
import {
  TermCycleError,
  TermValidationError,
  contentTaxonomyMigrationSql,
  createTerm,
  listTerms,
  moveTerm,
} from './taxonomy.js'

const CREATE_ENTRIES = sql`
  CREATE TABLE content_entries (
    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',
    visibility text NOT NULL DEFAULT 'public',
    published_at timestamptz(3),
    author text NOT NULL,
    created_at timestamptz(3) NOT NULL DEFAULT NOW(),
    updated_at timestamptz(3) NOT NULL DEFAULT NOW()
  )
`

const CREATE_INDEX = sql`
  CREATE UNIQUE INDEX content_entries_type_slug_uq ON content_entries (type, slug)
`

const manager: Actor = { id: 'mgr-1', canManageTaxonomy: true }

async function setupTaxonomyTables(d: Querier<ContentSchema>): Promise<void> {
  await d.execute(CREATE_ENTRIES)
  await d.execute(CREATE_INDEX)
  for (const s of contentRevisionsMigrationSql()
    .split(';')
    .map((x) => x.trim())
    .filter(Boolean)) {
    await d.execute(sql.raw(s))
  }
  for (const s of contentTaxonomyMigrationSql()
    .split(';')
    .map((x) => x.trim())
    .filter(Boolean)) {
    await d.execute(sql.raw(s))
  }
}

async function setupDb(
  handle: ReturnType<typeof createPgliteClient>,
): Promise<TransactionalDatabase<ContentSchema>> {
  const d = handle as unknown as TransactionalDatabase<ContentSchema>
  await setupTaxonomyTables(d)
  return d
}

async function hasTaxonomyCycle(db: Querier<ContentSchema>, taxonomy: string): Promise<boolean> {
  const result = (await db.execute(sql`
    SELECT t.id
    FROM content_terms t
    WHERE t.taxonomy = ${taxonomy}
      AND EXISTS (
        WITH RECURSIVE up AS (
          SELECT parent_id AS id, 1 AS lvl
          FROM content_terms
          WHERE id = t.id AND parent_id IS NOT NULL
          UNION ALL
          SELECT ct.parent_id, u.lvl + 1
          FROM content_terms ct
          INNER JOIN up u ON ct.id = u.id
          WHERE ct.parent_id IS NOT NULL AND u.lvl < 64
        )
        SELECT 1 FROM up WHERE id = t.id
      )
    LIMIT 1
  `)) as { rows?: unknown[] }
  return (result.rows?.length ?? 0) > 0
}

describe('moveTerm (pglite)', () => {
  let d: TransactionalDatabase<ContentSchema>
  beforeEach(async () => {
    d = await setupDb(createPgliteClient({ schema: contentSchema }))
  })

  it('rejects moveTerm(A, C) on chain A→B→C with TermCycleError', async () => {
    const a = await createTerm(d, { taxonomy: 'category', slug: 'a', name: 'A' }, manager)
    const b = await createTerm(d, { taxonomy: 'category', slug: 'b', name: 'B', parentId: a.id }, manager)
    const c = await createTerm(d, { taxonomy: 'category', slug: 'c', name: 'C', parentId: b.id }, manager)

    await expect(moveTerm(d, a.id, c.id, manager)).rejects.toBeInstanceOf(TermCycleError)
  })

  it('moveTerm(C, null) makes C root at depth 0 and shifts its descendants', async () => {
    const a = await createTerm(d, { taxonomy: 'category', slug: 'a', name: 'A' }, manager)
    const b = await createTerm(d, { taxonomy: 'category', slug: 'b', name: 'B', parentId: a.id }, manager)
    const c = await createTerm(d, { taxonomy: 'category', slug: 'c', name: 'C', parentId: b.id }, manager)
    const child = await createTerm(d, { taxonomy: 'category', slug: 'd', name: 'D', parentId: c.id }, manager)

    const moved = await moveTerm(d, c.id, null, manager)
    expect(moved.parentId).toBeNull()
    expect(moved.depth).toBe(0)

    const listed = await listTerms(d, 'category')
    const byId = Object.fromEntries(listed.map((t) => [t.id, t]))
    expect(byId[c.id]!.depth).toBe(0)
    expect(byId[child.id]!.depth).toBe(1)
    expect(byId[b.id]!.depth).toBe(1)
    expect(byId[a.id]!.depth).toBe(0)
  })

  it('rejects move that would push a descendant past the depth cap (state unchanged)', async () => {
    let parentId: string | null = null
    const ids: string[] = []
    for (let i = 0; i <= 6; i++) {
      const t = await createTerm(
        d,
        { taxonomy: 'category', slug: `n${i}`, name: `N${i}`, parentId: parentId ?? undefined },
        manager,
      )
      ids.push(t.id)
      parentId = t.id
    }
    const chainRoot = ids[0]!
    const chainLeaf = ids[6]!

    const t0 = await createTerm(d, { taxonomy: 'category', slug: 't0', name: 'T0' }, manager)
    const t1 = await createTerm(d, { taxonomy: 'category', slug: 't1', name: 'T1', parentId: t0.id }, manager)
    const t2 = await createTerm(d, { taxonomy: 'category', slug: 't2', name: 'T2', parentId: t1.id }, manager)

    await expect(moveTerm(d, chainRoot, t2.id, manager)).rejects.toBeInstanceOf(TermValidationError)

    const listed = await listTerms(d, 'category')
    const byId = Object.fromEntries(listed.map((t) => [t.id, t]))
    expect(byId[chainRoot]!.parentId).toBeNull()
    expect(byId[chainRoot]!.depth).toBe(0)
    expect(byId[chainLeaf]!.depth).toBe(6)
  })

  it('move to a different parent updates depth of the whole subtree', async () => {
    const root = await createTerm(d, { taxonomy: 'category', slug: 'root', name: 'Root' }, manager)
    const branch = await createTerm(d, { taxonomy: 'category', slug: 'branch', name: 'Branch', parentId: root.id }, manager)
    const leaf = await createTerm(
      d,
      { taxonomy: 'category', slug: 'leaf', name: 'Leaf', parentId: branch.id },
      manager,
    )
    const other = await createTerm(d, { taxonomy: 'category', slug: 'other', name: 'Other' }, manager)

    await moveTerm(d, branch.id, other.id, manager)

    const listed = await listTerms(d, 'category')
    const byId = Object.fromEntries(listed.map((t) => [t.id, t]))
    expect(byId[branch.id]!.parentId).toBe(other.id)
    expect(byId[branch.id]!.depth).toBe(1)
    expect(byId[leaf.id]!.depth).toBe(2)
  })
})

describe('moveTerm concurrency (real PG)', () => {
  let db: TransactionalDatabase<ContentSchema>
  let dbB: TransactionalDatabase<ContentSchema>
  let stop: () => Promise<void>

  beforeAll(async () => {
    const pg = await startPg()
    db = pg.db
    dbB = pg.dbB
    stop = pg.stop
    await setupTaxonomyTables(db)
  }, 45_000)

  afterAll(async () => {
    await stop?.()
  }, 15_000)

  it('two crossing moveTerm calls: exactly one TermCycleError, final tree has no cycle', async () => {
    const a = await createTerm(db, { taxonomy: 'category', slug: 'a', name: 'A' }, manager)
    const b = await createTerm(db, { taxonomy: 'category', slug: 'b', name: 'B', parentId: a.id }, manager)
    const c = await createTerm(db, { taxonomy: 'category', slug: 'c', name: 'C', parentId: a.id }, manager)

    const [r1, r2] = await Promise.allSettled([
      moveTerm(db, b.id, c.id, manager),
      moveTerm(dbB, c.id, b.id, manager),
    ])

    const successes = [r1, r2].filter((r) => r.status === 'fulfilled')
    const failures = [r1, r2].filter((r) => r.status === 'rejected')
    expect(successes).toHaveLength(1)
    expect(failures).toHaveLength(1)
    expect((failures[0] as PromiseRejectedResult).reason).toBeInstanceOf(TermCycleError)

    expect(await hasTaxonomyCycle(db, 'category')).toBe(false)
  }, 45_000)
})