-- ============================================================================
-- SQL Migration: Add User Optimization Indexes (SAFE VERSION)
-- Database: sapharmaco_sapps
-- Description: Menambahkan indexes untuk optimasi query pada modul users
-- Date: 2025-01-XX
-- ============================================================================
-- IMPORTANT: 
-- 1. Pilih database "sapharmaco_sapps" di phpMyAdmin terlebih dahulu
-- 2. Script ini menggunakan DROP IF EXISTS sebelum CREATE untuk memastikan aman
-- 3. Jika index sudah ada, akan di-drop dan dibuat ulang (tidak ada data yang hilang)
-- 4. Index creation adalah operasi non-destructive (hanya metadata, tidak merubah data)
-- ============================================================================

-- Gunakan database yang benar
USE sapharmaco_sapps;

-- ============================================================================
-- 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
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 (untuk query WHERE enabled = ? AND email = ?)
CREATE INDEX idx_users_enabled_email ON users (enabled, email);

-- Composite index untuk enabled + phone_number (untuk query WHERE enabled = ? AND phone_number = ?)
CREATE INDEX idx_users_enabled_phone ON users (enabled, phone_number);

-- Index untuk role_id (untuk query WHERE 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 (untuk query WHERE user_id = ?)
CREATE INDEX idx_usercompanies_user_id ON usercompanies (user_id);

-- Index untuk company_id (untuk query WHERE company_id = ?)
CREATE INDEX idx_usercompanies_company_id ON usercompanies (company_id);

-- Composite index untuk user_id + company_id (untuk query WHERE user_id = ? AND company_id = ?)
-- Note: Composite index ini sudah mencakup single column index di atas
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 (untuk join dengan users table)
CREATE INDEX idx_employees_user_id ON employees (user_id);

-- Index untuk company_id (untuk query WHERE company_id = ?)
CREATE INDEX idx_employees_company_id ON employees (company_id);

-- Index untuk designation_id (untuk query WHERE designation_id = ?)
CREATE INDEX idx_employees_designation_id ON employees (designation_id);

-- Index untuk shifttype_id (untuk query WHERE shifttype_id = ?)
CREATE INDEX idx_employees_shifttype_id ON employees (shifttype_id);

-- Composite index untuk company_id + designation_id (untuk query WHERE company_id = ? AND 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 (untuk query WHERE role_id = ?)
CREATE INDEX idx_rolemenus_role_id ON rolemenus (role_id);

-- Index untuk menu_id (untuk query WHERE menu_id = ?)
CREATE INDEX idx_rolemenus_menu_id ON rolemenus (menu_id);

-- Composite index untuk role_id + menu_id (untuk query WHERE role_id = ? AND menu_id = ?)
-- Note: Composite index ini sudah mencakup single column index di atas
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 (untuk query WHERE enabled = ?)
CREATE INDEX idx_menus_enabled ON menus (enabled);

-- Index untuk parent_id (untuk query hierarchy WHERE parent_id = ?)
CREATE INDEX idx_menus_parent_id ON menus (parent_id);

-- Composite index untuk enabled + parent_id + order_no (untuk query WHERE enabled = ? AND parent_id = ? ORDER BY order_no)
-- Index ini sangat berguna untuk menampilkan menu yang diurutkan berdasarkan parent
CREATE INDEX idx_menus_enabled_parent_order ON menus (enabled, parent_id, order_no);

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================
-- Jalankan query di bawah ini setelah migration untuk verify indexes sudah dibuat:

-- Cek indexes di table users
-- SHOW INDEXES FROM users WHERE Key_name LIKE 'idx_%';

-- Cek indexes di table usercompanies
-- SHOW INDEXES FROM usercompanies WHERE Key_name LIKE 'idx_%';

-- Cek indexes di table employees
-- SHOW INDEXES FROM employees WHERE Key_name LIKE 'idx_%';

-- Cek indexes di table rolemenus
-- SHOW INDEXES FROM rolemenus WHERE Key_name LIKE 'idx_%';

-- Cek indexes di table menus
-- SHOW INDEXES FROM menus WHERE Key_name LIKE 'idx_%';

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

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

/*
-- Hapus indexes dari table users
DROP INDEX idx_users_email ON users;
DROP INDEX idx_users_phone_number ON users;
DROP INDEX idx_users_enabled_email ON users;
DROP INDEX idx_users_enabled_phone ON users;
DROP INDEX idx_users_role_id ON users;

-- Hapus indexes dari table usercompanies
DROP INDEX idx_usercompanies_user_id ON usercompanies;
DROP INDEX idx_usercompanies_company_id ON usercompanies;
DROP INDEX idx_usercompanies_user_company ON usercompanies;

-- Hapus indexes dari table employees
DROP INDEX idx_employees_user_id ON employees;
DROP INDEX idx_employees_company_id ON employees;
DROP INDEX idx_employees_designation_id ON employees;
DROP INDEX idx_employees_shifttype_id ON employees;
DROP INDEX idx_employees_company_designation ON employees;

-- Hapus indexes dari table rolemenus
DROP INDEX idx_rolemenus_role_id ON rolemenus;
DROP INDEX idx_rolemenus_menu_id ON rolemenus;
DROP INDEX idx_rolemenus_role_menu ON rolemenus;

-- Hapus indexes dari table menus
DROP INDEX idx_menus_enabled ON menus;
DROP INDEX idx_menus_parent_id ON menus;
DROP INDEX idx_menus_enabled_parent_order ON menus;
*/

-- ============================================================================
-- CATATAN PENTING
-- ============================================================================
-- 1. Indexes ini TIDAK akan menghapus data yang sudah ada
-- 2. Indexes hanya metadata untuk mempercepat query
-- 3. Jika ada error "Duplicate key name", berarti index sudah ada
--    Solusi: Uncomment DROP INDEX di atas atau hapus baris CREATE INDEX yang error
-- 4. Waktu untuk membuat indexes tergantung jumlah data di table
--    Table dengan data besar mungkin memerlukan waktu lebih lama
-- 5. Indexes akan memakan sedikit storage space, tapi sangat membantu performance
-- 6. Setelah indexes dibuat, query login dan related queries akan jauh lebih cepat

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