-- CodeAlert — Hospital Emergency Code Activation System
-- MySQL / MariaDB schema (cPanel compatible)

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)  DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role          ENUM('admin','dispatcher','staff') NOT NULL DEFAULT 'staff',
  job_title     VARCHAR(120) DEFAULT NULL,
  department    VARCHAR(120) DEFAULT NULL,
  badge_number  VARCHAR(40)  DEFAULT NULL,
  active        TINYINT(1) NOT NULL DEFAULT 1,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Physical areas of the hospital (building / floor / zone / unit)
CREATE TABLE IF NOT EXISTS locations (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(150) NOT NULL,          -- e.g. "ICU", "ER Resus Bay", "Ward 3B"
  building  VARCHAR(100) DEFAULT NULL,
  floor     VARCHAR(50)  DEFAULT NULL,
  zone      VARCHAR(100) DEFAULT NULL,
  active    TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Emergency code definitions
CREATE TABLE IF NOT EXISTS code_types (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  name           VARCHAR(80)  NOT NULL,      -- "Code Blue"
  short_code     VARCHAR(20)  NOT NULL,      -- "BLUE"
  color          VARCHAR(9)   NOT NULL DEFAULT '#D32F2F',
  description    VARCHAR(255) DEFAULT NULL,
  voice_template VARCHAR(255) NOT NULL DEFAULT '{code}. {code}. {location}.',
  broadcast_all  TINYINT(1) NOT NULL DEFAULT 0,  -- 1 = disaster: alert EVERYONE regardless of roster
  repeat_count   TINYINT     NOT NULL DEFAULT 3, -- voice announcement repetitions on device
  active         TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Response teams (e.g. "Code Blue Team", "Trauma Team A")
CREATE TABLE IF NOT EXISTS teams (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  name   VARCHAR(120) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Which team responds to which code, optionally per location.
-- location_id NULL = this team covers that code hospital-wide.
CREATE TABLE IF NOT EXISTS code_team_map (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  code_type_id INT NOT NULL,
  team_id      INT NOT NULL,
  location_id  INT DEFAULT NULL,
  FOREIGN KEY (code_type_id) REFERENCES code_types(id) ON DELETE CASCADE,
  FOREIGN KEY (team_id)      REFERENCES teams(id)      ON DELETE CASCADE,
  FOREIGN KEY (location_id)  REFERENCES locations(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- On-call roster: who is on duty for which team, on which date/shift
CREATE TABLE IF NOT EXISTS oncall_schedule (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT  NOT NULL,
  team_id    INT  NOT NULL,
  duty_date  DATE NOT NULL,
  start_time TIME NOT NULL DEFAULT '00:00:00',
  end_time   TIME NOT NULL DEFAULT '23:59:59',  -- if end < start, shift crosses midnight into next day
  notes      VARCHAR(255) DEFAULT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  UNIQUE KEY uq_shift (user_id, team_id, duty_date, start_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- A code activation (the "dispatch")
CREATE TABLE IF NOT EXISTS activations (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  code_type_id    INT NOT NULL,
  location_id     INT NOT NULL,
  location_detail VARCHAR(150) DEFAULT NULL,   -- room/bed, e.g. "Room 12, Bed 2"
  details         TEXT,                        -- pre-information for responders
  caller_name     VARCHAR(120) DEFAULT NULL,
  caller_ext      VARCHAR(40)  DEFAULT NULL,
  activated_by    INT NOT NULL,
  status          ENUM('active','stood_down','resolved') NOT NULL DEFAULT 'active',
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at     DATETIME DEFAULT NULL,
  resolved_by     INT DEFAULT NULL,
  FOREIGN KEY (code_type_id) REFERENCES code_types(id),
  FOREIGN KEY (location_id)  REFERENCES locations(id),
  FOREIGN KEY (activated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Who was notified for an activation + their acknowledgement
CREATE TABLE IF NOT EXISTS activation_recipients (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  activation_id INT NOT NULL,
  user_id       INT NOT NULL,
  notified_at   DATETIME DEFAULT NULL,
  ack_status    ENUM('pending','acknowledged','responding','unavailable') NOT NULL DEFAULT 'pending',
  ack_at        DATETIME DEFAULT NULL,
  FOREIGN KEY (activation_id) REFERENCES activations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id)       REFERENCES users(id)       ON DELETE CASCADE,
  UNIQUE KEY uq_recipient (activation_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Follow-up broadcasts on an activation (updates / stand-down messages)
CREATE TABLE IF NOT EXISTS activation_updates (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  activation_id INT NOT NULL,
  message       TEXT NOT NULL,
  created_by    INT NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (activation_id) REFERENCES activations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by)    REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Vocera-style voice/text broadcasts (announcements without a code activation)
CREATE TABLE IF NOT EXISTS broadcasts (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  message    TEXT NOT NULL,
  target     ENUM('all','team') NOT NULL DEFAULT 'all',
  team_id    INT DEFAULT NULL,
  recipients INT NOT NULL DEFAULT 0,
  sent_by    INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sent_by) REFERENCES users(id),
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- ---------------------------------------------------------------------------
-- Seed data: standard hospital emergency codes
-- ---------------------------------------------------------------------------
INSERT INTO code_types (name, short_code, color, description, voice_template, broadcast_all, repeat_count) VALUES
('Code Blue',   'BLUE',   '#1565C0', 'Adult cardiac / respiratory arrest',      'Code Blue. Code Blue. {location}.', 0, 3),
('Code Blue Paediatric', 'BLUEP', '#0D47A1', 'Paediatric cardiac / respiratory arrest', 'Code Blue Paediatric. {location}.', 0, 3),
('Trauma Code', 'TRAUMA', '#B71C1C', 'Major trauma team activation',            'Trauma Code. Trauma Code. {location}.', 0, 3),
('Code Red',    'RED',    '#E65100', 'Fire / smoke',                            'Code Red. Code Red. {location}.', 0, 3),
('Code Pink',   'PINK',   '#C2185B', 'Infant / child abduction',                'Code Pink. Code Pink. {location}.', 1, 3),
('Code Black',  'BLACK',  '#212121', 'Bomb threat',                             'Code Black. {location}.', 1, 3),
('Code Orange', 'ORANGE', '#EF6C00', 'Hazardous material spill',                'Code Orange. {location}.', 0, 3),
('Code Silver', 'SILVER', '#607D8B', 'Person with a weapon',                    'Code Silver. {location}.', 1, 3),
('Code White',  'WHITE',  '#455A64', 'Violent / aggressive person',             'Code White. {location}.', 0, 3),
('Rapid Response', 'RRT', '#2E7D32', 'Deteriorating patient — rapid response team', 'Rapid Response Team. {location}.', 0, 2),
('Major Disaster', 'DISASTER', '#4A148C', 'Mass casualty / major incident', 'Attention all staff. Major incident declared. {location}.', 1, 3);

INSERT INTO teams (name) VALUES
('Code Blue Team'), ('Trauma Team'), ('Fire Response Team'),
('Security Team'), ('Rapid Response Team'), ('HazMat Team');

-- Map codes to teams (hospital-wide coverage)
INSERT INTO code_team_map (code_type_id, team_id) VALUES
(1, 1), (2, 1),            -- Code Blue (adult + paeds)  -> Code Blue Team
(3, 2),                    -- Trauma                     -> Trauma Team
(4, 3),                    -- Code Red                   -> Fire Response Team
(7, 6),                    -- Code Orange                -> HazMat Team
(9, 4),                    -- Code White                 -> Security Team
(10, 5);                   -- Rapid Response             -> RRT

INSERT INTO locations (name, building, floor, zone) VALUES
('Emergency Department', 'Main Building', 'Ground', 'ED'),
('ICU',                  'Main Building', '2',      'Critical Care'),
('Operating Theatres',   'Main Building', '1',      'Surgery'),
('Ward 3A',              'Main Building', '3',      'Medical'),
('Ward 3B',              'Main Building', '3',      'Medical'),
('Maternity',            'Women & Children', '1',   'Obstetrics'),
('NICU',                 'Women & Children', '2',   'Neonatal'),
('Outpatient Clinics',   'OPD Building',  'Ground', 'OPD'),
('Radiology',            'Main Building', 'Ground', 'Diagnostics'),
('Main Lobby',           'Main Building', 'Ground', 'Public');