-- Newsletter signup throttle bucket — same shape as `login_attempt`,
-- consumed by `NewsletterSubscribeThrottle` to slow down spammers
-- spraying the public `POST /newsletter/subscribe` endpoint.
--
-- Single key type: `ip`. We do NOT throttle by email because the spam
-- pattern is "one IP, many emails" — throttling per email lets the
-- same IP burn through fresh emails one per window. IP-only also keeps
-- the surface for legitimate users (different mailboxes from the same
-- household) from accidentally hitting the limit.
--
-- The fenêtre is sliding (default 60 minutes — see env
-- `NEWSLETTER_RATE_LIMIT_WINDOW_MINUTES`) and the cap default is 5
-- attempts (env `NEWSLETTER_RATE_LIMIT_PER_HOUR`).
CREATE TABLE `newsletter_subscribe_attempt` (
    `id`           BIGINT UNSIGNED       NOT NULL AUTO_INCREMENT,
    `key_type`     ENUM('ip')            NOT NULL,
    `key_value`    VARCHAR(255)          NOT NULL,
    `attempted_at` DATETIME              NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_key_time` (`key_type`, `key_value`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
