create table if not exists devcloud.func_test_case
(
    id         int auto_increment
        primary key,
    project_id int                  not null,
    title      varchar(63)          null,
    pass       tinyint(1) default 0 not null
) CHARSET=utf8;

create table if not exists devcloud.func_test_record
(
    id                 int auto_increment
        primary key,
    timestamp          timestamp default CURRENT_TIMESTAMP not null,
    operation_type     varchar(15)                         null,
    pipeline_record_id int                                 null
) CHARSET=utf8;

create table if not exists devcloud.func_test_step
(
    id           int auto_increment
        primary key,
    test_case_id int         null,
    description  text        null,
    expectation  text        null,
    state        varchar(11) null,
    constraint test_step_test_case_id_fk
        foreign key (test_case_id) references devcloud.func_test_case (id)
            on delete set null
) CHARSET=utf8;

create table if not exists devcloud.project
(
    id             int          not null
        primary key,
    name           varchar(128) null,
    description    varchar(512) null,
    git_remote_url varchar(512) not null,
    constraint project_name_uindex
        unique (name)
)
    charset = utf8;

create table if not exists devcloud.commit
(
    id              varchar(127) not null
        primary key,
    message         text         null,
    title           text         null,
    timestamp       varchar(63)  null,
    gitlab_username varchar(31)  null,
    related_id      int          null,
    project_id      int          null,
    related_type    varchar(11)  null,
    email           varchar(63)  null,
    constraint commit_project_id_fk
        foreign key (project_id) references devcloud.project (id)
) CHARSET=utf8;

create table if not exists devcloud.pipeline
(
    id            int auto_increment
        primary key,
    project_id    int         null,
    name          varchar(47) null,
    config_json   text        null,
    template_name varchar(31) null,
    constraint pipeline_project_id_fk
        foreign key (project_id) references devcloud.project (id)
            on delete set null
) CHARSET=utf8;

create table if not exists devcloud.deployment
(
    id            int auto_increment
        primary key,
    pipeline_id   int         not null,
    namespace     varchar(63) null,
    deploy_name   varchar(63) null,
    deployed_time timestamp   null,
    access_url    text        null,
    constraint deployment_pipeline_id_uindex
        unique (pipeline_id)
) CHARSET=utf8;

create table if not exists devcloud.project_related
(
    id           int auto_increment
        primary key,
    project_id_a int null,
    project_id_b int null,
    constraint project_related_project_id_fk
        foreign key (project_id_a) references devcloud.project (id)
            on delete cascade,
    constraint project_related_project_id_fk_2
        foreign key (project_id_b) references devcloud.project (id)
            on delete cascade
) CHARSET=utf8;

create table if not exists devcloud.stage
(
    id          int auto_increment
        primary key,
    title       varchar(31)          null,
    description text                 null,
    name        varchar(63)          null,
    skippable   tinyint(1) default 0 not null
) CHARSET=utf8;

create table if not exists devcloud.stage_config
(
    id       int auto_increment
        primary key,
    name     varchar(63) null,
    value    text        null,
    editable tinyint(1)  null,
    stage_id int         null,
    constraint stage_config_stage_id_fk
        foreign key (stage_id) references devcloud.stage (id)
) CHARSET=utf8;

create table if not exists devcloud.test_info
(
    test_id     int auto_increment
        primary key,
    test_name   varchar(255) not null,
    project_id  int          not null,
    username    varchar(255) not null,
    create_time datetime     not null,
    url         varchar(255) not null,
    params      json         null,
    qps         int          not null,
    method      varchar(255) not null
) CHARSET=utf8;

create table if not exists devcloud.test_result
(
    id                 int auto_increment
        primary key,
    test_id            int           not null,
    execute_time       datetime      not null,
    success            int default 0 not null,
    failure            int default 0 not null,
    time_list          text          not null,
    max_time           int           not null,
    min_time           int           not null,
    average_time       double        not null,
    pipeline_record_id int           null
) CHARSET=utf8;

create table if not exists devcloud.tree_node
(
    id           int auto_increment
        primary key,
    title        varchar(24)                           not null,
    description  varchar(512)                          null,
    type         varchar(32) default '0'               not null,
    priority     varchar(32) default '0'               null,
    state        varchar(32) default '0'               not null,
    time_to_take int         default 0                 not null,
    start_time   datetime    default CURRENT_TIMESTAMP not null,
    end_time     datetime                              null,
    leaf         tinyint(1)  default 0                 null,
    project_id   int                                   null,
    deep         int                                   null,
    parent_id    int                                   null,
    processor_id int                                   null
) CHARSET=utf8;

create table if not exists devcloud.user
(
    id          int         not null
        primary key,
    username    varchar(31) null,
    phone       varchar(11) null,
    email       varchar(63) null,
    role        varchar(11) null,
    with_gitlab tinyint(1)  null
) CHARSET=utf8;

create table if not exists devcloud.bug_list
(
    id           int auto_increment
        primary key,
    title        varchar(31)                         null,
    description  text                                null,
    time_to_take mediumtext                          null,
    start_time   timestamp default CURRENT_TIMESTAMP not null,
    end_time     timestamp                           null,
    priority     varchar(10)                         null,
    state        varchar(10)                         null,
    project_id   int                                 null,
    processor_id int                                 null,
    constraint bug_list_project_id_fk
        foreign key (project_id) references devcloud.project (id),
    constraint bug_list_user_id_fk
        foreign key (processor_id) references devcloud.user (id)
) CHARSET=utf8;

create table if not exists devcloud.pipeline_record
(
    id          int auto_increment
        primary key,
    pipeline_id int                                 null,
    start_time  timestamp default CURRENT_TIMESTAMP not null,
    user_id     int                                 null,
    result      varchar(15)                         null,
    details     longtext                            null,
    constraint pipeline_record_pipeline_id_fk
        foreign key (pipeline_id) references devcloud.pipeline (id)
            on delete set null,
    constraint pipeline_record_user_id_fk
        foreign key (user_id) references devcloud.user (id)
            on delete set null
)charset = utf8;

create table if not exists devcloud.code_review
(
    id int auto_increment primary key ,
    project_id int not null ,
    source_branch varchar(40) not null,
    target_branch varchar(40) not null,
    creator_id int not null,
    reviewer_id int not null,
    status varchar(10) default '0' not null,
    title varchar(20) not null,
    description varchar(255) null,
    delete_source_branch boolean default 0 null,
    create_time timestamp default current_timestamp not null
)charset = utf8;

create table if not exists devcloud.commit_quality
(
    id                              int             auto_increment
        primary key,
    project_id                      int             not null,
    commit_hash                     varchar(127)    not null,
    sonar_project_key               varchar(127)    not null,
    check_result                    varchar(31)     not null,
    check_time                      datetime default CURRENT_TIMESTAMP not null,
    result_url                      varchar(255)    null,
    quality_conditions_json         varchar(1023)   null
) CHARSET=utf8;
create table if not exists devcloud.file_review
(
    id int auto_increment primary key ,
    project_id int not null,
    creator_id int not null ,
    reviewer_id text not null ,
    files text not null ,
    branch varchar(40) not null ,
    create_time timestamp default current_timestamp not null ,
    title varchar(20) not null ,
    status varchar(15) not null
)charset = utf8;

create table if not exists devcloud.comment
(
    id int auto_increment primary key ,
    code_review_id int not null ,
    creator_id int not null,
    creator_name varchar(30),
    content text,
    time timestamp default current_timestamp not null,
    comment_type varchar(10) default 'Comment' not null,
    reply_id int default '0' not null
)charset = utf8;

create table if not exists devcloud.message
(
    id int auto_increment primary key ,
    creator_id int not null ,
    receiver_id int not null ,
    content text,
    link_to text not null,
    create_time timestamp default current_timestamp not null ,
    status varchar(10) default 'New' not null
)charset = utf8;

create table if not exists devcloud.inspection_result
(
    id int auto_increment primary key ,
    file_review_id int not null ,
    reviewer_id int not null,
    pass int not null
)charset = utf8;

create table if not exists devcloud.interface_test_info(
                                       `id` int(11) NOT NULL AUTO_INCREMENT,
                                       `name` varchar(255) NOT NULL,
                                       `project_id` int(11) NOT NULL,
                                       `create_time` datetime NOT NULL,
                                       `username` varchar(255) NOT NULL,
                                       `url` varchar(255) NOT NULL,
                                       `method` varchar(255) NOT NULL,
                                       `params` json DEFAULT NULL,
                                       PRIMARY KEY (`id`)
)charset = utf8;

CREATE TABLE if not exists devcloud.interface_test_result (
                                         `id` int(11) NOT NULL AUTO_INCREMENT,
                                         `test_id` int(11) NOT NULL,
                                         `execute_time` datetime NOT NULL,
                                         `username` varchar(255) NOT NULL,
                                         `code` int(11) NOT NULL,
                                         `cost` int(11) NOT NULL,
                                         `result` longtext,
                                         `deployment_id` int(11) DEFAULT NULL,
                                         PRIMARY KEY (`id`)
)charset = utf8;