DROP TABLE IF EXISTS `blade_ng_reason_info`;
CREATE TABLE `blade_ng_reason_info`
(
    `id`          bigint(20) NOT NULL COMMENT 'id',
    `tenant_id`   varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '000000' COMMENT '租户ID',
    `type_id`     bigint(20) NULL DEFAULT NULL COMMENT '父类型id(暂没用,现使用的是公共组表做关联)',
    `reason_code` varchar(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '原因编号',
    `reason_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '原因名称',
    `create_user` bigint(20) NULL DEFAULT NULL COMMENT '创建人',
    `create_dept` bigint(20) NULL DEFAULT NULL COMMENT '创建单位',
    `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
    `update_user` bigint(20) NULL DEFAULT NULL COMMENT '更新人',
    `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
    `remark`      varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
    `is_deleted`  int(11) NULL DEFAULT NULL,
    `status`      int(11) NULL DEFAULT NULL COMMENT '业务状态',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `uniqueCodeIndex` (`reason_code`, `is_deleted`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT = 'NG原因维护信息表'
  ROW_FORMAT = Dynamic;

DROP TABLE IF EXISTS `blade_trace_flow_record`;
CREATE TABLE `blade_trace_flow_record`
(
    `id`                    bigint                                                       NOT NULL AUTO_INCREMENT,
    `remark`                varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci      DEFAULT NULL COMMENT '备注',
    `create_user`           bigint                                                       DEFAULT NULL,
    `create_dept`           bigint                                                       DEFAULT NULL,
    `create_time`           datetime                                                     DEFAULT NULL,
    `update_user`           bigint                                                       DEFAULT NULL,
    `update_time`           datetime                                                     DEFAULT NULL,
    `status`                int                                                          DEFAULT NULL,
    `is_deleted`            int                                                          DEFAULT NULL,
    `tenant_id`             varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci       DEFAULT '000000',
    `part_no`               varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工件编号',
    `flow_code`             varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '流程编号',
    `flow_display_name`     varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '流程名称',
    `flow_state`            int                                                          DEFAULT NULL COMMENT '流程状态:1 完成  2 进行中',
    `flow_tag`              int                                                          DEFAULT NULL COMMENT '流程标识:1 质检合格 2 质检不合格',
    `trace_flow_setting_id` bigint                                                       DEFAULT NULL COMMENT '流程设定ID',
    `workstation_id`        bigint                                                       DEFAULT NULL COMMENT '工位id',
    `workstation_code`      varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '工位编号',
    `workstation_name`      varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '工位名称',
    `workbench_id`          bigint                                                       DEFAULT NULL COMMENT '工作台id',
    `workbench_name`        varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '工作台名称',
    `entry_time`            datetime                                                     DEFAULT NULL COMMENT '进入时间',
    `left_time`             datetime                                                     DEFAULT NULL COMMENT '离开时间',
    `extension_data`        text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '数据',
    `archive_table`         varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
    `position`              varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '位置',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 12
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci
  ROW_FORMAT = DYNAMIC COMMENT ='工件流程记录';

DROP TABLE IF EXISTS `blade_trace_catalog`;
CREATE TABLE `blade_trace_catalog`
(
    `id`                   bigint                                                       NOT NULL AUTO_INCREMENT,
    `remark`               varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci      DEFAULT NULL COMMENT '备注',
    `create_user`          bigint                                                       DEFAULT NULL,
    `create_dept`          bigint                                                       DEFAULT NULL,
    `create_time`          datetime                                                     DEFAULT NULL,
    `update_user`          bigint                                                       DEFAULT NULL,
    `update_time`          datetime                                                     DEFAULT NULL,
    `status`               int                                                          DEFAULT NULL,
    `is_deleted`           int                                                          DEFAULT NULL,
    `tenant_id`            varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci       DEFAULT '000000',
    `part_no`              varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工件编号',
    `batch_number`         varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '批次号',
    `online_time`          datetime                                                     DEFAULT NULL COMMENT '上线时间',
    `offline_time`         datetime                                                     DEFAULT NULL COMMENT '下线时间',
    `qualified`            tinyint                                                      DEFAULT NULL COMMENT '是否合格：0 否 1 是',
    `is_rework_part`       tinyint                                                      DEFAULT NULL COMMENT '是否返工：0 否 1 是',
    `shift_index`          int                                                          DEFAULT NULL COMMENT '班次下标',
    `shift_index_name`     varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '班次下标别名',
    `plan_id`              int                                                          DEFAULT NULL COMMENT '计划id,无计划填0',
    `trace_status`         int                                                          DEFAULT NULL COMMENT '工件转态：1 已下线 2 未下线 3 未知',
    `workstation_group_id` bigint                                                       DEFAULT NULL COMMENT '工位组id',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 34
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci
  ROW_FORMAT = DYNAMIC COMMENT ='工件上线记录';

DROP TABLE IF EXISTS `blade_trace_flow_settings`;
CREATE TABLE `blade_trace_flow_settings`
(
    `id`                     bigint NOT NULL,
    `tenant_id`              varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '所属租户',
    `code`                   varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '流程编号',
    `name`                   varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程名称',
    `flow_seq`               int                                                           DEFAULT NULL COMMENT '流程顺序',
    `station_type`           int                                                           DEFAULT NULL COMMENT '工位类型 0-其他 1-打标 2-测量 3-清洗 4-装卸站 5-加工 6-搬运 7-三坐标',
    `workstation_group_id`   bigint                                                        DEFAULT NULL COMMENT '工位组id',
    `pre_flow_id`            bigint                                                        DEFAULT NULL COMMENT '上一流程id',
    `next_flow_id`           bigint                                                        DEFAULT NULL COMMENT '下一流程id',
    `flow_type`              int(10) unsigned zerofill DEFAULT NULL COMMENT '流程类别 1-上线流程 2-途径流程 3-下线流程 4-可选流程',
    `trigger_end_flow_style` int                                                           DEFAULT NULL COMMENT '结束流程方式 1-仅本流程 2-仅上个流程 3-本流程和上个流程',
    `quality_result_offline` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '按质量结果下线 1-OK下线 2-NG下线',
    `quality_maker_flow_id`  bigint                                                        DEFAULT NULL COMMENT '质量责任归属到特定流程',
    `is_deleted`             int                                                           DEFAULT NULL,
    `create_time`            datetime                                                      DEFAULT NULL COMMENT '创建时间',
    `create_user`            bigint                                                        DEFAULT NULL COMMENT '创建人',
    `update_time`            datetime                                                      DEFAULT NULL COMMENT '更新时间',
    `update_user`            bigint                                                        DEFAULT NULL COMMENT '更新人',
    `create_dept`            bigint                                                        DEFAULT NULL COMMENT '创建部门',
    `status`                 int                                                           DEFAULT NULL COMMENT '业务状态',
    `extension_data`         text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci COMMENT '数据',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE KEY `uniqueCodeIndex` (`code`, `is_deleted`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci
  ROW_FORMAT = DYNAMIC COMMENT ='流程维护';

-- 追溯关联工位
DROP TABLE IF EXISTS `blade_trace_related_workstation`;
CREATE TABLE `blade_trace_related_workstation`
(
    `id`                    bigint NOT NULL,
    `workstation_id`        bigint NOT NULL COMMENT '工位id',
    `workbench_id`          bigint NOT NULL COMMENT '工作台id',
    `trace_flow_setting_id` bigint NOT NULL COMMENT '流程设定ID',
    `machining_position`    varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '加工位置',
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT = '追溯关联工位'
  ROW_FORMAT = DYNAMIC;

-- 工件追溯NG表
DROP TABLE if EXISTS `blade_trace_record_ng_reason`;
CREATE TABLE `blade_trace_record_ng_reason`
(
    `id`               bigint NOT NULL,
    `record_id`        bigint                                  DEFAULT NULL COMMENT '工件流程记录id',
    `bad_part_id`      bigint                                  DEFAULT NULL COMMENT '不良部位id',
    `bad_part`         varchar(32) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '不良部位',
    `reason_id`        bigint                                  DEFAULT NULL COMMENT '原因id',
    `reason_code`      varchar(24) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '原因编号',
    `reason_name`      varchar(64) COLLATE utf8mb4_general_ci  DEFAULT NULL COMMENT '原因名称',
    `create_user_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建用户名称',
    `tenant_id`        varchar(12) COLLATE utf8mb4_general_ci  DEFAULT '000000' COMMENT '租户ID',
    `status`           tinyint                                 DEFAULT '1' COMMENT '业务状态 1激活 0 冻结',
    `is_deleted`       bigint unsigned DEFAULT NULL COMMENT '删除标记：0-未删除，1-已删除',
    `create_user`      bigint                                  DEFAULT NULL COMMENT '创建人',
    `create_dept`      bigint                                  DEFAULT NULL COMMENT '创建部门',
    `create_time`      datetime                                DEFAULT NULL COMMENT '创建时间',
    `update_user`      bigint                                  DEFAULT NULL COMMENT '更新人',
    `update_time`      datetime                                DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT ='工件流程NG原因表';

INSERT INTO `blade_menu`(`id`, `parent_id`, `code`, `name`, `alias`, `path`, `source`, `sort`,
                         `category`, `action`, `is_open`, `remark`, `is_deleted`, `belong_application`, `keep_alive`)
VALUES (1518863601934606338, 0, 'trace', '工件追溯', 'trace', '/trace', 'iconfont saber-zhushuju', 8, 0, 0, 1, '', 0,
        'CPS',
        1),
       (1518864188126339074, 1518863601934606338, 'trace_query', '追溯查询', 'trace_query', '/trace/query/index',
        'iconfont iconicon_search', 1, 1, 0, 1, '', 0, 'CPS', 1),
       (1519183072176939010, 1518863601934606338, 'processMaintenance', '流程维护', 'processMaintenance',
        '/trace/maintain/index', 'iconfont saber-caijifenxi', 3, 1, 0, 1, '', 0, 'CPS', 1),
       (1522377449602215938, 1518863601934606338, 'ng', 'NG原因维护', 'ng', '/trace/ng-info/index',
        'iconfont iconicon_setting', 4, 1, 0, 1, '', 0, 'CPS', 1),
       (1523465621521539073, 1518863601934606338, 'ng_trace', 'NG工件追溯', 'ng_trace', '/trace/ng-trace/index',
        'iconfont iconicon_ding', 99, 1, 0, 1, '', 0, 'CPS', 1);


CREATE UNIQUE INDEX default_index ON `blade_trace_catalog` (`part_no`, `batch_number`, `workstation_group_id`,
                                                            `online_time`, `offline_time`) USING BTREE;

CREATE INDEX time_index ON `blade_trace_catalog` (`online_time`, `offline_time`, `part_no`, `batch_number`) USING BTREE;

CREATE INDEX default_index ON `blade_trace_flow_record` (`part_no`, `workstation_id`, `entry_time`) USING BTREE;

CREATE INDEX flag_index ON `blade_trace_flow_record` (`flow_tag`, `part_no`, `workstation_id`) USING BTREE;

CREATE INDEX default_index ON `blade_trace_record_ng_reason` (`record_id`) USING BTREE;
