-- Migration: Add Performance Indexes
-- Run this migration to improve query performance on common operations

-- Single-column indexes for filtering
-- These support the most common WHERE clauses in getAllTickets()

-- Status filtering (very common - used in almost every query)
CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets(status);

-- Category and type filtering
CREATE INDEX IF NOT EXISTS idx_tickets_category ON tickets(category);
CREATE INDEX IF NOT EXISTS idx_tickets_type ON tickets(type);

-- Priority filtering
CREATE INDEX IF NOT EXISTS idx_tickets_priority ON tickets(priority);

-- Date-based filtering and sorting
CREATE INDEX IF NOT EXISTS idx_tickets_created_at ON tickets(created_at);
CREATE INDEX IF NOT EXISTS idx_tickets_updated_at ON tickets(updated_at);

-- User filtering
CREATE INDEX IF NOT EXISTS idx_tickets_created_by ON tickets(created_by);
CREATE INDEX IF NOT EXISTS idx_tickets_assigned_to ON tickets(assigned_to);

-- Visibility filtering (used in every authenticated query)
CREATE INDEX IF NOT EXISTS idx_tickets_visibility ON tickets(visibility);

-- Composite indexes for common query patterns
-- These are more efficient than single indexes for combined filters

-- Status + created_at (common sorting with status filter)
CREATE INDEX IF NOT EXISTS idx_tickets_status_created ON tickets(status, created_at);

-- Assigned_to + status (for "my open tickets" queries)
CREATE INDEX IF NOT EXISTS idx_tickets_assigned_status ON tickets(assigned_to, status);

-- Visibility + status (visibility filtering with status)
CREATE INDEX IF NOT EXISTS idx_tickets_visibility_status ON tickets(visibility, status);

-- ticket_comments table
-- Optimize comment retrieval by ticket
CREATE INDEX IF NOT EXISTS idx_comments_ticket_created ON ticket_comments(ticket_id, created_at);

-- Audit log indexes (if audit_log table exists)
-- Optimize audit log queries
CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit_log(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_log(action, created_at);