-- Trust Mail M1: core tables (settings, rate limits, jobs, locks).
-- Rules: statements must be idempotent (IF NOT EXISTS); never edit this file after release.
-- up
CREATE TABLE IF NOT EXISTS core_settings (
  `key` VARCHAR(120) NOT NULL,
  value MEDIUMTEXT NOT NULL,
  is_secret TINYINT(1) NOT NULL DEFAULT 0,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS core_rate_limits (
  bucket CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  window_start DATETIME NOT NULL,
  hits INT UNSIGNED NOT NULL DEFAULT 0,
  blocked_until DATETIME NULL,
  PRIMARY KEY (bucket),
  KEY idx_window (window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS core_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  type VARCHAR(60) NOT NULL,
  payload JSON NULL,
  run_at DATETIME NOT NULL,
  status ENUM('queued','running','done','failed') NOT NULL DEFAULT 'queued',
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  last_error VARCHAR(500) NULL,
  PRIMARY KEY (id),
  KEY idx_status_run (status, run_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS core_locks (
  name VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  owner CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  heartbeat_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- down
DROP TABLE IF EXISTS core_locks;
DROP TABLE IF EXISTS core_jobs;
DROP TABLE IF EXISTS core_rate_limits;
DROP TABLE IF EXISTS core_settings;