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;