/*
 TechStackBase – Email Verification Tokens
 Version: v1.0.0
 Purpose: Secure email verification flow

 DESIGN PRINCIPLES
 -----------------
 • Token hashes only (never store raw tokens)
 • Expiry enforced
 • One active verification per user
 • Enumeration-safe
 • No hard dependency on email uniqueness timing
*/

/* ============================================================
   EMAIL VERIFICATIONS TABLE
   ============================================================ */

CREATE TABLE email_verifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    /* Ownership */
    user_id BIGINT UNSIGNED NOT NULL,

    /* Security */
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,

    /* Metadata */
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    /* Indexes */
    UNIQUE KEY uq_email_verification_user (user_id),
    UNIQUE KEY uq_email_verification_token (token_hash),
    INDEX idx_email_verification_expiry (expires_at),

    /* Foreign key (soft integrity) */
    CONSTRAINT fk_email_verification_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB
  ROW_FORMAT=DYNAMIC
  COMMENT='Email verification tokens (hashed)';
