import type { DatabaseSync } from "node:sqlite";

const now = () => new Date().toISOString();
const id = (prefix: string) => `${prefix}:${Date.now().toString(36)}:${Math.random().toString(36).slice(2, 8)}`;

type ContextRef = {
  context_id?: string;
  module?: string;
  context_slug?: string;
};

function normModule(module: string): string {
  const m = module.trim().toLowerCase();
  if (!m) throw new Error("module is required");
  return m;
}

function normSlug(slug: string): string {
  const s = slug.trim().toLowerCase().replace(/[^a-z0-9._-]+/g, "-").replace(/^-+|-+$/g, "");
  if (!s) throw new Error("slug is required");
  return s;
}

function text(v: unknown): string {
  return typeof v === "string" ? v : "";
}

function metadataJson(v: unknown): string {
  if (v == null) return "{}";
  if (typeof v === "string") return v.trim() ? v : "{}";
  return JSON.stringify(v);
}

export function ensureSchema(db: DatabaseSync): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS work_context (
      id TEXT PRIMARY KEY,
      module TEXT NOT NULL,
      kind TEXT NOT NULL DEFAULT 'workspace',
      slug TEXT NOT NULL,
      title TEXT NOT NULL,
      subtitle TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'active',
      owner TEXT NOT NULL DEFAULT '',
      external_ref TEXT NOT NULL DEFAULT '',
      metadata_json TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(module, slug)
    );
    CREATE TABLE IF NOT EXISTS work_session (
      id TEXT PRIMARY KEY,
      context_id TEXT REFERENCES work_context(id),
      module TEXT NOT NULL,
      purpose TEXT NOT NULL DEFAULT '',
      owner TEXT NOT NULL DEFAULT '',
      tool TEXT NOT NULL DEFAULT '',
      status TEXT NOT NULL DEFAULT 'idle',
      pid INTEGER,
      cwd TEXT NOT NULL DEFAULT '',
      endpoint TEXT NOT NULL DEFAULT '',
      current_item_id TEXT,
      heartbeat_at TEXT NOT NULL DEFAULT '',
      metadata_json TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS work_item (
      id TEXT PRIMARY KEY,
      context_id TEXT REFERENCES work_context(id),
      module TEXT NOT NULL,
      kind TEXT NOT NULL DEFAULT 'task',
      title TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'open',
      priority TEXT NOT NULL DEFAULT 'normal',
      owner TEXT NOT NULL DEFAULT '',
      session_id TEXT REFERENCES work_session(id),
      source TEXT NOT NULL DEFAULT 'agent',
      summary TEXT NOT NULL DEFAULT '',
      due_at TEXT NOT NULL DEFAULT '',
      metadata_json TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS work_memory (
      id TEXT PRIMARY KEY,
      context_id TEXT REFERENCES work_context(id),
      module TEXT NOT NULL,
      kind TEXT NOT NULL DEFAULT 'fact',
      body TEXT NOT NULL,
      source TEXT NOT NULL DEFAULT 'agent',
      confidence TEXT NOT NULL DEFAULT 'medium',
      status TEXT NOT NULL DEFAULT 'proposed',
      owner TEXT NOT NULL DEFAULT '',
      verified_at TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS work_decision (
      id TEXT PRIMARY KEY,
      context_id TEXT REFERENCES work_context(id),
      module TEXT NOT NULL,
      title TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'proposed',
      proposal TEXT NOT NULL DEFAULT '',
      rationale TEXT NOT NULL DEFAULT '',
      risk TEXT NOT NULL DEFAULT 'normal',
      decided_by TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS work_evidence (
      id TEXT PRIMARY KEY,
      context_id TEXT REFERENCES work_context(id),
      module TEXT NOT NULL,
      item_id TEXT REFERENCES work_item(id),
      session_id TEXT REFERENCES work_session(id),
      kind TEXT NOT NULL DEFAULT 'note',
      title TEXT NOT NULL,
      source TEXT NOT NULL DEFAULT '',
      uri TEXT NOT NULL DEFAULT '',
      summary TEXT NOT NULL DEFAULT '',
      metadata_json TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL
    );
    CREATE TABLE IF NOT EXISTS work_agent (
      id TEXT PRIMARY KEY,
      context_id TEXT REFERENCES work_context(id),
      module TEXT NOT NULL,
      name TEXT NOT NULL,
      tool TEXT NOT NULL DEFAULT '',
      role TEXT NOT NULL DEFAULT 'worker',
      status TEXT NOT NULL DEFAULT 'idle',
      current_task TEXT NOT NULL DEFAULT '',
      session_id TEXT REFERENCES work_session(id),
      heartbeat_at TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL,
      updated_at TEXT NOT NULL,
      UNIQUE(module, context_id, name, tool)
    );
    CREATE TABLE IF NOT EXISTS work_message (
      id TEXT PRIMARY KEY,
      context_id TEXT REFERENCES work_context(id),
      module TEXT NOT NULL,
      session_id TEXT REFERENCES work_session(id),
      agent_id TEXT REFERENCES work_agent(id),
      speaker TEXT NOT NULL DEFAULT '',
      role TEXT NOT NULL DEFAULT 'agent',
      kind TEXT NOT NULL DEFAULT 'message',
      body TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'posted',
      metadata_json TEXT NOT NULL DEFAULT '{}',
      created_at TEXT NOT NULL
    );
    CREATE INDEX IF NOT EXISTS ix_work_context_module ON work_context(module, kind, status, updated_at);
    CREATE INDEX IF NOT EXISTS ix_work_session_context ON work_session(module, context_id, status, heartbeat_at);
    CREATE INDEX IF NOT EXISTS ix_work_item_context ON work_item(module, context_id, kind, status, updated_at);
    CREATE INDEX IF NOT EXISTS ix_work_memory_context ON work_memory(module, context_id, status, updated_at);
    CREATE INDEX IF NOT EXISTS ix_work_decision_context ON work_decision(module, context_id, status, updated_at);
    CREATE INDEX IF NOT EXISTS ix_work_evidence_context ON work_evidence(module, context_id, created_at);
    CREATE INDEX IF NOT EXISTS ix_work_agent_context ON work_agent(module, context_id, status, updated_at);
    CREATE INDEX IF NOT EXISTS ix_work_message_context ON work_message(module, context_id, created_at);
  `);
}

function contextId(db: DatabaseSync, ref: ContextRef): string | null {
  ensureSchema(db);
  if (ref.context_id) {
    const row = db.prepare(`SELECT id FROM work_context WHERE id=?`).get(ref.context_id) as any;
    if (!row?.id) throw new Error(`work_context '${ref.context_id}' not found`);
    return row.id;
  }
  if (ref.module && ref.context_slug) {
    const row = db.prepare(`SELECT id FROM work_context WHERE module=? AND slug=?`)
      .get(normModule(ref.module), normSlug(ref.context_slug)) as any;
    if (!row?.id) throw new Error(`work_context '${ref.module}/${ref.context_slug}' not found`);
    return row.id;
  }
  return null;
}

export function workContextUpsert(db: DatabaseSync, a: {
  module: string;
  slug: string;
  kind?: string;
  title: string;
  subtitle?: string;
  status?: string;
  owner?: string;
  external_ref?: string;
  metadata?: unknown;
}) {
  ensureSchema(db);
  const module = normModule(a.module);
  const slug = normSlug(a.slug);
  const cid = `ctx:${module}:${slug}`;
  const ts = now();
  db.prepare(`
    INSERT INTO work_context(id, module, kind, slug, title, subtitle, status, owner, external_ref, metadata_json, created_at, updated_at)
    VALUES(?,?,?,?,?,?,?,?,?,?,?,?)
    ON CONFLICT(module, slug) DO UPDATE SET
      kind=excluded.kind,
      title=excluded.title,
      subtitle=excluded.subtitle,
      status=excluded.status,
      owner=excluded.owner,
      external_ref=excluded.external_ref,
      metadata_json=excluded.metadata_json,
      updated_at=excluded.updated_at`)
    .run(cid, module, a.kind ?? "workspace", slug, a.title, a.subtitle ?? "", a.status ?? "active",
      a.owner ?? "", a.external_ref ?? "", metadataJson(a.metadata), ts, ts);
  return db.prepare(`SELECT * FROM work_context WHERE module=? AND slug=?`).get(module, slug);
}

export function workContextList(db: DatabaseSync, a: { module?: string; kind?: string; status?: string; limit?: number } = {}) {
  ensureSchema(db);
  const module = a.module ? normModule(a.module) : "";
  const rows = db.prepare(`
    SELECT c.*,
      COALESCE((SELECT COUNT(*) FROM work_item i WHERE i.context_id=c.id AND i.status!='done'),0) AS open_items,
      COALESCE((SELECT COUNT(*) FROM work_session s WHERE s.context_id=c.id AND s.status IN ('running','live')),0) AS live_sessions,
      COALESCE((SELECT COUNT(*) FROM work_agent ag WHERE ag.context_id=c.id AND ag.status IN ('running','live')),0) AS live_agents
    FROM work_context c
    WHERE (?='' OR c.module=?) AND (?='' OR c.kind=?) AND (?='' OR c.status=?)
    ORDER BY c.updated_at DESC
    LIMIT ?`)
    .all(module, module, a.kind ?? "", a.kind ?? "", a.status ?? "", a.status ?? "", a.limit ?? 100) as any[];
  return rows;
}

export function workContextGet(db: DatabaseSync, a: ContextRef) {
  ensureSchema(db);
  const cid = contextId(db, a);
  if (!cid) throw new Error("context_id or module+context_slug is required");
  const context = db.prepare(`SELECT * FROM work_context WHERE id=?`).get(cid) as any;
  if (!context) return null;
  const recent_items = workItemList(db, { context_id: cid, limit: 20 });
  const sessions = workSessionList(db, { context_id: cid, limit: 20 });
  const agents = workAgentList(db, { context_id: cid, limit: 20 });
  const evidence = workEvidenceList(db, { context_id: cid, limit: 20 });
  const decisions = workDecisionList(db, { context_id: cid, limit: 20 });
  const memory = workMemoryList(db, { context_id: cid, limit: 20 });
  const messages = workMessageList(db, { context_id: cid, limit: 50 });
  return { context, recent_items, sessions, agents, evidence, decisions, memory, messages };
}

export function workSessionRegister(db: DatabaseSync, a: ContextRef & {
  session_id?: string;
  module: string;
  purpose: string;
  owner?: string;
  tool: string;
  status?: string;
  pid?: number;
  cwd?: string;
  endpoint?: string;
  current_item_id?: string;
  metadata?: unknown;
}) {
  ensureSchema(db);
  const module = normModule(a.module);
  const cid = contextId(db, a);
  const sid = a.session_id || id("sess");
  const ts = now();
  db.prepare(`
    INSERT INTO work_session(id, context_id, module, purpose, owner, tool, status, pid, cwd, endpoint, current_item_id, heartbeat_at, metadata_json, created_at, updated_at)
    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    ON CONFLICT(id) DO UPDATE SET
      context_id=excluded.context_id,
      module=excluded.module,
      purpose=excluded.purpose,
      owner=excluded.owner,
      tool=excluded.tool,
      status=excluded.status,
      pid=excluded.pid,
      cwd=excluded.cwd,
      endpoint=excluded.endpoint,
      current_item_id=excluded.current_item_id,
      heartbeat_at=excluded.heartbeat_at,
      metadata_json=excluded.metadata_json,
      updated_at=excluded.updated_at`)
    .run(sid, cid, module, a.purpose, a.owner ?? "", a.tool, a.status ?? "running", a.pid ?? null,
      a.cwd ?? "", a.endpoint ?? "", a.current_item_id ?? null, ts, metadataJson(a.metadata), ts, ts);
  return db.prepare(`SELECT * FROM work_session WHERE id=?`).get(sid);
}

export function workSessionHeartbeat(db: DatabaseSync, a: {
  session_id: string;
  status?: string;
  purpose?: string;
  current_item_id?: string;
  metadata?: unknown;
}) {
  ensureSchema(db);
  const ts = now();
  const current = db.prepare(`SELECT metadata_json FROM work_session WHERE id=?`).get(a.session_id) as any;
  const r = db.prepare(`
    UPDATE work_session
    SET status=COALESCE(?, status),
        purpose=COALESCE(?, purpose),
        current_item_id=COALESCE(?, current_item_id),
        heartbeat_at=?,
        metadata_json=?,
        updated_at=?
    WHERE id=?`)
    .run(a.status ?? null, a.purpose ?? null, a.current_item_id ?? null, ts,
      a.metadata === undefined ? (current?.metadata_json ?? "{}") : metadataJson(a.metadata), ts, a.session_id);
  return { ok: (r.changes ?? 0) > 0, heartbeat_at: ts };
}

export function workSessionList(db: DatabaseSync, a: ContextRef & { module?: string; status?: string; owner?: string; limit?: number } = {}) {
  ensureSchema(db);
  const cid = contextId(db, a);
  const module = a.module ? normModule(a.module) : "";
  return db.prepare(`
    SELECT s.*, COALESCE(c.slug,'') AS context_slug, COALESCE(c.title,'') AS context_title
    FROM work_session s
    LEFT JOIN work_context c ON c.id=s.context_id
    WHERE (? IS NULL OR s.context_id=?)
      AND (?='' OR s.module=?)
      AND (?='' OR s.status=?)
      AND (?='' OR s.owner=?)
    ORDER BY s.updated_at DESC
    LIMIT ?`)
    .all(cid, cid, module, module, a.status ?? "", a.status ?? "", a.owner ?? "", a.owner ?? "", a.limit ?? 50);
}

export function workItemCreate(db: DatabaseSync, a: ContextRef & {
  module: string;
  kind?: string;
  title: string;
  status?: string;
  priority?: string;
  owner?: string;
  session_id?: string;
  source?: string;
  summary?: string;
  due_at?: string;
  metadata?: unknown;
}) {
  ensureSchema(db);
  const module = normModule(a.module);
  const cid = contextId(db, a);
  const wid = id(`work:${a.kind ?? "task"}`);
  const ts = now();
  db.prepare(`
    INSERT INTO work_item(id, context_id, module, kind, title, status, priority, owner, session_id, source, summary, due_at, metadata_json, created_at, updated_at)
    VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`)
    .run(wid, cid, module, a.kind ?? "task", a.title, a.status ?? "open", a.priority ?? "normal",
      a.owner ?? "", a.session_id ?? null, a.source ?? "agent", a.summary ?? "", a.due_at ?? "",
      metadataJson(a.metadata), ts, ts);
  return db.prepare(`SELECT * FROM work_item WHERE id=?`).get(wid);
}

export function workItemUpdate(db: DatabaseSync, a: ContextRef & {
  item_id: string;
  module?: string;
  kind?: string;
  title?: string;
  status?: string;
  priority?: string;
  owner?: string;
  session_id?: string;
  summary?: string;
  due_at?: string;
  metadata?: unknown;
}) {
  ensureSchema(db);
  const current = db.prepare(`SELECT * FROM work_item WHERE id=?`).get(a.item_id) as any;
  if (!current) return { ok: false, error: `work_item '${a.item_id}' not found` };
  const cid = a.context_id || a.context_slug ? contextId(db, a) : current.context_id;
  const module = a.module ? normModule(a.module) : current.module;
  const ts = now();
  const r = db.prepare(`
    UPDATE work_item
    SET context_id=?, module=?, kind=COALESCE(?, kind), title=COALESCE(?, title),
        status=COALESCE(?, status), priority=COALESCE(?, priority), owner=COALESCE(?, owner),
        session_id=COALESCE(?, session_id), summary=COALESCE(?, summary), due_at=COALESCE(?, due_at),
        metadata_json=?, updated_at=?
    WHERE id=?`)
    .run(cid, module, a.kind ?? null, a.title ?? null, a.status ?? null, a.priority ?? null, a.owner ?? null,
      a.session_id ?? null, a.summary ?? null, a.due_at ?? null,
      a.metadata === undefined ? current.metadata_json : metadataJson(a.metadata), ts, a.item_id);
  return { ok: (r.changes ?? 0) > 0, item: db.prepare(`SELECT * FROM work_item WHERE id=?`).get(a.item_id) };
}

export function workItemList(db: DatabaseSync, a: ContextRef & {
  module?: string;
  kind?: string;
  status?: string;
  owner?: string;
  limit?: number;
} = {}) {
  ensureSchema(db);
  const cid = contextId(db, a);
  const module = a.module ? normModule(a.module) : "";
  return db.prepare(`
    SELECT i.*, COALESCE(c.slug,'') AS context_slug, COALESCE(c.title,'') AS context_title
    FROM work_item i
    LEFT JOIN work_context c ON c.id=i.context_id
    WHERE (? IS NULL OR i.context_id=?)
      AND (?='' OR i.module=?)
      AND (?='' OR i.kind=?)
      AND (?='' OR i.status=?)
      AND (?='' OR i.owner=?)
    ORDER BY CASE i.priority WHEN 'high' THEN 0 WHEN 'normal' THEN 1 ELSE 2 END, i.updated_at DESC
    LIMIT ?`)
    .all(cid, cid, module, module, a.kind ?? "", a.kind ?? "", a.status ?? "", a.status ?? "",
      a.owner ?? "", a.owner ?? "", a.limit ?? 50);
}

export function workMemoryPropose(db: DatabaseSync, a: ContextRef & {
  module: string;
  kind?: string;
  body: string;
  source?: string;
  confidence?: string;
  owner?: string;
}) {
  ensureSchema(db);
  const module = normModule(a.module);
  const cid = contextId(db, a);
  const mid = id("mem");
  const ts = now();
  db.prepare(`
    INSERT INTO work_memory(id, context_id, module, kind, body, source, confidence, status, owner, verified_at, created_at, updated_at)
    VALUES(?,?,?,?,?,?,?, 'proposed', ?, '', ?, ?)`)
    .run(mid, cid, module, a.kind ?? "fact", a.body, a.source ?? "agent", a.confidence ?? "medium", a.owner ?? "", ts, ts);
  return db.prepare(`SELECT * FROM work_memory WHERE id=?`).get(mid);
}

export function workMemoryApprove(db: DatabaseSync, a: { memory_id: string; owner?: string; status?: string }) {
  ensureSchema(db);
  const status = a.status ?? "approved";
  if (!["approved", "rejected"].includes(status)) throw new Error("status must be approved or rejected");
  const ts = now();
  const r = db.prepare(`UPDATE work_memory SET status=?, owner=COALESCE(?, owner), verified_at=?, updated_at=? WHERE id=?`)
    .run(status, a.owner ?? null, ts, ts, a.memory_id);
  return { ok: (r.changes ?? 0) > 0, status };
}

export function workMemoryList(db: DatabaseSync, a: ContextRef & { module?: string; status?: string; limit?: number } = {}) {
  ensureSchema(db);
  const cid = contextId(db, a);
  const module = a.module ? normModule(a.module) : "";
  return db.prepare(`
    SELECT m.*, COALESCE(c.slug,'') AS context_slug, COALESCE(c.title,'') AS context_title
    FROM work_memory m
    LEFT JOIN work_context c ON c.id=m.context_id
    WHERE (? IS NULL OR m.context_id=?) AND (?='' OR m.module=?) AND (?='' OR m.status=?)
    ORDER BY m.updated_at DESC LIMIT ?`)
    .all(cid, cid, module, module, a.status ?? "", a.status ?? "", a.limit ?? 50);
}

export function workDecisionPropose(db: DatabaseSync, a: ContextRef & {
  module: string;
  title: string;
  proposal?: string;
  rationale?: string;
  risk?: string;
}) {
  ensureSchema(db);
  const module = normModule(a.module);
  const cid = contextId(db, a);
  const did = id("dec");
  const ts = now();
  db.prepare(`
    INSERT INTO work_decision(id, context_id, module, title, status, proposal, rationale, risk, decided_by, created_at, updated_at)
    VALUES(?,?,?,?, 'proposed', ?, ?, ?, '', ?, ?)`)
    .run(did, cid, module, a.title, a.proposal ?? "", a.rationale ?? "", a.risk ?? "normal", ts, ts);
  return db.prepare(`SELECT * FROM work_decision WHERE id=?`).get(did);
}

export function workDecisionApprove(db: DatabaseSync, a: { decision_id: string; decided_by: string; status?: string; rationale?: string }) {
  ensureSchema(db);
  const status = a.status ?? "approved";
  if (!["approved", "rejected"].includes(status)) throw new Error("status must be approved or rejected");
  const ts = now();
  const r = db.prepare(`
    UPDATE work_decision
    SET status=?, decided_by=?, rationale=CASE WHEN ?='' THEN rationale ELSE ? END, updated_at=?
    WHERE id=?`)
    .run(status, a.decided_by, a.rationale ?? "", a.rationale ?? "", ts, a.decision_id);
  return { ok: (r.changes ?? 0) > 0, status };
}

export function workDecisionList(db: DatabaseSync, a: ContextRef & { module?: string; status?: string; limit?: number } = {}) {
  ensureSchema(db);
  const cid = contextId(db, a);
  const module = a.module ? normModule(a.module) : "";
  return db.prepare(`
    SELECT d.*, COALESCE(c.slug,'') AS context_slug, COALESCE(c.title,'') AS context_title
    FROM work_decision d
    LEFT JOIN work_context c ON c.id=d.context_id
    WHERE (? IS NULL OR d.context_id=?) AND (?='' OR d.module=?) AND (?='' OR d.status=?)
    ORDER BY d.updated_at DESC LIMIT ?`)
    .all(cid, cid, module, module, a.status ?? "", a.status ?? "", a.limit ?? 50);
}

export function workEvidenceAttach(db: DatabaseSync, a: ContextRef & {
  module: string;
  item_id?: string;
  session_id?: string;
  kind?: string;
  title: string;
  source?: string;
  uri?: string;
  summary?: string;
  metadata?: unknown;
}) {
  ensureSchema(db);
  const module = normModule(a.module);
  const cid = contextId(db, a);
  const eid = id("ev");
  const ts = now();
  db.prepare(`
    INSERT INTO work_evidence(id, context_id, module, item_id, session_id, kind, title, source, uri, summary, metadata_json, created_at)
    VALUES(?,?,?,?,?,?,?,?,?,?,?,?)`)
    .run(eid, cid, module, a.item_id ?? null, a.session_id ?? null, a.kind ?? "note", a.title,
      a.source ?? "", a.uri ?? "", a.summary ?? "", metadataJson(a.metadata), ts);
  return db.prepare(`SELECT * FROM work_evidence WHERE id=?`).get(eid);
}

export function workEvidenceList(db: DatabaseSync, a: ContextRef & {
  module?: string;
  item_id?: string;
  session_id?: string;
  kind?: string;
  limit?: number;
} = {}) {
  ensureSchema(db);
  const cid = contextId(db, a);
  const module = a.module ? normModule(a.module) : "";
  return db.prepare(`
    SELECT e.*, COALESCE(c.slug,'') AS context_slug, COALESCE(c.title,'') AS context_title
    FROM work_evidence e
    LEFT JOIN work_context c ON c.id=e.context_id
    WHERE (? IS NULL OR e.context_id=?)
      AND (?='' OR e.module=?)
      AND (?='' OR e.kind=?)
      AND (?='' OR e.item_id=?)
      AND (?='' OR e.session_id=?)
    ORDER BY e.created_at DESC LIMIT ?`)
    .all(cid, cid, module, module, a.kind ?? "", a.kind ?? "", a.item_id ?? "", a.item_id ?? "",
      a.session_id ?? "", a.session_id ?? "", a.limit ?? 50);
}

export function workMessageCreate(db: DatabaseSync, a: ContextRef & {
  module: string;
  session_id?: string;
  agent_id?: string;
  speaker?: string;
  role?: string;
  kind?: string;
  body: string;
  status?: string;
  metadata?: unknown;
}) {
  ensureSchema(db);
  const module = normModule(a.module);
  const cid = contextId(db, a);
  const body = text(a.body).trim();
  if (!body) throw new Error("body is required");
  const mid = id("msg");
  const ts = now();
  db.prepare(`
    INSERT INTO work_message(id, context_id, module, session_id, agent_id, speaker, role, kind, body, status, metadata_json, created_at)
    VALUES(?,?,?,?,?,?,?,?,?,?,?,?)`)
    .run(mid, cid, module, a.session_id ?? null, a.agent_id ?? null, a.speaker ?? "", a.role ?? "agent",
      a.kind ?? "message", body, a.status ?? "posted", metadataJson(a.metadata), ts);
  return db.prepare(`SELECT * FROM work_message WHERE id=?`).get(mid);
}

export function workMessageList(db: DatabaseSync, a: ContextRef & {
  module?: string;
  session_id?: string;
  agent_id?: string;
  kind?: string;
  limit?: number;
} = {}) {
  ensureSchema(db);
  const cid = contextId(db, a);
  const module = a.module ? normModule(a.module) : "";
  return db.prepare(`
    SELECT m.*, COALESCE(c.slug,'') AS context_slug, COALESCE(c.title,'') AS context_title
    FROM work_message m
    LEFT JOIN work_context c ON c.id=m.context_id
    WHERE (? IS NULL OR m.context_id=?)
      AND (?='' OR m.module=?)
      AND (?='' OR m.kind=?)
      AND (?='' OR m.session_id=?)
      AND (?='' OR m.agent_id=?)
    ORDER BY m.created_at ASC
    LIMIT ?`)
    .all(cid, cid, module, module, a.kind ?? "", a.kind ?? "", a.session_id ?? "", a.session_id ?? "",
      a.agent_id ?? "", a.agent_id ?? "", a.limit ?? 100);
}

export function workAgentRegister(db: DatabaseSync, a: ContextRef & {
  module: string;
  name: string;
  tool: string;
  role?: string;
  status?: string;
  current_task?: string;
  session_id?: string;
}) {
  ensureSchema(db);
  const module = normModule(a.module);
  const cid = contextId(db, a);
  const aid = `agent:${module}:${cid ?? "global"}:${a.tool}:${a.name}`.toLowerCase().replace(/[^a-z0-9:._-]+/g, "-");
  const ts = now();
  db.prepare(`
    INSERT INTO work_agent(id, context_id, module, name, tool, role, status, current_task, session_id, heartbeat_at, created_at, updated_at)
    VALUES(?,?,?,?,?,?,?,?,?,?,?,?)
    ON CONFLICT(module, context_id, name, tool) DO UPDATE SET
      role=excluded.role,
      status=excluded.status,
      current_task=excluded.current_task,
      session_id=excluded.session_id,
      heartbeat_at=excluded.heartbeat_at,
      updated_at=excluded.updated_at`)
    .run(aid, cid, module, a.name, a.tool, a.role ?? "worker", a.status ?? "idle",
      a.current_task ?? "", a.session_id ?? null, ts, ts, ts);
  return db.prepare(`SELECT * FROM work_agent WHERE id=?`).get(aid);
}

export function workAgentHeartbeat(db: DatabaseSync, a: { agent_id: string; status?: string; current_task?: string; session_id?: string }) {
  ensureSchema(db);
  const ts = now();
  const r = db.prepare(`
    UPDATE work_agent
    SET status=COALESCE(?, status), current_task=COALESCE(?, current_task), session_id=COALESCE(?, session_id),
        heartbeat_at=?, updated_at=?
    WHERE id=?`)
    .run(a.status ?? null, a.current_task ?? null, a.session_id ?? null, ts, ts, a.agent_id);
  return { ok: (r.changes ?? 0) > 0, heartbeat_at: ts };
}

export function workAgentList(db: DatabaseSync, a: ContextRef & { module?: string; status?: string; limit?: number } = {}) {
  ensureSchema(db);
  const cid = contextId(db, a);
  const module = a.module ? normModule(a.module) : "";
  return db.prepare(`
    SELECT ag.*, COALESCE(c.slug,'') AS context_slug, COALESCE(c.title,'') AS context_title
    FROM work_agent ag
    LEFT JOIN work_context c ON c.id=ag.context_id
    WHERE (? IS NULL OR ag.context_id=?) AND (?='' OR ag.module=?) AND (?='' OR ag.status=?)
    ORDER BY ag.updated_at DESC LIMIT ?`)
    .all(cid, cid, module, module, a.status ?? "", a.status ?? "", a.limit ?? 50);
}

export function workModuleSnapshot(db: DatabaseSync, a: { module: string; limit?: number }) {
  ensureSchema(db);
  const module = normModule(a.module);
  return {
    module,
    contexts: workContextList(db, { module, limit: a.limit ?? 50 }),
    sessions: workSessionList(db, { module, limit: a.limit ?? 50 }),
    items: workItemList(db, { module, status: "open", limit: a.limit ?? 50 }),
    agents: workAgentList(db, { module, limit: a.limit ?? 50 }),
    decisions: workDecisionList(db, { module, status: "proposed", limit: a.limit ?? 50 }),
    evidence: workEvidenceList(db, { module, limit: a.limit ?? 50 }),
    messages: workMessageList(db, { module, limit: a.limit ?? 50 }),
  };
}
