-- CarePulse IoT — CLIENT DATABASE (one per client hospital / clinic / RPM provider)
-- Created automatically when the Level 1 console onboards a client.
-- Schema version 1. Later changes go in database/migrations/client/NNN_name.sql

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS meta (
  k VARCHAR(40) PRIMARY KEY,
  v VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  k VARCHAR(80) PRIMARY KEY,
  v TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  mobile VARCHAR(20) NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('client_admin','doctor','nurse','coordinator','biomed','staff') NOT NULL DEFAULT 'staff',
  department VARCHAR(80) NULL,
  registration_no VARCHAR(40) NULL,
  whatsapp_alerts TINYINT(1) NOT NULL DEFAULT 1,
  email_alerts TINYINT(1) NOT NULL DEFAULT 1,
  totp_secret VARCHAR(64) NULL,
  totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  failed_logins INT NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  last_login DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS wards (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  ward_type ENUM('icu','hdu','ward','ot','er','nicu','opd','daycare','home','store','other') NOT NULL DEFAULT 'ward',
  his_ward_code VARCHAR(40) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS beds (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ward_id INT UNSIGNED NOT NULL,
  bed_no VARCHAR(20) NOT NULL,
  his_bed_code VARCHAR(40) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY (ward_id, bed_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS patients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uhid VARCHAR(40) NULL,
  his_patient_id VARCHAR(60) NULL,
  name VARCHAR(140) NOT NULL,
  gender ENUM('M','F','O') NULL,
  dob DATE NULL,
  mobile VARCHAR(20) NULL,
  email VARCHAR(160) NULL,
  abha_number VARCHAR(20) NULL,
  abha_address VARCHAR(80) NULL,
  height_cm DECIMAL(5,1) NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(80) NULL,
  pincode VARCHAR(10) NULL,
  caregiver_name VARCHAR(120) NULL,
  caregiver_mobile VARCHAR(20) NULL,
  caregiver_relation VARCHAR(40) NULL,
  language ENUM('en','hi','mr','gu','ta','te','kn','bn','ml') NOT NULL DEFAULT 'en',
  conditions VARCHAR(255) NULL,
  notify_patient TINYINT(1) NOT NULL DEFAULT 1,
  notify_caregiver TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active','inactive','deceased') NOT NULL DEFAULT 'active',
  source ENUM('manual','his','import','app') NOT NULL DEFAULT 'manual',
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY (uhid), KEY (mobile), KEY (his_patient_id), KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  his_admission_id VARCHAR(60) NULL,
  ward_id INT UNSIGNED NULL,
  bed_id INT UNSIGNED NULL,
  doctor_id INT UNSIGNED NULL,
  doctor_name VARCHAR(120) NULL,
  admitted_at DATETIME NOT NULL,
  discharged_at DATETIME NULL,
  status ENUM('admitted','discharged','transferred') NOT NULL DEFAULT 'admitted',
  KEY (patient_id), KEY (his_admission_id), KEY (bed_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS programmes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  description TEXT NULL,
  duration_days INT NOT NULL DEFAULT 30,
  fee DECIMAL(10,2) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS programme_params (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  programme_id INT UNSIGNED NOT NULL,
  param_code VARCHAR(30) NOT NULL,
  per_day DECIMAL(4,1) NOT NULL DEFAULT 1 COMMENT 'expected readings per day; 0.14 = weekly',
  reminder_times VARCHAR(80) NULL COMMENT 'HH:MM,HH:MM',
  warn_low DECIMAL(10,2) NULL, warn_high DECIMAL(10,2) NULL,
  crit_low DECIMAL(10,2) NULL, crit_high DECIMAL(10,2) NULL,
  UNIQUE KEY (programme_id, param_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS enrolments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  programme_id INT UNSIGNED NOT NULL,
  doctor_id INT UNSIGNED NULL,
  coordinator_id INT UNSIGNED NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL,
  status ENUM('active','paused','completed','withdrawn') NOT NULL DEFAULT 'active',
  notes TEXT NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id, status), KEY (coordinator_id), KEY (doctor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS consents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  purpose VARCHAR(80) NOT NULL,
  version VARCHAR(20) NOT NULL,
  channel ENUM('app','staff','paper','whatsapp','kiosk') NOT NULL,
  given_by VARCHAR(120) NULL COMMENT 'patient / caregiver name',
  recorded_by INT UNSIGNED NULL,
  consent_text TEXT NOT NULL,
  ip VARCHAR(45) NULL,
  given_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  withdrawn_at DATETIME NULL,
  KEY (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS devices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  asset_tag VARCHAR(30) NULL,
  serial_no VARCHAR(80) NULL,
  mac VARCHAR(40) NULL,
  model_id INT UNSIGNED NULL COMMENT 'central device_models.id',
  category VARCHAR(40) NOT NULL,
  manufacturer VARCHAR(80) NULL,
  model_name VARCHAR(120) NULL,
  connection VARCHAR(20) NOT NULL DEFAULT 'ble_standard',
  ownership ENUM('hospital','patient_kit','patient_own') NOT NULL DEFAULT 'hospital',
  ward_id INT UNSIGNED NULL,
  bed_id INT UNSIGNED NULL COMMENT 'bedside device: auto-binds to the patient admitted in this bed',
  status ENUM('in_stock','issued','active','maintenance','retired') NOT NULL DEFAULT 'in_stock',
  purchase_date DATE NULL,
  warranty_till DATE NULL,
  amc_till DATE NULL,
  calibration_due DATE NULL,
  battery TINYINT UNSIGNED NULL,
  firmware VARCHAR(40) NULL,
  last_seen DATETIME NULL,
  last_gateway_id INT UNSIGNED NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (mac), KEY (serial_no), KEY (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_bindings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id INT UNSIGNED NOT NULL,
  patient_id INT UNSIGNED NOT NULL,
  bed_id INT UNSIGNED NULL,
  method ENUM('manual','qr','app','bed','api') NOT NULL DEFAULT 'manual',
  bound_by INT UNSIGNED NULL,
  bound_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  unbound_at DATETIME NULL,
  unbound_by INT UNSIGNED NULL,
  KEY (device_id, unbound_at), KEY (patient_id, unbound_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS gateways (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  gw_type ENUM('android','ios','edge','api') NOT NULL DEFAULT 'android',
  token_prefix VARCHAR(16) NULL,
  token_hash CHAR(64) NULL,
  pairing_code VARCHAR(20) NULL,
  pairing_expires DATETIME NULL,
  patient_id INT UNSIGNED NULL COMMENT 'home gateway bound to one patient',
  ward_id INT UNSIGNED NULL,
  push_token VARCHAR(255) NULL,
  app_version VARCHAR(20) NULL,
  device_info VARCHAR(255) NULL,
  battery TINYINT UNSIGNED NULL,
  last_seen DATETIME NULL,
  status ENUM('pending','active','revoked') NOT NULL DEFAULT 'pending',
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (token_prefix), KEY (pairing_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS readings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NULL COMMENT 'NULL = unassigned, waiting for staff',
  device_id INT UNSIGNED NULL,
  gateway_id INT UNSIGNED NULL,
  param_code VARCHAR(30) NOT NULL,
  value DECIMAL(10,3) NOT NULL,
  unit VARCHAR(16) NOT NULL,
  context VARCHAR(30) NULL COMMENT 'e.g. fasting, post_meal, resting',
  measured_at DATETIME NOT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  source ENUM('device','manual','photo','his','api','patient') NOT NULL DEFAULT 'device',
  status ENUM('valid','pending','rejected') NOT NULL DEFAULT 'valid',
  flag ENUM('normal','warning','critical') NOT NULL DEFAULT 'normal',
  batch_ref VARCHAR(40) NULL,
  entered_by INT UNSIGNED NULL,
  validated_by INT UNSIGNED NULL,
  validated_at DATETIME NULL,
  note VARCHAR(255) NULL,
  KEY (patient_id, param_code, measured_at),
  KEY (measured_at), KEY (status), KEY (device_id, measured_at),
  UNIQUE KEY uq_dedupe (device_id, param_code, measured_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS photo_reads (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  image_path VARCHAR(255) NOT NULL,
  provider VARCHAR(30) NOT NULL,
  detected_device VARCHAR(80) NULL,
  result_json TEXT NULL,
  status ENUM('processing','done','failed','confirmed','discarded') NOT NULL DEFAULT 'processing',
  error VARCHAR(255) NULL,
  uploaded_by_type ENUM('user','patient') NOT NULL DEFAULT 'user',
  uploaded_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS thresholds (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  scope ENUM('client','patient') NOT NULL,
  scope_id INT UNSIGNED NOT NULL DEFAULT 0,
  param_code VARCHAR(30) NOT NULL,
  warn_low DECIMAL(10,2) NULL, warn_high DECIMAL(10,2) NULL,
  crit_low DECIMAL(10,2) NULL, crit_high DECIMAL(10,2) NULL,
  set_by INT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY (scope, scope_id, param_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS alerts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NULL,
  device_id INT UNSIGNED NULL,
  gateway_id INT UNSIGNED NULL,
  reading_id BIGINT UNSIGNED NULL,
  param_code VARCHAR(30) NULL,
  alert_type ENUM('threshold','missed_reading','device_offline','gateway_offline','low_battery','manual') NOT NULL,
  severity ENUM('info','warning','critical') NOT NULL,
  value DECIMAL(10,3) NULL,
  message VARCHAR(255) NOT NULL,
  repeat_count INT NOT NULL DEFAULT 1,
  status ENUM('open','acknowledged','closed') NOT NULL DEFAULT 'open',
  raised_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ack_by INT UNSIGNED NULL, ack_at DATETIME NULL,
  closed_by INT UNSIGNED NULL, closed_at DATETIME NULL,
  action_note VARCHAR(255) NULL,
  KEY (status, severity), KEY (patient_id, status), KEY (raised_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS alert_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  alert_id INT UNSIGNED NOT NULL,
  event VARCHAR(30) NOT NULL,
  detail VARCHAR(255) NULL,
  user_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (alert_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reviews (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  enrolment_id INT UNSIGNED NULL,
  user_id INT UNSIGNED NOT NULL,
  note TEXT NOT NULL,
  share_with_patient TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notification_queue (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  channel ENUM('email','whatsapp','sms','push') NOT NULL,
  recipient VARCHAR(255) NOT NULL,
  subject VARCHAR(200) NULL,
  body TEXT NOT NULL,
  template VARCHAR(80) NULL,
  template_params TEXT NULL COMMENT 'JSON array',
  attachment_path VARCHAR(255) NULL,
  related_type VARCHAR(30) NULL,
  related_id BIGINT UNSIGNED NULL,
  status ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued',
  attempts TINYINT NOT NULL DEFAULT 0,
  provider_ref VARCHAR(120) NULL,
  last_error VARCHAR(255) NULL,
  send_after DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (status, send_after)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS patient_otps (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  patient_id INT UNSIGNED NOT NULL,
  code_hash CHAR(64) NOT NULL,
  attempts TINYINT NOT NULL DEFAULT 0,
  expires_at DATETIME NOT NULL,
  used TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (patient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS his_outbox (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event VARCHAR(40) NOT NULL,
  payload MEDIUMTEXT NOT NULL,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  attempts TINYINT NOT NULL DEFAULT 0,
  last_error VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at DATETIME NULL,
  KEY (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_type ENUM('user','patient','system','api','platform') NOT NULL,
  actor_id INT UNSIGNED NULL,
  action VARCHAR(60) NOT NULL,
  entity VARCHAR(40) NULL,
  entity_id BIGINT UNSIGNED NULL,
  detail VARCHAR(500) NULL,
  ip VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY (created_at), KEY (entity, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO meta (k, v) VALUES ('schema_version', '1');

-- Default RPM programme templates
INSERT IGNORE INTO programmes (code, name, description, duration_days) VALUES
('HTN', 'Hypertension care', 'Twice-daily BP with weekly weight for patients with high blood pressure.', 90),
('DM', 'Diabetes care', 'Fasting and post-meal sugar with weekly weight and BP.', 90),
('CARDIAC', 'Cardiac care (post-MI / heart failure)', 'Daily BP, pulse, SpO2 and weight - sudden weight gain is an early warning in heart failure.', 60),
('COPD', 'COPD / respiratory care', 'SpO2, pulse and temperature twice daily.', 60),
('POSTOP', 'Post-surgery recovery', 'Temperature, pulse, BP and SpO2 for the first weeks after discharge.', 21),
('ANC', 'Pregnancy (antenatal) monitoring', 'BP, weight and sugar for high-risk pregnancy.', 180),
('ELDER', 'Elderly wellness', 'Daily BP, pulse, SpO2 and temperature for seniors living at home.', 365);

INSERT IGNORE INTO programme_params (programme_id, param_code, per_day, reminder_times, warn_low, warn_high, crit_low, crit_high)
SELECT id,'bp_sys',2,'08:00,20:00',95,140,85,180 FROM programmes WHERE code='HTN' UNION ALL
SELECT id,'bp_dia',2,'08:00,20:00',55,90,45,110 FROM programmes WHERE code='HTN' UNION ALL
SELECT id,'pulse',2,'08:00,20:00',50,110,40,130 FROM programmes WHERE code='HTN' UNION ALL
SELECT id,'weight',0.2,'08:00',NULL,NULL,NULL,NULL FROM programmes WHERE code='HTN' UNION ALL
SELECT id,'glucose',2,'07:30,14:00',70,180,54,300 FROM programmes WHERE code='DM' UNION ALL
SELECT id,'bp_sys',0.3,'08:00',95,140,85,180 FROM programmes WHERE code='DM' UNION ALL
SELECT id,'weight',0.2,'08:00',NULL,NULL,NULL,NULL FROM programmes WHERE code='DM' UNION ALL
SELECT id,'bp_sys',1,'08:00',95,140,85,170 FROM programmes WHERE code='CARDIAC' UNION ALL
SELECT id,'bp_dia',1,'08:00',55,90,45,105 FROM programmes WHERE code='CARDIAC' UNION ALL
SELECT id,'pulse',1,'08:00',55,100,45,120 FROM programmes WHERE code='CARDIAC' UNION ALL
SELECT id,'spo2',1,'08:00',94,NULL,90,NULL FROM programmes WHERE code='CARDIAC' UNION ALL
SELECT id,'weight',1,'08:00',NULL,NULL,NULL,NULL FROM programmes WHERE code='CARDIAC' UNION ALL
SELECT id,'spo2',2,'08:00,20:00',92,NULL,88,NULL FROM programmes WHERE code='COPD' UNION ALL
SELECT id,'pulse',2,'08:00,20:00',55,110,45,130 FROM programmes WHERE code='COPD' UNION ALL
SELECT id,'temp',2,'08:00,20:00',NULL,37.8,NULL,38.9 FROM programmes WHERE code='COPD' UNION ALL
SELECT id,'temp',2,'08:00,20:00',NULL,37.8,NULL,38.9 FROM programmes WHERE code='POSTOP' UNION ALL
SELECT id,'pulse',2,'08:00,20:00',50,110,40,130 FROM programmes WHERE code='POSTOP' UNION ALL
SELECT id,'bp_sys',2,'08:00,20:00',95,150,85,180 FROM programmes WHERE code='POSTOP' UNION ALL
SELECT id,'spo2',2,'08:00,20:00',94,NULL,90,NULL FROM programmes WHERE code='POSTOP' UNION ALL
SELECT id,'bp_sys',1,'08:00',95,135,85,160 FROM programmes WHERE code='ANC' UNION ALL
SELECT id,'bp_dia',1,'08:00',55,85,45,110 FROM programmes WHERE code='ANC' UNION ALL
SELECT id,'weight',0.2,'08:00',NULL,NULL,NULL,NULL FROM programmes WHERE code='ANC' UNION ALL
SELECT id,'glucose',0.5,'07:30',70,140,54,250 FROM programmes WHERE code='ANC' UNION ALL
SELECT id,'bp_sys',1,'08:00',95,150,85,180 FROM programmes WHERE code='ELDER' UNION ALL
SELECT id,'pulse',1,'08:00',50,110,40,130 FROM programmes WHERE code='ELDER' UNION ALL
SELECT id,'spo2',1,'08:00',94,NULL,90,NULL FROM programmes WHERE code='ELDER' UNION ALL
SELECT id,'temp',1,'08:00',NULL,37.8,NULL,38.9 FROM programmes WHERE code='ELDER';

INSERT IGNORE INTO wards (name, ward_type) VALUES ('Home monitoring', 'home');
