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';
// Row 124 (#1618, REQ-LIMIT-4): the 3g→3h step on a copy of a 3g store built here from the pinned 3g
// schema (4ebef2284's schema.sql) and fixture rows. No live store is read or copied.
const AT = '2026-10-16T00:00:00.000Z';
function source3g(t) {
  const root = mkdtempSync(join(tmpdir(), 'usage-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-3g.sql', import.meta.url), 'utf8');
  assert.equal(createHash('sha256').update(ddl).digest('hex'), 'a79b3dbe6f4dfba10871c01ad8513ea290071eb8d8af2b87be6db52dbcf23428');
  db.exec(ddl);
  const run = (sql, ...args) => db.prepare(sql).run(...args);
  run('INSERT INTO meta VALUES (?,?)', 'schema_version', '3g');
  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,spec_path,spec_commit) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
    'decision', AT, 'demo', 'coder', 'run', 'gated', 'deploy', 'human', '?', '[{"key":"yes","text":"Yes"},{"key":"no","text":"No"}]', 'yes', 0, 'docs/spec.md', 'c'.repeat(40));
  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');
  run('INSERT INTO instance_names(at,business,instance,name,by) VALUES(?,?,?,?,?)', AT, 'demo', 'coder', 'Builder', 'jason');
  db.close(); chmodSync(path, 0o600);
  const copy = join(root, 'untouched-3g.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 ${table === 'sqlite_sequence' ? 'name' : '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.
// The step writes the thread_claims and thread_opens rows again, after instance_names', so the
// snapshot reads sqlite_sequence by name, as account-migration.test.mjs does (row 126).
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: 'session_defaults', seq: 1 }, { name: 'sessions', seq: 1 }]);
  return { ...rows, sqlite_sequence: rows.sqlite_sequence.filter(r => !SEQ_3L.includes(r.name)) };
}
test('REQ-LIMIT-4 3g copy migrates through 3h, 3i, 3j and 3k to 3l preserving every old column, row and sequence; new usage table is empty', t => {
  const { root, path, copy } = source3g(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 to_name FROM messages') }, { to_name: null });
    assert.equal(store.get('SELECT count(*) AS n FROM usage_records').n, 0);
    assert.equal(store.get('SELECT name FROM instance_names').name, 'Builder');
    assert.deepEqual({ ...store.get('SELECT spec_path,spec_commit FROM decisions') }, { spec_path: 'docs/spec.md', spec_commit: 'c'.repeat(40) });
    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();
});