-- Phase 5: AI deterioration risk + anomaly detection, smart pill dispensers & inhalers (medication adherence)

CREATE TABLE IF NOT EXISTS risk_scores (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  score TINYINT UNSIGNED NOT NULL COMMENT '0-100 risk index',
  level ENUM('low','watch','high') NOT NULL,
  setting ENUM('hospital','home') NOT NULL DEFAULT 'hospital',
  factors TEXT NULL COMMENT 'JSON [{code,text,points}] — why the score is what it is',
  features TEXT NULL COMMENT 'JSON inputs used (for audit and model training)',
  model VARCHAR(40) NOT NULL DEFAULT 'cp-risk-1',
  computed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id, computed_at), KEY (computed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS anomalies (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NULL,
  device_id INT UNSIGNED NULL,
  reading_id BIGINT UNSIGNED NULL,
  param_code VARCHAR(30) NOT NULL,
  kind ENUM('outlier','shift','flatline','jump','implausible') NOT NULL,
  value DECIMAL(10,3) NULL,
  baseline DECIMAL(10,3) NULL,
  spread DECIMAL(10,3) NULL,
  score DECIMAL(6,2) NULL,
  detail VARCHAR(255) NULL,
  alert_id INT UNSIGNED NULL,
  detected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id, detected_at), KEY (device_id, detected_at), KEY (kind, detected_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS medications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  kind ENUM('tablet','capsule','inhaler_controller','inhaler_rescue','injection','liquid','other') NOT NULL DEFAULT 'tablet',
  dose VARCHAR(80) NULL,
  times VARCHAR(120) NULL COMMENT 'comma separated HH:MM; empty = as needed',
  window_min SMALLINT UNSIGNED NOT NULL DEFAULT 60,
  device_id INT UNSIGNED NULL COMMENT 'pill dispenser / smart inhaler',
  slot VARCHAR(20) NULL COMMENT 'dispenser compartment / cartridge',
  start_date DATE NOT NULL,
  end_date DATE NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  remind TINYINT(1) NOT NULL DEFAULT 1,
  notes VARCHAR(255) NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id, active), KEY (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS dose_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  medication_id INT UNSIGNED NULL,
  patient_id INT UNSIGNED NOT NULL,
  device_id INT UNSIGNED NULL,
  scheduled_at DATETIME NULL,
  event_at DATETIME NOT NULL,
  status ENUM('taken','late','missed','skipped','extra','puff','dispensed') NOT NULL,
  source ENUM('dispenser','inhaler','app','portal','nurse','system') NOT NULL,
  puffs TINYINT UNSIGNED NULL,
  detail VARCHAR(255) NULL,
  entered_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_slot (medication_id, scheduled_at),
  KEY (patient_id, event_at), KEY (device_id, event_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE alerts MODIFY alert_type ENUM('threshold','missed_reading','device_offline','gateway_offline','low_battery','manual','news2','env_excursion','device_alarm','cgm','rtls','nurse_call','maintenance','ai_risk','anomaly','medication') NOT NULL,
  ADD COLUMN feedback ENUM('useful','not_useful') NULL,
  ADD COLUMN feedback_by INT UNSIGNED NULL,
  ADD COLUMN feedback_note VARCHAR(255) NULL;

ALTER TABLE escalation_policies MODIFY match_type ENUM('any','threshold','news2','env_excursion','device_alarm','device','cgm','rtls','nurse_call','maintenance','ai_risk','anomaly','medication') NOT NULL DEFAULT 'any';
