sqlite.ts

/**
 * SQLite Database Provider
 * File-based SQLite support - runs under both Bun and Node.
 *
 * The underlying driver is selected at runtime by the sqlite-driver adapter:
 * bun:sqlite under Bun, node:sqlite under Node (override with
 * LIBREDB_SQLITE_DRIVER=bun|node).
 *
 * Note: SQLite is primarily for local development. Cloud deployments
 * typically use PostgreSQL or MySQL instead.
 */

import { SQLBaseProvider } from "./sql-base";
import {
  type DatabaseConnection,
  type TableSchema,
  type QueryResult,
  type HealthInfo,
  type MaintenanceType,
  type MaintenanceResult,
  type ProviderOptions,
  type ProviderExecutionContext,
  type ReadOnlyStatementBudget,
  type ProviderCapabilities,
  type DatabaseOverview,
  type PerformanceMetrics,
  type SlowQueryStats,
  type ActiveSessionDetails,
  type TableStats,
  type IndexStats,
  type StorageStats,
} from "../../types";
import {
  DatabaseConfigError,
  ConnectionError,
  ExecutionProfileError,
  QueryError,
  mapDatabaseError,
} from "../../errors";
import { assertReadOnlyBudget, measureResultBytes } from "./read-only-budget";
import { formatBytes } from "../../utils/pool-manager";
import { loadSQLiteDriver, type SQLiteDatabase } from "./sqlite-driver";
import * as fs from "fs";
import * as path from "path";

// ============================================================================
// Type Definitions
// ============================================================================

// Row shapes returned by the PRAGMA introspection statements below.
interface SQLiteColumnInfoRow {
  cid: number;
  name: string;
  type: string;
  notnull: number;
  dflt_value: string | null;
  pk: number;
}

interface SQLiteForeignKeyRow {
  id: number;
  seq: number;
  table: string;
  from: string;
  to: string;
}

interface SQLiteIndexListRow {
  seq: number;
  name: string;
  unique: number;
}

// ============================================================================
// Introspection SQL
// ----------------------------------------------------------------------------
// Hoisted to module scope (not inlined in the methods) on purpose. bun's
// coverage instruments the interior lines of a *multi-line template literal in
// a function body* as 0-hit in any test process that imports this file but
// does not exercise the method — and the merged lcov then reports those SQL
// lines as uncovered even though the method is tested. Evaluated once at
// module load, these consts are reported as covered everywhere, so coverage
// stays accurate (same pattern as postgres.ts).
// ============================================================================

const SCHEMA_TABLES_SQL = `
      SELECT name FROM sqlite_master
      WHERE type = 'table'
      AND name NOT LIKE 'sqlite_%'
      ORDER BY name;
    `;

const DB_PAGE_SIZE_SQL = `
          SELECT (SELECT page_count FROM pragma_page_count()) *
                 (SELECT page_size FROM pragma_page_size()) as size
        `;

// Size of a :memory: database (no file to stat).
const MEMORY_DB_SIZE_SQL = `
          SELECT (page_count * page_size) as size
          FROM pragma_page_count(), pragma_page_size()
        `;

const TABLE_COUNT_SQL = `
      SELECT COUNT(*) as count FROM sqlite_master
      WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
    `;

const INDEX_COUNT_SQL = `
      SELECT COUNT(*) as count FROM sqlite_master
      WHERE type = 'index' AND name NOT LIKE 'sqlite_%'
    `;

const STATS_TABLES_SQL = `
      SELECT name FROM sqlite_master
      WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
      ORDER BY name
    `;

const STATS_INDEXES_SQL = `
      SELECT name, tbl_name FROM sqlite_master
      WHERE type = 'index' AND name NOT LIKE 'sqlite_%'
      ORDER BY tbl_name, name
    `;

// ============================================================================
// Agent read-only execution profile (#328)
// ============================================================================

const QUERY_ONLY_PRAGMA_SQL = "PRAGMA query_only = true";
const QUERY_ONLY_READBACK_SQL = "PRAGMA query_only";

/**
 * Refuses a read-only handle whose `query_only` pragma does not read back
 * enabled.
 *
 * A read-only OPEN does not imply `query_only` on either adapter (it reads
 * back 0 until explicitly set), so the profile sets it and verifies it — at
 * open AND before every statement.
 *
 * The two controls cover different things and neither is redundant. The
 * read-only open governs the TARGET database file: writes to it are refused
 * and a missing file is not created. It does NOT govern writes to OTHER
 * files — `VACUUM INTO '<path>'` copies the whole database to an arbitrary
 * server path from a read-only handle on both adapters. That is what
 * `query_only` refuses, and why it is re-asserted per statement rather than
 * only at open: a statement can turn it off, but `prepare()` compiles exactly
 * one statement, so the disable and the write can never ride in the same call.
 */
export function assertQueryOnlyEnabled(readback: unknown[]): void {
  const value = (readback[0] as { query_only?: unknown } | undefined)?.query_only;
  if (value !== 1) {
    throw new ConnectionError(
      `SQLite read-only profile could not enable query_only (read back ${JSON.stringify(value ?? null)})`,
      "sqlite",
    );
  }
}

// ============================================================================
// SQLite Provider
// ============================================================================

export class SQLiteProvider extends SQLBaseProvider {
  private db: SQLiteDatabase | null = null;
  /** True when this instance was opened under the agent read-only profile. */
  private readonly readOnlyProfile: boolean;

  constructor(config: DatabaseConnection, options: ProviderOptions = {}, execution: ProviderExecutionContext = {}) {
    super(config, options);
    // Server-injected only (see ProviderExecutionContext): the shared editor
    // path builds providers from caller-supplied ProviderOptions, which has no
    // route to this flag in either direction.
    this.readOnlyProfile = execution.readOnly === true;
    this.validate();
  }

  // ============================================================================
  // Provider Metadata
  // ============================================================================

  public override getCapabilities(): ProviderCapabilities {
    return {
      ...super.getCapabilities(),
      defaultPort: null,
      supportsExplain: true,
      explainFormat: "sqlite-queryplan",
      supportsConnectionString: false,
      supportsInlineRowEdit: true,
      maintenanceOperations: ["vacuum", "analyze", "reindex", "check"],
    };
  }

  // ============================================================================
  // Validation
  // ============================================================================

  public validate(): void {
    super.validate();

    if (!this.config.database && !this.config.connectionString) {
      throw new DatabaseConfigError(
        'Database file path is required for SQLite (use "database" field or ":memory:" for in-memory)',
        "sqlite",
      );
    }
  }

  // ============================================================================
  // Connection Management
  // ============================================================================

  public async connect(): Promise<void> {
    if (this.db) {
      return;
    }

    try {
      // Dynamically load the runtime-appropriate SQLite driver
      const SQLiteDB = await loadSQLiteDriver();

      const dbPath = this.getDatabasePath();

      if (this.readOnlyProfile) {
        this.connectReadOnly(SQLiteDB, dbPath);
        return;
      }

      if (dbPath !== ":memory:") {
        const dir = path.dirname(dbPath);
        if (!fs.existsSync(dir)) {
          fs.mkdirSync(dir, { recursive: true });
        }
      }

      this.db = new SQLiteDB(dbPath, {
        create: true,
        readwrite: true,
      });

      // Enable WAL mode and foreign keys
      this.db.exec("PRAGMA foreign_keys = ON");
      this.db.exec("PRAGMA journal_mode = WAL");
      this.db.exec("PRAGMA synchronous = NORMAL");

      this.setConnected(true);
    } catch (error) {
      this.setError(error instanceof Error ? error : new Error(String(error)));

      // Typed refusals keep their own identity: wrapping them would strip the
      // config diagnosis / the profile's deny reason code.
      if (error instanceof DatabaseConfigError || error instanceof ExecutionProfileError) {
        throw error;
      }

      throw new ConnectionError(
        `Failed to open SQLite database: ${error instanceof Error ? error.message : error}`,
        "sqlite",
      );
    }
  }

  /**
   * Open the agent read-only handle (#328). Deliberately NOT the shared
   * sequence above:
   *
   * - no directory is created and no `create` flag is passed, so a missing
   *   target leaves the filesystem untouched (the read-only open itself
   *   refuses to create the file on both adapters);
   * - `PRAGMA journal_mode = WAL` is a write and fails outright on a read-only
   *   handle, so the shared pragma trio is skipped — none of it applies to a
   *   connection that cannot write;
   * - `query_only` is set and verified (a read-only open does not imply it).
   *
   * An in-memory target is refused: a read-only open of an anonymous database
   * can only ever yield an empty one (node:sqlite) or fail (bun:sqlite), so
   * vending it would hand the agent a silently useless target.
   */
  private connectReadOnly(SQLiteDB: Awaited<ReturnType<typeof loadSQLiteDriver>>, dbPath: string): void {
    if (dbPath === ":memory:") {
      throw new ExecutionProfileError(
        "The agent read-only execution profile cannot target an in-memory SQLite database",
        "PROFILE_UNSUPPORTED_TARGET",
      );
    }

    this.db = new SQLiteDB(dbPath, { readonly: true });
    try {
      this.enforceQueryOnly();
    } catch (error) {
      this.db.close();
      this.db = null;
      throw error;
    }

    this.setConnected(true);
  }

  /** Set `query_only` and refuse to continue unless it reads back enabled. */
  private enforceQueryOnly(): void {
    this.db!.exec(QUERY_ONLY_PRAGMA_SQL);
    assertQueryOnlyEnabled(this.db!.prepare(QUERY_ONLY_READBACK_SQL).all());
  }

  public async disconnect(): Promise<void> {
    if (this.db) {
      this.db.close();
      this.db = null;
      this.setConnected(false);
    }
  }

  private getDatabasePath(): string {
    let dbPath: string;
    if (this.config.connectionString) {
      dbPath = this.config.connectionString.startsWith("file:")
        ? this.config.connectionString.replace("file:", "")
        : this.config.connectionString;
    } else {
      dbPath = this.config.database || ":memory:";
    }

    // Allow :memory: without path validation
    if (dbPath === ":memory:") return dbPath;

    // Reject NUL bytes (never valid in a filesystem path), then resolve to an
    // absolute path. ".." segments are accepted by design: sqlite paths are
    // trusted server-side paths (docs/providers/sqlite.md).
    if (dbPath.includes("\0")) {
      throw new DatabaseConfigError("Invalid database path: NUL bytes are not allowed", "sqlite");
    }

    return path.resolve(dbPath);
  }

  // ============================================================================
  // Query Execution
  // ============================================================================

  public async query(sql: string, params?: unknown[]): Promise<QueryResult> {
    this.ensureConnected();

    return this.trackQuery(async () => {
      const { result, executionTime } = await this.measureExecution(async () => {
        try {
          const isSelect = this.isReadOnlyQuery(sql);

          if (isSelect) {
            const stmt = this.db!.prepare(sql);
            const rows = params ? stmt.all(...params) : stmt.all();
            const fields = rows.length > 0 ? Object.keys(rows[0] as object) : [];
            return {
              rows: (rows as unknown[]).map((row) => row as Record<string, unknown>) as Record<string, unknown>[],
              fields,
              changes: 0,
            };
          } else {
            const stmt = this.db!.prepare(sql);
            const info = params ? stmt.run(...params) : stmt.run();
            return {
              rows: [],
              fields: [],
              changes: info.changes,
            };
          }
        } catch (error) {
          throw mapDatabaseError(error, "sqlite", sql);
        }
      });

      return {
        rows: result.rows,
        fields: result.fields,
        rowCount: result.rows.length || result.changes,
        executionTime,
      };
    });
  }

  /**
   * Execute exactly one statement under SQLite's own read-only enforcement
   * (#328).
   *
   * The boundary is the database's own enforcement, not any inspection of
   * `sql`: a write reaching this method is executed and rejected by the
   * engine (see `assertQueryOnlyEnabled` for which control covers what). It is
   * therefore refused outright on a provider that was not opened under the
   * profile — a writable handle has no boundary to enforce, and silently
   * running the statement there would be exactly the fail-open this layer
   * exists to prevent.
   *
   * Statements are compiled with `prepare()`, never `exec()`: `exec()` runs
   * every statement of a multi-statement string, while `prepare()` compiles
   * only the first. Rejecting multi-statement input is the policy pipeline's
   * job — this method only guarantees the tail is never executed.
   */
  public async queryReadOnly(sql: string, budget: ReadOnlyStatementBudget): Promise<QueryResult> {
    this.ensureConnected();
    assertReadOnlyBudget(budget, "sqlite");
    if (!this.readOnlyProfile) {
      throw new QueryError(
        "Read-only execution requires a provider opened under the agent read-only profile",
        "sqlite",
        sql,
      );
    }
    // Per statement, not just at open: the profiled provider is pooled and
    // reused, so a previous statement's `PRAGMA query_only = false` would
    // otherwise persist for every later call on this connection.
    this.enforceQueryOnly();

    return this.trackQuery(async () => {
      const { result, executionTime } = await this.measureExecution(async () => {
        try {
          return this.db!.prepare(sql).all() as Record<string, unknown>[];
        } catch (error) {
          throw mapDatabaseError(error, "sqlite", sql);
        }
      });

      if (result.length > budget.maxResultRows) {
        throw new QueryError(
          `Read-only execution exceeded the row budget: ${result.length} rows > ${budget.maxResultRows} allowed`,
          "sqlite",
          sql,
        );
      }
      const resultBytes = measureResultBytes(result);
      if (resultBytes > budget.maxResultBytes) {
        throw new QueryError(
          `Read-only execution exceeded the byte budget: ${resultBytes} bytes > ${budget.maxResultBytes} allowed`,
          "sqlite",
          sql,
        );
      }
      // SQLite has no transaction-local statement timeout, and neither adapter
      // exposes sqlite3_interrupt or a progress handler, so the budget's
      // timeout is a post-execution deadline: an overrunning statement is not
      // preempted, but its result is refused rather than returned as if it had
      // been within budget. Recorded as such in docs/providers/sqlite.md.
      if (executionTime > budget.statementTimeoutMs) {
        throw new QueryError(
          `Read-only execution exceeded the time budget: ${executionTime}ms > ${budget.statementTimeoutMs}ms allowed`,
          "sqlite",
          sql,
        );
      }

      return {
        rows: result,
        fields: result.length > 0 ? Object.keys(result[0]) : [],
        rowCount: result.length,
        executionTime,
      };
    });
  }

  // ============================================================================
  // Schema Operations
  // ============================================================================

  public async getSchema(): Promise<TableSchema[]> {
    this.ensureConnected();

    const tablesStmt = this.db!.prepare(SCHEMA_TABLES_SQL);
    const tables = tablesStmt.all() as { name: string }[];

    const schemas: TableSchema[] = [];

    for (const { name: tableName } of tables) {
      const countStmt = this.db!.prepare(`SELECT COUNT(*) as count FROM "${tableName}"`);
      const countResult = countStmt.get() as { count: number };
      const rowCount = countResult?.count || 0;

      const columnsStmt = this.db!.prepare(`PRAGMA table_info("${tableName}")`);
      const columns = columnsStmt.all() as SQLiteColumnInfoRow[];

      const fkStmt = this.db!.prepare(`PRAGMA foreign_key_list("${tableName}")`);
      const foreignKeys = fkStmt.all() as SQLiteForeignKeyRow[];

      const indexStmt = this.db!.prepare(`PRAGMA index_list("${tableName}")`);
      const indexList = indexStmt.all() as SQLiteIndexListRow[];

      const indexes = [];
      for (const idx of indexList) {
        if (idx.name.startsWith("sqlite_")) continue;

        const indexInfoStmt = this.db!.prepare(`PRAGMA index_info("${idx.name}")`);
        const indexCols = indexInfoStmt.all() as Array<{ seqno: number; cid: number; name: string }>;

        indexes.push({
          name: idx.name,
          columns: indexCols.map((c) => c.name),
          unique: idx.unique === 1,
        });
      }

      let sizeBytes = 0;
      try {
        const pageCountStmt = this.db!.prepare(DB_PAGE_SIZE_SQL);
        const sizeResult = pageCountStmt.get() as { size: number };
        sizeBytes = sizeResult?.size || 0;
      } catch {
        // Ignore size calculation errors
      }

      schemas.push({
        name: tableName,
        rowCount,
        size: formatBytes(sizeBytes),
        columns: columns.map((col) => ({
          name: col.name,
          type: col.type || "TEXT",
          nullable: col.notnull === 0,
          isPrimary: col.pk === 1,
          defaultValue: col.dflt_value ?? undefined,
        })),
        indexes,
        foreignKeys: foreignKeys.map((fk) => ({
          columnName: fk.from,
          referencedTable: fk.table,
          referencedColumn: fk.to,
        })),
      });
    }

    return schemas;
  }

  // ============================================================================
  // Health & Monitoring
  // ============================================================================

  public async getHealth(): Promise<HealthInfo> {
    this.ensureConnected();

    const dbPath = this.getDatabasePath();

    let databaseSize = "N/A";
    if (dbPath !== ":memory:") {
      try {
        const stats = fs.statSync(dbPath);
        databaseSize = formatBytes(stats.size);
      } catch {
        databaseSize = "Unknown";
      }
    } else {
      try {
        const sizeStmt = this.db!.prepare(MEMORY_DB_SIZE_SQL);
        const result = sizeStmt.get() as { size: number };
        databaseSize = formatBytes(result?.size || 0);
      } catch {
        databaseSize = "N/A";
      }
    }

    let isHealthy = true;
    try {
      const integrityStmt = this.db!.prepare("PRAGMA integrity_check");
      const integrityResult = integrityStmt.get() as { integrity_check: string };
      isHealthy = integrityResult?.integrity_check === "ok";
    } catch {
      isHealthy = false;
    }

    let journalMode = "unknown";
    try {
      const journalStmt = this.db!.prepare("PRAGMA journal_mode");
      const journalResult = journalStmt.get() as { journal_mode: string };
      journalMode = journalResult?.journal_mode || "unknown";
    } catch {
      // Ignore
    }

    return {
      activeConnections: 1,
      databaseSize,
      cacheHitRatio: "N/A",
      slowQueries: [
        {
          query: `Integrity: ${isHealthy ? "OK" : "FAILED"}`,
          calls: 0,
          avgTime: "N/A",
        },
        {
          query: `Journal Mode: ${journalMode}`,
          calls: 0,
          avgTime: "N/A",
        },
      ],
      activeSessions: [
        {
          pid: process.pid,
          user: "sqlite",
          database: path.basename(dbPath),
          state: "active",
          query: "",
          duration: "N/A",
        },
      ],
    };
  }

  // ============================================================================
  // Maintenance Operations
  // ============================================================================

  public async runMaintenance(type: MaintenanceType, target?: string): Promise<MaintenanceResult> {
    this.ensureConnected();

    const { result, executionTime } = await this.measureExecution(async () => {
      let sql = "";

      switch (type) {
        case "vacuum":
          sql = "VACUUM";
          break;
        case "analyze":
          sql = target ? `ANALYZE ${this.escapeIdentifier(target)}` : "ANALYZE";
          break;
        case "reindex":
          sql = target ? `REINDEX ${this.escapeIdentifier(target)}` : "REINDEX";
          break;
        case "check":
          const checkStmt = this.db!.prepare("PRAGMA integrity_check");
          const checkResult = checkStmt.get() as { integrity_check: string };
          return {
            success: checkResult?.integrity_check === "ok",
            message: checkResult?.integrity_check || "Unknown",
          };
      }

      // Unsupported types fall through the switch with sql left empty. A
      // `default:` label is deliberately avoided here: bun's coverage emits a
      // 0-hit line record for `default:` that no runtime execution ever
      // credits, which permanently poisons the merged lcov report.
      if (!sql) {
        throw new QueryError(`Unsupported maintenance type for SQLite: ${type}`, "sqlite");
      }

      this.db!.exec(sql);
      return { success: true, message: `${type.toUpperCase()} completed successfully` };
    });

    return {
      success: result.success,
      executionTime,
      message: result.message,
    };
  }

  // ============================================================================
  // Monitoring Operations
  // ============================================================================

  public async getOverview(): Promise<DatabaseOverview> {
    this.ensureConnected();

    // Get SQLite version
    const versionStmt = this.db!.prepare("SELECT sqlite_version() as version");
    const versionResult = versionStmt.get() as { version: string };
    const version = `SQLite ${versionResult?.version || "Unknown"}`;

    // Get database size
    const dbPath = this.getDatabasePath();
    let databaseSizeBytes = 0;

    if (dbPath !== ":memory:") {
      try {
        const stats = fs.statSync(dbPath);
        databaseSizeBytes = stats.size;
      } catch {
        // File might not exist yet
      }
    } else {
      try {
        const sizeStmt = this.db!.prepare(MEMORY_DB_SIZE_SQL);
        const result = sizeStmt.get() as { size: number };
        databaseSizeBytes = result?.size || 0;
      } catch {
        // Ignore
      }
    }

    // Get table count
    const tableCountStmt = this.db!.prepare(TABLE_COUNT_SQL);
    const tableCountResult = tableCountStmt.get() as { count: number };
    const tableCount = tableCountResult?.count || 0;

    // Get index count
    const indexCountStmt = this.db!.prepare(INDEX_COUNT_SQL);
    const indexCountResult = indexCountStmt.get() as { count: number };
    const indexCount = indexCountResult?.count || 0;

    return {
      version,
      uptime: "N/A",
      activeConnections: 1,
      maxConnections: 1,
      databaseSize: formatBytes(databaseSizeBytes),
      databaseSizeBytes,
      tableCount,
      indexCount,
    };
  }

  public async getPerformanceMetrics(): Promise<PerformanceMetrics> {
    this.ensureConnected();

    let cacheHitRatio = 99;

    try {
      // Get cache stats
      const cacheStmt = this.db!.prepare("PRAGMA cache_size");
      const cacheResult = cacheStmt.get() as { cache_size: number };

      // SQLite doesn't provide detailed cache hit stats, estimate high ratio
      if (cacheResult?.cache_size) {
        cacheHitRatio = 95; // Reasonable estimate for in-memory cache
      }
    } catch {
      // Ignore
    }

    return {
      cacheHitRatio,
      // SQLite doesn't provide these metrics
      queriesPerSecond: undefined,
      bufferPoolUsage: undefined,
      deadlocks: 0,
    };
  }

  public async getSlowQueries(): Promise<SlowQueryStats[]> {
    // SQLite doesn't have built-in query statistics
    return [];
  }

  public async getActiveSessions(): Promise<ActiveSessionDetails[]> {
    this.ensureConnected();

    const dbPath = this.getDatabasePath();

    // SQLite is single-connection, return current session
    return [
      {
        pid: process.pid,
        user: "sqlite",
        database: path.basename(dbPath),
        state: "active",
        query: "",
        duration: "N/A",
        durationMs: 0,
      },
    ];
  }

  public async getTableStats(): Promise<TableStats[]> {
    this.ensureConnected();

    const tablesStmt = this.db!.prepare(STATS_TABLES_SQL);
    const tables = tablesStmt.all() as { name: string }[];

    const stats: TableStats[] = [];

    for (const { name: tableName } of tables) {
      // Get row count
      const countStmt = this.db!.prepare(`SELECT COUNT(*) as count FROM "${tableName}"`);
      const countResult = countStmt.get() as { count: number };
      const rowCount = countResult?.count || 0;

      // Estimate table size (SQLite doesn't provide per-table sizes)
      // Use page count approximation
      let tableSizeBytes = 0;
      try {
        // Rough estimate: rows * average row size
        tableSizeBytes = rowCount * 100; // Assume 100 bytes average per row
      } catch {
        // Ignore
      }

      stats.push({
        schemaName: "main",
        tableName,
        rowCount,
        tableSize: formatBytes(tableSizeBytes),
        tableSizeBytes,
        totalSize: formatBytes(tableSizeBytes),
        totalSizeBytes: tableSizeBytes,
      });
    }

    return stats;
  }

  public async getIndexStats(): Promise<IndexStats[]> {
    this.ensureConnected();

    const indexesStmt = this.db!.prepare(STATS_INDEXES_SQL);
    const indexes = indexesStmt.all() as { name: string; tbl_name: string }[];

    const stats: IndexStats[] = [];

    for (const { name: indexName, tbl_name: tableName } of indexes) {
      // Get index info
      const indexInfoStmt = this.db!.prepare(`PRAGMA index_info("${indexName}")`);
      const indexCols = indexInfoStmt.all() as { seqno: number; cid: number; name: string }[];

      // Get index uniqueness
      const indexListStmt = this.db!.prepare(`PRAGMA index_list("${tableName}")`);
      const indexList = indexListStmt.all() as { name: string; unique: number }[];
      const indexMeta = indexList.find((i) => i.name === indexName);

      stats.push({
        schemaName: "main",
        tableName,
        indexName,
        columns: indexCols.map((c) => c.name),
        isUnique: indexMeta?.unique === 1,
        isPrimary: false, // SQLite auto-creates rowid, explicit PKs are shown differently
        indexSize: "N/A",
        indexSizeBytes: 0,
        scans: 0, // SQLite doesn't track index usage
      });
    }

    return stats;
  }

  public async getStorageStats(): Promise<StorageStats[]> {
    this.ensureConnected();

    const stats: StorageStats[] = [];
    const dbPath = this.getDatabasePath();

    // Main database file
    let mainSizeBytes = 0;
    if (dbPath !== ":memory:") {
      try {
        const fileStats = fs.statSync(dbPath);
        mainSizeBytes = fileStats.size;
      } catch {
        // File might not exist
      }
    } else {
      try {
        const sizeStmt = this.db!.prepare(MEMORY_DB_SIZE_SQL);
        const result = sizeStmt.get() as { size: number };
        mainSizeBytes = result?.size || 0;
      } catch {
        // Ignore
      }
    }

    stats.push({
      name: "Main Database",
      location: dbPath === ":memory:" ? ":memory:" : path.basename(dbPath),
      size: formatBytes(mainSizeBytes),
      sizeBytes: mainSizeBytes,
    });

    // WAL file (if exists)
    if (dbPath !== ":memory:") {
      const walPath = `${dbPath}-wal`;
      try {
        const walStats = fs.statSync(walPath);
        stats.push({
          name: "WAL",
          location: path.basename(walPath),
          size: formatBytes(walStats.size),
          sizeBytes: walStats.size,
          walSize: formatBytes(walStats.size),
          walSizeBytes: walStats.size,
        });
      } catch {
        // WAL might not exist
      }

      // SHM file (if exists)
      const shmPath = `${dbPath}-shm`;
      try {
        const shmStats = fs.statSync(shmPath);
        stats.push({
          name: "Shared Memory",
          location: path.basename(shmPath),
          size: formatBytes(shmStats.size),
          sizeBytes: shmStats.size,
        });
      } catch {
        // SHM might not exist
      }
    }

    return stats;
  }
}