-- Migration: 002_create_indexes.sql
-- Performance indexes for multi-agent coordination tables
-- Author: Rivet (Ops)
-- Target: Supabase Database (rateright-growth)

BEGIN;

-- Indexes for bot_tasks table
CREATE INDEX idx_bot_tasks_bot_id ON bot_tasks(bot_id);
CREATE INDEX idx_bot_tasks_assigned_to ON bot_tasks(assigned_to);
CREATE INDEX idx_bot_tasks_status ON bot_tasks(status);
CREATE INDEX idx_bot_tasks_priority ON bot_tasks(priority);
CREATE INDEX idx_bot_tasks_created_at ON bot_tasks(created_at);
CREATE INDEX idx_bot_tasks_status_priority ON bot_tasks(status, priority);

-- Indexes for bot_comms_queue table
CREATE INDEX idx_bot_comms_queue_status ON bot_comms_queue(status);
CREATE INDEX idx_bot_comms_queue_requesting_bot ON bot_comms_queue(requesting_bot);
CREATE INDEX idx_bot_comms_queue_channel ON bot_comms_queue(channel);
CREATE INDEX idx_bot_comms_queue_created_at ON bot_comms_queue(created_at);
CREATE INDEX idx_bot_comms_queue_status_created ON bot_comms_queue(status, created_at);

-- Indexes for bot_handoffs table
CREATE INDEX idx_bot_handoffs_from_bot ON bot_handoffs(from_bot);
CREATE INDEX idx_bot_handoffs_to_bot ON bot_handoffs(to_bot);
CREATE INDEX idx_bot_handoffs_status ON bot_handoffs(status);
CREATE INDEX idx_bot_handoffs_created_at ON bot_handoffs(created_at);
CREATE INDEX idx_bot_handoffs_status_created ON bot_handoffs(status, created_at);

-- Indexes for bot_audit_log table
CREATE INDEX idx_bot_audit_log_bot_id ON bot_audit_log(bot_id);
CREATE INDEX idx_bot_audit_log_action ON bot_audit_log(action);
CREATE INDEX idx_bot_audit_log_created_at ON bot_audit_log(created_at);
CREATE INDEX idx_bot_audit_log_bot_created ON bot_audit_log(bot_id, created_at);

-- Indexes for bot_status table (primary key already exists)
CREATE INDEX idx_bot_status_last_heartbeat ON bot_status(last_heartbeat);
CREATE INDEX idx_bot_status_status ON bot_status(status);

COMMIT;