-- ====================================================================
-- STEP 25: INDEXES
-- ====================================================================

-- Locations
CREATE INDEX idx_locations_active ON locations(is_active);
CREATE INDEX idx_locations_type ON locations(location_type);

-- Module & Permissions
CREATE INDEX idx_module_meta_active ON module_meta(is_active);
CREATE INDEX idx_field_meta_table ON field_meta(table_name(100));
CREATE INDEX idx_role_perms_role ON role_permissions(role_id);
CREATE INDEX idx_role_perms_table ON role_permissions(table_name(100));
CREATE INDEX idx_emp_override_emp ON employee_permission_overrides(employee_id);
CREATE INDEX idx_emp_override_table ON employee_permission_overrides(table_name(100));

-- Auth & Sessions
CREATE INDEX idx_sessions_employee ON user_sessions(employee_id);
CREATE INDEX idx_sessions_token_hash ON user_sessions(token_hash(255));
CREATE INDEX idx_sessions_active ON user_sessions(expires_at, revoked_at);
CREATE INDEX idx_pwd_reset_employee ON password_reset_tokens(employee_id);
CREATE INDEX idx_pwd_reset_token ON password_reset_tokens(token_hash(255));

-- Employees
CREATE INDEX idx_employees_location ON employees(works_at_location_id);
CREATE INDEX idx_employees_dept ON employees(department_id);
CREATE INDEX idx_employees_manager ON employees(manager_id);
CREATE INDEX idx_employees_status ON employees(status);
CREATE INDEX idx_employees_email ON employees(work_email);

-- Employee Roles
CREATE INDEX idx_emp_roles_employee ON employee_roles(employee_id);
CREATE INDEX idx_emp_roles_role ON employee_roles(role_id);

-- Leave
CREATE INDEX idx_leave_req_employee ON leave_requests(employee_id);
CREATE INDEX idx_leave_req_location ON leave_requests(location_id);
CREATE INDEX idx_leave_req_status ON leave_requests(status);
CREATE INDEX idx_leave_req_dates ON leave_requests(start_date, end_date);
CREATE INDEX idx_leave_bal_employee ON leave_balances(employee_id);
CREATE INDEX idx_leave_bal_year ON leave_balances(employee_id, year);

-- Payroll
CREATE INDEX idx_payroll_runs_location ON payroll_runs(location_id);
CREATE INDEX idx_payslips_employee ON payslips(employee_id);
CREATE INDEX idx_payslips_location ON payslips(location_id);
CREATE INDEX idx_payslips_run ON payslips(payroll_run_id);
CREATE INDEX idx_payslips_status ON payslips(status);

-- Loans
CREATE INDEX idx_loans_employee ON loan_applications(employee_id);
CREATE INDEX idx_loans_location ON loan_applications(location_id);
CREATE INDEX idx_loans_status ON loan_applications(status);
CREATE INDEX idx_loan_inst_loan ON loan_installments(loan_id);
CREATE INDEX idx_loan_inst_employee ON loan_installments(employee_id);
CREATE INDEX idx_loan_inst_status ON loan_installments(status);

-- Expenses
CREATE INDEX idx_expenses_location ON expenses(location_id);
CREATE INDEX idx_expenses_submitted_by ON expenses(submitted_by);
CREATE INDEX idx_expenses_department ON expenses(department_id);
CREATE INDEX idx_expenses_status ON expenses(status);
CREATE INDEX idx_expenses_date ON expenses(expense_date);
CREATE INDEX idx_expenses_category ON expenses(category);
CREATE INDEX idx_expenses_project ON expenses(project_id);

-- Performance
CREATE INDEX idx_perf_reviews_employee ON performance_reviews(employee_id);
CREATE INDEX idx_perf_reviews_cycle ON performance_reviews(cycle_id);

-- Tasks & Projects
CREATE INDEX idx_projects_location ON projects(location_id);
CREATE INDEX idx_tasks_location ON tasks(location_id);
CREATE INDEX idx_tasks_assigned ON tasks(assigned_to);
CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_due_date ON tasks(due_date);

-- Notifications
CREATE INDEX idx_notif_recipient ON notifications(recipient_id);
CREATE INDEX idx_notif_unread ON notifications(recipient_id, is_read);

-- Audit
CREATE INDEX idx_audit_actor ON audit_logs(actor_id);
CREATE INDEX idx_audit_location ON audit_logs(location_id);
CREATE INDEX idx_audit_entity ON audit_logs(entity_type(100), entity_id);
CREATE INDEX idx_audit_action ON audit_logs(action);
CREATE INDEX idx_audit_created ON audit_logs(created_at DESC);

-- Documents expiry alerts
CREATE INDEX idx_emp_docs_expiry ON employee_documents(expiry_date);

-- Announcements
CREATE INDEX idx_announcements_published ON announcements(is_published, publish_at);

-- ============================================================
-- Database Index Recommendations
-- Run these to support the permission queries efficiently
-- ============================================================

-- role_permissions: the most queried table
-- Query pattern: WHERE role_id = ? AND column_name IS NULL
-- Query pattern: WHERE role_id IN (...) AND column_name IS NULL/NOT NULL
CREATE INDEX IF NOT EXISTS idx_role_permissions_role_col
  ON role_permissions (role_id, column_name);

-- employee_roles: used in every permission check JOIN
-- Query pattern: WHERE employee_id = ? AND (expires_at IS NULL OR expires_at > NOW())
CREATE INDEX IF NOT EXISTS idx_employee_roles_employee_expires
  ON employee_roles (employee_id, expires_at);

-- roles: ORDER BY display_order, name is used on every list fetch
CREATE INDEX IF NOT EXISTS idx_roles_order_name
  ON roles (display_order, name);

-- roles: code must be unique (also speeds up duplicate-code check on create)
-- Only add if not already a UNIQUE column:
-- ALTER TABLE roles ADD UNIQUE INDEX uq_roles_code (code);