CREATE TABLE `Caregiver` (
	`uid` int,
	`User_id` int,
	`自我介紹` longtext,
	`經歷` longtext,
	`專長` varchar(255),
	`證書字號` varchar(255),
	`證照期限` varchar(255),
	`受訓單位` varchar(255),
	FOREIGN KEY(`User_id`) REFERENCES `User` (`id`)
);
CREATE TABLE `User` (
	`uid` int,
	`姓名` varchar(255),
	`性別` varchar(255),
	`age` varchar(255),
	`line ID` varchar(255),
	`電話` varchar(255),
	`first_name` varchar(255),
	`last_name` varchar(255),
	`照片` varchar(255)
);
CREATE TABLE `Requester` (
	`uid` int,
	`User_id` int,
	`地址` varchar(255),
	`狀態簡介` longtext,
	FOREIGN KEY(`User_id`) REFERENCES `User` (``)
);
CREATE TABLE `Demand` (
	`uid` int PRIMARY KEY,
	`Demand_category_id` int,
	`demand_name` varchar(255),
	`-------` varchar(255),
	`關懷服務(□陪伴□情緒支持)` varchar(255),
	`個人健康照顧(□盥洗□入浴□如廁□更衣□餵食□肢體關節運動□翻身□拍背)` varchar(255),
	`家務服務(□洗衣□服務對象起居環境清潔□陪同或代理購物 □備餐（購買/加熱）□備餐（煮食）)` varchar(255),
	`文書服務(□協助申請社會福利服務□代寫書信及聯絡親友)` varchar(255),
	`醫務服務(□陪同就醫□代領藥品 □保健服務（□量血壓□量血糖＊相關器材乙方自備）)` varchar(255),
	`休閒服務(□陪同散步□閱讀書報)` varchar(255),
	`其他服務` varchar(255),
	FOREIGN KEY(`Demand_category_id`) REFERENCES `Demand_category` (`id`)
);
CREATE TABLE `Schedule` (
	`uid` int PRIMARY KEY,
	`Caregiver_id` int,
	`scheduled_date` date,
	FOREIGN KEY(`Caregiver_id`) REFERENCES `Caregiver` (`id`)
);
CREATE TABLE `Event` (
	`uid` int PRIMARY KEY,
	`Schedule_id` datetime,
	`Requester_id` int,
	`event_name` varchar(255),
	`time` varchar(255),
	`Important` bool,
	FOREIGN KEY(`Schedule_id`) REFERENCES `Schedule` (`id`),
	FOREIGN KEY(`Requester_id`) REFERENCES `Requester` (`id`)
);
CREATE TABLE `Medication` (
	`uid` int PRIMARY KEY,
	`Requester_id` int,
	`description` longtext,
	`photo` varchar(255),
	`time_id` int,
	FOREIGN KEY(`Requester_id`) REFERENCES `Requester` (`id`),
	FOREIGN KEY(`time_id`) REFERENCES `Medication_time` (``)
);
CREATE TABLE `time_zone` (
	`uid` int PRIMARY KEY,
	`zone` varchar(255),
	`-----` varchar(255),
	`0000` varchar(255),
	`0030` varchar(255),
	`0100` varchar(255),
	`0130` varchar(255),
	`0200` varchar(255),
	`0230` varchar(255),
	`0300` varchar(255),
	`2300` varchar(255),
	`2330` varchar(255),
	FOREIGN KEY(`zone`) REFERENCES `time_zone` (`name`)
);
CREATE TABLE `Comment` (
	`uid` int PRIMARY KEY,
	`description` varchar(255),
	`Caregiver_id` int,
	`Requester_id` int,
	`Schedule_id` int,
	`category` varchar(255),
	`Comment_category_id` int,
	FOREIGN KEY(`Caregiver_id`) REFERENCES `Caregiver` (`id`),
	FOREIGN KEY(`Requester_id`) REFERENCES `Requester` (`id`),
	FOREIGN KEY(`Schedule_id`) REFERENCES `Schedule` (`id`),
	FOREIGN KEY(`Comment_category_id`) REFERENCES `Comment_category` (`id`)
);
CREATE TABLE `time_eventship` (
	`uid` int PRIMARY KEY,
	`time_zone_id` int,
	`Event_id` int,
	FOREIGN KEY(`time_zone_id`) REFERENCES `time_zone` (`id`),
	FOREIGN KEY(`Event_id`) REFERENCES `Event` (`id`)
);
CREATE TABLE `Event_demandship` (
	`uid` int PRIMARY KEY,
	`Event_id` int,
	`demand_id` int,
	FOREIGN KEY(`Event_id`) REFERENCES `Event` (`id`),
	FOREIGN KEY(`demand_id`) REFERENCES `需求_技能` (`id`)
);
CREATE TABLE `Demand_category` (
	`uid` int PRIMARY KEY,
	`name` varchar(255)
);
CREATE TABLE `Comment_category` (
	`uid` int PRIMARY KEY,
	`name` varchar(255)
);
CREATE TABLE `Health_record` (
	`uid` int,
	`Requester_id` int,
	`Caregiver_id` int,
	`systolic_data` int,
	`diastolic_data` int,
	`heart_rate` int,
	`blood_sugar` varchar(255),
	`height` varchar(255),
	`weight` varchar(255),
	FOREIGN KEY(`Requester_id`) REFERENCES `Requester` (``),
	FOREIGN KEY(`Caregiver_id`) REFERENCES `Caregiver` (`id`)
);
CREATE TABLE `Medication_time` (
	`uid` int PRIMARY KEY,
	`name` varchar(255),
	`-----` varchar(255),
	`早餐飯前` varchar(255),
	`早餐飯後` varchar(255),
	`午餐飯前` varchar(255),
	`午餐飯後` varchar(255),
	`晚餐飯前` varchar(255),
	`晚餐飯後` varchar(255),
	`睡前` varchar(255)
);
CREATE TABLE `Order` (
	`uid` int PRIMARY KEY,
	`Caregiver_id` int,
	`Requester_Id` int,
	`status` varchar(255),
	FOREIGN KEY(`Caregiver_id`) REFERENCES `Caregiver` (`id`),
	FOREIGN KEY(`Requester_Id`) REFERENCES `Requester` (`Id`)
);