// SQLite store with FTS5.
// Synchronous API over an async-open handle (Bun:sqlite / better-sqlite3).

import { mkdirSync } from "node:fs";
import { dirname } from "node:path";
import { DB_PATH } from "../config.js";
import { openDatabase, type DbHandle } from "./adapter.js";
import type { Observation, Summary, SearchHit, FullObservation } from "../types.js";

const SCHEMA = `
CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY,
  project TEXT NOT NULL,
  started_at INTEGER NOT NULL,
  last_seen_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_sessions_project ON sessions(project);

CREATE TABLE IF NOT EXISTS observations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id TEXT NOT NULL,
  project TEXT NOT NULL,
  type TEXT NOT NULL,
  title TEXT NOT NULL,
  subtitle TEXT,
  facts TEXT NOT NULL DEFAULT '[]',
  narrative TEXT,
  concepts TEXT NOT NULL DEFAULT '[]',
  files_read TEXT NOT NULL DEFAULT '[]',
  files_modified TEXT NOT NULL DEFAULT '[]',
  source_tool_name TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (session_id) REFERENCES sessions(id)
);

CREATE INDEX IF NOT EXISTS idx_obs_session ON observations(session_id);
CREATE INDEX IF NOT EXISTS idx_obs_project_created ON observations(project, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_obs_type ON observations(type);

CREATE VIRTUAL TABLE IF NOT EXISTS observations_fts USING fts5(
  title,
  subtitle,
  facts,
  narrative,
  concepts,
  files_read,
  files_modified,
  content='observations',
  content_rowid='id',
  tokenize='porter unicode61'
);

CREATE TRIGGER IF NOT EXISTS obs_ai AFTER INSERT ON observations BEGIN
  INSERT INTO observations_fts(rowid, title, subtitle, facts, narrative, concepts, files_read, files_modified)
  VALUES (new.id, new.title, new.subtitle, new.facts, new.narrative, new.concepts, new.files_read, new.files_modified);
END;

CREATE TRIGGER IF NOT EXISTS obs_ad AFTER DELETE ON observations BEGIN
  INSERT INTO observations_fts(observations_fts, rowid, title, subtitle, facts, narrative, concepts, files_read, files_modified)
  VALUES ('delete', old.id, old.title, old.subtitle, old.facts, old.narrative, old.concepts, old.files_read, old.files_modified);
END;

CREATE TRIGGER IF NOT EXISTS obs_au AFTER UPDATE ON observations BEGIN
  INSERT INTO observations_fts(observations_fts, rowid, title, subtitle, facts, narrative, concepts, files_read, files_modified)
  VALUES ('delete', old.id, old.title, old.subtitle, old.facts, old.narrative, old.concepts, old.files_read, old.files_modified);
  INSERT INTO observations_fts(rowid, title, subtitle, facts, narrative, concepts, files_read, files_modified)
  VALUES (new.id, new.title, new.subtitle, new.facts, new.narrative, new.concepts, new.files_read, new.files_modified);
END;

CREATE TABLE IF NOT EXISTS summaries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id TEXT NOT NULL,
  project TEXT NOT NULL,
  request TEXT,
  investigated TEXT,
  learned TEXT,
  completed TEXT,
  next_steps TEXT,
  notes TEXT,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (session_id) REFERENCES sessions(id)
);

CREATE INDEX IF NOT EXISTS idx_sum_session ON summaries(session_id);
CREATE INDEX IF NOT EXISTS idx_sum_project_created ON summaries(project, created_at DESC);
`;

let _initPromise: Promise<DbHandle> | null = null;
let _db: DbHandle | null = null;

async function ensureDb(): Promise<DbHandle> {
  if (_db) return _db;
  if (_initPromise) return _initPromise;
  _initPromise = (async () => {
    try {
      mkdirSync(dirname(DB_PATH), { recursive: true });
    } catch {
      /* ignore */
    }
    const db = await openDatabase(DB_PATH);
    db.exec(SCHEMA);
    _db = db;
    return db;
  })();
  return _initPromise;
}

// Synchronous wrapper: assumes ensureDb has been called during activation.
// If called before init, we trigger it and throw — caller should ensure init.
function db(): DbHandle {
  if (!_db) {
    throw new Error("pi-mem-cc DB not initialized; call ensureDb() during activate()");
  }
  return _db;
}

export async function initStore(): Promise<void> {
  await ensureDb();
}

export function upsertSession(id: string, project: string, at: number): void {
  db()
    .prepare(
      `INSERT INTO sessions (id, project, started_at, last_seen_at)
       VALUES (?, ?, ?, ?)
       ON CONFLICT(id) DO UPDATE SET last_seen_at = excluded.last_seen_at`,
    )
    .run(id, project, at, at);
}

export function insertObservation(obs: Observation): number {
  const result = db()
    .prepare(
      `INSERT INTO observations
         (session_id, project, type, title, subtitle, facts, narrative, concepts, files_read, files_modified, source_tool_name, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    )
    .run(
      obs.sessionId,
      obs.project,
      obs.type,
      obs.title,
      obs.subtitle,
      JSON.stringify(obs.facts),
      obs.narrative,
      JSON.stringify(obs.concepts),
      JSON.stringify(obs.filesRead),
      JSON.stringify(obs.filesModified),
      obs.sourceToolName,
      obs.createdAt,
    );
  return Number(result.lastInsertRowid);
}

export function insertSummary(s: Summary): number {
  const result = db()
    .prepare(
      `INSERT INTO summaries
         (session_id, project, request, investigated, learned, completed, next_steps, notes, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    )
    .run(
      s.sessionId,
      s.project,
      s.request,
      s.investigated,
      s.learned,
      s.completed,
      s.nextSteps,
      s.notes,
      s.createdAt,
    );
  return Number(result.lastInsertRowid);
}

interface ObservationRow {
  id: number;
  session_id: string;
  project: string;
  type: string;
  title: string;
  subtitle: string | null;
  facts: string;
  narrative: string | null;
  concepts: string;
  files_read: string;
  files_modified: string;
  source_tool_name: string;
  created_at: number;
}

function rowToObservation(row: ObservationRow): FullObservation {
  return {
    id: row.id,
    sessionId: row.session_id,
    project: row.project,
    type: row.type as FullObservation["type"],
    title: row.title,
    subtitle: row.subtitle,
    facts: safeJsonArray(row.facts),
    narrative: row.narrative,
    concepts: safeJsonArray(row.concepts),
    filesRead: safeJsonArray(row.files_read),
    filesModified: safeJsonArray(row.files_modified),
    sourceToolName: row.source_tool_name,
    createdAt: row.created_at,
  };
}

function safeJsonArray(s: string): string[] {
  try {
    const v = JSON.parse(s);
    return Array.isArray(v) ? v : [];
  } catch {
    return [];
  }
}

export function getObservationsByIds(ids: number[]): FullObservation[] {
  if (ids.length === 0) return [];
  const placeholders = ids.map(() => "?").join(",");
  const rows = db()
    .prepare(`SELECT * FROM observations WHERE id IN (${placeholders})`)
    .all(...ids) as ObservationRow[];
  return rows.map(rowToObservation);
}

export function getRecentObservations(
  project: string | null,
  limit: number,
): FullObservation[] {
  if (project) {
    const rows = db()
      .prepare(
        `SELECT * FROM observations WHERE project = ?
         ORDER BY created_at DESC LIMIT ?`,
      )
      .all(project, limit) as ObservationRow[];
    return rows.map(rowToObservation);
  }
  const rows = db()
    .prepare(`SELECT * FROM observations ORDER BY created_at DESC LIMIT ?`)
    .all(limit) as ObservationRow[];
  return rows.map(rowToObservation);
}

export function getRecentSummaries(project: string | null, limit: number): Summary[] {
  interface SummaryRow {
    id: number;
    session_id: string;
    project: string;
    request: string | null;
    investigated: string | null;
    learned: string | null;
    completed: string | null;
    next_steps: string | null;
    notes: string | null;
    created_at: number;
  }
  const map = (r: SummaryRow): Summary => ({
    id: r.id,
    sessionId: r.session_id,
    project: r.project,
    request: r.request,
    investigated: r.investigated,
    learned: r.learned,
    completed: r.completed,
    nextSteps: r.next_steps,
    notes: r.notes,
    createdAt: r.created_at,
  });
  if (project) {
    const rows = db()
      .prepare(
        `SELECT * FROM summaries WHERE project = ?
         ORDER BY created_at DESC LIMIT ?`,
      )
      .all(project, limit) as SummaryRow[];
    return rows.map(map);
  }
  const rows = db()
    .prepare(`SELECT * FROM summaries ORDER BY created_at DESC LIMIT ?`)
    .all(limit) as SummaryRow[];
  return rows.map(map);
}

export function searchObservationsFts(
  query: string,
  project: string | null,
  limit: number,
): SearchHit[] {
  const tokens = query
    .split(/\s+/)
    .map((t) => t.trim())
    .filter((t) => t.length > 0)
    .map((t) => `"${t.replace(/"/g, '""')}"*`);
  if (tokens.length === 0) return [];
  const ftsQuery = tokens.join(" ");

  interface HitRow {
    id: number;
    type: string;
    title: string;
    subtitle: string | null;
    session_id: string;
    project: string;
    created_at: number;
    rank: number;
  }

  const projectFilter = project ? "AND o.project = ?" : "";
  const params: unknown[] = [ftsQuery];
  if (project) params.push(project);
  params.push(limit);

  const rows = db()
    .prepare(
      `SELECT o.id, o.type, o.title, o.subtitle, o.session_id, o.project, o.created_at, bm25(observations_fts) AS rank
       FROM observations_fts f
       JOIN observations o ON o.id = f.rowid
       WHERE observations_fts MATCH ? ${projectFilter}
       ORDER BY rank ASC
       LIMIT ?`,
    )
    .all(...params) as HitRow[];

  return rows.map((r) => ({
    id: r.id,
    type: r.type as SearchHit["type"],
    title: r.title,
    subtitle: r.subtitle,
    sessionId: r.session_id,
    project: r.project,
    createdAt: r.created_at,
    score: -r.rank,
    matchedVia: "fts" as const,
  }));
}

export function searchObservationsRecency(
  project: string | null,
  limit: number,
): SearchHit[] {
  const obs = getRecentObservations(project, limit);
  const now = Date.now();
  return obs.map((o) => ({
    id: o.id,
    type: o.type,
    title: o.title,
    subtitle: o.subtitle,
    sessionId: o.sessionId,
    project: o.project,
    createdAt: o.createdAt,
    score: 1 / (1 + (now - o.createdAt) / (1000 * 60 * 60 * 24)),
    matchedVia: "recency" as const,
  }));
}

export function getTimelineAround(
  observationId: number,
  windowMs: number,
): FullObservation[] {
  interface Row {
    created_at: number;
  }
  const target = db()
    .prepare(`SELECT created_at FROM observations WHERE id = ?`)
    .get(observationId) as Row | undefined;
  if (!target) return [];
  const lo = target.created_at - windowMs;
  const hi = target.created_at + windowMs;
  const rows = db()
    .prepare(
      `SELECT * FROM observations
       WHERE created_at BETWEEN ? AND ?
       ORDER BY created_at ASC`,
    )
    .all(lo, hi) as ObservationRow[];
  return rows.map(rowToObservation);
}

export function countObservations(): number {
  const row = db().prepare(`SELECT COUNT(*) AS c FROM observations`).get() as {
    c: number;
  };
  return row.c;
}

export function countSummaries(): number {
  const row = db().prepare(`SELECT COUNT(*) AS c FROM summaries`).get() as {
    c: number;
  };
  return row.c;
}