DELIMITER //

CREATE OR REPLACE PROCEDURE `zen_fieldspec_best` (
  IN p_functiontag VARCHAR(100)
)
/*
  Function   : 針對 zen_field 的內容, 根據 schema 給出最接近的預設值, 以簡化設定動作
  Description: 
  Build Date : 2021/08/14
  Call Sample: 
    CALL zen_fieldspec_best('abc01');
    
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    1    Michael    2021-11-25 新增 fieldblock = 90 的判斷(detail)
    2    Michael    2022-04-16 update zen_fields set fieldro = Y (Read Only 預設)
    3    Michael    2022-10-02 加入對 Query 的支援(根據 tag 判斷)
    4    Michael    2023-02-09 加入 flow_status 預設值=N
    5    Michael    2023-09-01 加入 2個系統欄位的預設值 create_user/create_date
    6    [改寫者]    2023-11-15 改寫為 MariaDB 版本並最佳化
*/
BEGIN
  /*
  根據下列內容設定
  fieldobject 更換為最合理的物件
  fieldcn ==> dd ? (date=日期, id=代號, Name=名稱, 描述 取已經存在的同名? 建立一個常用習慣的詞庫? 這可以和 dd 並存)
  fieldwidth 根據 length 推算 width(Grid 顯示寬度)
  --fielddefault 日期=today 等
  --fieldchecknull
  --fieldds,fieldsh
  cellx,celly
  */
  
  DECLARE v_imax INT;
  DECLARE v_inow INT;
  DECLARE v_cnt INT;
  DECLARE v_x INT;
  DECLARE v_y INT;
  DECLARE v_row INT;
  DECLARE v_line INT;
  DECLARE v_block INT;
  DECLARE v_tablename VARCHAR(100);
  DECLARE v_fieldname VARCHAR(100);
  DECLARE v_fieldcn VARCHAR(100);
  DECLARE v_fld_type VARCHAR(100);
  DECLARE v_fieldobject VARCHAR(100);
  DECLARE v_fieldlength INT;
  DECLARE v_fld_width VARCHAR(10);
  DECLARE v_jump SMALLINT;
  --
  DECLARE v_report_type VARCHAR(100);
  DECLARE v_type_f SMALLINT;
  DECLARE v_type_q SMALLINT;
  
  -- 創建臨時表
  DROP TEMPORARY TABLE IF EXISTS tmp_fields;
  DROP TEMPORARY TABLE IF EXISTS tmp_query_fields;
  
  -- upgrade data(防錯)
  UPDATE zen_fields SET datatype = fieldobject WHERE functiontag = p_functiontag AND datatype IS NULL;
  
  /*
    根據 p_functiontag 識別是 Form or Query
  */
  SELECT COUNT(*) INTO v_type_f FROM zen_fields WHERE functiontag = p_functiontag;
  SELECT COUNT(*) INTO v_type_q FROM zen_report_fields WHERE functiontag = p_functiontag;
  
  IF v_type_f > 0 THEN
    -- 創建臨時表並插入數據
    CREATE TEMPORARY TABLE tmp_fields AS
    SELECT * FROM zen_fields WHERE functiontag = p_functiontag;
    
    SELECT MAX(fieldid), MIN(fieldid), COUNT(*) 
    INTO v_imax, v_y, v_cnt
    FROM tmp_fields;
      
    SET v_row = 1;
    IF v_cnt > 10 AND v_cnt <= 30 THEN
      SET v_row = 2;
    ELSEIF v_cnt > 30 THEN
      SET v_row = 2;
    END IF;
      
    SET v_x = 1;
    SET v_line = 1;
    
    WHILE v_y <= v_imax DO
      SELECT tablename, fieldname, fieldcn, datatype, fieldlength, fieldblock 
      INTO v_tablename, v_fieldname, v_fieldcn, v_fieldobject, v_fieldlength, v_block
      FROM tmp_fields WHERE fieldid = v_y;
      
      /* step 1: 設定 object */
      -- varchar, char, nvarchar ...
      SET v_fld_type = v_fieldobject;
      IF LOCATE('char', v_fieldobject) > 0 THEN
        IF v_fieldlength >= 200 THEN
          SET v_fld_type = 'textarea';
        ELSEIF v_fieldlength = 1 THEN
          SET v_fld_type = 'switch';
        ELSE 
          SET v_fld_type = 'input-text';
        END IF;
      END IF;
      
      IF LOCATE('int', v_fieldobject) > 0 OR LOCATE('decimal', v_fieldobject) > 0 THEN
        SET v_fld_type = 'input-number';
      END IF;
      
      IF LOCATE('date', v_fieldobject) > 0 THEN
        SET v_fld_type = 'input-date';
      END IF;
      
      -- 防錯
      IF v_fld_type = '' THEN
        SET v_fld_type = 'input-text';
      END IF;
        
      /*
        step 2: 推估 width(暫時保留, 但在 amis 中, width 已經幾乎不必設定)
      */
      IF v_fld_type = 'input-date' OR v_fld_type = 'input-number' THEN
        SET v_fld_width = 80;
      ELSE
        IF v_fieldlength <= 10 THEN
          SET v_fld_width = 80;
        ELSEIF v_fieldlength <= 20 THEN
          SET v_fld_width = 120;
        ELSE 
          SET v_fld_width = 200;
        END IF;
      END IF;
      
      -- 判斷如果是 detail, fieldblock = 90
      SELECT COUNT(*) INTO v_cnt FROM zen_function WHERE detail_table = v_tablename;
      IF v_cnt > 0 THEN
        SET v_block = 90;
      END IF;
      
      /*
        step3: 推算 x/y
      */
      SET v_jump = 0;
      IF v_row = 2 AND v_fieldlength >= 50 THEN
        IF v_x = 1 THEN
          SET v_jump = 1;
        ELSE
          SET v_x = 1;
          SET v_line = v_line + 1;
        END IF;
      END IF;
      
      IF v_row = 3 AND v_fieldlength >= 50 THEN
        IF v_x = 1 THEN
          SET v_jump = 1;
        ELSE
          SET v_x = 1;
          SET v_line = v_line + 1;
        END IF;
      END IF;
      
      /*
        step 4: update & cname(dd)
      */
      UPDATE zen_fields 
      SET fieldobject = v_fld_type, 
          fieldwidth = v_fld_width, 
          cellx = v_x, 
          celly = v_line, 
          fieldblock = v_block,
          fieldcn = trans_cname(v_fieldname, v_fieldcn)
      WHERE fieldid = v_y;
      
      IF v_row = 1 THEN
        SET v_line = v_line + 1;
      ELSEIF v_row = 2 THEN
        SET v_x = v_x + 1;
        IF v_x > 2 OR (v_jump = 1) THEN
          SET v_x = 1;
          SET v_line = v_line + 1;
        END IF;
      ELSEIF v_row = 3 THEN
        SET v_x = v_x + 1;
        IF v_x > 3 OR (v_jump = 1) THEN
          SET v_x = 1;
          SET v_line = v_line + 1;
        END IF;
      END IF;
      
      SELECT MIN(fieldid) INTO v_y FROM tmp_fields WHERE fieldid > v_y;
    END WHILE;
    
    /*
      批次更新(不需逐筆寫入)
      step 5: setup read only(create_user ..)
      step 6: flow_status 的預設值為 N
    */
    UPDATE zen_fields 
    SET fieldro = 'Y' 
    WHERE functiontag = p_functiontag 
      AND fieldname IN ('create_user','create_date','update_user','update_date') 
      AND IFNULL(fieldro, '') = '';
    
    UPDATE zen_fields 
    SET fielddefault = '$ls:userid' 
    WHERE functiontag = p_functiontag 
      AND fieldname = 'create_user' 
      AND IFNULL(fielddefault, '') = '';
    
    UPDATE zen_fields 
    SET fielddefault = 'today' 
    WHERE functiontag = p_functiontag 
      AND fieldname = 'create_date' 
      AND IFNULL(fielddefault, '') = '';
    
    UPDATE zen_fields 
    SET fielddefault = '自動編碼' 
    WHERE functiontag = p_functiontag 
      AND fieldname = 'billno' 
      AND IFNULL(fielddefault, '') = '';
    
    UPDATE zen_fields 
    SET fielddefault = 'today' 
    WHERE functiontag = p_functiontag 
      AND fieldname = 'billdate' 
      AND IFNULL(fielddefault, '') = '';
    
    UPDATE zen_fields 
    SET fielddefault = 'N' 
    WHERE functiontag = p_functiontag 
      AND fieldname = 'flow_status' 
      AND IFNULL(fielddefault, '') = '';
    
    -- hidden 的 ds 和 sh 都應該設為 Y ,20230926
    UPDATE zen_fields 
    SET fieldds = 'Y', fieldsh = 'Y' 
    WHERE functiontag = p_functiontag 
      AND fieldobject = 'hidden';
      
    SELECT 0 AS code, 'OK' AS msg;
    
  ELSEIF v_type_q > 0 THEN
    -- 處理查詢字段
    CREATE TEMPORARY TABLE tmp_query_fields AS
    SELECT * FROM zen_report_fields WHERE functiontag = p_functiontag;
    
    SELECT MAX(fieldid), MIN(fieldid) 
    INTO v_imax, v_y
    FROM tmp_query_fields;
    
    SELECT COUNT(*) INTO v_cnt FROM tmp_query_fields;
      
    SET v_x = 1;
    SET v_line = 1;
    
    WHILE v_y <= v_imax DO
      SELECT fieldname, fieldcn, fieldobject, report_type
      INTO v_fieldname, v_fieldcn, v_fieldobject, v_report_type
      FROM tmp_query_fields WHERE fieldid = v_y;
      
      /*
        step 1: update cname(dd)
      */
      UPDATE zen_report_fields 
      SET fieldcn = trans_cname(v_fieldname, v_fieldcn)
      WHERE fieldid = v_y AND fieldname = fieldcn;
      
      /*
        step 2: setup display
      */
      UPDATE zen_report_fields 
      SET fieldds = 'Y' 
      WHERE fieldid = v_y;
      
      SELECT MIN(fieldid) INTO v_y FROM tmp_query_fields WHERE fieldid > v_y;
    END WHILE;
    
    SELECT 0 AS code, 'OK' AS msg;
  END IF;
  
  -- 清理臨時表
  DROP TEMPORARY TABLE IF EXISTS tmp_fields;
  DROP TEMPORARY TABLE IF EXISTS tmp_query_fields;
END //

DELIMITER ;