DROP TABLE IF EXISTS "blade_ng_reason_info";
CREATE TABLE "blade_ng_reason_info"
(
    "id"          BIGINT NOT NULL,
    "tenant_id"   VARCHAR(12) DEFAULT '000000',
    "type_id"     BIGINT,
    "reason_code" VARCHAR(24),
    "reason_name" VARCHAR(64),
    "create_user" BIGINT,
    "create_dept" BIGINT,
    "create_time" TIMESTAMP(0),
    "update_user" BIGINT,
    "update_time" TIMESTAMP(0),
    "remark"      VARCHAR(255),
    "is_deleted"  INT,
    "status"      INT,
    NOT           CLUSTER PRIMARY KEY("id"),
    UNIQUE ("reason_code", "is_deleted")
) STORAGE(ON "MAIN", CLUSTERBTR);

COMMENT
ON TABLE "blade_ng_reason_info" IS 'NG原因维护信息表';
COMMENT
ON COLUMN "blade_ng_reason_info"."create_dept" IS '创建单位';
COMMENT
ON COLUMN "blade_ng_reason_info"."create_time" IS '创建时间';
COMMENT
ON COLUMN "blade_ng_reason_info"."create_user" IS '创建人';
COMMENT
ON COLUMN "blade_ng_reason_info"."id" IS 'id';
COMMENT
ON COLUMN "blade_ng_reason_info"."reason_code" IS '原因编号';
COMMENT
ON COLUMN "blade_ng_reason_info"."reason_name" IS '原因名称';
COMMENT
ON COLUMN "blade_ng_reason_info"."remark" IS '备注';
COMMENT
ON COLUMN "blade_ng_reason_info"."status" IS '业务状态';
COMMENT
ON COLUMN "blade_ng_reason_info"."tenant_id" IS '租户ID';
COMMENT
ON COLUMN "blade_ng_reason_info"."type_id" IS '父类型id(暂没用,现使用的是公共组表做关联)';
COMMENT
ON COLUMN "blade_ng_reason_info"."update_time" IS '更新时间';
COMMENT
ON COLUMN "blade_ng_reason_info"."update_user" IS '更新人';

DROP TABLE IF EXISTS "blade_trace_flow_record";
CREATE TABLE "blade_trace_flow_record"
(
    "id"                    BIGINT      NOT NULL,
    "remark"                VARCHAR(255),
    "create_user"           BIGINT,
    "create_dept"           BIGINT,
    "create_time"           TIMESTAMP(0),
    "update_user"           BIGINT,
    "update_time"           TIMESTAMP(0),
    "status"                INT,
    "is_deleted"            INT,
    "tenant_id"             VARCHAR(10) DEFAULT '000000',
    "part_no"               VARCHAR(50) NOT NULL,
    "flow_code"             VARCHAR(50) NOT NULL,
    "flow_display_name"     VARCHAR(50) NOT NULL,
    "flow_state"            INT,
    "flow_tag"              INT,
    "trace_flow_setting_id" BIGINT,
    "workstation_id"        BIGINT,
    "workstation_code"      VARCHAR(50),
    "workstation_name"      VARCHAR(50),
    "workbench_id"          BIGINT,
    "workbench_name"        VARCHAR(50),
    "entry_time"            TIMESTAMP(0),
    "left_time"             TIMESTAMP(0),
    "extension_data"        TEXT,
    "archive_table"         VARCHAR(50),
    "position"              VARCHAR(50),
    NOT                     CLUSTER PRIMARY KEY("id")
) STORAGE(ON "MAIN", CLUSTERBTR);

COMMENT
ON TABLE "blade_trace_flow_record" IS '工件流程记录';
COMMENT
ON COLUMN "blade_trace_flow_record"."entry_time" IS '进入时间';
COMMENT
ON COLUMN "blade_trace_flow_record"."extension_data" IS '数据';
COMMENT
ON COLUMN "blade_trace_flow_record"."flow_code" IS '流程编号';
COMMENT
ON COLUMN "blade_trace_flow_record"."flow_display_name" IS '流程名称';
COMMENT
ON COLUMN "blade_trace_flow_record"."flow_state" IS '流程状态:1 完成  2 进行中';
COMMENT
ON COLUMN "blade_trace_flow_record"."flow_tag" IS '流程标识:1 质检合格 2 质检不合格';
COMMENT
ON COLUMN "blade_trace_flow_record"."left_time" IS '离开时间';
COMMENT
ON COLUMN "blade_trace_flow_record"."part_no" IS '工件编号';
COMMENT
ON COLUMN "blade_trace_flow_record"."remark" IS '备注';
COMMENT
ON COLUMN "blade_trace_flow_record"."trace_flow_setting_id" IS '流程设定ID';
COMMENT
ON COLUMN "blade_trace_flow_record"."workbench_id" IS '工作台id';
COMMENT
ON COLUMN "blade_trace_flow_record"."workbench_name" IS '工作台名称';
COMMENT
ON COLUMN "blade_trace_flow_record"."workstation_code" IS '工位编号';
COMMENT
ON COLUMN "blade_trace_flow_record"."POSITION" IS '位置';
COMMENT
ON COLUMN "blade_trace_flow_record"."workstation_id" IS '工位id';
COMMENT
ON COLUMN "blade_trace_flow_record"."workstation_name" IS '工位名称';


DROP TABLE IF EXISTS "blade_trace_catalog";
CREATE TABLE "blade_trace_catalog"
(
    "id"                   BIGINT      NOT NULL,
    "remark"               VARCHAR(255),
    "create_user"          BIGINT,
    "create_dept"          BIGINT,
    "create_time"          TIMESTAMP(0),
    "update_user"          BIGINT,
    "update_time"          TIMESTAMP(0),
    "status"               INT,
    "is_deleted"           INT,
    "tenant_id"            VARCHAR(10) DEFAULT '000000',
    "part_no"              VARCHAR(50) NOT NULL,
    "batch_number"         VARCHAR(50),
    "online_time"          TIMESTAMP(0),
    "offline_time"         TIMESTAMP(0),
    "qualified"            TINYINT,
    "is_rework_part"       TINYINT,
    "shift_index"          INT,
    "shift_index_name"     VARCHAR(50),
    "plan_id"              INT,
    "trace_status"         INT,
    "workstation_group_id" BIGINT,
    NOT                    CLUSTER PRIMARY KEY("id")
) STORAGE(ON "MAIN", CLUSTERBTR);

COMMENT
ON TABLE "blade_trace_catalog" IS '工件上线记录';
COMMENT
ON COLUMN "blade_trace_catalog"."batch_number" IS '批次号';
COMMENT
ON COLUMN "blade_trace_catalog"."is_rework_part" IS '是否返工：0 否 1 是';
COMMENT
ON COLUMN "blade_trace_catalog"."offline_time" IS '下线时间';
COMMENT
ON COLUMN "blade_trace_catalog"."online_time" IS '上线时间';
COMMENT
ON COLUMN "blade_trace_catalog"."part_no" IS '工件编号';
COMMENT
ON COLUMN "blade_trace_catalog"."plan_id" IS '计划id,无计划填0';
COMMENT
ON COLUMN "blade_trace_catalog"."qualified" IS '是否合格：0 否 1 是';
COMMENT
ON COLUMN "blade_trace_catalog"."remark" IS '备注';
COMMENT
ON COLUMN "blade_trace_catalog"."shift_index" IS '班次下标';
COMMENT
ON COLUMN "blade_trace_catalog"."shift_index_name" IS '班次下标别名';
COMMENT
ON COLUMN "blade_trace_catalog"."trace_status" IS '工件转态：1 已下线 2 未下线 3 未知';
COMMENT
ON COLUMN "blade_trace_catalog"."workstation_group_id" IS '工位组id';

-- 流程维护
DROP TABLE IF EXISTS "blade_trace_flow_settings";
CREATE TABLE "blade_trace_flow_settings"
(
    "id"                     BIGINT NOT NULL,
    "tenant_id"              VARCHAR(12),
    "code"                   VARCHAR(40),
    "name"                   VARCHAR(100),
    "flow_seq"               INT,
    "station_type"           INT,
    "workstation_group_id"   BIGINT,
    "pre_flow_id"            BIGINT,
    "next_flow_id"           BIGINT,
    "flow_type"              BIGINT,
    "trigger_end_flow_style" INT,
    "quality_result_offline" VARCHAR(32),
    "quality_maker_flow_id"  BIGINT,
    "is_deleted"             INT,
    "create_time"            TIMESTAMP(0),
    "create_user"            BIGINT,
    "update_time"            TIMESTAMP(0),
    "update_user"            BIGINT,
    "create_dept"            BIGINT,
    "status"                 INT,
    "extension_data"         TEXT,
    not                      CLUSTER PRIMARY KEY ("id"),
    UNIQUE ("code", "is_deleted"),
    CHECK ("flow_type" >= 0)
) STORAGE(ON "MAIN", CLUSTERBTR);

COMMENT
ON TABLE "blade_trace_flow_settings" IS '流程维护';
COMMENT
ON COLUMN "blade_trace_flow_settings"."code" IS '流程编号';
COMMENT
ON COLUMN "blade_trace_flow_settings"."create_dept" IS '创建部门';
COMMENT
ON COLUMN "blade_trace_flow_settings"."create_time" IS '创建时间';
COMMENT
ON COLUMN "blade_trace_flow_settings"."create_user" IS '创建人';
COMMENT
ON COLUMN "blade_trace_flow_settings"."extension_data" IS '数据';
COMMENT
ON COLUMN "blade_trace_flow_settings"."flow_seq" IS '流程顺序';
COMMENT
ON COLUMN "blade_trace_flow_settings"."flow_type" IS '流程类别 1-上线流程 2-途径流程 3-下线流程 4-可选流程';
COMMENT
ON COLUMN "blade_trace_flow_settings"."name" IS '流程名称';
COMMENT
ON COLUMN "blade_trace_flow_settings"."next_flow_id" IS '下一流程id';
COMMENT
ON COLUMN "blade_trace_flow_settings"."pre_flow_id" IS '上一流程id';
COMMENT
ON COLUMN "blade_trace_flow_settings"."quality_maker_flow_id" IS '质量责任归属到特定流程';
COMMENT
ON COLUMN "blade_trace_flow_settings"."quality_result_offline" IS '按质量结果下线 1-OK下线 2-NG下线';
COMMENT
ON COLUMN "blade_trace_flow_settings"."station_type" IS '工位类型 0-其他 1-打标 2-测量 3-清洗 4-装卸站 5-加工 6-搬运 7-三坐标';
COMMENT
ON COLUMN "blade_trace_flow_settings"."status" IS '业务状态';
COMMENT
ON COLUMN "blade_trace_flow_settings"."tenant_id" IS '所属租户';
COMMENT
ON COLUMN "blade_trace_flow_settings"."trigger_end_flow_style" IS '结束流程方式 1-仅本流程 2-仅上个流程 3-本流程和上个流程';
COMMENT
ON COLUMN "blade_trace_flow_settings"."update_time" IS '更新时间';
COMMENT
ON COLUMN "blade_trace_flow_settings"."update_user" IS '更新人';
COMMENT
ON COLUMN "blade_trace_flow_settings"."workstation_group_id" IS '工位组id';

-- 追溯关联工位
DROP TABLE IF EXISTS "blade_trace_related_workstation";
CREATE TABLE "blade_trace_related_workstation"
(
    "id"                    BIGINT NOT NULL,
    "workstation_id"        BIGINT NOT NULL,
    "workbench_id"          BIGINT NOT NULL,
    "trace_flow_setting_id" BIGINT NOT NULL,
    "machining_position"    VARCHAR(255),
    NOT                     CLUSTER PRIMARY KEY("id")
) STORAGE(ON "MAIN", CLUSTERBTR);

COMMENT
ON TABLE "blade_trace_related_workstation" IS '追溯关联工位';
COMMENT
ON COLUMN "blade_trace_related_workstation"."machining_position" IS '加工位置';
COMMENT
ON COLUMN "blade_trace_related_workstation"."trace_flow_setting_id" IS '流程设定ID';
COMMENT
ON COLUMN "blade_trace_related_workstation"."workbench_id" IS '工作台id';
COMMENT
ON COLUMN "blade_trace_related_workstation"."workstation_id" IS '工位id';

-- 工件追溯NG表
DROP TABLE IF EXISTS "blade_trace_record_ng_reason";
CREATE TABLE "blade_trace_record_ng_reason"
(
    "id"               BIGINT NOT NULL,
    "record_id"        BIGINT,
    "bad_part_id"      BIGINT,
    "bad_part"         VARCHAR(32),
    "reason_id"        BIGINT,
    "reason_code"      VARCHAR(24),
    "reason_name"      VARCHAR(64),
    "create_user_name" VARCHAR(255),
    "tenant_id"        VARCHAR(12) DEFAULT '000000',
    "status"           TINYINT     DEFAULT 1,
    "is_deleted"       DECIMAL(20, 0),
    "create_user"      BIGINT,
    "create_dept"      BIGINT,
    "create_time"      TIMESTAMP(0),
    "update_user"      BIGINT,
    "update_time"      TIMESTAMP(0),
    NOT                CLUSTER PRIMARY KEY("id"),
    CHECK ("is_deleted" >= 0)
) STORAGE(ON "MAIN", CLUSTERBTR);

COMMENT
ON TABLE "blade_trace_record_ng_reason" IS '工件流程NG原因表';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."bad_part" is '不良部位';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."bad_part_id" IS '不良部位id';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."create_dept" IS '创建部门';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."create_time" IS '创建时间';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."create_user" IS '创建人';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."create_user_name" IS '创建用户名称';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."is_deleted" IS '删除标记：0-未删除，1-已删除';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."reason_code" IS '原因编号';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."reason_id" IS '原因id';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."reason_name" IS '原因名称';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."record_id" IS '工件流程记录id';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."status" IS '业务状态 1激活 0 冻结';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."tenant_id" IS '租户ID';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."update_time" IS '更新时间';
COMMENT
ON COLUMN "blade_trace_record_ng_reason"."update_user" IS '更新人';



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);

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

CREATE INDEX record_default_index ON blade_trace_flow_record (part_no, workstation_id, entry_time);

CREATE INDEX record_flag_index ON blade_trace_flow_record (flow_tag, part_no, workstation_id);

CREATE INDEX ng_reason_default_index ON blade_trace_record_ng_reason (record_id);