use nms;

CREATE TABLE IF NOT EXISTS `checks` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT NOT NULL,
  `device_id` INT NOT NULL,
  `type` VARCHAR(12) NOT NULL, -- ping|http|tcp|snmp
  `name` VARCHAR(120) NOT NULL,
  `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `is_critical` TINYINT(1) NOT NULL DEFAULT 1,

  `interval_sec` INT NOT NULL DEFAULT 60,
  `timeout_sec` INT NOT NULL DEFAULT 3,
  `retries` INT NOT NULL DEFAULT 1,

  `config_json` JSON NOT NULL,

  `next_run_at` DATETIME NOT NULL,
  `last_run_at` DATETIME NULL,

  -- locking para daemon (evita doble ejecución)
  `locked_by` VARCHAR(64) NULL,
  `locked_at` DATETIME NULL,

  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,

  PRIMARY KEY (`id`),
  KEY `idx_due` (`is_enabled`,`next_run_at`),
  KEY `idx_company_device` (`company_id`,`device_id`),
  KEY `idx_lock` (`locked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

CREATE TABLE IF NOT EXISTS `check_runs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT NOT NULL,
  `device_id` INT NOT NULL,
  `check_id` BIGINT UNSIGNED NOT NULL,

  `ran_at` DATETIME NOT NULL,

  `status` VARCHAR(10) NOT NULL, -- up|down|warn
  `latency_ms` INT NULL,

  `value_num` DOUBLE NULL,
  `value_text` MEDIUMTEXT NULL,
  `error` TEXT NULL,

  PRIMARY KEY (`id`),
  KEY `idx_check_time` (`check_id`,`ran_at`),
  KEY `idx_device_time` (`device_id`,`ran_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

-- opcional pero recomendado para dedupe / streaks
CREATE TABLE IF NOT EXISTS `alert_state` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` INT NOT NULL,
  `check_id` BIGINT UNSIGNED NOT NULL,
  `last_status` VARCHAR(10) NOT NULL DEFAULT 'unknown',
  `last_change_at` DATETIME NULL,
  `streak_fail` INT NOT NULL DEFAULT 0,
  `streak_ok` INT NOT NULL DEFAULT 0,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_check` (`company_id`,`check_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
