CREATE TABLE "user" (
	"name" varchar(255)
);
CREATE TABLE "task" (
	"stage_id" int FOREIGN KEY("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 FOREIGN KEY("project_id") REFERENCES "project" ("id"),
	"group_id" int,
	"published_at" bool
);
CREATE TABLE "team" (
	"project_id" int FOREIGN KEY("project_id") REFERENCES "project" ("id"),
	"num" int,
	"group_id" int FOREIGN KEY("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 FOREIGN KEY("category_id") REFERENCES "category" ("id"),
	"semester_id" int FOREIGN KEY("semester_id") REFERENCES "semester" ("id")
);
CREATE TABLE "group" (
	"name" varchar(255)
);
CREATE TABLE "participation" (
	"project_id" int FOREIGN KEY("project_id") REFERENCES "project" ("id"),
	"user_id" int,
	"status" varchar(255),
	"start_date" datetime,
	"quit_date" datetime
);
CREATE TABLE "upload" (
	"task_id" int FOREIGN KEY("task_id") REFERENCES "task" ("id"),
	"team_id" int FOREIGN KEY("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 FOREIGN KEY("user_id") REFERENCES "user" ("id"),
	"stage_id" int FOREIGN KEY("stage_id") REFERENCES "stage" ("id")
);
CREATE TABLE "teammateship" (
	"user_id" int FOREIGN KEY("user_id") REFERENCES "user" ("id"),
	"team_id" int FOREIGN KEY("team_id") REFERENCES "team" ("id"),
	"has_quit?" bool,
	"quit_date" datetime
);
CREATE TABLE "semester" (
	"name" varchar(255)
);
CREATE TABLE "project_groupship" (
	"project_id" int FOREIGN KEY("project_id") REFERENCES "project" ("id"),
	"group_id" int FOREIGN KEY("group_id") REFERENCES "group" ("id")
);
CREATE TABLE "assignment" (
	"stage_id" int FOREIGN KEY("stage_id") REFERENCES "stage" ("id"),
	"task_id" int FOREIGN KEY("task_id") REFERENCES "task" ("id"),
	"deadline" datetime,
	"published_at" datetime,
	"team_work?" bool FOREIGN KEY("team_work?") REFERENCES "team" ("work?")
);
CREATE TABLE "stage_groupship" (
	"stage_id" int FOREIGN KEY("stage_id") REFERENCES "stage" ("id"),
	"group_id" int FOREIGN KEY("group_id") REFERENCES "group" ("id"),
	"published_at" varchar(255) FOREIGN KEY("published_at") REFERENCES "published" ("at")
);