-- Dewx Local — Control Plane (Ops) schema v1  [ADDITIVE]
-- ====================================================
-- The control plane: Projects ← Tabs (workers/brain) push state via the :8765 MCP; the brain reads
-- the board + sends orders. Truth lives here (SQLite, WAL) + each project's PROJECT.md (mirror).
-- Additive only — never alters the inbox/CRM tables. Apply alongside schema-v1.sql on the same DB.
-- NOTE: `update` is a SQL keyword → the table is referenced as "update" (quoted) everywhere.

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',   -- active|paused|blocked|done
  priority      INTEGER DEFAULT 3,
  category      TEXT NOT NULL DEFAULT '',          -- Dewx|Clients|Ventures|custom rail group
  control_dir   TEXT NOT NULL,                     -- ~/dewx/state/projects/<slug>/
  repo_path     TEXT,                              -- code location (may be read-only / remote)
  exec_host     TEXT NOT NULL DEFAULT 'mac',       -- mac|hetzner|server
  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',  -- worker|brain
  status             TEXT NOT NULL DEFAULT 'idle',    -- running|idle|blocked|down
  pid                INTEGER,
  cwd                TEXT,
  model              TEXT,
  interval_sec       INTEGER DEFAULT 600,
  heartbeat_at       TEXT,
  current_project_id TEXT REFERENCES project(id),
  current_task       TEXT,
  created_at         TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS project_tab (
  project_id  TEXT NOT NULL REFERENCES project(id),
  tab_id      TEXT NOT NULL REFERENCES tab(id),
  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 REFERENCES project(id),
  tab_id      TEXT REFERENCES tab(id),
  ts          TEXT NOT NULL,
  kind        TEXT NOT NULL,        -- progress|found|blocked|need|decision|done
  body        TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS message (
  id          TEXT PRIMARY KEY,
  from_tab_id TEXT REFERENCES tab(id),
  to_tab_id   TEXT REFERENCES tab(id),   -- NULL = broadcast
  project_id  TEXT REFERENCES project(id),
  ts          TEXT NOT NULL,
  body        TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending'  -- pending|delivered|ack|done
);

CREATE TABLE IF NOT EXISTS ask (
  id          TEXT PRIMARY KEY,
  project_id  TEXT REFERENCES project(id),
  tab_id      TEXT REFERENCES tab(id),
  ts          TEXT NOT NULL,
  question    TEXT NOT NULL,
  urgency     TEXT NOT NULL DEFAULT 'normal',   -- low|normal|high
  status      TEXT NOT NULL DEFAULT 'open',     -- open|answered
  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_message_project ON message(project_id, status);
CREATE INDEX IF NOT EXISTS ix_ask_status ON ask(status, ts);
CREATE INDEX IF NOT EXISTS ix_tab_project ON tab(current_project_id);

-- DEV workspace v1: living SRS, project tasks/bugs, shared memory, team/agent presence,
-- decision log, and Chromium browser QA metadata. Additive only.

CREATE TABLE IF NOT EXISTS dev_srs (
  project_id      TEXT PRIMARY KEY REFERENCES project(id),
  overview        TEXT NOT NULL DEFAULT '',
  users           TEXT NOT NULL DEFAULT '',
  requirements    TEXT NOT NULL DEFAULT '',
  modules         TEXT NOT NULL DEFAULT '',
  flows           TEXT NOT NULL DEFAULT '',
  acceptance      TEXT NOT NULL DEFAULT '',
  constraints     TEXT NOT NULL DEFAULT '',
  open_questions  TEXT NOT NULL DEFAULT '',
  updated_at      TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dev_work_item (
  id             TEXT PRIMARY KEY,
  project_id     TEXT NOT NULL REFERENCES project(id),
  kind           TEXT NOT NULL DEFAULT 'task',     -- task|bug|ask|handoff
  title          TEXT NOT NULL,
  status         TEXT NOT NULL DEFAULT 'open',     -- open|in_progress|blocked|done
  priority       TEXT NOT NULL DEFAULT 'normal',   -- low|normal|high
  module         TEXT NOT NULL DEFAULT '',
  owner          TEXT NOT NULL DEFAULT '',
  source         TEXT NOT NULL DEFAULT 'manual',
  url            TEXT NOT NULL DEFAULT '',
  evidence_path  TEXT NOT NULL DEFAULT '',
  session_id     TEXT NOT NULL DEFAULT '',
  summary        TEXT NOT NULL DEFAULT '',
  created_at     TEXT NOT NULL,
  updated_at     TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dev_memory (
  id          TEXT PRIMARY KEY,
  project_id  TEXT NOT NULL REFERENCES project(id),
  kind        TEXT NOT NULL DEFAULT 'fact',       -- fact|decision|gotcha|command|architecture|bug-pattern
  body        TEXT NOT NULL,
  source      TEXT NOT NULL DEFAULT '',
  confidence  TEXT NOT NULL DEFAULT 'medium',
  status      TEXT NOT NULL DEFAULT 'proposed',   -- proposed|approved|rejected
  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 dev_agent (
  id            TEXT PRIMARY KEY,
  project_id    TEXT NOT NULL REFERENCES project(id),
  name          TEXT NOT NULL,
  tool          TEXT NOT NULL DEFAULT '',          -- codex|claude|kimi|gemini|cursor|human|browser
  role          TEXT NOT NULL DEFAULT 'worker',
  status        TEXT NOT NULL DEFAULT 'idle',      -- idle|running|blocked|review
  current_task  TEXT NOT NULL DEFAULT '',
  session_id    TEXT NOT NULL DEFAULT '',
  heartbeat_at  TEXT NOT NULL DEFAULT '',
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dev_decision (
  id          TEXT PRIMARY KEY,
  project_id  TEXT NOT NULL REFERENCES project(id),
  title       TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'proposed',    -- proposed|approved|rejected
  proposal    TEXT NOT NULL DEFAULT '',
  rationale   TEXT NOT NULL DEFAULT '',
  decided_by  TEXT NOT NULL DEFAULT '',
  created_at  TEXT NOT NULL,
  updated_at  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dev_browser_session (
  id                 TEXT PRIMARY KEY,
  project_id          TEXT NOT NULL REFERENCES project(id),
  title              TEXT NOT NULL DEFAULT 'Browser QA',
  url                TEXT NOT NULL DEFAULT '',
  engine             TEXT NOT NULL DEFAULT 'cef',
  status             TEXT NOT NULL DEFAULT 'runtime_missing',
  last_capture_path  TEXT NOT NULL DEFAULT '',
  last_error         TEXT NOT NULL DEFAULT '',
  created_at         TEXT NOT NULL,
  updated_at         TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS dev_terminal_session (
  id            TEXT PRIMARY KEY,
  project_id    TEXT REFERENCES project(id),
  project_slug  TEXT NOT NULL DEFAULT '',
  title         TEXT NOT NULL,
  backend       TEXT NOT NULL DEFAULT 'tmux',       -- tmux|direct|ssh-tmux
  tmux_session  TEXT NOT NULL DEFAULT '',
  cwd           TEXT NOT NULL DEFAULT '',
  host          TEXT NOT NULL DEFAULT 'mac',
  status        TEXT NOT NULL DEFAULT 'attached',  -- attached|detached|closed
  last_command  TEXT NOT NULL DEFAULT '',
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL,
  last_attach_at TEXT NOT NULL DEFAULT ''
);

CREATE INDEX IF NOT EXISTS ix_dev_work_project ON dev_work_item(project_id, kind, status, updated_at);
CREATE INDEX IF NOT EXISTS ix_dev_memory_project ON dev_memory(project_id, status, updated_at);
CREATE INDEX IF NOT EXISTS ix_dev_agent_project ON dev_agent(project_id, status, updated_at);
CREATE INDEX IF NOT EXISTS ix_dev_decision_project ON dev_decision(project_id, status, updated_at);
CREATE INDEX IF NOT EXISTS ix_dev_browser_project ON dev_browser_session(project_id, updated_at);
CREATE INDEX IF NOT EXISTS ix_dev_terminal_project ON dev_terminal_session(project_id, status, updated_at);

-- Universal Work Console v1: shared GTM/CX/Ops/DEV/Dew coordination objects.
-- Additive and intentionally separate from dev_* for now; DEV can migrate later after the
-- terminal workspace stabilizes.

CREATE TABLE IF NOT EXISTS work_context (
  id             TEXT PRIMARY KEY,
  module         TEXT NOT NULL,                    -- gtm|cx|ops|dev|dew
  kind           TEXT NOT NULL DEFAULT 'workspace', -- campaign|client|project|ticket|invoice|hire|admin|...
  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',      -- task|bug|ask|handoff|approval|reply|screen
  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',   -- proposed|approved|rejected
  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',    -- proposed|approved|rejected
  proposal    TEXT NOT NULL DEFAULT '',
  rationale   TEXT NOT NULL DEFAULT '',
  risk        TEXT NOT NULL DEFAULT 'normal',      -- low|normal|high|external|money|credential
  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',    -- roki|agent|system|safety
  kind           TEXT NOT NULL DEFAULT 'message',  -- message|note|status|ask
  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);
