import { sql } from 'drizzle-orm'
import { afterAll, beforeAll, describe, expect, it } from 'vitest'
import type { Querier } from '@platform-modules/db'
import { startPg } from './pg-harness.js'
import { type ContentSchema } from './schema.js'
import { contentTaxonomyMigrationSql } from './taxonomy.js'
import { contentTaxonomyBackfillSql } from './taxonomy.migrate.js'

const LEGACY_SCHEMA = 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,
    category jsonb NOT NULL DEFAULT '[]'::jsonb,
    tag jsonb NOT NULL DEFAULT '[]'::jsonb,
    created_at timestamptz(3) NOT NULL DEFAULT NOW(),
    updated_at timestamptz(3) NOT NULL DEFAULT NOW()
  );
  CREATE UNIQUE INDEX content_entries_type_slug_uq ON content_entries (type, slug);
  CREATE TABLE content_revisions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    entry_id uuid NOT NULL REFERENCES content_entries(id) ON DELETE CASCADE,
    seq bigserial NOT NULL,
    title text NOT NULL,
    body text NOT NULL,
    slug text NOT NULL,
    type text NOT NULL,
    category jsonb NOT NULL DEFAULT '[]'::jsonb,
    tag jsonb NOT NULL DEFAULT '[]'::jsonb,
    editor text NOT NULL,
    created_at timestamptz(3) NOT NULL DEFAULT NOW()
  );
`

async function runStatements(db: Querier<ContentSchema>, script: string) {
  for (const s of script
    .split(';')
    .map((x) => x.trim())
    .filter(Boolean)) {
    await db.execute(sql.raw(s))
  }
}

describe('contentTaxonomyBackfillSql (real PG migration proof)', () => {
  let db: Querier<ContentSchema>
  let stop: () => Promise<void>
  let entryId: string
  let categoryTermId: string
  let legacyCategoryFilterIds: string[]

  beforeAll(async () => {
    const pg = await startPg()
    db = pg.db
    stop = pg.stop
    await db.execute(LEGACY_SCHEMA)

    const inserted = (await db.execute(sql`
      INSERT INTO content_entries (slug, type, title, body, status, author, category, tag)
      VALUES ('post-a', 'post', 'A', 'body', 'published', 'u1', '["news"]'::jsonb, '["alpha"]'::jsonb)
      RETURNING id
    `)) as { rows: { id: string }[] }
    entryId = inserted.rows[0]!.id

    await db.execute(sql`
      INSERT INTO content_revisions (entry_id, title, body, slug, type, category, tag, editor)
      VALUES (${entryId}, 'Old', 'old body', 'post-a', 'post', '["news"]'::jsonb, '["legacy-only"]'::jsonb, 'ed')
    `)

    const legacy = (await db.execute(sql`
      SELECT id FROM content_entries WHERE category @> '["news"]'::jsonb
    `)) as { rows: { id: string }[] }
    legacyCategoryFilterIds = legacy.rows.map((r) => r.id)

    await runStatements(db, contentTaxonomyMigrationSql())

    const backfill = contentTaxonomyBackfillSql()
    const dropMarker = 'ALTER TABLE content_entries DROP COLUMN'
    const preDrop = backfill.slice(0, backfill.indexOf(dropMarker))
    await runStatements(db, preDrop)

    const tagCheck = (await db.execute(sql`
      SELECT tag FROM content_revisions WHERE entry_id = ${entryId} LIMIT 1
    `)) as { rows: { tag: string[] }[] }
    expect(tagCheck.rows[0]?.tag).toEqual(['legacy-only'])

    const idsBeforeDrop = (await db.execute(sql`
      SELECT term_ids FROM content_revisions WHERE entry_id = ${entryId} LIMIT 1
    `)) as { rows: { term_ids: string[] }[] }
    expect(idsBeforeDrop.rows[0]!.term_ids.length).toBe(2)

    await runStatements(db, backfill.slice(backfill.indexOf(dropMarker)))

    const cat = (await db.execute(sql`SELECT id FROM content_terms WHERE taxonomy='category' AND slug='news'`)) as {
      rows: { id: string }[]
    }
    categoryTermId = cat.rows[0]!.id
  }, 60_000)

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

  it('(a) materializes terms and join rows', async () => {
    const terms = (await db.execute(sql`SELECT taxonomy, slug FROM content_terms ORDER BY taxonomy, slug`)) as {
      rows: { taxonomy: string; slug: string }[]
    }
    expect(terms.rows.map((r) => `${r.taxonomy}:${r.slug}`).sort()).toEqual(
      ['category:news', 'tag:alpha', 'tag:legacy-only'].sort(),
    )

    const joins = (await db.execute(sql`
      SELECT t.taxonomy, t.slug FROM content_entry_terms cet
      JOIN content_terms t ON t.id = cet.term_id
      WHERE cet.entry_id = ${entryId}
      ORDER BY t.taxonomy, t.slug
    `)) as { rows: { taxonomy: string; slug: string }[] }
    expect(joins.rows.map((r) => `${r.taxonomy}:${r.slug}`).sort()).toEqual(['category:news', 'tag:alpha'].sort())
  })

  it('(b) revision term_ids resolves revision-only tag value', async () => {
    const rev = (await db.execute(sql`
      SELECT term_ids FROM content_revisions WHERE entry_id = ${entryId} LIMIT 1
    `)) as { rows: { term_ids: string[] }[] }
    const ids = rev.rows[0]!.term_ids
    expect(ids.length).toBe(2)

    const legacyOnly = (await db.execute(sql`
      SELECT id FROM content_terms WHERE taxonomy = 'tag' AND slug = 'legacy-only'
    `)) as { rows: { id: string }[] }
    const news = (await db.execute(sql`
      SELECT id FROM content_terms WHERE taxonomy = 'category' AND slug = 'news'
    `)) as { rows: { id: string }[] }
    expect(ids).toEqual(expect.arrayContaining([legacyOnly.rows[0]!.id, news.rows[0]!.id]))
  })

  it('(c) EXISTS term filter parity vs prior jsonb @> filter', async () => {
    const migrated = (await db.execute(sql`
      SELECT e.id FROM content_entries e
      WHERE EXISTS (
        SELECT 1 FROM content_entry_terms cet
        WHERE cet.entry_id = e.id AND cet.term_id = ${categoryTermId}::uuid
      )
      ORDER BY e.id
    `)) as { rows: { id: string }[] }
    expect(migrated.rows.map((row) => row.id).sort()).toEqual(legacyCategoryFilterIds.sort())
  })

  it('(d) category/tag columns are dropped', async () => {
    const cols = (await db.execute(sql`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'content_entries' AND column_name IN ('category', 'tag')
    `)) as { rows: unknown[] }
    expect(cols.rows).toHaveLength(0)

    const revCols = (await db.execute(sql`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'content_revisions' AND column_name IN ('category', 'tag')
    `)) as { rows: unknown[] }
    expect(revCols.rows).toHaveLength(0)
  })
})