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