-- Phase 3: waveforms, therapy devices (ventilator / infusion / dialysis / CTG), device alarms,
-- stethoscope audio, CGM + wearables, teleconsults, ABDM records, escalation trees with voice calls

-- Waveform chunks (ECG, pleth, resp, CO2, arterial line, CTG, ventilator curves).
-- Samples are int16 little-endian; physical value = raw * scale + offset. Kept for waveform_retention_hours; saved strips are kept for good.
CREATE TABLE IF NOT EXISTS waveforms (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NULL,
  bed_id INT UNSIGNED NULL,
  device_id INT UNSIGNED NULL,
  gateway_id INT UNSIGNED NULL,
  channel VARCHAR(20) NOT NULL,
  sample_rate SMALLINT UNSIGNED NOT NULL,
  unit VARCHAR(16) NOT NULL DEFAULT '',
  scale DOUBLE NOT NULL DEFAULT 1,
  offset_v DOUBLE NOT NULL DEFAULT 0,
  start_ms BIGINT NOT NULL COMMENT 'epoch milliseconds of first sample',
  n_samples INT UNSIGNED NOT NULL,
  samples MEDIUMBLOB NOT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_wf_patient (patient_id, channel, start_ms),
  KEY idx_wf_bed (bed_id, channel, start_ms),
  KEY idx_wf_recv (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS waveform_strips (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  title VARCHAR(160) NOT NULL,
  from_ms BIGINT NOT NULL,
  to_ms BIGINT NOT NULL,
  channels VARCHAR(120) NOT NULL,
  data MEDIUMTEXT NOT NULL COMMENT 'JSON: {channel:{rate,unit,start_ms,values[]}}',
  note VARCHAR(500) NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Therapy sessions: what the device is doing for the patient (ventilator mode, drug on a pump, dialysis prescription, CTG monitoring)
CREATE TABLE IF NOT EXISTS therapy_sessions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  device_id INT UNSIGNED NULL,
  kind ENUM('ventilation','anaesthesia','infusion','dialysis','ctg','oxygen') NOT NULL,
  label VARCHAR(160) NULL COMMENT 'e.g. Noradrenaline 8 mg in 50 mL, SIMV-VC, HD 4 h',
  details TEXT NULL COMMENT 'JSON settings / prescription',
  started_at DATETIME NOT NULL,
  ended_at DATETIME NULL,
  status ENUM('running','paused','ended') NOT NULL DEFAULT 'running',
  started_by INT UNSIGNED NULL,
  ended_by INT UNSIGNED NULL,
  end_note VARCHAR(255) NULL,
  KEY (patient_id, status), KEY (device_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Alarms raised by the device itself (occlusion, apnoea, disconnect, air in line, high pressure ...)
ALTER TABLE alerts MODIFY alert_type ENUM('threshold','missed_reading','device_offline','gateway_offline','low_battery','manual','news2','env_excursion','device_alarm','cgm') NOT NULL,
  ADD COLUMN source_code VARCHAR(80) NULL AFTER param_code,
  ADD COLUMN escalation_policy_id INT UNSIGNED NULL,
  ADD COLUMN escalation_step TINYINT UNSIGNED NOT NULL DEFAULT 0,
  ADD COLUMN escalation_next_at DATETIME NULL,
  ADD KEY idx_alert_esc (status, escalation_next_at);

-- Stethoscope recordings and CTG strips live with other device documents
ALTER TABLE documents MODIFY doc_type ENUM('ecg','report','other','audio','ctg') NOT NULL DEFAULT 'ecg',
  ADD COLUMN site VARCHAR(40) NULL AFTER title;

-- Escalation trees
CREATE TABLE IF NOT EXISTS escalation_policies (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  match_type ENUM('any','threshold','news2','env_excursion','device_alarm','device','cgm') NOT NULL DEFAULT 'any',
  min_severity ENUM('warning','critical') NOT NULL DEFAULT 'critical',
  ward_id INT UNSIGNED NULL,
  priority SMALLINT NOT NULL DEFAULT 100,
  active TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS escalation_steps (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  policy_id INT UNSIGNED NOT NULL,
  step_no TINYINT UNSIGNED NOT NULL,
  delay_min SMALLINT UNSIGNED NOT NULL COMMENT 'minutes after the alert was raised',
  channel ENUM('voice','whatsapp','sms','email') NOT NULL,
  target_type ENUM('care_team','role','user','phone') NOT NULL,
  target VARCHAR(120) NULL,
  KEY (policy_id, step_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS voice_calls (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  alert_id INT UNSIGNED NULL,
  user_id INT UNSIGNED NULL,
  to_number VARCHAR(20) NOT NULL,
  message VARCHAR(500) NOT NULL,
  provider VARCHAR(20) NOT NULL,
  token CHAR(32) NOT NULL,
  call_ref VARCHAR(80) NULL,
  status ENUM('queued','calling','answered','acknowledged','declined','no_answer','busy','failed') NOT NULL DEFAULT 'queued',
  digits VARCHAR(10) NULL,
  error VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY (token), KEY (alert_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Teleconsults with live vitals
CREATE TABLE IF NOT EXISTS teleconsults (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  doctor_id INT UNSIGNED NOT NULL,
  scheduled_at DATETIME NOT NULL,
  duration_min SMALLINT UNSIGNED NOT NULL DEFAULT 15,
  reason VARCHAR(255) NULL,
  status ENUM('scheduled','live','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
  provider VARCHAR(20) NOT NULL DEFAULT 'caremeet',
  room_ref VARCHAR(120) NULL,
  host_url VARCHAR(500) NULL,
  guest_url VARCHAR(500) NULL,
  patient_token CHAR(32) NOT NULL,
  findings TEXT NULL,
  advice TEXT NULL,
  follow_up DATE NULL,
  started_at DATETIME NULL,
  ended_at DATETIME NULL,
  reminder_sent TINYINT(1) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (patient_token), KEY (doctor_id, scheduled_at), KEY (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ABDM health records (FHIR documents handed to the ABDM bridge for the patient's ABHA)
CREATE TABLE IF NOT EXISTS abdm_records (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  record_type ENUM('WellnessRecord','OPConsultation') NOT NULL,
  care_context_ref VARCHAR(80) NOT NULL,
  display VARCHAR(160) NOT NULL,
  period_from DATETIME NULL,
  period_to DATETIME NULL,
  source_ref VARCHAR(40) NULL,
  bundle MEDIUMTEXT NOT NULL,
  status ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued',
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  response VARCHAR(500) NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  UNIQUE KEY (care_context_ref), KEY (patient_id), KEY (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE patients ADD COLUMN diabetes_type ENUM('none','t1','t2','gdm','other') NULL AFTER conditions;
ALTER TABLE users ADD COLUMN push_token VARCHAR(255) NULL;
