-- ----------------------------
-- 生产管理模块建表脚本（P0）
-- 执行方式：在 MySQL 8.0 中执行本脚本
-- ----------------------------

-- ----------------------------
-- 1、包装BOM主表
-- ----------------------------
DROP TABLE IF EXISTS `jsh_pack_bom`;
CREATE TABLE `jsh_pack_bom` (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `bom_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '方案编号',
  `bom_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '方案名称',
  `product_material_id` bigint(0) NULL DEFAULT NULL COMMENT '待包装产品商品id',
  `product_material_extend_id` bigint(0) NULL DEFAULT NULL COMMENT '待包装产品扩展id',
  `product_sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '待包装产品多属性',
  `output_material_id` bigint(0) NULL DEFAULT NULL COMMENT '成品SKU商品id',
  `output_material_extend_id` bigint(0) NULL DEFAULT NULL COMMENT '成品扩展id',
  `output_sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '成品多属性',
  `output_number` decimal(24, 6) NULL DEFAULT NULL COMMENT '单位待包装产品产出成品数量',
  `version` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT 'V1' COMMENT '版本',
  `enabled` bit(1) NULL DEFAULT b'1' COMMENT '启用状态',
  `remark` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `creator` bigint(0) NULL DEFAULT NULL COMMENT '创建人',
  `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
  `delete_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标记，0未删除，1删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `product_material_id`(`product_material_id`) USING BTREE,
  INDEX `output_material_id`(`output_material_id`) USING BTREE,
  INDEX `tenant_id`(`tenant_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '包装BOM主表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 2、包装BOM包材明细表
-- ----------------------------
DROP TABLE IF EXISTS `jsh_pack_bom_item`;
CREATE TABLE `jsh_pack_bom_item` (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `bom_id` bigint(0) NOT NULL COMMENT '包装BOM id',
  `material_id` bigint(0) NOT NULL COMMENT '包材商品id',
  `material_extend_id` bigint(0) NULL DEFAULT NULL COMMENT '商品扩展id',
  `material_sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '多属性',
  `unit` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单位',
  `quantity` decimal(24, 6) NULL DEFAULT NULL COMMENT '用量(每产出1单位成品所需包材数量)',
  `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
  `delete_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标记，0未删除，1删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `bom_id`(`bom_id`) USING BTREE,
  INDEX `material_id`(`material_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '包装BOM包材明细表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 3、生产工单主表
-- ----------------------------
DROP TABLE IF EXISTS `jsh_production_order`;
CREATE TABLE `jsh_production_order` (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工单号',
  `source_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '来源类型(销售订单/手工备货)',
  `source_id` bigint(0) NULL DEFAULT NULL COMMENT '来源单据id(销售订单depot_head.id)',
  `source_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '来源单据号(销售订单号)',
  `product_material_id` bigint(0) NULL DEFAULT NULL COMMENT '待包装产品商品id',
  `product_material_extend_id` bigint(0) NULL DEFAULT NULL COMMENT '待包装产品扩展id',
  `product_sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '待包装产品多属性',
  `bom_id` bigint(0) NULL DEFAULT NULL COMMENT '包装BOM id',
  `output_material_id` bigint(0) NULL DEFAULT NULL COMMENT '成品SKU商品id',
  `output_material_extend_id` bigint(0) NULL DEFAULT NULL COMMENT '成品扩展id',
  `output_sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '成品多属性',
  `order_quantity` decimal(24, 6) NULL DEFAULT NULL COMMENT '工单数量(待包装产品)',
  `output_quantity` decimal(24, 6) NULL DEFAULT NULL COMMENT '预计成品数量',
  `finished_quantity` decimal(24, 6) NULL DEFAULT 0.000000 COMMENT '已完工良品数',
  `plan_start_date` datetime(0) NULL DEFAULT NULL COMMENT '计划开工日期',
  `plan_end_date` datetime(0) NULL DEFAULT NULL COMMENT '计划完工日期',
  `actual_start_date` datetime(0) NULL DEFAULT NULL COMMENT '实际开工日期',
  `actual_end_date` datetime(0) NULL DEFAULT NULL COMMENT '实际完工日期',
  `priority` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '优先级，0普通、1紧急',
  `status` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '状态，0已创建、1已下达、2生产中、3已完工、4已关闭',
  `audit_by` bigint(0) NULL DEFAULT NULL COMMENT '审批人',
  `audit_time` datetime(0) NULL DEFAULT NULL COMMENT '审批时间',
  `release_by` bigint(0) NULL DEFAULT NULL COMMENT '下达人',
  `release_time` datetime(0) NULL DEFAULT NULL COMMENT '下达时间',
  `close_reason` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关闭原因',
  `remark` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `creator` bigint(0) NULL DEFAULT NULL COMMENT '创建人',
  `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
  `delete_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标记，0未删除，1删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `order_number`(`order_number`) USING BTREE,
  INDEX `source_number`(`source_number`) USING BTREE,
  INDEX `status`(`status`) USING BTREE,
  INDEX `tenant_id`(`tenant_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '生产工单主表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 4、工单操作日志表
-- ----------------------------
DROP TABLE IF EXISTS `jsh_production_order_log`;
CREATE TABLE `jsh_production_order_log` (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_id` bigint(0) NULL DEFAULT NULL COMMENT '工单id',
  `order_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工单号',
  `operation_type` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作类型(创建/修改/审批下达/开工/生成领料/发料/完工/关闭等)',
  `operation_desc` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '操作描述',
  `operator` bigint(0) NULL DEFAULT NULL COMMENT '操作人',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '操作时间',
  `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `order_id`(`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '工单操作日志表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 5、生产领料单主表
-- ----------------------------
DROP TABLE IF EXISTS `jsh_production_requisition`;
CREATE TABLE `jsh_production_requisition` (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `requisition_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '领料单号',
  `order_id` bigint(0) NULL DEFAULT NULL COMMENT '工单id',
  `order_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工单号',
  `depot_id` bigint(0) NULL DEFAULT NULL COMMENT '发料仓库id',
  `status` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '状态，0待确认、1已发料、2已驳回',
  `confirm_by` bigint(0) NULL DEFAULT NULL COMMENT '确认人',
  `confirm_time` datetime(0) NULL DEFAULT NULL COMMENT '确认时间',
  `remark` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `creator` bigint(0) NULL DEFAULT NULL COMMENT '创建人',
  `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
  `delete_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标记，0未删除，1删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `requisition_number`(`requisition_number`) USING BTREE,
  INDEX `order_id`(`order_id`) USING BTREE,
  INDEX `status`(`status`) USING BTREE,
  INDEX `tenant_id`(`tenant_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '生产领料单主表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 6、生产领料单明细表
-- ----------------------------
DROP TABLE IF EXISTS `jsh_production_requisition_item`;
CREATE TABLE `jsh_production_requisition_item` (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `requisition_id` bigint(0) NOT NULL COMMENT '领料单id',
  `depot_id` bigint(0) NULL DEFAULT NULL COMMENT '发料仓库id',
  `material_id` bigint(0) NOT NULL COMMENT '物料商品id',
  `material_extend_id` bigint(0) NULL DEFAULT NULL COMMENT '商品扩展id',
  `material_sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '多属性',
  `unit` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单位',
  `required_quantity` decimal(24, 6) NULL DEFAULT NULL COMMENT '申请数量(BOM定额)',
  `issued_quantity` decimal(24, 6) NULL DEFAULT NULL COMMENT '实发数量',
  `unit_price` decimal(24, 6) NULL DEFAULT NULL COMMENT '参考单价',
  `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
  `delete_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标记，0未删除，1删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `requisition_id`(`requisition_id`) USING BTREE,
  INDEX `depot_id`(`depot_id`) USING BTREE,
  INDEX `material_id`(`material_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '生产领料单明细表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 7、完工报告主表
-- ----------------------------
DROP TABLE IF EXISTS `jsh_production_report`;
CREATE TABLE `jsh_production_report` (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `report_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '报告号',
  `order_id` bigint(0) NULL DEFAULT NULL COMMENT '工单id',
  `order_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '工单号',
  `finished_quantity` decimal(24, 6) NULL DEFAULT NULL COMMENT '实际良品数',
  `defect_quantity` decimal(24, 6) NULL DEFAULT NULL COMMENT '不良品数',
  `qualified_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '质检结果，0未检、1合格、2不合格',
  `depot_id` bigint(0) NULL DEFAULT NULL COMMENT '入库仓库id',
  `in_number` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '生产入库单号',
  `audit_status` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '审核状态，0待审核、1已审核、2已驳回',
  `audit_by` bigint(0) NULL DEFAULT NULL COMMENT '审核人',
  `audit_time` datetime(0) NULL DEFAULT NULL COMMENT '审核时间',
  `report_by` bigint(0) NULL DEFAULT NULL COMMENT '报工人',
  `report_date` datetime(0) NULL DEFAULT NULL COMMENT '报工日期',
  `remark` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `creator` bigint(0) NULL DEFAULT NULL COMMENT '创建人',
  `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
  `delete_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标记，0未删除，1删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `report_number`(`report_number`) USING BTREE,
  INDEX `order_id`(`order_id`) USING BTREE,
  INDEX `audit_status`(`audit_status`) USING BTREE,
  INDEX `tenant_id`(`tenant_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '完工报告主表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 8、完工报告物料消耗明细表
-- ----------------------------
DROP TABLE IF EXISTS `jsh_production_report_item`;
CREATE TABLE `jsh_production_report_item` (
  `id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `report_id` bigint(0) NOT NULL COMMENT '完工报告id',
  `material_id` bigint(0) NOT NULL COMMENT '物料商品id',
  `material_extend_id` bigint(0) NULL DEFAULT NULL COMMENT '商品扩展id',
  `material_sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '多属性',
  `unit` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '单位',
  `consumption_quantity` decimal(24, 6) NULL DEFAULT NULL COMMENT '实际消耗数量',
  `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `tenant_id` bigint(0) NULL DEFAULT NULL COMMENT '租户id',
  `delete_flag` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '删除标记，0未删除，1删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `report_id`(`report_id`) USING BTREE,
  INDEX `material_id`(`material_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '完工报告物料消耗明细表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 9、生产管理菜单（jsh_function）
-- ----------------------------
INSERT INTO `jsh_function` (`number`, `name`, `parent_number`, `url`, `component`, `state`, `sort`, `enabled`, `type`, `push_btn`, `icon`, `delete_flag`) VALUES ('0901', '生产管理', '0', '/production', '/layouts/TabLayout', b'0', '0480', b'1', '电脑版', '', 'cluster', '0');
INSERT INTO `jsh_function` (`number`, `name`, `parent_number`, `url`, `component`, `state`, `sort`, `enabled`, `type`, `push_btn`, `icon`, `delete_flag`) VALUES ('090101', '包装方案', '0901', '/production/pack_bom', '/production/PackBomList', b'0', '0481', b'1', '电脑版', '1,2,3', 'profile', '0');
INSERT INTO `jsh_function` (`number`, `name`, `parent_number`, `url`, `component`, `state`, `sort`, `enabled`, `type`, `push_btn`, `icon`, `delete_flag`) VALUES ('090102', '生产工单', '0901', '/production/order', '/production/OrderList', b'0', '0482', b'1', '电脑版', '1,2,3', 'profile', '0');
INSERT INTO `jsh_function` (`number`, `name`, `parent_number`, `url`, `component`, `state`, `sort`, `enabled`, `type`, `push_btn`, `icon`, `delete_flag`) VALUES ('090103', '领料管理', '0901', '/production/requisition', '/production/RequisitionList', b'0', '0483', b'1', '电脑版', '1,2,3', 'profile', '0');
INSERT INTO `jsh_function` (`number`, `name`, `parent_number`, `url`, `component`, `state`, `sort`, `enabled`, `type`, `push_btn`, `icon`, `delete_flag`) VALUES ('090104', '完工报工', '0901', '/production/report', '/production/ReportList', b'0', '0484', b'1', '电脑版', '1,2,3', 'profile', '0');