Pharmacy Information System Data Specifications

CREATE TABLE pharmacy_orders (
    order_id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_order_id       VARCHAR(50),
    order_source_system     VARCHAR(30) NOT NULL DEFAULT 'CPOE',
    patient_id              BIGINT NOT NULL,
    encounter_id            BIGINT,
    ordering_provider_id    BIGINT NOT NULL,
    facility_id             BIGINT NOT NULL,
    department_id           BIGINT,
    order_type              VARCHAR(20) NOT NULL DEFAULT 'inpatient',
    order_status            VARCHAR(20) NOT NULL DEFAULT 'pending_verification',
    priority                VARCHAR(10) NOT NULL DEFAULT 'routine',
    drug_code_rxnorm        VARCHAR(20) NOT NULL,
    drug_name               VARCHAR(255) NOT NULL,
    dose                    NUMERIC(12,3) NOT NULL,
    dose_unit               VARCHAR(20) NOT NULL,
    route                   VARCHAR(30) NOT NULL,
    frequency               VARCHAR(30) NOT NULL,
    duration_days           INT,
    prn_flag                BOOLEAN NOT NULL DEFAULT FALSE,
    prn_indication          VARCHAR(255),
    indication_icd10        VARCHAR(10),
    indication_text         VARCHAR(500),
    weight_kg_at_order      NUMERIC(6,2),
    bsa_m2_at_order         NUMERIC(5,3),
    renal_function_json     JSONB,
    order_datetime          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    verified_by             BIGINT,
    verified_datetime       TIMESTAMP WITH TIME ZONE,
    verification_outcome    VARCHAR(20),
    verification_notes      TEXT,
    formulary_item_id       BIGINT,
    non_formulary_reason    VARCHAR(255),
    insurance_payer_id      BIGINT,
    prior_auth_required     BOOLEAN NOT NULL DEFAULT FALSE,
    prior_auth_status       VARCHAR(20),
    is_controlled           BOOLEAN NOT NULL DEFAULT FALSE,
    controlled_schedule     VARCHAR(20),
    uep_required            BOOLEAN NOT NULL DEFAULT FALSE,
    uep_prescription_id     VARCHAR(50),
    max_daily_dose          NUMERIC(12,3),
    cds_alerts_json         JSONB,
    override_flags_json     JSONB,
    created_by              BIGINT NOT NULL,
    updated_by              BIGINT,
    created_at              TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE pharmacy_orders
    ADD CONSTRAINT fk_pharm_orders_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_pharm_orders_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_pharm_orders_provider
        FOREIGN KEY (ordering_provider_id) REFERENCES providers(provider_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_pharm_orders_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_pharm_orders_department
        FOREIGN KEY (department_id) REFERENCES departments(department_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_pharm_orders_formulary_item
        FOREIGN KEY (formulary_item_id) REFERENCES formulary_items(item_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_pharm_orders_payer
        FOREIGN KEY (insurance_payer_id) REFERENCES payers(payer_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_pharm_orders_verified_by
        FOREIGN KEY (verified_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_pharm_orders_created_by
        FOREIGN KEY (created_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_pharm_orders_updated_by
        FOREIGN KEY (updated_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT chk_pharm_orders_order_type
        CHECK (order_type IN ('inpatient','outpatient','discharge')),
    ADD CONSTRAINT chk_pharm_orders_status
        CHECK (order_status IN ('pending_verification','verified','rejected','on_hold','discontinued','completed','cancelled')),
    ADD CONSTRAINT chk_pharm_orders_priority
        CHECK (priority IN ('stat','urgent','first_dose','routine')),
    ADD CONSTRAINT chk_pharm_orders_dose_positive
        CHECK (dose > 0),
    ADD CONSTRAINT chk_pharm_orders_duration_positive
        CHECK (duration_days IS NULL OR duration_days > 0),
    ADD CONSTRAINT chk_pharm_orders_prn_indication
        CHECK (prn_flag = FALSE OR prn_indication IS NOT NULL),
    ADD CONSTRAINT chk_pharm_orders_verification_outcome
        CHECK (verification_outcome IS NULL OR verification_outcome IN ('approved','modified','rejected')),
    ADD CONSTRAINT chk_pharm_orders_prior_auth_status
        CHECK (prior_auth_status IS NULL OR prior_auth_status IN ('not_required','pending','approved','denied')),
    ADD CONSTRAINT chk_pharm_orders_controlled_schedule
        CHECK (is_controlled = FALSE OR controlled_schedule IS NOT NULL);

CREATE INDEX idx_pharm_orders_patient ON pharmacy_orders(patient_id);
CREATE INDEX idx_pharm_orders_encounter ON pharmacy_orders(encounter_id);
CREATE INDEX idx_pharm_orders_status ON pharmacy_orders(order_status);
CREATE INDEX idx_pharm_orders_priority ON pharmacy_orders(priority);
CREATE INDEX idx_pharm_orders_verified_dt ON pharmacy_orders(verified_datetime);
CREATE INDEX idx_pharm_orders_drug ON pharmacy_orders(drug_code_rxnorm);
CREATE INDEX idx_pharm_orders_controlled ON pharmacy_orders(is_controlled) WHERE is_controlled = TRUE;
CREATE INDEX idx_pharm_orders_facility_status ON pharmacy_orders(facility_id, order_status);

CREATE TABLE pharmacy_dispensing (
    dispensing_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id                BIGINT NOT NULL,
    patient_id              BIGINT NOT NULL,
    encounter_id            BIGINT,
    facility_id             BIGINT NOT NULL,
    dispensing_location_id  BIGINT,
    drug_code_rxnorm        VARCHAR(20) NOT NULL,
    ndc_code                VARCHAR(20),
    quantity_dispensed      NUMERIC(12,3) NOT NULL,
    quantity_unit           VARCHAR(20) NOT NULL DEFAULT 'unit',
    days_supply             INT,
    lot_number              VARCHAR(50),
    expiry_date             DATE,
    dispensed_by            BIGINT NOT NULL,
    verified_by             BIGINT,
    dispensing_datetime     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    label_printed           BOOLEAN NOT NULL DEFAULT FALSE,
    label_language          VARCHAR(10),
    billing_status          VARCHAR(20) NOT NULL DEFAULT 'pending',
    billing_reference       VARCHAR(50),
    is_controlled           BOOLEAN NOT NULL DEFAULT FALSE,
    controlled_log_id       BIGINT,
    hie_submission_status   VARCHAR(20) NOT NULL DEFAULT 'pending',
    hie_last_attempt        TIMESTAMP WITH TIME ZONE,
    hie_error_message       TEXT,
    created_at              TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at              TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE pharmacy_dispensing
    ADD CONSTRAINT fk_disp_order
        FOREIGN KEY (order_id) REFERENCES pharmacy_orders(order_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_disp_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_disp_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_disp_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_disp_location
        FOREIGN KEY (dispensing_location_id) REFERENCES locations(location_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_disp_dispensed_by
        FOREIGN KEY (dispensed_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_disp_verified_by
        FOREIGN KEY (verified_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_disp_controlled_log
        FOREIGN KEY (controlled_log_id) REFERENCES controlled_substance_log(log_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT chk_disp_quantity_positive
        CHECK (quantity_dispensed > 0),
    ADD CONSTRAINT chk_disp_days_supply
        CHECK (days_supply IS NULL OR days_supply > 0),
    ADD CONSTRAINT chk_disp_billing_status
        CHECK (billing_status IN ('pending','sent','accepted','rejected','not_billable')),
    ADD CONSTRAINT chk_disp_hie_status
        CHECK (hie_submission_status IN ('pending','accepted','rejected','error','not_required')),
    ADD CONSTRAINT chk_disp_label_language
        CHECK (label_language IS NULL OR label_language IN ('en','ar','bilingual'));

CREATE INDEX idx_disp_order ON pharmacy_dispensing(order_id);
CREATE INDEX idx_disp_patient ON pharmacy_dispensing(patient_id);
CREATE INDEX idx_disp_datetime ON pharmacy_dispensing(dispensing_datetime);
CREATE INDEX idx_disp_billing_status ON pharmacy_dispensing(billing_status);
CREATE INDEX idx_disp_hie_status ON pharmacy_dispensing(hie_submission_status);
CREATE INDEX idx_disp_facility_datetime ON pharmacy_dispensing(facility_id, dispensing_datetime);

CREATE TABLE medication_administration (
    admin_id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id            BIGINT NOT NULL,
    dispensing_id       BIGINT,
    patient_id          BIGINT NOT NULL,
    encounter_id        BIGINT NOT NULL,
    facility_id         BIGINT NOT NULL,
    location_id         BIGINT,
    drug_code_rxnorm    VARCHAR(20) NOT NULL,
    scheduled_datetime  TIMESTAMP WITH TIME ZONE NOT NULL,
    actual_datetime     TIMESTAMP WITH TIME ZONE,
    dose_given          NUMERIC(12,3),
    dose_unit           VARCHAR(20),
    route               VARCHAR(30),
    site                VARCHAR(50),
    status              VARCHAR(20) NOT NULL DEFAULT 'scheduled',
    hold_reason         VARCHAR(255),
    refuse_reason       VARCHAR(255),
    omission_reason     VARCHAR(255),
    administered_by     BIGINT,
    witness_id          BIGINT,
    barcode_verified    BOOLEAN NOT NULL DEFAULT FALSE,
    patient_barcode     VARCHAR(50),
    medication_barcode  VARCHAR(50),
    prn_indication      VARCHAR(255),
    prn_response        TEXT,
    notes               TEXT,
    created_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE medication_administration
    ADD CONSTRAINT fk_medadmin_order
        FOREIGN KEY (order_id) REFERENCES pharmacy_orders(order_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_medadmin_dispensing
        FOREIGN KEY (dispensing_id) REFERENCES pharmacy_dispensing(dispensing_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_medadmin_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_medadmin_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_medadmin_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_medadmin_location
        FOREIGN KEY (location_id) REFERENCES locations(location_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_medadmin_admin_by
        FOREIGN KEY (administered_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_medadmin_witness
        FOREIGN KEY (witness_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT chk_medadmin_status
        CHECK (status IN ('scheduled','in_progress','completed','held','refused','omitted','cancelled')),
    ADD CONSTRAINT chk_medadmin_dose_nonnegative
        CHECK (dose_given IS NULL OR dose_given >= 0),
    ADD CONSTRAINT chk_medadmin_hold_reason
        CHECK (status <> 'held' OR hold_reason IS NOT NULL),
    ADD CONSTRAINT chk_medadmin_refuse_reason
        CHECK (status <> 'refused' OR refuse_reason IS NOT NULL),
    ADD CONSTRAINT chk_medadmin_omission_reason
        CHECK (status <> 'omitted' OR omission_reason IS NOT NULL);

CREATE INDEX idx_medadmin_patient ON medication_administration(patient_id);
CREATE INDEX idx_medadmin_encounter ON medication_administration(encounter_id);
CREATE INDEX idx_medadmin_order ON medication_administration(order_id);
CREATE INDEX idx_medadmin_status ON medication_administration(status);
CREATE INDEX idx_medadmin_sched_dt ON medication_administration(scheduled_datetime);
CREATE INDEX idx_medadmin_barcode_verified ON medication_administration(barcode_verified);

CREATE TABLE formulary (
    formulary_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    formulary_name  VARCHAR(100) NOT NULL,
    facility_id     BIGINT NOT NULL,
    effective_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    end_date        DATE,
    approved_by     BIGINT NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'active',
    notes           TEXT,
    created_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_formulary_name_facility UNIQUE (formulary_name, facility_id)
);

ALTER TABLE formulary
    ADD CONSTRAINT fk_formulary_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_formulary_approved_by
        FOREIGN KEY (approved_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT chk_formulary_status
        CHECK (status IN ('draft','active','retired')),
    ADD CONSTRAINT chk_formulary_dates
        CHECK (end_date IS NULL OR end_date > effective_date);

CREATE INDEX idx_formulary_facility_status ON formulary(facility_id, status);

CREATE TABLE formulary_items (
    item_id                       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    formulary_id                  BIGINT NOT NULL,
    drug_code_rxnorm              VARCHAR(20) NOT NULL,
    drug_name                     VARCHAR(255) NOT NULL,
    generic_name                  VARCHAR(255),
    strength                      VARCHAR(50),
    dosage_form                   VARCHAR(50),
    formulary_status              VARCHAR(20) NOT NULL DEFAULT 'on_formulary',
    restriction_type              VARCHAR(50),
    therapeutic_class             VARCHAR(50),
    therapeutic_alternative_rxnorm VARCHAR(20),
    requires_prior_auth           BOOLEAN NOT NULL DEFAULT FALSE,
    is_high_alert                 BOOLEAN NOT NULL DEFAULT FALSE,
    is_controlled                 BOOLEAN NOT NULL DEFAULT FALSE,
    controlled_schedule           VARCHAR(20),
    default_route                 VARCHAR(30),
    default_frequency             VARCHAR(30),
    default_dose                  NUMERIC(12,3),
    default_dose_unit             VARCHAR(20),
    active_flag                   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at                    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at                    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE formulary_items
    ADD CONSTRAINT fk_formulary_items_formulary
        FOREIGN KEY (formulary_id) REFERENCES formulary(formulary_id) ON UPDATE CASCADE ON DELETE CASCADE,
    ADD CONSTRAINT chk_formulary_items_status
        CHECK (formulary_status IN ('on_formulary','non_formulary','restricted','obsolete')),
    ADD CONSTRAINT chk_formulary_items_controlled_schedule
        CHECK (is_controlled = FALSE OR controlled_schedule IS NOT NULL);

CREATE INDEX idx_formulary_items_formulary ON formulary_items(formulary_id);
CREATE INDEX idx_formulary_items_drug ON formulary_items(drug_code_rxnorm);
CREATE INDEX idx_formulary_items_status ON formulary_items(formulary_status) WHERE active_flag = TRUE;

CREATE TABLE drug_interactions (
    interaction_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    drug_a_rxnorm         VARCHAR(20) NOT NULL,
    drug_b_rxnorm         VARCHAR(20) NOT NULL,
    severity              VARCHAR(20) NOT NULL,
    description           TEXT NOT NULL,
    clinical_significance TEXT,
    recommendation        TEXT,
    evidence_level        VARCHAR(20),
    source                VARCHAR(100),
    active_flag           BOOLEAN NOT NULL DEFAULT TRUE,
    created_at            TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_drug_interactions_pair UNIQUE (drug_a_rxnorm, drug_b_rxnorm)
);

ALTER TABLE drug_interactions
    ADD CONSTRAINT chk_drug_interactions_severity
        CHECK (severity IN ('contraindicated','major','moderate','minor'));

CREATE INDEX idx_drug_interactions_drug_a ON drug_interactions(drug_a_rxnorm) WHERE active_flag = TRUE;
CREATE INDEX idx_drug_interactions_drug_b ON drug_interactions(drug_b_rxnorm) WHERE active_flag = TRUE;

CREATE TABLE pharmacy_inventory (
    inventory_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    drug_code_rxnorm    VARCHAR(20) NOT NULL,
    ndc_code            VARCHAR(20),
    facility_id         BIGINT NOT NULL,
    location_id         BIGINT NOT NULL,
    lot_number          VARCHAR(50),
    expiry_date         DATE,
    quantity_on_hand    NUMERIC(14,3) NOT NULL DEFAULT 0,
    reorder_point       NUMERIC(14,3),
    par_level           NUMERIC(14,3),
    last_count_date     DATE,
    unit_cost           NUMERIC(12,4),
    is_controlled       BOOLEAN NOT NULL DEFAULT FALSE,
    controlled_schedule VARCHAR(20),
    active_flag         BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE pharmacy_inventory
    ADD CONSTRAINT fk_inventory_facility
        FOREIGN KEY (facility_id) REFERENCES facilities(facility_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_inventory_location
        FOREIGN KEY (location_id) REFERENCES locations(location_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT chk_inventory_qty_nonnegative
        CHECK (quantity_on_hand >= 0),
    ADD CONSTRAINT chk_inventory_reorder_nonnegative
        CHECK (reorder_point IS NULL OR reorder_point >= 0),
    ADD CONSTRAINT chk_inventory_par_nonnegative
        CHECK (par_level IS NULL OR par_level >= 0),
    ADD CONSTRAINT chk_inventory_unit_cost_nonnegative
        CHECK (unit_cost IS NULL OR unit_cost >= 0),
    ADD CONSTRAINT chk_inventory_controlled_schedule
        CHECK (is_controlled = FALSE OR controlled_schedule IS NOT NULL);

CREATE INDEX idx_inventory_drug_loc ON pharmacy_inventory(drug_code_rxnorm, facility_id, location_id);
CREATE INDEX idx_inventory_expiry ON pharmacy_inventory(expiry_date) WHERE active_flag = TRUE;
CREATE INDEX idx_inventory_controlled ON pharmacy_inventory(is_controlled) WHERE is_controlled = TRUE;

CREATE TABLE inventory_transactions (
    transaction_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    inventory_id          BIGINT NOT NULL,
    transaction_type      VARCHAR(20) NOT NULL,
    quantity              NUMERIC(14,3) NOT NULL,
    from_location_id      BIGINT,
    to_location_id        BIGINT,
    reference_type        VARCHAR(30),
    reference_id          BIGINT,
    performed_by          BIGINT NOT NULL,
    transaction_datetime  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reason                VARCHAR(255),
    is_controlled         BOOLEAN NOT NULL DEFAULT FALSE,
    created_at            TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE inventory_transactions
    ADD CONSTRAINT fk_inv_tx_inventory
        FOREIGN KEY (inventory_id) REFERENCES pharmacy_inventory(inventory_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_inv_tx_from_location
        FOREIGN KEY (from_location_id) REFERENCES locations(location_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_inv_tx_to_location
        FOREIGN KEY (to_location_id) REFERENCES locations(location_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_inv_tx_performed_by
        FOREIGN KEY (performed_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT chk_inv_tx_type
        CHECK (transaction_type IN ('receipt','issue','return','adjustment','transfer_out','transfer_in','waste')),
    ADD CONSTRAINT chk_inv_tx_quantity_nonzero
        CHECK (quantity <> 0);

CREATE INDEX idx_inv_tx_inventory ON inventory_transactions(inventory_id);
CREATE INDEX idx_inv_tx_datetime ON inventory_transactions(transaction_datetime);
CREATE INDEX idx_inv_tx_type ON inventory_transactions(transaction_type);
CREATE INDEX idx_inv_tx_controlled ON inventory_transactions(is_controlled) WHERE is_controlled = TRUE;

CREATE TABLE controlled_substance_log (
    log_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    inventory_id         BIGINT,
    drug_code_rxnorm     VARCHAR(20) NOT NULL,
    controlled_schedule  VARCHAR(20) NOT NULL,
    transaction_type     VARCHAR(20) NOT NULL,
    quantity             NUMERIC(14,3) NOT NULL,
    balance_after        NUMERIC(14,3) NOT NULL,
    from_user_id         BIGINT,
    to_user_id           BIGINT,
    witness_id           BIGINT,
    patient_id           BIGINT,
    encounter_id         BIGINT,
    waste_amount         NUMERIC(14,3),
    waste_witnessed_by   BIGINT,
    location_id          BIGINT NOT NULL,
    transaction_datetime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reason               VARCHAR(255),
    reported_to_moh      BOOLEAN NOT NULL DEFAULT FALSE,
    moh_report_reference VARCHAR(50),
    created_at           TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE controlled_substance_log
    ADD CONSTRAINT fk_cslog_inventory
        FOREIGN KEY (inventory_id) REFERENCES pharmacy_inventory(inventory_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_cslog_from_user
        FOREIGN KEY (from_user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_cslog_to_user
        FOREIGN KEY (to_user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_cslog_witness
        FOREIGN KEY (witness_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_cslog_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_cslog_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_cslog_waste_witness
        FOREIGN KEY (waste_witnessed_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_cslog_location
        FOREIGN KEY (location_id) REFERENCES locations(location_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT chk_cslog_type
        CHECK (transaction_type IN ('receipt','dispense_to_unit','return_to_pharmacy','administer_to_patient','waste','adjustment','loss_theft')),
    ADD CONSTRAINT chk_cslog_quantity_positive
        CHECK (quantity > 0),
    ADD CONSTRAINT chk_cslog_balance_nonnegative
        CHECK (balance_after >= 0),
    ADD CONSTRAINT chk_cslog_waste_amount
        CHECK (waste_amount IS NULL OR waste_amount >= 0);

CREATE INDEX idx_cslog_drug_loc ON controlled_substance_log(drug_code_rxnorm, location_id);
CREATE INDEX idx_cslog_datetime ON controlled_substance_log(transaction_datetime);
CREATE INDEX idx_cslog_patient ON controlled_substance_log(patient_id);
CREATE INDEX idx_cslog_type ON controlled_substance_log(transaction_type);

CREATE TABLE iv_admixture_orders (
    admixture_id                 BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id                     BIGINT NOT NULL,
    patient_id                   BIGINT NOT NULL,
    encounter_id                 BIGINT NOT NULL,
    base_solution                VARCHAR(100) NOT NULL,
    base_solution_rxnorm        VARCHAR(20),
    additive_drug               VARCHAR(255) NOT NULL,
    additive_drug_rxnorm        VARCHAR(20),
    additive_dose               NUMERIC(12,3) NOT NULL,
    additive_dose_unit          VARCHAR(20) NOT NULL,
    concentration               VARCHAR(50),
    total_volume                NUMERIC(12,3) NOT NULL,
    infusion_rate               NUMERIC(12,3),
    beyond_use_datetime         TIMESTAMP WITH TIME ZONE NOT NULL,
    prepared_by                 BIGINT NOT NULL,
    verified_by                 BIGINT NOT NULL,
    preparation_datetime        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status                      VARCHAR(20) NOT NULL DEFAULT 'prepared',
    hood_location_id            BIGINT,
    gravimetric_verification_json JSONB,
    label_printed               BOOLEAN NOT NULL DEFAULT FALSE,
    created_at                  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE iv_admixture_orders
    ADD CONSTRAINT fk_iv_order
        FOREIGN KEY (order_id) REFERENCES pharmacy_orders(order_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_iv_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_iv_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_iv_prepared_by
        FOREIGN KEY (prepared_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_iv_verified_by
        FOREIGN KEY (verified_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_iv_hood_location
        FOREIGN KEY (hood_location_id) REFERENCES locations(location_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT chk_iv_status
        CHECK (status IN ('queued','in_progress','prepared','released','discarded')),
    ADD CONSTRAINT chk_iv_additive_dose_positive
        CHECK (additive_dose > 0),
    ADD CONSTRAINT chk_iv_total_volume_positive
        CHECK (total_volume > 0),
    ADD CONSTRAINT chk_iv_infusion_rate_nonnegative
        CHECK (infusion_rate IS NULL OR infusion_rate >= 0),
    ADD CONSTRAINT chk_iv_bud_after_prep
        CHECK (beyond_use_datetime > preparation_datetime);

CREATE INDEX idx_iv_order ON iv_admixture_orders(order_id);
CREATE INDEX idx_iv_patient ON iv_admixture_orders(patient_id);
CREATE INDEX idx_iv_status ON iv_admixture_orders(status);
CREATE INDEX idx_iv_bud ON iv_admixture_orders(beyond_use_datetime);

CREATE TABLE pharmacy_interventions (
    intervention_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id               BIGINT NOT NULL,
    patient_id             BIGINT NOT NULL,
    encounter_id           BIGINT,
    pharmacist_id          BIGINT NOT NULL,
    intervention_type      VARCHAR(50) NOT NULL,
    description            TEXT NOT NULL,
    recommendation         TEXT,
    prescriber_contacted   BOOLEAN NOT NULL DEFAULT FALSE,
    prescriber_response    VARCHAR(50),
    outcome                VARCHAR(50),
    intervention_datetime  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    time_spent_minutes     INT,
    cds_triggered          BOOLEAN NOT NULL DEFAULT FALSE,
    created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE pharmacy_interventions
    ADD CONSTRAINT fk_intervention_order
        FOREIGN KEY (order_id) REFERENCES pharmacy_orders(order_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_intervention_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_intervention_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_intervention_pharmacist
        FOREIGN KEY (pharmacist_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT chk_intervention_time_spent
        CHECK (time_spent_minutes IS NULL OR time_spent_minutes >= 0);

CREATE INDEX idx_intervention_order ON pharmacy_interventions(order_id);
CREATE INDEX idx_intervention_patient ON pharmacy_interventions(patient_id);
CREATE INDEX idx_intervention_datetime ON pharmacy_interventions(intervention_datetime);
CREATE INDEX idx_intervention_type ON pharmacy_interventions(intervention_type);

CREATE TABLE medication_reconciliation (
    recon_id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id             BIGINT NOT NULL,
    encounter_id           BIGINT NOT NULL,
    reconciliation_type    VARCHAR(20) NOT NULL,
    home_medication_rxnorm VARCHAR(20),
    home_medication_name   VARCHAR(255),
    home_dose              VARCHAR(50),
    home_route             VARCHAR(30),
    home_frequency         VARCHAR(30),
    source_of_information  VARCHAR(50),
    action_taken           VARCHAR(20),
    new_order_id           BIGINT,
    discrepancy_type       VARCHAR(50),
    reconciled_by          BIGINT NOT NULL,
    reconciled_datetime    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    notes                  TEXT,
    created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE medication_reconciliation
    ADD CONSTRAINT fk_recon_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_recon_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_recon_new_order
        FOREIGN KEY (new_order_id) REFERENCES pharmacy_orders(order_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_recon_user
        FOREIGN KEY (reconciled_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT chk_recon_type
        CHECK (reconciliation_type IN ('admission','transfer','discharge')),
    ADD CONSTRAINT chk_recon_action
        CHECK (action_taken IS NULL OR action_taken IN ('continue','hold','modify','discontinue','new'));

CREATE INDEX idx_recon_patient ON medication_reconciliation(patient_id);
CREATE INDEX idx_recon_encounter ON medication_reconciliation(encounter_id);
CREATE INDEX idx_recon_type ON medication_reconciliation(reconciliation_type);
CREATE INDEX idx_recon_datetime ON medication_reconciliation(reconciled_datetime);

CREATE TABLE antimicrobial_stewardship_reviews (
    review_id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id            BIGINT NOT NULL,
    patient_id          BIGINT NOT NULL,
    encounter_id        BIGINT,
    antibiotic_rxnorm   VARCHAR(20) NOT NULL,
    review_type         VARCHAR(20) NOT NULL,
    indication          VARCHAR(255),
    culture_result_id   BIGINT,
    recommendation      TEXT,
    accepted            BOOLEAN,
    reviewed_by         BIGINT NOT NULL,
    reviewed_datetime   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    prescriber_response VARCHAR(255),
    ddd_value           NUMERIC(10,3),
    created_at          TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE antimicrobial_stewardship_reviews
    ADD CONSTRAINT fk_steward_order
        FOREIGN KEY (order_id) REFERENCES pharmacy_orders(order_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_steward_patient
        FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT fk_steward_encounter
        FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id) ON UPDATE CASCADE ON DELETE SET NULL,
    ADD CONSTRAINT fk_steward_reviewer
        FOREIGN KEY (reviewed_by) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    ADD CONSTRAINT chk_steward_review_type
        CHECK (review_type IN ('prospective','retrospective','automatic')),
    ADD CONSTRAINT chk_steward_ddd_nonnegative
        CHECK (ddd_value IS NULL OR ddd_value >= 0);

CREATE INDEX idx_steward_order ON antimicrobial_stewardship_reviews(order_id);
CREATE INDEX idx_steward_patient ON antimicrobial_stewardship_reviews(patient_id);
CREATE INDEX idx_steward_datetime ON antimicrobial_stewardship_reviews(reviewed_datetime);
CREATE INDEX idx_steward_review_type ON antimicrobial_stewardship_reviews(review_type);