er_diagram.puml

@startuml er_diagram

title DiagFlow — Entity-Relationship Diagram
skinparam backgroundColor #FEFEFE
skinparam defaultFontName Inter
skinparam shadowing false
skinparam linetype ortho

hide circle

' ─────────────────────────────────────────────
'  diagflow.db — Config & State
' ─────────────────────────────────────────────

package "diagflow.db" as db1 #E3F2FD {

    entity "diagnosticians" as diag {
        * id : INTEGER <<PK, autoincrement>>
        --
        name : TEXT <<NOT NULL>>
        active : INTEGER <<NOT NULL, default 1>>
        can_ct : INTEGER <<default 1>>
        can_mri : INTEGER <<default 1>>
        quota_monday : INTEGER <<default 15>>
        quota_tuesday : INTEGER <<default 15>>
        quota_wednesday : INTEGER <<default 15>>
        quota_thursday : INTEGER <<default 15>>
        quota_friday : INTEGER <<default 15>>
        quota_saturday : INTEGER <<default 0>>
        quota_sunday : INTEGER <<default 0>>
        preferred_lab_id : INTEGER <<nullable>>
    }

    entity "diagnostician_skills" as skills {
        * id : INTEGER <<PK, autoincrement>>
        --
        diagnostician_id : INTEGER <<FK, NOT NULL>>
        exam_code : TEXT <<NOT NULL>>
        is_preferred : INTEGER <<default 0>>
        ..
        <<UNIQUE(diagnostician_id, exam_code)>>
    }

    entity "partnerships" as partners {
        * id : INTEGER <<PK, autoincrement>>
        --
        issuing_doctor_id : TEXT <<FK, NOT NULL>>
        issuing_doctor_name : TEXT <<NOT NULL>>
        preferred_diagnostician_id : INTEGER <<FK, NOT NULL>>
        priority : INTEGER <<default 1>>
        exclusive : INTEGER <<default 0>>
        is_active : INTEGER <<default 1>>
    }

    entity "availability" as avail {
        * id : INTEGER <<PK, autoincrement>>
        --
        diagnostician_id : INTEGER <<FK, NOT NULL>>
        date : TEXT <<NOT NULL>> (ISO-8601)
        status : TEXT <<NOT NULL, default 'available'>>
        is_pamakristos_oncall : INTEGER <<default 0>>
        notes : TEXT
        ..
        <<UNIQUE(diagnostician_id, date)>>
    }

    entity "doctors" as docs {
        * id : TEXT <<PK>>
        --
        name : TEXT <<NOT NULL>>
    }

    entity "local_assignments" as local {
        * exammoreid : INTEGER <<PK>>
        --
        diagnostician_id : INTEGER <<NOT NULL>>
        diagnostician_name : TEXT <<NOT NULL>>
        assigned_at : TEXT <<NOT NULL>> (ISO-8601)
        is_auto : INTEGER <<NOT NULL, default 0>>
        rule_desc : TEXT
    }

    entity "assignment_log" as log {
        * exammoreid : INTEGER <<PK>>
        --
        diagnostician_id : INTEGER <<NOT NULL>>
        assigned_at : TEXT <<NOT NULL>> (ISO-8601)
        modality : TEXT
        extracode : TEXT
    }

    entity "pamakristos_schedule" as pam {
        * weekday : INTEGER <<PK>> (0=Mon..6=Sun)
        --
        diagnostician_id : INTEGER <<FK, NOT NULL>>
    }

    entity "exam_routing_rules" as route {
        * id : INTEGER <<PK, autoincrement>>
        --
        lab_id : INTEGER <<nullable>>
        is_pamakristos : INTEGER <<NOT NULL, default 0>>
        exam_codes : TEXT <<NOT NULL>>
        diagnostician_id : INTEGER <<FK, NOT NULL>>
        description : TEXT
        issuing_doctor_id : INTEGER <<nullable>>
        issuing_doctor_name : TEXT <<nullable>>
        is_active : INTEGER <<NOT NULL, default 1>>
    }

    entity "exclusive_lab_rules" as ex_lab {
        * id : INTEGER <<PK, autoincrement>>
        --
        diagnostician_id : INTEGER <<FK, NOT NULL>>
        lab_id : INTEGER <<NOT NULL>>
        lab_name : TEXT
        is_active : INTEGER <<NOT NULL, default 1>>
    }

    entity "modality_quotas" as mod_q {
        * id : INTEGER <<PK, autoincrement>>
        --
        diagnostician_id : INTEGER <<FK, NOT NULL>>
        modality : TEXT <<NOT NULL>> (CT|MRI)
        max_count : INTEGER <<NOT NULL>>
        is_active : INTEGER <<NOT NULL, default 1>>
    }

    entity "system_settings" as sys {
        * key : TEXT <<PK>>
        --
        value : TEXT <<NOT NULL>>
    }

    entity "exam_dictionary" as exam_dict {
        * code : TEXT <<PK>>
        --
        name : TEXT <<NOT NULL>>
        category : TEXT
    }

    entity "admin_users" as admin_users {
        * id : INTEGER <<PK, autoincrement>>
        --
        username : TEXT <<UNIQUE, NOT NULL>>
        password_hash : TEXT <<NOT NULL>>
        role : TEXT <<NOT NULL, default 'admin'>>
        is_active : INTEGER <<NOT NULL, default 1>>
    }
}

' ─────────────────────────────────────────────
'  mock_slis.db — Slis Mirror
' ─────────────────────────────────────────────

package "mock_slis.db" as db2 #FFF3E0 {

    entity "exam_categories" as cats {
        * examnumcode : INTEGER <<PK>>
        --
        name : TEXT <<NOT NULL>>
        category : TEXT <<NOT NULL>> (CT|MRI|MRA)
    }

    entity "slis_exams" as exams {
        * exammoreid : INTEGER <<PK>>
        --
        extracode : INTEGER
        visitid : INTEGER
        demogid : INTEGER
        fname : TEXT
        lname : TEXT
        age : INTEGER
        examid : INTEGER
        examnumcode : INTEGER <<FK>>
        examname : TEXT
        visitdate : TEXT (ISO-8601)
        labcodeid : INTEGER
        laboratoryname : TEXT
        wardid : INTEGER
        wcode : TEXT
        wname : TEXT
        diagnostis : INTEGER <<nullable>>
        personelid : INTEGER
        code : TEXT
        name : TEXT
        notes : TEXT
        category : TEXT
        oldpers : INTEGER <<nullable>>
        slis_synced_at : TEXT <<nullable>>
    }

    entity "diagnosticians\n(Slis personnel)" as slis_diag {
        * PERSONELID : INTEGER <<PK>>
        --
        DOCNAME : TEXT
    }

    entity "doctors\n(Slis doctors)" as slis_docs {
        * CODE : INTEGER <<PK>>
        --
        DOCNAME : TEXT
    }
}

' ─────────────────────────────────────────────
'  Relationships — Central DB / diagflow.db
' ─────────────────────────────────────────────
diag ||--o{ skills : "1:N"
diag ||--o{ avail : "1:N"
diag ||--o{ partners : "1:N\n(preferred_diagnostician_id)"
diag ||--o{ pam : "1:N"
diag ||--o{ local : "1:N\n(diagnostician_id)"
diag ||--o{ log : "1:N\n(diagnostician_id)"
diag ||--o{ route : "1:N\n(diagnostician_id)"
diag ||--o{ ex_lab : "1:N\n(diagnostician_id)"
diag ||--o{ mod_q : "1:N\n(diagnostician_id)"

partners }o--|| docs : "N:1\n(issuing_doctor_id)"

' ─────────────────────────────────────────────
'  Relationships — mock_slis.db
' ─────────────────────────────────────────────
exams }o--|| cats : "N:1\n(examnumcode)"

' ─────────────────────────────────────────────
'  Cross-database references & Master Data Sync
' ─────────────────────────────────────────────
local ..> exams : "references\nexammoreid"
log ..> exams : "references\nexammoreid"
slis_diag ..> diag : "syncs personnel\n(id, name)"
slis_docs ..> docs : "syncs doctors\n(id, name)"

note bottom of db2
    In **production**, mock_slis.db is replaced
    by the real Slis MSSQL database.
    DiagFlow executes stored procedures directly.
end note

note bottom of db1
    In **production**, all DiagFlow tables are
    prefixed with **df_*** (e.g. df_diagnosticians)
    and reside within the central MSSQL server.
    In **development**, local SQLite is used.
end note

@enduml