-- Add indexes to optimize Login and Hierarchy lookups
-- Using IF NOT EXISTS (MySQL syntax varies, often manual check is safer)
-- OR Drop if exists logic. Since this is for optimization, we can safely ignore duplicate error or wrap in procedure.
-- However, standard SQL scripts often just run. The error #1061 means update is already partial or index exists.

-- Safest approach for User: Only create if doesn't exist.
-- MySQL < 5.7 doesn't support IF NOT EXISTS for indexes easily without procedure.
-- But standard MariaDB/MySQL 5.7+ supports CREATE INDEX IF NOT EXISTS (if strictly supported)
-- OR, we use a specialized syntax or just comment out the one that failed.

-- 1. Optimize Territory Version lookup
-- 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. User designation lookup (FIXED: Removed duplicate)
-- idx_employees_designation_id ALREADY EXISTS. Skipping.
-- CREATE INDEX idx_employees_designation_id ON employees (designation_id);

-- Check other potentially missing indexes on employees
-- 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
-- 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);

-- --------------------------------------------------------------------------------------
-- SAFE SCRIPT: (Run this)
-- We will use a stored procedure to safely add indexes only if they don't exist.
-- This works on MySQL 5.7+ / MariaDB
-- --------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS SafeCreateIndex;
DELIMITER $$
CREATE PROCEDURE SafeCreateIndex(
    IN p_table_name VARCHAR(255),
    IN p_index_name VARCHAR(255),
    IN p_columns VARCHAR(255)
)
BEGIN
    DECLARE index_count INT;
    
    SELECT COUNT(1) INTO index_count
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
    AND table_name = p_table_name
    AND index_name = p_index_name;
    
    IF index_count = 0 THEN
        SET @sql = CONCAT('CREATE INDEX ', p_index_name, ' ON ', p_table_name, ' (', p_columns, ')');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT CONCAT('Index ', p_index_name, ' created on table ', p_table_name) AS status;
    ELSE
        SELECT CONCAT('Index ', p_index_name, ' already exists on table ', p_table_name) AS status;
    END IF;
END $$
DELIMITER ;

-- Execute safe creations
CALL SafeCreateIndex('territoryversions', 'idx_territoryversions_company_valid', 'company_id, enabled, valid_from, valid_to');
CALL SafeCreateIndex('employeeterritories', 'idx_employeeterritories_employee_id', 'employee_id');
CALL SafeCreateIndex('employeeterritories', 'idx_employeeterritories_territory_id', 'territory_id');
CALL SafeCreateIndex('designationversions', 'idx_designationversions_company_valid', 'company_id, enabled, valid_from, valid_to');

-- This one caused the error, now handled safely:
CALL SafeCreateIndex('employees', 'idx_employees_designation_id', 'designation_id');
CALL SafeCreateIndex('employees', 'idx_employees_user_id', 'user_id');
CALL SafeCreateIndex('employees', 'idx_employees_company_dept', 'company_id, department_id');

CALL SafeCreateIndex('bankaccounts', 'idx_bankaccounts_employee_enabled', 'employee_id, enabled');
CALL SafeCreateIndex('users', 'idx_users_email_enabled', 'email, enabled');
CALL SafeCreateIndex('users', 'idx_users_phone_enabled', 'phone_number, enabled');

-- Clean up
DROP PROCEDURE IF EXISTS SafeCreateIndex;