-- Newsletter mailing-list subscribers — visitors AND signed-in Hydrogen
-- users land in the same table. This is the single source of truth for
-- the "do we send the newsletter to this address" question (the legacy
-- per-user `UserSetting.newsletter` toggle has been retired).
--
-- Flow (double opt-in, RGPD):
--   1. POST /newsletter/subscribe → INSERT status='pending', issue a
--      `confirm_token` (32-byte CSPRNG, only SHA-256 hash stored), send
--      the confirmation email.
--   2. GET /newsletter/confirm?token=… → flip status='confirmed', clear
--      `confirm_token_hash`, issue a long-lived `unsubscribe_token`
--      embedded in every future mail.
--   3. GET /newsletter/unsubscribe?token=… → flip status='unsubscribed'.
--      Re-subscribing later soft-undoes (back to 'pending' + new
--      confirm_token).
--
-- `email` is UNIQUE (lowercased + trimmed at insert) so a second submit
-- on the same address upserts the existing row (issue a fresh
-- confirm_token instead of inserting a duplicate).
--
-- `user_id` is OPTIONAL and SET NULL on user delete: a former Hydrogen
-- account that opted into the newsletter keeps its opt-in even after
-- account deletion (RGPD: the user explicitly subscribed at some point;
-- if they want out they unsubscribe). Cascade would silently lose the
-- record.
--
-- IP + UA are stored as RGPD evidence of consent (date + client meta);
-- they are NOT used for matching or dedup.
CREATE TABLE `newsletter_subscriber` (
    `id`                       BINARY(16)                                       NOT NULL,
    `email`                    VARCHAR(254)                                     NOT NULL,
    `status`                   ENUM('pending','confirmed','unsubscribed')       NOT NULL DEFAULT 'pending',
    `locale`                   VARCHAR(10)                                      NULL,
    `source`                   VARCHAR(255)                                     NULL,
    `user_id`                  BINARY(16)                                       NULL,
    `ip_address`               VARBINARY(16)                                    NULL,
    `user_agent`               VARCHAR(255)                                     NULL,
    `confirm_token_hash`       BINARY(32)                                       NULL,
    `confirm_token_expires_at` DATETIME                                         NULL,
    `unsubscribe_token_hash`   BINARY(32)                                       NULL,
    `confirmed_at`             DATETIME                                         NULL,
    `unsubscribed_at`          DATETIME                                         NULL,
    `created_at`               DATETIME                                         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`               DATETIME                                         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_email` (`email`),
    -- Token-hash lookups are the hot read path (confirm + unsubscribe
    -- HTTP handlers) — index both. They're nullable but MySQL still
    -- benefits from the index on non-null lookups.
    UNIQUE KEY `uniq_confirm_token_hash`     (`confirm_token_hash`),
    UNIQUE KEY `uniq_unsubscribe_token_hash` (`unsubscribe_token_hash`),
    KEY `idx_status_created` (`status`, `created_at`),
    KEY `idx_user_id`        (`user_id`),
    CONSTRAINT `fk_newsletter_subscriber_user`
        FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
