-- 物料管理 / 送货单记录
-- 送货单主表，以及送货单与成品批次的绑定关系

CREATE TABLE IF NOT EXISTS `mes_delivery_note` (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号',
  `sn` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '送货单号',
  `customer` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '客户',
  `contact` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '联系人',
  `phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '电话',
  `address` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '送货地址',
  `delivery_date` datetime NULL DEFAULT NULL COMMENT '送货日期',
  `remark` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建者',
  `create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新者',
  `update_date` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_mes_delivery_note_sn` (`sn`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '送货单' ROW_FORMAT = DYNAMIC;

CREATE TABLE IF NOT EXISTS `mes_delivery_note_batch` (
  `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号',
  `delivery_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '送货单id',
  `batch_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '成品批次id',
  `batch_sn` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '批次码',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '创建者',
  `create_date` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '更新者',
  `update_date` datetime NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uk_mes_delivery_note_batch` (`batch_id`) USING BTREE,
  KEY `idx_mes_delivery_note_batch_delivery` (`delivery_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '送货单绑定成品批次' ROW_FORMAT = DYNAMIC;

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`)
SELECT
'2099101500000000001', p.`menu_code`, CONCAT(p.`parent_codes`, p.`menu_code`, ','),
900, CONCAT(p.`tree_sorts`, '0000000900,'), '1', p.`tree_level` + 1,
CONCAT(p.`tree_names`, '/送货单记录'), '送货单记录', '1',
'/mes/mesDeliveryNote/list', '', '', '', '',
'mes:mesDeliveryNote:view,mes:mesDeliveryNote:edit', 40, '1', 'default', 'core', NULL, NULL, '0',
'system', NOW(), 'system', NOW(), '送货单及成品批次绑定'
FROM `js_sys_menu` p
WHERE p.`menu_code` = '1667359431003865088'
  AND NOT EXISTS (SELECT 1 FROM `js_sys_menu` WHERE `menu_code` = '2099101500000000001');

INSERT INTO `js_sys_role_menu` (`role_code`, `menu_code`)
SELECT rm.`role_code`, '2099101500000000001'
FROM `js_sys_role_menu` rm
WHERE rm.`menu_code` = '1667359431003865088'
  AND NOT EXISTS (
    SELECT 1 FROM `js_sys_role_menu` x
    WHERE x.`role_code` = rm.`role_code`
      AND x.`menu_code` = '2099101500000000001'
  );