-- ==========================================================
-- Table: otp_verifications
-- Menyimpan kode OTP untuk verifikasi register / login / reset pin
-- ==========================================================

USE `resellink_account_app`;

CREATE TABLE IF NOT EXISTS `otp_verifications` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     VARCHAR(20)  NOT NULL,
  `otp_hash`    VARCHAR(255) NOT NULL,
  `purpose`     ENUM('register','login','reset_pin') NOT NULL DEFAULT 'register',
  `method`      ENUM('email','sms') NOT NULL DEFAULT 'email',
  `attempts`    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `is_used`     TINYINT(1) NOT NULL DEFAULT 0,
  `expired_at`  DATETIME NOT NULL,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  KEY `idx_user_purpose` (`user_id`, `purpose`),
  KEY `idx_expired_at`   (`expired_at`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;