import { describe, it, expect } from 'vitest';
import { PGlite } from '@electric-sql/pglite';
import { drizzle } from 'drizzle-orm/pglite';
import { sql } from 'drizzle-orm';
import type { Querier } from '@platform-modules/db';
import { applyContentSchema } from './install.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',
    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 listContentIndexes(db: Querier): Promise<string[]> {
  const result = await db.execute(sql`
    SELECT indexname FROM pg_indexes WHERE tablename = 'content_entries' ORDER BY indexname
  `);
  const rows = Array.isArray(result) ? result : (result as { rows?: { indexname: string }[] }).rows ?? [];
  return rows.map((r) => r.indexname);
}

describe('applyContentSchema', () => {
  it('is idempotent — visibility + search_vector column and indexes apply without error', async () => {
    const client = new PGlite();
    const db = drizzle(client) as unknown as Querier;
    await db.execute(CREATE_ENTRIES);
    await db.execute(CREATE_INDEX);

    await applyContentSchema(db);
    await applyContentSchema(db);

    const vis = await db.execute(sql`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'content_entries' AND column_name = 'visibility'
    `);
    const visRows = Array.isArray(vis) ? vis : (vis as { rows?: { column_name: string }[] }).rows ?? [];
    expect(visRows[0]?.column_name).toBe('visibility');

    const sv = await db.execute(sql`
      SELECT column_name FROM information_schema.columns
      WHERE table_name = 'content_entries' AND column_name = 'search_vector'
    `);
    const svRows = Array.isArray(sv) ? sv : (sv as { rows?: { column_name: string }[] }).rows ?? [];
    expect(svRows[0]?.column_name).toBe('search_vector');

    const indexes = await listContentIndexes(db);
    expect(indexes).toContain('content_entries_type_status_vis_pub_idx');
    expect(indexes).toContain('content_entries_search_idx');
  });
});