-- Performance optimization indexes for WHOOSH
-- These indexes improve query performance for common access patterns

-- Agents table performance indexes
-- Composite index for status and last_seen filtering
CREATE INDEX IF NOT EXISTS idx_agents_status_last_seen ON agents(status, last_seen);

-- Repositories table performance indexes
-- Index on full_name for repository lookups
CREATE INDEX IF NOT EXISTS idx_repositories_full_name_lookup ON repositories(full_name);

-- Index on last_issue_sync for monitoring sync operations
CREATE INDEX IF NOT EXISTS idx_repositories_last_issue_sync ON repositories(last_issue_sync);

-- Tasks table performance indexes
-- Composite index for external_id and source_type lookups
CREATE INDEX IF NOT EXISTS idx_tasks_external_source_lookup ON tasks(external_id, source_type);

-- Councils table performance indexes
-- Index on councils.id for faster council lookups (covering existing primary key)
-- Note: Primary key already provides this, but adding explicit index for clarity
-- CREATE INDEX IF NOT EXISTS idx_councils_id ON councils(id); -- Redundant with PRIMARY KEY

-- Council_agents table performance indexes
-- Index on council_id for agent-to-council lookups
CREATE INDEX IF NOT EXISTS idx_council_agents_council_lookup ON council_agents(council_id);

-- Additional performance indexes based on common query patterns

-- Teams table - index on status and task relationships
CREATE INDEX IF NOT EXISTS idx_teams_status_task ON teams(status, current_task_id);

-- Repository webhooks - index for active webhook lookups
CREATE INDEX IF NOT EXISTS idx_repository_webhooks_active_repo ON repository_webhooks(is_active, repository_id);

-- Repository sync logs - index for recent sync monitoring
CREATE INDEX IF NOT EXISTS idx_repository_sync_logs_recent ON repository_sync_logs(repository_id, created_at DESC);

-- Task assignments - index for active assignments
CREATE INDEX IF NOT EXISTS idx_task_assignments_active ON team_assignments(status, team_id, agent_id) WHERE status = 'active';

-- Council agents - index for deployment status monitoring
CREATE INDEX IF NOT EXISTS idx_council_agents_deployment_status ON council_agents(deployed, status, council_id);

-- Performance statistics collection support
-- Index for task completion analysis
CREATE INDEX IF NOT EXISTS idx_tasks_completion_analysis ON tasks(status, completed_at, assigned_team_id) WHERE completed_at IS NOT NULL;

-- Index for agent performance monitoring
CREATE INDEX IF NOT EXISTS idx_agents_performance_monitoring ON agents(status, last_seen, updated_at) WHERE status IN ('available', 'busy', 'error');