import assert from 'node:assert/strict';
import fs from 'node:fs';
import test from 'node:test';

const schemaSql = fs.readFileSync(new URL('./schema.sql', import.meta.url), 'utf8');
const dbSource = fs.readFileSync(new URL('./db.mjs', import.meta.url), 'utf8');

function indexOfTable(source, tableName) {
  return source.indexOf(`CREATE TABLE IF NOT EXISTS ${tableName}`);
}

test('schema.sql creates conversation package tables after their dependencies', () => {
  const users = indexOfTable(schemaSql, 'h5_users');
  const assets = indexOfTable(schemaSql, 'h5_assets');
  const pages = indexOfTable(schemaSql, 'h5_page_records');
  const publications = indexOfTable(schemaSql, 'h5_publish_records');
  const runs = indexOfTable(schemaSql, 'h5_agent_runs');
  const runEvents = indexOfTable(schemaSql, 'h5_agent_run_events');
  const packages = indexOfTable(schemaSql, 'h5_conversation_packages');
  const artifacts = indexOfTable(schemaSql, 'h5_conversation_artifacts');

  for (const [name, index] of Object.entries({
    users,
    assets,
    pages,
    publications,
    runs,
    runEvents,
    packages,
    artifacts,
  })) {
    assert.notEqual(index, -1, `${name} table should exist in schema.sql`);
  }

  assert.ok(packages > users, 'packages table should be after h5_users');
  assert.ok(packages > assets, 'packages table should be after h5_assets');
  assert.ok(artifacts > packages, 'artifacts table should be after packages');
  assert.ok(artifacts > pages, 'artifacts table should be after pages');
  assert.ok(artifacts > publications, 'artifacts table should be after publications');
  assert.ok(artifacts > runs, 'artifacts table should be after runs');
  assert.ok(artifacts > runEvents, 'artifacts table should be after run events');
});

test('runtime schema migration keeps conversation package tables additive', () => {
  assert.match(dbSource, /CREATE TABLE IF NOT EXISTS h5_conversation_packages/);
  assert.match(dbSource, /CREATE TABLE IF NOT EXISTS h5_conversation_artifacts/);
  assert.match(dbSource, /uq_h5_conversation_package_session/);
  assert.match(dbSource, /fk_h5_conversation_artifact_package/);
});