-- Migration: 004_create_rls_policies.sql
-- Row Level Security policies for multi-agent coordination
-- Author: Rivet (Ops)
-- Target: Supabase Database (rateright-growth)

BEGIN;

-- Enable RLS on all multi-agent tables
ALTER TABLE bot_tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE bot_comms_queue ENABLE ROW LEVEL SECURITY;
ALTER TABLE bot_handoffs ENABLE ROW LEVEL SECURITY;
ALTER TABLE bot_audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE bot_status ENABLE ROW LEVEL SECURITY;

-- Function to get current bot ID from session context
-- This will be set via app.bot_id configuration parameter
CREATE OR REPLACE FUNCTION current_bot_id()
RETURNS TEXT AS $$
BEGIN
    RETURN current_setting('app.bot_id', true);
EXCEPTION
    WHEN OTHERS THEN RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Bot Tasks RLS Policies
-- Rivet (ops) can see and modify all tasks
CREATE POLICY "rivet_all_tasks" ON bot_tasks
    FOR ALL TO authenticated
    USING (current_bot_id() = 'rivet');

-- Bots can see tasks they own or are assigned to
CREATE POLICY "bot_own_assigned_tasks" ON bot_tasks
    FOR ALL TO authenticated
    USING (
        bot_id = current_bot_id() 
        OR assigned_to = current_bot_id()
    );

-- All authenticated bots can create new tasks
CREATE POLICY "bot_create_tasks" ON bot_tasks
    FOR INSERT TO authenticated
    WITH CHECK (bot_id = current_bot_id());

-- Bot Communications Queue RLS Policies
-- Rivet and system can see all communications
CREATE POLICY "rivet_all_comms" ON bot_comms_queue
    FOR ALL TO authenticated
    USING (current_bot_id() IN ('rivet', 'system'));

-- Bots can see their own communication requests
CREATE POLICY "bot_own_comms" ON bot_comms_queue
    FOR ALL TO authenticated
    USING (requesting_bot = current_bot_id());

-- Bots can create communication requests
CREATE POLICY "bot_create_comms" ON bot_comms_queue
    FOR INSERT TO authenticated
    WITH CHECK (requesting_bot = current_bot_id());

-- Bot Handoffs RLS Policies
-- All bots can see handoffs (for coordination)
CREATE POLICY "all_bots_read_handoffs" ON bot_handoffs
    FOR SELECT TO authenticated
    USING (true);

-- Bots can create handoffs from themselves
CREATE POLICY "bot_create_handoffs" ON bot_handoffs
    FOR INSERT TO authenticated
    WITH CHECK (from_bot = current_bot_id());

-- Bots can update handoffs they're involved in
CREATE POLICY "bot_update_handoffs" ON bot_handoffs
    FOR UPDATE TO authenticated
    USING (from_bot = current_bot_id() OR to_bot = current_bot_id());

-- Rivet can manage all handoffs
CREATE POLICY "rivet_all_handoffs" ON bot_handoffs
    FOR ALL TO authenticated
    USING (current_bot_id() = 'rivet');

-- Bot Audit Log RLS Policies
-- All bots can read the audit log (transparency)
CREATE POLICY "all_bots_read_audit" ON bot_audit_log
    FOR SELECT TO authenticated
    USING (true);

-- Bots can only insert their own audit entries
CREATE POLICY "bot_own_audit_entries" ON bot_audit_log
    FOR INSERT TO authenticated
    WITH CHECK (bot_id = current_bot_id());

-- System/Rivet can insert audit entries for any bot
CREATE POLICY "system_audit_entries" ON bot_audit_log
    FOR INSERT TO authenticated
    WITH CHECK (current_bot_id() IN ('rivet', 'system'));

-- Bot Status RLS Policies
-- All bots can read status information (for coordination)
CREATE POLICY "all_bots_read_status" ON bot_status
    FOR SELECT TO authenticated
    USING (true);

-- Bots can only update their own status
CREATE POLICY "bot_own_status" ON bot_status
    FOR ALL TO authenticated
    USING (bot_id = current_bot_id());

-- Rivet can manage all bot status
CREATE POLICY "rivet_all_status" ON bot_status
    FOR ALL TO authenticated
    USING (current_bot_id() = 'rivet');

COMMIT;