/*
<MODULE_CONTRACT>
<purpose>
RFC-0382: SQLite-backed cache layer using better-sqlite3. Provides persistent,
file-backed caching with WAL mode and busy timeout for concurrent access.
Self-healing: corrupt DB is deleted and recreated on open error.
</purpose>
<non-goals>
  <item>Do not implement RFC-specific cache helpers — that lives in rfc-cache.ts.</item>
  <item>Do not handle cache invalidation logic — callers check mtime + contentHash.</item>
</non-goals>
</MODULE_CONTRACT>
<KEY_DECISIONS>
  <item>SQLite is the persistent backend — it survives process restarts unlike the in-memory layer.</item>
</KEY_DECISIONS>
<CHANGE_SUMMARY>
  <item>ADR-0023: add close() method for explicit SQLite connection cleanup after pipeline completion.</item>
  <item>RFC-1097: step 6 — compass.migrate codemod run

Mechanical v1 to v2 header migration across the workspace: 942 files rewritten — CHANGE_SUMMARY windows collapsed into history, forbidden v1 blocks stripped, KEY_DECISIONS seeded from @ai-invariant comments (5 files) or TODO placeholders (103 files), blocks reordered to canonical order.</item>
  <item>RFC-1097: sweep — werkstatt-engine clean

Sweep batch 4: 73 Compass headers on headerless engine files (certification, component-runtime, isolation, evolution, testing), real KEY_DECISIONS on 75 files (kernel, cache, dht, swim, gitmesh, runtime), ~80 purpose expansions (CONTRACT-02/PURPOSE-02), non-goals on 13 CONTRACT-03 files, CS-07 history literal fix repo-wide (253 files). Policy: .template.ts/.template.astro excludedPaths. werkstatt-engine now 0 diagnostics.</item>
  <item>RFC-1133: cache direct executeKernelCommand executions with flag-keyed results</item>
  <item>RFC-1149: migrate ambient filesystem and process IO to the WorkspaceIO port across engine and shared</item>
  <history>RFC-0382</history>
</CHANGE_SUMMARY>
*/

import { dirname } from "node:path";
import { createRequire } from "node:module";
import { createDefaultIO, type WorkspaceIO } from "@warpgogol/werkstatt-shared/kernel/workspace-io";

import type {
  CacheEntry,
  CacheEntryInfo,
  CacheLayer,
  CacheListFilter,
  CacheNamespaceStatus,
  CacheStatus,
} from "./cache-layer.ts";
import { parseCommandResultCacheKey } from "./command-result-cache.ts";

const require_ = createRequire(import.meta.url);

const SCHEMA_DDL = `
CREATE TABLE IF NOT EXISTS cache_entries (
  namespace TEXT NOT NULL,
  key TEXT NOT NULL,
  data TEXT NOT NULL,
  mtime INTEGER NOT NULL,
  content_hash TEXT NOT NULL,
  schema_version INTEGER NOT NULL DEFAULT 1,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (namespace, key)
);

CREATE TABLE IF NOT EXISTS cache_stats (
  namespace TEXT NOT NULL,
  key TEXT NOT NULL,
  hits INTEGER NOT NULL DEFAULT 0,
  misses INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (namespace, key)
);
`;

interface CacheRow {
  data: string;
  mtime: number;
  content_hash: string;
  schema_version: number;
  updated_at: number;
}

interface NamespaceCountRow {
  namespace: string;
  entries: number;
}

interface StatsAggregateRow {
  total_hits: number | null;
  total_misses: number | null;
}

export class SqliteCacheLayer implements CacheLayer {
  readonly available = true;
  readonly unavailableReason: undefined;
  private readonly dbPath: string;
  private readonly io: WorkspaceIO;
  private db!: import("better-sqlite3").Database;

  private constructor(dbPath: string, io: WorkspaceIO) {
    this.dbPath = dbPath;
    this.io = io;
  }

  private openDb(): void {
    const BetterSqlite3 = require_("better-sqlite3") as typeof import("better-sqlite3");
    this.db = new BetterSqlite3(this.dbPath);
    this.db.pragma("journal_mode = WAL");
    this.db.pragma("busy_timeout = 5000");
    this.db.exec(SCHEMA_DDL);
  }

  static async create(
    dbPath: string,
    io: WorkspaceIO = createDefaultIO().io,
  ): Promise<SqliteCacheLayer> {
    const layer = new SqliteCacheLayer(dbPath, io);
    try {
      await io.mkdir(dirname(dbPath));
      layer.openDb();
    } catch (err) {
      // Self-healing: delete corrupt DB and retry once
      if (await io.exists(dbPath)) {
        try {
          await io.rm(dbPath);
        } catch {
          // ignore unlink errors
        }
      }
      await io.mkdir(dirname(dbPath));
      layer.openDb();
      if (err instanceof Error) {
        // pipeline-log-ok: self-healing corrupt cache DB — operational runtime warning, not a pipeline diagnostic
        console.warn(`cache DB was corrupt and has been recreated: ${err.message}`);
      }
    }
    return layer;
  }

  async get(namespace: string, key: string): Promise<CacheEntry | null> {
    const row = this.db
      .prepare<[string, string]>(
        "SELECT data, mtime, content_hash, schema_version, updated_at FROM cache_entries WHERE namespace = ? AND key = ?",
      )
      .get(namespace, key) as CacheRow | undefined;

    if (!row) {
      this.incrementMiss(namespace, key);
      return null;
    }

    this.incrementHit(namespace, key);
    return {
      key,
      data: JSON.parse(row.data),
      mtime: row.mtime,
      contentHash: row.content_hash,
      updatedAt: row.updated_at,
    };
  }

  async set(
    namespace: string,
    key: string,
    data: unknown,
    mtime: number,
    contentHash: string,
  ): Promise<void> {
    const dataJson = JSON.stringify(data);
    const now = Date.now();
    this.db
      .prepare<[string, string, string, number, string, number]>(
        `INSERT INTO cache_entries (namespace, key, data, mtime, content_hash, schema_version, updated_at)
         VALUES (?, ?, ?, ?, ?, 1, ?)
         ON CONFLICT(namespace, key) DO UPDATE SET
           data = excluded.data,
           mtime = excluded.mtime,
           content_hash = excluded.content_hash,
           schema_version = excluded.schema_version,
           updated_at = excluded.updated_at`,
      )
      .run(namespace, key, dataJson, mtime, contentHash, now);
  }

  async clear(namespace?: string): Promise<void> {
    if (namespace) {
      this.db.prepare("DELETE FROM cache_entries WHERE namespace = ?").run(namespace);
      this.db.prepare("DELETE FROM cache_stats WHERE namespace = ?").run(namespace);
    } else {
      this.db.exec("DELETE FROM cache_entries");
      this.db.exec("DELETE FROM cache_stats");
    }
  }

  async status(): Promise<CacheStatus> {
    let dbSizeBytes = 0;
    try {
      dbSizeBytes = (await this.io.stat(this.dbPath)).size;
    } catch {
      dbSizeBytes = 0;
    }

    const namespaceRows = this.db
      .prepare<[]>("SELECT namespace, COUNT(*) as entries FROM cache_entries GROUP BY namespace")
      .all() as NamespaceCountRow[];

    const namespaces: CacheNamespaceStatus[] = [];

    for (const ns of namespaceRows) {
      const statsRow = this.db
        .prepare<[string]>(
          "SELECT COALESCE(SUM(hits), 0) as total_hits, COALESCE(SUM(misses), 0) as total_misses FROM cache_stats WHERE namespace = ?",
        )
        .get(ns.namespace) as StatsAggregateRow;

      const totalHits = statsRow?.total_hits ?? 0;
      const totalMisses = statsRow?.total_misses ?? 0;
      const total = totalHits + totalMisses;
      const hitRatio = total > 0 ? totalHits / total : 0;

      namespaces.push({
        name: ns.namespace,
        entries: ns.entries,
        hitRatio,
      });
    }

    return {
      available: true,
      dbPath: this.dbPath,
      dbSizeBytes,
      namespaces,
    };
  }

  private incrementHit(namespace: string, key: string): void {
    this.db
      .prepare<[string, string]>(
        `INSERT INTO cache_stats (namespace, key, hits, misses) VALUES (?, ?, 1, 0)
         ON CONFLICT(namespace, key) DO UPDATE SET hits = hits + 1`,
      )
      .run(namespace, key);
  }

  private incrementMiss(namespace: string, key: string): void {
    this.db
      .prepare<[string, string]>(
        `INSERT INTO cache_stats (namespace, key, hits, misses) VALUES (?, ?, 0, 1)
         ON CONFLICT(namespace, key) DO UPDATE SET misses = misses + 1`,
      )
      .run(namespace, key);
  }

  async close(): Promise<void> {
    this.db.close();
  }

  async list(filter?: CacheListFilter): Promise<CacheEntryInfo[]> {
    let sql = "SELECT namespace, key, updated_at FROM cache_entries";
    const conditions: string[] = [];
    const params: string[] = [];
    if (filter?.namespace) {
      conditions.push("namespace = ?");
      params.push(filter.namespace);
    }
    if (filter?.commandName) {
      conditions.push("key LIKE ?");
      params.push(`%:${filter.commandName}:%`);
    }
    if (conditions.length > 0) {
      sql += " WHERE " + conditions.join(" AND ");
    }
    sql += " ORDER BY updated_at DESC";
    const rows = this.db.prepare(sql).all(...params) as {
      namespace: string;
      key: string;
      updated_at: number;
    }[];

    const results: CacheEntryInfo[] = [];
    for (const row of rows) {
      const parsed = parseCommandResultCacheKey(row.namespace, row.key);
      if (!parsed) continue;
      const statsRow = this.db
        .prepare<[string, string]>("SELECT hits FROM cache_stats WHERE namespace = ? AND key = ?")
        .get(row.namespace, row.key) as { hits: number } | undefined;
      results.push({
        namespace: row.namespace,
        key: row.key,
        commandName: parsed.commandName,
        siteName: parsed.siteName,
        inputsHash: parsed.inputsHash,
        moduleHash: parsed.moduleHash,
        flagsHash: parsed.flagsHash,
        cachedAt: row.updated_at,
        hitCount: statsRow?.hits ?? 0,
      });
    }
    return results;
  }
}