-- ============================================================
-- 工位设备保养：建表 + 菜单 + 角色权限 + OP030 示例计划
-- 数据库：mes_cloud_199_0y7a
-- ============================================================

-- ----------------------------
-- 1. 数据表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `mes_device_maintenance_plan` (
  `id` varchar(64) NOT NULL COMMENT '编号',
  `title` varchar(100) DEFAULT NULL COMMENT '计划名称',
  `line_sn` varchar(100) NOT NULL COMMENT '产线编号',
  `oprno` varchar(20) NOT NULL COMMENT '工位号',
  `interval_days` int NOT NULL COMMENT '保养周期(天)',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `status` char(1) NOT NULL DEFAULT '0' COMMENT '状态（0正常 1删除 2停用）',
  `create_by` varchar(64) NOT NULL COMMENT '创建者',
  `create_date` datetime NOT NULL COMMENT '创建时间',
  `update_by` varchar(64) NOT NULL COMMENT '更新者',
  `update_date` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_mdm_plan_line_oprno` (`line_sn`,`oprno`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工位设备保养计划';

CREATE TABLE IF NOT EXISTS `mes_device_maintenance_record` (
  `id` varchar(64) NOT NULL COMMENT '编号',
  `plan_id` varchar(64) DEFAULT NULL COMMENT '保养计划',
  `line_sn` varchar(100) NOT NULL COMMENT '产线编号',
  `oprno` varchar(20) NOT NULL COMMENT '工位号',
  `content` varchar(500) DEFAULT NULL COMMENT '保养内容',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) NOT NULL COMMENT '保养人员',
  `create_date` datetime NOT NULL COMMENT '保养时间',
  `update_by` varchar(64) NOT NULL COMMENT '更新者',
  `update_date` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_mdm_record_line_oprno` (`line_sn`,`oprno`,`create_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工位设备保养记录';

-- ----------------------------
-- 2. 菜单（设备管理 > 工位保养）
-- menu_code 说明：
--   2068001000000000100  工位保养（目录）
--   2068001000000000101  保养计划
--   2068001000000000102  保养记录
-- ----------------------------
DELETE FROM `js_sys_role_menu` WHERE `menu_code` IN ('2068001000000000100','2068001000000000101','2068001000000000102');
DELETE FROM `js_sys_menu` WHERE `menu_code` IN ('2068001000000000100','2068001000000000101','2068001000000000102');

INSERT INTO `js_sys_menu`
(`menu_code`, `parent_code`, `parent_codes`, `tree_sort`, `tree_sorts`, `tree_leaf`, `tree_level`, `tree_names`, `menu_name`, `menu_type`, `menu_href`, `menu_target`, `menu_icon`, `menu_color`, `menu_title`, `permission`, `weight`, `is_show`, `sys_code`, `module_codes`, `component`, `params`, `status`, `create_by`, `create_date`, `update_by`, `update_date`, `remarks`)
VALUES
('2068001000000000100', '1582259848270577664', '0,1582259848270577664,', 405, '0000009170,0000000405,', '0', 1, '设备管理/工位保养', '工位保养', '1', '', '', 'fa fa-wrench', '', '', '', 40, '1', 'default', 'app', NULL, NULL, '0', 'system', NOW(), 'system', NOW(), '工位周期性保养（按天拦截加工）'),
('2068001000000000101', '2068001000000000100', '0,1582259848270577664,2068001000000000100,', 30, '0000009170,0000000405,0000000030,', '1', 2, '设备管理/工位保养/保养计划', '保养计划', '1', '/mes/mesDeviceMaintenancePlan/list', '', '', '', '', 'mes:mesDeviceMaintenancePlan:view,mes:mesDeviceMaintenancePlan:edit', 40, '1', 'default', 'core', NULL, NULL, '0', 'system', NOW(), 'system', NOW(), ''),
('2068001000000000102', '2068001000000000100', '0,1582259848270577664,2068001000000000100,', 60, '0000009170,0000000405,0000000060,', '1', 2, '设备管理/工位保养/保养记录', '保养记录', '1', '/mes/mesDeviceMaintenanceRecord/list', '', '', '', '', 'mes:mesDeviceMaintenanceRecord:view,mes:mesDeviceMaintenanceRecord:edit', 40, '1', 'default', 'core', NULL, NULL, '0', 'system', NOW(), 'system', NOW(), '');

-- ----------------------------
-- 3. 角色菜单授权
-- 凡已拥有「设备管理」下任意子菜单的角色，自动授权工位保养（三件套）
-- ----------------------------
INSERT IGNORE INTO `js_sys_role_menu` (`role_code`, `menu_code`)
SELECT DISTINCT rm.`role_code`, m.`menu_code`
FROM `js_sys_role_menu` rm
INNER JOIN `js_sys_menu` dm ON dm.`menu_code` = rm.`menu_code`
  AND dm.`parent_code` = '1582259848270577664'
  AND dm.`status` = '0'
CROSS JOIN (
  SELECT '2068001000000000100' AS menu_code UNION ALL
  SELECT '2068001000000000101' UNION ALL
  SELECT '2068001000000000102'
) m;

-- ----------------------------
-- 4. OP030 示例保养计划（产线 XT，A/B 面分别配置）
-- ----------------------------
DELETE FROM `mes_device_maintenance_plan` WHERE `id` IN ('2068002000000000001','2068002000000000002');

INSERT INTO `mes_device_maintenance_plan`
(`id`, `title`, `line_sn`, `oprno`, `interval_days`, `remark`, `status`, `create_by`, `create_date`, `update_by`, `update_date`)
VALUES
('2068002000000000001', 'OP030A整站保养', 'XT', 'OP030A', 7, 'OP030 A面整站保养，到期拦截加工', '0', 'system', NOW(), 'system', NOW()),
('2068002000000000002', 'OP030B整站保养', 'XT', 'OP030B', 7, 'OP030 B面整站保养，到期拦截加工', '0', 'system', NOW(), 'system', NOW());