-- 原材料送检记录（主表 + 子表）
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `mes_xsl_raw_material_inspect_record` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `raw_material_card_id` varchar(32) DEFAULT NULL COMMENT '原材料卡片ID',
  `barcode` varchar(128) DEFAULT NULL COMMENT '条码',
  `batch_no` varchar(128) DEFAULT NULL COMMENT '批次号',
  `material_id` varchar(32) DEFAULT NULL COMMENT '物料ID',
  `material_name` varchar(200) DEFAULT NULL COMMENT '物料名称',
  `inspect_status` varchar(10) DEFAULT '0' COMMENT '检验状态（字典 xslmes_inspect_status：0待检 1合格 2不合格）',
  `inspect_time` datetime DEFAULT NULL COMMENT '送检时间',
  `result_time` datetime DEFAULT NULL COMMENT '判定时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `tenant_id` int DEFAULT NULL COMMENT '租户',
  PRIMARY KEY (`id`),
  KEY `idx_xsl_rm_ir_card` (`raw_material_card_id`),
  KEY `idx_xsl_rm_ir_status` (`inspect_status`),
  KEY `idx_xsl_rm_ir_time` (`inspect_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='原材料送检记录';

CREATE TABLE IF NOT EXISTS `mes_xsl_raw_material_inspect_record_line` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `record_id` varchar(32) NOT NULL COMMENT '送检记录主表ID',
  `inspect_std_id` varchar(32) DEFAULT NULL COMMENT '检验标准ID',
  `inspect_item_id` varchar(32) DEFAULT NULL COMMENT '检验项目ID',
  `inspect_item_name` varchar(200) DEFAULT NULL COMMENT '检验项目名称',
  `allow_min` decimal(24,6) DEFAULT NULL COMMENT '容许最小值',
  `include_min_flag` int NOT NULL DEFAULT '0' COMMENT '包含最小值：1是 0否',
  `allow_max` decimal(24,6) DEFAULT NULL COMMENT '容许最大值',
  `include_max_flag` int NOT NULL DEFAULT '0' COMMENT '包含最大值：1是 0否',
  `inspect_value` decimal(24,6) DEFAULT NULL COMMENT '检验值',
  `pass_flag` varchar(10) DEFAULT '0' COMMENT '判定状态（字典 xslmes_inspect_status：0待检 1合格 2不合格）',
  `sort_no` int DEFAULT NULL COMMENT '排序',
  `create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_xsl_rm_irl_record` (`record_id`),
  KEY `idx_xsl_rm_irl_item` (`inspect_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='原材料送检记录-检验明细';

-- 字典：送检状态
INSERT INTO `sys_dict` (`id`, `dict_name`, `dict_code`, `description`, `del_flag`, `create_by`, `create_time`, `type`)
SELECT REPLACE(UUID(), '-', ''), '送检状态', 'xslmes_inspect_status', '原材料送检状态：待检/合格/不合格', 0, 'admin', NOW(), 0
WHERE NOT EXISTS (
  SELECT 1 FROM `sys_dict` WHERE `dict_code` = 'xslmes_inspect_status' AND `del_flag` = 0
);

INSERT INTO `sys_dict_item` (`id`, `dict_id`, `item_text`, `item_value`, `description`, `sort_order`, `status`, `create_by`, `create_time`)
SELECT REPLACE(UUID(), '-', ''), d.id, '待检', '0', '待检', 1, 1, 'admin', NOW()
FROM `sys_dict` d
WHERE d.`dict_code` = 'xslmes_inspect_status'
  AND d.`del_flag` = 0
  AND NOT EXISTS (
    SELECT 1 FROM `sys_dict_item` i WHERE i.dict_id = d.id AND i.item_value = '0'
  );

INSERT INTO `sys_dict_item` (`id`, `dict_id`, `item_text`, `item_value`, `description`, `sort_order`, `status`, `create_by`, `create_time`)
SELECT REPLACE(UUID(), '-', ''), d.id, '合格', '1', '合格', 2, 1, 'admin', NOW()
FROM `sys_dict` d
WHERE d.`dict_code` = 'xslmes_inspect_status'
  AND d.`del_flag` = 0
  AND NOT EXISTS (
    SELECT 1 FROM `sys_dict_item` i WHERE i.dict_id = d.id AND i.item_value = '1'
  );

INSERT INTO `sys_dict_item` (`id`, `dict_id`, `item_text`, `item_value`, `description`, `sort_order`, `status`, `create_by`, `create_time`)
SELECT REPLACE(UUID(), '-', ''), d.id, '不合格', '2', '不合格', 3, 1, 'admin', NOW()
FROM `sys_dict` d
WHERE d.`dict_code` = 'xslmes_inspect_status'
  AND d.`del_flag` = 0
  AND NOT EXISTS (
    SELECT 1 FROM `sys_dict_item` i WHERE i.dict_id = d.id AND i.item_value = '2'
  );