-- نظام إدارة العيادة الطبية - ملف إعداد قاعدة البيانات
-- Clinic Management System - Database Setup Script
-- نسخة مبسطة للاستضافة المشتركة - الجداول فقط

-- جدول المستخدمين (Users)
CREATE TABLE `users` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `email_verified_at` timestamp NULL DEFAULT NULL,
    `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `avatar` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `national_id` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `last_login_at` timestamp NULL DEFAULT NULL,
    `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`),
    UNIQUE KEY `users_national_id_unique` (`national_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول أدوار المستخدمين (Roles)
CREATE TABLE `roles` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `guard_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `description` text COLLATE utf8mb4_unicode_ci,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `roles_name_guard_name_unique` (`name`, `guard_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الصلاحيات (Permissions)
CREATE TABLE `permissions` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `guard_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `description` text COLLATE utf8mb4_unicode_ci,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `permissions_name_guard_name_unique` (`name`, `guard_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول ربط الأدوار بالصلاحيات
CREATE TABLE `role_has_permissions` (
    `permission_id` bigint(20) UNSIGNED NOT NULL,
    `role_id` bigint(20) UNSIGNED NOT NULL,
    PRIMARY KEY (`permission_id`, `role_id`),
    KEY `role_has_permissions_role_id_foreign` (`role_id`),
    KEY `role_has_permissions_permission_id_foreign` (`permission_id`),
    CONSTRAINT `role_has_permissions_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
    CONSTRAINT `role_has_permissions_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول ربط المستخدمين بالأدوار
CREATE TABLE `model_has_roles` (
    `role_id` bigint(20) UNSIGNED NOT NULL,
    `model_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `model_id` bigint(20) UNSIGNED NOT NULL,
    PRIMARY KEY (`role_id`, `model_id`, `model_type`),
    KEY `model_has_roles_model_id_foreign` (`model_id`),
    KEY `model_has_roles_model_type_model_id_index` (`model_type`, `model_id`),
    CONSTRAINT `model_has_roles_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول المرضى (Patients)
CREATE TABLE `patients` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED DEFAULT NULL,
    `medical_record_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `national_id` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `date_of_birth` date NOT NULL,
    `gender` enum('male','female') COLLATE utf8mb4_unicode_ci NOT NULL,
    `blood_type` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `allergies` text COLLATE utf8mb4_unicode_ci,
    `chronic_diseases` text COLLATE utf8mb4_unicode_ci,
    `emergency_contact_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `emergency_contact_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `insurance_company` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `insurance_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `patients_medical_record_number_unique` (`medical_record_number`),
    UNIQUE KEY `patients_national_id_unique` (`national_id`),
    KEY `patients_user_id_foreign` (`user_id`),
    CONSTRAINT `patients_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الأطباء (Doctors)
CREATE TABLE `doctors` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `specialization` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `license_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `experience_years` int(11) DEFAULT NULL,
    `qualification` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `consultation_fee` decimal(10,2) DEFAULT 0.00,
    `is_available` tinyint(1) NOT NULL DEFAULT 1,
    `schedule_notes` text COLLATE utf8mb4_unicode_ci,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `doctors_license_number_unique` (`license_number`),
    KEY `doctors_user_id_foreign` (`user_id`),
    CONSTRAINT `doctors_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول المواعيد (Appointments)
CREATE TABLE `appointments` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `patient_id` bigint(20) UNSIGNED NOT NULL,
    `doctor_id` bigint(20) UNSIGNED NOT NULL,
    `appointment_date` date NOT NULL,
    `appointment_time` time NOT NULL,
    `duration` int(11) NOT NULL DEFAULT 30,
    `status` enum('scheduled','confirmed','completed','cancelled','no_show') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'scheduled',
    `reason` text COLLATE utf8mb4_unicode_ci,
    `notes` text COLLATE utf8mb4_unicode_ci,
    `cancellation_reason` text COLLATE utf8mb4_unicode_ci,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `appointments_patient_id_foreign` (`patient_id`),
    KEY `appointments_doctor_id_foreign` (`doctor_id`),
    KEY `appointments_appointment_date_index` (`appointment_date`),
    CONSTRAINT `appointments_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE,
    CONSTRAINT `appointments_doctor_id_foreign` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول السجلات الطبية (Medical Records)
CREATE TABLE `medical_records` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `patient_id` bigint(20) UNSIGNED NOT NULL,
    `doctor_id` bigint(20) UNSIGNED NOT NULL,
    `appointment_id` bigint(20) UNSIGNED DEFAULT NULL,
    `record_date` date NOT NULL,
    `chief_complaint` text COLLATE utf8mb4_unicode_ci,
    `history_of_present_illness` text COLLATE utf8mb4_unicode_ci,
    `physical_examination` text COLLATE utf8mb4_unicode_ci,
    `diagnosis` text COLLATE utf8mb4_unicode_ci,
    `treatment_plan` text COLLATE utf8mb4_unicode_ci,
    `prescription` text COLLATE utf8mb4_unicode_ci,
    `follow_up_date` date DEFAULT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `medical_records_patient_id_foreign` (`patient_id`),
    KEY `medical_records_doctor_id_foreign` (`doctor_id`),
    KEY `medical_records_appointment_id_foreign` (`appointment_id`),
    CONSTRAINT `medical_records_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE,
    CONSTRAINT `medical_records_doctor_id_foreign` FOREIGN KEY (`doctor_id`) REFERENCES `doctors` (`id`) ON DELETE CASCADE,
    CONSTRAINT `medical_records_appointment_id_foreign` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الأدوية (Medications)
CREATE TABLE `medications` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `generic_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `category` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `dosage_form` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `strength` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `description` text COLLATE utf8mb4_unicode_ci,
    `side_effects` text COLLATE utf8mb4_unicode_ci,
    `contraindications` text COLLATE utf8mb4_unicode_ci,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `medications_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الوصفات (Prescriptions)
CREATE TABLE `prescriptions` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `medical_record_id` bigint(20) UNSIGNED NOT NULL,
    `medication_id` bigint(20) UNSIGNED NOT NULL,
    `dosage` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `frequency` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `duration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `instructions` text COLLATE utf8mb4_unicode_ci,
    `is_active` tinyint(1) NOT NULL DEFAULT 1,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `prescriptions_medical_record_id_foreign` (`medical_record_id`),
    KEY `prescriptions_medication_id_foreign` (`medication_id`),
    CONSTRAINT `prescriptions_medical_record_id_foreign` FOREIGN KEY (`medical_record_id`) REFERENCES `medical_records` (`id`) ON DELETE CASCADE,
    CONSTRAINT `prescriptions_medication_id_foreign` FOREIGN KEY (`medication_id`) REFERENCES `medications` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول المدفوعات (Payments)
CREATE TABLE `payments` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `appointment_id` bigint(20) UNSIGNED DEFAULT NULL,
    `patient_id` bigint(20) UNSIGNED NOT NULL,
    `amount` decimal(10,2) NOT NULL,
    `payment_method` enum('cash','credit_card','debit_card','bank_transfer','insurance') COLLATE utf8mb4_unicode_ci NOT NULL,
    `payment_status` enum('pending','completed','failed','refunded') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
    `transaction_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `reference_number` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `payment_date` date NOT NULL,
    `notes` text COLLATE utf8mb4_unicode_ci,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `payments_appointment_id_foreign` (`appointment_id`),
    KEY `payments_patient_id_foreign` (`patient_id`),
    CONSTRAINT `payments_appointment_id_foreign` FOREIGN KEY (`appointment_id`) REFERENCES `appointments` (`id`) ON DELETE SET NULL,
    CONSTRAINT `payments_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول الفواتير (Invoices)
CREATE TABLE `invoices` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `patient_id` bigint(20) UNSIGNED NOT NULL,
    `invoice_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `invoice_date` date NOT NULL,
    `due_date` date NOT NULL,
    `subtotal` decimal(10,2) NOT NULL,
    `tax_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
    `discount_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
    `total_amount` decimal(10,2) NOT NULL,
    `paid_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
    `payment_status` enum('pending','partial','paid','overdue') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
    `notes` text COLLATE utf8mb4_unicode_ci,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `invoices_invoice_number_unique` (`invoice_number`),
    KEY `invoices_patient_id_foreign` (`patient_id`),
    CONSTRAINT `invoices_patient_id_foreign` FOREIGN KEY (`patient_id`) REFERENCES `patients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول عناصر الفواتير (Invoice Items)
CREATE TABLE `invoice_items` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `invoice_id` bigint(20) UNSIGNED NOT NULL,
    `service_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `service_description` text COLLATE utf8mb4_unicode_ci,
    `quantity` int(11) NOT NULL DEFAULT 1,
    `unit_price` decimal(10,2) NOT NULL,
    `total_price` decimal(10,2) NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `invoice_items_invoice_id_foreign` (`invoice_id`),
    CONSTRAINT `invoice_items_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول جلسات العمل (Work Sessions)
CREATE TABLE `work_sessions` (
    `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` bigint(20) UNSIGNED NOT NULL,
    `start_time` timestamp NULL DEFAULT NULL,
    `end_time` timestamp NULL DEFAULT NULL,
    `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `user_agent` text COLLATE utf8mb4_unicode_ci,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `work_sessions_user_id_foreign` (`user_id`),
    CONSTRAINT `work_sessions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول جلسات المستخدمين في قاعدة البيانات
CREATE TABLE `sessions` (
    `id` varchar(255) NOT NULL,
    `user_id` bigint(20) UNSIGNED DEFAULT NULL,
    `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    `user_agent` text COLLATE utf8mb4_unicode_ci,
    `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
    `last_activity` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `sessions_user_id_index` (`user_id`),
    KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- إدراج البيانات الأساسية

-- إدراج الأدوار الأساسية
INSERT INTO `roles` (`name`, `guard_name`, `description`, `created_at`, `updated_at`) VALUES
('Admin', 'web', 'مدير النظام', NOW(), NOW()),
('Doctor', 'web', 'طبيب', NOW(), NOW()),
('Nurse', 'web', 'ممرضة', NOW(), NOW()),
('Receptionist', 'web', 'موظف استقبال', NOW(), NOW()),
('Patient', 'web', 'مريض', NOW(), NOW());

-- إدراج الصلاحيات الأساسية
INSERT INTO `permissions` (`name`, `guard_name`, `description`, `created_at`, `updated_at`) VALUES
('Manage Users', 'web', 'إدارة المستخدمين', NOW(), NOW()),
('Manage Patients', 'web', 'إدارة المرضى', NOW(), NOW()),
('Manage Appointments', 'web', 'إدارة المواعيد', NOW(), NOW()),
('Manage Medical Records', 'web', 'إدارة السجلات الطبية', NOW(), NOW()),
('Manage Prescriptions', 'web', 'إدارة الوصفات', NOW(), NOW()),
('Manage Payments', 'web', 'إدارة المدفوعات', NOW(), NOW()),
('Manage Invoices', 'web', 'إدارة الفواتير', NOW(), NOW()),
('View Reports', 'web', 'عرض التقارير', NOW(), NOW()),
('Manage Settings', 'web', 'إدارة الإعدادات', NOW(), NOW());

-- ربط الصلاحيات بالأدوار
-- مدير النظام يحصل على جميع الصلاحيات
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`)
SELECT p.id, r.id FROM permissions p CROSS JOIN roles r WHERE r.name = 'Admin';

-- الأطباء يحصلون على صلاحيات المرضى والمواعيد والسجلات الطبية
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`) VALUES
(2, 2), (3, 2), (4, 2), (5, 2), (7, 2);

-- الممرضات يحصلون على صلاحيات المرضى والمواعيد
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`) VALUES
(2, 3), (3, 3);

-- موظفو الاستقبال يحصلون على صلاحيات المرضى والمواعيد والمدفوعات
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`) VALUES
(2, 4), (3, 4), (6, 4), (7, 4);

-- المرضى يحصلون على صلاحية عرض سجلاتهم فقط
INSERT INTO `role_has_permissions` (`permission_id`, `role_id`) VALUES
(4, 5);

-- إدراج مستخدم مدير النظام الافتراضي
INSERT INTO `users` (`name`, `email`, `password`, `phone`, `national_id`, `is_active`, `created_at`, `updated_at`) VALUES
('مدير النظام', 'admin@clinic.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '+966123456789', '1234567890', 1, NOW(), NOW());

-- ربط مدير النظام بالدور الإداري
INSERT INTO `model_has_roles` (`role_id`, `model_type`, `model_id`) VALUES (1, 'App\Models\User', 1);

-- إدراج بعض الأدوية الشائعة
INSERT INTO `medications` (`name`, `generic_name`, `category`, `dosage_form`, `strength`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
('باراسيتامول', 'Paracetamol', 'مسكن وخافض حرارة', 'أقراص', '500mg', 'مسكن للألم وخافض للحرارة', 1, NOW(), NOW()),
('أسبرين', 'Aspirin', 'مسكن ومضاد للالتهاب', 'أقراص', '100mg', 'مسكن ومضاد للالتهاب ومميع للدم', 1, NOW(), NOW()),
('أوميغا 3', 'Omega-3', 'مكمل غذائي', 'كبسولات', '1000mg', 'مكمل غذائي للحماية من أمراض القلب', 1, NOW(), NOW());

-- إنشاء الفهارس لتحسين الأداء
CREATE INDEX idx_appointments_status ON appointments(status);
CREATE INDEX idx_appointments_doctor_date ON appointments(doctor_id, appointment_date);
CREATE INDEX idx_patients_national_id ON patients(national_id);
CREATE INDEX idx_payments_status ON payments(payment_status);
CREATE INDEX idx_invoices_status ON invoices(payment_status);
CREATE INDEX idx_medical_records_patient_date ON medical_records(patient_id, record_date);
