-- ============================================================================
-- 需求：基础参数(工艺参数)可与工序绑定，绑定后报工需填写参数字段
-- 一键整合脚本：可直接手动执行（含幂等保护，重复执行不报错）
--
-- 第一部分：功能迁移（工序-参数绑定）
-- 第二部分：活库漂移修复（代码已引用但库缺失的列/表，需补齐才能建任务/报工）
-- ============================================================================

-- ============================================================================
-- 第一部分：功能迁移
-- ============================================================================

-- ---------- 1.1 工序/任务/工单工序 增加 param_template_id（参数模板关联，功能链路前置） ----------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_pro_task' AND column_name = 'param_template_id');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_pro_task ADD COLUMN param_template_id bigint DEFAULT NULL COMMENT ''工艺参数模板编号'' AFTER process_id',
              'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_process' AND column_name = 'param_template_id');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_pro_route_process ADD COLUMN param_template_id bigint DEFAULT NULL COMMENT ''工艺参数模板编号'' AFTER process_id',
              'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process' AND column_name = 'param_template_id');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_pro_work_order_process ADD COLUMN param_template_id bigint DEFAULT NULL COMMENT ''工艺参数模板编号'' AFTER process_id',
              'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 1.2 新建 工序-工艺参数绑定表 ----------
CREATE TABLE IF NOT EXISTS `mes_pro_process_param` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
  `process_id` bigint NOT NULL COMMENT '工序编号（关联 mes_pro_process）',
  `process_param_id` bigint NOT NULL COMMENT '工艺参数编号（关联 mes_pd_process_param）',
  `required` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否必填',
  `sort` int NULL DEFAULT NULL COMMENT '排序',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL 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`) USING BTREE,
  INDEX `idx_process_id`(`process_id` ASC) USING BTREE,
  INDEX `idx_process_param_id`(`process_param_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'MES 工序-工艺参数绑定' ROW_FORMAT = DYNAMIC;

-- ---------- 1.3 参数记录主表模板字段允许为空（工序绑定参数生成时无模板） ----------
ALTER TABLE mes_pro_work_order_param_record MODIFY COLUMN template_id bigint NULL DEFAULT NULL COMMENT '参数模板编号（工序绑定参数生成时为空）';
ALTER TABLE mes_pro_work_order_param_record MODIFY COLUMN template_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '模板名称（快照）';

-- ---------- 1.4 参数记录明细增加 required 快照（报工必填校验用） ----------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_param_record_detail' AND column_name = 'required');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_pro_work_order_param_record_detail ADD COLUMN required bit(1) NOT NULL DEFAULT b''0'' COMMENT ''是否必填（快照）'' AFTER param_type',
              'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================================
-- 第二部分：活库漂移修复（代码已引用但库缺失；若你的库已有这些列/表可忽略）
-- ============================================================================

-- ---------- 2.1 mes_pro_work_order 缺失列（报工审批 BPM 相关） ----------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order' AND column_name = 'is_feedback_approve');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_pro_work_order ADD COLUMN is_feedback_approve tinyint(1) DEFAULT NULL COMMENT ''是否开启报工审批 (1: 开启, 0/NULL: 关闭)'' AFTER deleted',
              'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order' AND column_name = 'process_definition_key');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_pro_work_order ADD COLUMN process_definition_key varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ''BPM 流程定义 Key'' AFTER is_feedback_approve',
              'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order' AND column_name = 'feedback_role');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_pro_work_order ADD COLUMN feedback_role varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ''报工角色 (多个角色以逗号分隔)'' AFTER process_definition_key',
              'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 2.2 mes_pro_feedback 缺失列（报工 BPM 相关） ----------
SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND column_name = 'process_instance_id');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_pro_feedback ADD COLUMN process_instance_id varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ''BPM 流程实例编号'' AFTER remark',
              'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @exists = (SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback' AND column_name = 'process_definition_key');
SET @sql = IF(@exists = 0,
              'ALTER TABLE mes_pro_feedback ADD COLUMN process_definition_key varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ''BPM 流程定义 Key'' AFTER process_instance_id',
              'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------- 2.3 缺失表：任务指定报工人员、任务停工记录（此前迁移未应用） ----------
CREATE TABLE IF NOT EXISTS `mes_pro_task_assignee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `task_id` bigint(20) NOT NULL COMMENT '生产任务 ID',
  `user_id` bigint(20) NOT NULL COMMENT '系统用户 ID (指定报工人员)',
  `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`),
  KEY `idx_task_id` (`task_id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='生产任务指定报工人员表';

CREATE TABLE IF NOT EXISTS `mes_pro_task_pause_record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `task_id` bigint(20) NOT NULL COMMENT '任务编号',
  `work_order_id` bigint(20) DEFAULT NULL COMMENT '工单编号',
  `workstation_id` bigint(20) DEFAULT NULL COMMENT '工作站编号',
  `reason` varchar(255) NOT NULL COMMENT '停工原因',
  `pause_time` datetime NOT NULL COMMENT '停工时间',
  `pause_user_id` bigint(20) NOT NULL COMMENT '停工操作人ID',
  `resume_time` datetime DEFAULT NULL COMMENT '复工时间',
  `resume_user_id` bigint(20) DEFAULT NULL COMMENT '复工操作人ID',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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`) USING BTREE,
  KEY `idx_task_id` (`task_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='生产任务停工记录表';

-- ============================================================================
-- 执行完成。可执行以下语句核对：
--   SHOW TABLES LIKE 'mes_pro_process_param';
--   SHOW COLUMNS FROM mes_pro_work_order_param_record_detail LIKE 'required';
-- ============================================================================