import { sql } from 'drizzle-orm'
import { beforeEach, describe, expect, it } from 'vitest'
import type { Querier } from '@platform-modules/db'
import { createPgliteClient } from '../../db/src/postgres/pglite.js'
import { contentSchema, type ContentSchema } from './schema.js'
import { setupTaxonomyDb } from './taxonomy.test-helpers.js'
import { createTerm, contentTaxonomyMigrationSql } from './taxonomy.js'
import { ContentAuthzError, type Actor } from './authz.js'
import { ContentSanitizationError, ContentValidationError, type ContentInput } from './model.js'
import { feedFrom, toFeed, type FeedMeta } from './feed.js'
import {
  contentEntriesBaseMigrationSql,
  contentVisibilityMigrationSql,
  getBySlug,
  getById,
  list,
  publish,
  put as putRaw,
  remove,
  schedule,
  setVisibility,
  unpublish,
  promoteScheduled,
  trash,
  restore,
  ContentConflictError,
  ContentNotFoundError,
  type StoreOpts,
} from './store.js'

// Deterministic fake sanitizer (no DOMPurify dep — engine is host-owned; see security-primitives spec §3).
function sanitize(raw: string): string {
  return raw
    .replace(/<script\b[^>]*>[\s\S]*?<\/script>/gi, '')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
}

// Test helper — injects the host sanitizer so existing call sites read against the OLD arity unchanged.
function put(d: Querier<ContentSchema>, raw: ContentInput, actor: Actor, opts?: StoreOpts) {
  return putRaw(d, raw, actor, sanitize, opts)
}

const CREATE_TABLE = 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 author: Actor = { id: 'author-1' }
const editor: Actor = { id: 'editor-1', canEditAny: true, canPublish: true, canManageTaxonomy: true }
const member: Actor = { id: 'member-1', canViewMembers: true }
const plain: Actor = { id: 'plain-1' }

const feedMeta: FeedMeta = {
  title: 'Site',
  siteUrl: 'https://x.test/',
  feedUrl: 'https://x.test/feed.xml',
}

async function db(): Promise<Querier<ContentSchema>> {
  return setupTaxonomyDb(createPgliteClient({ schema: contentSchema }))
}

async function seedPublished(
  d: Querier<ContentSchema>,
  slug: string,
  over: { visibility?: 'public' | 'private' | 'members'; status?: 'draft' | 'published'; author?: string } = {},
) {
  const who: Actor = over.author ? { id: over.author } : author
  const e = await put(
    d,
    { slug, type: 'post', title: slug, body: 'b', visibility: over.visibility },
    who,
  )
  if (over.status === 'draft') return e
  await publish(d, e.id, editor)
  return e
}

describe('content store', () => {
  let d: Querier<ContentSchema>
  beforeEach(async () => {
    d = await db()
  })

  it('put creates a draft owned by the actor; getBySlug reads it back for the author', async () => {
    const created = await put(d, { slug: 'p1', type: 'post', title: 'One', body: 'b' }, author)
    expect(created.status).toBe('draft')
    expect(created.author).toBe('author-1')
    expect(created.visibility).toBe('public')
    const got = await getBySlug(d, 'post', 'p1', author)
    expect(got?.id).toBe(created.id)
  })

  it('publish returns {id,slug,type}, sets status+publishedAt; list filters by status', async () => {
    const e = await put(d, { slug: 'p2', type: 'post', title: 'Two', body: 'b' }, editor)
    const ref = await publish(d, e.id, editor)
    expect(ref).toEqual({ id: e.id, slug: 'p2', type: 'post' })
    const published = await list(d, { status: 'published' }, editor)
    expect(published.map((x) => x.id)).toEqual([e.id])
    expect(published[0]?.publishedAt).toBeInstanceOf(Date)
  })

  it('schedule sets a future publishedAt; rejects a past date with ContentValidationError', async () => {
    const e = await put(d, { slug: 'p3', type: 'post', title: 'Three', body: 'b' }, editor)
    const future = new Date(Date.parse('2099-01-01T00:00:00.000Z'))
    const ref = await schedule(d, e.id, future, editor)
    expect(ref.id).toBe(e.id)
    const got = await getBySlug(d, 'post', 'p3', editor)
    expect(got?.status).toBe('scheduled')
    await expect(schedule(d, e.id, new Date(Date.parse('2000-01-01T00:00:00.000Z')), editor)).rejects.toBeInstanceOf(
      ContentValidationError,
    )
  })

  it('unpublish + remove return identity; remove deletes the row', async () => {
    const e = await put(d, { slug: 'p4', type: 'post', title: 'Four', body: 'b' }, editor)
    await publish(d, e.id, editor)
    const unp = await unpublish(d, e.id, editor)
    expect(unp.id).toBe(e.id)
    const rem = await remove(d, e.id, editor)
    expect(rem).toEqual({ id: e.id, slug: 'p4', type: 'post' })
    expect(await getBySlug(d, 'post', 'p4', editor)).toBeNull()
  })

  it('enforces object-level authz: a non-author non-editor cannot publish or remove', async () => {
    const e = await put(d, { slug: 'p5', type: 'post', title: 'Five', body: 'b' }, author)
    const stranger: Actor = { id: 'stranger' }
    await expect(publish(d, e.id, stranger)).rejects.toBeInstanceOf(ContentAuthzError)
    await expect(remove(d, e.id, stranger)).rejects.toBeInstanceOf(ContentAuthzError)
  })

  it('throws ContentConflictError (typed, not a raw PG error) on a duplicate (type, slug)', async () => {
    await put(d, { slug: 'dup', type: 'post', title: 'A', body: '' }, editor)
    await expect(
      put(d, { slug: 'dup', type: 'post', title: 'B', body: '' }, editor),
    ).rejects.toBeInstanceOf(ContentConflictError)
  })

  it('throws ContentNotFoundError for a missing id on a write', async () => {
    await expect(publish(d, '00000000-0000-4000-8000-000000000000', editor)).rejects.toBeInstanceOf(
      ContentNotFoundError,
    )
  })

  it('promoteScheduled promotes due scheduled rows only; idempotent; excludes future/trashed/already-published', async () => {
    const now = new Date('2026-06-19T12:00:00.000Z')
    const past = new Date('2026-06-19T11:00:00.000Z')
    const future = new Date('2026-06-19T13:00:00.000Z')

    const due = await put(d, { slug: 'due', type: 'post', title: 'Due', body: '' }, editor)
    await d.execute(sql`UPDATE content_entries SET status = 'scheduled', published_at = ${past} WHERE id = ${due.id}`)

    const later = await put(d, { slug: 'later', type: 'post', title: 'Later', body: '' }, editor)
    await d.execute(sql`UPDATE content_entries SET status = 'scheduled', published_at = ${future} WHERE id = ${later.id}`)

    const already = await put(d, { slug: 'already', type: 'post', title: 'Already', body: '' }, editor)
    await publish(d, already.id, editor)

    const trashed = await put(d, { slug: 'trashed', type: 'post', title: 'Trashed', body: '' }, editor)
    await d.execute(sql`UPDATE content_entries SET status = 'trashed', published_at = ${past} WHERE id = ${trashed.id}`)

    const promoted = await promoteScheduled(d, now)
    expect(promoted).toEqual([{ id: due.id, slug: 'due', type: 'post' }])

    const dueRow = await getById(d, due.id, editor)
    expect(dueRow?.status).toBe('published')
    expect(dueRow?.publishedAt?.toISOString()).toBe(past.toISOString())

    expect((await getById(d, later.id, editor))?.status).toBe('scheduled')
    expect((await getById(d, already.id, editor))?.status).toBe('published')
    expect((await getById(d, trashed.id, editor))?.status).toBe('trashed')

    expect(await promoteScheduled(d, now)).toEqual([])
  })

  it('promoteScheduled rejects an invalid now with ContentValidationError', async () => {
    await expect(promoteScheduled(d, 'not-a-date' as never)).rejects.toBeInstanceOf(ContentValidationError)
  })

  it('trash excludes from default list but appears in status=trashed; getById still loads; restore returns draft', async () => {
    const e = await put(d, { slug: 'trash-me', type: 'post', title: 'Trash', body: '' }, author)
    await trash(d, e.id, author)

    const defaultList = await list(d, { limit: 100 }, editor)
    expect(defaultList.map((x) => x.id)).not.toContain(e.id)

    const trashedList = await list(d, { status: 'trashed', limit: 100 }, editor)
    expect(trashedList.map((x) => x.id)).toContain(e.id)

    expect((await getById(d, e.id, editor))?.status).toBe('trashed')

    await restore(d, e.id, author)
    expect((await getById(d, e.id, editor))?.status).toBe('draft')
  })

  it('restore is scoped to status=trashed: cannot move a LIVE published entry to draft (P2 publish-state bypass) and is idempotent', async () => {
    // canEditAny but NOT canPublish — unpublish() denies this actor; restore must too.
    const liveEditor: Actor = { id: 'live-editor', canEditAny: true, canPublish: false }
    const e = await put(d, { slug: 'live-post', type: 'post', title: 'Live', body: '' }, editor)
    await publish(d, e.id, editor)
    expect((await getById(d, e.id, editor))?.status).toBe('published')

    // restore on a non-trashed (published) entry = no-op: returns the ref, status stays published.
    const ref = await restore(d, e.id, liveEditor)
    expect(ref.id).toBe(e.id)
    expect((await getById(d, e.id, editor))?.status).toBe('published')

    // genuine restore from trash → draft; a second restore is a no-op (idempotent).
    await trash(d, e.id, editor)
    expect((await restore(d, e.id, editor)).id).toBe(e.id)
    expect((await getById(d, e.id, editor))?.status).toBe('draft')
    await restore(d, e.id, editor)
    expect((await getById(d, e.id, editor))?.status).toBe('draft')
  })

  it('author can trash/restore own entry; stranger cannot trash another authors entry', async () => {
    const e = await put(d, { slug: 'owned', type: 'post', title: 'Owned', body: '' }, author)
    const stranger: Actor = { id: 'stranger' }

    await trash(d, e.id, author)
    await restore(d, e.id, author)

    await expect(trash(d, e.id, stranger)).rejects.toBeInstanceOf(ContentAuthzError)
  })

  describe('take-offline authz floor (U1b)', () => {
    const editorActor: Actor = { id: 'editor', canEditAny: true, canPublish: false }
    const admin: Actor = { id: 'admin', canEditAny: true, canPublish: true }

    async function expectAuthzDenied(promise: Promise<unknown>, action: 'trash' | 'remove') {
      try {
        await promise
        throw new Error('expected throw')
      } catch (e) {
        expect(e).toBeInstanceOf(ContentAuthzError)
        expect((e as ContentAuthzError).action).toBe(action)
      }
    }

    it('trash rejects editor on published entry', async () => {
      const e = await put(d, { slug: 'pub-trash', type: 'post', title: 'Pub', body: '' }, admin)
      await publish(d, e.id, admin)
      await expectAuthzDenied(trash(d, e.id, editorActor), 'trash')
    })

    it('trash rejects editor on scheduled entry', async () => {
      const e = await put(d, { slug: 'sched-trash', type: 'post', title: 'Sched', body: '' }, admin)
      const future = new Date(Date.now() + 86_400_000)
      await schedule(d, e.id, future, admin)
      await expectAuthzDenied(trash(d, e.id, editorActor), 'trash')
    })

    it('trash resolves for editor on draft entry', async () => {
      const e = await put(d, { slug: 'draft-trash', type: 'post', title: 'Draft', body: '' }, admin)
      await trash(d, e.id, editorActor)
      expect((await getById(d, e.id, admin))?.status).toBe('trashed')
    })

    it('remove rejects editor on published entry', async () => {
      const e = await put(d, { slug: 'pub-remove', type: 'post', title: 'Pub', body: '' }, admin)
      await publish(d, e.id, admin)
      await expectAuthzDenied(remove(d, e.id, editorActor), 'remove')
    })

    it('remove rejects editor on scheduled entry', async () => {
      const e = await put(d, { slug: 'sched-remove', type: 'post', title: 'Sched', body: '' }, admin)
      const future = new Date(Date.now() + 86_400_000)
      await schedule(d, e.id, future, admin)
      await expectAuthzDenied(remove(d, e.id, editorActor), 'remove')
    })

    it('remove resolves for editor on trashed entry', async () => {
      const e = await put(d, { slug: 'trashed-remove', type: 'post', title: 'Trashed', body: '' }, admin)
      await trash(d, e.id, admin)
      await remove(d, e.id, editorActor)
      expect(await getById(d, e.id, admin)).toBeNull()
    })

    it('remove resolves for editor on draft entry', async () => {
      const e = await put(d, { slug: 'draft-remove', type: 'post', title: 'Draft', body: '' }, admin)
      await remove(d, e.id, editorActor)
      expect(await getById(d, e.id, admin)).toBeNull()
    })

    it('trash resolves for admin on published entry', async () => {
      const e = await put(d, { slug: 'admin-trash', type: 'post', title: 'Pub', body: '' }, admin)
      await publish(d, e.id, admin)
      await trash(d, e.id, admin)
      expect((await getById(d, e.id, admin))?.status).toBe('trashed')
    })

    it('remove resolves for admin on published entry', async () => {
      const e = await put(d, { slug: 'admin-remove', type: 'post', title: 'Pub', body: '' }, admin)
      await publish(d, e.id, admin)
      await remove(d, e.id, admin)
      expect(await getById(d, e.id, admin)).toBeNull()
    })
  })

  it('filters list by term id at the SQL layer before pagination', async () => {
    const tagX = await createTerm(d, { taxonomy: 'tag', slug: 'x', name: 'X' }, editor)
    const tagY = await createTerm(d, { taxonomy: 'tag', slug: 'y', name: 'Y' }, editor)
    const a = await put(d, { slug: 't1', type: 'post', title: 'A', body: '', termIds: [tagX.id] }, editor)
    await put(d, { slug: 't2', type: 'post', title: 'B', body: '', termIds: [tagY.id] }, editor)
    const tagged = await list(d, { term: tagX.id, limit: 10 }, editor)
    expect(tagged.map((e) => e.id)).toEqual([a.id])
  })

  it('list includeDescendants returns child-tagged entries', async () => {
    const parent = await createTerm(d, { taxonomy: 'category', slug: 'parent', name: 'Parent' }, editor)
    const child = await createTerm(
      d,
      { taxonomy: 'category', slug: 'child', name: 'Child', parentId: parent.id },
      editor,
    )
    const onParent = await put(d, { slug: 'p1', type: 'post', title: 'P', body: '', termIds: [parent.id] }, editor)
    const onChild = await put(d, { slug: 'c1', type: 'post', title: 'C', body: '', termIds: [child.id] }, editor)
    await put(d, { slug: 'other', type: 'post', title: 'O', body: '' }, editor)

    const listed = await list(d, { term: parent.id, includeDescendants: true, limit: 10 }, editor)
    expect(new Set(listed.map((e) => e.id))).toEqual(new Set([onParent.id, onChild.id]))
  })

  it('list term filter still excludes private entries the visibility predicate hides', async () => {
    const tag = await createTerm(d, { taxonomy: 'tag', slug: 'priv', name: 'Priv' }, editor)
    const pub = await put(
      d,
      { slug: 'pub', type: 'post', title: 'Pub', body: '', termIds: [tag.id], visibility: 'public' },
      author,
    )
    await publish(d, pub.id, editor)
    await put(
      d,
      { slug: 'priv', type: 'post', title: 'Priv', body: '', termIds: [tag.id], visibility: 'private' },
      author,
    )

    const anon = await list(d, { term: tag.id, limit: 10 }, null)
    expect(anon.map((e) => e.id)).toEqual([pub.id])
  })

  it('put with termIds writes join rows; update without termIds leaves terms untouched', async () => {
    const t = await createTerm(d, { taxonomy: 'tag', slug: 'keep', name: 'Keep' }, editor)
    const created = await put(d, { slug: 'terms', type: 'post', title: 'T', body: '', termIds: [t.id] }, editor)
    expect(created.terms.map((x) => x.id)).toEqual([t.id])

    const updated = await put(
      d,
      { id: created.id, slug: 'terms', type: 'post', title: 'T2', body: '' },
      editor,
    )
    expect(updated.terms.map((x) => x.id)).toEqual([t.id])
    expect(updated.title).toBe('T2')
  })

  it('put with termIds: [] clears terms', async () => {
    const t = await createTerm(d, { taxonomy: 'tag', slug: 'clr', name: 'Clr' }, editor)
    const created = await put(d, { slug: 'clr', type: 'post', title: 'T', body: '', termIds: [t.id] }, editor)
    const cleared = await put(
      d,
      { id: created.id, slug: 'clr', type: 'post', title: 'T', body: '', termIds: [] },
      editor,
    )
    expect(cleared.terms).toEqual([])
  })

  it('accepts the scope param as a no-op in the single-install schema', async () => {
    const e = await put(d, { slug: 's1', type: 'post', title: 'S', body: '' }, editor, { scope: 'site-42' })
    const got = await getBySlug(d, 'post', 's1', editor, { scope: 'site-42' })
    expect(got?.id).toBe(e.id)
  })

  it('sanitizes the HTML body on create; the stored body is the sanitized value', async () => {
    const e = await putRaw(
      d,
      { slug: 'xss', type: 'post', title: 'X', body: '<p>ok</p><script>steal()</script>' },
      editor,
      sanitize,
    )
    expect(e.body).toBe('&lt;p&gt;ok&lt;/p&gt;')
    expect(e.body).not.toContain('<script')
    const got = await getBySlug(d, 'post', 'xss', editor)
    expect(got?.body).toBe('&lt;p&gt;ok&lt;/p&gt;')
  })

  it('re-sanitizes the body on update (id path)', async () => {
    const created = await putRaw(d, { slug: 'up', type: 'post', title: 'U', body: 'clean' }, editor, sanitize)
    const updated = await putRaw(
      d,
      { id: created.id, slug: 'up', type: 'post', title: 'U', body: '<script>x</script>after' },
      editor,
      sanitize,
    )
    expect(updated.body).toBe('after')
    expect(updated.body).not.toContain('<script')
  })

  it('fails closed: put without a sanitize function throws ContentSanitizationError and writes nothing', async () => {
    await expect(
      putRaw(d, { slug: 'nope', type: 'post', title: 'N', body: 'x' }, editor, undefined as never),
    ).rejects.toBeInstanceOf(ContentSanitizationError)
    await expect(
      putRaw(d, { slug: 'nope2', type: 'post', title: 'N', body: 'x' }, editor, {} as never),
    ).rejects.toBeInstanceOf(ContentSanitizationError)
    // nothing persisted
    expect(await getBySlug(d, 'post', 'nope', editor)).toBeNull()
  })
})

describe('content visibility read floor (spec §8)', () => {
  let d: Querier<ContentSchema>
  beforeEach(async () => {
    d = await db()
  })

  it('1 anonymous list => only published+public (not drafts/private/members)', async () => {
    await put(d, { slug: 'draft', type: 'post', title: 'draft', body: '' }, author)
    await seedPublished(d, 'pub', { visibility: 'public' })
    await seedPublished(d, 'priv', { visibility: 'private' })
    await seedPublished(d, 'mem', { visibility: 'members' })
    const rows = await list(d)
    expect(rows.map((e) => e.slug)).toEqual(['pub'])
  })

  it('2 author list(viewer=self) => sees own draft + own private', async () => {
    const draft = await put(d, { slug: 'my-draft', type: 'post', title: 'd', body: '' }, author)
    await seedPublished(d, 'my-private', { visibility: 'private' })
    const rows = await list(d, {}, author)
    expect(rows.map((e) => e.slug).sort()).toEqual(['my-draft', 'my-private'].sort())
    expect(rows.find((e) => e.id === draft.id)?.status).toBe('draft')
  })

  it('3 canEditAny viewer => sees everything', async () => {
    await put(d, { slug: 'd1', type: 'post', title: 'd', body: '' }, author)
    await seedPublished(d, 'p1', { visibility: 'private' })
    await seedPublished(d, 'm1', { visibility: 'members' })
    const rows = await list(d, { limit: 100 }, editor)
    expect(rows.map((e) => e.slug).sort()).toEqual(['d1', 'm1', 'p1'].sort())
  })

  it('4 canViewMembers viewer => published members + public, NOT private, NOT others drafts', async () => {
    await put(d, { slug: 'other-draft', type: 'post', title: 'd', body: '' }, { id: 'other' })
    await seedPublished(d, 'pub', { visibility: 'public' })
    await seedPublished(d, 'priv', { visibility: 'private' })
    await seedPublished(d, 'mem', { visibility: 'members' })
    const rows = await list(d, { limit: 100 }, member)
    expect(rows.map((e) => e.slug).sort()).toEqual(['mem', 'pub'].sort())
  })

  it('5 plain viewer (no caps) => public+published + own only', async () => {
    await put(d, { slug: 'mine-draft', type: 'post', title: 'd', body: '' }, plain)
    await seedPublished(d, 'pub', { visibility: 'public' })
    await seedPublished(d, 'priv', { visibility: 'private' })
    await seedPublished(d, 'mem', { visibility: 'members' })
    const rows = await list(d, { limit: 100 }, plain)
    expect(rows.map((e) => e.slug).sort()).toEqual(['mine-draft', 'pub'].sort())
  })

  it('6 getBySlug private as anonymous => null (no oracle)', async () => {
    await seedPublished(d, 'secret', { visibility: 'private' })
    expect(await getBySlug(d, 'post', 'secret')).toBeNull()
  })

  it('7 setVisibility author->members; anonymous getBySlug => null; member viewer => entry', async () => {
    const e = await put(d, { slug: 'flip', type: 'post', title: 'f', body: '' }, author)
    await publish(d, e.id, editor)
    await setVisibility(d, e.id, 'members', author)
    expect(await getBySlug(d, 'post', 'flip')).toBeNull()
    expect((await getBySlug(d, 'post', 'flip', member))?.slug).toBe('flip')
  })

  it('8 setVisibility by non-author non-editor => ContentAuthzError', async () => {
    const e = await put(d, { slug: 'owned', type: 'post', title: 'o', body: '' }, author)
    await expect(setVisibility(d, e.id, 'private', plain)).rejects.toBeInstanceOf(ContentAuthzError)
  })

  it('9 bad literal => ContentValidationError(visibility,...)', async () => {
    await expect(
      put(d, { slug: 'bad', type: 'post', title: 'b', body: '', visibility: 'secret' as never }, author),
    ).rejects.toMatchObject({ field: 'visibility' })
    const e = await put(d, { slug: 'ok', type: 'post', title: 'o', body: '' }, author)
    await expect(setVisibility(d, e.id, 'nope' as never, author)).rejects.toBeInstanceOf(ContentValidationError)
  })

  it('10 backward-compat: existing row (no explicit visibility) reads as public', async () => {
    await d.execute(sql`
      INSERT INTO content_entries (slug, type, title, body, status, author)
      VALUES ('legacy', 'post', 'Legacy', '', 'published', 'legacy-author')
    `)
    const got = await getBySlug(d, 'post', 'legacy')
    expect(got?.visibility).toBe('public')
  })

  it('11 feed floor: anonymous list + toFeed contains ONLY the public entry', async () => {
    await seedPublished(d, 'feed-public', { visibility: 'public' })
    await seedPublished(d, 'feed-private', { visibility: 'private' })
    await seedPublished(d, 'feed-members', { visibility: 'members' })
    const rss = toFeed(await list(d, { limit: 100 }, null), 'rss', feedMeta)
    expect(rss).toContain('feed-public')
    expect(rss).not.toContain('feed-private')
    expect(rss).not.toContain('feed-members')
  })

  it('11b feedFrom (§3.1 structural floor): syndicates ONLY published+public, never private/members', async () => {
    await seedPublished(d, 'ff-public', { visibility: 'public' })
    await seedPublished(d, 'ff-private', { visibility: 'private' })
    await seedPublished(d, 'ff-members', { visibility: 'members' })
    // feedFrom takes NO viewer arg — it cannot be handed a privileged list.
    const rss = await feedFrom(d, { limit: 100 }, 'rss', feedMeta)
    expect(rss).toContain('ff-public')
    expect(rss).not.toContain('ff-private')
    expect(rss).not.toContain('ff-members')
  })
})

describe('getById (read by stable id — inherits the visibility floor)', () => {
  let d: Querier<ContentSchema>
  beforeEach(async () => {
    d = await db()
  })

  it('admin (canEditAny) loads a private entry by id at any status/visibility', async () => {
    const e = await seedPublished(d, 'gid-priv', { visibility: 'private' })
    const got = await getById(d, e.id, editor)
    expect(got?.id).toBe(e.id)
    expect(got?.slug).toBe('gid-priv')
  })

  it('admin loads a draft (unpublished) by id', async () => {
    const e = await seedPublished(d, 'gid-draft', { status: 'draft' })
    expect((await getById(d, e.id, editor))?.id).toBe(e.id)
  })

  it('author loads own private entry by id', async () => {
    const e = await seedPublished(d, 'gid-own', { visibility: 'private' })
    expect((await getById(d, e.id, author))?.id).toBe(e.id)
  })

  it('anonymous on a private entry => null (no existence oracle)', async () => {
    const e = await seedPublished(d, 'gid-secret', { visibility: 'private' })
    expect(await getById(d, e.id, null)).toBeNull()
  })

  it('plain viewer (not author, no caps) on a private entry => null', async () => {
    const e = await seedPublished(d, 'gid-other', { visibility: 'private' })
    expect(await getById(d, e.id, plain)).toBeNull()
  })

  it('unknown id => null', async () => {
    expect(await getById(d, '00000000-0000-0000-0000-000000000000', editor)).toBeNull()
  })
})

describe('contentEntriesBaseMigrationSql (v0.0.1 base shape)', () => {
  it('emits idempotent CREATE TABLE + indexes without visibility or search_vector', () => {
    const ddl = contentEntriesBaseMigrationSql()
    expect(ddl).toContain('CREATE TABLE IF NOT EXISTS content_entries')
    for (const col of ['id', 'slug', 'type', 'title', 'body', 'status', 'published_at', 'author', 'created_at', 'updated_at']) {
      expect(ddl).toContain(col)
    }
    expect(ddl).toContain('content_entries_type_slug_uq')
    expect(ddl).toContain('content_entries_type_status_pub_idx')
    expect(ddl).not.toContain('visibility')
    expect(ddl).not.toContain('search_vector')
    expect(ddl).not.toContain('_vis_pub_idx')
  })
})

describe('contentVisibilityMigrationSql (spec §7 additive DDL)', () => {
  it('defaults the table name and emits an idempotent, additive ALTER + matching index', () => {
    const ddl = contentVisibilityMigrationSql()
    expect(ddl).toContain('ALTER TABLE content_entries ADD COLUMN IF NOT EXISTS visibility text NOT NULL DEFAULT \'public\'')
    expect(ddl).toContain("CREATE INDEX IF NOT EXISTS content_entries_type_status_vis_pub_idx")
    expect(ddl).toContain('(type, status, visibility, published_at DESC)')
  })

  it('honors a custom table name', () => {
    expect(contentVisibilityMigrationSql('cms_posts')).toContain('ALTER TABLE cms_posts ADD COLUMN IF NOT EXISTS visibility')
  })

  it('migrates a legacy table that predates the column: existing rows backfill to public', async () => {
    const handle = createPgliteClient({ schema: contentSchema })
    const legacy = handle as unknown as Querier<ContentSchema>
    // A pre-axis content_entries (no visibility column), with an existing row.
    await legacy.execute(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',
        published_at timestamptz(3), author text NOT NULL,
        created_at timestamptz(3) NOT NULL DEFAULT NOW(), updated_at timestamptz(3) NOT NULL DEFAULT NOW()
      )
    `)
    await legacy.execute(sql`CREATE UNIQUE INDEX content_entries_type_slug_uq ON content_entries (type, slug)`)
    await legacy.execute(sql`
      INSERT INTO content_entries (slug, type, title, body, status, author)
      VALUES ('old', 'post', 'Old', '', 'published', 'a')
    `)
    // Apply the exported migration statement-by-statement, then re-run it — idempotent (IF NOT EXISTS).
    const stmts = contentVisibilityMigrationSql()
      .split(';')
      .map((s) => s.trim())
      .filter(Boolean)
    for (let pass = 0; pass < 2; pass++) {
      for (const s of stmts) await legacy.execute(sql.raw(s))
    }
    for (const s of contentTaxonomyMigrationSql()
      .split(';')
      .map((x) => x.trim())
      .filter((x) => x && !x.includes('content_revisions'))) {
      await legacy.execute(sql.raw(s))
    }
    const got = await getBySlug(legacy, 'post', 'old')
    expect(got?.visibility).toBe('public')
  })
})