<?php
/**
 * Migration: إضافة Indexes لتحسين الأداء
 * 
 * هذا الملف يضيف indexes على الأعمدة التي يتم البحث فيها بشكل متكرر
 * لتحسين سرعة الاستعلامات
 */

return [
    'up' => function($db) {
        $indexes = [
            // patients table
            "CREATE INDEX IF NOT EXISTS idx_patients_tenant_id ON patients(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_patients_patient_code ON patients(patient_code)",
            "CREATE INDEX IF NOT EXISTS idx_patients_phone ON patients(phone)",
            "CREATE INDEX IF NOT EXISTS idx_patients_full_name ON patients(full_name)",
            "CREATE INDEX IF NOT EXISTS idx_patients_created_at ON patients(created_at)",
            
            // lab_orders table
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_tenant_id ON lab_orders(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_order_number ON lab_orders(order_number)",
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_patient_id ON lab_orders(patient_id)",
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_doctor_id ON lab_orders(doctor_id)",
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_order_date ON lab_orders(order_date)",
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_status ON lab_orders(status)",
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_payment_status ON lab_orders(payment_status)",
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_branch_id ON lab_orders(branch_id)",
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_created_at ON lab_orders(created_at)",
            
            // order_items table
            "CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items(order_id)",
            "CREATE INDEX IF NOT EXISTS idx_order_items_test_id ON order_items(test_id)",
            
            // test_results table
            "CREATE INDEX IF NOT EXISTS idx_test_results_order_id ON test_results(order_id)",
            "CREATE INDEX IF NOT EXISTS idx_test_results_test_id ON test_results(test_id)",
            "CREATE INDEX IF NOT EXISTS idx_test_results_status ON test_results(status)",
            "CREATE INDEX IF NOT EXISTS idx_test_results_created_at ON test_results(created_at)",
            
            // samples table
            "CREATE INDEX IF NOT EXISTS idx_samples_order_id ON samples(order_id)",
            "CREATE INDEX IF NOT EXISTS idx_samples_sample_code ON samples(sample_code)",
            "CREATE INDEX IF NOT EXISTS idx_samples_barcode ON samples(barcode)",
            "CREATE INDEX IF NOT EXISTS idx_samples_status ON samples(status)",
            
            // doctors table
            "CREATE INDEX IF NOT EXISTS idx_doctors_tenant_id ON doctors(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_doctors_full_name ON doctors(full_name)",
            "CREATE INDEX IF NOT EXISTS idx_doctors_clinic_name ON doctors(clinic_name)",
            
            // doctor_accounts table
            "CREATE INDEX IF NOT EXISTS idx_doctor_accounts_doctor_id ON doctor_accounts(doctor_id)",
            "CREATE INDEX IF NOT EXISTS idx_doctor_accounts_tenant_id ON doctor_accounts(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_doctor_accounts_is_active ON doctor_accounts(is_active)",
            "CREATE INDEX IF NOT EXISTS idx_doctor_accounts_booking_token ON doctor_accounts(booking_token)",
            
            // users table
            "CREATE INDEX IF NOT EXISTS idx_users_tenant_id ON users(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)",
            "CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)",
            "CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active)",
            
            // tests table
            "CREATE INDEX IF NOT EXISTS idx_tests_tenant_id ON tests(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_tests_name ON tests(name)",
            "CREATE INDEX IF NOT EXISTS idx_tests_category_id ON tests(category_id)",
            "CREATE INDEX IF NOT EXISTS idx_tests_is_active ON tests(is_active)",
            
            // invoices table
            "CREATE INDEX IF NOT EXISTS idx_invoices_order_id ON invoices(order_id)",
            "CREATE INDEX IF NOT EXISTS idx_invoices_invoice_number ON invoices(invoice_number)",
            "CREATE INDEX IF NOT EXISTS idx_invoices_issue_date ON invoices(issue_date)",
            
            // audit_log table
            "CREATE INDEX IF NOT EXISTS idx_audit_log_user_id ON audit_log(user_id)",
            "CREATE INDEX IF NOT EXISTS idx_audit_log_tenant_id ON audit_log(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_audit_log_action ON audit_log(action)",
            "CREATE INDEX IF NOT EXISTS idx_audit_log_entity_type ON audit_log(entity_type)",
            "CREATE INDEX IF NOT EXISTS idx_audit_log_entity_id ON audit_log(entity_id)",
            "CREATE INDEX IF NOT EXISTS idx_audit_log_created_at ON audit_log(created_at)",
            
            // inventory_items table
            "CREATE INDEX IF NOT EXISTS idx_inventory_items_tenant_id ON inventory_items(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_inventory_items_name ON inventory_items(name)",
            "CREATE INDEX IF NOT EXISTS idx_inventory_items_category ON inventory_items(category)",
            "CREATE INDEX IF NOT EXISTS idx_inventory_items_expiry_date ON inventory_items(expiry_date)",
            
            // branches table
            "CREATE INDEX IF NOT EXISTS idx_branches_tenant_id ON branches(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_branches_is_active ON branches(is_active)",
            
            // settings table
            "CREATE INDEX IF NOT EXISTS idx_settings_tenant_id ON settings(tenant_id)",
            "CREATE INDEX IF NOT EXISTS idx_settings_setting_key ON settings(setting_key)",
            
            // loyalty_transactions table
            "CREATE INDEX IF NOT EXISTS idx_loyalty_transactions_patient_id ON loyalty_transactions(patient_id)",
            "CREATE INDEX IF NOT EXISTS idx_loyalty_transactions_order_id ON loyalty_transactions(order_id)",
            "CREATE INDEX IF NOT EXISTS idx_loyalty_transactions_created_at ON loyalty_transactions(created_at)",
            
            // rate_limit_logs table
            "CREATE INDEX IF NOT EXISTS idx_rate_limit_logs_identifier ON rate_limit_logs(identifier)",
            "CREATE INDEX IF NOT EXISTS idx_rate_limit_logs_action_type ON rate_limit_logs(action_type)",
            "CREATE INDEX IF NOT EXISTS idx_rate_limit_logs_created_at ON rate_limit_logs(created_at)",
            
            // doctor_appointments table
            "CREATE INDEX IF NOT EXISTS idx_doctor_appointments_doctor_id ON doctor_appointments(doctor_id)",
            "CREATE INDEX IF NOT EXISTS idx_doctor_appointments_appointment_date ON doctor_appointments(appointment_date)",
            "CREATE INDEX IF NOT EXISTS idx_doctor_appointments_status ON doctor_appointments(status)",
            
            // doctor_reminders table
            "CREATE INDEX IF NOT EXISTS idx_doctor_reminders_doctor_id ON doctor_reminders(doctor_id)",
            "CREATE INDEX IF NOT EXISTS idx_doctor_reminders_reminder_date ON doctor_reminders(reminder_date)",
            
            // doctor_activity_log table
            "CREATE INDEX IF NOT EXISTS idx_doctor_activity_log_doctor_account_id ON doctor_activity_log(doctor_account_id)",
            "CREATE INDEX IF NOT EXISTS idx_doctor_activity_log_created_at ON doctor_activity_log(created_at)",
            
            // Composite indexes for common query patterns
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_tenant_status ON lab_orders(tenant_id, status)",
            "CREATE INDEX IF NOT EXISTS idx_lab_orders_tenant_date ON lab_orders(tenant_id, order_date)",
            "CREATE INDEX IF NOT EXISTS idx_patients_tenant_code ON patients(tenant_id, patient_code)",
            "CREATE INDEX IF NOT EXISTS idx_test_results_order_test ON test_results(order_id, test_id)",
            "CREATE INDEX IF NOT EXISTS idx_audit_log_tenant_created ON audit_log(tenant_id, created_at)",
        ];
        
        foreach ($indexes as $sql) {
            try {
                $db->exec($sql);
            } catch (PDOException $e) {
                // Skip if index already exists or table doesn't exist
                if (strpos($e->getMessage(), 'already exists') === false && 
                    strpos($e->getMessage(), "doesn't exist") === false) {
                    error_log("Failed to create index: " . $e->getMessage());
                }
            }
        }
        
        return true;
    },
    
    'down' => function($db) {
        // Drop indexes (optional - usually not needed)
        $indexes = [
            "DROP INDEX IF EXISTS idx_patients_tenant_id",
            "DROP INDEX IF EXISTS idx_patients_patient_code",
            "DROP INDEX IF EXISTS idx_patients_phone",
            "DROP INDEX IF EXISTS idx_patients_full_name",
            "DROP INDEX IF EXISTS idx_patients_created_at",
            "DROP INDEX IF EXISTS idx_lab_orders_tenant_id",
            "DROP INDEX IF EXISTS idx_lab_orders_order_number",
            "DROP INDEX IF EXISTS idx_lab_orders_patient_id",
            "DROP INDEX IF EXISTS idx_lab_orders_doctor_id",
            "DROP INDEX IF EXISTS idx_lab_orders_order_date",
            "DROP INDEX IF EXISTS idx_lab_orders_status",
            "DROP INDEX IF EXISTS idx_lab_orders_payment_status",
            "DROP INDEX IF EXISTS idx_lab_orders_branch_id",
            "DROP INDEX IF EXISTS idx_lab_orders_created_at",
            "DROP INDEX IF EXISTS idx_order_items_order_id",
            "DROP INDEX IF EXISTS idx_order_items_test_id",
            "DROP INDEX IF EXISTS idx_test_results_order_id",
            "DROP INDEX IF EXISTS idx_test_results_test_id",
            "DROP INDEX IF EXISTS idx_test_results_status",
            "DROP INDEX IF EXISTS idx_test_results_created_at",
            "DROP INDEX IF EXISTS idx_samples_order_id",
            "DROP INDEX IF EXISTS idx_samples_sample_code",
            "DROP INDEX IF EXISTS idx_samples_barcode",
            "DROP INDEX IF EXISTS idx_samples_status",
            "DROP INDEX IF EXISTS idx_doctors_tenant_id",
            "DROP INDEX IF EXISTS idx_doctors_full_name",
            "DROP INDEX IF EXISTS idx_doctors_clinic_name",
            "DROP INDEX IF EXISTS idx_doctor_accounts_doctor_id",
            "DROP INDEX IF EXISTS idx_doctor_accounts_tenant_id",
            "DROP INDEX IF EXISTS idx_doctor_accounts_is_active",
            "DROP INDEX IF EXISTS idx_doctor_accounts_booking_token",
            "DROP INDEX IF EXISTS idx_users_tenant_id",
            "DROP INDEX IF EXISTS idx_users_username",
            "DROP INDEX IF EXISTS idx_users_email",
            "DROP INDEX IF EXISTS idx_users_is_active",
            "DROP INDEX IF EXISTS idx_tests_tenant_id",
            "DROP INDEX IF EXISTS idx_tests_name",
            "DROP INDEX IF EXISTS idx_tests_category_id",
            "DROP INDEX IF EXISTS idx_tests_is_active",
            "DROP INDEX IF EXISTS idx_invoices_order_id",
            "DROP INDEX IF EXISTS idx_invoices_invoice_number",
            "DROP INDEX IF EXISTS idx_invoices_issue_date",
            "DROP INDEX IF EXISTS idx_audit_log_user_id",
            "DROP INDEX IF EXISTS idx_audit_log_tenant_id",
            "DROP INDEX IF EXISTS idx_audit_log_action",
            "DROP INDEX IF EXISTS idx_audit_log_entity_type",
            "DROP INDEX IF EXISTS idx_audit_log_entity_id",
            "DROP INDEX IF EXISTS idx_audit_log_created_at",
            "DROP INDEX IF EXISTS idx_inventory_items_tenant_id",
            "DROP INDEX IF EXISTS idx_inventory_items_name",
            "DROP INDEX IF EXISTS idx_inventory_items_category",
            "DROP INDEX IF EXISTS idx_inventory_items_expiry_date",
            "DROP INDEX IF EXISTS idx_branches_tenant_id",
            "DROP INDEX IF EXISTS idx_branches_is_active",
            "DROP INDEX IF EXISTS idx_settings_tenant_id",
            "DROP INDEX IF EXISTS idx_settings_setting_key",
            "DROP INDEX IF EXISTS idx_loyalty_transactions_patient_id",
            "DROP INDEX IF EXISTS idx_loyalty_transactions_order_id",
            "DROP INDEX IF EXISTS idx_loyalty_transactions_created_at",
            "DROP INDEX IF EXISTS idx_rate_limit_logs_identifier",
            "DROP INDEX IF EXISTS idx_rate_limit_logs_action_type",
            "DROP INDEX IF EXISTS idx_rate_limit_logs_created_at",
            "DROP INDEX IF EXISTS idx_doctor_appointments_doctor_id",
            "DROP INDEX IF EXISTS idx_doctor_appointments_appointment_date",
            "DROP INDEX IF EXISTS idx_doctor_appointments_status",
            "DROP INDEX IF EXISTS idx_doctor_reminders_doctor_id",
            "DROP INDEX IF EXISTS idx_doctor_reminders_reminder_date",
            "DROP INDEX IF EXISTS idx_doctor_activity_log_doctor_account_id",
            "DROP INDEX IF EXISTS idx_doctor_activity_log_created_at",
            "DROP INDEX IF EXISTS idx_lab_orders_tenant_status",
            "DROP INDEX IF EXISTS idx_lab_orders_tenant_date",
            "DROP INDEX IF EXISTS idx_patients_tenant_code",
            "DROP INDEX IF EXISTS idx_test_results_order_test",
            "DROP INDEX IF EXISTS idx_audit_log_tenant_created",
        ];
        
        foreach ($indexes as $sql) {
            try {
                $db->exec($sql);
            } catch (PDOException $e) {
                // Ignore errors
            }
        }
        
        return true;
    }
];
