-- ══════════════════════════════════════════════════════════════
-- Control Centre Tables
-- Run this in Supabase SQL Editor: https://supabase.com/dashboard
-- ══════════════════════════════════════════════════════════════

-- ── Tasks Board ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_tasks (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'backlog' CHECK (status IN ('backlog', 'in_progress', 'review', 'done')),
  priority TEXT DEFAULT 'medium' CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
  agent_id TEXT,
  project_id TEXT,
  created_by TEXT DEFAULT 'michael',
  approved_by TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  due_date TIMESTAMPTZ
);

-- ── Projects ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_projects (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'on_hold', 'completed')),
  agents TEXT[] DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

-- ── Council Topics ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_council_topics (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL,
  pinned BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now(),
  last_activity_at TIMESTAMPTZ DEFAULT now()
);

-- ── Council Messages ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_council_messages (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  topic_id UUID REFERENCES cc_council_topics(id) ON DELETE CASCADE,
  sender_type TEXT NOT NULL CHECK (sender_type IN ('michael', 'agent')),
  sender_id TEXT NOT NULL,
  content TEXT NOT NULL,
  mentions TEXT[] DEFAULT '{}',
  pinned BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- ── Agent Status Snapshots ───────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_agent_snapshots (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  agent_name TEXT NOT NULL,
  status TEXT NOT NULL,
  model TEXT,
  port INTEGER,
  uptime_seconds INTEGER,
  active_sessions INTEGER DEFAULT 0,
  error_count INTEGER DEFAULT 0,
  memory_mb INTEGER DEFAULT 0,
  cpu_percent DECIMAL(5,2) DEFAULT 0,
  current_task TEXT,
  snapshot_at TIMESTAMPTZ DEFAULT now()
);

-- ── API Cost Tracking ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_api_costs (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  agent_name TEXT NOT NULL,
  model TEXT NOT NULL,
  tokens_in INTEGER DEFAULT 0,
  tokens_out INTEGER DEFAULT 0,
  cost_usd DECIMAL(10,4) DEFAULT 0,
  recorded_at TIMESTAMPTZ DEFAULT now()
);

-- ── Approvals Queue ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_approvals (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  task_id UUID REFERENCES cc_tasks(id),
  agent_id TEXT NOT NULL,
  action_description TEXT NOT NULL,
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
  decided_by TEXT,
  decided_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- ── Activity Log ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_activity_log (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  agent_id TEXT NOT NULL,
  action TEXT NOT NULL,
  detail TEXT,
  event_type TEXT DEFAULT 'system' CHECK (event_type IN ('task', 'message', 'error', 'deploy', 'system')),
  created_at TIMESTAMPTZ DEFAULT now()
);

-- ══════════════════════════════════════════════════════════════
-- RLS Policies — allow anon access (Michael-only app, no multi-user)
-- ══════════════════════════════════════════════════════════════

ALTER TABLE cc_tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE cc_projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE cc_council_topics ENABLE ROW LEVEL SECURITY;
ALTER TABLE cc_council_messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE cc_agent_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE cc_api_costs ENABLE ROW LEVEL SECURITY;
ALTER TABLE cc_approvals ENABLE ROW LEVEL SECURITY;
ALTER TABLE cc_activity_log ENABLE ROW LEVEL SECURITY;

-- Allow all operations for anon (single-user dashboard)
CREATE POLICY "cc_tasks_all" ON cc_tasks FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);
CREATE POLICY "cc_projects_all" ON cc_projects FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);
CREATE POLICY "cc_council_topics_all" ON cc_council_topics FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);
CREATE POLICY "cc_council_messages_all" ON cc_council_messages FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);
CREATE POLICY "cc_agent_snapshots_all" ON cc_agent_snapshots FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);
CREATE POLICY "cc_api_costs_all" ON cc_api_costs FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);
CREATE POLICY "cc_approvals_all" ON cc_approvals FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);
CREATE POLICY "cc_activity_log_all" ON cc_activity_log FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);

-- ── Realtime ─────────────────────────────────────────────────
-- Enable realtime on tables that need live updates
ALTER PUBLICATION supabase_realtime ADD TABLE cc_council_messages;
ALTER PUBLICATION supabase_realtime ADD TABLE cc_approvals;
ALTER PUBLICATION supabase_realtime ADD TABLE cc_activity_log;
ALTER PUBLICATION supabase_realtime ADD TABLE cc_agent_snapshots;

-- ══════════════════════════════════════════════════════════════
-- Seed Data — Default topics and projects
-- ══════════════════════════════════════════════════════════════

-- Default council topics
INSERT INTO cc_council_topics (id, title, pinned) VALUES
  ('00000000-0000-0000-0000-000000000001', 'General', true),
  ('00000000-0000-0000-0000-000000000002', 'Deployments', false),
  ('00000000-0000-0000-0000-000000000003', 'Growth Strategy', true),
  ('00000000-0000-0000-0000-000000000004', 'Bug Reports', false),
  ('00000000-0000-0000-0000-000000000005', 'Water Cooler', false)
ON CONFLICT (id) DO NOTHING;

-- Default projects
INSERT INTO cc_projects (id, name, description, status, agents) VALUES
  ('rateright', 'RateRight Platform', 'Construction hiring marketplace', 'active', ARRAY['rivet','builder','susan','radar']),
  ('growth-engine', 'Growth Engine', 'Lead generation and nurturing system', 'active', ARRAY['rivet','builder','susan','harper']),
  ('opsman', 'OpsMan', 'AI fleet management product', 'active', ARRAY['rivet','builder','cog']),
  ('personal', 'Personal', 'Personal projects and experiments', 'active', ARRAY['rivet'])
ON CONFLICT (id) DO NOTHING;

-- Seed some initial council messages so it's not empty
INSERT INTO cc_council_messages (topic_id, sender_type, sender_id, content) VALUES
  ('00000000-0000-0000-0000-000000000001', 'agent', 'rivet', 'Control Centre is live. All agents reporting in. Fleet status: nominal.'),
  ('00000000-0000-0000-0000-000000000001', 'agent', 'builder', 'Standing by for tasks. Ready to build.'),
  ('00000000-0000-0000-0000-000000000001', 'agent', 'susan', 'Pipeline loaded. 321 leads in the system.'),
  ('00000000-0000-0000-0000-000000000001', 'agent', 'sentinel', 'Security scan complete. All clear.'),
  ('00000000-0000-0000-0000-000000000001', 'agent', 'harper', 'Content calendar drafted for the week.'),
  ('00000000-0000-0000-0000-000000000001', 'agent', 'radar', 'Market intel updated. Hipages pricing change detected.'),
  ('00000000-0000-0000-0000-000000000001', 'agent', 'herald', 'Notification channels active: Telegram, SMS.'),
  ('00000000-0000-0000-0000-000000000001', 'agent', 'cog', 'Infrastructure check complete. VPS healthy.');
