CREATE TABLE "被照顧者" (
	"uid" int PRIMARY KEY,
	"User_id" int FOREIGN KEY("User_id") REFERENCES "User" ("id"),
	"地址" varchar(255),
	"緊急聯絡人" varchar(255),
	"緊急聯絡人電話" int,
	"基本狀況描述" varchar(255)
);
CREATE TABLE "User" (
	"uid" int PRIMARY KEY,
	"name" varchar(255),
	"description" varchar(255),
	"email" varchar(255),
	"age" int,
	"category_id" int,
	"group_id" int,
	"create_at" datetime,
	"update_at" datetime,
	"fb_uid" varchar(255),
	"fb_token" varchar(255)
);
CREATE TABLE "照顧者" (
	"uid" int PRIMARY KEY,
	"User_id" int FOREIGN KEY("User_id") REFERENCES "User" ("id"),
	"photo" varchar(255),
	"經歷" varchar(255),
	"專長" varchar(255),
	"服務地區" varchar(255),
	"證書字號" varchar(255),
	"證照期限" date,
	"受訓單位" varchar(255)
);
CREATE TABLE "被照顧者_照顧者_關聯" (
	"被照顧者_id" int FOREIGN KEY("被照顧者_id") REFERENCES "被照顧者" ("id"),
	"照顧者_id" int FOREIGN KEY("照顧者_id") REFERENCES "照顧者" ("id")
);
CREATE TABLE "家屬" (
	"uid" int PRIMARY KEY,
	"User_id" int FOREIGN KEY("User_id") REFERENCES "User" ("id"),
	"被照顧者_id" int FOREIGN KEY("被照顧者_id") REFERENCES "被照顧者" ("id"),
	"關係" varchar(255)
);
CREATE TABLE "被照顧者_照顧者_Like" (
	"uid" int PRIMARY KEY,
	"被照顧者_id" int FOREIGN KEY("被照顧者_id") REFERENCES "被照顧者" ("id"),
	"照顧者_id" int FOREIGN KEY("照顧者_id") REFERENCES "照顧者" ("id")
);
CREATE TABLE "家屬_照顧者_Like" (
	"uid" int PRIMARY KEY,
	"照顧者_id" int FOREIGN KEY("照顧者_id") REFERENCES "照顧者" ("id"),
	"家屬_id" int FOREIGN KEY("家屬_id") REFERENCES "家屬" ("id")
);
CREATE TABLE "Comments" (
	"uid" int PRIMARY KEY,
	"被照顧者_id" int FOREIGN KEY("被照顧者_id") REFERENCES "被照顧者" (""),
	"家屬_id" varchar(255) FOREIGN KEY("家屬_id") REFERENCES "家屬" (""),
	"照顧者_id" int FOREIGN KEY("照顧者_id") REFERENCES "照顧者" ("id"),
	"description" varchar(255)
);
CREATE TABLE "健康紀錄" (
	"uid" int PRIMARY KEY,
	"被照顧者_id" int FOREIGN KEY("被照顧者_id") REFERENCES "被照顧者" ("id"),
	"舒張壓" varchar(255),
	"收縮壓" varchar(255),
	"血糖" varchar(255),
	"BMI" varchar(255),
	"其他狀況" varchar(255),
	"日期" date
);
CREATE TABLE "身體狀況" (
	"uid" int PRIMARY KEY,
	"健康紀錄_id" int FOREIGN KEY("健康紀錄_id") REFERENCES "健康紀錄" ("id"),
	"狀況(良好, 中等,稍差 ，請多留意觀察,差，需請醫生評估)" varchar(255)
);
CREATE TABLE "照顧者_技能_關係" (
	"uid" int PRIMARY KEY,
	"需求_技能_id" int FOREIGN KEY("需求_技能_id") REFERENCES "需求_技能" (""),
	"照顧者_id" int FOREIGN KEY("照顧者_id") REFERENCES "照顧者" ("id")
);
CREATE TABLE "需求_技能" (
	"uid" int PRIMARY KEY,
	"關懷服務(□陪伴□情緒支持)" varchar(255),
	"個人健康照顧(□盥洗□入浴□如廁□更衣□餵食□肢體關節運動□翻身□拍背)" varchar(255),
	"家務服務(□洗衣□服務對象起居環境清潔□陪同或代理購物 □備餐（購買/加熱）□備餐（煮食）)" varchar(255),
	"文書服務(□協助申請社會福利服務□代寫書信及聯絡親友)" varchar(255),
	"醫務服務(□陪同就醫□代領藥品 □保健服務（□量血壓□量血糖＊相關器材乙方自備）)" varchar(255),
	"休閒服務(□陪同散步□閱讀書報)" varchar(255),
	"每週服務時間" datetime,
	"被照顧者_id" int FOREIGN KEY("被照顧者_id") REFERENCES "被照顧者" (""),
	"照顧者_id" int FOREIGN KEY("照顧者_id") REFERENCES "照顧者" ("")
);
CREATE TABLE "被照顧者_需求_關係" (
	"uid" int PRIMARY KEY,
	"被照顧者_id" int FOREIGN KEY("被照顧者_id") REFERENCES "被照顧者" ("id"),
	"需求_技能_id" int FOREIGN KEY("需求_技能_id") REFERENCES "需求_技能" ("id")
);
CREATE TABLE "服用藥物" (
	"uid" int PRIMARY KEY,
	"被照顧者_id" int FOREIGN KEY("被照顧者_id") REFERENCES "被照顧者" ("id"),
	"藥單photo" varchar(255),
	"單次服用photo" varchar(255)
);
CREATE TABLE "藥物服用時間" (
	"uid" int PRIMARY KEY,
	"服用藥物_id" int FOREIGN KEY("服用藥物_id") REFERENCES "服用藥物" ("id"),
	"名稱(早餐飯前、早餐飯後、午餐飯前、午餐飯後、晚餐飯前、晚餐飯後、睡前)" varchar(255)
);
CREATE TABLE "訂單" (
	"uid" int PRIMARY KEY,
	"照顧者_id" int FOREIGN KEY("照顧者_id") REFERENCES "照顧者" ("id"),
	"被照顧者_id" int FOREIGN KEY("被照顧者_id") REFERENCES "被照顧者" ("id")
);
CREATE TABLE "照顧者有空時間/被照顧者服務時間" (
	"uid" int PRIMARY KEY,
	"訂單_id" int FOREIGN KEY("訂單_id") REFERENCES "訂單" ("id"),
	"照顧者_id" int FOREIGN KEY("照顧者_id") REFERENCES "照顧者" ("id"),
	"日期" date,
	"開始時間(half hour a unit)" varchar(255),
	"結束時間(half hour a unit)" varchar(255)
);