-- Add indexes to optimize Login and Hierarchy lookups
-- 1. Optimize Territory Version lookup (filtering by company and dates)
CREATE INDEX idx_territoryversions_company_valid ON territoryversions (company_id, enabled, valid_from, valid_to);

-- 2. Optimize user territory lookup
CREATE INDEX idx_employeeterritories_employee_id ON employeeterritories (employee_id);
CREATE INDEX idx_employeeterritories_territory_id ON employeeterritories (territory_id);

-- 3. Optimize Designation Version lookup
CREATE INDEX idx_designationversions_company_valid ON designationversions (company_id, enabled, valid_from, valid_to);

-- 4. Optimize user designation lookup -- Assuming table name is employeedesignations or similar, need to verify.
-- Actually, designation_id is on the Employee table directly.
CREATE INDEX idx_employees_designation_id ON employees (designation_id);
CREATE INDEX idx_employees_user_id ON employees (user_id);
CREATE INDEX idx_employees_company_dept ON employees (company_id, department_id);

-- 5. Optimize Bank Account lookup for hierarchy
CREATE INDEX idx_bankaccounts_employee_enabled ON bankaccounts (employee_id, enabled);

-- 6. Optimize User lookup
CREATE INDEX idx_users_email_enabled ON users (email, enabled);
CREATE INDEX idx_users_phone_enabled ON users (phone_number, enabled);