create database ji_she;
use ji_she;

# 普通用户表
CREATE TABLE students
(
    student_id         INT PRIMARY KEY,
    name               VARCHAR(255) NOT NULL,
    authority          INT CHECK (authority BETWEEN 0 AND 1),
    email              VARCHAR(255),
    sex                CHAR(1) CHECK (sex IN ('F', 'M', 'U')),
    birthday           DATE,
    brief_introduction TEXT,
    nickname           VARCHAR(30) UNIQUE,
    avatar             VARCHAR(255),
    current_company    VARCHAR(255),
    position           VARCHAR(255)
);

# 用户标签表
CREATE TABLE tags_for_students
(
    tag_id   INT PRIMARY KEY,
    tag_name VARCHAR(255) NOT NULL UNIQUE
);

# 普通用户-标签关联表
CREATE TABLE students_tags
(
    student_tag_id INT PRIMARY KEY AUTO_INCREMENT,
    tag_id         INT,
    student_id     INT,
    FOREIGN KEY (tag_id) REFERENCES tags_for_students (tag_id),
    FOREIGN KEY (student_id) REFERENCES students (student_id)
);

# 管理员表
CREATE TABLE managers
(
    manager_id INT PRIMARY KEY,
    name       VARCHAR(255) NOT NULL,
    sex        CHAR(1) CHECK (sex IN ('F', 'M', 'U')),
    avatar     VARCHAR(255),
    username   VARCHAR(255) UNIQUE,
    password   VARCHAR(255) NOT NULL
);

# 帖子详情表
CREATE TABLE posts
(
    post_id       INT PRIMARY KEY,
    student_id    INT,
    title         VARCHAR(255) NOT NULL,
    content       TEXT,
    created_at    DATETIME,
    updated_at    DATETIME,
    status        INT CHECK (status BETWEEN 0 AND 2),
    is_share      BOOLEAN,
    share_post_id INT,
    like_num      INT DEFAULT 0,
    dislike       INT DEFAULT 0,
    FOREIGN KEY (student_id) REFERENCES students (student_id)
);

# 收藏帖子表
CREATE TABLE collections
(
    collection_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id       INT,
    student_id    INT,
    FOREIGN KEY (post_id) REFERENCES posts (post_id),
    FOREIGN KEY (student_id) REFERENCES students (student_id)
);

# 评论表
CREATE TABLE comments
(
    comment_id        INT PRIMARY KEY AUTO_INCREMENT,
    post_id           INT,
    parent_comment_id INT DEFAULT NULL,
    student_id        INT,
    content           TEXT NOT NULL,
    created_at        DATETIME,
    like_num          INT DEFAULT 0,
    dislike           INT DEFAULT 0,
    FOREIGN KEY (post_id) REFERENCES posts (post_id),
    FOREIGN KEY (student_id) REFERENCES students (student_id),
    FOREIGN KEY (parent_comment_id) REFERENCES comments (comment_id)
);

# 帖子标签表
CREATE TABLE tags_for_posts
(
    tag_id   INT PRIMARY KEY,
    tag_name VARCHAR(255) NOT NULL UNIQUE
);

# 帖子-标签关联表
CREATE TABLE posts_tags
(
    post_tag_id INT PRIMARY KEY AUTO_INCREMENT,
    tag_id      INT,
    post_id     INT,
    FOREIGN KEY (tag_id) REFERENCES tags_for_posts (tag_id),
    FOREIGN KEY (post_id) REFERENCES posts (post_id)
);

# 用户关注表
CREATE TABLE star_relations
(
    id              INT PRIMARY KEY AUTO_INCREMENT,
    student_id      INT,
    star_student_id INT,
    FOREIGN KEY (student_id) REFERENCES students (student_id),
    FOREIGN KEY (star_student_id) REFERENCES students (student_id)
);

# 学生token表
create table student_token
(
    student_id int primary key,
    token      varchar(255),
    foreign key (student_id) references students (student_id)
);

# 管理员token表
create table manager_token
(
    manager_id int primary key,
    token      varchar(255),
    foreign key (manager_id) references managers (manager_id)
);