-- Migration: 005_create_views_functions.sql
-- Utility views and functions for multi-agent coordination
-- Author: Rivet (Ops)
-- Target: Supabase Database (rateright-growth)

BEGIN;

-- View: Active tasks by priority
CREATE VIEW active_tasks_by_priority AS
SELECT 
    id,
    bot_id,
    assigned_to,
    title,
    description,
    status,
    priority,
    created_at,
    updated_at,
    CASE priority 
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2  
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
    END as priority_order
FROM bot_tasks 
WHERE status IN ('pending', 'in_progress')
ORDER BY priority_order ASC, created_at ASC;

-- View: Bot workload summary
CREATE VIEW bot_workload_summary AS
SELECT 
    COALESCE(assigned_to, 'unassigned') as bot_id,
    COUNT(*) as total_tasks,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_tasks,
    COUNT(CASE WHEN status = 'in_progress' THEN 1 END) as in_progress_tasks,
    COUNT(CASE WHEN priority = 'critical' THEN 1 END) as critical_tasks,
    COUNT(CASE WHEN priority = 'high' THEN 1 END) as high_tasks,
    MIN(created_at) as oldest_task,
    MAX(updated_at) as last_activity
FROM bot_tasks 
WHERE status IN ('pending', 'in_progress')
GROUP BY COALESCE(assigned_to, 'unassigned')
ORDER BY critical_tasks DESC, high_tasks DESC, total_tasks DESC;

-- View: Recent bot activity
CREATE VIEW recent_bot_activity AS
SELECT 
    bot_id,
    action,
    details,
    created_at,
    details->>'task_id' as task_id,
    details->>'title' as task_title
FROM bot_audit_log 
WHERE created_at > NOW() - INTERVAL '24 hours'
ORDER BY created_at DESC;

-- View: Pending communications requiring approval
CREATE VIEW pending_communications AS
SELECT 
    id,
    requesting_bot,
    channel,
    recipient,
    LEFT(message_body, 100) as message_preview,
    created_at,
    EXTRACT(EPOCH FROM (NOW() - created_at))/60 as minutes_waiting
FROM bot_comms_queue 
WHERE status = 'pending_review'
ORDER BY created_at ASC;

-- View: Bot health status
CREATE VIEW bot_health_status AS
SELECT 
    bot_id,
    status,
    last_heartbeat,
    EXTRACT(EPOCH FROM (NOW() - last_heartbeat))/60 as minutes_since_heartbeat,
    current_task,
    CASE 
        WHEN last_heartbeat > NOW() - INTERVAL '5 minutes' THEN 'healthy'
        WHEN last_heartbeat > NOW() - INTERVAL '15 minutes' THEN 'warning'
        ELSE 'critical'
    END as health_status
FROM bot_status
ORDER BY last_heartbeat DESC;

-- View: Failed tasks requiring attention
CREATE VIEW failed_tasks_alert AS
SELECT 
    bot_id,
    assigned_to,
    COUNT(*) as failed_count,
    MAX(updated_at) as last_failure,
    array_agg(title ORDER BY updated_at DESC) as recent_failures
FROM bot_tasks 
WHERE status = 'failed' 
    AND updated_at > NOW() - INTERVAL '24 hours'
GROUP BY bot_id, assigned_to
ORDER BY failed_count DESC, last_failure DESC;

-- View: Cross-bot handoff pipeline
CREATE VIEW handoff_pipeline AS
SELECT 
    h.id,
    h.from_bot,
    h.to_bot,
    h.task_summary,
    h.status as handoff_status,
    h.created_at,
    EXTRACT(EPOCH FROM (NOW() - h.created_at))/60 as minutes_pending,
    bs.status as to_bot_status,
    bs.last_heartbeat as to_bot_last_seen
FROM bot_handoffs h
LEFT JOIN bot_status bs ON h.to_bot = bs.bot_id
WHERE h.status IN ('pending', 'accepted')
ORDER BY h.created_at ASC;

-- Function: Assign task to best available bot
CREATE OR REPLACE FUNCTION assign_task_to_bot(
    task_uuid UUID,
    preferred_bot TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
    target_bot TEXT;
    task_exists BOOLEAN;
BEGIN
    -- Check if task exists and is unassigned
    SELECT EXISTS(
        SELECT 1 FROM bot_tasks 
        WHERE id = task_uuid 
        AND assigned_to IS NULL 
        AND status = 'pending'
    ) INTO task_exists;
    
    IF NOT task_exists THEN
        RETURN FALSE;
    END IF;
    
    -- Use preferred bot if specified and online
    IF preferred_bot IS NOT NULL THEN
        SELECT CASE 
            WHEN EXISTS(
                SELECT 1 FROM bot_status 
                WHERE bot_id = preferred_bot 
                AND status = 'online' 
                AND last_heartbeat > NOW() - INTERVAL '15 minutes'
            ) THEN preferred_bot
            ELSE NULL
        END INTO target_bot;
    END IF;
    
    -- If no preferred bot or preferred bot unavailable, find best available
    IF target_bot IS NULL THEN
        SELECT bot_id INTO target_bot
        FROM bot_status bs
        WHERE bs.status = 'online'
        AND bs.last_heartbeat > NOW() - INTERVAL '15 minutes'
        AND bs.bot_id != 'rivet' -- Don't auto-assign to Rivet (ops)
        ORDER BY (
            SELECT COUNT(*) 
            FROM bot_tasks 
            WHERE assigned_to = bs.bot_id 
            AND status IN ('pending', 'in_progress')
        ) ASC
        LIMIT 1;
    END IF;
    
    -- Assign the task if we found a bot
    IF target_bot IS NOT NULL THEN
        UPDATE bot_tasks 
        SET assigned_to = target_bot, 
            updated_at = NOW()
        WHERE id = task_uuid;
        
        RETURN TRUE;
    END IF;
    
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql;

-- Function: Create task with automatic assignment
CREATE OR REPLACE FUNCTION create_and_assign_task(
    p_bot_id TEXT,
    p_title TEXT,
    p_description TEXT DEFAULT NULL,
    p_priority TEXT DEFAULT 'medium',
    p_assigned_to TEXT DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
    new_task_id UUID;
BEGIN
    -- Insert the new task
    INSERT INTO bot_tasks (bot_id, title, description, priority, assigned_to)
    VALUES (p_bot_id, p_title, p_description, p_priority, p_assigned_to)
    RETURNING id INTO new_task_id;
    
    -- If no specific assignment, try to auto-assign
    IF p_assigned_to IS NULL THEN
        PERFORM assign_task_to_bot(new_task_id);
    END IF;
    
    RETURN new_task_id;
END;
$$ LANGUAGE plpgsql;

COMMIT;