Mih/MolarCheck

graph LR
    subgraph MIH["MIH (Source Table)"]
        mih_id["mih_id"]
        patient_id["patient_id"]
        start_date["start_date"]
        end_date["end_date"]
        photo_id1["photo_id1"]
        photo_id2["photo_id2"]
        photo_id3["photo_id3"]
        painLevel["painLevel"]
        sensitivityField["sensitivityField"]
        stain["stain"]
        aestheticDiscomfort["aestheticDiscomfort"]
        userObservations["userObservations"]
        specialistObservations["specialistObservations"]
        diagnosis["diagnosis"]
        created_at["created_at"]
        updated_at["updated_at"]
    end

    subgraph CondOcc["OMOP: Condition_Occurrence"]
        co_condition_occurrence_id["condition_occurrence_id 🔑"]
        co_person_id["person_id 🔑"]
        co_condition_concept_id["condition_concept_id"]
        co_condition_start_date["condition_start_date"]
        co_condition_end_date["condition_end_date"]
    end

    subgraph MeasPain["OMOP: Measurement (Pain Level)"]
        mp_person_id["person_id 🔑"]
        mp_concept["measurement_concept_id 🔑<br/>(CID_PAIN_LEVEL)"]
        mp_value["value_as_number"]
        mp_date["measurement_date"]
    end

    subgraph ObsSensitivity["OMOP: Observation (Sensitivity Field)"]
        os_person_id["person_id 🔑"]
        os_concept["observation_concept_id 🔑<br/>(CID_SENSITIVITY_FIELD)"]
        os_value["value_as_concept_id<br/>(Yes/No)"]
        os_date["observation_date"]
    end

    subgraph ObsStain["OMOP: Observation (Stain)"]
        ost_person_id["person_id 🔑"]
        ost_concept["observation_concept_id 🔑<br/>(CID_STAIN)"]
        ost_value["value_as_concept_id<br/>(Yes/No)"]
        ost_date["observation_date"]
    end

    subgraph ObsAesthetic["OMOP: Observation (Aesthetic Discomfort)"]
        oa_person_id["person_id 🔑"]
        oa_concept["observation_concept_id 🔑<br/>(CID_AESTHETIC_DISCOMFORT)"]
        oa_value["value_as_concept_id<br/>(Yes/No)"]
        oa_date["observation_date"]
    end

    subgraph NoteUser["OMOP: Note (User Observations)"]
        nu_person_id["person_id 🔑"]
        nu_concept["note_class_concept_id 🔑<br/>(CID_NOTE_USER)"]
        nu_text["note_text"]
        nu_date["note_date"]
    end

    subgraph NoteSpecialist["OMOP: Note (Specialist Observations)"]
        ns_person_id["person_id 🔑"]
        ns_concept["note_class_concept_id 🔑<br/>(CID_NOTE_SPECIALIST)"]
        ns_text["note_text"]
        ns_date["note_date"]
    end

    subgraph FR["OMOP: Fact_Relationship (MIH ↔ Photos)"]
        fr_domain1["domain_concept_id_1"]
        fr_fact1["fact_id_1"]
        fr_domain2["domain_concept_id_2"]
        fr_fact2["fact_id_2"]
    end

    subgraph Custom: PatientNonClinicalInfos
        internal_ctrl["created_at"]
        custom_person_id["person_id"]
    end

    %% CONDITION_OCCURRENCE mappings
    mih_id -->|"row_condition_occurrence"| co_condition_occurrence_id
    patient_id -->|"row_condition_occurrence"| co_person_id
    diagnosis -->|"row_condition_occurrence<br/>(choicemap)"| co_condition_concept_id
    start_date -->|"row_condition_occurrence"| co_condition_start_date
    end_date -->|"row_condition_occurrence"| co_condition_end_date

    %% MEASUREMENT mappings
    painLevel -->|"row_measurement"| mp_value

    %% OBSERVATION mappings
    sensitivityField -->|"row_observation"| os_value
    stain -->|"row_observation"| ost_value
    aestheticDiscomfort -->|"row_observation"| oa_value

    %% NOTE mappings
    userObservations -->|"row_note"| nu_text
    specialistObservations -->|"row_note"| ns_text

    %% FACT_RELATIONSHIP mappings
    photo_id1 -->|"row_fact_relationship"| fr_fact2
    photo_id2 -->|"row_fact_relationship"| fr_fact2
    photo_id3 -->|"row_fact_relationship"| fr_fact2

    %% NOT MAPPED
    created_at --> internal_ctrl
    patient_id --> custom_person_id

    %% person_id propagation
    co_person_id --> mp_person_id
    co_person_id --> os_person_id
    co_person_id --> ost_person_id
    co_person_id --> oa_person_id
    co_person_id --> nu_person_id
    co_person_id --> ns_person_id

    %% condition_occurrence_id propagation to FR
    co_condition_occurrence_id --> fr_fact1

    %% updated_at propagation
    updated_at -->|"row_measurement"| mp_date
    updated_at -->|"row_observation"| os_date
    updated_at -->|"row_observation"| ost_date
    updated_at -->|"row_observation"| oa_date
    updated_at -->|"row_note"| nu_date
    updated_at -->|"row_note"| ns_date

    style MIH fill:#e1f5ff
    style CondOcc fill:#fff4e1
    style MeasPain fill:#f5e1ff
    style ObsSensitivity fill:#f5e1ff
    style ObsStain fill:#f5e1ff
    style ObsAesthetic fill:#f5e1ff
    style NoteUser fill:#f5e1ff
    style NoteSpecialist fill:#f5e1ff
    style FR fill:#f5e1ff