DELIMITER //

CREATE OR REPLACE PROCEDURE orm_api_kernel_v2(
  IN p_tablename VARCHAR(100),
  IN p_pk VARCHAR(100),         -- primary key(for update delete)
  IN p_action VARCHAR(20),      -- CRUD
  IN p_uuid VARCHAR(100),
  IN p_json_id INT
)
/*
  Function   : 通用 orm-api 根据传入参数及设定，智能生成相应SQL
  Description: 模仿 ORM 设定，让前端程序只需传入参数就能执行增删查改基本功能
  Build Date : 2024-03-20
  
  call orm_api_kernel_v2('pms_sprint','sprintid','U','uuid-20250803_2',0);
  
  Modify History
    Item Who        Date       Modify Docs
    ==== ========== ========== ================================================
    1    Developer 2024/03/20 从 MS-SQL v2 版本转换为 MariaDB 版本并优化
*/
BEGIN
  DECLARE v_field_identity VARCHAR(100);
  DECLARE v_fields TEXT;
  DECLARE v_datas TEXT;
  DECLARE v_updatedatas TEXT;
  DECLARE v_pkwhere TEXT;
  DECLARE v_sql LONGTEXT;
  DECLARE v_pk_cnt INT;
  DECLARE v_pk_no INT;
  DECLARE v_max_no VARCHAR(20);
  DECLARE v_bill_uuid VARCHAR(100);
  DECLARE v_bill_head VARCHAR(20);
  DECLARE v_middlefield VARCHAR(100);
  DECLARE v_dest_table VARCHAR(100);
  DECLARE v_pk_id VARCHAR(50);
  DECLARE v_fieldname VARCHAR(100);
  DECLARE v_contents LONGTEXT;
  DECLARE v_fieldtype VARCHAR(20);
  DECLARE v_quot CHAR(1);
  DECLARE v_cnt INT;
  DECLARE v_cnt2 INT;
  DECLARE v_maxcount INT;
  DECLARE v_currid INT;
  DECLARE v_nextrowid INT;
  DECLARE v_crlf VARCHAR(2);
  
  -- 创建临时表存储JSON数据
  CREATE TEMPORARY TABLE IF NOT EXISTS temp_jsondata (
    parent_id INT,
    json_key VARCHAR(100),
    json_value LONGTEXT,
    ValueType VARCHAR(20),
    rowid INT AUTO_INCREMENT PRIMARY KEY
  );
  
  -- 临时表用于同步删除
  CREATE TEMPORARY TABLE IF NOT EXISTS temp_sync_del (
    dest_table VARCHAR(100),
    pk_id VARCHAR(50),
    rowid INT AUTO_INCREMENT PRIMARY KEY
  );
  
  -- 临时表用于避免重复字段名
  CREATE TEMPORARY TABLE IF NOT EXISTS temp_comp_json (
    fieldname VARCHAR(100)
  );
  
  -- 清空临时表
  TRUNCATE TABLE temp_jsondata;
  TRUNCATE TABLE temp_sync_del;
  TRUNCATE TABLE temp_comp_json;
  
  -- 将传入参数转为JSON数据(多筆 Grid 資料寫入)
  IF p_json_id = 1 then
    INSERT INTO temp_jsondata (parent_id, json_key, json_value, ValueType)
    SELECT parent_id, json_key, json_value, ValueType 
    FROM orm_json_tmp 
    WHERE uuid = p_uuid;
  else 
    INSERT INTO temp_jsondata (parent_id, json_key, json_value, ValueType)
    SELECT parent_id, json_key, json_value, ValueType 
    FROM orm_json_tmp 
    WHERE uuid = p_uuid AND parent_id = FLOOR(p_json_id / 1000) and object_id = p_json_id - FLOOR(p_json_id / 1000)*1000;
  end if;
  
  -- 获取自增字段
  SELECT COLUMN_NAME INTO v_field_identity
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = p_tablename
    AND EXTRA = 'auto_increment'
  LIMIT 1;
  
  -- 初始化变量
  SET v_fields = '';
  SET v_datas = '';
  SET v_pkwhere = '';
  SET v_updatedatas = '';
  SET v_pk_cnt = 0;
  SET v_bill_uuid = '';
  SET v_bill_head = '';
  SET v_crlf = CONCAT(CHAR(13), CHAR(10));
  
  -- 获取记录数
  SELECT COUNT(*) INTO v_maxcount FROM temp_jsondata;
  SELECT MIN(rowid) INTO v_currid FROM temp_jsondata;
  
  -- 遍历JSON数据构建SQL
  WHILE v_currid <= v_maxcount DO
    -- 获取当前记录
    SELECT json_key, IFNULL(json_value, ''), ValueType 
    INTO v_fieldname, v_contents, v_fieldtype
    FROM temp_jsondata 
    WHERE rowid = v_currid;
    
    -- 设置引号
    IF lower(v_fieldtype) = 'string' THEN
      SET v_quot = '''';
    ELSE 
      SET v_quot = '';
    END IF;
    
    -- 记录billno和uuid用于MD更新
    IF v_fieldname = 'uuid' THEN
      SET v_bill_uuid = v_contents;
    END IF;
    IF v_fieldname = 'billno' THEN
      SET v_bill_head = v_contents;
    END IF;
    
    -- 检查字段是否存在
    SELECT COUNT(*) INTO v_cnt 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = p_tablename AND COLUMN_NAME = v_fieldname;
    
    SELECT COUNT(*) INTO v_cnt2 
    FROM temp_comp_json 
    WHERE fieldname = v_fieldname;
    
    -- 如果字段存在且未处理过
    IF v_cnt > 0 AND v_cnt2 <= 0 THEN
      -- 记录主键内容
      SELECT COUNT(*) INTO v_pk_no 
      FROM (
        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(p_pk, ',', numbers.n), ',', -1) AS col
        FROM (
          SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
          SELECT 4 UNION ALL SELECT 5 -- 假设最多5个主键字段
        ) numbers
        WHERE numbers.n <= LENGTH(p_pk) - LENGTH(REPLACE(p_pk, ',', '')) + 1
      ) AS split_pk
      WHERE col = v_fieldname;
      
      IF v_pk_no > 0 THEN
        IF v_pk_cnt >= 1 THEN
          UPDATE sys_transaction SET para02 = v_contents WHERE tablename = p_tablename;
        ELSE 
          UPDATE sys_transaction SET para01 = v_contents WHERE tablename = p_tablename;
        END IF;
        SET v_pk_cnt = v_pk_cnt + 1;
      END IF;
      
      -- 非自增字段处理
      IF IFNULL(v_field_identity, '') <> v_fieldname THEN
        SET v_fields = CONCAT(v_fields, v_fieldname, ',');
        
        -- 构建INSERT值
        IF p_action = 'C' OR p_action = 'Insert' THEN
          SET v_datas = CONCAT(v_datas, v_quot, v_contents, v_quot, ',');
        END IF;
      END IF;
      
      -- 构建UPDATE语句
      IF (p_action = 'U' OR p_action = 'Update') AND v_pk_no = 0 THEN
        -- 处理日期格式
        IF SUBSTRING(v_contents, 11, 19) = 'T08:00:00.000+08:00' THEN
          SET v_contents = SUBSTRING(v_contents, 1, 10);
        END IF;
        
        SET v_updatedatas = CONCAT(v_updatedatas, v_fieldname, ' = ', v_quot, v_contents, v_quot, ',');
      END IF;
      
      -- 构建WHERE条件
      IF v_pk_no > 0 THEN
        SET v_pkwhere = CONCAT(v_pkwhere, v_fieldname, ' = ', v_quot, v_contents, v_quot, ' AND ');
      END IF;
    END IF;
    
    -- 记录已处理字段
    INSERT INTO temp_comp_json VALUES (v_fieldname);
    
    -- 获取下一行
    SELECT MIN(rowid) INTO v_nextrowid 
    FROM temp_jsondata 
    WHERE rowid > v_currid;
    SET v_currid = v_nextrowid;
  END WHILE;
  
  -- 处理没有主键的情况
  IF v_pk_cnt = 0 THEN
    SELECT middlefield INTO v_middlefield 
    FROM sys_transaction 
    WHERE tablename = p_tablename 
    LIMIT 1;
    
    SELECT json_value INTO v_contents 
    FROM temp_jsondata 
    WHERE json_key = v_middlefield 
    LIMIT 1;
    
    IF LENGTH(v_contents) <= 1000 THEN
      UPDATE sys_transaction SET para01 = v_contents WHERE tablename = p_tablename;
    END IF;
  END IF;
  
  -- 处理自增主键情况
  IF (p_action = 'C' OR p_action = 'Insert') AND (p_pk = v_field_identity) THEN
    UPDATE sys_transaction SET para01 = 'identity' WHERE tablename = p_tablename;
  END IF;
  
  -- 移除末尾的逗号或AND
  IF v_fields <> '' THEN
    -- SET v_fields = SUBSTRING(v_fields, 1, LENGTH(v_fields) - 1);
	SET v_fields = TRIM(TRAILING ',' FROM v_fields);
  END IF;
  
  IF v_datas <> '' THEN
    -- SET v_datas = SUBSTRING(v_datas, 1, LENGTH(v_datas) - 1);
	SET v_datas = TRIM(TRAILING ',' FROM v_datas);
  END IF;
  
  IF v_updatedatas <> '' THEN
    -- SET v_updatedatas = SUBSTRING(v_updatedatas, 1, LENGTH(v_updatedatas) - 1);
	SET v_updatedatas = TRIM(TRAILING ',' FROM v_updatedatas);
  END IF;
  
  IF v_pkwhere <> '' THEN
    SET v_pkwhere = SUBSTRING(v_pkwhere, 1, LENGTH(v_pkwhere) - 5);
  END IF;
  
  -- 构建基础SQL
  IF p_action = 'C' OR p_action = 'Insert' THEN
    SET v_sql = CONCAT('INSERT INTO ', p_tablename, ' (', v_fields, ') VALUES (', v_datas, ')');
    
    -- 加入 M-D 的 D 同步 billno update
    INSERT INTO temp_sync_del (dest_table, pk_id)
    SELECT dest_table, pk_id FROM sys_sync_delete_detail WHERE tablename = p_tablename;
    
    -- 处理同步更新
    SELECT COUNT(*) INTO v_maxcount FROM temp_sync_del;
    SELECT MIN(rowid) INTO v_currid FROM temp_sync_del;
    
    WHILE v_currid <= v_maxcount DO
      SELECT dest_table, pk_id INTO v_dest_table, v_pk_id 
      FROM temp_sync_del 
      WHERE rowid = v_currid;
      
      -- 检查目标表是否有uuid字段
      SELECT COUNT(*) INTO v_cnt 
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_NAME = v_dest_table AND COLUMN_NAME = 'uuid';
      
      IF v_cnt > 0 THEN
        SET v_sql = CONCAT(v_sql, ';\n',
          'UPDATE ', v_dest_table, ' SET ', IFNULL(v_pk_id, 'billno'), ' = ''', 
          v_bill_head, ''' WHERE uuid = ''', v_bill_uuid, '''');
      END IF;
      
      -- 获取下一行
      SELECT MIN(rowid) INTO v_nextrowid 
      FROM temp_sync_del 
      WHERE rowid > v_currid;
      SET v_currid = v_nextrowid;
    END WHILE;
    
  ELSEIF p_action = 'U' OR p_action = 'Update' THEN
    SET v_sql = CONCAT('UPDATE ', p_tablename, ' SET ', v_updatedatas, ' WHERE ', v_pkwhere);
    
  ELSEIF p_action = 'D' OR p_action = 'Delete' THEN
    -- 基础删除语句
    SET v_sql = CONCAT('DELETE FROM ', p_tablename, ' WHERE ', v_pkwhere);
    
    -- 处理同步删除
    INSERT INTO temp_sync_del (dest_table, pk_id)
    SELECT dest_table, pk_id FROM sys_sync_delete_detail WHERE tablename = p_tablename;
    
    SELECT COUNT(*) INTO v_maxcount FROM temp_sync_del;
    SELECT MIN(rowid) INTO v_currid FROM temp_sync_del;
    
    WHILE v_currid <= v_maxcount DO
      SELECT dest_table, pk_id INTO v_dest_table, v_pk_id 
      FROM temp_sync_del 
      WHERE rowid = v_currid;
      
      -- 解决 M-D 的 pk 栏名不同的问题
      IF IFNULL(v_pk_id, '') = '' THEN
        SET v_sql = CONCAT(v_sql, ';', v_crlf,
          'DELETE FROM ', v_dest_table, ' WHERE ', v_pkwhere, ';');
      ELSE 
        SET v_sql = CONCAT(v_sql, ';', v_crlf,
          'DELETE FROM ', v_dest_table, ' WHERE ', 
          REPLACE(v_pkwhere, p_pk, v_pk_id), ';');
      END IF;
      
      -- 获取下一行
      SELECT MIN(rowid) INTO v_nextrowid 
      FROM temp_sync_del 
      WHERE rowid > v_currid;
      SET v_currid = v_nextrowid;
    END WHILE;
  END IF;
  
  -- 处理日期格式问题
  IF INSTR(v_sql, '000+08:00') > 0 THEN
    SET v_sql = REPLACE(v_sql, '000+08:00', '000Z');
  END IF;
  
  -- 执行SQL
  /* Mariadb 不支援動態語句多 sql, 只能切割 
      GET DIAGNOSTICS CONDITION 1
      @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
      
      SELECT 1 AS code, CONCAT('Error: ', @errno, ' (', @sqlstate, '): ', @text) AS msg, 0 AS recno;
      SELECT v_sql AS error_sql;
    END;
    
    -- 返回成功结果
    SELECT 0 AS code, 'ok' AS msg, 0 AS recno;
    
    -- 执行SQL
    SET @dynamic_sql = v_sql;
    PREPARE stmt FROM @dynamic_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END;
  */
  call execute_multiple_sql(v_sql);
  
  -- 清理临时表
  DROP TEMPORARY TABLE IF EXISTS temp_jsondata;
  DROP TEMPORARY TABLE IF EXISTS temp_sync_del;
  DROP TEMPORARY TABLE IF EXISTS temp_comp_json;
END //

DELIMITER ;