Blood Bank Management Data Specifications

CREATE TABLE blood_donors (
    donor_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    emirates_id           VARCHAR(18) NOT NULL,
    mrn_patient_id        BIGINT,
    first_name_en         VARCHAR(100) NOT NULL,
    middle_name_en        VARCHAR(100),
    last_name_en          VARCHAR(100) NOT NULL,
    first_name_ar         VARCHAR(100),
    last_name_ar          VARCHAR(100),
    dob                   DATE NOT NULL,
    gender                VARCHAR(10) NOT NULL DEFAULT 'unknown',
    nationality_code      VARCHAR(3),
    phone_mobile          VARCHAR(20) NOT NULL,
    phone_home            VARCHAR(20),
    email                 VARCHAR(150),
    address_line1         VARCHAR(200),
    address_line2         VARCHAR(200),
    emirate               VARCHAR(20),
    abo_group             VARCHAR(2),
    rh_type               VARCHAR(3),
    blood_type_source     VARCHAR(20),
    donor_status          VARCHAR(20) NOT NULL DEFAULT 'active',
    total_donations       INTEGER NOT NULL DEFAULT 0,
    last_donation_date    DATE,
    deferral_reason_code  VARCHAR(20),
    deferral_until        DATE,
    consent_signed        BOOLEAN NOT NULL DEFAULT FALSE,
    consent_datetime      TIMESTAMP,
    notes                 TEXT,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by            BIGINT NOT NULL,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by            BIGINT,
    CONSTRAINT uq_blood_donors_emirates_id UNIQUE (emirates_id),
    CONSTRAINT fk_blood_donors_patient
        FOREIGN KEY (mrn_patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_blood_donors_deferral_reason
        FOREIGN KEY (deferral_reason_code) REFERENCES md_donor_deferral_reasons(code),
    CONSTRAINT fk_blood_donors_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_blood_donors_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT chk_blood_donors_gender
        CHECK (gender IN ('male','female','other','unknown')),
    CONSTRAINT chk_blood_donors_status
        CHECK (donor_status IN ('active','temporarily_deferred','permanently_deferred','inactive','deceased')),
    CONSTRAINT chk_blood_donors_emirate
        CHECK (emirate IS NULL OR emirate IN ('Abu Dhabi','Dubai','Sharjah','Ajman','Umm Al Quwain','Ras Al Khaimah','Fujairah')),
    CONSTRAINT chk_blood_donors_total_donations
        CHECK (total_donations >= 0),
    CONSTRAINT chk_blood_donors_deferral_dates
        CHECK (deferral_until IS NULL OR deferral_until >= CURRENT_DATE)
);

CREATE INDEX idx_blood_donors_emirates_id ON blood_donors(emirates_id);
CREATE INDEX idx_blood_donors_name_en ON blood_donors(last_name_en, first_name_en);
CREATE INDEX idx_blood_donors_status ON blood_donors(donor_status);
CREATE INDEX idx_blood_donors_deferral ON blood_donors(donor_status, deferral_until);

CREATE TABLE blood_donations (
    donation_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    donor_id                  BIGINT NOT NULL,
    donation_number           VARCHAR(30) NOT NULL,
    donation_datetime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    donation_end_datetime     TIMESTAMP,
    donation_type             VARCHAR(20) NOT NULL,
    volume_ml                 INTEGER,
    facility_id               BIGINT NOT NULL,
    collection_site           VARCHAR(100),
    collector_id              BIGINT NOT NULL,
    pre_hb_g_dl               NUMERIC(4,1),
    complications             TEXT,
    screening_pass            BOOLEAN NOT NULL DEFAULT FALSE,
    infectious_disease_status VARCHAR(20) NOT NULL DEFAULT 'pending',
    infectious_panel_result_id BIGINT,
    status                    VARCHAR(20) NOT NULL DEFAULT 'collected',
    notes                     TEXT,
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                BIGINT NOT NULL,
    updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                BIGINT,
    CONSTRAINT uq_blood_donations_number UNIQUE (donation_number),
    CONSTRAINT fk_blood_donations_donor
        FOREIGN KEY (donor_id) REFERENCES blood_donors(donor_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_blood_donations_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
    CONSTRAINT fk_blood_donations_collector
        FOREIGN KEY (collector_id) REFERENCES users(user_id),
    CONSTRAINT fk_blood_donations_lab_result
        FOREIGN KEY (infectious_panel_result_id) REFERENCES lab_results(result_id),
    CONSTRAINT fk_blood_donations_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_blood_donations_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT chk_blood_donations_type
        CHECK (donation_type IN ('whole_blood','apheresis_platelet','apheresis_plasma','autologous','directed')),
    CONSTRAINT chk_blood_donations_status
        CHECK (status IN ('scheduled','collected','quarantined','released','discarded')),
    CONSTRAINT chk_blood_donations_infectious_status
        CHECK (infectious_disease_status IN ('pending','negative','positive','indeterminate')),
    CONSTRAINT chk_blood_donations_times
        CHECK (donation_end_datetime IS NULL OR donation_end_datetime >= donation_datetime),
    CONSTRAINT chk_blood_donations_volume
        CHECK (volume_ml IS NULL OR volume_ml > 0)
);

CREATE INDEX idx_blood_donations_donor ON blood_donations(donor_id);
CREATE INDEX idx_blood_donations_number ON blood_donations(donation_number);
CREATE INDEX idx_blood_donations_status ON blood_donations(status);
CREATE INDEX idx_blood_donations_infectious ON blood_donations(infectious_disease_status);
CREATE INDEX idx_blood_donations_datetime ON blood_donations(donation_datetime);

CREATE TABLE blood_components (
    component_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    donation_id                BIGINT NOT NULL,
    component_number           VARCHAR(40) NOT NULL,
    component_type             VARCHAR(30) NOT NULL,
    abo_group                  VARCHAR(2) NOT NULL,
    rh_type                    VARCHAR(3) NOT NULL,
    volume_ml                  INTEGER,
    collection_datetime        TIMESTAMP NOT NULL,
    expiry_datetime            TIMESTAMP NOT NULL,
    status                     VARCHAR(20) NOT NULL DEFAULT 'quarantined',
    irradiated                 BOOLEAN NOT NULL DEFAULT FALSE,
    irradiation_datetime       TIMESTAMP,
    leukoreduced               BOOLEAN NOT NULL DEFAULT FALSE,
    washed                     BOOLEAN NOT NULL DEFAULT FALSE,
    antigen_negative_for       VARCHAR(200),
    special_attributes         JSONB,
    quarantine_reason          VARCHAR(200),
    final_disposition          VARCHAR(20),
    final_disposition_datetime TIMESTAMP,
    created_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                 BIGINT NOT NULL,
    updated_at                 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                 BIGINT,
    CONSTRAINT uq_blood_components_number UNIQUE (component_number),
    CONSTRAINT fk_blood_components_donation
        FOREIGN KEY (donation_id) REFERENCES blood_donations(donation_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_blood_components_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_blood_components_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT chk_blood_components_status
        CHECK (status IN ('quarantined','available','reserved','issued','transfused','discarded')),
    CONSTRAINT chk_blood_components_final_disposition
        CHECK (final_disposition IS NULL OR final_disposition IN ('transfused','discarded','returned','split')),
    CONSTRAINT chk_blood_components_times
        CHECK (expiry_datetime > collection_datetime),
    CONSTRAINT chk_blood_components_volume
        CHECK (volume_ml IS NULL OR volume_ml > 0)
);

CREATE INDEX idx_blood_components_donation ON blood_components(donation_id);
CREATE INDEX idx_blood_components_status_expiry ON blood_components(status, expiry_datetime);
CREATE INDEX idx_blood_components_group ON blood_components(abo_group, rh_type, component_type, status);
CREATE INDEX idx_blood_components_number ON blood_components(component_number);

CREATE TABLE blood_component_inventory (
    inventory_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    component_id              BIGINT NOT NULL,
    facility_id               BIGINT NOT NULL,
    storage_location          VARCHAR(100) NOT NULL,
    temperature_zone          VARCHAR(20) NOT NULL,
    status                    VARCHAR(20) NOT NULL DEFAULT 'in_stock',
    received_datetime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    issued_datetime           TIMESTAMP,
    issued_to_patient_id      BIGINT,
    issued_to_encounter_id    BIGINT,
    issued_to_location        VARCHAR(100),
    issued_by                 BIGINT,
    returned_datetime         TIMESTAMP,
    returned_condition        VARCHAR(20),
    current_temperature_c     NUMERIC(4,1),
    in_transit_to_facility_id BIGINT,
    notes                     TEXT,
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                BIGINT NOT NULL,
    updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                BIGINT,
    CONSTRAINT fk_bci_component
        FOREIGN KEY (component_id) REFERENCES blood_components(component_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_bci_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
    CONSTRAINT fk_bci_issued_patient
        FOREIGN KEY (issued_to_patient_id) REFERENCES patients(patient_id),
    CONSTRAINT fk_bci_issued_encounter
        FOREIGN KEY (issued_to_encounter_id) REFERENCES encounters(encounter_id),
    CONSTRAINT fk_bci_issued_by
        FOREIGN KEY (issued_by) REFERENCES users(user_id),
    CONSTRAINT fk_bci_transit_facility
        FOREIGN KEY (in_transit_to_facility_id) REFERENCES facilities(facility_id),
    CONSTRAINT fk_bci_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_bci_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT chk_bci_status
        CHECK (status IN ('in_stock','reserved','issued','in_transit','returned','discarded')),
    CONSTRAINT chk_bci_return_condition
        CHECK (returned_condition IS NULL OR returned_condition IN ('acceptable','temperature_excursion','damaged','other'))
);

CREATE INDEX idx_bci_component ON blood_component_inventory(component_id);
CREATE INDEX idx_bci_status_facility ON blood_component_inventory(facility_id, status);
CREATE INDEX idx_bci_patient ON blood_component_inventory(issued_to_patient_id);
CREATE INDEX idx_bci_expiry_lookup ON blood_component_inventory(facility_id, status, component_id);

CREATE TABLE blood_type_screen (
    ts_id                     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id                BIGINT NOT NULL,
    encounter_id              BIGINT NOT NULL,
    specimen_id               VARCHAR(40) NOT NULL,
    specimen_collection_datetime TIMESTAMP,
    abo_group                 VARCHAR(2) NOT NULL,
    rh_type                   VARCHAR(3) NOT NULL,
    forward_group             VARCHAR(20),
    reverse_group             VARCHAR(20),
    antibody_screen_result    VARCHAR(20) NOT NULL DEFAULT 'negative',
    antibody_specificity      VARCHAR(200),
    auto_control_result       VARCHAR(20),
    tested_by                 BIGINT NOT NULL,
    tested_datetime           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    valid_until               TIMESTAMP NOT NULL,
    historical_type_concordant BOOLEAN NOT NULL DEFAULT TRUE,
    historical_ts_id          BIGINT,
    comments                  TEXT,
    result_status             VARCHAR(20) NOT NULL DEFAULT 'final',
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                BIGINT NOT NULL,
    updated_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                BIGINT,
    CONSTRAINT fk_bts_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_bts_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
    CONSTRAINT fk_bts_tested_by
        FOREIGN KEY (tested_by) REFERENCES users(user_id),
    CONSTRAINT fk_bts_historical
        FOREIGN KEY (historical_ts_id) REFERENCES blood_type_screen(ts_id),
    CONSTRAINT fk_bts_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_bts_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT chk_bts_antibody_screen
        CHECK (antibody_screen_result IN ('negative','positive','inconclusive')),
    CONSTRAINT chk_bts_auto_control
        CHECK (auto_control_result IS NULL OR auto_control_result IN ('negative','positive')),
    CONSTRAINT chk_bts_result_status
        CHECK (result_status IN ('preliminary','final','amended','cancelled')),
    CONSTRAINT chk_bts_validity
        CHECK (valid_until > tested_datetime)
);

CREATE INDEX idx_bts_patient ON blood_type_screen(patient_id, valid_until);
CREATE INDEX idx_bts_specimen ON blood_type_screen(specimen_id);
CREATE INDEX idx_bts_antibody ON blood_type_screen(antibody_screen_result);
CREATE INDEX idx_bts_encounter ON blood_type_screen(encounter_id);

CREATE TABLE crossmatch_records (
    crossmatch_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id             BIGINT NOT NULL,
    component_id           BIGINT NOT NULL,
    ts_id                  BIGINT NOT NULL,
    order_id               BIGINT,
    crossmatch_type        VARCHAR(20) NOT NULL,
    result                 VARCHAR(20) NOT NULL,
    incompatibility_reason VARCHAR(200),
    performed_by           BIGINT NOT NULL,
    performed_datetime     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    valid_until            TIMESTAMP,
    comments               TEXT,
    created_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by             BIGINT NOT NULL,
    updated_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by             BIGINT,
    CONSTRAINT fk_cm_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_cm_component
        FOREIGN KEY (component_id) REFERENCES blood_components(component_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_cm_ts
        FOREIGN KEY (ts_id) REFERENCES blood_type_screen(ts_id),
    CONSTRAINT fk_cm_order
        FOREIGN KEY (order_id) REFERENCES transfusion_orders(order_id),
    CONSTRAINT fk_cm_performed_by
        FOREIGN KEY (performed_by) REFERENCES users(user_id),
    CONSTRAINT fk_cm_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_cm_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT chk_cm_type
        CHECK (crossmatch_type IN ('electronic','immediate_spin','ahg','other')),
    CONSTRAINT chk_cm_result
        CHECK (result IN ('compatible','incompatible','not_performed','cancelled')),
    CONSTRAINT chk_cm_incompatibility_reason
        CHECK (result <> 'incompatible' OR incompatibility_reason IS NOT NULL)
);

CREATE INDEX idx_cm_patient ON crossmatch_records(patient_id);
CREATE INDEX idx_cm_component ON crossmatch_records(component_id);
CREATE INDEX idx_cm_order ON crossmatch_records(order_id);
CREATE INDEX idx_cm_result ON crossmatch_records(result);

CREATE TABLE transfusion_orders (
    order_id                    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id                  BIGINT NOT NULL,
    encounter_id                BIGINT NOT NULL,
    ordering_provider_id        BIGINT NOT NULL,
    component_type_requested    VARCHAR(30) NOT NULL,
    units_requested             INTEGER NOT NULL DEFAULT 1,
    priority                    VARCHAR(20) NOT NULL DEFAULT 'routine',
    indication_code             VARCHAR(10),
    indication_text             VARCHAR(500),
    special_requirements        VARCHAR(200),
    target_hb_g_dl              NUMERIC(4,1),
    order_status                VARCHAR(20) NOT NULL DEFAULT 'pending',
    order_datetime              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    needed_by_datetime          TIMESTAMP,
    emergency_release           BOOLEAN NOT NULL DEFAULT FALSE,
    massive_transfusion_protocol BOOLEAN NOT NULL DEFAULT FALSE,
    comments                    TEXT,
    created_at                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                  BIGINT NOT NULL,
    updated_at                  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                  BIGINT,
    CONSTRAINT fk_to_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_to_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
    CONSTRAINT fk_to_provider
        FOREIGN KEY (ordering_provider_id) REFERENCES providers(provider_id),
    CONSTRAINT fk_to_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_to_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT fk_to_indication_code
        FOREIGN KEY (indication_code) REFERENCES md_transfusion_indications(code),
    CONSTRAINT chk_to_units
        CHECK (units_requested > 0),
    CONSTRAINT chk_to_priority
        CHECK (priority IN ('routine','urgent','stat','massive_transfusion')),
    CONSTRAINT chk_to_status
        CHECK (order_status IN ('pending','in_progress','completed','cancelled'))
);

CREATE INDEX idx_to_patient ON transfusion_orders(patient_id, order_status);
CREATE INDEX idx_to_encounter ON transfusion_orders(encounter_id);
CREATE INDEX idx_to_provider ON transfusion_orders(ordering_provider_id);
CREATE INDEX idx_to_priority ON transfusion_orders(priority);
CREATE INDEX idx_to_emergency ON transfusion_orders(emergency_release) WHERE emergency_release = TRUE;

CREATE TABLE transfusion_administration (
    admin_id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id                BIGINT NOT NULL,
    component_id            BIGINT NOT NULL,
    patient_id              BIGINT NOT NULL,
    encounter_id            BIGINT NOT NULL,
    verifier1_id            BIGINT NOT NULL,
    verifier2_id            BIGINT,
    start_datetime          TIMESTAMP NOT NULL,
    end_datetime            TIMESTAMP,
    volume_infused          INTEGER,
    rate_ml_per_hr          INTEGER,
    pre_vitals_json         JSONB,
    post_vitals_json        JSONB,
    intra_vitals_json       JSONB,
    reaction_occurred       BOOLEAN NOT NULL DEFAULT FALSE,
    reaction_id             BIGINT,
    status                  VARCHAR(20) NOT NULL DEFAULT 'in_progress',
    bedside_location        VARCHAR(100),
    product_barcode_scanned BOOLEAN NOT NULL DEFAULT FALSE,
    patient_barcode_scanned BOOLEAN NOT NULL DEFAULT FALSE,
    notes                   TEXT,
    created_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by              BIGINT NOT NULL,
    updated_at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by              BIGINT,
    CONSTRAINT fk_ta_order
        FOREIGN KEY (order_id) REFERENCES transfusion_orders(order_id),
    CONSTRAINT fk_ta_component
        FOREIGN KEY (component_id) REFERENCES blood_components(component_id),
    CONSTRAINT fk_ta_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    CONSTRAINT fk_ta_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
    CONSTRAINT fk_ta_verifier1
        FOREIGN KEY (verifier1_id) REFERENCES users(user_id),
    CONSTRAINT fk_ta_verifier2
        FOREIGN KEY (verifier2_id) REFERENCES users(user_id),
    CONSTRAINT fk_ta_reaction
        FOREIGN KEY (reaction_id) REFERENCES transfusion_reactions(reaction_id),
    CONSTRAINT fk_ta_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_ta_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT chk_ta_status
        CHECK (status IN ('planned','in_progress','completed','stopped','cancelled')),
    CONSTRAINT chk_ta_times
        CHECK (end_datetime IS NULL OR end_datetime >= start_datetime),
    CONSTRAINT chk_ta_volume
        CHECK (volume_infused IS NULL OR volume_infused >= 0),
    CONSTRAINT chk_ta_rate
        CHECK (rate_ml_per_hr IS NULL OR rate_ml_per_hr >= 0)
);

CREATE INDEX idx_ta_patient ON transfusion_administration(patient_id);
CREATE INDEX idx_ta_order ON transfusion_administration(order_id);
CREATE INDEX idx_ta_component ON transfusion_administration(component_id);
CREATE INDEX idx_ta_status ON transfusion_administration(status);
CREATE INDEX idx_ta_reaction ON transfusion_administration(reaction_occurred) WHERE reaction_occurred = TRUE;

CREATE TABLE transfusion_reactions (
    reaction_id                    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    admin_id                       BIGINT NOT NULL,
    patient_id                     BIGINT NOT NULL,
    component_id                   BIGINT NOT NULL,
    reaction_type                  VARCHAR(30) NOT NULL,
    severity                       VARCHAR(20) NOT NULL,
    symptoms                       TEXT NOT NULL,
    onset_time                     TIMESTAMP NOT NULL,
    investigation_findings         TEXT,
    clerical_check_ok              BOOLEAN NOT NULL DEFAULT TRUE,
    dat_result                     VARCHAR(20),
    hemolysis_check                VARCHAR(20),
    classification                 VARCHAR(50),
    reported_to_moh                BOOLEAN NOT NULL DEFAULT FALSE,
    moh_report_reference           VARCHAR(50),
    investigated_by                BIGINT NOT NULL,
    investigation_completed_datetime TIMESTAMP,
    outcome                        VARCHAR(30),
    related_lab_result_id          BIGINT,
    comments                       TEXT,
    created_at                     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by                     BIGINT NOT NULL,
    updated_at                     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by                     BIGINT,
    CONSTRAINT fk_tr_admin
        FOREIGN KEY (admin_id) REFERENCES transfusion_administration(admin_id),
    CONSTRAINT fk_tr_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
    CONSTRAINT fk_tr_component
        FOREIGN KEY (component_id) REFERENCES blood_components(component_id),
    CONSTRAINT fk_tr_investigated_by
        FOREIGN KEY (investigated_by) REFERENCES providers(provider_id),
    CONSTRAINT fk_tr_lab_result
        FOREIGN KEY (related_lab_result_id) REFERENCES lab_results(result_id),
    CONSTRAINT fk_tr_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_tr_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT fk_tr_reaction_type
        FOREIGN KEY (reaction_type) REFERENCES md_transfusion_reaction_types(code),
    CONSTRAINT chk_tr_severity
        CHECK (severity IN ('mild','moderate','severe','life_threatening','death')),
    CONSTRAINT chk_tr_dat_result
        CHECK (dat_result IS NULL OR dat_result IN ('positive','negative','not_done')),
    CONSTRAINT chk_tr_hemolysis
        CHECK (hemolysis_check IS NULL OR hemolysis_check IN ('present','absent','not_done')),
    CONSTRAINT chk_tr_outcome
        CHECK (outcome IS NULL OR outcome IN ('recovered','ongoing','death','unknown')),
    CONSTRAINT chk_tr_moh_reporting
        CHECK (reported_to_moh = FALSE OR moh_report_reference IS NOT NULL)
);

CREATE INDEX idx_tr_patient ON transfusion_reactions(patient_id);
CREATE INDEX idx_tr_type ON transfusion_reactions(reaction_type);
CREATE INDEX idx_tr_moh ON transfusion_reactions(reported_to_moh);
CREATE INDEX idx_tr_admin ON transfusion_reactions(admin_id);

CREATE TABLE blood_quarantine_log (
    quarantine_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    component_id         BIGINT NOT NULL,
    quarantine_reason    VARCHAR(200) NOT NULL,
    quarantined_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    quarantined_by       BIGINT NOT NULL,
    released_datetime    TIMESTAMP,
    released_by          BIGINT,
    release_reason       VARCHAR(200),
    final_disposition    VARCHAR(20),
    notes                TEXT,
    created_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by           BIGINT NOT NULL,
    updated_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by           BIGINT,
    CONSTRAINT fk_bql_component
        FOREIGN KEY (component_id) REFERENCES blood_components(component_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_bql_quarantined_by
        FOREIGN KEY (quarantined_by) REFERENCES users(user_id),
    CONSTRAINT fk_bql_released_by
        FOREIGN KEY (released_by) REFERENCES users(user_id),
    CONSTRAINT fk_bql_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_bql_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT chk_bql_disposition
        CHECK (final_disposition IS NULL OR final_disposition IN ('returned_to_inventory','discarded','transfused')),
    CONSTRAINT chk_bql_release_time
        CHECK (released_datetime IS NULL OR released_datetime >= quarantined_datetime)
);

CREATE INDEX idx_bql_component ON blood_quarantine_log(component_id);
CREATE INDEX idx_bql_quarantine_time ON blood_quarantine_log(quarantined_datetime);

CREATE TABLE blood_discard_log (
    discard_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    component_id             BIGINT NOT NULL,
    discard_reason           VARCHAR(100) NOT NULL,
    discard_datetime         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    discarded_by             BIGINT NOT NULL,
    witness_id               BIGINT,
    disposal_method          VARCHAR(50) NOT NULL,
    biohazard_manifest_number VARCHAR(50),
    notes                    TEXT,
    created_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by               BIGINT NOT NULL,
    updated_at               TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by               BIGINT,
    CONSTRAINT fk_bdl_component
        FOREIGN KEY (component_id) REFERENCES blood_components(component_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_bdl_discarded_by
        FOREIGN KEY (discarded_by) REFERENCES users(user_id),
    CONSTRAINT fk_bdl_witness
        FOREIGN KEY (witness_id) REFERENCES users(user_id),
    CONSTRAINT fk_bdl_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id),
    CONSTRAINT fk_bdl_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id),
    CONSTRAINT fk_bdl_discard_reason
        FOREIGN KEY (discard_reason) REFERENCES md_discard_reasons(code),
    CONSTRAINT chk_bdl_disposal_method
        CHECK (disposal_method IN ('biohazard_incineration','return_to_supplier','other'))
);

CREATE INDEX idx_bdl_component ON blood_discard_log(component_id);
CREATE INDEX idx_bdl_reason ON blood_discard_log(discard_reason);
CREATE INDEX idx_bdl_datetime ON blood_discard_log(discard_datetime);