-- Additional Indexes for Performance Optimization
-- This migration adds additional indexes beyond those in the initial schema

-- Note: Basic indexes were already created in 001_initial_schema.sql
-- This file is for any additional performance indexes discovered during development

-- Composite indexes for common query patterns
CREATE INDEX IF NOT EXISTS idx_tickets_company_status ON tickets(company_id, status) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_tickets_assigned_date ON tickets(assigned_resource_id, create_date DESC) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_tasks_project_status ON tasks(project_id, status) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_config_items_company_active ON configuration_items(company_id, is_active) WHERE is_deleted = false;

-- Full-text search indexes (if needed in future)
-- CREATE INDEX IF NOT EXISTS idx_tickets_title_fts ON tickets USING gin(to_tsvector('english', title));
-- CREATE INDEX IF NOT EXISTS idx_tickets_description_fts ON tickets USING gin(to_tsvector('english', description));

-- Partial indexes for active records only (more efficient)
CREATE INDEX IF NOT EXISTS idx_companies_active_only ON companies(id) WHERE is_active = true AND is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_resources_active_only ON resources(id) WHERE is_active = true AND is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_contacts_active_only ON contacts(company_id) WHERE is_active = true AND is_deleted = false;

-- Indexes for sync operations
CREATE INDEX IF NOT EXISTS idx_companies_last_modified ON companies(last_tracked_modification_date_time DESC) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_tickets_last_activity ON tickets(last_activity_date DESC) WHERE is_deleted = false;
CREATE INDEX IF NOT EXISTS idx_contacts_last_modified ON contacts(last_modified_date DESC) WHERE is_deleted = false;

COMMENT ON INDEX idx_tickets_company_status IS 'Optimizes queries filtering tickets by company and status';
COMMENT ON INDEX idx_config_items_company_active IS 'Optimizes queries for active config items by company';