CREATE TABLE IF NOT EXISTS otp_codes (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    identifier  VARCHAR(255)    NOT NULL,
    code_hash   VARCHAR(255)    NOT NULL,
    type        ENUM('phone','email') NOT NULL,
    purpose     ENUM('register','login','reset','verify') NOT NULL,
    attempts    TINYINT UNSIGNED DEFAULT 0,
    expires_at  DATETIME        NOT NULL,
    used_at     DATETIME        DEFAULT NULL,
    created_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_identifier (identifier, type, purpose),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;