DELIMITER //

CREATE OR REPLACE PROCEDURE zen_create_json_prepare_ds(
  IN p_type VARCHAR(1),      -- M:Master D:Detail
  IN p_uuid VARCHAR(100),
  IN p_FunctionTag VARCHAR(100)
)
/*
  功能   : amis Wizard 產生 json UI 核心程式的暫存檔
  描述: 
  建立日期 : 2023/12/20
  
  修改歷史
    項目 誰         日期       修改說明
    ==== ========== ========== ================================================
    1    Michael    2023/12/20 根據 v1.0 重構
*/
BEGIN
  DECLARE v_imax INT;
  DECLARE v_imin INT;
  DECLARE v_imax_block INT;
  DECLARE v_y INT;
  DECLARE v_y1 INT;
  DECLARE v_y2 INT;
  DECLARE v_x INT;
  DECLARE v_x1 INT;
  DECLARE v_x2 INT;
  DECLARE v_g_max INT;
  DECLARE v_gx INT;
  DECLARE v_gy INT;
  DECLARE v_gy1 INT;
  DECLARE v_gy2 INT;

  -- 建立臨時表儲存 jsondata
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_json_data (
    fieldname VARCHAR(100),
    fieldcn VARCHAR(100),
    fieldwidth INT,
    fieldobject VARCHAR(100),
    fieldchecknull VARCHAR(10),
    picker_tag VARCHAR(100),
    fielddefault VARCHAR(200),
    fieldro VARCHAR(1),
    fieldfm VARCHAR(20),
    fieldblock SMALLINT,
    fieldds VARCHAR(10),
    fieldsh VARCHAR(10),
    cellno SMALLINT,
    cellx SMALLINT,
    celly SMALLINT,
    cellline SMALLINT,
    iscolumnmatch CHAR(1),
    visibleon VARCHAR(100),
    _group VARCHAR(10),
    _block VARCHAR(10),
    rowid INT AUTO_INCREMENT PRIMARY KEY
  );

  TRUNCATE TABLE tmp_json_data;
  
  -- 插入基本資料
  INSERT INTO tmp_json_data (
    fieldname, fieldcn, fieldwidth, fieldobject, fieldchecknull, picker_tag,
    fielddefault, fieldro, fieldfm,
    fieldblock, fieldds, fieldsh, cellno, cellx, celly, cellline, iscolumnmatch, visibleon
  )
  SELECT 
    fieldname, fieldcn, fieldwidth, fieldobject, fieldchecknull, picker_tag,
    fielddefault, fieldro, fieldfm,
    fieldblock, fieldds, fieldsh, cellno, cellx, celly, cellline, IFNULL(iscolumnmatch,'N'), visibleon
  FROM zen_fields 
  WHERE functiontag = p_FunctionTag 
    AND ((p_type = 'M' AND fieldblock < 90) OR (p_type = 'D' AND fieldblock >= 90))
  ORDER BY fieldblock, celly, cellx;
  
  -- 計算 _block (body-panels or tabs) 排列
  SELECT MAX(fieldblock) INTO v_imax FROM tmp_json_data;
  SELECT MIN(fieldblock) INTO v_imin FROM tmp_json_data;
  SET v_y = 0;
  SET v_imax_block = v_imax;
  
  WHILE v_y <= v_imax DO
    SELECT COUNT(*) INTO v_x FROM tmp_json_data WHERE fieldblock = v_y AND fieldsh = 'Y';
    
    IF v_x > 1 AND v_imax > v_imin THEN
      -- 找出區塊的開始和結束位置
      SELECT MIN(celly) INTO v_y1 FROM tmp_json_data WHERE fieldblock = v_y AND fieldsh = 'Y';
      SELECT MIN(cellx) INTO v_x1 FROM tmp_json_data WHERE fieldblock = v_y AND celly = v_y1 AND fieldsh = 'Y';
      SELECT rowid INTO v_y1 FROM tmp_json_data WHERE fieldblock = v_y AND celly = v_y1 AND cellx=v_x1 AND fieldsh = 'Y';
      
      SELECT MAX(celly) INTO v_y2 FROM tmp_json_data WHERE fieldblock = v_y AND fieldsh = 'Y';
      SELECT MAX(cellx) INTO v_x2 FROM tmp_json_data WHERE fieldblock = v_y AND celly = v_y2 AND fieldsh = 'Y';
      SELECT rowid INTO v_y2 FROM tmp_json_data WHERE fieldblock = v_y AND celly = v_y2 AND cellx=v_x2 AND fieldsh = 'Y';
    
      -- 更新區塊標記
      UPDATE tmp_json_data SET _block='BH' WHERE rowid=v_y1;
      UPDATE tmp_json_data SET _block='BF' WHERE rowid=v_y2;
    END IF;

    -- 針對 Image 特別處理(BA  -- Header/Footer All)
    SELECT COUNT(*) INTO v_x FROM tmp_json_data WHERE fieldblock = v_y AND fieldobject = 'input-image' AND fieldsh = 'Y';
    IF v_x = 1 THEN
      UPDATE tmp_json_data SET _block='BA' WHERE fieldblock = v_y AND fieldsh = 'Y';
    END IF;
    
    -- 計算 _group (body-group) 排列    
    SELECT MAX(celly) INTO v_g_max FROM tmp_json_data WHERE fieldblock = v_y AND fieldsh = 'Y';
    SET v_gy = 1;
    
    WHILE v_gy <= v_g_max DO
      SELECT COUNT(*) INTO v_gx FROM tmp_json_data WHERE fieldblock = v_y AND celly = v_gy AND fieldsh = 'Y';
      
      IF v_gx > 1 THEN
        -- 找出群組的開始和結束位置
        SELECT MIN(cellx) INTO v_gy1 FROM tmp_json_data WHERE fieldblock = v_y AND celly = v_gy AND fieldsh = 'Y';
        SELECT MIN(rowid) INTO v_gy1 FROM tmp_json_data WHERE fieldblock = v_y AND celly = v_gy AND cellx=v_gy1 AND fieldsh = 'Y';
        
        SELECT MAX(cellx) INTO v_gy2 FROM tmp_json_data WHERE fieldblock = v_y AND celly = v_gy AND fieldsh = 'Y';
        SELECT MAX(rowid) INTO v_gy2 FROM tmp_json_data WHERE fieldblock = v_y AND celly = v_gy AND cellx=v_gy2 AND fieldsh = 'Y';
      
        -- 更新群組標記
        UPDATE tmp_json_data SET _group='GH' WHERE rowid=v_gy1;
        UPDATE tmp_json_data SET _group='GF' WHERE rowid=v_gy2;
      END IF;
    
      SET v_gy = v_gy + 1;
    END WHILE;
    
    SET v_y = v_y + 1;
  END WHILE;
  
  -- 寫入暫存檔
  INSERT INTO zen_wizard_jsondata (
    uuid, fieldname, fieldcn, fieldwidth, fieldobject, fieldchecknull, picker_tag,
    fielddefault, fieldro, fieldfm, fieldblock, fieldds, fieldsh, cellno, cellx, celly, cellline,
    iscolumnmatch, visibleon, _group, _block, json_bh, json_gh, json_insert, json_update,
    json_quickedit, json_gridview, json_view, json_gf, json_bf, create_date
  )
  SELECT 
    p_uuid, fieldname, fieldcn, fieldwidth, fieldobject, fieldchecknull, picker_tag,
    fielddefault, fieldro, fieldfm, fieldblock, fieldds, fieldsh, cellno, cellx, celly, cellline,
    iscolumnmatch, visibleon, _group, _block, '', '', '', '', '', '', '', '', '', NOW()
  FROM tmp_json_data;
  
  DROP TEMPORARY TABLE IF EXISTS tmp_json_data;
END //

DELIMITER ;