-- Sets a user's password hash/salt directly by email.
-- NOTE: MySQL has no BCrypt function. p_password_hash/p_password_salt must be
-- produced the same way the app does (PasswordService: BCrypt.hashpw(password + salt, ...)
-- with a fresh random salt) BEFORE calling this procedure — it does not hash anything itself.
-- Clears any pending OTP so a stale reset token can't be replayed afterward.

DROP PROCEDURE IF EXISTS sp_set_user_password;

DELIMITER $$

CREATE PROCEDURE sp_set_user_password(
    IN p_email VARCHAR(255),
    IN p_password_hash VARCHAR(512),
    IN p_password_salt VARCHAR(255)
)
BEGIN
    UPDATE users
    SET password_hash = p_password_hash,
        password_salt = p_password_salt,
        otp_code = NULL,
        otp_expires_at = NULL,
        updated_at = CURRENT_TIMESTAMP(6)
    WHERE email = p_email
      AND is_deleted = 0;
END$$

DELIMITER ;