-- Database Indexes for Employee Query Performance Optimization
-- Run these CREATE INDEX statements to improve query performance

-- Primary indexes (if not already present)
CREATE INDEX IF NOT EXISTS idx_employee_details_company_active ON employee_details(company_id, is_active);
CREATE INDEX IF NOT EXISTS idx_employee_details_joining_date ON employee_details(joining_date);
CREATE INDEX IF NOT EXISTS idx_employee_details_user_id ON employee_details(user_id);

-- User table indexes for search optimization
CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);

-- Role-related indexes
CREATE INDEX IF NOT EXISTS idx_role_user_user_id ON role_user(user_id);
CREATE INDEX IF NOT EXISTS idx_role_user_role_id ON role_user(role_id);
CREATE INDEX IF NOT EXISTS idx_roles_id ON roles(id);

-- Media-related indexes
CREATE INDEX IF NOT EXISTS idx_media_link_object ON media_link(object_id, object_type);
CREATE INDEX IF NOT EXISTS idx_media_id ON media(id);

-- Composite indexes for better performance
CREATE INDEX IF NOT EXISTS idx_employee_details_composite ON employee_details(company_id, is_active, joining_date);
CREATE INDEX IF NOT EXISTS idx_users_search ON users(name, email);

-- Full-text search index for better search performance (MySQL 5.6+)
-- ALTER TABLE users ADD FULLTEXT INDEX ft_users_search (name, email);

-- Analyze table statistics (MySQL)
-- ANALYZE TABLE employee_details, users, role_user, roles, media_link, media;

-- For PostgreSQL, use:
-- CREATE INDEX CONCURRENTLY idx_employee_details_company_active ON employee_details(company_id, is_active);
-- CREATE INDEX CONCURRENTLY idx_employee_details_joining_date ON employee_details(joining_date);
-- CREATE INDEX CONCURRENTLY idx_users_name ON users(name);
-- CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- CREATE INDEX CONCURRENTLY idx_role_user_user_id ON role_user(user_id);
-- CREATE INDEX CONCURRENTLY idx_role_user_role_id ON role_user(role_id);
