-- Phase 4: hospital operations — RTLS floor maps (assets, staff, infants), nurse call & bed-exit, medical gas & utilities,
-- biomedical maintenance, scheduled reports

CREATE TABLE IF NOT EXISTS floors (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  building VARCHAR(80) NULL,
  level SMALLINT NOT NULL DEFAULT 0,
  image_path VARCHAR(255) NULL,
  width_m DECIMAL(7,2) NULL, height_m DECIMAL(7,2) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Polygon points are in % of the floor image (0–100) so maps scale on any screen
CREATE TABLE IF NOT EXISTS zones (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  floor_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  kind ENUM('room','ward','corridor','nursery','maternity','icu','ot','store','exit','restricted','other') NOT NULL DEFAULT 'room',
  ward_id INT UNSIGNED NULL,
  bed_id INT UNSIGNED NULL,
  polygon TEXT NOT NULL,
  KEY (floor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS rtls_anchors (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(64) NOT NULL,
  floor_id INT UNSIGNED NOT NULL,
  zone_id INT UNSIGNED NULL,
  x DECIMAL(6,2) NOT NULL, y DECIMAL(6,2) NOT NULL,
  last_seen DATETIME NULL,
  UNIQUE KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tags (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tag_code VARCHAR(64) NOT NULL,
  kind ENUM('asset','staff','infant','mother','patient') NOT NULL DEFAULT 'asset',
  label VARCHAR(120) NULL,
  device_id INT UNSIGNED NULL,
  user_id INT UNSIGNED NULL,
  patient_id INT UNSIGNED NULL,
  mother_tag_id INT UNSIGNED NULL COMMENT 'infant tag paired with the mother''s tag',
  status ENUM('active','inactive','lost') NOT NULL DEFAULT 'active',
  battery TINYINT UNSIGNED NULL,
  last_seen DATETIME NULL,
  floor_id INT UNSIGNED NULL, zone_id INT UNSIGNED NULL,
  x DECIMAL(6,2) NULL, y DECIMAL(6,2) NULL,
  anchor_id INT UNSIGNED NULL, anchor_rssi SMALLINT NULL,
  zone_since DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY (tag_code), KEY (kind, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per zone visit (written when a tag changes zone) – the location history
CREATE TABLE IF NOT EXISTS tag_positions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tag_id INT UNSIGNED NOT NULL,
  floor_id INT UNSIGNED NULL, zone_id INT UNSIGNED NULL,
  x DECIMAL(6,2) NULL, y DECIMAL(6,2) NULL,
  entered_at DATETIME NOT NULL,
  left_at DATETIME NULL,
  KEY (tag_id, entered_at), KEY (zone_id, entered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS nurse_calls (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  call_type ENUM('normal','bathroom','staff_assist','code_blue','bed_exit','pain','other') NOT NULL DEFAULT 'normal',
  ward_id INT UNSIGNED NULL, bed_id INT UNSIGNED NULL, zone_id INT UNSIGNED NULL,
  patient_id INT UNSIGNED NULL,
  source VARCHAR(80) NULL,
  status ENUM('open','answered','cleared') NOT NULL DEFAULT 'open',
  raised_at DATETIME NOT NULL,
  answered_at DATETIME NULL, answered_by INT UNSIGNED NULL, answer_method ENUM('presence','button','screen','auto') NULL,
  cleared_at DATETIME NULL,
  alert_id INT UNSIGNED NULL,
  KEY (status, raised_at), KEY (ward_id, raised_at), KEY (bed_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS maintenance_plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id INT UNSIGNED NOT NULL,
  kind ENUM('ppm','calibration','electrical_safety','qa') NOT NULL DEFAULT 'ppm',
  interval_days SMALLINT UNSIGNED NOT NULL DEFAULT 180,
  last_done DATE NULL,
  next_due DATE NOT NULL,
  vendor VARCHAR(120) NULL,
  checklist TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  KEY (next_due, active), KEY (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS work_orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  number VARCHAR(20) NOT NULL,
  device_id INT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NULL,
  kind ENUM('breakdown','ppm','calibration','electrical_safety','qa','installation','other') NOT NULL DEFAULT 'breakdown',
  priority ENUM('low','normal','high','critical') NOT NULL DEFAULT 'normal',
  title VARCHAR(200) NOT NULL,
  description TEXT NULL,
  reported_by INT UNSIGNED NULL, reported_name VARCHAR(120) NULL,
  reported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  assigned_to INT UNSIGNED NULL,
  vendor VARCHAR(120) NULL,
  status ENUM('open','assigned','in_progress','waiting_parts','completed','cancelled') NOT NULL DEFAULT 'open',
  device_down TINYINT(1) NOT NULL DEFAULT 0,
  down_from DATETIME NULL, down_to DATETIME NULL,
  due_date DATE NULL,
  resolution TEXT NULL, parts VARCHAR(255) NULL, cost DECIMAL(10,2) NULL,
  certificate_doc_id INT UNSIGNED NULL,
  completed_at DATETIME NULL, completed_by INT UNSIGNED NULL,
  UNIQUE KEY (number), KEY (device_id, status), KEY (status, priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS scheduled_reports (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  report ENUM('management','clinical','nurse_call','cold_chain','maintenance') NOT NULL DEFAULT 'management',
  frequency ENUM('daily','weekly','monthly') NOT NULL DEFAULT 'weekly',
  send_hour TINYINT UNSIGNED NOT NULL DEFAULT 8,
  recipients VARCHAR(500) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  last_sent_at DATETIME NULL,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) 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') NOT NULL,
  ADD COLUMN tag_id INT UNSIGNED NULL AFTER location_id;
ALTER TABLE escalation_policies MODIFY match_type ENUM('any','threshold','news2','env_excursion','device_alarm','device','cgm','rtls','nurse_call','maintenance') NOT NULL DEFAULT 'any';
ALTER TABLE env_locations MODIFY kind ENUM('fridge','freezer','blood_bank','pharmacy','vaccine','lab','room','ot','icu','gas','lmo','power','water','hvac','other') NOT NULL DEFAULT 'fridge';
ALTER TABLE documents MODIFY doc_type ENUM('ecg','report','other','audio','ctg','certificate') NOT NULL DEFAULT 'ecg';
ALTER TABLE devices ADD COLUMN criticality ENUM('high','medium','low') NOT NULL DEFAULT 'medium' AFTER status,
  ADD COLUMN cost DECIMAL(12,2) NULL AFTER purchase_date, ADD COLUMN amc_vendor VARCHAR(120) NULL AFTER amc_till;
