/*
 TechStackBase – Password Reset Tokens
 Version: v1.0.0
 Purpose: Secure password reset workflow

 DESIGN PRINCIPLES
 -----------------
 • Hash-only tokens (never store raw tokens)
 • Expiry enforced
 • One active reset token per user
 • Enumeration-safe
 • Safe cleanup & rotation
*/

/* ============================================================
   PASSWORD RESETS TABLE
   ============================================================ */

CREATE TABLE password_resets (
    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_password_reset_user (user_id),
    UNIQUE KEY uq_password_reset_token (token_hash),
    INDEX idx_password_reset_expiry (expires_at),

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