-- OnCall Dispatch — MySQL schema
-- Import via cPanel phpMyAdmin or: mysql -u USER -p DBNAME < schema.sql

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(40) NULL,
  password_hash VARCHAR(100) NOT NULL,
  role ENUM('admin','dispatcher','physician') NOT NULL DEFAULT 'physician',
  specialty VARCHAR(120) NULL,
  on_duty TINYINT(1) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  token_version INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS code_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(40) NOT NULL UNIQUE,
  name VARCHAR(80) NOT NULL,
  description VARCHAR(255) NULL,
  color CHAR(7) NOT NULL DEFAULT '#c62828',
  -- sound/channel base name; must match a file in mobile res/raw and iOS bundle
  sound VARCHAR(60) NOT NULL DEFAULT 'code_blue',
  escalation_seconds INT NOT NULL DEFAULT 90,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS areas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  category ENUM('ED','OPD','WARD','ICU','OT','OTHER') NOT NULL DEFAULT 'OTHER',
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS devices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  fcm_token VARCHAR(512) NOT NULL,
  platform ENUM('android','ios') NOT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_token (fcm_token(191)),
  CONSTRAINT fk_dev_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Who is on call for what. NULL code_type_id = all codes; NULL area_id = all areas.
-- NULL starts_at/ends_at = standing assignment (gated only by users.on_duty).
CREATE TABLE IF NOT EXISTS oncall_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  code_type_id INT NULL,
  area_id INT NULL,
  is_backup TINYINT(1) NOT NULL DEFAULT 0,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_oc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_oc_code FOREIGN KEY (code_type_id) REFERENCES code_types(id) ON DELETE CASCADE,
  CONSTRAINT fk_oc_area FOREIGN KEY (area_id) REFERENCES areas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code_type_id INT NOT NULL,
  area_id INT NOT NULL,
  ward VARCHAR(120) NULL,
  bed VARCHAR(40) NULL,
  details VARCHAR(500) NULL,
  status ENUM('active','resolved','cancelled') NOT NULL DEFAULT 'active',
  escalation_round INT NOT NULL DEFAULT 0,
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME NULL,
  resolved_by INT NULL,
  CONSTRAINT fk_act_code FOREIGN KEY (code_type_id) REFERENCES code_types(id),
  CONSTRAINT fk_act_area FOREIGN KEY (area_id) REFERENCES areas(id),
  CONSTRAINT fk_act_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activation_recipients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  activation_id INT NOT NULL,
  user_id INT NOT NULL,
  is_backup TINYINT(1) NOT NULL DEFAULT 0,
  notified_at DATETIME NULL,
  ack_status ENUM('pending','acknowledged','responding','arrived','declined') NOT NULL DEFAULT 'pending',
  ack_at DATETIME NULL,
  UNIQUE KEY uq_act_user (activation_id, user_id),
  CONSTRAINT fk_ar_act FOREIGN KEY (activation_id) REFERENCES activations(id) ON DELETE CASCADE,
  CONSTRAINT fk_ar_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Seed data ----------

INSERT INTO code_types (slug, name, description, color, sound, escalation_seconds) VALUES
  ('code_blue',      'Code Blue',      'Cardiac / respiratory arrest',          '#1565c0', 'code_blue',      60),
  ('trauma',         'Trauma Code',    'Major trauma team activation',          '#e65100', 'trauma',         60),
  ('code_red',       'Code Red',       'Fire / smoke',                          '#c62828', 'code_red',       120),
  ('code_black',     'Code Black',     'Bomb threat / suspicious package',      '#212121', 'code_black',     120),
  ('code_pink',      'Code Pink',      'Infant / child abduction',              '#ad1457', 'code_pink',      90),
  ('code_orange',    'Code Orange',    'Hazmat / mass casualty incident',       '#ef6c00', 'code_orange',    120),
  ('rapid_response', 'Rapid Response', 'Deteriorating patient — RRT activation','#2e7d32', 'rapid_response', 90)
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO areas (name, category) VALUES
  ('Emergency Department', 'ED'),
  ('Outpatient Clinics',   'OPD'),
  ('ICU',                  'ICU'),
  ('Ward 1',               'WARD'),
  ('Ward 2',               'WARD'),
  ('Ward 3',               'WARD'),
  ('Operating Theatre',    'OT'),
  ('Radiology',            'OTHER'),
  ('Main Lobby / Entrance','OTHER')
ON DUPLICATE KEY UPDATE category = VALUES(category);