// Nordbank — SQLite database via sql.js (WASM build) running in the browser.
//
// IMPORTANT: this runs entirely client-side. The DB blob is persisted to
// localStorage. The user can inspect it. In production this MUST move to a
// real backend (e.g. Cloudflare D1 + Workers). The SCHEMA, queries, and
// hashing parameters here are deliberately shaped so the migration is a
// mechanical port.

const SQL_JS_URL  = 'https://cdn.jsdelivr.net/npm/sql.js@1.10.3/dist/sql-wasm.js';
const SQL_WASM_URL = 'https://cdn.jsdelivr.net/npm/sql.js@1.10.3/dist/sql-wasm.wasm';
const DB_STORAGE_KEY = 'nordbank.db.v1';

const SCHEMA = `
  CREATE TABLE IF NOT EXISTS users (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    email           TEXT    UNIQUE NOT NULL COLLATE NOCASE,
    password_hash   TEXT    NOT NULL,
    password_salt   TEXT    NOT NULL,
    iterations      INTEGER NOT NULL DEFAULT 100000,
    created_at      INTEGER NOT NULL
  );

  CREATE TABLE IF NOT EXISTS applications (
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id              INTEGER,
    confirmation_number  TEXT    NOT NULL,
    country              TEXT,
    currency             TEXT,
    amount               REAL,
    term_years           INTEGER,
    monthly              REAL,
    effective_rate       REAL,
    insurance_selected   INTEGER,
    insurance_premium    REAL,
    insurance_quote_id   TEXT,
    insurance_product_id TEXT,
    data_json            TEXT    NOT NULL,
    signed_at            INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
  );

  CREATE INDEX IF NOT EXISTS ix_applications_user ON applications(user_id);
  CREATE INDEX IF NOT EXISTS ix_applications_email
    ON applications(json_extract(data_json, '$.personal.email'));
`;

// ---------------------------------------------------------------------------
// Base64 <-> bytes
// ---------------------------------------------------------------------------
function bytesToBase64(bytes) {
  let bin = '';
  const chunk = 0x8000;
  for (let i = 0; i < bytes.length; i += chunk) {
    bin += String.fromCharCode.apply(null, bytes.subarray(i, i + chunk));
  }
  return btoa(bin);
}
function base64ToBytes(b64) {
  const bin = atob(b64);
  const out = new Uint8Array(bin.length);
  for (let i = 0; i < bin.length; i++) out[i] = bin.charCodeAt(i);
  return out;
}

// ---------------------------------------------------------------------------
// sql.js loader
// ---------------------------------------------------------------------------
let _sqlPromise = null;
let _db = null;
let _dbReady = null;

function loadSqlJs() {
  if (_sqlPromise) return _sqlPromise;
  _sqlPromise = new Promise((resolve, reject) => {
    if (window.initSqlJs) return resolve(window.initSqlJs);
    const s = document.createElement('script');
    s.src = SQL_JS_URL;
    s.async = true;
    s.onload = () => resolve(window.initSqlJs);
    s.onerror = () => reject(new Error('Failed to load sql.js'));
    document.head.appendChild(s);
  });
  return _sqlPromise;
}

async function getDb() {
  if (_db) return _db;
  if (_dbReady) return _dbReady;
  _dbReady = (async () => {
    const initSqlJs = await loadSqlJs();
    const SQL = await initSqlJs({ locateFile: () => SQL_WASM_URL });
    const stored = localStorage.getItem(DB_STORAGE_KEY);
    if (stored) {
      try {
        _db = new SQL.Database(base64ToBytes(stored));
      } catch (e) {
        console.warn('[nordbank.db] failed to rehydrate stored DB; starting fresh', e);
        _db = new SQL.Database();
      }
    } else {
      _db = new SQL.Database();
    }
    _db.run(SCHEMA);
    return _db;
  })();
  return _dbReady;
}

function persistDb() {
  if (!_db) return;
  try {
    const blob = _db.export();
    localStorage.setItem(DB_STORAGE_KEY, bytesToBase64(blob));
  } catch (e) {
    console.warn('[nordbank.db] persist failed', e);
  }
}

// Hook variant: returns the DB or null while loading.
function useDb() {
  const [db, setDb] = React.useState(_db);
  React.useEffect(() => {
    if (db) return;
    let cancelled = false;
    getDb().then(d => { if (!cancelled) setDb(d); });
    return () => { cancelled = true; };
  }, []);
  return db;
}

// ---------------------------------------------------------------------------
// Application-level queries
// ---------------------------------------------------------------------------

// Convert an exec() result to an array of plain row objects.
function rowsOf(result) {
  if (!result || result.length === 0) return [];
  const { columns, values } = result[0];
  return values.map(row => {
    const obj = {};
    for (let i = 0; i < columns.length; i++) obj[columns[i]] = row[i];
    return obj;
  });
}

// Save a signed loan application. Returns the new application id.
async function dbSaveApplication(app, quote) {
  const db = await getDb();
  const stmt = db.prepare(`
    INSERT INTO applications (
      user_id, confirmation_number, country, currency,
      amount, term_years, monthly, effective_rate,
      insurance_selected, insurance_premium,
      insurance_quote_id, insurance_product_id,
      data_json, signed_at
    ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)
  `);
  stmt.run([
    null,
    app.confirmationNumber,
    app.country,
    app.currency,
    app.amount,
    app.termYears,
    quote ? quote.monthly : null,
    quote ? quote.effectiveRate : null,
    app.insuranceSelected ? 1 : 0,
    app.insurancePremium || 0,
    app.insuranceQuoteId,
    app.insuranceProductId,
    JSON.stringify(app),
    Date.now(),
  ]);
  stmt.free();
  const id = rowsOf(db.exec('SELECT last_insert_rowid() AS id'))[0].id;
  persistDb();
  return id;
}

// Create a user. Returns the new user id.
async function dbCreateUser(email, passwordHash, salt, iterations) {
  const db = await getDb();
  const stmt = db.prepare(`
    INSERT INTO users (email, password_hash, password_salt, iterations, created_at)
    VALUES (?, ?, ?, ?, ?)
  `);
  stmt.run([email.trim().toLowerCase(), passwordHash, salt, iterations, Date.now()]);
  stmt.free();
  const id = rowsOf(db.exec('SELECT last_insert_rowid() AS id'))[0].id;
  persistDb();
  return id;
}

// Look up a user by email (returns row or null).
async function dbFindUserByEmail(email) {
  const db = await getDb();
  const stmt = db.prepare('SELECT * FROM users WHERE email = ?');
  stmt.bind([email.trim().toLowerCase()]);
  let row = null;
  if (stmt.step()) row = stmt.getAsObject();
  stmt.free();
  return row;
}

// Link an application to a user.
async function dbLinkApplicationToUser(applicationId, userId) {
  const db = await getDb();
  const stmt = db.prepare('UPDATE applications SET user_id = ? WHERE id = ?');
  stmt.run([userId, applicationId]);
  stmt.free();
  persistDb();
}

// Also retroactively claim any unlinked applications whose stored email matches.
async function dbClaimApplicationsForUser(userId, email) {
  const db = await getDb();
  const stmt = db.prepare(`
    UPDATE applications
       SET user_id = ?
     WHERE user_id IS NULL
       AND lower(json_extract(data_json, '$.personal.email')) = lower(?)
  `);
  stmt.run([userId, email]);
  stmt.free();
  persistDb();
}

// List a user's applications, newest first.
async function dbListApplicationsForUser(userId) {
  const db = await getDb();
  const stmt = db.prepare(`
    SELECT id, confirmation_number, country, currency, amount, term_years,
           monthly, effective_rate, insurance_selected, insurance_premium,
           data_json, signed_at
      FROM applications
     WHERE user_id = ?
     ORDER BY signed_at DESC
  `);
  stmt.bind([userId]);
  const rows = [];
  while (stmt.step()) rows.push(stmt.getAsObject());
  stmt.free();
  // Parse data_json so consumers don't have to.
  return rows.map(r => ({ ...r, data: safeParse(r.data_json) }));
}

function safeParse(s) {
  try { return JSON.parse(s); } catch (e) { return null; }
}

// Dev helper: reset the DB.
function dbReset() {
  localStorage.removeItem(DB_STORAGE_KEY);
  _db = null;
  _dbReady = null;
}

Object.assign(window, {
  // low-level
  loadSqlJs, getDb, persistDb, useDb, dbReset,
  // queries
  dbSaveApplication, dbCreateUser, dbFindUserByEmail,
  dbLinkApplicationToUser, dbClaimApplicationsForUser,
  dbListApplicationsForUser,
});