# ERD - nasrcity_antibiotic_management

```mermaid
erDiagram

    %% ============================================================
    %% CORE ENTITIES
    %% ============================================================

    users {
        int id PK
        varchar full_name
        varchar username
        varchar password
        enum role
        tinyint is_active
        timestamp created_at
    }

    patients {
        int id PK
        varchar patient_name
        varchar mrn
        enum gender
        date date_of_birth
        varchar phone
        text address
        int created_by FK
        timestamp created_at
    }

    antibiotics {
        int id PK
        varchar antibiotic_name
        varchar generic_name
        varchar category
        varchar dosage_form
        varchar strength
        text indication
        tinyint is_active
        timestamp created_at
    }

    units {
        int id PK
        varchar name
        varchar name_en
        varchar department
        tinyint is_active
    }

    %% ============================================================
    %% REPORTS & INTERVENTIONS
    %% ============================================================

    antibiotic_reports {
        int id PK
        int patient_id FK
        int reporting_pharmacist_id FK
        int unit_id FK
        int antibiotic_id FK
        date report_date
        enum problem_authority
        text intervention_choice
        text degree_of_damage
        enum result_of_intervention
        text corrective_action
        enum result_of_corrective_action
        enum status
        timestamp created_at
    }

    problem_definitions {
        int id PK
        int report_id FK
        text problem_description
        timestamp created_at
    }

    report_attachments {
        int id PK
        int report_id FK
        int uploaded_by FK
        varchar file_name
        varchar file_path
        timestamp created_at
    }

    %% ============================================================
    %% DEPARTMENT REPORTS
    %% ============================================================

    icu_daily_reports {
        int id PK
        int created_by FK
        date report_date
        text patients_data
        text notes
        timestamp created_at
    }

    nursery_pediatric_reports {
        int id PK
        int created_by FK
        date report_date
        text patients_data
        text notes
        timestamp created_at
    }

    simple_patient_monitoring {
        int id PK
        int patient_id FK
        int created_by FK
        date monitoring_date
        text monitoring_data
        timestamp created_at
    }

    clinical_pharmacy_forms {
        int id PK
        int created_by FK
        date form_date
        varchar department
        int patient_count
        int discharge_count
        text blue_text_data
        text notes
        timestamp created_at
    }

    internal_reports {
        int id PK
        int created_by FK
        date report_date
        time time_from
        time time_to
        int total_cases
        int discharges
        int fourth_floor_cases
        int fifth_floor_cases
        int sixth_floor_cases
        int intermediate_care_cases
        timestamp created_at
    }

    pharmacy_handover_forms {
        int id PK
        int created_by FK
        date form_date
        varchar shift_type
        text checkboxes
        text items
        text yes_no_selections
        text reasons
        text narcotic_patients
        text disposal_records
        text notes
        timestamp created_at
    }

    daily_pharmacist_reports {
        int id PK
        int pharmacist_id FK
        date report_date
        varchar pharmacist_during_shift
        int total_patients
        int total_discharged
        text patients_data
        text sedation_patients
        text notes
        timestamp created_at
    }

    %% ============================================================
    %% SURGERY & MEDICATIONS
    %% ============================================================

    surgery_antibiotics {
        int id PK
        int created_by FK
        date surgery_date
        text surgery_data
        timestamp created_at
    }

    surgery_antibiotics_log {
        int id PK
        int antibiotic_id FK
        int created_by FK
        int serial_number
        varchar patient_name
        varchar medical_number
        varchar surgery_type
        varchar consultant_name
        varchar quantity
        varchar unit_distinction
        varchar entry_status
        tinyint is_matched
        timestamp created_at
    }

    patient_medication_tracking {
        int id PK
        int created_by FK
        date tracking_date
        text medication_data
        timestamp created_at
    }

    %% ============================================================
    %% DISPOSAL & BREAKAGE
    %% ============================================================

    breakage_reports {
        int id PK
        int reported_by FK
        date report_date
        varchar item_name
        int quantity
        text reason
        timestamp created_at
    }

    narcotic_disposal_reports {
        int id PK
        int reported_by FK
        date report_date
        varchar drug_name
        int quantity
        enum disposal_reason
        timestamp created_at
    }

    expired_drugs_disposal {
        int id PK
        int reported_by FK
        date report_date
        varchar drug_name
        int quantity
        date expiry_date
        varchar batch_number
        text notes
        timestamp created_at
    }

    %% ============================================================
    %% PHARMACIST SCHEDULE
    %% ============================================================

    pharmacists {
        int id PK
        varchar name
        varchar name_en
        varchar role
        tinyint is_active
    }

    pharmacist_schedule {
        int id PK
        int pharmacist_id FK
        date schedule_date
        varchar shift_type
        varchar department
        text notes
        timestamp created_at
    }

    %% ============================================================
    %% PERMISSIONS & USERS
    %% ============================================================

    page_permissions {
        int id PK
        varchar page_key
        varchar page_name_ar
        varchar page_name_en
        varchar file_name
    }

    user_page_permissions {
        int id PK
        int user_id FK
        int granted_by FK
        varchar page_key
        tinyint can_access
        timestamp created_at
    }

    %% ============================================================
    %% SYSTEM & LOGS
    %% ============================================================

    activity_log {
        int id PK
        int user_id FK
        varchar action
        varchar table_name
        int record_id
        text old_values
        text new_values
        varchar ip_address
        timestamp created_at
    }

    notifications {
        int id PK
        int user_id FK
        varchar title
        text message
        tinyint is_read
        timestamp created_at
    }

    notification_settings {
        int id PK
        int user_id FK
        varchar setting_key
        varchar setting_value
        timestamp updated_at
    }

    export_logs {
        int id PK
        int exported_by FK
        varchar export_type
        varchar file_name
        int record_count
        timestamp export_date
    }

    system_backups {
        int id PK
        int created_by FK
        varchar file_name
        varchar file_path
        bigint file_size
        timestamp created_at
    }

    system_settings {
        int id PK
        int updated_by FK
        varchar setting_key
        text setting_value
        timestamp updated_at
    }

    custom_stats {
        int id PK
        int updated_by FK
        date stat_date
        varchar stat_name
        varchar stat_value
        timestamp updated_at
    }

    daily_statistics {
        int id PK
        date stat_date
        int total_reports
        int completed_reports
        int pending_reports
        int total_patients
        int active_antibiotics
        timestamp created_at
    }

    monthly_pharmacist_stats {
        int id PK
        int pharmacist_id
        int year
        int month
        int total_reports
        int total_patients
        timestamp created_at
    }

    holidays {
        int id PK
        int created_by
        varchar holiday_name
        date holiday_date
        enum holiday_type
        tinyint is_recurring
        timestamp created_at
    }

    %% ============================================================
    %% RELATIONSHIPS
    %% ============================================================

    %% users → many tables
    users ||--o{ patients : "created_by"
    users ||--o{ antibiotic_reports : "reporting_pharmacist_id"
    users ||--o{ activity_log : "user_id"
    users ||--o{ notifications : "user_id"
    users ||--o{ notification_settings : "user_id"
    users ||--o{ export_logs : "exported_by"
    users ||--o{ system_backups : "created_by"
    users ||--o{ system_settings : "updated_by"
    users ||--o{ custom_stats : "updated_by"
    users ||--o{ daily_pharmacist_reports : "pharmacist_id"
    users ||--o{ icu_daily_reports : "created_by"
    users ||--o{ nursery_pediatric_reports : "created_by"
    users ||--o{ clinical_pharmacy_forms : "created_by"
    users ||--o{ internal_reports : "created_by"
    users ||--o{ pharmacy_handover_forms : "created_by"
    users ||--o{ surgery_antibiotics : "created_by"
    users ||--o{ surgery_antibiotics_log : "created_by"
    users ||--o{ patient_medication_tracking : "created_by"
    users ||--o{ breakage_reports : "reported_by"
    users ||--o{ narcotic_disposal_reports : "reported_by"
    users ||--o{ expired_drugs_disposal : "reported_by"
    users ||--o{ simple_patient_monitoring : "created_by"
    users ||--o{ report_attachments : "uploaded_by"
    users ||--o{ user_page_permissions : "user_id"
    users ||--o{ user_page_permissions : "granted_by"

    %% antibiotic_reports → core
    patients ||--o{ antibiotic_reports : "patient_id"
    units ||--o{ antibiotic_reports : "unit_id"
    antibiotics ||--o{ antibiotic_reports : "antibiotic_id"

    %% antibiotic_reports → children
    antibiotic_reports ||--o{ problem_definitions : "report_id"
    antibiotic_reports ||--o{ report_attachments : "report_id"

    %% surgery
    antibiotics ||--o{ surgery_antibiotics_log : "antibiotic_id"

    %% monitoring
    patients ||--o{ simple_patient_monitoring : "patient_id"

    %% pharmacist schedule
    pharmacists ||--o{ pharmacist_schedule : "pharmacist_id"

    %% page permissions
    page_permissions ||--o{ user_page_permissions : "page_key"
```

---

## ملخص الجداول (35 جدول)

| المجموعة | الجداول |
|----------|---------|
| **الأساسية** | `users`, `patients`, `antibiotics`, `units` |
| **التقارير والتدخلات** | `antibiotic_reports`, `problem_definitions`, `report_attachments` |
| **تقارير الأقسام** | `icu_daily_reports`, `nursery_pediatric_reports`, `simple_patient_monitoring`, `clinical_pharmacy_forms`, `internal_reports`, `pharmacy_handover_forms`, `daily_pharmacist_reports` |
| **العمليات والأدوية** | `surgery_antibiotics`, `surgery_antibiotics_log`, `patient_medication_tracking` |
| **الإعدام والكسر** | `breakage_reports`, `narcotic_disposal_reports`, `expired_drugs_disposal` |
| **جدول الصيادلة** | `pharmacists`, `pharmacist_schedule` |
| **الصلاحيات** | `page_permissions`, `user_page_permissions` |
| **النظام والسجلات** | `activity_log`, `notifications`, `notification_settings`, `export_logs`, `system_backups`, `system_settings`, `custom_stats`, `daily_statistics`, `monthly_pharmacist_stats`, `holidays` |
