zkxw/docs/sql/oa_instance_draft.sql

-- OA 发起人填写草稿（非模板设计草稿）
CREATE TABLE IF NOT EXISTS `oa_instance_draft` (
  `id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '主键ID',
  `app_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '应用ID',
  `app_version_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '暂存时锁定的已发布版本ID',
  `title` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '标题',
  `form_data_json` mediumtext COLLATE utf8mb4_unicode_ci COMMENT '表单数据 JSON',
  `initiator_select_json` text COLLATE utf8mb4_unicode_ci COMMENT '发起人自选 JSON',
  `initiator_dept_id` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '发起人部门',
  `owner_user_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '草稿所属人',
  `tenant_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'zkxw' COMMENT '租户编号',
  `order_num` int(11) DEFAULT NULL COMMENT '排序号',
  `remark` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '创建人id',
  `create_dept_id` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人部门ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_by` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '更新人id',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标记：0-正常 1-已删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_oid_owner` (`tenant_no`,`owner_user_id`,`del_flag`) USING BTREE,
  KEY `idx_oid_app` (`tenant_no`,`app_id`,`del_flag`) USING BTREE,
  KEY `idx_oid_update` (`tenant_no`,`owner_user_id`,`update_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='OA发起审批草稿';

-- 草稿箱位于「发起审批」与「待办中心」之间；仅首次插入时顺延 order_num
UPDATE `sys_menu` AS m
INNER JOIN (
  SELECT 1 AS ok
  FROM DUAL
  WHERE NOT EXISTS (
    SELECT 1 FROM `sys_menu`
    WHERE `id` = 'OA000000000000000000000000000109' AND `del_flag` = 0
  )
) AS guard ON 1 = 1
SET m.`order_num` = m.`order_num` + 1
WHERE m.`parent_id` = 'OA000000000000000000000000000001'
  AND m.`del_flag` = 0
  AND m.`order_num` IS NOT NULL
  AND m.`order_num` >= 4;

INSERT INTO `sys_menu` (
  `id`, `menu_name`, `parent_id`, `menu_type`, `path`, `component`, `perms`,
  `is_frame`, `visible`, `status`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
) SELECT
  'OA000000000000000000000000000109', '草稿箱', 'OA000000000000000000000000000001',
  'C', 'drafts', '/oa-approval/drafts/index', 'oa:drafts',
  1, '1', '1', 'zkxw', 4, 'OA发起审批草稿箱',
  NULL, '', NOW(), NULL, NOW(), 0
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_menu` WHERE `id` = 'OA000000000000000000000000000109' AND `del_flag` = 0
);

-- 已拥有「发起审批」菜单的角色自动获得「草稿箱」（幂等）
-- 注意：MySQL 5.7 下 INSERT…SELECT 中 UUID() 可能整批相同，导致主键冲突；改用按行唯一的 MD5。
INSERT INTO `sys_role_menu` (
  `id`, `role_id`, `menu_id`, `tenant_no`, `order_num`, `remark`,
  `create_by`, `create_dept_id`, `create_time`, `update_by`, `update_time`, `del_flag`
)
SELECT
  MD5(CONCAT(rm.`role_id`, '#', 'OA000000000000000000000000000109')),
  rm.`role_id`,
  'OA000000000000000000000000000109',
  COALESCE(NULLIF(rm.`tenant_no`, ''), 'zkxw'),
  0,
  'auto from oa:start',
  NULL,
  '',
  NOW(),
  NULL,
  NOW(),
  0
FROM `sys_role_menu` rm
WHERE rm.`menu_id` = 'OA000000000000000000000000000103'
  AND rm.`del_flag` = 0
  AND NOT EXISTS (
    SELECT 1
    FROM `sys_role_menu` x
    WHERE x.`role_id` = rm.`role_id`
      AND x.`menu_id` = 'OA000000000000000000000000000109'
      AND x.`del_flag` = 0
  );