Radiology Information System Data Specifications

CREATE TABLE radiology_orders (
    order_id                  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_order_id         VARCHAR(64),
    patient_id                BIGINT NOT NULL,
    encounter_id              BIGINT NOT NULL,
    ordering_provider_id      BIGINT NOT NULL,
    ordering_user_id          BIGINT,
    facility_id               BIGINT NOT NULL,
    department_id             BIGINT NOT NULL,
    payer_id                  BIGINT,
    exam_code_cpt             VARCHAR(10) NOT NULL,
    exam_description          VARCHAR(255) NOT NULL,
    body_part_code            VARCHAR(32),
    body_part_display         VARCHAR(128),
    laterality                VARCHAR(10),
    modality_type             VARCHAR(10) NOT NULL,
    clinical_indication       TEXT NOT NULL,
    icd10am_code              VARCHAR(10),
    priority                  VARCHAR(16) NOT NULL DEFAULT 'Routine',
    order_status              VARCHAR(20) NOT NULL DEFAULT 'new',
    order_datetime            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    scheduled_datetime        TIMESTAMP,
    protocol_id               BIGINT,
    requires_prior_auth       BOOLEAN NOT NULL DEFAULT FALSE,
    prior_auth_status         VARCHAR(20),
    prior_auth_number         VARCHAR(64),
    prior_auth_checked_at     TIMESTAMP,
    contraindications_json    JSONB,
    notes                     TEXT,
    hie_submission_status     VARCHAR(20) NOT NULL DEFAULT 'not_required',
    hie_last_submission_at    TIMESTAMP,
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                BIGINT,
    updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                BIGINT,
    CONSTRAINT uq_radiology_orders_external UNIQUE (external_order_id),
    CONSTRAINT fk_rord_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rord_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rord_ordering_provider
        FOREIGN KEY (ordering_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rord_ordering_user
        FOREIGN KEY (ordering_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rord_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rord_department
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rord_payer
        FOREIGN KEY (payer_id) REFERENCES payers(payer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rord_protocol
        FOREIGN KEY (protocol_id) REFERENCES radiology_protocols(protocol_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rord_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rord_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_rord_priority
        CHECK (priority IN ('Routine','Urgent','STAT','Wet')),
    CONSTRAINT chk_rord_laterality
        CHECK (laterality IS NULL OR laterality IN ('L','R','B','NA')),
    CONSTRAINT chk_rord_status
        CHECK (order_status IN ('new','protocolling','scheduled','in_progress','completed','cancelled','no_show')),
    CONSTRAINT chk_rord_hie_status
        CHECK (hie_submission_status IN ('not_required','pending','sent','accepted','rejected')),
    CONSTRAINT chk_rord_sched_after_order
        CHECK (scheduled_datetime IS NULL OR scheduled_datetime >= order_datetime)
);

CREATE INDEX idx_rord_patient ON radiology_orders(patient_id);
CREATE INDEX idx_rord_encounter ON radiology_orders(encounter_id);
CREATE INDEX idx_rord_ordering_provider ON radiology_orders(ordering_provider_id);
CREATE INDEX idx_rord_status_priority ON radiology_orders(order_status, priority);
CREATE INDEX idx_rord_modality_sched ON radiology_orders(modality_type, scheduled_datetime);
CREATE INDEX idx_rord_hie_status ON radiology_orders(hie_submission_status)
    WHERE hie_submission_status IN ('pending','rejected');
CREATE INDEX idx_rord_prior_auth_status ON radiology_orders(prior_auth_status)
    WHERE requires_prior_auth = TRUE;

CREATE TABLE radiology_exams (
    exam_id                   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id                  BIGINT NOT NULL,
    patient_id                BIGINT NOT NULL,
    encounter_id              BIGINT NOT NULL,
    technologist_user_id      BIGINT,
    technologist_provider_id  BIGINT,
    modality_resource_id      BIGINT NOT NULL,
    exam_status               VARCHAR(20) NOT NULL DEFAULT 'scheduled',
    check_in_time             TIMESTAMP,
    exam_start_time           TIMESTAMP,
    exam_end_time             TIMESTAMP,
    contrast_used             BOOLEAN NOT NULL DEFAULT FALSE,
    contrast_type_code        VARCHAR(32),
    contrast_volume_ml        NUMERIC(6,2),
    contrast_route            VARCHAR(20),
    accession_number          VARCHAR(32) NOT NULL,
    study_instance_uid        VARCHAR(64),
    series_instance_uids      JSONB,
    image_count               INT,
    tech_notes                TEXT,
    qa_status                 VARCHAR(20),
    repeat_flag               BOOLEAN NOT NULL DEFAULT FALSE,
    repeat_reason             TEXT,
    billing_exported          BOOLEAN NOT NULL DEFAULT FALSE,
    billing_exported_at       TIMESTAMP,
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                BIGINT,
    updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                BIGINT,
    CONSTRAINT uq_rexam_accession UNIQUE (accession_number),
    CONSTRAINT fk_rexam_order
        FOREIGN KEY (order_id) REFERENCES radiology_orders(order_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rexam_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rexam_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rexam_tech_user
        FOREIGN KEY (technologist_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rexam_tech_provider
        FOREIGN KEY (technologist_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rexam_modality_resource
        FOREIGN KEY (modality_resource_id) REFERENCES modality_resources(resource_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rexam_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rexam_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_rexam_status
        CHECK (exam_status IN ('scheduled','arrived','in_progress','images_acquired','completed','cancelled','no_show')),
    CONSTRAINT chk_rexam_times
        CHECK (
            (exam_start_time IS NULL OR check_in_time IS NULL OR exam_start_time >= check_in_time) AND
            (exam_end_time IS NULL OR exam_start_time IS NULL OR exam_end_time >= exam_start_time)
        ),
    CONSTRAINT chk_rexam_contrast_volume
        CHECK (contrast_volume_ml IS NULL OR contrast_volume_ml >= 0),
    CONSTRAINT chk_rexam_qa_status
        CHECK (qa_status IS NULL OR qa_status IN ('pass','fail','needs_review')),
    CONSTRAINT chk_rexam_repeat_reason
        CHECK (repeat_flag = FALSE OR repeat_reason IS NOT NULL)
);

CREATE INDEX idx_rexam_order ON radiology_exams(order_id);
CREATE INDEX idx_rexam_patient ON radiology_exams(patient_id);
CREATE INDEX idx_rexam_status ON radiology_exams(exam_status);
CREATE INDEX idx_rexam_modality_status ON radiology_exams(modality_resource_id, exam_status);
CREATE INDEX idx_rexam_accession ON radiology_exams(accession_number);
CREATE INDEX idx_rexam_billing_exported ON radiology_exams(billing_exported)
    WHERE billing_exported = FALSE;

CREATE TABLE radiology_reports (
    report_id                 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    exam_id                   BIGINT NOT NULL,
    order_id                  BIGINT NOT NULL,
    patient_id                BIGINT NOT NULL,
    encounter_id              BIGINT NOT NULL,
    radiologist_user_id       BIGINT NOT NULL,
    radiologist_provider_id   BIGINT,
    report_status             VARCHAR(20) NOT NULL DEFAULT 'draft',
    findings_text             TEXT,
    impression_text           TEXT,
    structured_findings_json  JSONB,
    report_template_id        BIGINT,
    dictation_start           TIMESTAMP,
    dictation_end             TIMESTAMP,
    preliminary_sign_datetime TIMESTAMP,
    final_sign_datetime       TIMESTAMP,
    is_critical               BOOLEAN NOT NULL DEFAULT FALSE,
    critical_finding_text     TEXT,
    hie_submission_status     VARCHAR(20) NOT NULL DEFAULT 'pending',
    hie_last_submission_at    TIMESTAMP,
    version_number            INT NOT NULL DEFAULT 1,
    supersedes_report_id      BIGINT,
    language_code             VARCHAR(10) NOT NULL DEFAULT 'en',
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                BIGINT,
    updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                BIGINT,
    CONSTRAINT fk_rrep_exam
        FOREIGN KEY (exam_id) REFERENCES radiology_exams(exam_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rrep_order
        FOREIGN KEY (order_id) REFERENCES radiology_orders(order_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rrep_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rrep_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rrep_radiologist_user
        FOREIGN KEY (radiologist_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rrep_radiologist_provider
        FOREIGN KEY (radiologist_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rrep_template
        FOREIGN KEY (report_template_id) REFERENCES radiology_templates(template_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rrep_supersedes
        FOREIGN KEY (supersedes_report_id) REFERENCES radiology_reports(report_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rrep_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rrep_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_rrep_status
        CHECK (report_status IN ('draft','preliminary','final','corrected','cancelled')),
    CONSTRAINT chk_rrep_hie_status
        CHECK (hie_submission_status IN ('pending','sent','accepted','rejected')),
    CONSTRAINT chk_rrep_dictation_times
        CHECK (dictation_end IS NULL OR dictation_start IS NULL OR dictation_end >= dictation_start),
    CONSTRAINT chk_rrep_language
        CHECK (language_code IN ('en','ar')),
    CONSTRAINT chk_rrep_critical_text
        CHECK (is_critical = FALSE OR critical_finding_text IS NOT NULL)
);

CREATE INDEX idx_rrep_exam ON radiology_reports(exam_id);
CREATE INDEX idx_rrep_patient ON radiology_reports(patient_id);
CREATE INDEX idx_rrep_status ON radiology_reports(report_status);
CREATE INDEX idx_rrep_final_signed ON radiology_reports(final_sign_datetime)
    WHERE report_status IN ('final','corrected');
CREATE INDEX idx_rrep_hie_status ON radiology_reports(hie_submission_status)
    WHERE hie_submission_status IN ('pending','rejected');
CREATE INDEX idx_rrep_radiologist ON radiology_reports(radiologist_user_id, final_sign_datetime);

CREATE TABLE radiology_report_addenda (
    addendum_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    report_id                BIGINT NOT NULL,
    radiologist_user_id      BIGINT NOT NULL,
    radiologist_provider_id  BIGINT,
    addendum_type            VARCHAR(20) NOT NULL,
    addendum_text            TEXT NOT NULL,
    reason                   TEXT,
    signed_datetime          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_significant_change    BOOLEAN NOT NULL DEFAULT FALSE,
    created_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by               BIGINT,
    updated_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by               BIGINT,
    CONSTRAINT fk_radd_report
        FOREIGN KEY (report_id) REFERENCES radiology_reports(report_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_radd_radiologist_user
        FOREIGN KEY (radiologist_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_radd_radiologist_provider
        FOREIGN KEY (radiologist_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_radd_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_radd_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_radd_type
        CHECK (addendum_type IN ('correction','additional_info','clinical_correlation'))
);

CREATE INDEX idx_radd_report ON radiology_report_addenda(report_id);
CREATE INDEX idx_radd_radiologist ON radiology_report_addenda(radiologist_user_id);

CREATE TABLE radiology_worklist (
    worklist_id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    exam_id                    BIGINT NOT NULL,
    assigned_radiologist_user_id BIGINT,
    priority_score             INT NOT NULL DEFAULT 0,
    worklist_status            VARCHAR(20) NOT NULL DEFAULT 'queued',
    added_datetime             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_datetime           TIMESTAMP,
    completed_datetime         TIMESTAMP,
    queue_name                 VARCHAR(64),
    auto_assigned              BOOLEAN NOT NULL DEFAULT TRUE,
    lock_user_id               BIGINT,
    lock_acquired_at           TIMESTAMP,
    created_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                 BIGINT,
    updated_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                 BIGINT,
    CONSTRAINT fk_rwl_exam
        FOREIGN KEY (exam_id) REFERENCES radiology_exams(exam_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_rwl_assigned_radiologist
        FOREIGN KEY (assigned_radiologist_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rwl_lock_user
        FOREIGN KEY (lock_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rwl_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rwl_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_rwl_status
        CHECK (worklist_status IN ('queued','in_progress','completed','removed')),
    CONSTRAINT chk_rwl_times
        CHECK (
            (started_datetime IS NULL OR started_datetime >= added_datetime) AND
            (completed_datetime IS NULL OR completed_datetime >= started_datetime)
        )
);

CREATE INDEX idx_rwl_exam ON radiology_worklist(exam_id);
CREATE INDEX idx_rwl_status_priority ON radiology_worklist(worklist_status, priority_score DESC);
CREATE INDEX idx_rwl_assigned ON radiology_worklist(assigned_radiologist_user_id, worklist_status);
CREATE INDEX idx_rwl_queue ON radiology_worklist(queue_name, worklist_status);

CREATE TABLE radiology_protocols (
    protocol_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    exam_code_cpt             VARCHAR(10) NOT NULL,
    modality_type             VARCHAR(10) NOT NULL,
    protocol_name             VARCHAR(128) NOT NULL,
    protocol_parameters_json  JSONB NOT NULL DEFAULT '{}'::JSONB,
    contrast_required         BOOLEAN NOT NULL DEFAULT FALSE,
    contrast_type_code        VARCHAR(32),
    default_prep_instructions TEXT,
    estimated_duration_minutes INT,
    age_min_years             INT,
    age_max_years             INT,
    is_active                 BOOLEAN NOT NULL DEFAULT TRUE,
    effective_from            DATE NOT NULL DEFAULT CURRENT_DATE,
    effective_to              DATE,
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                BIGINT,
    updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                BIGINT,
    CONSTRAINT fk_rprot_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rprot_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_rprot_age_range
        CHECK (age_min_years IS NULL OR age_min_years >= 0),
    CONSTRAINT chk_rprot_age_max
        CHECK (age_max_years IS NULL OR age_min_years IS NULL OR age_max_years >= age_min_years),
    CONSTRAINT chk_rprot_effective_dates
        CHECK (effective_to IS NULL OR effective_to >= effective_from)
);

CREATE INDEX idx_rprot_exam_modality ON radiology_protocols(exam_code_cpt, modality_type)
    WHERE is_active = TRUE;
CREATE INDEX idx_rprot_effective ON radiology_protocols(effective_from, effective_to);

CREATE TABLE modality_resources (
    resource_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    facility_id           BIGINT NOT NULL,
    department_id         BIGINT NOT NULL,
    modality_type         VARCHAR(10) NOT NULL,
    equipment_name        VARCHAR(128) NOT NULL,
    manufacturer          VARCHAR(128),
    model                 VARCHAR(128),
    serial_number         VARCHAR(64),
    ae_title              VARCHAR(32) NOT NULL,
    ip_address            VARCHAR(45),
    port                  INT DEFAULT 104,
    is_active             BOOLEAN NOT NULL DEFAULT TRUE,
    max_daily_slots       INT,
    maintenance_status    VARCHAR(20) NOT NULL DEFAULT 'operational',
    maintenance_notes     TEXT,
    vendor_service_contact VARCHAR(128),
    last_qc_date          DATE,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by            BIGINT,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by            BIGINT,
    CONSTRAINT uq_mres_ae_title UNIQUE (ae_title),
    CONSTRAINT fk_mres_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_mres_department
        FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_mres_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_mres_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_mres_port
        CHECK (port IS NULL OR (port >= 1 AND port <= 65535)),
    CONSTRAINT chk_mres_status
        CHECK (maintenance_status IN ('operational','maintenance','down'))
);

CREATE INDEX idx_mres_facility_modality ON modality_resources(facility_id, modality_type)
    WHERE is_active = TRUE;
CREATE INDEX idx_mres_status ON modality_resources(maintenance_status);

CREATE TABLE radiology_templates (
    template_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    template_name          VARCHAR(128) NOT NULL,
    modality_type          VARCHAR(10),
    body_part_code         VARCHAR(32),
    body_part_display      VARCHAR(128),
    template_content       TEXT NOT NULL,
    structured_fields_json JSONB,
    language_code          VARCHAR(10) NOT NULL DEFAULT 'en',
    created_by_user_id     BIGINT NOT NULL,
    is_active              BOOLEAN NOT NULL DEFAULT TRUE,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by             BIGINT,
    CONSTRAINT fk_rtpl_created_by
        FOREIGN KEY (created_by_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rtpl_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_rtpl_language
        CHECK (language_code IN ('en','ar'))
);

CREATE INDEX idx_rtpl_modality_bodypart ON radiology_templates(modality_type, body_part_code)
    WHERE is_active = TRUE;
CREATE INDEX idx_rtpl_name ON radiology_templates(template_name);

CREATE TABLE critical_result_notifications (
    notification_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    report_id                    BIGINT NOT NULL,
    critical_finding             TEXT NOT NULL,
    notifying_radiologist_user_id BIGINT NOT NULL,
    target_provider_id           BIGINT NOT NULL,
    notification_method          VARCHAR(20) NOT NULL,
    sent_datetime                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    acknowledged_datetime        TIMESTAMP,
    acknowledged_by_user_id      BIGINT,
    escalation_level             INT NOT NULL DEFAULT 0,
    read_back_confirmed          BOOLEAN NOT NULL DEFAULT FALSE,
    read_back_datetime           TIMESTAMP,
    status                       VARCHAR(20) NOT NULL DEFAULT 'pending',
    failure_reason               TEXT,
    created_at                   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                   BIGINT,
    updated_at                   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                   BIGINT,
    CONSTRAINT fk_crn_report
        FOREIGN KEY (report_id) REFERENCES radiology_reports(report_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_crn_notifying_radiologist
        FOREIGN KEY (notifying_radiologist_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_crn_target_provider
        FOREIGN KEY (target_provider_id) REFERENCES providers(provider_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_crn_ack_by
        FOREIGN KEY (acknowledged_by_user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_crn_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_crn_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_crn_method
        CHECK (notification_method IN ('in_app','phone','sms','email','pager')),
    CONSTRAINT chk_crn_status
        CHECK (status IN ('pending','acknowledged','failed')),
    CONSTRAINT chk_crn_ack_time
        CHECK (acknowledged_datetime IS NULL OR acknowledged_datetime >= sent_datetime),
    CONSTRAINT chk_crn_readback
        CHECK (read_back_confirmed = FALSE OR read_back_datetime IS NOT NULL)
);

CREATE INDEX idx_crn_report ON critical_result_notifications(report_id);
CREATE INDEX idx_crn_status ON critical_result_notifications(status);
CREATE INDEX idx_crn_target_provider ON critical_result_notifications(target_provider_id, status);
CREATE INDEX idx_crn_sent_datetime ON critical_result_notifications(sent_datetime);

CREATE TABLE radiation_dose_records (
    dose_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    exam_id               BIGINT NOT NULL,
    patient_id            BIGINT NOT NULL,
    modality_type         VARCHAR(10) NOT NULL,
    dose_type             VARCHAR(20) NOT NULL,
    dose_value            NUMERIC(12,4) NOT NULL,
    dose_unit             VARCHAR(16) NOT NULL,
    body_region_code      VARCHAR(32),
    body_region_display   VARCHAR(128),
    ctdi_vol              NUMERIC(12,4),
    dlp                   NUMERIC(12,4),
    dap                   NUMERIC(12,4),
    effective_dose_msv    NUMERIC(12,4),
    drl_exceeded          BOOLEAN NOT NULL DEFAULT FALSE,
    drl_reference_id      BIGINT,
    captured_from_rdsr    BOOLEAN NOT NULL DEFAULT FALSE,
    rdsr_instance_uid     VARCHAR(64),
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by            BIGINT,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by            BIGINT,
    CONSTRAINT fk_rdose_exam
        FOREIGN KEY (exam_id) REFERENCES radiology_exams(exam_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_rdose_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_rdose_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_rdose_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_rdose_value
        CHECK (dose_value >= 0),
    CONSTRAINT chk_rdose_ctdi
        CHECK (ctdi_vol IS NULL OR ctdi_vol >= 0),
    CONSTRAINT chk_rdose_dlp
        CHECK (dlp IS NULL OR dlp >= 0),
    CONSTRAINT chk_rdose_dap
        CHECK (dap IS NULL OR dap >= 0),
    CONSTRAINT chk_rdose_eff
        CHECK (effective_dose_msv IS NULL OR effective_dose_msv >= 0),
    CONSTRAINT chk_rdose_type
        CHECK (dose_type IN ('ctdi_vol','dlp','dap','fluoro_time','other'))
);

CREATE INDEX idx_rdose_patient ON radiation_dose_records(patient_id);
CREATE INDEX idx_rdose_exam ON radiation_dose_records(exam_id);
CREATE INDEX idx_rdose_modality ON radiation_dose_records(modality_type);
CREATE INDEX idx_rdose_drl_exceeded ON radiation_dose_records(drl_exceeded)
    WHERE drl_exceeded = TRUE;

CREATE TABLE radiology_quality_metrics (
    metric_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    period_start     TIMESTAMP NOT NULL,
    period_end       TIMESTAMP NOT NULL,
    user_id          BIGINT NOT NULL,
    user_role        VARCHAR(32) NOT NULL,
    metric_type      VARCHAR(64) NOT NULL,
    metric_value     NUMERIC(18,4) NOT NULL,
    benchmark_value  NUMERIC(18,4),
    unit             VARCHAR(32),
    details_json     JSONB,
    created_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by       BIGINT,
    CONSTRAINT fk_rqm_user
        FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_rqm_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_rqm_period
        CHECK (period_end >= period_start)
);

CREATE INDEX idx_rqm_user_period ON radiology_quality_metrics(user_id, period_start, period_end);
CREATE INDEX idx_rqm_type ON radiology_quality_metrics(metric_type);