drop table if exists device_model_relation;
CREATE TABLE device_model_relation
(
    id                   BIGINT NOT NULL,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    device_id            BIGINT,
    algo_model_id        BIGINT,
    is_use               SMALLINT,
    threshold            DECIMAL(8,2),
    confidence_level     DECIMAL(8,2),
    recognition_type_id  BIGINT,
    boundary             TEXT,
    remark               TEXT,
    PRIMARY KEY (id)
);
drop table if exists box_device_relation;
CREATE TABLE box_device_relation
(
    id                   BIGINT NOT NULL,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    box_id               BIGINT,
    recognition_interval INT,
    alarm_interval       INT,
    device_id            BIGINT,
    type                 SMALLINT,
    rtsp_url             VARCHAR(255),
    recognition_url      VARCHAR(255),
    boundary             CLOB,
    PRIMARY KEY (id)
);

drop table if exists device_scene_relation;
CREATE TABLE device_scene_relation
(
    id                   BIGINT NOT NULL,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    device_id            BIGINT,
    scene_id             BIGINT,
    boundary             TEXT,
    remark               TEXT,
    PRIMARY KEY (id)
);
drop table if exists algo_model;
create table algo_model
(
    id                   bigint not null,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    model_name           text,
    version              text,
    path                 text,
    remark               text,
    handle_task          varchar(64),
    code                 varchar(64),
    primary key (id)
);

drop table if exists scene;

/*==============================================================*/
/* Table: scene                                                  */
/*==============================================================*/
create table scene
(
    id                   bigint not null,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    scene_name           text,
    version              text,
    remark               text,
    handle_task          varchar(64),
    scene_code           varchar(64),
    primary key (id)
);


drop table if exists model_recognition_type;

/*==============================================================*/
/* Table: model_recognition_type                                */
/*==============================================================*/
create table model_recognition_type
(
    id                   bigint not null,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    type_name            varchar(32),
    type_code            varchar(32),
    model_id             bigint,
    primary key (id)
);


drop table if exists box_device;

create table box_device
(
    id                   bigint not null,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    code                 varchar(64),
    device_type          varchar(32),
    ip                   varchar(32),
    area                 varchar(32),
    dept_id              bigint,
    longitude            decimal(10,6),
    latitude             decimal(10,6),
    device_status        smallint,
    box_name             varchar(255),
    primary key (id)
);

drop table if exists risk_point_info;

create table risk_point_info
(
    id                   bigint not null,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    risk_name            varchar(32),
    longitude            decimal(10,6),
    latitude             decimal(10,6),
    dept_id              bigint,
    description          text,
    content              text,
    area                 varchar(32),
    nature               text,
    grade                varchar(32),
    assign_post          varchar(32),
    staff_quota          varchar(32),
    substances_quantities varchar(255),
    risk_type            smallint,
    classification_type  bigint,
    classification_type2 bigint,
    address              varchar(255),
    secret_level         smallint,
    venue_level          varchar(255),
    hazardous_chemicals  varchar(255),
    stock                varchar(255),
    critical_quantity    varchar(64),
    grading_index        varchar(64),
    hazard_level         varchar(64),
    hazardous_chemical_site varchar(255),
    venue_classification varchar(255),
    quantitative         varchar(255),
    fixed_lifespan       varchar(255),
    regular_inspection   varchar(255),
    hazardous_work       varchar(255),
    work_level           varchar(255),
    hazardous_remark     varchar(255),
    relevant_departments varchar(255),
    operation_frequency  varchar(64),
    work_procedure       varchar(64),
    worker_num           varchar(64),
    accident_type        varchar(64),
    primary key (id)
);


drop table if exists alarm_record;

create table alarm_record
(
    id                   bigint not null,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    device_id            bigint,
    algo_model_id        bigint,
    recognition_type_id  bigint,
    file_path            varchar(255),
    alarm_time           TIMESTAMP,
    scene_id             varchar(32),
    alarm_context        text
);

-- alter table alarm_record comment '设备告警记录'; -- 这行也被移除了，因为神通数据库不支持注释

drop table if exists risk_rel_device;

create table risk_rel_device
(
    id                   bigint not null,
    risk_id              bigint,
    create_time          TIMESTAMP,
    device_id            bigint,
    primary key (id)
);

drop table if exists hazard_classification_info;

create table hazard_classification_info
(
    id                   bigint not null,
    create_time          TIMESTAMP,
    update_time          TIMESTAMP,
    pid                  bigint,
    hazard_name          varchar(64),
    hazard_code          varchar(64),
    primary key (id)
);

-- drop table if exists sys_user_group;
--
-- create table sys_user_group
-- (
--     id                   bigint,
--     group_name           varchar(256),
--     create_time          TIMESTAMP,
--     dept_id              bigint,
--     create_id            bigint,
--     description          varchar(256),
--     safe                 smallint
-- );

drop table if exists sys_group_rel_user;

create table sys_group_rel_user
(
    id                   bigint,
    group_id             bigint,
    user_id              bigint,
    create_time          timestamp
);


drop table if exists sys_group_device;

create table sys_group_device
(
    id                   bigint,
    group_id             bigint,
    create_time          timestamp,
    device_id            bigint
);