import test from 'node:test';
import assert from 'node:assert/strict';
import { chmodSync, copyFileSync, mkdirSync, mkdtempSync, readFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { createHash } from 'node:crypto';
import { DatabaseSync } from 'node:sqlite';
import { Store, schemaDigest } from '../src/store.mjs';
const AT = '2026-10-15T00:00:00.000Z';
function source3e(t) {
  const root = mkdtempSync(join(tmpdir(), 'spec-migrate-'));
  t.after(() => rmSync(root, { recursive: true, force: true }));
  mkdirSync(join(root, 'bus'), { mode: 0o700 });
  const path = join(root, 'bus/bus.sqlite'), db = new DatabaseSync(path);
  const ddl = readFileSync(new URL('./fixtures/schema-3e.sql', import.meta.url), 'utf8');
  assert.equal(createHash('sha256').update(ddl).digest('hex'), '21e6c091f14f818396f4d7836f381cc1389d45b2680ecaa1a00646b652c45050');
  db.exec(ddl);
  const run = (sql, ...args) => db.prepare(sql).run(...args);
  run('INSERT INTO meta VALUES (?,?)', 'schema_version', '3e');
  run('INSERT INTO meta VALUES (?,?)', 'schema_digest', schemaDigest(db));
  run('INSERT INTO meta VALUES (?,?)', 'comment_cursor_epoch', AT);
  run('INSERT INTO events(id,at,business,kind,body) VALUES(?,?,?,?,?)', 'event', AT, 'demo', 'action.allowed', '{}');
  run('INSERT INTO role_claims(at,business,role,op,holder_run,harness,by) VALUES(?,?,?,?,?,?,?)', AT, 'demo', 'coder', 'claim', 'run', 'pi', 'run');
  run('INSERT INTO decisions(id,at,business,raised_by_role,raised_by_run,class,action,route_to,question,options,recommendation,blocking) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)',
    'decision', AT, 'demo', 'coder', 'run', 'gated', 'deploy', 'human', '?', '[{"key":"yes","text":"Yes"},{"key":"no","text":"No"}]', 'yes', 0);
  run('INSERT INTO decision_events(decision,at,op,by,choice) VALUES(?,?,?,?,?)', 'decision', AT, 'resolved', 'jason', 'yes');
  run('INSERT INTO messages(id,at,business,from_role,from_run,to_role,class,body) VALUES(?,?,?,?,?,?,?,?)', 'message', AT, 'demo', 'human', 'human', 'assistant', 'INFO', 'kept');
  run('INSERT INTO deliveries(message,at,op) VALUES(?,?,?)', 'message', AT, 'routed');
  run('INSERT INTO task_snapshots(at,business,task_ref,updated,digest,fields,source,role,run) VALUES(?,?,?,?,?,?,?,?,?)', AT, 'demo', 'vikunja:1/1', AT, 'a'.repeat(64), '{"bucket":1,"done":false}', 'self', 'coder', 'run');
  run('INSERT INTO task_comment_cursors(business,task_id,last_comment) VALUES(?,?,?)', 'demo', 1, 8);
  run('INSERT INTO projects(id,at,business,name,repo,created_by) VALUES(?,?,?,?,?,?)', 'project', AT, 'demo', 'Work', 'stack', 'jason');
  run('INSERT INTO threads(id,at,business,project,instance,owner,name) VALUES(?,?,?,?,?,?,?)', 'thread', AT, 'demo', 'project', 'assistant', 'jason', 'One');
  run('INSERT INTO thread_claims(at,business,thread,op,holder_run,harness,by) VALUES(?,?,?,?,?,?,?)', AT, 'demo', 'thread', 'claim', 'assistant-run', 'pi', 'assistant-run');
  run('INSERT INTO message_threads VALUES(?,?)', 'message', 'thread');
  run('INSERT INTO thread_opens(at,thread,by) VALUES(?,?,?)', AT, 'thread', 'jason');
  db.close(); chmodSync(path, 0o600);
  const copy = join(root, 'untouched-3e.sqlite'); copyFileSync(path, copy);
  return { root, path, copy };
}
function snapshot(path, columns) {
  const db = new DatabaseSync(path, { readOnly: true });
  try {
    columns ??= Object.fromEntries(db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all()
      .map(({ name }) => [name, db.prepare(`PRAGMA table_info(${name})`).all().map(c => c.name)]));
    const rows = Object.fromEntries(Object.entries(columns).map(([table, keys]) => [table,
      db.prepare(`SELECT ${keys.join(',')} FROM ${table} ORDER BY rowid`).all()
        .filter(r => table !== 'meta' || !['schema_version', 'schema_digest'].includes(r.key))]));
    return { columns, rows };
  } finally { db.close(); }
}
// Row 264a: the 3k→3l step fills sessions and session_defaults with INSERT ... SELECT, which gives
// each a sqlite_sequence row (3k-3l.sql:50-52), at seq 1 for the one thread here. The compare after
// the step leaves those two rows out and checks them exactly, so any other new row still fails.
// thread_claims and thread_opens stay in the compare: the step carries their seq.
const SEQ_3L = ['sessions', 'session_defaults'];
function migrated3l(rows) {
  const left = rows.sqlite_sequence.filter(r => SEQ_3L.includes(r.name));
  assert.deepEqual(left.map(r => ({ ...r })), [{ name: 'sessions', seq: 1 }, { name: 'session_defaults', seq: 1 }]);
  return { ...rows, sqlite_sequence: rows.sqlite_sequence.filter(r => !SEQ_3L.includes(r.name)) };
}
test('3e copy migrates through 3f to 3g preserving every old column/row and adding null spec citations', t => {
  const { root, path, copy } = source3e(t), before = snapshot(path), untouched = readFileSync(copy);
  assert.throws(() => new Store(root), /schema-mismatch/);
  const store = new Store(root, { migrateSchema: true });
  try {
    assert.equal(store.get("SELECT value FROM meta WHERE key='schema_version'").value, '3v');
    assert.deepEqual({ ...store.get('SELECT spec_path,spec_commit FROM decisions') }, { spec_path: null, spec_commit: null });
    assert.equal(store.get('PRAGMA quick_check').quick_check, 'ok');
    assert.deepEqual(store.all('PRAGMA foreign_key_check'), []);
  } finally { store.close(); }
  assert.deepEqual(migrated3l(snapshot(path, before.columns).rows), before.rows);
  assert.deepEqual(readFileSync(copy), untouched);
  assert.deepEqual(snapshot(copy).rows, before.rows);
  new Store(root).close();
});