-- ══════════════════════════════════════════════════════════════
-- Council Router Schema
-- Adds rounds, dispatches, and structured message support
-- ══════════════════════════════════════════════════════════════

-- ── Council Rounds ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_council_rounds (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  topic_id UUID REFERENCES cc_council_topics(id) ON DELETE CASCADE,
  trigger_message_id UUID REFERENCES cc_council_messages(id) ON DELETE SET NULL,
  round_number INTEGER NOT NULL DEFAULT 1,
  round_type TEXT NOT NULL DEFAULT 'fan_out' CHECK (round_type IN ('fan_out', 'discussion', 'consolidation')),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'dispatching', 'awaiting_replies', 'complete', 'timed_out', 'failed')),
  targets TEXT[] DEFAULT '{}',
  idempotency_key TEXT UNIQUE,
  timeout_seconds INTEGER DEFAULT 300,
  created_at TIMESTAMPTZ DEFAULT now(),
  completed_at TIMESTAMPTZ,
  consolidated_plan JSONB
);

-- ── Council Dispatches ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS cc_council_dispatches (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  round_id UUID REFERENCES cc_council_rounds(id) ON DELETE CASCADE,
  agent_id TEXT NOT NULL,
  dispatch_method TEXT DEFAULT 'wake' CHECK (dispatch_method IN ('wake', 'invoke', 'localcc', 'ccvps')),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'dispatched', 'delivered', 'replied', 'failed', 'timed_out')),
  dispatched_at TIMESTAMPTZ,
  replied_at TIMESTAMPTZ,
  response_message_id UUID REFERENCES cc_council_messages(id) ON DELETE SET NULL,
  error TEXT,
  retry_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(round_id, agent_id)
);

-- ── New columns on cc_council_messages ─────────────────────
ALTER TABLE cc_council_messages
  ADD COLUMN IF NOT EXISTS round_id UUID REFERENCES cc_council_rounds(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS message_type TEXT DEFAULT 'chat' CHECK (message_type IN ('chat', 'prompt', 'response', 'discussion', 'plan', 'system')),
  ADD COLUMN IF NOT EXISTS reply_to UUID REFERENCES cc_council_messages(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS structured_data JSONB,
  ADD COLUMN IF NOT EXISTS dispatch_status TEXT DEFAULT 'none' CHECK (dispatch_status IN ('none', 'pending', 'dispatched', 'delivered', 'failed', 'timed_out'));

-- ── RLS ────────────────────────────────────────────────────
ALTER TABLE cc_council_rounds ENABLE ROW LEVEL SECURITY;
ALTER TABLE cc_council_dispatches ENABLE ROW LEVEL SECURITY;

CREATE POLICY "cc_council_rounds_all" ON cc_council_rounds FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);
CREATE POLICY "cc_council_dispatches_all" ON cc_council_dispatches FOR ALL TO anon, authenticated USING (true) WITH CHECK (true);

-- ── Realtime ───────────────────────────────────────────────
ALTER PUBLICATION supabase_realtime ADD TABLE cc_council_rounds;
ALTER PUBLICATION supabase_realtime ADD TABLE cc_council_dispatches;

-- ── Indexes ────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_council_rounds_topic ON cc_council_rounds(topic_id);
CREATE INDEX IF NOT EXISTS idx_council_rounds_status ON cc_council_rounds(status);
CREATE INDEX IF NOT EXISTS idx_council_dispatches_round ON cc_council_dispatches(round_id);
CREATE INDEX IF NOT EXISTS idx_council_messages_round ON cc_council_messages(round_id);
CREATE INDEX IF NOT EXISTS idx_council_messages_type ON cc_council_messages(message_type);
