
CREATE TABLE `APPOINTMENTS` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '掛號編號',
  `patient_id` varchar(12) NOT NULL COMMENT '病患編號',
  `doctor_id` varchar(10) NOT NULL COMMENT '醫師編號',
  `room_id` varchar(5) DEFAULT NULL,
  `type` enum('網路掛號','現場掛號','診間預掛') NOT NULL COMMENT '掛號類型',
  `appt_date` date NOT NULL COMMENT '預約看診日期',
  `appt_no` int(10) unsigned NOT NULL COMMENT '預約看診編號',
  `status` enum('預約','完成','看診中','取消') NOT NULL DEFAULT '預約' COMMENT '掛號狀態',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_medrec` (`patient_id`),
  KEY `doctor_id` (`doctor_id`),
  KEY `room_id` (`room_id`),
  CONSTRAINT `APPOINTMENTS_ibfk_1` FOREIGN KEY (`patient_id`) REFERENCES `PATIENTS` (`patient_id`),
  CONSTRAINT `APPOINTMENTS_ibfk_2` FOREIGN KEY (`doctor_id`) REFERENCES `EMPLOYEES` (`employee_id`),
  CONSTRAINT `APPOINTMENTS_ibfk_3` FOREIGN KEY (`room_id`) REFERENCES `ROOMS` (`room_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='掛號紀錄表';

CREATE TABLE `EMPLOYEES` (
  `employee_id` varchar(10) NOT NULL COMMENT '員工編號，格式: SYYYYnnnnn',
  `login_id` int(10) unsigned NOT NULL,
  `cname` varchar(50) NOT NULL COMMENT '中文名字',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '注音鍵',
  `ename` varchar(100) NOT NULL COMMENT '英文名字',
  `gender` enum('M','F','X') NOT NULL COMMENT '性別，M: 男，F: 女，X: 非二元或不公開',
  `birth_date` date NOT NULL COMMENT '出生日期',
  `nationality` varchar(50) DEFAULT '' COMMENT '國籍',
  `marital_status` enum('未婚','已婚','離異','喪偶','其他') DEFAULT NULL COMMENT '婚姻',
  `blood_type` enum('A','B','AB','O','未知') DEFAULT NULL COMMENT '血型',
  `national_id_type` enum('身分證','護照','其他') NOT NULL COMMENT '身份證號類別',
  `national_id` varchar(20) NOT NULL COMMENT '身份證號或護照編號',
  `mobile_phone` varchar(20) NOT NULL COMMENT '連絡手機號碼',
  `home_phone` varchar(20) DEFAULT '' COMMENT '住家電話',
  `work_phone` varchar(20) DEFAULT '' COMMENT '公司電話',
  `email` varchar(100) NOT NULL COMMENT '電子郵件信箱',
  `address_country` varchar(50) NOT NULL COMMENT '居住地國家',
  `address_city` varchar(50) NOT NULL COMMENT '居住地城市',
  `address_district` varchar(50) DEFAULT '' COMMENT '居住地區名',
  `address_postcode` varchar(10) DEFAULT '' COMMENT '郵遞區號',
  `address_detail` text NOT NULL COMMENT '詳細地址的路街巷弄號樓室',
  `education_level` enum('高中','學士','碩士','博士','其他') DEFAULT NULL COMMENT '最高學歷',
  `graduated_from` varchar(100) NOT NULL DEFAULT '' COMMENT '畢業院校',
  `major` varchar(50) NOT NULL DEFAULT '' COMMENT '主修專業',
  `graduation_year` year(4) DEFAULT NULL COMMENT '畢業年份',
  `work_experience` text NOT NULL DEFAULT '' COMMENT '過往工作經歷摘要',
  `emergency_name` varchar(100) NOT NULL COMMENT '緊急連絡人',
  `emergency_relation` varchar(50) NOT NULL COMMENT '與員工關係',
  `emergency_phone` varchar(20) NOT NULL COMMENT '緊急連絡電話',
  `hire_date` date NOT NULL COMMENT '雇用時間',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '資料建立時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '資料最後修改時間',
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `national_id` (`national_id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_name_search` (`cname`,`ename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `PATIENTS` (
  `patient_id` varchar(12) NOT NULL COMMENT '病患編號，格式: PYYYYNNNNN',
  `cname` varchar(50) NOT NULL COMMENT '中文姓名',
  `ename` varchar(50) NOT NULL COMMENT '英文姓名',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '注音鍵',
  `gender` enum('M','F','X') NOT NULL COMMENT '性別，M: 男，F: 女，X: 非二元或不公開',
  `birth_date` date NOT NULL COMMENT '出生日期',
  `birth_place` varchar(100) DEFAULT NULL COMMENT '籍貫',
  `blood_type` enum('A','B','AB','O','未知') DEFAULT NULL COMMENT '血型',
  `nationality` varchar(50) DEFAULT '' COMMENT '國籍',
  `marital_status` enum('未婚','已婚','離異','喪偶','其他') DEFAULT NULL COMMENT '婚姻狀況',
  `national_id_type` enum('身分證','護照','其他') NOT NULL COMMENT '身份證號類別',
  `national_id` varchar(20) DEFAULT NULL COMMENT '身份證號',
  `occupation` varchar(50) DEFAULT NULL COMMENT '職業',
  `phone` varchar(20) NOT NULL COMMENT '連絡電話',
  `email` varchar(100) NOT NULL DEFAULT '' COMMENT '電子郵件信箱',
  `line` varchar(50) NOT NULL DEFAULT '' COMMENT 'Line 帳號',
  `address` text NOT NULL COMMENT '居住地址',
  `allergy_history` text DEFAULT NULL COMMENT '過敏史',
  `family_history` text DEFAULT NULL COMMENT '家族史',
  `emergency_name` varchar(100) NOT NULL COMMENT '緊急連絡人',
  `emergency_relation` varchar(50) NOT NULL COMMENT '與員工關係',
  `emergency_phone` varchar(20) NOT NULL COMMENT '緊急連絡電話',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`patient_id`),
  UNIQUE KEY `national_id` (`national_id`),
  KEY `idx_name` (`cname`),
  KEY `idx_national_id` (`national_id`),
  KEY `idx_birth_date` (`birth_date`),
  KEY `idx_blood_type` (`blood_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `ROOMS` (
  `room_id` varchar(5) NOT NULL COMMENT '診間編號',
  `name` varchar(50) NOT NULL COMMENT '診間名稱',
  `location` varchar(100) DEFAULT NULL COMMENT '診間位置',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`room_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `SCHEDULES` (
  `schedule_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '排班記錄編號',
  `employee_id` varchar(10) NOT NULL COMMENT '員工編號',
  `shift_type` enum('早班','午班','夜班','休假') NOT NULL COMMENT '班別',
  `shift_date` date NOT NULL COMMENT '排班日期',
  `check_in_time` timestamp NULL DEFAULT NULL COMMENT '實際簽到時間',
  `check_out_time` timestamp NULL DEFAULT NULL COMMENT '實際簽退時間',
  `absence_reason` text DEFAULT NULL COMMENT '缺勤原因',
  PRIMARY KEY (`schedule_id`),
  KEY `employee_id` (`employee_id`),
  KEY `idx_shift_date` (`shift_date`,`employee_id`),
  CONSTRAINT `SCHEDULES_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `EMPLOYEES` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
