CREATE TABLE "user" (
	"name" varchar(255)
);
CREATE TABLE "task" (
	"stage_id" int CONSTRAINT stage_id REFERENCES stage(id),
	"name" varchar(255),
	"intro_file" varchar(255),
	"team_work?" bool
);
CREATE TABLE "stage" (
	"name" varchar(255),
	"info" varchar(255),
	"file_link" varchar(255),
	"project_id" int CONSTRAINT project_id REFERENCES project(id),
	"group_id" int,
	"published_at" bool
);
CREATE TABLE "team" (
	"project_id" int CONSTRAINT project_id REFERENCES project(id),
	"num" int,
	"group_id" int CONSTRAINT group_id REFERENCES group(id),
	"name" varchar(255)
);
CREATE TABLE "project" (
	"name" varchar(255),
	"info" varchar(255),
	"start_date" datetime,
	"end_date" datetime,
	"category_id" int CONSTRAINT category_id REFERENCES category(id),
	"semester_id" int CONSTRAINT semester_id REFERENCES semester(id)
);
CREATE TABLE "group" (
	"name" varchar(255)
);
CREATE TABLE "participation" (
	"project_id" int CONSTRAINT project_id REFERENCES project(id),
	"user_id" int,
	"status" varchar(255),
	"start_date" datetime,
	"quit_date" datetime
);
CREATE TABLE "upload" (
	"task_id" int CONSTRAINT task_id REFERENCES task(id),
	"team_id" int CONSTRAINT team_id REFERENCES team(id),
	"user_id" int,
	"file_link" varchar(255),
	"who_upload" varchar(255),
	"deadline" varchar(255),
	"upload_time" varchar(255),
	"status" varchar(255)
);
CREATE TABLE "category" (
	"name" varchar(255)
);
CREATE TABLE "出席紀錄" (
	"user_id" int CONSTRAINT user_id REFERENCES user(id),
	"stage_id" int CONSTRAINT stage_id REFERENCES stage(id)
);
CREATE TABLE "teammateship" (
	"user_id" int CONSTRAINT user_id REFERENCES user(id),
	"team_id" int CONSTRAINT team_id REFERENCES team(id),
	"has_quit?" bool,
	"quit_date" datetime
);
CREATE TABLE "semester" (
	"name" varchar(255)
);
CREATE TABLE "project_groupship" (
	"project_id" int CONSTRAINT project_id REFERENCES project(id),
	"group_id" int CONSTRAINT group_id REFERENCES group(id)
);
CREATE TABLE "assignment" (
	"stage_id" int CONSTRAINT stage_id REFERENCES stage(id),
	"task_id" int CONSTRAINT task_id REFERENCES task(id),
	"deadline" datetime,
	"published_at" datetime,
	"team_work?" bool CONSTRAINT team_work? REFERENCES team(work?)
);
CREATE TABLE "stage_groupship" (
	"stage_id" int CONSTRAINT stage_id REFERENCES stage(id),
	"group_id" int CONSTRAINT group_id REFERENCES group(id),
	"published_at" varchar(255) CONSTRAINT published_at REFERENCES published(at)
);