-- Self-service email change (double opt-in) — `PATCH /api/users/me/profile`.
--
-- A change to the account email is NEVER applied immediately: the new address
-- is parked here with a single-use, time-boxed token. The swap onto `user.email`
-- happens only when the owner clicks the confirmation link sent to the NEW
-- address (`POST /api/users/me/email/confirm`), proving they control it.
--
-- Same token convention as `email_confirmation`:
--   - 32 bytes CSPRNG, transported base64url, only the SHA-256 hash stored.
-- A fresh request invalidates the previous pending row for the user (used_at
-- stamped), so at most one live change is outstanding per account.

CREATE TABLE `email_change_request` (
    `id`         BINARY(16)   NOT NULL,
    `user_id`    BINARY(16)   NOT NULL,
    `new_email`  VARCHAR(255) NOT NULL,
    `token_hash` BINARY(32)   NOT NULL,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME     NOT NULL,
    `used_at`    DATETIME     NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_ecr_token_hash` (`token_hash`),
    KEY `idx_ecr_user_id` (`user_id`),
    KEY `idx_ecr_expires_at` (`expires_at`),
    CONSTRAINT `fk_ecr_user`
        FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
