-- ============================================================
-- 销售订单审批接入协同办公(BPM) - 数据库变更脚本
-- 说明:
--   1. 可重复执行(幂等),重复执行不会报错/重复插入
--   2. 项目不使用多租户,脚本不包含 tenant_id
--   3. BPM 流程定义(sale_order_approve)
--      需要在「协同办公 -> 流程管理」中创建并发布,无法用 SQL 完成,见文末说明
-- ============================================================

-- ------------------------------------------------------------
-- 1. erp_sale_order 补 BPM 字段(幂等:缺列才加)
-- ------------------------------------------------------------
SET @s1 = IF((SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='erp_sale_order' AND column_name='process_instance_id')=0,
  'ALTER TABLE erp_sale_order ADD COLUMN process_instance_id VARCHAR(64) NULL COMMENT ''BPM 流程实例编号'' AFTER remark', 'SELECT 1');
PREPARE s1 FROM @s1; EXECUTE s1; DEALLOCATE PREPARE s1;

-- ------------------------------------------------------------
-- 2. 新增 BPM 流程分类:销售订单审批
-- ------------------------------------------------------------
INSERT INTO bpm_category (id, name, code, description, status, sort, creator, create_time, updater, update_time, deleted)
SELECT (SELECT COALESCE(MAX(id), 0) + 1 FROM bpm_category), '销售订单审批', 'sale_order_approve',
       '销售订单审批流程分类', 0, 30, '1', NOW(), '1', NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM bpm_category WHERE code = 'sale_order_approve' AND deleted = 0);

-- ------------------------------------------------------------
-- 3. 修正/新增 erp_audit_status 字典(与 ErpAuditStatus 枚举保持一致)
-- ------------------------------------------------------------
-- 3.1 值 0 = 未提交(销售订单创建后初始状态;不存在则新增,存在则修正标签)
INSERT INTO system_dict_data (id, sort, label, value, dict_type, status, color_type, css_class, remark, creator, create_time, updater, update_time, deleted)
SELECT (SELECT COALESCE(MAX(id), 0) + 1 FROM system_dict_data), 0, '未提交', '0', 'erp_audit_status', 0, 'info', '',
       '销售订单等未提交审批', '1', NOW(), '1', NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM system_dict_data WHERE dict_type = 'erp_audit_status' AND value = '0' AND deleted = 0);

-- 3.2 若已存在但标签不是「未提交」,强制修正为「未提交」
UPDATE system_dict_data
SET label = '未提交', remark = '销售订单等未提交审批', updater = '1', update_time = NOW()
WHERE dict_type = 'erp_audit_status' AND value = '0' AND deleted = 0;

-- ------------------------------------------------------------
-- 4. bpm_business_attach 表:流程定义 -> 业务详情组件映射
--    作用:协同办公审批详情页加载对应业务组件展示业务内容
--    若销售订单详情组件路径不是 /erp/sale/order/detail/index.vue,请修改 component_path
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bpm_business_attach` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `process_definition_key` varchar(64) NOT NULL DEFAULT '' COMMENT '流程定义的Key',
  `business_type` varchar(64) NOT NULL DEFAULT '' COMMENT '业务类型',
  `component_path` varchar(255) NOT NULL DEFAULT '' COMMENT '业务详情组件路径',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='BPM业务附件配置';

-- 4.1 销售订单审批 -> 销售订单详情组件(协同办公审批页显示订单内容)
INSERT INTO bpm_business_attach (id, process_definition_key, business_type, component_path, status, remark, creator, create_time, updater, update_time, deleted)
SELECT (SELECT COALESCE(MAX(id), 0) + 1 FROM bpm_business_attach), 'sale_order_approve', 'erp_sale_order',
       '/erp/sale/order/detail/index.vue', 0, '销售订单审批业务详情组件', '1', NOW(), '1', NOW(), 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM bpm_business_attach WHERE process_definition_key = 'sale_order_approve' AND deleted = 0);

-- ------------------------------------------------------------
-- 5. 历史数据迁移:存量「未审核(10)」销售订单调整为「未提交(0)」
--    新逻辑中创建后即为未提交,10 不再用于销售订单
-- ------------------------------------------------------------
UPDATE erp_sale_order SET status = 0, updater = '1', update_time = NOW()
WHERE status = 10 AND deleted = 0;

-- ============================================================
-- 注意:以下流程定义需要在「协同办公 -> 流程管理」中创建并发布,无法用 SQL 完成
--   1. 流程 key: sale_order_approve
--      名称: 销售订单审批
--      分类: sale_order_approve(对应本脚本第 2 步)
--      审批人: 按需配置
--      表单: 普通表单(表单1);配合 bpm_business_attach 展示销售订单详情
-- ============================================================