import { describe, it, expect, beforeAll, afterAll } 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 { contentEntriesBaseMigrationSql } from '@platform-modules/content';
import { applyCommentsSchema, applyFullSchema } from './install.js';
import { startPg } from './pg-harness.js';

function sqlText(query: unknown): string {
  if (typeof query === 'object' && query !== null && 'queryChunks' in query) {
    return (query as { queryChunks: { value?: unknown[] }[] }).queryChunks
      .flatMap((chunk) => chunk.value ?? [])
      .map(String)
      .join('');
  }
  return String(query);
}

const EXPECTED_INDEXES = [
  'comments_target_keyset_idx',
  'comments_parent_id_idx',
  'comments_status_idx',
  'comments_moderation_idx',
];

async function listCommentsIndexes(db: Querier): Promise<string[]> {
  const result = await db.execute(sql`
    SELECT indexname FROM pg_indexes WHERE tablename = 'comments' ORDER BY indexname
  `);
  const rows = Array.isArray(result) ? result : (result as { rows?: { indexname: string }[] }).rows ?? [];
  return rows.map((r) => r.indexname);
}

describe('applyFullSchema (unit order)', () => {
  it('executes composed DDL in dependency order with auth-status ALTER last', async () => {
    const executed: string[] = [];
    const fakeDb = {
      execute: async (query: unknown) => {
        executed.push(sqlText(query));
        return [];
      },
    } as unknown as Querier;

    await applyFullSchema(fakeDb, 'postgres');
    const joined = executed.join('\n');

    const idx = (needle: string) => joined.indexOf(needle);
    expect(idx('CREATE TABLE IF NOT EXISTS languages')).toBeGreaterThanOrEqual(0);
    expect(idx('languages')).toBeLessThan(idx('translation_value'));
    expect(idx('translation_value')).toBeLessThan(idx('CREATE TABLE IF NOT EXISTS content_entries'));
    expect(idx('CREATE TABLE IF NOT EXISTS content_entries')).toBeGreaterThanOrEqual(0);
    expect(idx('CREATE TABLE IF NOT EXISTS content_entries')).toBeLessThan(idx('visibility'));
    expect(idx('visibility')).toBeLessThan(idx('search_vector'));
    expect(idx('search_vector')).toBeLessThan(idx('content_revisions'));
    expect(idx('content_revisions')).toBeLessThan(idx('content_terms'));
    expect(idx('content_terms')).toBeLessThan(idx('comments'));
    expect(idx('comments')).toBeLessThan(idx('form_submissions'));
    expect(idx('form_submissions')).toBeLessThan(idx('notifications'));
    expect(idx('notifications')).toBeLessThan(idx('field_values'));
    expect(idx('field_values')).toBeLessThan(idx('menus'));
    expect(idx('menus')).toBeLessThan(idx('media_assets'));
    expect(idx('media_assets')).toBeLessThan(idx('audit_log'));
    expect(idx('audit_log')).toBeLessThan(idx('auth_users'));
    expect(joined.lastIndexOf('auth_users')).toBe(idx('auth_users'));
  });
});

const CMS_TABLES = [
  'languages',
  'translation_value',
  'content_entries',
  'content_terms',
  'content_entry_terms',
  'content_revisions',
  'comments',
  'form_submissions',
  'notifications',
  'field_values',
  'field_groups',
  'menus',
  'menu_items',
  'media_assets',
  'audit_log',
] as const;

async function seedAuthUsers(db: Querier): Promise<void> {
  await db.execute(sql`
    CREATE TABLE IF NOT EXISTS auth_users (
      id text PRIMARY KEY,
      email text NOT NULL UNIQUE,
      password_hash text,
      session_version integer NOT NULL DEFAULT 0,
      roles text NOT NULL DEFAULT '["user"]',
      created_at timestamptz NOT NULL DEFAULT now()
    )
  `);
}

async function tableExists(db: Querier, name: string): Promise<boolean> {
  const result = await db.execute(sql`SELECT to_regclass(${`public.${name}`}) AS reg`);
  const rows = Array.isArray(result) ? result : (result as { rows?: { reg: string | null }[] }).rows ?? [];
  return rows[0]?.reg != null;
}

async function columnExists(db: Querier, table: string, column: string): Promise<boolean> {
  const result = await db.execute(sql`
    SELECT column_name FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = ${table} AND column_name = ${column}
  `);
  const rows = Array.isArray(result) ? result : (result as { rows?: { column_name: string }[] }).rows ?? [];
  return rows.length > 0;
}

async function dropCmsTables(db: Querier): Promise<void> {
  await db.execute(sql`
    DROP TABLE IF EXISTS
      translation_value,
      languages,
      content_entry_terms,
      content_revisions,
      content_terms,
      content_entries,
      comments,
      form_submissions,
      notifications,
      field_values,
      field_groups,
      menu_items,
      menus,
      media_assets,
      audit_log
    CASCADE
  `);
}

describe('applyFullSchema (real Postgres)', () => {
  let db: Querier;
  let stop: (() => Promise<void>) | undefined;

  beforeAll(async () => {
    const pg = await startPg();
    db = pg.db;
    stop = pg.stop;
    await seedAuthUsers(db);
  }, 120_000);

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

  it('provisions all CMS tables on an empty branch', async () => {
    await applyFullSchema(db, 'postgres');
    for (const table of CMS_TABLES) {
      expect(await tableExists(db, table), `missing table: ${table}`).toBe(true);
    }
    expect(await columnExists(db, 'content_entries', 'visibility')).toBe(true);
    expect(await columnExists(db, 'content_entries', 'search_vector')).toBe(true);
    expect(await columnExists(db, 'auth_users', 'status')).toBe(true);
  });

  it('is idempotent — second run is a no-op (no throw)', async () => {
    await expect(applyFullSchema(db, 'postgres')).resolves.toBeUndefined();
  });

  it('heals a v0.0.1-shaped DB additively and preserves rows', async () => {
    await dropCmsTables(db);
    for (const stmt of contentEntriesBaseMigrationSql()
      .split(';')
      .map((s) => s.trim())
      .filter(Boolean)) {
      await db.execute(sql.raw(stmt));
    }
    await db.execute(sql`
      INSERT INTO content_entries (slug, type, title, body, status, author)
      VALUES ('legacy', 'post', 'Legacy', 'body', 'draft', 'author-1')
    `);

    await applyFullSchema(db, 'postgres');

    const row = await db.execute(sql`SELECT slug, title FROM content_entries WHERE slug = 'legacy'`);
    const rows = Array.isArray(row) ? row : (row as { rows?: { slug: string; title: string }[] }).rows ?? [];
    expect(rows[0]?.slug).toBe('legacy');
    expect(rows[0]?.title).toBe('Legacy');
    expect(await columnExists(db, 'content_entries', 'visibility')).toBe(true);
    expect(await tableExists(db, 'audit_log')).toBe(true);
  });
});

describe('applyCommentsSchema', () => {
  it('is idempotent — second run leaves table + 4 indexes, no error', async () => {
    const client = new PGlite();
    const db = drizzle(client) as unknown as Querier;
    await applyCommentsSchema(db);
    await applyCommentsSchema(db);

    const table = await db.execute(sql`SELECT to_regclass('public.comments') AS reg`);
    const tableRows = Array.isArray(table) ? table : (table as { rows?: { reg: string }[] }).rows ?? [];
    expect(tableRows[0]?.reg).toBe('comments');
    const indexes = await listCommentsIndexes(db);
    for (const name of EXPECTED_INDEXES) {
      expect(indexes).toContain(name);
    }
    expect(indexes.filter((n) => n !== 'comments_pkey')).toHaveLength(4);
  });
});