-- ══════════════════════════════════════════════════════════════
-- Control Centre: Memories & Documents Tables
-- 2026-03-12 — adds cc_memories and cc_documents for live data
-- ══════════════════════════════════════════════════════════════

-- ── Memories ───────────────────────────────────────────────────
-- Agent learnings, decisions, lessons, SOPs
CREATE TABLE IF NOT EXISTS cc_memories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  agent_id TEXT NOT NULL,
  category TEXT DEFAULT 'general',
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  source TEXT DEFAULT 'manual', -- 'vault', 'conversation', 'manual'
  tags TEXT[] DEFAULT '{}',
  importance INTEGER DEFAULT 5 CHECK (importance BETWEEN 1 AND 10),
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_cc_memories_agent ON cc_memories(agent_id);
CREATE INDEX IF NOT EXISTS idx_cc_memories_category ON cc_memories(category);
CREATE INDEX IF NOT EXISTS idx_cc_memories_created ON cc_memories(created_at DESC);

-- ── Documents ──────────────────────────────────────────────────
-- SOPs, runbooks, reports, notes produced by agents
CREATE TABLE IF NOT EXISTS cc_documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  category TEXT DEFAULT 'general',
  content TEXT,
  file_path TEXT,
  agent_id TEXT,
  doc_type TEXT DEFAULT 'note', -- 'sop', 'runbook', 'note', 'report', 'research', 'technical', 'plan'
  tags TEXT[] DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_cc_documents_category ON cc_documents(category);
CREATE INDEX IF NOT EXISTS idx_cc_documents_type ON cc_documents(doc_type);
CREATE INDEX IF NOT EXISTS idx_cc_documents_agent ON cc_documents(agent_id);
CREATE INDEX IF NOT EXISTS idx_cc_documents_created ON cc_documents(created_at DESC);

-- ── RLS ────────────────────────────────────────────────────────
ALTER TABLE cc_memories ENABLE ROW LEVEL SECURITY;
ALTER TABLE cc_documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "cc_memories_all" ON cc_memories FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);
CREATE POLICY "cc_documents_all" ON cc_documents FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);

-- ── Realtime ───────────────────────────────────────────────────
ALTER PUBLICATION supabase_realtime ADD TABLE cc_memories;
ALTER PUBLICATION supabase_realtime ADD TABLE cc_documents;
