import { mkdirSync } from "node:fs";
import { dirname, resolve } from "node:path";
import Database from "better-sqlite3";
import type { SignedAgentEvent } from "@pons-agent/protocol";

export class AgentState {
  readonly database: Database.Database;

  constructor(path: string) {
    const resolved = resolve(path);
    mkdirSync(dirname(resolved), { recursive: true });
    this.database = new Database(resolved);
    this.database.pragma("journal_mode = WAL");
    this.database.exec(`
      CREATE TABLE IF NOT EXISTS state (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );
      CREATE TABLE IF NOT EXISTS launches (
        token TEXT PRIMARY KEY,
        wallet_address TEXT NOT NULL DEFAULT '',
        pool TEXT NOT NULL,
        tx_hash TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL,
        symbol TEXT NOT NULL,
        created_at TEXT NOT NULL
      );
      CREATE TABLE IF NOT EXISTS ledger (
        id TEXT PRIMARY KEY,
        wallet_address TEXT NOT NULL DEFAULT '',
        bucket TEXT NOT NULL CHECK (bucket IN ('creator-fees', 'buyback', 'treasury', 'gas')),
        direction TEXT NOT NULL CHECK (direction IN ('credit', 'debit')),
        asset TEXT NOT NULL,
        amount TEXT NOT NULL,
        tx_hash TEXT,
        created_at TEXT NOT NULL
      );
      CREATE TABLE IF NOT EXISTS launched_topics (
        wallet_address TEXT NOT NULL DEFAULT '',
        topic_key TEXT NOT NULL,
        token TEXT NOT NULL,
        tx_hash TEXT NOT NULL,
        created_at TEXT NOT NULL,
        PRIMARY KEY(wallet_address, topic_key)
      );
      CREATE TABLE IF NOT EXISTS runtime_locks (
        name TEXT PRIMARY KEY,
        owner TEXT NOT NULL,
        acquired_at TEXT NOT NULL,
        expires_at TEXT NOT NULL
      );
      CREATE TABLE IF NOT EXISTS cycles (
        id TEXT PRIMARY KEY,
        started_at TEXT NOT NULL,
        completed_at TEXT,
        status TEXT NOT NULL CHECK (status IN ('running', 'completed', 'failed')),
        decision_count INTEGER,
        error TEXT
      );
      CREATE TABLE IF NOT EXISTS public_event_outbox (
        event_id TEXT PRIMARY KEY,
        sequence INTEGER NOT NULL UNIQUE,
        event_json TEXT NOT NULL,
        status TEXT NOT NULL CHECK (status IN ('pending', 'sent')),
        attempts INTEGER NOT NULL DEFAULT 0,
        last_error TEXT,
        created_at TEXT NOT NULL,
        sent_at TEXT
      );
      CREATE TABLE IF NOT EXISTS executions (
        id TEXT PRIMARY KEY,
        wallet_address TEXT NOT NULL DEFAULT '',
        kind TEXT NOT NULL CHECK (kind IN ('launch', 'claim', 'buyback')),
        status TEXT NOT NULL CHECK (status IN ('planned', 'submitted', 'confirmed', 'failed', 'uncertain')),
        target TEXT,
        amount TEXT,
        tx_hash TEXT,
        result_json TEXT,
        error TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );
      CREATE TABLE IF NOT EXISTS wallet_vault (
        id TEXT PRIMARY KEY,
        address TEXT NOT NULL UNIQUE,
        encrypted_private_key TEXT NOT NULL,
        active INTEGER NOT NULL DEFAULT 0 CHECK (active IN (0, 1)),
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL
      );
      CREATE TABLE IF NOT EXISTS operator_commands (
        id TEXT PRIMARY KEY,
        wallet_address TEXT NOT NULL DEFAULT '',
        kind TEXT NOT NULL CHECK (kind IN ('launch', 'claim')),
        status TEXT NOT NULL CHECK (status IN ('queued', 'running', 'completed', 'failed')),
        request_json TEXT NOT NULL,
        result_json TEXT,
        error TEXT,
        created_at TEXT NOT NULL,
        started_at TEXT,
        completed_at TEXT
      );
      CREATE INDEX IF NOT EXISTS cycles_started_at_idx ON cycles(started_at DESC);
      CREATE INDEX IF NOT EXISTS public_event_outbox_status_idx ON public_event_outbox(status, sequence);
      CREATE INDEX IF NOT EXISTS executions_status_idx ON executions(status, created_at DESC);
      CREATE INDEX IF NOT EXISTS operator_commands_status_idx ON operator_commands(status, created_at ASC);
    `);
    this.migrateWalletScopes();
  }

  private hasColumn(table: string, column: string) {
    const columns = this.database.prepare(`PRAGMA table_info(${table})`).all() as Array<{ name: string }>;
    return columns.some((item) => item.name === column);
  }

  private migrateWalletScopes() {
    const migrate = this.database.transaction(() => {
      for (const table of ["launches", "ledger", "executions", "operator_commands"]) {
        if (!this.hasColumn(table, "wallet_address")) {
          this.database.exec(`ALTER TABLE ${table} ADD COLUMN wallet_address TEXT NOT NULL DEFAULT ''`);
        }
      }
      if (!this.hasColumn("launched_topics", "wallet_address")) {
        this.database.exec(`
          CREATE TABLE launched_topics_scoped (
            wallet_address TEXT NOT NULL DEFAULT '',
            topic_key TEXT NOT NULL,
            token TEXT NOT NULL,
            tx_hash TEXT NOT NULL,
            created_at TEXT NOT NULL,
            PRIMARY KEY(wallet_address, topic_key)
          );
          INSERT INTO launched_topics_scoped(wallet_address, topic_key, token, tx_hash, created_at)
          SELECT '', topic_key, token, tx_hash, created_at FROM launched_topics;
          DROP TABLE launched_topics;
          ALTER TABLE launched_topics_scoped RENAME TO launched_topics;
        `);
      }
      this.database.exec(`
        CREATE INDEX IF NOT EXISTS launches_wallet_idx ON launches(wallet_address, created_at);
        CREATE INDEX IF NOT EXISTS ledger_wallet_idx ON ledger(wallet_address, bucket, asset, created_at);
        CREATE INDEX IF NOT EXISTS executions_wallet_idx ON executions(wallet_address, status, created_at);
        CREATE INDEX IF NOT EXISTS operator_commands_wallet_idx ON operator_commands(wallet_address, status, created_at);
      `);
    }).immediate;
    migrate();
  }

  scopeLegacyWalletData(address: string) {
    const walletAddress = address.toLowerCase();
    const scope = this.database.transaction(() => {
      for (const table of ["launches", "ledger", "launched_topics", "executions", "operator_commands"]) {
        this.database.prepare(`UPDATE ${table} SET wallet_address = ? WHERE wallet_address = ''`).run(walletAddress);
      }
    }).immediate;
    scope();
  }

  upsertWalletProfile(profile: { id: string; address: string; encryptedPrivateKey: string }) {
    const now = new Date().toISOString();
    const update = this.database.transaction(() => {
      this.database.prepare("UPDATE wallet_vault SET active = 0, updated_at = ? WHERE active = 1").run(now);
      this.database.prepare(`
        INSERT INTO wallet_vault(id, address, encrypted_private_key, active, created_at, updated_at)
        VALUES(@id, @address, @encryptedPrivateKey, 1, @now, @now)
        ON CONFLICT(address) DO UPDATE SET
          encrypted_private_key = excluded.encrypted_private_key,
          active = 1,
          updated_at = excluded.updated_at
      `).run({ ...profile, now });
    }).immediate;
    update();
  }

  getActiveWalletProfile() {
    return this.database.prepare(`
      SELECT id, address, encrypted_private_key AS encryptedPrivateKey,
             created_at AS createdAt, updated_at AS updatedAt
      FROM wallet_vault WHERE active = 1 LIMIT 1
    `).get() as {
      id: string;
      address: string;
      encryptedPrivateKey: string;
      createdAt: string;
      updatedAt: string;
    } | undefined;
  }

  listWalletProfiles() {
    return this.database.prepare(`
      SELECT id, address, active, created_at AS createdAt, updated_at AS updatedAt
      FROM wallet_vault ORDER BY updated_at DESC
    `).all() as Array<{ id: string; address: string; active: 0 | 1; createdAt: string; updatedAt: string }>;
  }

  enqueueOperatorCommand(walletAddress: string, command: { id: string; kind: "launch" | "claim"; request: Record<string, unknown> }) {
    this.database.prepare(`
      INSERT INTO operator_commands(id, wallet_address, kind, status, request_json, created_at)
      VALUES(?, ?, ?, 'queued', ?, ?)
    `).run(command.id, walletAddress.toLowerCase(), command.kind, JSON.stringify(command.request), new Date().toISOString());
  }

  claimNextOperatorCommand(walletAddress: string) {
    const scopedAddress = walletAddress.toLowerCase();
    const claim = this.database.transaction(() => {
      const row = this.database.prepare(`
        SELECT id, wallet_address AS walletAddress, kind, request_json AS requestJson, created_at AS createdAt
        FROM operator_commands
        WHERE status = 'queued' AND lower(wallet_address) = lower(?)
        ORDER BY created_at ASC LIMIT 1
      `).get(scopedAddress) as { id: string; walletAddress: string; kind: "launch" | "claim"; requestJson: string; createdAt: string } | undefined;
      if (!row) return undefined;
      const result = this.database.prepare(`
        UPDATE operator_commands SET status = 'running', started_at = ?
        WHERE id = ? AND status = 'queued' AND lower(wallet_address) = lower(?)
      `).run(new Date().toISOString(), row.id, scopedAddress);
      return result.changes === 1 ? row : undefined;
    }).immediate;
    return claim();
  }

  completeOperatorCommand(walletAddress: string, id: string, result: Record<string, unknown>) {
    this.database.prepare(`
      UPDATE operator_commands SET status = 'completed', result_json = ?, error = NULL, completed_at = ?
      WHERE id = ? AND lower(wallet_address) = lower(?)
    `).run(JSON.stringify(result), new Date().toISOString(), id, walletAddress);
  }

  failOperatorCommand(walletAddress: string, id: string, error: string) {
    this.database.prepare(`
      UPDATE operator_commands SET status = 'failed', error = ?, completed_at = ?
      WHERE id = ? AND lower(wallet_address) = lower(?)
    `).run(error.slice(0, 2_000), new Date().toISOString(), id, walletAddress);
  }

  listOperatorCommands(walletAddress: string, limit = 20) {
    return this.database.prepare(`
      SELECT id, wallet_address AS walletAddress, kind, status, request_json AS requestJson, result_json AS resultJson,
             error, created_at AS createdAt, started_at AS startedAt, completed_at AS completedAt
      FROM operator_commands WHERE lower(wallet_address) = lower(?)
      ORDER BY created_at DESC LIMIT ?
    `).all(walletAddress, limit) as Array<{
      id: string;
      walletAddress: string;
      kind: "launch" | "claim";
      status: "queued" | "running" | "completed" | "failed";
      requestJson: string;
      resultJson: string | null;
      error: string | null;
      createdAt: string;
      startedAt: string | null;
      completedAt: string | null;
    }>;
  }

  getSequence() {
    const row = this.database.prepare("SELECT value FROM state WHERE key = 'event_sequence'").get() as { value: string } | undefined;
    return row ? Number(row.value) : 0;
  }

  nextSequence() {
    const next = this.getSequence() + 1;
    this.database.prepare(`
      INSERT INTO state(key, value, updated_at) VALUES('event_sequence', ?, ?)
      ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at
    `).run(String(next), new Date().toISOString());
    return next;
  }

  addLaunch(walletAddress: string, launch: { token: string; pool: string; txHash: string; name: string; symbol: string; createdAt?: string }) {
    this.database.prepare(`
      INSERT INTO launches(token, wallet_address, pool, tx_hash, name, symbol, created_at)
      VALUES(@token, @walletAddress, @pool, @txHash, @name, @symbol, @createdAt)
      ON CONFLICT(token) DO NOTHING
    `).run({ ...launch, walletAddress: walletAddress.toLowerCase(), createdAt: launch.createdAt ?? new Date().toISOString() });
  }

  recordLaunchedTopic(walletAddress: string, topic: string, token: string, txHash: string) {
    this.database.prepare(`
      INSERT INTO launched_topics(wallet_address, topic_key, token, tx_hash, created_at)
      VALUES(?, ?, ?, ?, ?)
      ON CONFLICT(wallet_address, topic_key) DO NOTHING
    `).run(walletAddress.toLowerCase(), topic.trim().toLowerCase(), token, txHash, new Date().toISOString());
  }

  hasLaunchedTopic(walletAddress: string, topic: string) {
    return Boolean(this.database.prepare(`
      SELECT 1 FROM launched_topics WHERE lower(wallet_address) = lower(?) AND topic_key = ?
    `).get(walletAddress, topic.trim().toLowerCase()));
  }

  hasLaunchWithin(walletAddress: string, minutes: number) {
    const row = this.database.prepare(`
      SELECT created_at AS createdAt FROM launches
      WHERE lower(wallet_address) = lower(?) ORDER BY created_at DESC LIMIT 1
    `).get(walletAddress) as { createdAt: string } | undefined;
    if (!row) return false;
    return Date.now() - new Date(row.createdAt).getTime() < minutes * 60_000;
  }

  listLaunches(walletAddress: string) {
    return this.database.prepare(`
      SELECT token, pool, tx_hash AS txHash, name, symbol, created_at AS createdAt
      FROM launches WHERE lower(wallet_address) = lower(?)
      ORDER BY created_at ASC
    `).all(walletAddress) as Array<{ token: string; pool: string; txHash: string; name: string; symbol: string; createdAt: string }>;
  }

  addLedgerEntry(walletAddress: string, entry: {
    id: string;
    bucket: "creator-fees" | "buyback" | "treasury" | "gas";
    direction: "credit" | "debit";
    asset: string;
    amount: string;
    txHash?: string;
  }) {
    if (BigInt(entry.amount) < 0n) throw new Error("Ledger amounts must be non-negative");
    this.database.prepare(`
      INSERT INTO ledger(id, wallet_address, bucket, direction, asset, amount, tx_hash, created_at)
      VALUES(@id, @walletAddress, @bucket, @direction, @asset, @amount, @txHash, @createdAt)
      ON CONFLICT(id) DO NOTHING
    `).run({ ...entry, walletAddress: walletAddress.toLowerCase(), txHash: entry.txHash ?? null, createdAt: new Date().toISOString() });
  }

  getBucketBalance(walletAddress: string, bucket: "creator-fees" | "buyback" | "treasury" | "gas", asset: string) {
    const rows = this.database.prepare(`
      SELECT direction, amount FROM ledger
      WHERE lower(wallet_address) = lower(?) AND bucket = ? AND lower(asset) = lower(?)
      ORDER BY created_at ASC
    `).all(walletAddress, bucket, asset) as Array<{ direction: "credit" | "debit"; amount: string }>;
    return rows.reduce(
      (balance, row) => balance + (row.direction === "credit" ? BigInt(row.amount) : -BigInt(row.amount)),
      0n,
    );
  }

  listBucketBalances(walletAddress: string, bucket: "creator-fees" | "buyback" | "treasury" | "gas") {
    const rows = this.database.prepare(`
      SELECT asset, direction, amount FROM ledger
      WHERE lower(wallet_address) = lower(?) AND bucket = ? ORDER BY created_at ASC
    `).all(walletAddress, bucket) as Array<{ asset: string; direction: "credit" | "debit"; amount: string }>;
    const balances = new Map<string, { asset: string; balance: bigint }>();
    for (const row of rows) {
      const key = row.asset.toLowerCase();
      const current = balances.get(key) ?? { asset: row.asset, balance: 0n };
      current.balance += row.direction === "credit" ? BigInt(row.amount) : -BigInt(row.amount);
      balances.set(key, current);
    }
    return [...balances.values()];
  }

  reserveBucketFunds(walletAddress: string, entry: {
    id: string;
    bucket: "creator-fees" | "buyback" | "treasury" | "gas";
    asset: string;
    amount: string;
  }) {
    if (BigInt(entry.amount) <= 0n) throw new Error("Reservation amount must be positive");
    // IMMEDIATE prevents two CLI processes from both reserving the same balance.
    const reserve = this.database.transaction(() => {
      if (this.getBucketBalance(walletAddress, entry.bucket, entry.asset) < BigInt(entry.amount)) return false;
      const result = this.database.prepare(`
        INSERT OR IGNORE INTO ledger(id, wallet_address, bucket, direction, asset, amount, tx_hash, created_at)
        VALUES(?, ?, ?, 'debit', ?, ?, NULL, ?)
      `).run(entry.id, walletAddress.toLowerCase(), entry.bucket, entry.asset, entry.amount, new Date().toISOString());
      return result.changes === 1;
    }).immediate;
    return reserve();
  }

  releaseBucketFunds(walletAddress: string, reservationId: string, bucket: "creator-fees" | "buyback" | "treasury" | "gas", asset: string, amount: string) {
    if (BigInt(amount) <= 0n) throw new Error("Release amount must be positive");
    const release = this.database.transaction(() => {
      const reservation = this.database.prepare(`
        SELECT amount FROM ledger
        WHERE id = ? AND lower(wallet_address) = lower(?) AND bucket = ? AND direction = 'debit' AND lower(asset) = lower(?)
      `).get(reservationId, walletAddress, bucket, asset) as { amount: string } | undefined;
      if (!reservation || reservation.amount !== amount) return false;
      const result = this.database.prepare(`
        INSERT OR IGNORE INTO ledger(id, wallet_address, bucket, direction, asset, amount, tx_hash, created_at)
        VALUES(?, ?, ?, 'credit', ?, ?, NULL, ?)
      `).run(`${reservationId}:release`, walletAddress.toLowerCase(), bucket, asset, amount, new Date().toISOString());
      return result.changes === 1;
    }).immediate;
    return release();
  }

  setLedgerEntryTxHash(walletAddress: string, id: string, txHash: string) {
    this.database.prepare("UPDATE ledger SET tx_hash = ? WHERE id = ? AND lower(wallet_address) = lower(?)").run(txHash, id, walletAddress);
  }

  startExecution(walletAddress: string, execution: {
    id: string;
    kind: "launch" | "claim" | "buyback";
    target?: string;
    amount?: string;
    result?: Record<string, unknown>;
  }) {
    const now = new Date().toISOString();
    this.database.prepare(`
      INSERT INTO executions(id, wallet_address, kind, status, target, amount, result_json, created_at, updated_at)
      VALUES(@id, @walletAddress, @kind, 'planned', @target, @amount, @resultJson, @now, @now)
    `).run({
      ...execution,
      walletAddress: walletAddress.toLowerCase(),
      target: execution.target ?? null,
      amount: execution.amount ?? null,
      resultJson: execution.result ? JSON.stringify(execution.result) : null,
      now,
    });
  }

  startBuybackExecutionAndReserve(walletAddress: string, execution: {
    id: string;
    target: string;
    amount: string;
    result?: Record<string, unknown>;
  }, reservation: { id: string; asset: string }) {
    if (BigInt(execution.amount) <= 0n) throw new Error("Reservation amount must be positive");
    const start = this.database.transaction(() => {
      if (this.getBucketBalance(walletAddress, "buyback", reservation.asset) < BigInt(execution.amount)) return false;
      this.startExecution(walletAddress, { ...execution, kind: "buyback" });
      const result = this.database.prepare(`
        INSERT INTO ledger(id, wallet_address, bucket, direction, asset, amount, tx_hash, created_at)
        VALUES(?, ?, 'buyback', 'debit', ?, ?, NULL, ?)
      `).run(reservation.id, walletAddress.toLowerCase(), reservation.asset, execution.amount, new Date().toISOString());
      return result.changes === 1;
    }).immediate;
    return start();
  }

  updateExecution(walletAddress: string, id: string, status: "submitted" | "confirmed" | "failed" | "uncertain", options: {
    txHash?: string;
    result?: Record<string, unknown>;
    error?: string;
  } = {}) {
    this.database.prepare(`
      UPDATE executions
      SET status = @status,
          tx_hash = COALESCE(@txHash, tx_hash),
          result_json = COALESCE(@resultJson, result_json),
          error = @error,
          updated_at = @updatedAt
      WHERE id = @id AND lower(wallet_address) = lower(@walletAddress)
    `).run({
      id,
      walletAddress,
      status,
      txHash: options.txHash ?? null,
      resultJson: options.result ? JSON.stringify(options.result) : null,
      error: options.error?.slice(0, 2_000) ?? null,
      updatedAt: new Date().toISOString(),
    });
  }

  listRecentExecutions(walletAddress: string, limit = 10) {
    return this.database.prepare(`
      SELECT id, kind, status, target, amount, tx_hash AS txHash,
             result_json AS resultJson, error, created_at AS createdAt, updated_at AS updatedAt
      FROM executions WHERE lower(wallet_address) = lower(?) ORDER BY created_at DESC LIMIT ?
    `).all(walletAddress, limit) as Array<{
      id: string;
      kind: "launch" | "claim" | "buyback";
      status: "planned" | "submitted" | "confirmed" | "failed" | "uncertain";
      target: string | null;
      amount: string | null;
      txHash: string | null;
      resultJson: string | null;
      error: string | null;
      createdAt: string;
      updatedAt: string;
    }>;
  }

  getExecution(walletAddress: string, id: string) {
    return this.database.prepare(`
      SELECT id, kind, status, target, amount, tx_hash AS txHash,
             result_json AS resultJson, error, created_at AS createdAt, updated_at AS updatedAt
      FROM executions WHERE id = ? AND lower(wallet_address) = lower(?)
    `).get(id, walletAddress) as {
      id: string;
      kind: "launch" | "claim" | "buyback";
      status: "planned" | "submitted" | "confirmed" | "failed" | "uncertain";
      target: string | null;
      amount: string | null;
      txHash: string | null;
      resultJson: string | null;
      error: string | null;
      createdAt: string;
      updatedAt: string;
    } | undefined;
  }

  listRecoverableExecutions(walletAddress: string, limit = 100) {
    return this.database.prepare(`
      SELECT id, kind, status, target, amount, tx_hash AS txHash,
             result_json AS resultJson, error, created_at AS createdAt, updated_at AS updatedAt
      FROM executions
      WHERE lower(wallet_address) = lower(?)
        AND status IN ('submitted', 'uncertain') AND tx_hash IS NOT NULL
      ORDER BY created_at ASC LIMIT ?
    `).all(walletAddress, limit) as Array<{
      id: string;
      kind: "launch" | "claim" | "buyback";
      status: "submitted" | "uncertain";
      target: string | null;
      amount: string | null;
      txHash: string;
      resultJson: string | null;
      error: string | null;
      createdAt: string;
      updatedAt: string;
    }>;
  }

  listConfirmedExecutionsWithoutPublicEvent(walletAddress: string, limit = 100) {
    return this.database.prepare(`
      SELECT e.id, e.kind, e.status, e.target, e.amount, e.tx_hash AS txHash,
             e.result_json AS resultJson, e.error, e.created_at AS createdAt, e.updated_at AS updatedAt
      FROM executions e
      WHERE lower(e.wallet_address) = lower(?)
        AND e.status = 'confirmed' AND e.tx_hash IS NOT NULL
        AND NOT EXISTS (
          SELECT 1 FROM public_event_outbox o
          WHERE json_extract(o.event_json, '$.txHash') = e.tx_hash
            AND json_extract(o.event_json, '$.type') = CASE e.kind
              WHEN 'launch' THEN 'launch.confirmed'
              WHEN 'claim' THEN 'fees.claimed'
              WHEN 'buyback' THEN 'buyback.confirmed'
            END
        )
        AND COALESCE(json_extract(e.result_json, '$.action'), '') <> 'convert'
      ORDER BY e.created_at ASC LIMIT ?
    `).all(walletAddress, limit) as Array<{
      id: string;
      kind: "launch" | "claim" | "buyback";
      status: "confirmed";
      target: string | null;
      amount: string | null;
      txHash: string;
      resultJson: string | null;
      error: string | null;
      createdAt: string;
      updatedAt: string;
    }>;
  }

  recordLaunchExecution(walletAddress: string, executionId: string, launch: { token: string; pool: string; txHash: string; name: string; symbol: string; topic?: string }, result: Record<string, unknown>) {
    const record = this.database.transaction(() => {
      this.addLaunch(walletAddress, launch);
      if (launch.topic) this.recordLaunchedTopic(walletAddress, launch.topic, launch.token, launch.txHash);
      this.updateExecution(walletAddress, executionId, "confirmed", { txHash: launch.txHash, result });
    }).immediate;
    record();
  }

  recordClaimExecution(walletAddress: string, executionId: string, claim: {
    txHash: string;
    token: string;
    token0: string;
    token1: string;
    recipientAmount0: bigint;
    recipientAmount1: bigint;
  }, result: Record<string, unknown>) {
    const buybackAmount0 = claim.recipientAmount0 / 2n;
    const buybackAmount1 = claim.recipientAmount1 / 2n;
    const treasuryAmount0 = claim.recipientAmount0 - buybackAmount0;
    const treasuryAmount1 = claim.recipientAmount1 - buybackAmount1;
    const record = this.database.transaction(() => {
      const entries = [
        ["creator:0", "creator-fees", "credit", claim.token0, claim.recipientAmount0],
        ["creator:1", "creator-fees", "credit", claim.token1, claim.recipientAmount1],
        ["creator-allocation:0", "creator-fees", "debit", claim.token0, claim.recipientAmount0],
        ["creator-allocation:1", "creator-fees", "debit", claim.token1, claim.recipientAmount1],
        ["buyback:0", "buyback", "credit", claim.token0, buybackAmount0],
        ["buyback:1", "buyback", "credit", claim.token1, buybackAmount1],
        ["treasury:0", "treasury", "credit", claim.token0, treasuryAmount0],
        ["treasury:1", "treasury", "credit", claim.token1, treasuryAmount1],
      ] as const;
      for (const [prefix, bucket, direction, asset, amount] of entries) {
        this.addLedgerEntry(walletAddress, {
          id: `${claim.txHash}:${prefix}`,
          bucket,
          direction,
          asset,
          amount: amount.toString(),
          txHash: claim.txHash,
        });
      }
      this.updateExecution(walletAddress, executionId, "confirmed", { txHash: claim.txHash, result });
    }).immediate;
    record();
  }

  recordConversionExecution(walletAddress: string, executionId: string, conversion: {
    txHash: string;
    inputAsset: string;
    inputAmount: string;
    outputAsset: string;
    outputAmount: string;
  }, result: Record<string, unknown>) {
    const record = this.database.transaction(() => {
      const reservation = this.database.prepare(`
        SELECT amount FROM ledger
        WHERE id = ? AND lower(wallet_address) = lower(?)
          AND bucket = 'buyback' AND direction = 'debit' AND lower(asset) = lower(?)
      `).get(`${executionId}:buyback-reservation`, walletAddress, conversion.inputAsset) as { amount: string } | undefined;
      if (!reservation || reservation.amount !== conversion.inputAmount) {
        throw new Error("Fee conversion reservation does not match the confirmed execution");
      }
      this.addLedgerEntry(walletAddress, {
        id: `${conversion.txHash}:conversion:output`,
        bucket: "buyback",
        direction: "credit",
        asset: conversion.outputAsset,
        amount: conversion.outputAmount,
        txHash: conversion.txHash,
      });
      this.updateExecution(walletAddress, executionId, "confirmed", { txHash: conversion.txHash, result });
    }).immediate;
    record();
  }

  acquireRuntimeLock(name: string, owner: string, ttlMs: number) {
    const acquire = this.database.transaction(() => {
      const now = new Date();
      this.database.prepare("DELETE FROM runtime_locks WHERE expires_at <= ?").run(now.toISOString());
      const result = this.database.prepare(`
        INSERT OR IGNORE INTO runtime_locks(name, owner, acquired_at, expires_at)
        VALUES(?, ?, ?, ?)
      `).run(name, owner, now.toISOString(), new Date(now.getTime() + ttlMs).toISOString());
      return result.changes === 1;
    });
    return acquire();
  }

  renewRuntimeLock(name: string, owner: string, ttlMs: number) {
    const result = this.database.prepare(`
      UPDATE runtime_locks SET expires_at = ? WHERE name = ? AND owner = ?
    `).run(new Date(Date.now() + ttlMs).toISOString(), name, owner);
    return result.changes === 1;
  }

  releaseRuntimeLock(name: string, owner: string) {
    this.database.prepare("DELETE FROM runtime_locks WHERE name = ? AND owner = ?").run(name, owner);
  }

  getRuntimeLock(name: string) {
    return this.database.prepare(`
      SELECT name, owner, acquired_at AS acquiredAt, expires_at AS expiresAt
      FROM runtime_locks WHERE name = ? AND expires_at > ?
    `).get(name, new Date().toISOString()) as { name: string; owner: string; acquiredAt: string; expiresAt: string } | undefined;
  }

  startCycle(id: string) {
    this.database.prepare(`
      INSERT INTO cycles(id, started_at, status) VALUES(?, ?, 'running')
    `).run(id, new Date().toISOString());
  }

  completeCycle(id: string, decisionCount: number) {
    this.database.prepare(`
      UPDATE cycles SET completed_at = ?, status = 'completed', decision_count = ?, error = NULL WHERE id = ?
    `).run(new Date().toISOString(), decisionCount, id);
  }

  failCycle(id: string, error: string) {
    this.database.prepare(`
      UPDATE cycles SET completed_at = ?, status = 'failed', error = ? WHERE id = ?
    `).run(new Date().toISOString(), error.slice(0, 2_000), id);
  }

  listRecentCycles(limit = 10) {
    return this.database.prepare(`
      SELECT id, started_at AS startedAt, completed_at AS completedAt, status, decision_count AS decisionCount, error
      FROM cycles ORDER BY started_at DESC LIMIT ?
    `).all(limit) as Array<{
      id: string;
      startedAt: string;
      completedAt: string | null;
      status: "running" | "completed" | "failed";
      decisionCount: number | null;
      error: string | null;
    }>;
  }

  enqueuePublicEvent(event: SignedAgentEvent) {
    this.database.prepare(`
      INSERT INTO public_event_outbox(event_id, sequence, event_json, status, created_at)
      VALUES(?, ?, ?, 'pending', ?)
      ON CONFLICT(event_id) DO NOTHING
    `).run(event.eventId, event.sequence, JSON.stringify(event), new Date().toISOString());
  }

  listPendingPublicEvents(limit = 100) {
    const rows = this.database.prepare(`
      SELECT event_json AS eventJson FROM public_event_outbox
      WHERE status = 'pending' ORDER BY sequence ASC LIMIT ?
    `).all(limit) as Array<{ eventJson: string }>;
    return rows.map((row) => JSON.parse(row.eventJson) as SignedAgentEvent);
  }

  markPublicEventSent(eventId: string) {
    this.database.prepare(`
      UPDATE public_event_outbox SET status = 'sent', attempts = attempts + 1, last_error = NULL, sent_at = ?
      WHERE event_id = ?
    `).run(new Date().toISOString(), eventId);
  }

  markPublicEventFailed(eventId: string, error: string) {
    this.database.prepare(`
      UPDATE public_event_outbox SET attempts = attempts + 1, last_error = ? WHERE event_id = ?
    `).run(error.slice(0, 2_000), eventId);
  }

  getPublicEventOutboxStatus() {
    const rows = this.database.prepare(`
      SELECT status, COUNT(*) AS count FROM public_event_outbox GROUP BY status
    `).all() as Array<{ status: "pending" | "sent"; count: number }>;
    return rows.reduce((summary, row) => ({ ...summary, [row.status]: row.count }), { pending: 0, sent: 0 });
  }

  close() {
    this.database.close();
  }
}
