-- Database Indexes Optimization
-- تحسين الأداء بإضافة indexes للاستعلامات الشائعة

-- Indexes للطلبات
CREATE INDEX IF NOT EXISTS idx_lab_orders_tenant_doctor_date ON lab_orders(tenant_id, doctor_id, order_date);
CREATE INDEX IF NOT EXISTS idx_lab_orders_tenant_status ON lab_orders(tenant_id, status);
CREATE INDEX IF NOT EXISTS idx_lab_orders_patient_tenant ON lab_orders(patient_id, tenant_id);
CREATE INDEX IF NOT EXISTS idx_lab_orders_order_number ON lab_orders(order_number);

-- Indexes للنتائج
CREATE INDEX IF NOT EXISTS idx_results_order_item_flag ON results(order_item_id, flag);
CREATE INDEX IF NOT EXISTS idx_results_order_item_id ON results(order_item_id);

-- Indexes للمرضى
CREATE INDEX IF NOT EXISTS idx_patients_tenant_code ON patients(tenant_id, patient_code);
CREATE INDEX IF NOT EXISTS idx_patients_tenant_phone ON patients(tenant_id, phone);
CREATE INDEX IF NOT EXISTS idx_patients_tenant_name ON patients(tenant_id, full_name);

-- Indexes لـ order_items
CREATE INDEX IF NOT EXISTS idx_order_items_order_test ON order_items(order_id, test_id);
CREATE INDEX IF NOT EXISTS idx_order_items_test ON order_items(test_id);

-- Indexes للفواتير
CREATE INDEX IF NOT EXISTS idx_invoices_order_id ON invoices(order_id);
CREATE INDEX IF NOT EXISTS idx_invoices_issue_date ON invoices(issue_date);

-- Indexes للعيّنات
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);

-- Indexes للأطباء
CREATE INDEX IF NOT EXISTS idx_doctors_tenant ON doctors(tenant_id);
CREATE INDEX IF NOT EXISTS idx_doctors_clinic ON doctors(clinic_name);

-- Indexes للـ audit_log
CREATE INDEX IF NOT EXISTS idx_audit_log_created_at ON audit_log(created_at);
CREATE INDEX IF NOT EXISTS idx_audit_log_user_action ON audit_log(user_id, action);
CREATE INDEX IF NOT EXISTS idx_audit_log_entity ON audit_log(entity_type, entity_id);

-- Indexes للـ doctor_accounts
CREATE INDEX IF NOT EXISTS idx_doctor_accounts_tenant ON doctor_accounts(tenant_id);
CREATE INDEX IF NOT EXISTS idx_doctor_accounts_doctor ON doctor_accounts(doctor_id);

-- Indexes للـ whatsapp_send_queue
CREATE INDEX IF NOT EXISTS idx_whatsapp_queue_status ON whatsapp_send_queue(status, created_at);
CREATE INDEX IF NOT EXISTS idx_whatsapp_queue_tenant ON whatsapp_send_queue(tenant_id);

-- Indexes للـ loyalty_transactions
CREATE INDEX IF NOT EXISTS idx_loyalty_transactions_patient ON loyalty_transactions(patient_id, created_at);
CREATE INDEX IF NOT EXISTS idx_loyalty_transactions_order ON loyalty_transactions(order_id);
