-- نظام إدارة العيادات والأطباء
-- Clinic Management System (CMS)

-- ============================================
-- 1. إدارة العيادات (Clinic Management)
-- ============================================

CREATE TABLE IF NOT EXISTS clinics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    clinic_code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NULL,
    specialty VARCHAR(150) NULL COMMENT 'تخصص العيادة',
    license_number VARCHAR(100) NULL COMMENT 'رقم الترخيص',
    phone VARCHAR(30) NULL,
    mobile VARCHAR(30) NULL,
    email VARCHAR(150) NULL,
    website VARCHAR(255) NULL,
    address TEXT NULL,
    city VARCHAR(100) NULL,
    country VARCHAR(100) NULL DEFAULT 'Iraq',
    latitude DECIMAL(10, 8) NULL COMMENT 'خط العرض لـ Google Maps',
    longitude DECIMAL(11, 8) NULL COMMENT 'خط الطول لـ Google Maps',
    logo_url VARCHAR(500) NULL,
    is_main_branch TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'فرع رئيسي',
    parent_clinic_id INT NULL COMMENT 'العيادة الأم (للفروع)',
    tenant_id INT NOT NULL DEFAULT 1,
    branch_id INT NULL COMMENT 'ربط مع نظام الفروع',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_clinic_id) REFERENCES clinics(id) ON DELETE SET NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL,
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_clinic_code (clinic_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- أوقات الدوام للعيادة
CREATE TABLE IF NOT EXISTS clinic_working_hours (
    id INT AUTO_INCREMENT PRIMARY KEY,
    clinic_id INT NOT NULL,
    day_of_week TINYINT NOT NULL COMMENT '0=الأحد, 1=الاثنين, ..., 6=السبت',
    is_open TINYINT(1) NOT NULL DEFAULT 1,
    open_time TIME NULL,
    close_time TIME NULL,
    break_start TIME NULL COMMENT 'بداية الاستراحة',
    break_end TIME NULL COMMENT 'نهاية الاستراحة',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (clinic_id) REFERENCES clinics(id) ON DELETE CASCADE,
    UNIQUE KEY unique_clinic_day (clinic_id, day_of_week)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. إدارة الأطباء (Doctor Management)
-- ============================================

CREATE TABLE IF NOT EXISTS clinic_doctors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    doctor_code VARCHAR(50) NOT NULL UNIQUE,
    full_name VARCHAR(200) NOT NULL,
    full_name_en VARCHAR(200) NULL,
    specialty VARCHAR(150) NOT NULL COMMENT 'الاختصاص',
    sub_specialty VARCHAR(150) NULL COMMENT 'التخصص الفرعي',
    syndicate_number VARCHAR(100) NULL COMMENT 'رقم النقابة',
    phone VARCHAR(30) NULL,
    mobile VARCHAR(30) NULL,
    email VARCHAR(150) NULL,
    date_of_birth DATE NULL,
    gender ENUM('M', 'F') NULL,
    photo_url VARCHAR(500) NULL COMMENT 'صورة شخصية',
    signature_url VARCHAR(500) NULL COMMENT 'توقيع إلكتروني للوصفات',
    clinic_id INT NOT NULL,
    profit_percentage DECIMAL(5, 2) NOT NULL DEFAULT 0.00 COMMENT 'نسبة الأرباح من كل زيارة',
    consultation_fee DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'رسوم الاستشارة',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    notes TEXT NULL,
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (clinic_id) REFERENCES clinics(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_clinic_id (clinic_id),
    INDEX idx_doctor_code (doctor_code),
    INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول مواعيد الطبيب
CREATE TABLE IF NOT EXISTS doctor_schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    doctor_id INT NOT NULL,
    day_of_week TINYINT NOT NULL COMMENT '0=الأحد, 1=الثنين, ..., 6=السبت',
    is_available TINYINT(1) NOT NULL DEFAULT 1,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    break_start TIME NULL,
    break_end TIME NULL,
    appointment_duration INT NOT NULL DEFAULT 30 COMMENT 'مدة الموعد بالدقائق',
    max_appointments INT NULL COMMENT 'الحد الأقصى للمواعيد في اليوم',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (doctor_id) REFERENCES clinic_doctors(id) ON DELETE CASCADE,
    UNIQUE KEY unique_doctor_day (doctor_id, day_of_week)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- استثناءات في جدول الطبيب (إجازات، مواعيد خاصة)
CREATE TABLE IF NOT EXISTS doctor_schedule_exceptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    doctor_id INT NOT NULL,
    exception_date DATE NOT NULL,
    exception_type ENUM('holiday', 'special', 'unavailable') NOT NULL DEFAULT 'unavailable',
    start_time TIME NULL,
    end_time TIME NULL,
    reason TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (doctor_id) REFERENCES clinic_doctors(id) ON DELETE CASCADE,
    INDEX idx_doctor_date (doctor_id, exception_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 3. نظام الحجز والمواعيد (Appointment System)
-- ============================================

CREATE TABLE IF NOT EXISTS appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    appointment_number VARCHAR(50) NOT NULL UNIQUE,
    clinic_id INT NOT NULL,
    doctor_id INT NOT NULL,
    patient_id INT NOT NULL,
    appointment_date DATE NOT NULL,
    appointment_time TIME NOT NULL,
    status ENUM('scheduled', 'confirmed', 'in_progress', 'completed', 'cancelled', 'no_show') NOT NULL DEFAULT 'scheduled',
    appointment_type ENUM('consultation', 'follow_up', 'emergency', 'online') NOT NULL DEFAULT 'consultation',
    queue_number INT NULL COMMENT 'رقم الطابور',
    notes TEXT NULL,
    cancellation_reason TEXT NULL,
    cancelled_by INT NULL COMMENT 'المستخدم الذي ألغى الموعد',
    cancelled_at DATETIME NULL,
    reminder_sent TINYINT(1) NOT NULL DEFAULT 0,
    reminder_sent_at DATETIME NULL,
    created_by INT NOT NULL COMMENT 'من أنشأ الحجز',
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (clinic_id) REFERENCES clinics(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES clinic_doctors(id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (cancelled_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_clinic_doctor_date (clinic_id, doctor_id, appointment_date),
    INDEX idx_patient_id (patient_id),
    INDEX idx_status (status),
    INDEX idx_appointment_number (appointment_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 4. نظام السجلات الطبية الإلكترونية (EMR)
-- ============================================

-- زيارة المريض (Visit/Encounter)
CREATE TABLE IF NOT EXISTS patient_visits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_number VARCHAR(50) NOT NULL UNIQUE,
    appointment_id INT NULL COMMENT 'ربط مع الموعد',
    clinic_id INT NOT NULL,
    doctor_id INT NOT NULL,
    patient_id INT NOT NULL,
    visit_date DATETIME NOT NULL,
    visit_type ENUM('consultation', 'follow_up', 'emergency', 'lab_follow_up') NOT NULL DEFAULT 'consultation',
    chief_complaint TEXT NULL COMMENT 'الشكوى الرئيسية',
    history_of_present_illness TEXT NULL COMMENT 'تاريخ المرض الحالي',
    physical_examination TEXT NULL COMMENT 'الفحص السريري',
    diagnosis TEXT NULL COMMENT 'التشخيص',
    diagnosis_code VARCHAR(50) NULL COMMENT 'كود التشخيص (ICD-10)',
    treatment_plan TEXT NULL COMMENT 'خطة العلاج',
    notes TEXT NULL,
    next_visit_date DATE NULL,
    visit_status ENUM('in_progress', 'completed', 'cancelled') NOT NULL DEFAULT 'in_progress',
    created_by INT NOT NULL,
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
    FOREIGN KEY (clinic_id) REFERENCES clinics(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES clinic_doctors(id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_patient_id (patient_id),
    INDEX idx_doctor_id (doctor_id),
    INDEX idx_visit_date (visit_date),
    INDEX idx_visit_number (visit_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- معلومات إضافية للمريض (EMR Extended)
ALTER TABLE patients 
ADD COLUMN IF NOT EXISTS blood_type VARCHAR(10) NULL COMMENT 'فصيلة الدم',
ADD COLUMN IF NOT EXISTS allergies TEXT NULL COMMENT 'الحساسيات (JSON)',
ADD COLUMN IF NOT EXISTS chronic_diseases TEXT NULL COMMENT 'الأمراض المزمنة (JSON)',
ADD COLUMN IF NOT EXISTS medications TEXT NULL COMMENT 'الأدوية الحالية (JSON)',
ADD COLUMN IF NOT EXISTS emergency_contact_name VARCHAR(150) NULL,
ADD COLUMN IF NOT EXISTS emergency_contact_phone VARCHAR(30) NULL,
ADD COLUMN IF NOT EXISTS insurance_provider VARCHAR(150) NULL,
ADD COLUMN IF NOT EXISTS insurance_number VARCHAR(100) NULL;

-- الوصفات الطبية (Prescriptions)
CREATE TABLE IF NOT EXISTS prescriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    prescription_number VARCHAR(50) NOT NULL UNIQUE,
    visit_id INT NOT NULL,
    doctor_id INT NOT NULL,
    patient_id INT NOT NULL,
    prescription_date DATETIME NOT NULL,
    notes TEXT NULL,
    is_dispensed TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'تم صرفه من الصيدلية',
    dispensed_at DATETIME NULL,
    dispensed_by INT NULL,
    pdf_url VARCHAR(500) NULL COMMENT 'نسخة PDF من الوصفة',
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES patient_visits(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES clinic_doctors(id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (dispensed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_visit_id (visit_id),
    INDEX idx_patient_id (patient_id),
    INDEX idx_prescription_number (prescription_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- أدوية الوصفة
CREATE TABLE IF NOT EXISTS prescription_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    prescription_id INT NOT NULL,
    medication_name VARCHAR(200) NOT NULL,
    dosage VARCHAR(100) NULL COMMENT 'الجرعة',
    frequency VARCHAR(100) NULL COMMENT 'التكرار (مثل: مرتين يومياً)',
    duration VARCHAR(100) NULL COMMENT 'المدة (مثل: 7 أيام)',
    quantity INT NULL,
    instructions TEXT NULL COMMENT 'تعليمات خاصة',
    item_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id) ON DELETE CASCADE,
    INDEX idx_prescription_id (prescription_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- المرفقات (تحاليل، أشعة، صور)
CREATE TABLE IF NOT EXISTS visit_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    attachment_type ENUM('lab_result', 'xray', 'image', 'document', 'other') NOT NULL DEFAULT 'document',
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NULL COMMENT 'الحجم بالبايت',
    mime_type VARCHAR(100) NULL,
    description TEXT NULL,
    uploaded_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES patient_visits(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id),
    INDEX idx_visit_id (visit_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 5. النظام المالي للعيادات
-- ============================================

-- فواتير العيادة
CREATE TABLE IF NOT EXISTS clinic_invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    visit_id INT NULL COMMENT 'ربط مع الزيارة',
    appointment_id INT NULL,
    clinic_id INT NOT NULL,
    doctor_id INT NULL,
    patient_id INT NOT NULL,
    invoice_date DATETIME NOT NULL,
    consultation_fee DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    procedure_fees DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'رسوم إجراءات',
    medication_fees DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'رسوم أدوية',
    lab_fees DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'رسوم تحاليل',
    other_fees DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    subtotal DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    discount_percentage DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
    tax_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    remaining_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    payment_status ENUM('unpaid', 'partial', 'paid') NOT NULL DEFAULT 'unpaid',
    payment_method ENUM('cash', 'card', 'transfer', 'mixed', 'insurance') NULL,
    doctor_share DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'حصة الطبيب',
    clinic_share DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'حصة العيادة',
    notes TEXT NULL,
    created_by INT NOT NULL,
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES patient_visits(id) ON DELETE SET NULL,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
    FOREIGN KEY (clinic_id) REFERENCES clinics(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES clinic_doctors(id) ON DELETE SET NULL,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_clinic_id (clinic_id),
    INDEX idx_doctor_id (doctor_id),
    INDEX idx_patient_id (patient_id),
    INDEX idx_invoice_date (invoice_date),
    INDEX idx_payment_status (payment_status),
    INDEX idx_invoice_number (invoice_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- المدفوعات
CREATE TABLE IF NOT EXISTS clinic_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_number VARCHAR(50) NOT NULL UNIQUE,
    invoice_id INT NOT NULL,
    payment_date DATETIME NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('cash', 'card', 'transfer', 'insurance') NOT NULL,
    reference_number VARCHAR(100) NULL COMMENT 'رقم المرجع (للمحولات)',
    notes TEXT NULL,
    created_by INT NOT NULL,
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES clinic_invoices(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_invoice_id (invoice_id),
    INDEX idx_payment_date (payment_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- مصاريف العيادة
CREATE TABLE IF NOT EXISTS clinic_expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    expense_number VARCHAR(50) NOT NULL UNIQUE,
    clinic_id INT NOT NULL,
    expense_date DATE NOT NULL,
    category VARCHAR(100) NOT NULL COMMENT 'نوع المصروف (راتب، إيجار، كهرباء، إلخ)',
    description TEXT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('cash', 'card', 'transfer') NOT NULL DEFAULT 'cash',
    receipt_url VARCHAR(500) NULL,
    created_by INT NOT NULL,
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (clinic_id) REFERENCES clinics(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_clinic_id (clinic_id),
    INDEX idx_expense_date (expense_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 6. الصيدلية داخل العيادة
-- ============================================

-- أدوية الصيدلية
CREATE TABLE IF NOT EXISTS clinic_pharmacy_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    name_en VARCHAR(200) NULL,
    generic_name VARCHAR(200) NULL COMMENT 'الاسم العلمي',
    category VARCHAR(100) NULL COMMENT 'فئة الدواء',
    unit VARCHAR(50) NULL COMMENT 'الوحدة (علبة، عبوة، إلخ)',
    current_quantity DECIMAL(10, 2) NOT NULL DEFAULT 0,
    min_quantity DECIMAL(10, 2) NOT NULL DEFAULT 0 COMMENT 'الحد الأدنى',
    max_quantity DECIMAL(10, 2) NULL COMMENT 'الحد الأقصى',
    unit_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    selling_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    clinic_id INT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (clinic_id) REFERENCES clinics(id) ON DELETE CASCADE,
    INDEX idx_clinic_id (clinic_id),
    INDEX idx_item_code (item_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- دفعات الأدوية (للتتبع والصلاحية)
CREATE TABLE IF NOT EXISTS clinic_pharmacy_batches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    batch_number VARCHAR(100) NOT NULL,
    quantity DECIMAL(10, 2) NOT NULL,
    purchase_price DECIMAL(10, 2) NOT NULL,
    expiry_date DATE NOT NULL,
    purchase_date DATE NOT NULL,
    supplier_name VARCHAR(200) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES clinic_pharmacy_items(id) ON DELETE CASCADE,
    INDEX idx_item_id (item_id),
    INDEX idx_expiry_date (expiry_date),
    UNIQUE KEY unique_batch (item_id, batch_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- حركات الصيدلية (بيع، شراء، تعديل)
CREATE TABLE IF NOT EXISTS clinic_pharmacy_movements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_id INT NOT NULL,
    batch_id INT NULL,
    movement_type ENUM('purchase', 'sale', 'adjustment', 'return', 'expired') NOT NULL,
    quantity DECIMAL(10, 2) NOT NULL COMMENT 'الكمية (موجب للشراء، سالب للبيع)',
    unit_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    prescription_id INT NULL COMMENT 'ربط مع وصفة',
    invoice_id INT NULL COMMENT 'ربط مع فاتورة',
    reference_number VARCHAR(100) NULL,
    notes TEXT NULL,
    created_by INT NOT NULL,
    movement_date DATETIME NOT NULL,
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_id) REFERENCES clinic_pharmacy_items(id) ON DELETE CASCADE,
    FOREIGN KEY (batch_id) REFERENCES clinic_pharmacy_batches(id) ON DELETE SET NULL,
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id) ON DELETE SET NULL,
    FOREIGN KEY (invoice_id) REFERENCES clinic_invoices(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_item_id (item_id),
    INDEX idx_movement_date (movement_date),
    INDEX idx_movement_type (movement_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 7. الربط مع نظام المختبر (Lab Integration)
-- ============================================

-- طلبات تحاليل من العيادة
CREATE TABLE IF NOT EXISTS clinic_lab_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_number VARCHAR(50) NOT NULL UNIQUE,
    visit_id INT NULL COMMENT 'ربط مع زيارة العيادة',
    clinic_id INT NOT NULL,
    doctor_id INT NOT NULL,
    patient_id INT NOT NULL,
    lab_order_id INT NULL COMMENT 'ربط مع طلب المختبر',
    request_date DATETIME NOT NULL,
    priority ENUM('normal', 'urgent', 'stat') NOT NULL DEFAULT 'normal',
    clinical_notes TEXT NULL COMMENT 'ملاحظات سريرية',
    status ENUM('pending', 'sent', 'received', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    sent_at DATETIME NULL,
    received_at DATETIME NULL,
    completed_at DATETIME NULL,
    created_by INT NOT NULL,
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES patient_visits(id) ON DELETE SET NULL,
    FOREIGN KEY (clinic_id) REFERENCES clinics(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES clinic_doctors(id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    FOREIGN KEY (lab_order_id) REFERENCES lab_orders(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_clinic_id (clinic_id),
    INDEX idx_visit_id (visit_id),
    INDEX idx_status (status),
    INDEX idx_request_number (request_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- فحوصات طلب التحليل
CREATE TABLE IF NOT EXISTS clinic_lab_request_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL,
    test_id INT NOT NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES clinic_lab_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (test_id) REFERENCES tests(id) ON DELETE CASCADE,
    INDEX idx_request_id (request_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 8. نظام الطوابير (Queue System)
-- ============================================

CREATE TABLE IF NOT EXISTS clinic_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    clinic_id INT NOT NULL,
    doctor_id INT NULL COMMENT 'NULL = طابور عام للعيادة',
    appointment_id INT NULL,
    patient_id INT NOT NULL,
    queue_number INT NOT NULL,
    queue_date DATE NOT NULL,
    status ENUM('waiting', 'in_progress', 'completed', 'cancelled', 'no_show') NOT NULL DEFAULT 'waiting',
    called_at DATETIME NULL,
    completed_at DATETIME NULL,
    display_screen TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'عرض على الشاشة',
    tenant_id INT NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (clinic_id) REFERENCES clinics(id) ON DELETE CASCADE,
    FOREIGN KEY (doctor_id) REFERENCES clinic_doctors(id) ON DELETE SET NULL,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE SET NULL,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    INDEX idx_clinic_doctor_date (clinic_id, doctor_id, queue_date),
    INDEX idx_status (status),
    INDEX idx_queue_number (queue_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 9. الصلاحيات الإضافية للعيادات
-- ============================================

-- إضافة صلاحيات جديدة في جدول permissions (سيتم إضافتها عبر PHP migration)
