-- VPS Monitor for cPanel / MySQL 5.7+ / MariaDB 10.3+
-- Import into a NEW dedicated database using phpMyAdmin.
CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(80) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  last_login_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ip_hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_login_attempts (ip_hash,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS monitors (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  type ENUM('http','tcp') NOT NULL DEFAULT 'http',
  target VARCHAR(2048) NOT NULL,
  port SMALLINT UNSIGNED NULL,
  is_enabled TINYINT(1) NOT NULL DEFAULT 1,
  fail_threshold TINYINT UNSIGNED NOT NULL DEFAULT 2,
  status ENUM('unknown','up','down') NOT NULL DEFAULT 'unknown',
  consecutive_fails SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  last_checked_at DATETIME NULL,
  last_changed_at DATETIME NULL,
  last_response_ms INT UNSIGNED NULL,
  last_http_code SMALLINT UNSIGNED NULL,
  last_error VARCHAR(240) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY(id),
  KEY ix_monitors_enabled (is_enabled, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS checks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  monitor_id BIGINT UNSIGNED NOT NULL,
  is_success TINYINT(1) NOT NULL,
  response_ms INT UNSIGNED NOT NULL,
  http_code SMALLINT UNSIGNED NULL,
  message VARCHAR(240) NULL,
  checked_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY ix_checks_monitor_at (monitor_id,checked_at),
  KEY ix_checks_at (checked_at),
  CONSTRAINT fk_checks_monitor FOREIGN KEY (monitor_id) REFERENCES monitors(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS incidents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  monitor_id BIGINT UNSIGNED NOT NULL,
  started_at DATETIME NOT NULL,
  resolved_at DATETIME NULL,
  cause VARCHAR(240) NULL,
  resolution_note VARCHAR(240) NULL,
  PRIMARY KEY (id),
  KEY ix_incidents_monitor_time (monitor_id,started_at),
  KEY ix_incidents_open (resolved_at),
  CONSTRAINT fk_incidents_monitor FOREIGN KEY (monitor_id) REFERENCES monitors(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  monitor_id BIGINT UNSIGNED NOT NULL,
  incident_id BIGINT UNSIGNED NULL,
  event_type ENUM('down','up') NOT NULL,
  message TEXT NOT NULL,
  state ENUM('pending','sent') NOT NULL DEFAULT 'pending',
  attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  next_attempt_at DATETIME NOT NULL,
  last_error VARCHAR(240) NULL,
  created_at DATETIME NOT NULL,
  sent_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY ix_notifications_retry (state,next_attempt_at),
  KEY ix_notifications_mon (monitor_id,created_at),
  CONSTRAINT fk_notifications_monitor FOREIGN KEY (monitor_id) REFERENCES monitors(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_notifications_incident FOREIGN KEY (incident_id) REFERENCES incidents(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;