CPOE Data Specifications

CREATE TABLE medication_orders (
  order_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  patient_id BIGINT NOT NULL,
  encounter_id BIGINT NOT NULL,
  ordering_provider_id BIGINT NOT NULL,
  medication_id BIGINT NOT NULL,
  order_type VARCHAR(20) NOT NULL DEFAULT 'inpatient',
  order_status VARCHAR(20) NOT NULL DEFAULT 'draft',
  order_source VARCHAR(20) NOT NULL DEFAULT 'direct',
  dose DECIMAL(10,3) NOT NULL,
  dose_unit VARCHAR(20) NOT NULL,
  route VARCHAR(50) NOT NULL,
  frequency VARCHAR(50) NOT NULL,
  duration_value INT,
  duration_unit VARCHAR(20),
  prn_flag BOOLEAN NOT NULL DEFAULT FALSE,
  prn_indication VARCHAR(200),
  sig TEXT NOT NULL,
  indication_code VARCHAR(10),
  indication_text VARCHAR(500),
  priority VARCHAR(20) NOT NULL DEFAULT 'routine',
  start_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  stop_datetime TIMESTAMP,
  order_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  signed_datetime TIMESTAMP,
  signed_by BIGINT,
  cosign_required BOOLEAN NOT NULL DEFAULT FALSE,
  cosign_by BIGINT,
  cosign_datetime TIMESTAMP,
  discontinue_datetime TIMESTAMP,
  discontinue_reason TEXT,
  cancel_reason TEXT,
  entered_by BIGINT,
  is_controlled_substance BOOLEAN NOT NULL DEFAULT FALSE,
  controlled_class VARCHAR(20),
  uae_legal_schedule VARCHAR(10),
  uep_required BOOLEAN NOT NULL DEFAULT FALSE,
  uep_prescription_id VARCHAR(50),
  uep_submission_status VARCHAR(20),
  uep_submission_datetime TIMESTAMP,
  special_prescription_number VARCHAR(30),
  prescriber_controlled_auth BOOLEAN,
  dual_verification_by BIGINT,
  dual_verification_datetime TIMESTAMP,
  override_flags JSONB,
  formulary_status VARCHAR(20),
  estimated_cost_aed DECIMAL(10,2),
  alerts_overridden INT NOT NULL DEFAULT 0,
  order_set_execution_id BIGINT,
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by BIGINT,
  updated_at TIMESTAMP,

  CONSTRAINT fk_mo_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT fk_mo_encounter FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
  CONSTRAINT fk_mo_provider FOREIGN KEY (ordering_provider_id) REFERENCES providers(provider_id),
  CONSTRAINT fk_mo_medication FOREIGN KEY (medication_id) REFERENCES medication_master(medication_id),
  CONSTRAINT fk_mo_signed_by FOREIGN KEY (signed_by) REFERENCES users(user_id),
  CONSTRAINT fk_mo_cosign_by FOREIGN KEY (cosign_by) REFERENCES users(user_id),
  CONSTRAINT fk_mo_entered_by FOREIGN KEY (entered_by) REFERENCES users(user_id),
  CONSTRAINT fk_mo_dual_verif FOREIGN KEY (dual_verification_by) REFERENCES users(user_id),
  CONSTRAINT fk_mo_created_by FOREIGN KEY (created_by) REFERENCES users(user_id),
  CONSTRAINT fk_mo_order_set FOREIGN KEY (order_set_execution_id) REFERENCES order_set_executions(execution_id),
  CONSTRAINT chk_order_type CHECK (order_type IN ('inpatient', 'outpatient', 'discharge')),
  CONSTRAINT chk_order_status CHECK (order_status IN ('draft', 'pending', 'active', 'completed', 'discontinued', 'cancelled')),
  CONSTRAINT chk_order_source CHECK (order_source IN ('direct', 'verbal', 'telephone', 'order_set')),
  CONSTRAINT chk_priority CHECK (priority IN ('stat', 'asap', 'today', 'routine')),
  CONSTRAINT chk_stop_after_start CHECK (stop_datetime IS NULL OR stop_datetime > start_datetime),
  CONSTRAINT chk_prn_indication CHECK (prn_flag = FALSE OR prn_indication IS NOT NULL),
  CONSTRAINT chk_controlled_class CHECK (
    is_controlled_substance = FALSE
    OR controlled_class IN ('CDa-Narcotic', 'CDa-Psychotropic', 'CDb')
  ),
  CONSTRAINT chk_uae_schedule CHECK (
    is_controlled_substance = FALSE
    OR uae_legal_schedule IN (
      'Schedule 1', 'Schedule 2', 'Schedule 3',
      'Schedule 4', 'Schedule 5', 'Schedule 6',
      'Schedule 7', 'Schedule 8', 'Schedule 9'
    )
  ),
  CONSTRAINT chk_uep_required CHECK (
    is_controlled_substance = FALSE OR uep_required = TRUE
  ),
  CONSTRAINT chk_self_prescribe CHECK (
    is_controlled_substance = FALSE
    OR dual_verification_by IS NULL
    OR ordering_provider_id != dual_verification_by
  ),
  CONSTRAINT chk_discontinue_reason CHECK (
    order_status != 'discontinued' OR discontinue_reason IS NOT NULL
  ),
  CONSTRAINT chk_cancel_reason CHECK (
    order_status != 'cancelled' OR cancel_reason IS NOT NULL
  )
);

CREATE INDEX idx_mo_patient ON medication_orders(patient_id);
CREATE INDEX idx_mo_provider ON medication_orders(ordering_provider_id);
CREATE INDEX idx_mo_encounter ON medication_orders(encounter_id);
CREATE INDEX idx_mo_status ON medication_orders(order_status);
CREATE INDEX idx_mo_datetime ON medication_orders(order_datetime);
CREATE INDEX idx_mo_active ON medication_orders(patient_id, order_status)
  WHERE order_status = 'active';
CREATE INDEX idx_mo_controlled ON medication_orders(is_controlled_substance, uep_submission_status)
  WHERE is_controlled_substance = TRUE;

CREATE TABLE laboratory_orders (
  order_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  patient_id BIGINT NOT NULL,
  encounter_id BIGINT NOT NULL,
  ordering_provider_id BIGINT NOT NULL,
  loinc_code VARCHAR(20) NOT NULL,
  test_name VARCHAR(200) NOT NULL,
  cpt_code VARCHAR(10),
  specimen_type VARCHAR(50) NOT NULL,
  order_status VARCHAR(20) NOT NULL DEFAULT 'pending',
  order_source VARCHAR(20) NOT NULL DEFAULT 'direct',
  priority VARCHAR(20) NOT NULL DEFAULT 'routine',
  collection_datetime TIMESTAMP,
  collected_datetime TIMESTAMP,
  fasting_required BOOLEAN NOT NULL DEFAULT FALSE,
  clinical_indication VARCHAR(10),
  clinical_indication_text VARCHAR(500),
  special_instructions TEXT,
  is_panel BOOLEAN NOT NULL DEFAULT FALSE,
  panel_name VARCHAR(100),
  order_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  signed_datetime TIMESTAMP,
  signed_by BIGINT,
  result_datetime TIMESTAMP,
  order_set_execution_id BIGINT,
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by BIGINT,
  updated_at TIMESTAMP,

  CONSTRAINT fk_lo_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT fk_lo_encounter FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
  CONSTRAINT fk_lo_provider FOREIGN KEY (ordering_provider_id) REFERENCES providers(provider_id),
  CONSTRAINT fk_lo_signed_by FOREIGN KEY (signed_by) REFERENCES users(user_id),
  CONSTRAINT fk_lo_created_by FOREIGN KEY (created_by) REFERENCES users(user_id),
  CONSTRAINT fk_lo_order_set FOREIGN KEY (order_set_execution_id) REFERENCES order_set_executions(execution_id),
  CONSTRAINT chk_lo_status CHECK (order_status IN ('pending', 'collected', 'in-process', 'completed', 'cancelled')),
  CONSTRAINT chk_lo_source CHECK (order_source IN ('direct', 'verbal', 'telephone', 'order_set')),
  CONSTRAINT chk_lo_priority CHECK (priority IN ('stat', 'today', 'routine'))
);

CREATE INDEX idx_lo_patient ON laboratory_orders(patient_id);
CREATE INDEX idx_lo_encounter ON laboratory_orders(encounter_id);
CREATE INDEX idx_lo_status ON laboratory_orders(order_status);
CREATE INDEX idx_lo_datetime ON laboratory_orders(order_datetime);
CREATE INDEX idx_lo_loinc ON laboratory_orders(loinc_code);

CREATE TABLE imaging_orders (
  order_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  patient_id BIGINT NOT NULL,
  encounter_id BIGINT NOT NULL,
  ordering_provider_id BIGINT NOT NULL,
  cpt_code VARCHAR(10) NOT NULL,
  snomed_code VARCHAR(20),
  exam_name VARCHAR(200) NOT NULL,
  modality VARCHAR(20) NOT NULL,
  body_part VARCHAR(100) NOT NULL,
  laterality VARCHAR(10),
  contrast_required BOOLEAN NOT NULL DEFAULT FALSE,
  contrast_type VARCHAR(50),
  exam_protocol VARCHAR(200),
  order_status VARCHAR(20) NOT NULL DEFAULT 'pending',
  order_source VARCHAR(20) NOT NULL DEFAULT 'direct',
  priority VARCHAR(20) NOT NULL DEFAULT 'routine',
  clinical_indication VARCHAR(10) NOT NULL,
  clinical_question TEXT NOT NULL,
  pregnancy_status VARCHAR(20),
  portable_flag BOOLEAN NOT NULL DEFAULT FALSE,
  scheduled_datetime TIMESTAMP,
  performed_datetime TIMESTAMP,
  appropriateness_score VARCHAR(30),
  cumulative_dose_msv DECIMAL(8,2),
  order_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  signed_datetime TIMESTAMP,
  signed_by BIGINT,
  report_datetime TIMESTAMP,
  order_set_execution_id BIGINT,
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by BIGINT,
  updated_at TIMESTAMP,

  CONSTRAINT fk_io_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT fk_io_encounter FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
  CONSTRAINT fk_io_provider FOREIGN KEY (ordering_provider_id) REFERENCES providers(provider_id),
  CONSTRAINT fk_io_signed_by FOREIGN KEY (signed_by) REFERENCES users(user_id),
  CONSTRAINT fk_io_created_by FOREIGN KEY (created_by) REFERENCES users(user_id),
  CONSTRAINT fk_io_order_set FOREIGN KEY (order_set_execution_id) REFERENCES order_set_executions(execution_id),
  CONSTRAINT chk_io_modality CHECK (modality IN ('XR', 'CT', 'MRI', 'US', 'NM', 'IR', 'PET', 'FLUORO')),
  CONSTRAINT chk_io_status CHECK (order_status IN ('pending', 'scheduled', 'in-progress', 'completed', 'cancelled')),
  CONSTRAINT chk_io_source CHECK (order_source IN ('direct', 'verbal', 'telephone', 'order_set')),
  CONSTRAINT chk_io_priority CHECK (priority IN ('stat', 'urgent', 'routine')),
  CONSTRAINT chk_io_laterality CHECK (laterality IS NULL OR laterality IN ('left', 'right', 'bilateral')),
  CONSTRAINT chk_io_pregnancy CHECK (pregnancy_status IS NULL OR pregnancy_status IN ('not-pregnant', 'pregnant', 'unknown', 'not-applicable'))
);

CREATE INDEX idx_io_patient ON imaging_orders(patient_id);
CREATE INDEX idx_io_encounter ON imaging_orders(encounter_id);
CREATE INDEX idx_io_status ON imaging_orders(order_status);
CREATE INDEX idx_io_modality ON imaging_orders(modality);
CREATE INDEX idx_io_datetime ON imaging_orders(order_datetime);

CREATE TABLE medication_master (
  medication_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  generic_name VARCHAR(200) NOT NULL,
  brand_name VARCHAR(200),
  rxnorm_code VARCHAR(20) NOT NULL UNIQUE,
  atc_code VARCHAR(10),
  drug_class VARCHAR(100) NOT NULL,
  available_strengths JSONB,
  available_forms JSONB,
  is_formulary BOOLEAN NOT NULL DEFAULT TRUE,
  formulary_tier VARCHAR(20),
  is_controlled_substance BOOLEAN NOT NULL DEFAULT FALSE,
  controlled_class VARCHAR(20),
  uae_legal_schedule VARCHAR(10),
  uep_required BOOLEAN NOT NULL DEFAULT FALSE,
  ede_registered BOOLEAN NOT NULL DEFAULT FALSE,
  ede_registration_number VARCHAR(50),
  formulary_alternatives JSONB,
  max_dose_per_day DECIMAL(10,3),
  max_dose_unit VARCHAR(20),
  renal_adjustment_required BOOLEAN NOT NULL DEFAULT FALSE,
  pediatric_dosing_available BOOLEAN NOT NULL DEFAULT FALSE,
  active_flag BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP,

  CONSTRAINT chk_mm_controlled_class CHECK (
    is_controlled_substance = FALSE
    OR controlled_class IN ('CDa-Narcotic', 'CDa-Psychotropic', 'CDb')
  ),
  CONSTRAINT chk_mm_uae_schedule CHECK (
    is_controlled_substance = FALSE
    OR uae_legal_schedule IN (
      'Schedule 1', 'Schedule 2', 'Schedule 3',
      'Schedule 4', 'Schedule 5', 'Schedule 6',
      'Schedule 7', 'Schedule 8', 'Schedule 9'
    )
  ),
  CONSTRAINT chk_mm_uep CHECK (
    is_controlled_substance = FALSE OR uep_required = TRUE
  )
);

CREATE INDEX idx_mm_rxnorm ON medication_master(rxnorm_code);
CREATE INDEX idx_mm_generic ON medication_master(generic_name);
CREATE INDEX idx_mm_controlled ON medication_master(is_controlled_substance)
  WHERE is_controlled_substance = TRUE;
CREATE INDEX idx_mm_active ON medication_master(active_flag)
  WHERE active_flag = TRUE;

CREATE TABLE clinical_alerts (
  alert_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  order_id BIGINT NOT NULL,
  order_table VARCHAR(30) NOT NULL,
  alert_type VARCHAR(50) NOT NULL,
  severity VARCHAR(20) NOT NULL,
  alert_message TEXT NOT NULL,
  interacting_entity VARCHAR(200),
  recommended_action TEXT,
  alert_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  alert_response VARCHAR(30),
  override_reason_code VARCHAR(30),
  override_reason_text TEXT,
  response_user_id BIGINT,
  response_datetime TIMESTAMP,

  CONSTRAINT chk_ca_order_table CHECK (
    order_table IN ('medication_orders', 'laboratory_orders', 'imaging_orders')
  ),
  CONSTRAINT chk_ca_type CHECK (
    alert_type IN ('drug-allergy', 'drug-drug', 'dose-range', 'duplicate-therapy',
                   'renal-dosing', 'formulary', 'duplicate-test', 'pregnancy',
                   'appropriateness', 'contrast-safety', 'radiation-dose',
                   'controlled-substance', 'insurance-auth')
  ),
  CONSTRAINT chk_ca_severity CHECK (
    severity IN ('contraindicated', 'severe', 'major', 'moderate', 'minor', 'informational')
  ),
  CONSTRAINT chk_ca_response CHECK (
    alert_response IS NULL
    OR alert_response IN ('cancelled', 'overridden', 'accepted-alternative', 'acknowledged', 'co-signed')
  ),
  CONSTRAINT chk_ca_override_reason CHECK (
    alert_response != 'overridden'
    OR override_reason_code IN (
      'CLIN_NECESSITY', 'PRIOR_TOLERANCE', 'SHORT_DURATION',
      'ALLERGY_UNCERTAIN', 'NO_ALTERNATIVE', 'SPECIALIST_CONSULT',
      'PATIENT_PREFERENCE', 'INCORRECT_ALERT'
    )
  )
);

CREATE INDEX idx_ca_order ON clinical_alerts(order_id, order_table);
CREATE INDEX idx_ca_type ON clinical_alerts(alert_type, severity);
CREATE INDEX idx_ca_response ON clinical_alerts(alert_response);
CREATE INDEX idx_ca_datetime ON clinical_alerts(alert_datetime);

CREATE TABLE order_audit_log (
  audit_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  order_id BIGINT NOT NULL,
  order_table VARCHAR(30) NOT NULL,
  action VARCHAR(30) NOT NULL,
  user_id BIGINT NOT NULL,
  action_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  workstation_id VARCHAR(50),
  ip_address INET,
  session_id VARCHAR(100),
  old_values JSONB,
  new_values JSONB,
  override_reason TEXT,
  alert_type VARCHAR(50),
  notes TEXT,

  CONSTRAINT fk_oal_user FOREIGN KEY (user_id) REFERENCES users(user_id),
  CONSTRAINT chk_oal_order_table CHECK (
    order_table IN ('medication_orders', 'laboratory_orders', 'imaging_orders')
  ),
  CONSTRAINT chk_oal_action CHECK (
    action IN ('created', 'signed', 'co-signed', 'modified', 'cancelled',
               'discontinued', 'transmitted', 'acknowledged', 'overridden',
               'uep-submitted', 'uep-approved', 'uep-rejected',
               'dual-verified', 'verbal-entered', 'break-the-glass')
  )
);

CREATE INDEX idx_oal_order ON order_audit_log(order_id, order_table);
CREATE INDEX idx_oal_user ON order_audit_log(user_id);
CREATE INDEX idx_oal_action ON order_audit_log(action, action_datetime);
CREATE INDEX idx_oal_datetime ON order_audit_log(action_datetime);

CREATE TABLE pending_pharmacy_queue (
  queue_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  order_id BIGINT NOT NULL UNIQUE,
  priority VARCHAR(20) NOT NULL,
  queue_status VARCHAR(30) NOT NULL DEFAULT 'pending',
  is_controlled_substance BOOLEAN NOT NULL DEFAULT FALSE,
  received_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  assigned_pharmacist_id BIGINT,
  verified_datetime TIMESTAMP,
  verified_by BIGINT,
  dispensed_datetime TIMESTAMP,
  dispensed_by BIGINT,
  rejection_reason TEXT,
  notes TEXT,

  CONSTRAINT fk_ppq_order FOREIGN KEY (order_id) REFERENCES medication_orders(order_id),
  CONSTRAINT fk_ppq_assigned FOREIGN KEY (assigned_pharmacist_id) REFERENCES users(user_id),
  CONSTRAINT fk_ppq_verified FOREIGN KEY (verified_by) REFERENCES users(user_id),
  CONSTRAINT chk_ppq_status CHECK (
    queue_status IN ('pending', 'in-review', 'verified', 'dispensed',
                     'rejected', 'on-hold', 'cancelled')
  ),
  CONSTRAINT chk_ppq_priority CHECK (priority IN ('stat', 'asap', 'today', 'routine'))
);

CREATE INDEX idx_ppq_status ON pending_pharmacy_queue(queue_status, priority);
CREATE INDEX idx_ppq_controlled ON pending_pharmacy_queue(is_controlled_substance)
  WHERE is_controlled_substance = TRUE;

CREATE TABLE order_sets (
  order_set_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  set_name VARCHAR(200) NOT NULL,
  set_code VARCHAR(50) NOT NULL UNIQUE,
  description TEXT,
  clinical_category VARCHAR(100),
  target_diagnoses JSONB,
  approved_by VARCHAR(200),
  approved_datetime TIMESTAMP,
  review_due_date DATE,
  active_flag BOOLEAN NOT NULL DEFAULT TRUE,
  version INT NOT NULL DEFAULT 1,
  facility_id BIGINT,
  created_by BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP,

  CONSTRAINT fk_os_facility FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
  CONSTRAINT fk_os_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
);

CREATE TABLE order_set_items (
  item_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  order_set_id BIGINT NOT NULL,
  item_type VARCHAR(20) NOT NULL,
  display_order INT NOT NULL,
  item_name VARCHAR(200) NOT NULL,
  default_values JSONB NOT NULL,
  is_required BOOLEAN NOT NULL DEFAULT FALSE,
  is_conditional BOOLEAN NOT NULL DEFAULT FALSE,
  condition_expression TEXT,
  time_offset_minutes INT DEFAULT 0,

  CONSTRAINT fk_osi_set FOREIGN KEY (order_set_id) REFERENCES order_sets(order_set_id),
  CONSTRAINT chk_osi_type CHECK (
    item_type IN ('medication', 'laboratory', 'imaging', 'nursing', 'dietary', 'consult')
  )
);

CREATE INDEX idx_osi_set ON order_set_items(order_set_id, display_order);

CREATE TABLE order_set_executions (
  execution_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  order_set_id BIGINT NOT NULL,
  patient_id BIGINT NOT NULL,
  encounter_id BIGINT NOT NULL,
  executed_by BIGINT NOT NULL,
  executed_datetime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  items_selected INT NOT NULL,
  items_total INT NOT NULL,
  items_modified INT NOT NULL DEFAULT 0,

  CONSTRAINT fk_ose_set FOREIGN KEY (order_set_id) REFERENCES order_sets(order_set_id),
  CONSTRAINT fk_ose_patient FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  CONSTRAINT fk_ose_encounter FOREIGN KEY (encounter_id) REFERENCES encounters(encounter_id),
  CONSTRAINT fk_ose_user FOREIGN KEY (executed_by) REFERENCES users(user_id)
);

CREATE TABLE controlled_substance_inventory (
  inventory_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  medication_id BIGINT NOT NULL,
  facility_id BIGINT NOT NULL,
  controlled_class VARCHAR(20) NOT NULL,
  uae_legal_schedule VARCHAR(10) NOT NULL,
  transaction_type VARCHAR(20) NOT NULL,
  quantity DECIMAL(10,2) NOT NULL,
  balance_after DECIMAL(10,2) NOT NULL,
  lot_number VARCHAR(50),
  expiry_date DATE,
  source_document VARCHAR(100),
  order_id BIGINT,
  witnessed_by BIGINT,
  performed_by BIGINT NOT NULL,
  performed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notes TEXT,

  CONSTRAINT fk_csi_medication FOREIGN KEY (medication_id) REFERENCES medication_master(medication_id),
  CONSTRAINT fk_csi_facility FOREIGN KEY (facility_id) REFERENCES facilities(facility_id),
  CONSTRAINT fk_csi_order FOREIGN KEY (order_id) REFERENCES medication_orders(order_id),
  CONSTRAINT fk_csi_witnessed FOREIGN KEY (witnessed_by) REFERENCES users(user_id),
  CONSTRAINT fk_csi_performed FOREIGN KEY (performed_by) REFERENCES users(user_id),
  CONSTRAINT chk_csi_class CHECK (controlled_class IN ('CDa-Narcotic', 'CDa-Psychotropic', 'CDb')),
  CONSTRAINT chk_csi_schedule CHECK (
    uae_legal_schedule IN (
      'Schedule 1', 'Schedule 2', 'Schedule 3',
      'Schedule 4', 'Schedule 5', 'Schedule 6',
      'Schedule 7', 'Schedule 8', 'Schedule 9'
    )
  ),
  CONSTRAINT chk_csi_transaction CHECK (
    transaction_type IN ('receipt', 'dispensing', 'return', 'disposal', 'adjustment')
  ),
  CONSTRAINT chk_disposal_witness CHECK (
    transaction_type != 'disposal' OR witnessed_by IS NOT NULL
  )
);

CREATE INDEX idx_csi_reporting ON controlled_substance_inventory(
  facility_id, controlled_class, transaction_type, performed_at
);
CREATE INDEX idx_csi_medication ON controlled_substance_inventory(medication_id);
CREATE INDEX idx_csi_balance ON controlled_substance_inventory(medication_id, facility_id);