-- ============================================================================
-- SQL Migration: Add User Optimization Indexes
-- Database: sapharmaco_sapps_v3
-- Description: Menambahkan indexes untuk optimasi query pada modul users
-- Date: 2025-01-XX
-- ============================================================================
-- IMPORTANT: Jalankan script ini di phpMyAdmin dengan memilih database terlebih dahulu
-- Script ini aman untuk dijalankan - akan membuat indexes jika belum ada
-- Catatan: MySQL 5.7+ diperlukan untuk partial indexes dengan WHERE clause
-- ============================================================================

-- Gunakan database yang benar
USE sapharmaco_sapps_v3;

-- ============================================================================
-- 1. INDEXES UNTUK TABLE: users
-- ============================================================================

-- Drop index jika sudah ada (untuk safety - PREVENT DUPLICATE KEY ERROR)
DROP INDEX IF EXISTS idx_users_email ON users;
DROP INDEX IF EXISTS idx_users_phone_number ON users;
DROP INDEX IF EXISTS idx_users_enabled_email ON users;
DROP INDEX IF EXISTS idx_users_enabled_phone ON users;
DROP INDEX IF EXISTS idx_users_role_id ON users;

-- Index untuk email
-- Note: Partial indexes (WHERE clause) hanya didukung di MySQL 8.0.13+
-- Jika MySQL versi < 8.0.13, gunakan index normal tanpa WHERE clause
-- Untuk MySQL 5.7, hapus WHERE clause atau gunakan full index
CREATE INDEX idx_users_email ON users (email);

-- Index untuk phone_number
CREATE INDEX idx_users_phone_number ON users (phone_number);

-- Composite index untuk enabled + email
CREATE INDEX idx_users_enabled_email ON users (enabled, email);

-- Composite index untuk enabled + phone_number
CREATE INDEX idx_users_enabled_phone ON users (enabled, phone_number);

-- Index untuk role_id
CREATE INDEX idx_users_role_id ON users (role_id);

-- ============================================================================
-- 2. INDEXES UNTUK TABLE: usercompanies
-- ============================================================================

-- Drop index jika sudah ada (untuk safety - PREVENT DUPLICATE KEY ERROR)
DROP INDEX IF EXISTS idx_usercompanies_user_id ON usercompanies;
DROP INDEX IF EXISTS idx_usercompanies_company_id ON usercompanies;
DROP INDEX IF EXISTS idx_usercompanies_user_company ON usercompanies;

-- Index untuk user_id
CREATE INDEX idx_usercompanies_user_id ON usercompanies (user_id);

-- Index untuk company_id
CREATE INDEX idx_usercompanies_company_id ON usercompanies (company_id);

-- Composite index untuk user_id + company_id
CREATE INDEX idx_usercompanies_user_company ON usercompanies (user_id, company_id);

-- ============================================================================
-- 3. INDEXES UNTUK TABLE: employees
-- ============================================================================

-- Drop index jika sudah ada (untuk safety - PREVENT DUPLICATE KEY ERROR)
DROP INDEX IF EXISTS idx_employees_user_id ON employees;
DROP INDEX IF EXISTS idx_employees_company_id ON employees;
DROP INDEX IF EXISTS idx_employees_designation_id ON employees;
DROP INDEX IF EXISTS idx_employees_shifttype_id ON employees;
DROP INDEX IF EXISTS idx_employees_company_designation ON employees;

-- Index untuk user_id
CREATE INDEX idx_employees_user_id ON employees (user_id);

-- Index untuk company_id
CREATE INDEX idx_employees_company_id ON employees (company_id);

-- Index untuk designation_id
CREATE INDEX idx_employees_designation_id ON employees (designation_id);

-- Index untuk shifttype_id
CREATE INDEX idx_employees_shifttype_id ON employees (shifttype_id);

-- Composite index untuk company_id + designation_id
CREATE INDEX idx_employees_company_designation ON employees (company_id, designation_id);

-- ============================================================================
-- 4. INDEXES UNTUK TABLE: rolemenus
-- ============================================================================

-- Drop index jika sudah ada (untuk safety - PREVENT DUPLICATE KEY ERROR)
DROP INDEX IF EXISTS idx_rolemenus_role_id ON rolemenus;
DROP INDEX IF EXISTS idx_rolemenus_menu_id ON rolemenus;
DROP INDEX IF EXISTS idx_rolemenus_role_menu ON rolemenus;

-- Index untuk role_id
CREATE INDEX idx_rolemenus_role_id ON rolemenus (role_id);

-- Index untuk menu_id
CREATE INDEX idx_rolemenus_menu_id ON rolemenus (menu_id);

-- Composite index untuk role_id + menu_id
CREATE INDEX idx_rolemenus_role_menu ON rolemenus (role_id, menu_id);

-- ============================================================================
-- 5. INDEXES UNTUK TABLE: menus
-- ============================================================================

-- Drop index jika sudah ada (untuk safety - PREVENT DUPLICATE KEY ERROR)
DROP INDEX IF EXISTS idx_menus_enabled ON menus;
DROP INDEX IF EXISTS idx_menus_parent_id ON menus;
DROP INDEX IF EXISTS idx_menus_enabled_parent_order ON menus;

-- Index untuk enabled
CREATE INDEX idx_menus_enabled ON menus (enabled);

-- Index untuk parent_id
CREATE INDEX idx_menus_parent_id ON menus (parent_id);

-- Composite index untuk enabled + parent_id + order_no
CREATE INDEX idx_menus_enabled_parent_order ON menus (enabled, parent_id, order_no);

-- ============================================================================
-- VERIFICATION QUERIES (Optional - untuk cek indexes yang sudah dibuat)
-- ============================================================================
-- Jalankan query di bawah ini setelah migration untuk verify indexes:

-- SHOW INDEXES FROM users WHERE Key_name LIKE 'idx_%';
-- SHOW INDEXES FROM usercompanies WHERE Key_name LIKE 'idx_%';
-- SHOW INDEXES FROM employees WHERE Key_name LIKE 'idx_%';
-- SHOW INDEXES FROM rolemenus WHERE Key_name LIKE 'idx_%';
-- SHOW INDEXES FROM menus WHERE Key_name LIKE 'idx_%';

-- Atau gunakan query di bawah untuk melihat semua indexes:
-- SELECT 
--     TABLE_NAME,
--     INDEX_NAME,
--     GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS COLUMNS
-- FROM information_schema.STATISTICS
-- WHERE TABLE_SCHEMA = 'sapharmaco_sapps_v3'
--     AND TABLE_NAME IN ('users', 'usercompanies', 'employees', 'rolemenus', 'menus')
--     AND INDEX_NAME LIKE 'idx_%'
-- GROUP BY TABLE_NAME, INDEX_NAME
-- ORDER BY TABLE_NAME, INDEX_NAME;

-- ============================================================================
-- ROLLBACK SCRIPT (Jika perlu menghapus indexes)
-- ============================================================================
-- Jika ingin rollback, jalankan script di bawah ini:

-- DROP INDEX IF EXISTS idx_users_email ON users;
-- DROP INDEX IF EXISTS idx_users_phone_number ON users;
-- DROP INDEX IF EXISTS idx_users_enabled_email ON users;
-- DROP INDEX IF EXISTS idx_users_enabled_phone ON users;
-- DROP INDEX IF EXISTS idx_users_role_id ON users;
-- DROP INDEX IF EXISTS idx_usercompanies_user_id ON usercompanies;
-- DROP INDEX IF EXISTS idx_usercompanies_company_id ON usercompanies;
-- DROP INDEX IF EXISTS idx_usercompanies_user_company ON usercompanies;
-- DROP INDEX IF EXISTS idx_employees_user_id ON employees;
-- DROP INDEX IF EXISTS idx_employees_company_id ON employees;
-- DROP INDEX IF EXISTS idx_employees_designation_id ON employees;
-- DROP INDEX IF EXISTS idx_employees_shifttype_id ON employees;
-- DROP INDEX IF EXISTS idx_employees_company_designation ON employees;
-- DROP INDEX IF EXISTS idx_rolemenus_role_id ON rolemenus;
-- DROP INDEX IF EXISTS idx_rolemenus_menu_id ON rolemenus;
-- DROP INDEX IF EXISTS idx_rolemenus_role_menu ON rolemenus;
-- DROP INDEX IF EXISTS idx_menus_enabled ON menus;
-- DROP INDEX IF EXISTS idx_menus_parent_id ON menus;
-- DROP INDEX IF EXISTS idx_menus_enabled_parent_order ON menus;

-- ============================================================================
-- END OF MIGRATION SCRIPT
-- ============================================================================