import { sql } from 'drizzle-orm'
import { describe, expect, it } from 'vitest'
import { createPgliteClient } from '../../db/src/postgres/pglite.js'
import { contentEntries, contentEntryTerms, contentSchema, contentTerms } from './schema.js'
import { contentRevisionsMigrationSql } from './revisions.js'
import { contentTaxonomyMigrationSql } 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)
`

async function applyTaxonomyDdl(db: ReturnType<typeof createPgliteClient>) {
  const revStmts = contentRevisionsMigrationSql()
    .split(';')
    .map((s) => s.trim())
    .filter(Boolean)
  for (const s of revStmts) await db.execute(sql.raw(s))
  const stmts = contentTaxonomyMigrationSql()
    .split(';')
    .map((s) => s.trim())
    .filter(Boolean)
  for (const s of stmts) await db.execute(sql.raw(s))
}

describe('content schema', () => {
  it('round-trips an insert through the exported drizzle table (defaults applied)', async () => {
    const db = createPgliteClient({ schema: contentSchema })
    await db.execute(CREATE_ENTRIES)
    await db.execute(CREATE_INDEX)
    const [row] = await db
      .insert(contentEntries)
      .values({ slug: 'a', type: 'post', title: 'T', author: 'u1' })
      .returning()
    expect(row?.status).toBe('draft')
    expect(row?.visibility).toBe('public')
    expect(row?.body).toBe('')
    expect(row?.publishedAt).toBeNull()
  })

  it('enforces unique (type, slug)', async () => {
    const db = createPgliteClient({ schema: contentSchema })
    await db.execute(CREATE_ENTRIES)
    await db.execute(CREATE_INDEX)
    await db.insert(contentEntries).values({ slug: 'a', type: 'post', title: 'T', author: 'u1' })
    await expect(
      db.insert(contentEntries).values({ slug: 'a', type: 'post', title: 'T2', author: 'u1' }),
    ).rejects.toThrow()
  })

  it('applies taxonomy DDL and round-trips term + join inserts', async () => {
    const db = createPgliteClient({ schema: contentSchema })
    await db.execute(CREATE_ENTRIES)
    await db.execute(CREATE_INDEX)
    await applyTaxonomyDdl(db)

    const [entry] = await db.insert(contentEntries).values({ slug: 'a', type: 'post', title: 'T', author: 'u1' }).returning()
    const [term] = await db
      .insert(contentTerms)
      .values({ taxonomy: 'tag', slug: 'news', name: 'News', depth: 0 })
      .returning()
    await db.insert(contentEntryTerms).values({ entryId: entry!.id, termId: term!.id })
    const joins = await db.select().from(contentEntryTerms)
    expect(joins).toHaveLength(1)
  })

  it('contentTaxonomyMigrationSql is idempotent (run twice)', async () => {
    const db = createPgliteClient({ schema: contentSchema })
    await db.execute(CREATE_ENTRIES)
    await db.execute(CREATE_INDEX)
    await applyTaxonomyDdl(db)
    await expect(applyTaxonomyDdl(db)).resolves.toBeUndefined()
  })
})