# DDL

### User 儲存使用者的帳號資料

| Attributes | meaning |
| ------------ | ------------------------------------ |
| ++userId++ | 作為 PK，hash 過的學號 (16進位 32碼) |
| name | 使用者顯示的名字 |
| email | 使用者的信箱 |
| role | 使用者的帳號權限身分 |
| contribution | 貢獻得分 |
| createTime | 創建時間 |
| googleId | 使用 Google OAuth 產生的 id |

CREATE TABLE if not exists user (
    userId        int not null auto_increment,
    name          varchar(16) not null,
    email         varchar(40) not null,
    role          tinyint check (role >= 0 and role <= 2) default 2,
    contribution  int not null default 0,
    createTime    datetime not null,
    googleId      varchar(32) not null,
    primary key (userId),
    unique key (email)
  );

### Document 考古題資料

| Attributes | meaning |
| ---------------- | -------------------------------------------- |
| ++docId++ | 作為 PK，生成一組考古題的 ID |
| courseId | 所屬課程的 course_id |
| userId | 上傳者的 ID |
| year | 考古題所屬學年 |
| semester | 考古題所屬學期 |
| title | 考古題的名稱，例如：期中考題、期末、A/B 卷等 |
| description | 關於此份考古題的詳細說明 |
| createTime | 創建時間 |
| lastUpdateTime | 最後更新時間 |
| folderPath | 考古題檔案路徑 |

CREATE TABLE if not exists document (
    docId               int not null,
    courseId            int not null default -1,
    userId              int not null default -1,
    year                smallint not null,
    semester            tinyint not null,
    title               varchar(64) not null,
    description         varchar(4096) default '',
    createTime          datetime not null,
    lastUpdateTime      datetime not null,
    folderPath          varchar(1024) not null,
    primary key (docId),
    foreign key (docId) references comment_area(areaId) on update cascade on delete cascade,
    foreign key (courseId) references course (courseId) on update cascade on delete set default,
    foreign key (userId) references user (userId) on update cascade on delete set default
  );

### Announcement 布告欄

| Attributes | meaning |
| ---------------- | -------------------------- |
| annId | 作為 PK，生成一組公告的 ID |
| userId | 上傳者的 ID |
| title | 公告標題 |
| content | 公告內容 |
| pinned | 是否訂選 |
| createTime | 創建時間 |
| lastUpdateTime | 最後更新時間 |

CREATE TABLE if not exists announcement (
    annId               int not null auto_increment,
    userId              int not null default -1,
    title               varchar(64) not null,
    content             varchar(4096) not null default '',
    pinned              bool not null default false,
    createTime          datetime not null,
    lastUpdateTime      datetime not null,
    primary key (annId),
    foreign key (userId) references user (userId) on update cascade on delete set default
  );

### Comment 留言

| Attributes | meaning |
| ---------------- | -------------------------- |
| ++comId++ | 作為 PK，生成一組公告的 ID |
| replyId | 回復的該則留言的 com_id |
| userId | 上傳者的 ID |
| content | 留言內容 |
| createTime | 創建時間 |
| lastUpdateTime | 最後更新時間 |

CREATE TABLE if not exists comment (
    comId               int not null,
    replyId             int not null,
    userId              int not null default -1,
    content             varchar(4096) not null,
    createTime          datetime not null,
    lastUpdateTime      datetime not null,
    primary key (comId),
    foreign key (comId) references comment_area(areaId) on update cascade on delete cascade,
    foreign key (replyId) references comment_area(areaId) on update cascade on delete cascade,
    foreign key (userId) references user (userId) on update cascade on delete set default
  );

### Comment area 留言區
|Attributes|meaning|
|--|--|
|++areaId++|作為 PK，生成一組 id 表示該留言區|

CREATE TABLE if not exists comment_area (
    areaId        int not null auto_increment,
    primary key (areaId)
  );

### Course 課程

| Attributes | meaning |
| ----------- | ------------------------------------- |
| ++courseId++ | 作為 PK，生成一組ID表示該課程 |
| courseName | 課程名字 |
| deptName | 開課系所 |
|category|分類（上／下學期）|
| description | 課程資訊 |

CREATE TABLE if not exists course (
    courseId      int not null auto_increment,
    courseName    varchar(32) not null,
    deptName      varchar(32) not null,
    category      varchar(16) default '',
    description   varchar(4096) default '',
    primary key (courseId)
  );

### vote 評分

| Attributes | meaning |
| ---------- | -------------------------- |
| userId | 評分者的 userId |
| docId | 被評分的考古題的 docId |
| score | 1 or -1，代表正評或負評 |

CREATE TABLE if not exists vote (
    userId        int not null,
    docId         int not null,
    score         smallint check (score = -1 or score = 1),
    primary key (userId, docId),
    foreign key (userId) references user(userId) on update cascade on delete cascade,
    foreign key (docId) references document(docId) on update cascade on delete cascade
  );