DELIMITER //

CREATE PROCEDURE resetpwd (
  IN p_token VARCHAR(100),
  IN p_orgpwd VARCHAR(200),
  IN p_newpwd VARCHAR(200),
  IN p_newpwd2 VARCHAR(200)
)
/*
  Function   : 重置密碼
  Description: 修改自 sp=regmember
  Build Date : 2023-12-10
  
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    1    Michael    2023-12-10 改寫為 MySQL 版
    
*/
BEGIN
  DECLARE v_userid VARCHAR(100);
  DECLARE v_err_code SMALLINT;
  DECLARE v_err_msg NVARCHAR(100);
  
  IF p_newpwd <> p_newpwd2 THEN
    SET v_err_code = 2;
    SET v_err_msg = '新密碼輸入錯誤, 請重新設定';
  ELSE
    -- 根據傳入的 token 取得 userid
    SELECT userid INTO v_userid FROM sys_users WHERE token = p_token AND pwd = p_orgpwd;
  
    SET v_err_code = 0;
    SET v_err_msg = 'reset pwd ok';
    
    IF COALESCE(v_userid, '') <> '' THEN
      UPDATE sys_users SET pwd = p_newpwd WHERE userid = v_userid;
    ELSE
      SET v_err_code = 4;
      SET v_err_msg = '原密碼輸入錯誤, 請重新輸入';
    END IF;
  END IF;
  
  -- 回傳 ds1
  SELECT v_err_code AS code, v_err_msg AS msg;
  
END //

DELIMITER ;
