-- =============================================================
--  Monitoring System - MySQL Schema (PHP-only version)
-- =============================================================

CREATE DATABASE IF NOT EXISTS monitoring
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE monitoring;

CREATE TABLE IF NOT EXISTS systems (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(64)  NOT NULL,
  status      ENUM('online','degraded','offline') NOT NULL DEFAULT 'offline',
  last_seen   TIMESTAMP    NULL DEFAULT NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_name (name),
  KEY idx_status (status)
) ENGINE=InnoDB;

INSERT IGNORE INTO systems (name) VALUES
  ('jobcard_api'),
  ('mobile_app'),
  ('admin_panel');

CREATE TABLE IF NOT EXISTS metrics (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  system_name    VARCHAR(64)      NOT NULL,
  endpoint       VARCHAR(255)     NOT NULL,
  method         VARCHAR(10)      NOT NULL DEFAULT 'GET',
  response_time  INT UNSIGNED     NOT NULL COMMENT 'milliseconds',
  status_code    SMALLINT UNSIGNED NOT NULL,
  created_at     TIMESTAMP(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  KEY idx_system_created (system_name, created_at),
  KEY idx_created        (created_at),
  KEY idx_status         (status_code)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS errors (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  system_name  VARCHAR(64)      NOT NULL,
  message      TEXT             NOT NULL,
  file         VARCHAR(500)     DEFAULT NULL,
  line         INT UNSIGNED     DEFAULT NULL,
  trace        TEXT             DEFAULT NULL,
  severity     ENUM('info','warning','error','critical') NOT NULL DEFAULT 'error',
  created_at   TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_system_created (system_name, created_at),
  KEY idx_severity       (severity, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS activity_logs (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  jobcard_id   INT UNSIGNED     DEFAULT NULL,
  action       VARCHAR(64)      NOT NULL,
  user_id      INT UNSIGNED     DEFAULT NULL,
  metadata     JSON             DEFAULT NULL,
  created_at   TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_jobcard       (jobcard_id),
  KEY idx_user_created  (user_id, created_at),
  KEY idx_action        (action, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS alerts (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  system_name  VARCHAR(64)      NOT NULL,
  alert_type   VARCHAR(64)      NOT NULL,
  message      TEXT             NOT NULL,
  severity     ENUM('warning','critical') NOT NULL DEFAULT 'warning',
  resolved     TINYINT(1)       NOT NULL DEFAULT 0,
  created_at   TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_system_type (system_name, alert_type),
  KEY idx_created     (created_at)
) ENGINE=InnoDB;

-- Small table to track alert cooldowns (replaces the in-memory Map we had in Node)
CREATE TABLE IF NOT EXISTS alert_cooldowns (
  cooldown_key VARCHAR(160) PRIMARY KEY,
  last_fired   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;