-- public status page: components (with optional HTTP / TCP monitor), daily uptime, incidents and maintenance
CREATE TABLE status_components (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NOT NULL DEFAULT '',
  sort INT NOT NULL DEFAULT 0,
  check_type VARCHAR(8) NOT NULL DEFAULT 'none' COMMENT 'none|http|tcp',
  target VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'URL, or host:port',
  expect VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'text the HTTP answer must contain',
  status VARCHAR(12) NOT NULL DEFAULT 'operational' COMMENT 'operational|degraded|partial|major|maintenance',
  manual TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'status set by staff, monitor does not change it',
  fails TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_checked DATETIME NULL,
  last_ms INT UNSIGNED NULL,
  last_error VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE status_days (
  component_id INT UNSIGNED NOT NULL,
  day DATE NOT NULL,
  checks INT UNSIGNED NOT NULL DEFAULT 0,
  failures INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (component_id, day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE status_incidents (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  kind VARCHAR(12) NOT NULL DEFAULT 'incident' COMMENT 'incident|maintenance',
  impact VARCHAR(12) NOT NULL DEFAULT 'degraded',
  components VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'component ids',
  starts_at DATETIME NOT NULL,
  ends_at DATETIME NULL COMMENT 'planned end of maintenance',
  resolved_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  KEY open (resolved_at, starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE status_updates (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  incident_id INT UNSIGNED NOT NULL,
  state VARCHAR(16) NOT NULL COMMENT 'investigating|identified|monitoring|resolved|scheduled|in progress|completed',
  message TEXT NOT NULL,
  admin_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  KEY incident (incident_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO settings (k, v) VALUES ('status_alert_dept', '0');