// Server only. A persistent local SQLite volume is required; never put it in public/.
import { DatabaseSync } from "node:sqlite";
import { mkdirSync } from "node:fs";
import path from "node:path";
import { createHash, randomBytes } from "node:crypto";
import type { CheckoutStateStore, CheckoutRecord, StorePaymentRecord, AgentEventStore } from "@relyon/store-kit";

export function openStorage(directory = process.env.STORE_DATA_DIR ?? path.resolve(".store")) {
  mkdirSync(directory, { recursive: true, mode: 0o700 });
  const db = new DatabaseSync(path.join(directory, "test-store.sqlite"));
  db.exec(`PRAGMA journal_mode=WAL; PRAGMA synchronous=FULL; PRAGMA busy_timeout=10000;
    CREATE TABLE IF NOT EXISTS records (kind TEXT NOT NULL, id TEXT NOT NULL, state TEXT, value TEXT NOT NULL, PRIMARY KEY(kind,id));
    CREATE TABLE IF NOT EXISTS claims (id TEXT PRIMARY KEY, owner TEXT, expires INTEGER, done INTEGER NOT NULL DEFAULT 0);
    CREATE TABLE IF NOT EXISTS orders (payment TEXT PRIMARY KEY, reference TEXT UNIQUE NOT NULL, value TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS events (seq INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT UNIQUE NOT NULL, value TEXT NOT NULL);`);
  const get = (kind: string, id: string): any => {
    const row = db.prepare("SELECT value FROM records WHERE kind=? AND id=?").get(kind, id);
    return row ? JSON.parse(String(row.value)) : null;
  };
  const put = (kind: string, id: string, value: any) => {
    db.prepare("INSERT INTO records VALUES (?,?,?,?) ON CONFLICT(kind,id) DO UPDATE SET state=excluded.state,value=excluded.value")
      .run(kind, id, value.state ?? null, JSON.stringify(value));
  };
  const state: CheckoutStateStore = {
    get: (protocol, id) => get("kit", `${protocol}:${id}`),
    put: record => put("kit", `${record.protocol}:${record.sessionId}`, record),
    byAgentCheckoutId(id) {
      const row = db.prepare("SELECT value FROM records WHERE kind='kit' AND json_extract(value,'$.agentCheckoutId')=? ORDER BY json_extract(value,'$.updatedAt') DESC LIMIT 1").get(id);
      return row ? JSON.parse(String(row.value)) as CheckoutRecord : null;
    },
    claimOrder(id, options) {
      const o = options ?? { claimId: "", now: Date.now(), leaseMs: 120000 };
      return db.prepare(`INSERT INTO claims VALUES (?,?,?,0) ON CONFLICT(id) DO UPDATE SET owner=excluded.owner,expires=excluded.expires
        WHERE claims.done=0 AND claims.expires<=?`).run(id, o.claimId, o.now + o.leaseMs, o.now).changes === 1;
    },
    completeOrder(id, owner) { db.prepare("UPDATE claims SET done=1,expires=NULL WHERE id=? AND owner=?").run(id, owner ?? ""); },
    releaseOrder(id, owner) { db.prepare("DELETE FROM claims WHERE id=? AND owner=? AND done=0").run(id, owner ?? ""); },
    orderClaimStatus(id, now) {
      const row = db.prepare("SELECT done,expires FROM claims WHERE id=?").get(id);
      return row?.done ? "created" : row && Number(row.expires) > now ? "creating" : null;
    },
    getStorePayment: id => get("payment", id) as StorePaymentRecord | null,
    putStorePayment(id, record) {
      // A stale writer can never erase durable evidence of a charge.
      db.prepare(`INSERT INTO records VALUES ('payment',?,?,?) ON CONFLICT(kind,id) DO UPDATE SET state=excluded.state,value=excluded.value
        WHERE CASE excluded.state WHEN 'order_created' THEN 4 WHEN 'order_pending' THEN 3 WHEN 'declined' THEN 2 ELSE 1 END
        >= CASE records.state WHEN 'order_created' THEN 4 WHEN 'order_pending' THEN 3 WHEN 'declined' THEN 2 ELSE 1 END`)
        .run(id, record.state, JSON.stringify(record));
    },
    claimHandoffToken(id, options) {
      return db.prepare("INSERT INTO claims VALUES (?,NULL,?,1) ON CONFLICT(id) DO UPDATE SET expires=excluded.expires WHERE claims.expires<=?")
        .run(`handoff:${id}`, options.expiresAt, options.now).changes === 1;
    },
    setHandoffToken(protocol, id, token, options) {
      const key = `${protocol}:${id}`;
      db.exec("BEGIN IMMEDIATE");
      try {
        const current = get("kit", key);
        const canonical = (value: any): string => JSON.stringify(value, (_key, v) =>
          v && typeof v === "object" && !Array.isArray(v) ? Object.fromEntries(Object.keys(v).sort().map(k => [k, v[k]])) : v);
        const matches = current && (current.handoffToken ?? null) === options.expectedToken
          && canonical(current.inputs) === canonical(options.expectedInputs);
        if (matches) put("kit", key, { ...current, handoffToken: token, handoffTokenAt: options.now, updatedAt: options.now });
        db.exec("COMMIT");
        return !!matches;
      } catch (error) { db.exec("ROLLBACK"); throw error; }
    },
  };
  const events: AgentEventStore = {
    append(event) { db.prepare("INSERT OR IGNORE INTO events(id,value) VALUES (?,?)").run(event.id, JSON.stringify(event)); },
    peek(limit) { return db.prepare("SELECT value FROM events ORDER BY seq LIMIT ?").all(limit).map(r => JSON.parse(String(r.value))); },
    remove(ids) { const del = db.prepare("DELETE FROM events WHERE id=?"); for (const id of ids) del.run(id); },
    size() { return Number(db.prepare("SELECT count(*) AS n FROM events").get()!.n); },
  };
  const checkouts = {
    get: (id: string) => get("checkout", id),
    async insert(id: string, value: any) {
      db.prepare("INSERT INTO records VALUES ('checkout',?,?,?)").run(id, value.state, JSON.stringify(value));
    },
    async update(id: string, expected: string, value: any) {
      db.exec("BEGIN IMMEDIATE");
      try {
        const current = get("checkout", id);
        if (current?.state !== expected) { db.exec("COMMIT"); return { updated: false, row: current }; }
        // Parallel replies must never erase evidence that the payment succeeded,
        // nor close a paid-but-unsaved checkout as though it charged nothing.
        const next = current.paid && value.state === "closed" ? current : {
          ...value, ...(current.paid ? { paid: true, paymentId: current.paymentId } : {}),
        };
        const changed = db.prepare("UPDATE records SET state=?,value=? WHERE kind='checkout' AND id=? AND state=?")
          .run(next.state, JSON.stringify(next), id, expected).changes === 1;
        const row = get("checkout", id);
        db.exec("COMMIT");
        return { updated: changed, row };
      } catch (error) { db.exec("ROLLBACK"); throw error; }
    },
    async confirming() { return db.prepare("SELECT id FROM records WHERE kind='checkout' AND state='confirming'").all().map(r => String(r.id)); },
  };
  const findOrder = (payment: string): any => {
    const row = db.prepare("SELECT value FROM orders WHERE payment=?").get(payment);
    return row ? JSON.parse(String(row.value)) : null;
  };
  // Store an independent cart-token key, stable over restarts and shared by workers.
  db.prepare("INSERT OR IGNORE INTO records VALUES ('config','cart-secret',NULL,?)").run(JSON.stringify(randomBytes(32).toString("hex")));
  return { db, get, put, state, events, checkouts, findOrder, cartSecret: get("config", "cart-secret") as string,
    createOrder(cart: any, payment: any) {
      const id = `order_${createHash("sha256").update(payment.id).digest("hex").slice(0, 24)}`;
      const order = { id, paymentId: payment.id, total: cart.totals.total };
      db.prepare("INSERT INTO orders VALUES (?,?,?) ON CONFLICT(payment) DO NOTHING")
        .run(payment.id, typeof payment.order_reference === "string" ? payment.order_reference : id, JSON.stringify(order));
      return findOrder(payment.id);
    },
  };
}