-- Run this once to create the member_otps table
CREATE TABLE IF NOT EXISTS `member_otps` (
  `id`         INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `profile_id` INT(11) UNSIGNED NOT NULL,
  `otp_code`   VARCHAR(6)       NOT NULL,
  `expires_at` DATETIME         NOT NULL,
  `attempts`   TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  `is_used`    TINYINT(1)       NOT NULL DEFAULT 0,
  `created_at` DATETIME         DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_profile_id` (`profile_id`),
  KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;