-- PR-19 (May 2026): public status-page email subscribers
-- =========================================================
-- Adds the StatusPageSubscriber table so external stakeholders can
-- subscribe at /status for email updates on incidents.
--
-- - email is UNIQUE (one subscription per address)
-- - confirmToken / unsubscribeToken are 32-byte hex tokens; treat as
--   long-lived secrets, never logged
-- - confirmedAt = NULL means the address is provisional (waiting for
--   the click on the confirmation email); notification fan-out skips
--   provisional rows

-- ============================================================
-- UP
-- ============================================================

CREATE TABLE `StatusPageSubscriber` (
  `id`               INT NOT NULL AUTO_INCREMENT,
  `email`            VARCHAR(255) NOT NULL,
  `confirmedAt`      DATETIME(3) NULL,
  `confirmToken`     VARCHAR(64) NOT NULL,
  `unsubscribeToken` VARCHAR(64) NOT NULL,
  `createdAt`        DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `lastNotifiedAt`   DATETIME(3) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `StatusPageSubscriber_email_key` (`email`),
  UNIQUE KEY `StatusPageSubscriber_confirmToken_key` (`confirmToken`),
  UNIQUE KEY `StatusPageSubscriber_unsubscribeToken_key` (`unsubscribeToken`),
  KEY `StatusPageSubscriber_confirmedAt_idx` (`confirmedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- DOWN
-- ============================================================

DROP TABLE `StatusPageSubscriber`;