DELIMITER //

CREATE OR REPLACE PROCEDURE response_issue_status(
  IN p_token VARCHAR(100),
  IN p_functiontag VARCHAR(100),
  IN p_id INT,
  IN p_assign_user VARCHAR(10),
  IN p_new_status VARCHAR(100),
  IN p_ok_rate SMALLINT,
  IN p_release_readme TEXT
)
/*
  Function   : 處理問題狀態變更和指派
  Description: 根據不同的功能標籤處理不同類型的問題狀態更新
  Build Date : 2023/07/19 (MariaDB 版本)
*/
BEGIN
  DECLARE v_userid VARCHAR(10);
  DECLARE v_org_userid VARCHAR(10);
  DECLARE v_err_code SMALLINT;
  DECLARE v_seq INT;
  DECLARE v_err_msg VARCHAR(100);
  
  SET v_err_code = 0;
  SET v_err_msg = '操作成功';
  
  -- 根據token獲取userid
  SELECT userid INTO v_userid FROM sys_users WHERE token = p_token LIMIT 1;
  
  IF v_userid IS NULL OR v_userid = '' THEN
    SET v_err_code = 4;
    SET v_err_msg = '認證失敗，請重新登錄';
  ELSE
    -- 根據功能標籤處理不同類型的問題
    IF p_functiontag = 'pmsm11' THEN  -- Product Backlog Item
      -- 獲取原始負責人和當前最大序號
      SELECT controller INTO v_org_userid FROM pms_pbi WHERE pbiid = p_id;
      SELECT IFNULL(MAX(seqno), 0) INTO v_seq FROM pms_pbi_log WHERE pbiid = p_id;
      
      -- 寫入日誌
      INSERT INTO pms_pbi_log (pbiid, response_date, response_by, assign_user, response, pbi_status, pbi_rate, seqno)
      VALUES (p_id, NOW(), v_userid, p_assign_user, p_release_readme, p_new_status, p_ok_rate, v_seq+1);
      
      -- 更新主表狀態
      UPDATE pms_pbi 
      SET pbi_status = IFNULL(p_new_status, pbi_status),
          controller = IFNULL(v_userid, controller),
          pbi_rate = IFNULL(p_ok_rate, pbi_rate)
      WHERE pbiid = p_id;
      
    ELSEIF p_functiontag = 'pmsm12' THEN  -- Issue Log 維護作業
      -- 獲取當前最大序號
      SELECT IFNULL(MAX(seqno), 0) INTO v_seq FROM pms_issuelog_d WHERE issueid = p_id;
      
      -- 寫入日誌
      INSERT INTO pms_issuelog_d (issueid, raised_date, raised_by, assign_user, response, issue_status, seqno)
      VALUES (p_id, NOW(), v_userid, p_assign_user, p_release_readme, p_new_status, v_seq+1);
      
      -- 更新主表狀態
      UPDATE pms_issuelog 
      SET issue_status = IFNULL(p_new_status, issue_status),
          responsible = IFNULL(p_assign_user, responsible)
      WHERE issueid = p_id;
    END IF;
  END IF;
  
  SELECT v_err_code AS code, v_err_msg AS msg;
END //

DELIMITER ;