DELIMITER //
CREATE OR REPLACE PROCEDURE zen_dynamicMenu(
    IN p_token VARCHAR(100),
    IN p_moduleid VARCHAR(10),
    IN p_ip VARCHAR(100),
    IN p_db1 VARCHAR(100),
    IN p_db2 VARCHAR(100)
)
/*
  Function   : 根據 menu 產生 amis 所需的 site.json 內容
  Description: 每一筆 menu 都預設先有一個 00 role(EveryOne)  
  Build Date : 2021/08/14
    CALL zen_dynamicMenu('89b179f7-72a0-11f0-9c5f-d8bbc15c1404','zen','https://api.gex.com.tw:8033','EIS_demo','EIS_demo');
	
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
	1    Michael    2022-05-05 微調2個判斷, 避免組出的 site menu 因逗號錯誤無法顯示 menu
    2    Michael    2022-05-21 修改 @ip 內容(配合 https 的大改版), 刪除原程式的 http:// 內容
	3    Michael    2023-02-01 為了更加彈性, @ip 及 @db 這2個欄位改傳入 ${ls:xxxx}, 測試可行(程式要調整前端呼叫的程式)
	4    Michael    2023-02-04 修改 menu 的排序, 改為依 seq_no(原先是 menuid)
	5    Michael    2023-04-04 改依參數檔設定的程式版本別
	6    Michael    2023-04-22 新增 Wizard 為 zen 模組的相關判斷
*/
BEGIN
    DECLARE v_rets, v_ret_menu, v_all_menu, v_myfavor LONGTEXT;
    DECLARE v_userid VARCHAR(20);
    DECLARE v_imax1, v_imax2, v_y1, v_y2, v_x1, v_x2 INT DEFAULT 0;
    DECLARE v_pid, v_id1, v_name1, v_id2, v_name2 VARCHAR(100);
    DECLARE v_isLocalMenu, v_crlf, v_comma CHAR(1);
    DECLARE v_ver_type, v_wizard_model VARCHAR(100);
    
    -- 宣告一級選單游標
    DECLARE cur_menu00 CURSOR FOR 
        SELECT menuid, caption 
        FROM tmp_menu00 
        ORDER BY rowid;
    
    -- 宣告二級選單游標
    DECLARE cur_menu99 CURSOR FOR 
        SELECT parent_menuid, menuid, caption, isLocalMenu 
        FROM tmp_menu99 
        ORDER BY rowid;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = TRUE;
    
    -- 初始化變數
    SET v_crlf = CHAR(10);
    SET v_wizard_model = 'zen';
    
    -- 取得版本類型（預設 beta）
    SELECT COALESCE(pvalue, 'beta') INTO v_ver_type 
    FROM sys_parameter 
    WHERE systemid = 'base' AND pid = 'ver_type'
    LIMIT 1;
    
    -- 根據 token 取得使用者 ID
    SELECT userid INTO v_userid 
    FROM sys_users 
    WHERE token = p_token
    LIMIT 1;
    
    -- 建立臨時表 (取代原來的表變數)
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_menu00 (
        menuid VARCHAR(100),
        caption NVARCHAR(100),
        rowid INT AUTO_INCREMENT PRIMARY KEY
    );
    
    CREATE TEMPORARY TABLE IF NOT EXISTS tmp_menu99 (
        parent_menuid VARCHAR(100),
        menuid VARCHAR(100),
        caption NVARCHAR(100),
        isLocalMenu CHAR(1),
        rowid INT AUTO_INCREMENT PRIMARY KEY
    );
    
    TRUNCATE TABLE tmp_menu00;
    TRUNCATE TABLE tmp_menu99;
    
    -- 插入一級選單 (排序用)
    INSERT INTO tmp_menu00 (menuid, caption)
    SELECT DISTINCT m.menuid, m.caption
    FROM sys_menutable m
    WHERE m.moduleid = p_moduleid
      AND (
        EXISTS (
          SELECT 1
          FROM sys_rolemenusecurity rms
          JOIN sys_userroles ur ON ur.roleid = rms.roleid
          WHERE ur.userid = v_userid
            AND rms.controlname = 'read'
            AND rms.menuid = m.menuid
        )
        OR EXISTS (
          SELECT 1
          FROM sys_rolemenusecurity rms
          WHERE rms.roleid = '00'
            AND rms.menuid = m.menuid
        )
      )
      AND COALESCE(m.parent_menuid, '') = ''
    ORDER BY (SELECT seq_no FROM sys_menutable WHERE menuid = m.menuid);
    
    -- 插入二級選單 (排序用)
    INSERT INTO tmp_menu99 (parent_menuid, menuid, caption, isLocalMenu)
    SELECT DISTINCT m.parent_menuid, m.menuid, m.caption, COALESCE(m.isLocalMenu, 'N')
    FROM sys_menutable m
    WHERE m.moduleid = p_moduleid
      AND (
        EXISTS (
          SELECT 1
          FROM sys_rolemenusecurity rms
          JOIN sys_userroles ur ON ur.roleid = rms.roleid
          WHERE ur.userid = v_userid
            AND rms.controlname = 'read'
            AND rms.menuid = m.menuid
        )
        OR EXISTS (
          SELECT 1
          FROM sys_rolemenusecurity rms
          WHERE rms.roleid = '00'
            AND rms.menuid = m.menuid
        )
      )
      AND COALESCE(m.parent_menuid, '') <> ''
    ORDER BY m.parent_menuid, (SELECT seq_no FROM sys_menutable WHERE menuid = m.menuid);
    
    -- 取得我的最愛選單
    SET v_myfavor = udf_myfavor(p_moduleid, v_userid, v_ver_type, p_ip, p_db2);
	
    -- 初始化固定 JSON 結構
    SET v_rets = CONCAT(
        '{', v_crlf,
        '    "pages": [', v_crlf,
        '        {', v_crlf,
        '            "label": "Home",', v_crlf,
        '            "url": "/",', v_crlf,
        '            "redirect": "/', CASE WHEN p_moduleid = 'bpm' THEN 'bpm' ELSE 'sys' END, '/dashboard"', v_crlf,
        '        },', v_crlf,
        '        {', v_crlf,
        '            "children": [', v_crlf
    );
    
    -- 加入 Dashboard 項目
    IF p_moduleid <> 'bpm' THEN
        SET v_rets = CONCAT(v_rets,
            '                {', v_crlf,
            '                    "label": "Dash Board",', v_crlf,
            '                    "url": "/sys/dashboard",', v_crlf,
            '                    "icon": "fas fa-chart-pie",', v_crlf,
            '                    "schemaApi": "get:/pages/sys-dashboard.json"', v_crlf,
            '                },', v_crlf,
            '                {', v_crlf,
            '                    "label": "電子簽核主頁",', v_crlf,
            '                    "visible": false,', v_crlf,
            '                    "url": "/bpm/dashboard",', v_crlf,
            '                    "icon": "fas fa-chart-pie",', v_crlf,
            '                    "schemaApi": "get:/pages/bpm-dashboard.json"', v_crlf,
            '                }', v_crlf
        );
    ELSE
        SET v_rets = CONCAT(v_rets,
            '                {', v_crlf,
            '                    "label": "電子簽核主頁",', v_crlf,
            '                    "url": "/bpm/dashboard",', v_crlf,
            '                    "icon": "fas fa-chart-pie",', v_crlf,
            '                    "schemaApi": "get:/pages/bpm-dashboard.json"', v_crlf,
            '                },', v_crlf,
            '                {', v_crlf,
            '                    "label": "Dash Board",', v_crlf,
            '                    "visible": false,', v_crlf,
            '                    "url": "/sys/dashboard",', v_crlf,
            '                    "icon": "fas fa-chart-pie",', v_crlf,
            '                    "schemaApi": "get:/pages/sys-dashboard.json"', v_crlf,
            '                }', v_crlf
        );
    END IF;
    
    -- 加入我的最愛
    SET v_rets = CONCAT(v_rets,
        '            ]', v_crlf,
		'        },', v_crlf, 
		v_myfavor, v_crlf
		-- CASE WHEN v_myfavor IS NOT NULL AND v_myfavor <> '' THEN 
        --    CONCAT(v_crlf, ',')  -- 只有當有內容時才加逗號和內容
        -- ELSE v_crlf
		-- END
    );
    
    -- 處理一級選單
    SET v_imax1 = (SELECT COALESCE(MAX(rowid), 0) FROM tmp_menu00);
    SET v_y1 = 1;
    
    WHILE v_y1 <= v_imax1 DO
        -- 取得一級選單資料
        SELECT menuid, caption INTO v_id1, v_name1 
        FROM tmp_menu00 
        WHERE rowid = v_y1;
        
        SET v_ret_menu = CONCAT(
            '        {', v_crlf,
            '            "children": [', v_crlf,
            '                {', v_crlf,
            '                    "label": "', v_name1, '",', v_crlf,
            '                    "url": "/', p_moduleid, '",', v_crlf,
            '                    "icon": "fa fa-cube",', v_crlf,
            '                    "children": [', v_crlf
        );
        
        -- 處理二級選單
        SET v_imax2 = (SELECT COALESCE(MAX(rowid), 0) FROM tmp_menu99 WHERE parent_menuid = v_id1);
        SET v_y2 = (SELECT COALESCE(MIN(rowid), 0) FROM tmp_menu99 WHERE parent_menuid = v_id1);
        
        WHILE v_y2 <= v_imax2 DO
            -- 取得二級選單資料
            SELECT menuid, caption, isLocalMenu INTO v_id2, v_name2, v_isLocalMenu
            FROM tmp_menu99 
            WHERE rowid = v_y2 
              AND parent_menuid = v_id1;
            
            -- 判斷是否為最後一項
            IF v_y2 = v_imax2 THEN
                SET v_comma = '';
            ELSE
                SET v_comma = ',';
            END IF;
            
            -- 根據是否為本地選單生成不同結構
            IF v_isLocalMenu = 'Y' THEN
                SET v_ret_menu = CONCAT(v_ret_menu,
                    '                        {', v_crlf,
                    '                            "label": "', v_name2, '",', v_crlf,
                    '                            "url": "/', p_moduleid, '/', v_id2, '",', v_crlf,
                    '                            "schemaApi": "get:/pages/', v_id2, '.json"', v_crlf,
                    '                        }', v_comma, v_crlf
                );
            ELSE
                SET v_ret_menu = CONCAT(v_ret_menu,
                    '                        {', v_crlf,
                    '                            "label": "', v_name2, '",', v_crlf,
                    '                            "url": "/', p_moduleid, '/', v_id2, '",', v_crlf,
                    '                            "schemaApi": {', v_crlf,
                    '                                "method": "post",', v_crlf,
                    '                                "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf,
                    '                                "data": {', v_crlf,
                    '                                    "para01": "', v_id2, '",', v_crlf,
                    '                                    "para02": "', v_ver_type, '",', v_crlf,
                    '                                    "para03": "', p_ip, '"', v_crlf,
                    '                                },', v_crlf,
                    '                                "dataType": "form"', v_crlf,
                    '                            }', v_crlf,
                    '                        }', v_comma, v_crlf
                );
            END IF;
            
            -- 特殊處理 Wizard 模組
            IF (v_id2 = 'zen01' OR v_id2 = 'zen11') AND p_moduleid = v_wizard_model THEN
                -- 加入四項 Wizard 相關功能
                SET v_ret_menu = CONCAT(v_ret_menu,
                    '                        {', v_crlf,
                    '                            "label": "Wizard Function",', v_crlf,
                    '                            "url": "/', v_wizard_model, '/function",', v_crlf,
                    '                            "visible": false,', v_crlf,
                    '                            "schemaApi": {', v_crlf,
                    '                                "method": "post",', v_crlf,
                    '                                "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf,
                    '                                "data": {', v_crlf,
                    '                                    "para01": "zen02",', v_crlf,
                    '                                    "para02": "', v_ver_type, '",', v_crlf,
                    '                                    "para03": "', p_ip, '"', v_crlf,
                    '                                },', v_crlf,
                    '                                "dataType": "form"', v_crlf,
                    '                            }', v_crlf,
                    '                        },', v_crlf,
                    '                        {', v_crlf,
                    '                            "label": "Wizard Fields",', v_crlf,
                    '                            "url": "/', v_wizard_model, '/fields",', v_crlf,
                    '                            "visible": false,', v_crlf,
                    '                            "schemaApi": {', v_crlf,
                    '                                "method": "post",', v_crlf,
                    '                                "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf,
                    '                                "data": {', v_crlf,
                    '                                    "para01": "zen03",', v_crlf,
                    '                                    "para02": "', v_ver_type, '",', v_crlf,
                    '                                    "para03": "', p_ip, '"', v_crlf,
                    '                                },', v_crlf,
                    '                                "dataType": "form"', v_crlf,
                    '                            }', v_crlf,
                    '                        },', v_crlf,
                    '                        {', v_crlf,
                    '                            "label": "Query Wizard Function",', v_crlf,
                    '                            "url": "/', v_wizard_model, '/query_function",', v_crlf,
                    '                            "visible": false,', v_crlf,
                    '                            "schemaApi": {', v_crlf,
                    '                                "method": "post",', v_crlf,
                    '                                "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf,
                    '                                "data": {', v_crlf,
                    '                                    "para01": "zen12",', v_crlf,
                    '                                    "para02": "', v_ver_type, '",', v_crlf,
                    '                                    "para03": "', p_ip, '"', v_crlf,
                    '                                },', v_crlf,
                    '                                "dataType": "form"', v_crlf,
                    '                            }', v_crlf,
                    '                        },', v_crlf,
                    '                        {', v_crlf,
                    '                            "label": "Query Wizard Fields",', v_crlf,
                    '                            "url": "/', v_wizard_model, '/query_fields",', v_crlf,
                    '                            "visible": false,', v_crlf,
                    '                            "schemaApi": {', v_crlf,
                    '                                "method": "post",', v_crlf,
                    '                                "url": "', p_ip, '/xapi/v2/', p_db2, '/zen_showsource/0/",', v_crlf,
                    '                                "data": {', v_crlf,
                    '                                    "para01": "zen13",', v_crlf,
                    '                                    "para02": "', v_ver_type, '",', v_crlf,
                    '                                    "para03": "', p_ip, '"', v_crlf,
                    '                                },', v_crlf,
                    '                                "dataType": "form"', v_crlf,
                    '                            }', v_crlf,
                    '                        }', v_comma, v_crlf
                );
            END IF;
            
            SET v_y2 = v_y2 + 1;
        END WHILE;
        
        -- 關閉一級選單結構
        SET v_ret_menu = CONCAT(v_ret_menu,
            '                    ]', v_crlf,
            '                }', v_crlf,
            '            ]', v_crlf,
            '        }'
        );
        
        -- 加入開發測試區（僅 Wizard 模組的最後一項）
        IF v_y1 = v_imax1 AND p_moduleid = v_wizard_model THEN
            SET v_ret_menu = CONCAT(v_ret_menu, ',', v_crlf,
				'	{', v_crlf,
				'		"children": [', v_crlf,
                '        	{', v_crlf,
                '           	"label": "開發公測區",', v_crlf,
                '            	"url": "/dev",', v_crlf,
                '            	"icon": "fab fa-dev",', v_crlf,
                -- '            	"visibleOn": "1=0",', v_crlf,
                '            	"children": [', v_crlf,
                '               	{', v_crlf,
                '                   	"label": "測試程式01",', v_crlf,
                '                    	"url": "/dev/dev01",', v_crlf,
                '                    	"schemaApi": "get:/pages/dev-test01.json"', v_crlf,
                '                	},', v_crlf,
                '                	{', v_crlf,
                '                   	"label": "測試程式02",', v_crlf,
                '                    	"url": "/dev/dev02",', v_crlf,
                '                    	"schemaApi": "get:/pages/dev-test02.json"', v_crlf,
                '                	},', v_crlf,
                '                	{', v_crlf,
                '                    	"label": "測試程式03",', v_crlf,
                '                    	"url": "/dev/dev03",', v_crlf,
                '                    	"schemaApi": "get:/pages/dev-test03.json"', v_crlf,
                '                	}', v_crlf,
                '            	]', v_crlf,
                '        	}', v_crlf,
				'		]', v_crlf,
				'	}'
            );
        END IF;
        
        -- 判斷是否為最後一項
        IF v_y1 = v_imax1 THEN
            SET v_comma = '';
        ELSE
            SET v_comma = ',';
        END IF;
        
        SET v_ret_menu = CONCAT(v_ret_menu, v_comma, v_crlf);
        SET v_all_menu = CONCAT(COALESCE(v_all_menu, ''), v_ret_menu);
        SET v_y1 = v_y1 + 1;
    END WHILE;
    
    -- 組合最終 JSON
	IF v_imax1 <= 0 THEN
		-- 移除最後的逗號，保留換行
		IF RIGHT(v_rets, 11) = '},\n\n    ]\n}' THEN
			SET v_rets = CONCAT(LEFT(v_rets, CHAR_LENGTH(v_rets) - 8), '\n\n  ]\n}');
		ELSEIF RIGHT(v_rets, 3) = '},\n' THEN
			SET v_rets = CONCAT(LEFT(v_rets, CHAR_LENGTH(v_rets) - 2), '\n    ]', v_crlf, '}');
		ELSE 
			SET v_rets = CONCAT(v_rets, '    ]', v_crlf, '}');
		END IF;
	ELSE
		SET v_rets = CONCAT(v_rets, v_all_menu, v_crlf, '    ]', v_crlf, '}');
	END IF;
	
    -- 返回結果
    SELECT 0 AS code, '選單生成成功!' AS msg;
    SELECT v_rets AS jsoncode;
    
    -- 清理臨時表
    DROP TEMPORARY TABLE IF EXISTS tmp_menu00;
    DROP TEMPORARY TABLE IF EXISTS tmp_menu99;
END //
DELIMITER ;