SET foreign_key_checks = 0;
# 需求执行者
drop table if exists treenode_processor;
create table treenode_processor
(
    id           int auto_increment,
    PRIMARY KEY (id),
    tree_node_id int not null ,
    processor_id int not null,
    processor_name varchar(64) not null
);
insert into treenode_processor(tree_node_id, processor_id, processor_name) VALUES (1,1,"qinliu");

# 需求节点
drop table if exists tree_nodes;
create table tree_nodes
(
    id           int auto_increment,
    title        varchar(24)                        not null,
    description  varchar(512)                       null,
    type         varchar(32)      default 0                 not null,
    priority     int      default 0                 null,
    state        varchar(32)      default 0                 not null,
    time_to_take int      default 0                 not null,
    start_time   datetime default CURRENT_TIMESTAMP null,
    end_time     datetime                           null,
    is_leaf      int      default 0                 null,
    group_id     int                                null,
    project_id   int                                null,
    deep         int                                null,
    parent_id     int                                null,
    constraint tree_nodes_id_uindex
        unique (id)
);

alter table tree_nodes
    add primary key (id);
insert into tree_nodes(title, description, type, priority, state, time_to_take, start_time, end_time, is_leaf, group_id, project_id, deep,parent_id) values ("test1","test1","REQUIREMENT",0,"PROCESSING",10,"2021-1-1","2021-2-13",1,1,1,1,1);
insert into tree_nodes(title, description, type, priority, state, time_to_take, start_time, end_time, is_leaf, group_id, project_id, deep,parent_id) values ("test2","test2","REQUIREMENT",0,"PROCESSING",10,"2021-1-2","2021-2-13",1,1,1,1,1);
insert into tree_nodes(title, description, type, priority, state, time_to_take, start_time, end_time, is_leaf, group_id, project_id, deep,parent_id) values ("test3","test3","REQUIREMENT",0,"PROCESSING",10,"2021-1-3","2021-2-13",1,1,1,1,1);

# 信息 留言
drop table if exists messages;
drop table if exists tree_messages;
create table tree_messages
(
    message_id   int auto_increment,
    creator      varchar(64)  not null,
    tree_node_id int          null,
    detail       varchar(512) null,
    constraint tree_messages_message_id_uindex
        unique (message_id),
    constraint tree_node_fk
        foreign key (tree_node_id) references tree_nodes (id)
            on update cascade on delete cascade
);
alter table tree_messages
    add primary key (message_id);

drop table if exists tree_sides;
create table tree_sides
(
    side_id   int auto_increment,
    parent_id int null,
    child_id  int null,
    constraint tree_sides_side_id_uindex
        unique (side_id),
    constraint child_fk
        foreign key (child_id) references tree_nodes (id)
            on update cascade on delete cascade,
    constraint parent_fk
        foreign key (parent_id) references tree_nodes (id)
            on update cascade on delete cascade
);
# 树边，暂时用不到了
alter table tree_sides
    add primary key (side_id);

insert into tree_sides(parent_id, child_id) VALUES (1,2);
insert into tree_sides(parent_id, child_id) VALUES (1,3);

SET foreign_key_checks = 1;