import { mkdtempSync, readFileSync, rmSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join, resolve } from 'node:path';
import { fileURLToPath } from 'node:url';
import { DatabaseSync } from 'node:sqlite';

const root = resolve(fileURLToPath(new URL('..', import.meta.url)));
const source = readFileSync(join(root, 'app/core/identity/identity-store.mjs'), 'utf8');
const match = source.match(/db\.exec\(`([\s\S]*?)`\);/);
if (!match) throw new Error('Identity-Schema-Block fehlt');

const tempRoot = mkdtempSync(join(tmpdir(), 'vendoo-identity-140-'));
const db = new DatabaseSync(join(tempRoot, 'identity.db'));
try {
  db.exec('PRAGMA foreign_keys = ON');
  db.exec(match[1]);

  for (const statement of [
    'ALTER TABLE users ADD COLUMN failed_login_count INTEGER NOT NULL DEFAULT 0',
    'ALTER TABLE users ADD COLUMN locked_until TEXT',
    'ALTER TABLE users ADD COLUMN password_changed_at TEXT',
    'ALTER TABLE sessions ADD COLUMN last_seen_at TEXT',
    'ALTER TABLE sessions ADD COLUMN revoked_at TEXT',
  ]) {
    try { db.exec(statement); } catch { /* additive migration may already exist */ }
  }

  const insertUser = db.prepare('INSERT INTO users(email,name,role,password_hash) VALUES(?,?,?,?)');
  insertUser.run('admin@example.invalid', 'Admin', 'admin', 'salt:hash');
  insertUser.run('editor@example.invalid', 'Editor', 'editor', 'salt:hash');
  const adminId = db.prepare("SELECT id FROM users WHERE email='admin@example.invalid'").get().id;
  const editorId = db.prepare("SELECT id FROM users WHERE email='editor@example.invalid'").get().id;
  const insertSession = db.prepare("INSERT INTO sessions(user_id,token_hash,expires_at,last_seen_at) VALUES(?,?,datetime('now','+1 day'),datetime('now'))");
  insertSession.run(adminId, 'admin-token');
  insertSession.run(editorId, 'editor-token');
  const editorSession = db.prepare('SELECT id FROM sessions WHERE user_id=?').get(editorId).id;

  let result = db.prepare("UPDATE sessions SET revoked_at=datetime('now') WHERE id=? AND user_id=? AND revoked_at IS NULL").run(editorSession, adminId);
  if (result.changes !== 0) throw new Error('Fremde Sitzung konnte durch falschen Eigentümer widerrufen werden');
  result = db.prepare("UPDATE sessions SET revoked_at=datetime('now') WHERE id=? AND user_id=? AND revoked_at IS NULL").run(editorSession, editorId);
  if (result.changes !== 1) throw new Error('Eigene Sitzung konnte nicht widerrufen werden');

  db.exec('CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT)');
  db.prepare('INSERT OR REPLACE INTO settings(key,value) VALUES(?,?)').run('default_language', 'de');
  if (db.prepare("SELECT value FROM settings WHERE key='default_language'").get().value !== 'de') {
    throw new Error('Settings-Persistenz fehlgeschlagen');
  }

  const tables = new Set(db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all().map((row) => row.name));
  const required = ['users', 'sessions', 'auth_tokens', 'audit_log', 'login_history', 'settings'];
  const missing = required.filter((name) => !tables.has(name));
  if (missing.length) throw new Error(`Fehlende Identity-/Settings-Tabellen: ${missing.join(', ')}`);

  console.log(JSON.stringify({
    ok: true,
    tables: required.sort(),
    foreign_session_blocked: true,
    own_session_revoked: true,
  }));
} finally {
  db.close();
  rmSync(tempRoot, { recursive: true, force: true });
}