CREATE TABLE IF NOT EXISTS panic_alerts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  user_role ENUM('student','driver') NOT NULL,
  trip_id INT NULL,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  accuracy_m DECIMAL(10,2) NULL,
  message VARCHAR(255) NULL,
  status ENUM('open','acknowledged','resolved') NOT NULL DEFAULT 'open',
  handled_by_admin_id INT NULL,
  handled_note VARCHAR(255) NULL,
  acknowledged_at DATETIME NULL,
  resolved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_status_created (status, created_at),
  INDEX idx_user_created (user_id, created_at),
  INDEX idx_trip_created (trip_id, created_at),
  CONSTRAINT fk_panic_alerts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_panic_alerts_trip FOREIGN KEY (trip_id) REFERENCES trips(id) ON DELETE SET NULL,
  CONSTRAINT fk_panic_alerts_admin FOREIGN KEY (handled_by_admin_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;