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")
);