DROP TABLE IF EXISTS `production_plan`;

CREATE TABLE production_plan
(
    id                 BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
    form_instance_id   VARCHAR(64) COMMENT '表单实例ID',
    serial_no          VARCHAR(50) COMMENT '流水号',
    apply_no           VARCHAR(100) COMMENT '申请单编号',
    customer_name      VARCHAR(200) COMMENT '客户名称',
    material_code      VARCHAR(100) COMMENT '物料编码',
    product_name       VARCHAR(200) COMMENT '产品名称',
    product_spec       VARCHAR(200) COMMENT '产品规格',
    length             INT COMMENT '长',
    width              INT COMMENT '宽',
    height             INT COMMENT '高',
    quantity           INT COMMENT '块数',
    total_count        INT COMMENT '此次统计数量',
    volume             DECIMAL(10, 4) COMMENT '方数',
    strength           VARCHAR(50) COMMENT '强度',
    start_date         DATETIME COMMENT '开始日期',
    end_date           DATETIME COMMENT '结束日期',
    submitter          VARCHAR(100) COMMENT '提交人',
    submit_org         VARCHAR(200) COMMENT '提交人组织',
    remark_one         TEXT COMMENT '备注1',
    remark_two         TEXT COMMENT '备注2',
    creator_name       VARCHAR(100) COMMENT '创建人',
    modifier_name      VARCHAR(100) COMMENT '修改人',
    form_created_time  DATETIME COMMENT '表单创建时间',
    form_modified_time DATETIME COMMENT '表单修改时间',
    data_sync_type     TINYINT  DEFAULT 1 COMMENT '数据同步类型：1=手动 2=定时任务',
    data_source_type   TINYINT  DEFAULT 1 COMMENT '数据来源类型：1=销售订单 2=销售预测',
    create_time        DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '数据库创建时间',
    update_time        DATETIME COMMENT '数据库更新时间',

    INDEX idx_apply_no (apply_no),
    INDEX idx_serial_no (serial_no),
    INDEX idx_form_instance_id (form_instance_id),
    INDEX idx_material_code (material_code)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4 COMMENT ='销售生产计划';

CREATE TABLE `product-inventory-management-zsjc`.`energy`
(
    `id`          int            NOT NULL AUTO_INCREMENT,
    `energy_tyep` varchar(255)   NULL COMMENT '能源类型',
    `energy_name` varchar(255)   NULL COMMENT '能源名称',
    `unit`        varchar(255)   NULL COMMENT '单位',
    `unit_price`  decimal(10, 2) NULL COMMENT '单价',
    `remark`      varchar(255)   NULL COMMENT '备注',
    `create_time` datetime(0)    NULL DEFAULT NULL COMMENT '创建时间',
    `update_time` datetime(0)    NULL DEFAULT NULL COMMENT '更新时间',
    `create_user` bigint         NULL DEFAULT NULL COMMENT '创建者',
    `update_user` bigint         NULL DEFAULT NULL COMMENT '更新者',
    PRIMARY KEY (`id`)
) COMMENT = '能源类型';

CREATE TABLE `product-inventory-management-zsjc`.`energy_consumption_detail`
(
    `id`                     int            NOT NULL AUTO_INCREMENT,
    `energy_id`              int            NULL COMMENT '外检关联能源类型id',
    `type`                   varchar(255)   NULL COMMENT '办公/生产',
    `meter_reading_location` varchar(255)   NULL COMMENT '抄表位置',
    `meter_reading_date`     date           NULL COMMENT '抄表日期',
    `start_code`             decimal(10, 2) NULL COMMENT '起码',
    `stop_code`              decimal(10, 2) NULL COMMENT '止码',
    `dosage`                 decimal(10, 2) NULL COMMENT '用量',
    `remark`                 varchar(255)   NULL COMMENT '备注',
    `create_time`            datetime(0)    NULL DEFAULT NULL COMMENT '创建时间',
    `update_time`            datetime(0)    NULL DEFAULT NULL COMMENT '更新时间',
    `create_user`            bigint         NULL DEFAULT NULL COMMENT '创建者',
    `update_user`            bigint         NULL DEFAULT NULL COMMENT '更新者',
    PRIMARY KEY (`id`)
) COMMENT = '能源类型-能耗抄表明细';

CREATE TABLE `product-inventory-management-zsjc`.`energy_consumption_detail_file`
(
    `id`                           int                                                           NOT NULL AUTO_INCREMENT,
    `energy_consumption_detail_id` int                                                           NULL COMMENT '关联外键能耗抄表id',
    `name`                         varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
    `url`                          varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
    `file_size`                    double                                                        NULL DEFAULT NULL,
    `create_time`                  timestamp(0)                                                  NULL DEFAULT NULL,
    `create_user`                  int                                                           NULL DEFAULT NULL,
    `update_time`                  timestamp(0)                                                  NULL DEFAULT NULL,
    `update_user`                  int                                                           NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) COMMENT = '能源类型-能耗抄表明细_附件';

alter table product_order
    drop column sales_ledger_id,
    drop column sale_ledger_product_id,
    drop column product_model_id;

alter table production_plan
    add assigned_quantity DECIMAL(10, 4) default 0 not null COMMENT '下发数量';

alter table product_order
    add plan_complete_time date NULL DEFAULT NULL COMMENT '计划完成时间';

# 生产订单与生产计划关联表
drop table if exists product_order_plan;
create table product_order_plan
(
    id                 bigint auto_increment primary key,
    product_order_id   bigint   not null default 0 comment '生产订单id',
    production_plan_id bigint   not null default 0 comment '生产计划id',
    create_time        datetime null comment '录入时间',
    update_time        datetime null comment '更新时间',
    assigned_quantity  DECIMAL(10, 4)    default 0 not null comment '下发数量',
    index idx_product_order_id (product_order_id),
    index idx_production_plan_id (production_plan_id),
    unique idx_product_order_production_plan (product_order_id, production_plan_id)
);

CREATE TABLE `product_material`
(
    `id`                    BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `tenant_id`             BIGINT      DEFAULT NULL COMMENT '租户ID',
    `material_type_id`      INT         DEFAULT NULL COMMENT '物料类型ID',
    `inventory_category_id` INT         DEFAULT NULL COMMENT '存货类别ID',
    `material_name`         VARCHAR(255) NOT NULL COMMENT '物料名称',
    `base_unit`             VARCHAR(50) DEFAULT NULL COMMENT '基本单位',
    `remark`                TEXT COMMENT '备注',
    `create_time`           DATETIME    DEFAULT CURRENT_TIMESTAMP,
    `update_time`           DATETIME    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_type_id` (`material_type_id`),
    KEY `idx_cat_id` (`inventory_category_id`),
    UNIQUE KEY `uk_material_name` (`material_name`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4 COMMENT = '物料主表';
CREATE TABLE `product_material_sku`
(
    `id`                 BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `material_id`        BIGINT NOT NULL COMMENT '物料ID',
    `identifier_code`    VARCHAR(100) DEFAULT NULL COMMENT '标识编码',
    `material_code`      VARCHAR(100) DEFAULT NULL COMMENT '物料编码',
    `specification`      VARCHAR(255) DEFAULT NULL COMMENT '规格型号',
    `supply_type`        VARCHAR(20)  DEFAULT NULL COMMENT '供应方式',
    `originator_name`    VARCHAR(100) DEFAULT NULL COMMENT '提交人姓名',
    `originator_org`     VARCHAR(255) DEFAULT '宁夏中创绿能实业集团有限公司',
    `form_instance_id`   VARCHAR(100) DEFAULT NULL COMMENT '宜搭表单实例ID',
    `form_modified_time` DATETIME     DEFAULT NULL COMMENT '宜搭修改时间',
    `create_time`        DATETIME     DEFAULT CURRENT_TIMESTAMP,
    `update_time`        DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_material_id` (`material_id`),
    UNIQUE KEY `uk_material_spec` (`material_id`, `specification`),
    CONSTRAINT `fk_material` FOREIGN KEY (`material_id`) REFERENCES `product_material` (`id`) ON DELETE CASCADE
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4 COMMENT = '物料规格表';

ALTER TABLE product_material_sku
    DROP FOREIGN KEY fk_material;

DROP TABLE IF EXISTS `product_material_config`;
CREATE TABLE `product_material_config`
(
    `id`          int                                                           NOT NULL AUTO_INCREMENT,
    `config_type` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci  NOT NULL COMMENT '区分类型: MATERIAL_TYPE 或 INVENTORY_CAT',
    `config_name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '显示的名称',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 39
  CHARACTER SET = utf8mb3
  COLLATE = utf8mb3_general_ci COMMENT = '物料信息表配置表'
  ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


alter table production_plan
    add status tinyint default 0 not null comment '状态 0未下发 1已下发';


ALTER TABLE `product-inventory-management-zsjc`.`production_plan`
    DROP COLUMN `data_sync_type`,
    MODIFY COLUMN `data_source_type` tinyint NULL DEFAULT 1 COMMENT '数据来源类型：1=钉钉同步 2=手动新增' AFTER `form_modified_time`;

-- 删除旧表
DROP TABLE IF EXISTS `base_param`;
DROP TABLE IF EXISTS `product_process_param`;

CREATE TABLE `base_param`
(
    `id`           bigint                                                        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `param_key`    varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '参数唯一标识',
    `param_name`   varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '参数名称',
    `param_type`   tinyint                                                       NOT NULL COMMENT '参数类型(1数字 2文本 3下拉选择 4时间)',
    `param_format` varchar(255)                                                  DEFAULT NULL COMMENT '参数格式',
    `value_mode`   tinyint                                                       DEFAULT '1' COMMENT '值模式(1单值 2区间)',
    `unit`         varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '单位',
    `is_required`  tinyint                                                       DEFAULT '0' COMMENT '是否必填',
    `remark`       varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '备注',
    `create_user`  varchar(64)                                                   DEFAULT NULL COMMENT '创建人',
    `create_time`  datetime                                                      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_user`  varchar(64)                                                   DEFAULT NULL COMMENT '修改人',
    `update_time`  datetime                                                      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `tenant_id`    bigint                                                        DEFAULT NULL COMMENT '租户ID',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 10
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci COMMENT ='基础参数定义表';

CREATE TABLE `product_process_param`
(
    `id`             bigint  NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `process_id`     bigint  NOT NULL COMMENT '所属工序ID (product_process.id)',
    `param_id`       bigint  NOT NULL COMMENT '关联基础参数ID (base_param.id)',
    `standard_value` varchar(200)     DEFAULT NULL COMMENT '在此工序设定的标准值(单值模式)',
    `min_value`      decimal(10, 2)   DEFAULT NULL COMMENT '在此工序设定的标准最小值(区间模式)',
    `max_value`      decimal(10, 2)   DEFAULT NULL COMMENT '在此工序设定的标准最大值(区间模式)',
    `is_required`    tinyint NOT NULL DEFAULT '0' COMMENT '在此工序中是否必填(0-否, 1-是)',
    `sort`           int     NOT NULL DEFAULT '0' COMMENT '排序号',
    `tenant_id`      bigint           DEFAULT NULL COMMENT '租户ID',
    `create_time`    datetime         DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`    datetime         DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_process_id` (`process_id`) USING BTREE,
    KEY `idx_param_id` (`param_id`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci COMMENT ='工序绑定参数表';

ALTER TABLE `product_process` MODIFY COLUMN `name` varchar(255) COMMENT '工序名称';
ALTER TABLE `product_process` MODIFY COLUMN `no` varchar(255) COMMENT '工序编号';
ALTER TABLE `product_process` ADD COLUMN `status` tinyint(1) DEFAULT '1' COMMENT '状态：0-停用，1-启用' AFTER `no`;
ALTER TABLE `product_process` MODIFY COLUMN `type` bigint COMMENT '类型：0-计时，1-计件';
ALTER TABLE `product_process` MODIFY COLUMN `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';
ALTER TABLE `product_process` MODIFY COLUMN `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间';

ALTER TABLE `product-inventory-management-zsjc`.`customer`
    ADD COLUMN `form_instance_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '宜搭表单实例ID' AFTER `customer_type`,
    ADD COLUMN `form_modified_time` datetime(0) NULL DEFAULT NULL COMMENT '宜搭修改时间' AFTER `form_instance_id`;

ALTER TABLE `process_route`
    ADD COLUMN `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态：0-草稿，1-已批准' AFTER `bom_id`;

CREATE TABLE `process_route_item_param`
(
    `id`               bigint  NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `route_item_id`    bigint  NOT NULL COMMENT '关联工艺路线明细ID (process_route_item.id)',
    `param_id`         bigint  NOT NULL COMMENT '关联基础参数定义ID (base_param.id)',
    `process_param_id` bigint                                                        DEFAULT NULL COMMENT '来源工序参数ID',

    `standard_value`   varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '此路线节点设定的标准值',
    `min_value`        decimal(10, 2)                                                DEFAULT NULL COMMENT '此路线节点设定的标准最小值',
    `max_value`        decimal(10, 2)                                                DEFAULT NULL COMMENT '此路线节点设定的标准最大值',
    `is_required`      tinyint NOT NULL                                              DEFAULT '0' COMMENT '是否必填',

    `sort`             int     NOT NULL                                              DEFAULT '0' COMMENT '排序',
    `tenant_id`        bigint                                                        DEFAULT NULL COMMENT '租户ID',
    `create_time`      datetime                                                      DEFAULT CURRENT_TIMESTAMP,
    `update_time`      datetime                                                      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_route_item_id` (`route_item_id`) USING BTREE,
    KEY `idx_param_id` (`param_id`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci COMMENT ='工艺路线工序参数表';


ALTER TABLE `product-inventory-management-zsjc`.`quality_inspect`
    ADD COLUMN `inspect_nature` varchar(255) NULL COMMENT '检测性质' AFTER `test_standard_id`,
ADD COLUMN `sample_code` varchar(255) NULL COMMENT '试样编号' AFTER `inspect_nature`,
ADD COLUMN `sample_state` varchar(255) NULL COMMENT '试样状态' AFTER `sample_code`,
ADD COLUMN `sample_time` date NULL COMMENT '取样日期' AFTER `sample_state`,
ADD COLUMN `license_plate_number` varchar(255) NULL COMMENT '车牌号' AFTER `sample_time`;


ALTER TABLE `product_bom`
    ADD COLUMN `dict_code` bigint NOT NULL COMMENT '关联字典数据编码（sys_dict_data.dict_code）';
ALTER TABLE `product_bom` ADD INDEX `idx_dict_code` (`dict_code`);

ALTER TABLE `product-inventory-management-zsjc`.`process_route`
    ADD COLUMN `dict_code` bigint NOT NULL COMMENT '产品类型字典编码' AFTER `status`;


ALTER TABLE `product-inventory-management-zsjc`.`product_order`
    ADD COLUMN `status` int NULL COMMENT '状态（1.待开始、2.进行中、3.已完成、4.已取消）' AFTER `plan_complete_time`;


ALTER TABLE `product-inventory-management-zsjc`.`product_order`
    ADD COLUMN `strength` varchar(255) NULL COMMENT '产品类型' AFTER `plan_complete_time`;

CREATE TABLE `product-inventory-management-zsjc`.`sales_delivery`  (
   `id` int NOT NULL AUTO_INCREMENT,
   `delivery_date` date NULL COMMENT '供货日期',
   `delivery_code` varchar(255) NULL COMMENT '发货单编号',
    `project_name` varchar(255) NULL COMMENT '项目名称',
    `delivery_place` varchar(255) NULL COMMENT '送货地点',
    `link_man` varchar(255) NULL COMMENT '联系人',
    `link_phone` varchar(255) NULL COMMENT '联系电话',
    `delivery_remark` varchar(255) NULL COMMENT '发货备注',
    `material_code` varchar(255) NULL COMMENT '物料代码',
    `product_name` varchar(255) NULL COMMENT '产品名称',
    `model` varchar(255) NULL COMMENT '产品规格',
    `volume` decimal(10, 4) NULL COMMENT '方量',
    `unit_price` decimal(10, 2) NULL COMMENT '单价',
    `price` decimal(10, 2) NULL COMMENT '价格',
    `product_remark` varchar(255) NULL COMMENT '产品备注',
    `customer` varchar(255) NULL COMMENT '客户名称',
    `salesman` varchar(255) NULL COMMENT '业务员',
    `delivery_method` varchar(255) NULL COMMENT '发货方式',
    `form_instance_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '宜搭表单实例ID',
    `form_modified_time` datetime(0) NULL DEFAULT NULL COMMENT '宜搭修改时间',
    `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
    `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0),
    PRIMARY KEY (`id`)
    ) COMMENT = '销售发货明细(宜搭)';


ALTER TABLE product_process_param
    DROP COLUMN param_id;

ALTER TABLE product_process_param
    ADD COLUMN `param_key`    varchar(100) DEFAULT NULL COMMENT '参数唯一标识',
    ADD COLUMN `param_name`   varchar(100) NOT NULL COMMENT '参数名称',
    ADD COLUMN `param_type`   tinyint      NOT NULL COMMENT '参数类型(1数字 2文本 3下拉选择 4时间)',
    ADD COLUMN `param_format` varchar(255) DEFAULT NULL COMMENT '参数格式',
    ADD COLUMN `value_mode`   tinyint      DEFAULT '1' COMMENT '值模式(1单值 2区间)',
    ADD COLUMN `unit`         varchar(50)  DEFAULT NULL COMMENT '单位',
    ADD COLUMN `remark`       varchar(255) DEFAULT NULL COMMENT '备注';

ALTER TABLE process_route_item_param
    DROP COLUMN param_id;

ALTER TABLE process_route_item_param
    ADD COLUMN `param_key`    varchar(100) DEFAULT NULL COMMENT '参数唯一标识',
    ADD COLUMN `param_name`   varchar(100) NOT NULL COMMENT '参数名称',
    ADD COLUMN `param_type`   tinyint      NOT NULL COMMENT '参数类型(1数字 2文本 3下拉选择 4时间)',
    ADD COLUMN `param_format` varchar(255) DEFAULT NULL COMMENT '参数格式',
    ADD COLUMN `value_mode`   tinyint      DEFAULT '1' COMMENT '值模式(1单值 2区间)',
    ADD COLUMN `unit`         varchar(50)  DEFAULT NULL COMMENT '单位',
    ADD COLUMN `remark`       varchar(255) DEFAULT NULL COMMENT '备注';

ALTER TABLE `product-inventory-management-zsjc`.`product_structure`
    MODIFY COLUMN `unit_quantity` decimal(20, 15) NOT NULL COMMENT '单位产出需要数量' AFTER `process_id`,
    MODIFY COLUMN `demanded_quantity` decimal(20, 15) NULL DEFAULT NULL COMMENT '需求数量' AFTER `unit_quantity`,


CREATE TABLE `production_order_route_item`
(
    `id`               bigint NOT NULL AUTO_INCREMENT,
    `order_id`         bigint NOT NULL COMMENT '生产订单id',
    `route_id`         bigint NOT NULL DEFAULT '0' COMMENT '工艺路线id',
    `product_model_id` bigint          DEFAULT '0' COMMENT '产品id',
    `process_id`       bigint NOT NULL DEFAULT '0' COMMENT '工序id',
    `tenant_id`        bigint NOT NULL COMMENT '租户id',
    `create_time`      datetime        DEFAULT NULL COMMENT '录入时间',
    `update_time`      datetime        DEFAULT NULL COMMENT '更新时间',
    `drag_sort`        int             DEFAULT NULL COMMENT '拖动排序',
    `is_quality`       tinyint(1)      DEFAULT '1' COMMENT '是否质检工序',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_order_id` (`order_id`)
) ENGINE = InnoDB
  AUTO_INCREMENT = 83
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci
  ROW_FORMAT = DYNAMIC
    COMMENT ='生产订单绑定的工艺路线--工序表';

CREATE TABLE `production_order_route_item_param`
(
    `id`               bigint                                                        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `order_id`         bigint                                                                 DEFAULT NULL COMMENT '生产订单ID',
    `route_item_id`    bigint                                                        NOT NULL COMMENT '关联工艺路线明细ID (production_order_route_item.id)',
    `process_param_id` bigint                                                                 DEFAULT NULL COMMENT '来源工序参数ID',
    `param_key`        varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci          DEFAULT NULL COMMENT '参数唯一标识',
    `param_name`       varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '参数名称',
    `param_type`       tinyint                                                       NOT NULL COMMENT '参数类型(1数字 2文本 3下拉选择 4时间)',
    `param_format`     varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci          DEFAULT NULL COMMENT '参数格式',
    `value_mode`       tinyint                                                                DEFAULT '1' COMMENT '值模式(1单值 2区间)',
    `unit`             varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci           DEFAULT NULL COMMENT '单位',
    `remark`           varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci          DEFAULT NULL COMMENT '备注',
    `standard_value`   varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci          DEFAULT NULL COMMENT '标准值',
    `min_value`        decimal(10, 2)                                                         DEFAULT NULL COMMENT '此路线节点设定的标准最小值',
    `max_value`        decimal(10, 2)                                                         DEFAULT NULL COMMENT '此路线节点设定的标准最大值',
    `is_required`      tinyint                                                       NOT NULL DEFAULT '0' COMMENT '是否必填',
    `sort`             int                                                           NOT NULL DEFAULT '0' COMMENT '排序',
    `tenant_id`        bigint                                                                 DEFAULT NULL COMMENT '租户ID',
    `create_time`      datetime                                                               DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`      datetime                                                               DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_route_item_id` (`route_item_id`) USING BTREE,
    KEY `idx_param_key` (`param_key`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci
  ROW_FORMAT = DYNAMIC
    COMMENT ='生产订单绑定的工艺路线工序--参数表';

CREATE TABLE `production_order_structure`
(
    `id`                bigint         NOT NULL AUTO_INCREMENT,
    `order_id`          bigint         NOT NULL COMMENT '生产订单id',
    `parent_id`         bigint                                                        DEFAULT NULL COMMENT '父节点ID',
    `product_model_id`  bigint                                                        DEFAULT NULL COMMENT '产品id',
    `process_id`        bigint                                                        DEFAULT NULL COMMENT '工序id',
    `unit_quantity`     decimal(16, 4) NOT NULL COMMENT '单位产出需要数量',
    `demanded_quantity` decimal(16, 4)                                                DEFAULT NULL COMMENT '需求数量',
    `unit`              varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '单位',
    `tenant_id`         bigint                                                        DEFAULT NULL COMMENT '租户id',
    `bom_id`            bigint         NOT NULL COMMENT 'bom的id',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_order_id` (`order_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci
  ROW_FORMAT = DYNAMIC
    COMMENT ='生产订单绑定的BOM子表';

DROP TABLE IF EXISTS process_route_item_instance;
DROP TABLE IF EXISTS process_route_item_param_instance;
DROP TABLE IF EXISTS product_structure_instance;

ALTER TABLE `product-inventory-management-zsjc`.`production_product_route_item_param`
    MODIFY COLUMN `order_item_param_id` bigint NULL DEFAULT NULL COMMENT '生产订单绑定的工艺路线工序--参数表ID' AFTER `production_product_route_item_id`;

CREATE TABLE `production_product_input`
(
    `id`              bigint          NOT NULL AUTO_INCREMENT,
    `product_main_id` bigint          NOT NULL COMMENT '报工单主表ID',
    `route_item_id`   bigint      DEFAULT NULL COMMENT '关联具体的报工工序ID (对应原参数表的关联ID)',
    `product_id`      bigint          NOT NULL COMMENT '产品/物料ID',
    `bom_id`          bigint      DEFAULT NULL COMMENT 'BOM ID',
    `quantity`        decimal(30, 15) NOT NULL COMMENT '投入数量 (对应原product_value)',
    `unit`            varchar(20) DEFAULT NULL COMMENT '单位',
    `create_user`     bigint      DEFAULT NULL COMMENT '录入人',
    `create_time`     datetime    DEFAULT CURRENT_TIMESTAMP COMMENT '录入时间',
    `update_time`     datetime    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `tenant_id`       bigint          NOT NULL COMMENT '租户ID',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `idx_main_id` (`product_main_id`),
    KEY `idx_product_id` (`product_id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT ='生产报工物料投入表';

ALTER TABLE `production_product_route_item_param`
    DROP COLUMN `product_id`,
    DROP COLUMN `bom_id`,
    DROP COLUMN `product_value`,
    DROP COLUMN `dict_code`;

ALTER TABLE `product-inventory-management-zsjc`.`production_product_output`
    ADD COLUMN `total_quantity` decimal(20, 15) NULL COMMENT '总数量' AFTER `scrap_qty`;