import { sql } from 'drizzle-orm'
import { describe, expect, it } from 'vitest'
import { createPgliteClient } from '../../db/src/postgres/pglite.js'
import {
  contentDefinitionVersions,
  contentEntries,
  contentEntryTerms,
  contentImportJournal,
  contentLifecycleJournal,
  contentMigrationCheckpoints,
  contentMigrationWriteJournal,
  contentSchema,
  contentStatusDefinitions,
  contentTerms,
  contentTombstones,
  contentTypeDefinitions,
} 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,
    parent_id uuid,
    menu_order integer NOT NULL DEFAULT 0,
    template_key text,
    excerpt text NOT NULL DEFAULT '',
    featured_media jsonb,
    comment_status text NOT NULL DEFAULT 'open',
    ping_status text NOT NULL DEFAULT 'open',
    sticky boolean NOT NULL DEFAULT false,
    format text,
    deleted_at timestamptz(3),
    last_edited_by text NOT NULL,
    type_definition_revision integer NOT NULL DEFAULT 1,
    status_definition_revision integer NOT NULL DEFAULT 1,
    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', lastEditedBy: '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', lastEditedBy: 'u1' })
    await expect(
      db.insert(contentEntries).values({ slug: 'a', type: 'post', title: 'T2', author: 'u1', lastEditedBy: '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', lastEditedBy: '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()
  })

  it('includes immutable definitions, journals, tombstones, and migration checkpoints in the final schema contract', () => {
    expect(contentSchema).toMatchObject({
      contentTypeDefinitions,
      contentStatusDefinitions,
      contentDefinitionVersions,
      contentLifecycleJournal,
      contentImportJournal,
      contentTombstones,
      contentMigrationCheckpoints,
      contentMigrationWriteJournal,
    })
  })

})