-- Migration: 001_create_multi_agent_schema.sql
-- RateRight Multi-Agent Coordination System - Core Tables
-- Author: Rivet (Ops)
-- Target: Supabase Database (rateright-growth)

BEGIN;

-- Bot Tasks Queue
CREATE TABLE bot_tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bot_id TEXT NOT NULL, -- Owner bot (e.g., 'rivet', 'builder', 'susan', 'harper', 'sentinel', 'radar')
    assigned_to TEXT, -- Assigned bot (null = unassigned)
    title TEXT NOT NULL,
    description TEXT,
    status TEXT NOT NULL CHECK (status IN ('pending', 'in_progress', 'completed', 'failed')),
    priority TEXT NOT NULL CHECK (priority IN ('critical', 'high', 'medium', 'low')) DEFAULT 'medium',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Bot Communications Queue (Approval System)
CREATE TABLE bot_comms_queue (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    requesting_bot TEXT NOT NULL,
    channel TEXT NOT NULL CHECK (channel IN ('sms', 'email', 'whatsapp', 'telegram', 'voice')),
    recipient TEXT NOT NULL,
    message_body TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('draft', 'pending_review', 'approved', 'rejected', 'sent')) DEFAULT 'draft',
    reviewed_by TEXT,
    review_notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    sent_at TIMESTAMPTZ
);

-- Cross-Bot Task Handoffs
CREATE TABLE bot_handoffs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    from_bot TEXT NOT NULL,
    to_bot TEXT NOT NULL,
    task_summary TEXT NOT NULL,
    context JSONB DEFAULT '{}'::jsonb,
    status TEXT NOT NULL CHECK (status IN ('pending', 'accepted', 'completed')) DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    accepted_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ
);

-- Immutable Audit Log
CREATE TABLE bot_audit_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bot_id TEXT NOT NULL,
    action TEXT NOT NULL,
    details JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Bot Status & Heartbeats
CREATE TABLE bot_status (
    bot_id TEXT PRIMARY KEY,
    last_heartbeat TIMESTAMPTZ DEFAULT NOW(),
    status TEXT NOT NULL CHECK (status IN ('online', 'offline', 'error')) DEFAULT 'online',
    current_task UUID REFERENCES bot_tasks(id),
    metadata JSONB DEFAULT '{}'::jsonb
);

COMMIT;