-- Phase 2: hospital connectivity — environment locations, documents (ECG), NEWS2, nurse charting

CREATE TABLE IF NOT EXISTS env_locations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  kind ENUM('fridge','freezer','blood_bank','pharmacy','vaccine','lab','room','ot','icu','gas','other') NOT NULL DEFAULT 'fridge',
  ward_id INT UNSIGNED NULL,
  notes VARCHAR(255) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE devices ADD COLUMN location_id INT UNSIGNED NULL AFTER bed_id;

ALTER TABLE readings ADD COLUMN location_id INT UNSIGNED NULL AFTER patient_id,
  ADD COLUMN charted_at DATETIME NULL, ADD COLUMN charted_by INT UNSIGNED NULL,
  ADD KEY idx_loc (location_id, param_code, measured_at);

ALTER TABLE alerts ADD COLUMN location_id INT UNSIGNED NULL AFTER patient_id,
  MODIFY alert_type ENUM('threshold','missed_reading','device_offline','gateway_offline','low_battery','manual','news2','env_excursion') NOT NULL,
  ADD KEY idx_alert_loc (location_id, status);

ALTER TABLE thresholds MODIFY scope ENUM('client','patient','location') NOT NULL;

ALTER TABLE admissions ADD COLUMN on_oxygen TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN consciousness ENUM('A','C','V','P','U') NOT NULL DEFAULT 'A',
  ADD COLUMN spo2_scale TINYINT NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS news2_scores (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  score TINYINT NOT NULL,
  risk ENUM('low','low_medium','medium','high') NOT NULL,
  components VARCHAR(500) NOT NULL,
  complete TINYINT(1) NOT NULL DEFAULT 1,
  computed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id, computed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS documents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NULL,
  device_id INT UNSIGNED NULL,
  gateway_id INT UNSIGNED NULL,
  doc_type ENUM('ecg','report','other') NOT NULL DEFAULT 'ecg',
  title VARCHAR(160) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  mime VARCHAR(80) NOT NULL,
  size_bytes INT UNSIGNED NOT NULL DEFAULT 0,
  meta TEXT NULL,
  status ENUM('new','reviewed','pending') NOT NULL DEFAULT 'new',
  reviewed_by INT UNSIGNED NULL, reviewed_at DATETIME NULL, review_note VARCHAR(255) NULL,
  measured_at DATETIME NOT NULL,
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id), KEY (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE gateways ADD COLUMN edge_config TEXT NULL;
