// Control Plane CORE — all ops logic lives here (the single core behind both surfaces).
// The MCP tools (engine/src/dewx-mcp.ts) and the SwiftUI Agents board both call THESE functions —
// no business logic in a surface (the "two equal surfaces, one core" rule). Pure: each fn takes a
// writable DatabaseSync + args and returns plain data. Truth = these tables (WAL) mirrored to PROJECT.md.

import type { DatabaseSync } from "node:sqlite";
import os from "node:os";
import fs from "node:fs";
import path from "node:path";

const HOME = process.env.HOME || os.homedir();
const now = () => new Date().toISOString();
const id = (p: string) => `${p}:${Date.now().toString(36)}:${Math.random().toString(36).slice(2, 8)}`;
function expand(dir: string): string { return dir.startsWith("~") ? path.join(HOME, dir.slice(1)) : dir; }
function defaultControlDir(slug: string): string { return path.join(HOME, "dewx", "state", "projects", slug); }

/// Idempotent — create the ops tables if absent (so a fresh DB self-heals; mirrors schema-ops-v1.sql).
export function ensureSchema(db: DatabaseSync): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS project (
      id TEXT PRIMARY KEY, slug TEXT UNIQUE NOT NULL, name TEXT NOT NULL, goal TEXT,
      status TEXT NOT NULL DEFAULT 'active', priority INTEGER DEFAULT 3, category TEXT NOT NULL DEFAULT '',
      control_dir TEXT NOT NULL,
      repo_path TEXT, exec_host TEXT NOT NULL DEFAULT 'mac', created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS tab (
      id TEXT PRIMARY KEY, name TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'worker',
      status TEXT NOT NULL DEFAULT 'idle', pid INTEGER, cwd TEXT, model TEXT, interval_sec INTEGER DEFAULT 600,
      heartbeat_at TEXT, current_project_id TEXT, current_task TEXT, created_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS project_tab (
      project_id TEXT NOT NULL, tab_id TEXT NOT NULL, active INTEGER NOT NULL DEFAULT 1,
      assigned_at TEXT NOT NULL, PRIMARY KEY (project_id, tab_id));
    CREATE TABLE IF NOT EXISTS "update" (
      id TEXT PRIMARY KEY, project_id TEXT NOT NULL, tab_id TEXT, ts TEXT NOT NULL, kind TEXT NOT NULL, body TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS message (
      id TEXT PRIMARY KEY, from_tab_id TEXT, to_tab_id TEXT, project_id TEXT, ts TEXT NOT NULL,
      body TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'pending');
    CREATE TABLE IF NOT EXISTS ask (
      id TEXT PRIMARY KEY, project_id TEXT, tab_id TEXT, ts TEXT NOT NULL, question TEXT NOT NULL,
      urgency TEXT NOT NULL DEFAULT 'normal', status TEXT NOT NULL DEFAULT 'open', answer TEXT,
      answered_by TEXT, answered_at TEXT);
    CREATE INDEX IF NOT EXISTS ix_update_project ON "update"(project_id, ts);
    CREATE INDEX IF NOT EXISTS ix_message_to ON message(to_tab_id, status);
    CREATE INDEX IF NOT EXISTS ix_ask_status ON ask(status, ts);
  `);
  try { db.exec(`ALTER TABLE project ADD COLUMN category TEXT NOT NULL DEFAULT '';`); } catch { /* already migrated */ }
}

/// Resolve a project by slug; auto-create a minimal one if missing (P1 enriches goal/repo/host).
export function ensureProject(db: DatabaseSync, slug: string, name?: string): any {
  const row = db.prepare(`SELECT * FROM project WHERE slug=?`).get(slug) as any;
  if (row) return row;
  const pid = id("pj");
  const dir = defaultControlDir(slug);
  db.prepare(`INSERT INTO project(id, slug, name, control_dir, status, priority, exec_host, created_at, updated_at)
              VALUES(?,?,?,?, 'active', 3, 'mac', ?, ?)`)
    .run(pid, slug, name || slug, dir, now(), now());
  return db.prepare(`SELECT * FROM project WHERE id=?`).get(pid);
}

/// Create or update a project (by slug) AND scaffold/refresh its PROJECT.md header — preserving the
/// append-only ## Log. The brain's project registry; idempotent so the seed can re-run safely.
export function upsertProject(db: DatabaseSync, p: { slug: string; name: string; goal?: string; status?: string; priority?: number; category?: string; control_dir?: string; repo_path?: string; exec_host?: string; focus?: string; blockers?: string }) {
  const dir = p.control_dir || defaultControlDir(p.slug);
  const existing = db.prepare(`SELECT id FROM project WHERE slug=?`).get(p.slug) as any;
  if (existing) {
    db.prepare(`UPDATE project SET name=?, goal=COALESCE(?,goal), status=COALESCE(?,status),
                priority=COALESCE(?,priority), category=COALESCE(?,category), control_dir=?, repo_path=COALESCE(?,repo_path),
                exec_host=COALESCE(?,exec_host), updated_at=? WHERE slug=?`)
      .run(p.name, p.goal ?? null, p.status ?? null, p.priority ?? null, p.category ?? null, dir, p.repo_path ?? null, p.exec_host ?? null, now(), p.slug);
  } else {
    db.prepare(`INSERT INTO project(id, slug, name, goal, status, priority, category, control_dir, repo_path, exec_host, created_at, updated_at)
                VALUES(?,?,?,?,?,?,?,?,?,?,?,?)`)
      .run(id("pj"), p.slug, p.name, p.goal ?? null, p.status || "active", p.priority ?? 3, p.category || "", dir, p.repo_path ?? null, p.exec_host || "mac", now(), now());
  }
  const proj = db.prepare(`SELECT * FROM project WHERE slug=?`).get(p.slug) as any;
  writeProjectHeader(proj, p.focus, p.blockers);
  return proj;
}

/// (Re)write PROJECT.md's header block, keeping the existing ## Log intact.
function writeProjectHeader(proj: any, focus?: string, blockers?: string) {
  try {
    const dir = expand(proj.control_dir || defaultControlDir(proj.slug));
    fs.mkdirSync(dir, { recursive: true });
    const md = path.join(dir, "PROJECT.md");
    let log = "";
    if (fs.existsSync(md)) {
      const cur = fs.readFileSync(md, "utf8");
      const i = cur.indexOf("## Log");
      if (i >= 0) log = cur.slice(i + "## Log".length).replace(/^\n/, "");
    }
    const header =
      `# ${proj.name} (${proj.slug})\n\n` +
      `- **Goal:** ${proj.goal || "(set me)"}\n` +
      `- **Status:** ${proj.status}\n` +
      `- **Category:** ${proj.category || "—"}\n` +
      `- **Exec host:** ${proj.exec_host}\n` +
      `- **Repo:** ${proj.repo_path || "—"}\n` +
      `- **Focus:** ${focus || "—"}\n` +
      `- **Blockers:** ${blockers || "none"}\n\n## Log\n`;
    fs.writeFileSync(md, header + log);
  } catch { /* best-effort mirror; DB is source of truth */ }
}

// ── Worker-facing ─────────────────────────────────────────────────────────

export function opsRegister(db: DatabaseSync, a: { name: string; project_slug: string; pid?: number; cwd?: string; interval?: number; role?: string; model?: string }) {
  const proj = ensureProject(db, a.project_slug);
  const tabId = id("tab");
  db.prepare(`INSERT INTO tab(id, name, role, status, pid, cwd, model, interval_sec, heartbeat_at, current_project_id, created_at)
              VALUES(?,?,?, 'running', ?,?,?,?,?,?, ?)`)
    .run(tabId, a.name, a.role || "worker", a.pid ?? null, a.cwd ?? null, a.model ?? null, a.interval ?? 600, now(), proj.id, now());
  db.prepare(`INSERT OR REPLACE INTO project_tab(project_id, tab_id, active, assigned_at) VALUES(?,?,1,?)`)
    .run(proj.id, tabId, now());
  return { tab_id: tabId, project_id: proj.id, project_slug: proj.slug };
}

export function opsHeartbeat(db: DatabaseSync, a: { tab_id: string; status?: string; current_task?: string }) {
  const r = db.prepare(`UPDATE tab SET heartbeat_at=?, status=COALESCE(?, status), current_task=COALESCE(?, current_task) WHERE id=?`)
    .run(now(), a.status ?? null, a.current_task ?? null, a.tab_id);
  return { ok: (r.changes ?? 0) > 0 };
}

export function opsReport(db: DatabaseSync, a: { tab_id?: string; project_slug: string; kind: string; body: string }) {
  const proj = ensureProject(db, a.project_slug);
  const uid = id("up");
  db.prepare(`INSERT INTO "update"(id, project_id, tab_id, ts, kind, body) VALUES(?,?,?,?,?,?)`)
    .run(uid, proj.id, a.tab_id ?? null, now(), a.kind, a.body);
  db.prepare(`UPDATE project SET updated_at=? WHERE id=?`).run(now(), proj.id);
  appendProjectMd(proj, a.kind, a.body, a.tab_id);   // two-store sync: DB row + PROJECT.md
  return { update_id: uid, project_slug: proj.slug };
}

/// Mirror the update into the project's PROJECT.md (creates the folder/file if missing).
function appendProjectMd(proj: any, kind: string, body: string, tabId?: string) {
  try {
    const dir = expand(proj.control_dir || defaultControlDir(proj.slug));
    fs.mkdirSync(dir, { recursive: true });
    const md = path.join(dir, "PROJECT.md");
    if (!fs.existsSync(md)) {
      fs.writeFileSync(md, `# ${proj.name} (${proj.slug})\n\n- **Goal:** ${proj.goal || "(set me)"}\n- **Status:** ${proj.status}\n\n## Log\n`);
    }
    fs.appendFileSync(md, `- ${now()} · **${kind}**${tabId ? ` · ${tabId}` : ""}: ${body}\n`);
  } catch { /* folder mirror is best-effort; DB row is the source of truth */ }
}

export function opsInbox(db: DatabaseSync, a: { tab_id: string }) {
  const tab = db.prepare(`SELECT current_project_id FROM tab WHERE id=?`).get(a.tab_id) as any;
  const projId = tab?.current_project_id ?? "";
  // Targeted to me + broadcasts to my project, still actionable.
  const rows = db.prepare(`
    SELECT id AS message_id, from_tab_id, to_tab_id, project_id, ts, body, status FROM message
    WHERE status IN ('pending','delivered')
      AND (to_tab_id=? OR (to_tab_id IS NULL AND project_id=?))
    ORDER BY ts ASC`).all(a.tab_id, projId) as any[];
  // Mark them delivered (seen once); ack closes them.
  for (const m of rows) db.prepare(`UPDATE message SET status='delivered' WHERE id=? AND status='pending'`).run(m.message_id);
  return rows;
}

export function opsAck(db: DatabaseSync, a: { message_id: string; result?: string }) {
  const r = db.prepare(`UPDATE message SET status='done', body = body || ? WHERE id=?`)
    .run(a.result ? `\n[ack: ${a.result}]` : "\n[ack]", a.message_id);
  return { ok: (r.changes ?? 0) > 0 };
}

export function opsAsk(db: DatabaseSync, a: { tab_id?: string; project_slug?: string; question: string; urgency?: string }) {
  const proj = a.project_slug ? ensureProject(db, a.project_slug) : null;
  const aid = id("ask");
  db.prepare(`INSERT INTO ask(id, project_id, tab_id, ts, question, urgency, status) VALUES(?,?,?,?,?,?, 'open')`)
    .run(aid, proj?.id ?? null, a.tab_id ?? null, now(), a.question, a.urgency || "normal");
  return { ask_id: aid };
}

// ── Brain-facing ──────────────────────────────────────────────────────────

/// The cockpit: every project + its tabs (with live/down liveness) + open-ask count.
export function opsBoard(db: DatabaseSync) {
  const nowMs = Date.now();
  const projects = db.prepare(`SELECT * FROM project ORDER BY priority ASC, updated_at DESC`).all() as any[];
  return projects.map((p) => {
    const tabs = (db.prepare(`SELECT id, name, role, status, pid, cwd, interval_sec, heartbeat_at, current_task FROM tab
                              WHERE current_project_id=? ORDER BY created_at`).all(p.id) as any[])
      .map((t) => {
        const hb = t.heartbeat_at ? Date.parse(t.heartbeat_at) : 0;
        const staleSec = hb ? Math.round((nowMs - hb) / 1000) : null;
        const down = !hb || (nowMs - hb) > 2 * (t.interval_sec || 600) * 1000;
        return { ...t, stale_sec: staleSec, live: !down, liveness: down ? "down" : "live" };
      });
    const openAsks = (db.prepare(`SELECT COUNT(*) AS n FROM ask WHERE project_id=? AND status='open'`).get(p.id) as any)?.n ?? 0;
    return { slug: p.slug, name: p.name, status: p.status, priority: p.priority, exec_host: p.exec_host,
             updated_at: p.updated_at, tabs, open_asks: openAsks };
  });
}

/// Deep-dive one project: meta + recent updates + tabs + blockers (blocked updates + open asks).
export function opsProject(db: DatabaseSync, a: { slug: string }) {
  const p = db.prepare(`SELECT * FROM project WHERE slug=?`).get(a.slug) as any;
  if (!p) return { error: `no project '${a.slug}'` };
  const updates = db.prepare(`SELECT id, tab_id, ts, kind, body FROM "update" WHERE project_id=? ORDER BY ts DESC LIMIT 50`).all(p.id);
  const tabs = db.prepare(`SELECT id, name, role, status, heartbeat_at, current_task FROM tab WHERE current_project_id=?`).all(p.id);
  const blockers = db.prepare(`SELECT id, ts, kind, body FROM "update" WHERE project_id=? AND kind IN ('blocked','need') ORDER BY ts DESC LIMIT 20`).all(p.id);
  const asks = db.prepare(`SELECT id, ts, question, urgency, status FROM ask WHERE project_id=? AND status='open' ORDER BY ts DESC`).all(p.id);
  return { project: p, updates, tabs, blockers, open_asks: asks };
}

/// Send an order. target = a tab_id (direct) OR a project slug (broadcast to that project's tabs).
export function opsOrder(db: DatabaseSync, a: { target: string; body: string; from_tab_id?: string }) {
  const tab = db.prepare(`SELECT id FROM tab WHERE id=?`).get(a.target) as any;
  const mid = id("msg");
  if (tab) {
    const t = db.prepare(`SELECT current_project_id FROM tab WHERE id=?`).get(a.target) as any;
    db.prepare(`INSERT INTO message(id, from_tab_id, to_tab_id, project_id, ts, body, status) VALUES(?,?,?,?,?,?, 'pending')`)
      .run(mid, a.from_tab_id ?? null, a.target, t?.current_project_id ?? null, now(), a.body);
    return { message_id: mid, delivered_to: "tab", target: a.target };
  }
  const proj = db.prepare(`SELECT id, slug FROM project WHERE slug=?`).get(a.target) as any;
  if (!proj) return { error: `target '${a.target}' is neither a tab_id nor a project slug` };
  db.prepare(`INSERT INTO message(id, from_tab_id, to_tab_id, project_id, ts, body, status) VALUES(?,?,NULL,?,?,?, 'pending')`)
    .run(mid, a.from_tab_id ?? null, proj.id, now(), a.body);
  return { message_id: mid, delivered_to: "project", target: proj.slug };
}

export function opsAnswer(db: DatabaseSync, a: { ask_id: string; answer: string; answered_by?: string }) {
  const r = db.prepare(`UPDATE ask SET status='answered', answer=?, answered_by=?, answered_at=? WHERE id=?`)
    .run(a.answer, a.answered_by || "brain", now(), a.ask_id);
  return { ok: (r.changes ?? 0) > 0 };
}

/// Restart manifest: every project + its tabs + cwd + a bootstrap preamble to relaunch each tab.
export function opsResume(db: DatabaseSync) {
  const projects = db.prepare(`SELECT id, slug, name, control_dir, repo_path, exec_host, status FROM project ORDER BY priority ASC`).all() as any[];
  const tabs = db.prepare(`SELECT id, name, role, cwd, model, interval_sec, current_project_id, current_task FROM tab`).all() as any[];
  const byProj = (pid: string) => tabs.filter((t) => t.current_project_id === pid);
  return projects.map((p) => ({
    slug: p.slug, name: p.name, control_dir: expand(p.control_dir), repo_path: p.repo_path, exec_host: p.exec_host, status: p.status,
    tabs: byProj(p.id).map((t) => ({
      id: t.id, name: t.name, role: t.role, cwd: t.cwd, model: t.model, interval_sec: t.interval_sec, current_task: t.current_task,
      preamble: t.role === "brain"
        ? `You are the orchestrator. Call ops_board(), summarize, await Roki.`
        : `You are the worker for project "${p.slug}". Standard loop: ops_inbox → do ONE unit → ops_report → ops_heartbeat. Current state is in ${expand(p.control_dir)}/PROJECT.md.`,
    })),
  }));
}
