--
-- Host: localhost    Database: clinic
-- ------------------------------------------------------


--
-- Current Database: `clinic`
--

/*!40000 DROP DATABASE IF EXISTS `clinic`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `clinic` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;

USE `clinic`;

--
-- Table structure for table `ACUPUNCTURES`
--

CREATE TABLE `ACUPUNCTURES` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `name` varchar(50) DEFAULT NULL COMMENT '名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '注音鍵',
  `ename` varchar(50) DEFAULT NULL COMMENT '英文名稱',
  `code` varchar(10) NOT NULL DEFAULT '' COMMENT '穴位代碼',
  `meridian` varchar(50) DEFAULT NULL COMMENT '經絡：肺經、大腸經…',
  `spot` varchar(1024) DEFAULT NULL COMMENT '部位',
  `description` text DEFAULT NULL COMMENT '描述',
  `implication` varchar(50) DEFAULT NULL COMMENT '特徵/臨床意義',
  `remark` varchar(1024) DEFAULT NULL COMMENT '注記或說明',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqid` (`name`,`code`,`meridian`) USING HASH
) ENGINE=MyISAM AUTO_INCREMENT=362 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `APPOINTMENTS`
--

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=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='掛號紀錄表';

--
-- Table structure for table `CONTACT_HISTORY`
--

CREATE TABLE `CONTACT_HISTORY` (
  `history_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '變更記錄ID',
  `employee_id` varchar(10) NOT NULL COMMENT '員工編號',
  `changed_field` varchar(50) NOT NULL COMMENT '變更的欄位名稱',
  `old_value` text DEFAULT NULL COMMENT '舊值',
  `new_value` text DEFAULT NULL COMMENT '新值',
  `update_time` datetime DEFAULT current_timestamp() COMMENT '變更時間',
  PRIMARY KEY (`history_id`),
  KEY `employee_id` (`employee_id`),
  KEY `idx_change_time` (`update_time`),
  CONSTRAINT `CONTACT_HISTORY_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `EMPLOYEES` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `DEPARTMENTS`
--

CREATE TABLE `DEPARTMENTS` (
  `department_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部門編號',
  `name` varchar(50) NOT NULL COMMENT '部門名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '注音鍵',
  `parent_dept` int(11) NOT NULL DEFAULT 0 COMMENT '上級部門',
  `manager_id` varchar(10) DEFAULT NULL COMMENT '部門主管編號',
  `extension` varchar(10) DEFAULT NULL COMMENT '分機號碼',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '資料建立時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '資料最後修改時間',
  PRIMARY KEY (`department_id`),
  UNIQUE KEY `name` (`name`),
  KEY `manager_id` (`manager_id`),
  CONSTRAINT `DEPARTMENTS_ibfk_1` FOREIGN KEY (`manager_id`) REFERENCES `EMPLOYEES` (`employee_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `DIAGNOSTICS`
--

CREATE TABLE `DIAGNOSTICS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `diagnostic` varchar(10) NOT NULL COMMENT '望、聞、問、切',
  `name` varchar(50) DEFAULT NULL COMMENT '名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '注音鍵',
  `category` varchar(50) DEFAULT NULL COMMENT '望：部位，[聞、問、切]：主類',
  `sndcategory` varchar(50) DEFAULT NULL COMMENT '[望、切]：類別，[聞、問]：細類',
  `trdcategory` varchar(50) DEFAULT NULL COMMENT '[望、切]：細類',
  `description` text DEFAULT NULL COMMENT '描述',
  `implication` varchar(50) DEFAULT NULL COMMENT '特徵/臨床意義',
  `treatment` text DEFAULT '' COMMENT '治則',
  `meridians` varchar(50) DEFAULT NULL COMMENT '歸經',
  `remark` varchar(1024) DEFAULT NULL COMMENT '注記或說明',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqid` (`diagnostic`,`name`,`category`,`sndcategory`,`trdcategory`,`implication`) USING HASH
) ENGINE=MyISAM AUTO_INCREMENT=941 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `DIEASES`
--

CREATE TABLE `DIEASES` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '疾病序號',
  `name` varchar(50) DEFAULT NULL COMMENT '疾病名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '拼音符號',
  `category` varchar(50) DEFAULT NULL COMMENT '疾病類別',
  `description` text DEFAULT NULL COMMENT '疾病描述',
  `feature` varchar(50) DEFAULT NULL COMMENT '疾病特徵',
  `meridians` varchar(50) DEFAULT NULL COMMENT '疾病歸經',
  `treatment` text DEFAULT NULL COMMENT '治療建議',
  `remark` varchar(1024) DEFAULT NULL COMMENT '注記或說明',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `EFFICACY`
--

CREATE TABLE `EFFICACY` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '功效序號',
  `name` varchar(50) DEFAULT NULL COMMENT '功效名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '拼音符號',
  `description` text DEFAULT NULL COMMENT '疾病描述',
  `remark` varchar(1024) DEFAULT NULL COMMENT '注記或說明',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=941 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `EMPLOYEES`
--

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;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER generate_employee_id
BEFORE INSERT ON EMPLOYEES
FOR EACH ROW
BEGIN
  DECLARE current_year_val INT;
  DECLARE next_seq_val INT;

  SET current_year_val = YEAR(CURDATE());

  
  INSERT INTO employee_id_sequence (current_year, next_seq)
  VALUES (current_year_val, 1)
  ON DUPLICATE KEY UPDATE next_seq = next_seq + 1;

  SELECT next_seq INTO next_seq_val
  FROM employee_id_sequence
  WHERE current_year = current_year_val;

  
  SET NEW.employee_id = CONCAT(
    'S',
    current_year_val,
    LPAD(next_seq_val, 5, '0')
  );
END */;;
DELIMITER ;

--
-- Table structure for table `EMPLOYEE_POSITIONS`
--

CREATE TABLE `EMPLOYEE_POSITIONS` (
  `assignment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分配記錄編號',
  `employee_id` varchar(10) NOT NULL COMMENT '員工編號',
  `department_id` int(11) NOT NULL COMMENT '部門編號',
  `position_id` int(11) NOT NULL COMMENT '職稱編號',
  `start_date` date NOT NULL COMMENT '生效日期',
  `end_date` date DEFAULT NULL COMMENT '結束日期',
  PRIMARY KEY (`assignment_id`),
  KEY `department_id` (`department_id`),
  KEY `position_id` (`position_id`),
  KEY `idx_employee` (`employee_id`),
  CONSTRAINT `EMPLOYEE_POSITIONS_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `EMPLOYEES` (`employee_id`),
  CONSTRAINT `EMPLOYEE_POSITIONS_ibfk_2` FOREIGN KEY (`department_id`) REFERENCES `DEPARTMENTS` (`department_id`),
  CONSTRAINT `EMPLOYEE_POSITIONS_ibfk_3` FOREIGN KEY (`position_id`) REFERENCES `JOBTITLES` (`position_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `FORMULA_COMPOSITION`
--

CREATE TABLE `FORMULA_COMPOSITION` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `fid` int(10) unsigned NOT NULL COMMENT '方劑編號',
  `mid` int(10) unsigned NOT NULL COMMENT '藥材編號',
  `quantity` decimal(5,2) NOT NULL DEFAULT 0.00 COMMENT '藥材份量',
  `processing` varchar(50) DEFAULT '' COMMENT '炮製方法',
  `caption` text NOT NULL DEFAULT '' COMMENT '藥材用法解釋',
  `remark` varchar(256) DEFAULT NULL COMMENT '備註',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=292889 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `FORMULA_MERIDIANS`
--

CREATE TABLE `FORMULA_MERIDIANS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `fid` int(10) unsigned NOT NULL COMMENT '方劑編號',
  `mid` int(10) unsigned NOT NULL COMMENT '藥材編號',
  `meridian` varchar(50) NOT NULL COMMENT '歸經名',
  `ratio` tinyint(3) NOT NULL DEFAULT 0 COMMENT '佔比(%)',
  `remark` varchar(256) DEFAULT NULL COMMENT '備註',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=288664 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `INSPECTION`
--

CREATE TABLE `INSPECTION` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `name` varchar(50) DEFAULT NULL COMMENT '望診名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '注音鍵',
  `bodypart` varchar(50) DEFAULT NULL COMMENT '部位分類',
  `category` varchar(50) DEFAULT NULL COMMENT '主類',
  `subcategory` varchar(50) DEFAULT NULL COMMENT '細類',
  `description` text DEFAULT NULL COMMENT '描述',
  `feature` varchar(50) DEFAULT NULL COMMENT '特徵',
  `meridians` varchar(50) DEFAULT NULL COMMENT '歸經',
  `remark` varchar(1024) DEFAULT NULL COMMENT '注記或說明',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=654 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `JOBTITLES`
--

CREATE TABLE `JOBTITLES` (
  `position_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '職稱編號',
  `title` varchar(50) NOT NULL COMMENT '職稱名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '注音鍵',
  `category` enum('醫療','行政','護理','技術') NOT NULL COMMENT '職務類別',
  `salary_range` varchar(50) DEFAULT NULL COMMENT '薪水範圍',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '資料建立時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '資料最後修改時間',
  PRIMARY KEY (`position_id`),
  UNIQUE KEY `title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `LAB_TESTS`
--

CREATE TABLE `LAB_TESTS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵，自增',
  `patient_id` varchar(12) NOT NULL COMMENT '病患編號，格式 PYYYYnnnnn',
  `test_no` varchar(20) NOT NULL COMMENT '檢查單編號，唯一',
  `test_datetime` datetime NOT NULL COMMENT '檢查完成時間',
  `test_item` varchar(100) NOT NULL COMMENT '檢查項目／檢驗類別，如血常規、尿液分析等',
  `test_unit` varchar(100) DEFAULT NULL COMMENT '檢查單位／檢驗單位',
  `result_type` enum('file','text') NOT NULL DEFAULT 'text' COMMENT '結果類型：file=檔案路徑、text=文字描述',
  `result_value` text DEFAULT NULL COMMENT '若 result_type=file，則為檔案在伺服器或物件存儲的完整路徑；若=text，則為檢查結果文字描述',
  `remarks` text DEFAULT NULL COMMENT '備註',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT '建立時間',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '最後更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_test_no` (`patient_id`,`test_no`),
  KEY `idx_patient_id` (`patient_id`),
  CONSTRAINT `fk_lab_tests_patient` FOREIGN KEY (`patient_id`) REFERENCES `PATIENTS` (`patient_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='實驗室檢查記錄表';

--
-- Table structure for table `MED_ADVICES`
--

CREATE TABLE `MED_ADVICES` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '治療建議序號',
  `rid` int(10) unsigned NOT NULL COMMENT '需求編號(DIAGNOSTICS id)',
  `treatment_method` int(10) unsigned NOT NULL DEFAULT 1 COMMENT '治療類別(1: 內服, 2: 外用, 3: 針炙, 4: 推拿, 5: 理傷)',
  `treatment` text DEFAULT NULL COMMENT '治則',
  `phonetic` varchar(256) NOT NULL DEFAULT '' COMMENT 'treatment的注音符號',
  `pinyin` varchar(256) NOT NULL DEFAULT '' COMMENT 'treatment的注音鍵',
  `fid` int(10) unsigned NOT NULL COMMENT '方劑、藥材或其他治療方法的編號',
  `formula_optimization` text DEFAULT NULL COMMENT '方劑優化調整',
  `remark` text DEFAULT NULL COMMENT '注記或說明',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqid` (`rid`,`treatment_method`,`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `OLFACTION`
--

CREATE TABLE `OLFACTION` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `name` varchar(50) DEFAULT NULL COMMENT '聞診名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '注音鍵',
  `specialty` varchar(50) DEFAULT NULL COMMENT '內科、外科、…',
  `category` varchar(50) DEFAULT NULL COMMENT '類別',
  `description` text DEFAULT NULL COMMENT '描述',
  `feature` varchar(50) DEFAULT NULL COMMENT '特徵',
  `meridians` varchar(50) DEFAULT NULL COMMENT '歸經',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=168 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `OPD_TREATMENTS`
--

CREATE TABLE `OPD_TREATMENTS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '治療方法編號',
  `type` enum('formula','acupuncture') NOT NULL COMMENT 'formula: 藥材/方劑, acupuncture: 針炙',
  `summary` varchar(1024) NOT NULL DEFAULT '' COMMENT '治療內容列表使用字串',
  `payload_json` text NOT NULL COMMENT '治療內容彈窗使用字串，為一JSOIN字串',
  `created_by` varchar(20) DEFAULT NULL COMMENT '建立者編號',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='治療方法記錄表';

--
-- Table structure for table `OUTPATIENTS`
--

CREATE TABLE `OUTPATIENTS` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '流水編號',
  `appt_id` bigint(20) unsigned DEFAULT 0 COMMENT '掛號編號',
  `patient_id` varchar(10) NOT NULL COMMENT '病歷號碼',
  `opddate` datetime NOT NULL COMMENT '門診日期時間',
  `chief_complaint` text DEFAULT NULL COMMENT '主訴',
  `symptoms` text DEFAULT NULL COMMENT '症狀（醫師輸入給 AI 判讀）',
  `past_history` text DEFAULT NULL COMMENT '過去病史',
  `tcm_observation` text DEFAULT NULL COMMENT '望診',
  `tcm_interrogation` text DEFAULT NULL COMMENT '問診',
  `tcm_olfaction` text DEFAULT NULL COMMENT '聞診',
  `tcm_palpation` text DEFAULT NULL COMMENT '切診',
  `syndrome` text DEFAULT NULL COMMENT '證型（自動/手動產生）',
  `treatment` text DEFAULT NULL COMMENT '治則（自動/手動產生）',
  `formula` text DEFAULT NULL COMMENT '方劑（自動/手動產生）',
  `lab_blood` text DEFAULT NULL COMMENT '血液檢查',
  `lab_urine` text DEFAULT NULL COMMENT '尿液分析',
  `lab_liver_kidney` text DEFAULT NULL COMMENT '肝腎功能',
  `lab_imaging` text DEFAULT NULL COMMENT '影像學檢查',
  `lab_other` text DEFAULT NULL COMMENT '其他檢查',
  `wm_diagnosis` text DEFAULT NULL COMMENT '西醫診斷',
  `wm_treatment` text DEFAULT NULL COMMENT '西醫治療',
  `remarks` text DEFAULT NULL COMMENT '備註',
  `followUp` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT '建立時間',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '最後更新時間',
  PRIMARY KEY (`id`),
  KEY `idx_medrec` (`patient_id`),
  KEY `appt_id` (`appt_id`),
  CONSTRAINT `OUTPATIENTS_ibfk_1` FOREIGN KEY (`appt_id`) REFERENCES `APPOINTMENTS` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='門診紀錄表';

--
-- Table structure for table `PALPATION`
--

CREATE TABLE `PALPATION` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `name` varchar(50) DEFAULT NULL COMMENT '切診名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '注音鍵',
  `category` varchar(50) DEFAULT NULL COMMENT '分類',
  `main_pulse` varchar(50) DEFAULT NULL COMMENT '主要脈',
  `combined_pulse` varchar(50) DEFAULT NULL COMMENT '相兼脈',
  `description` text DEFAULT NULL COMMENT '描述',
  `feature` varchar(1024) DEFAULT NULL COMMENT '臨床意義或特徵',
  `remark` varchar(1024) DEFAULT NULL COMMENT '注記或說明',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqid` (`name`,`category`,`main_pulse`,`combined_pulse`)
) ENGINE=MyISAM AUTO_INCREMENT=121 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `PATIENTS`
--

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;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER generate_patient_id 
BEFORE INSERT ON PATIENTS
FOR EACH ROW
BEGIN
    DECLARE current_year_val INT;
    DECLARE next_seq_val INT;
    
    SET current_year_val = YEAR(CURDATE());
    
    INSERT INTO patient_id_seq (current_year, next_seq)
    VALUES (current_year_val, 1)
    ON DUPLICATE KEY UPDATE next_seq = next_seq + 1;
    
    SELECT next_seq INTO next_seq_val 
    FROM patient_id_seq 
    WHERE current_year = current_year_val;
    
    SET NEW.patient_id = CONCAT(
        'P',
        current_year_val,
        LPAD(next_seq_val, 5, '0')
    );
END */;;
DELIMITER ;

--
-- Table structure for table `PERFORMANCE`
--

CREATE TABLE `PERFORMANCE` (
  `review_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '考核記錄編號',
  `employee_id` varchar(10) NOT NULL COMMENT '員工編號',
  `reviewer_id` varchar(10) NOT NULL COMMENT '考核人編號',
  `review_date` date NOT NULL COMMENT '考核日期',
  `score` decimal(5,2) DEFAULT NULL COMMENT '考核分數(0-100)',
  `comments` text DEFAULT NULL COMMENT '評語',
  PRIMARY KEY (`review_id`),
  KEY `employee_id` (`employee_id`),
  KEY `reviewer_id` (`reviewer_id`),
  CONSTRAINT `PERFORMANCE_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `EMPLOYEES` (`employee_id`),
  CONSTRAINT `PERFORMANCE_ibfk_2` FOREIGN KEY (`reviewer_id`) REFERENCES `EMPLOYEES` (`employee_id`),
  CONSTRAINT `chk_score_range` CHECK (`score` between 0 and 100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER trg_performance_score
BEFORE INSERT ON PERFORMANCE
FOR EACH ROW
BEGIN
  IF NEW.score < 0 OR NEW.score > 100 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '考核分數必須在 0-100 之間';
  END IF;
END */;;
DELIMITER ;

--
-- Table structure for table `PHONETIC`
--

CREATE TABLE `PHONETIC` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `word` varchar(50) DEFAULT NULL COMMENT '中文字',
  `phonetic` varchar(50) NOT NULL COMMENT '注音',
  `init_phonetic` varchar(4) NOT NULL COMMENT '第一個注音符號',
  `init_pinyin` varchar(4) 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 (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12139 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `RELATED_EFFICACY`
--

CREATE TABLE `RELATED_EFFICACY` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `type` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '0: 藥材，1: 方劑',
  `sid` int(10) unsigned NOT NULL COMMENT '藥材/方劑編號',
  `eid` int(10) unsigned NOT NULL COMMENT '功效序號',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqid` (`type`,`sid`,`eid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `RELATED_SYMPTOMS`
--

CREATE TABLE `RELATED_SYMPTOMS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `type` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '0: 藥材，1: 方劑, 2: 疾病, 3: 証, 4: 望, 5: 聞, 6: 問, 7: 切, 9: 其他',
  `sid` int(10) unsigned NOT NULL COMMENT '藥材/方劑/疾病/証/望/聞/問/切等的編號',
  `symptom_id` int(10) unsigned NOT NULL COMMENT '症狀序號',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqid` (`type`,`sid`,`symptom_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `RESIGNATIONS`
--

CREATE TABLE `RESIGNATIONS` (
  `resignation_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '離職記錄編號',
  `employee_id` varchar(10) NOT NULL COMMENT '員工編號',
  `resign_date` date NOT NULL COMMENT '離職生效日期',
  `reason` text DEFAULT NULL COMMENT '離職原因',
  `exit_interview` text DEFAULT NULL COMMENT '離職面談紀錄',
  PRIMARY KEY (`resignation_id`),
  UNIQUE KEY `employee_id` (`employee_id`),
  CONSTRAINT `RESIGNATIONS_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `EMPLOYEES` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `ROOMS`
--

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;

--
-- Table structure for table `SCHEDULES`
--

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;

--
-- Table structure for table `SYMPTOMS`
--

CREATE TABLE `SYMPTOMS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '症狀序號',
  `name` varchar(50) DEFAULT NULL COMMENT '症狀名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '拼音符號',
  `category` varchar(50) DEFAULT NULL COMMENT '症狀主類',
  `subcategory` varchar(50) DEFAULT NULL COMMENT '症狀細類',
  `description` text DEFAULT NULL COMMENT '症狀描述',
  `feature` varchar(50) DEFAULT NULL COMMENT '症狀特徵',
  `meridians` varchar(50) DEFAULT NULL COMMENT '症狀歸經',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=511 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `SYNDROMES`
--

CREATE TABLE `SYNDROMES` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '証候序號',
  `name` varchar(50) DEFAULT NULL COMMENT '証候名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '拼音符號',
  `category` varchar(50) DEFAULT NULL COMMENT '証候類別',
  `subcategory` varchar(50) DEFAULT '' COMMENT '細類',
  `description` text DEFAULT NULL COMMENT '証候描述',
  `implication` varchar(256) DEFAULT NULL COMMENT '臨床意義或特徵',
  `treatment` text DEFAULT NULL,
  `remark` text DEFAULT NULL COMMENT '備註或說明',
  `meridians` varchar(50) DEFAULT NULL COMMENT '証候歸經',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `name` (`name`,`phonetic`,`pinyin`,`category`,`subcategory`,`description`,`implication`)
) ENGINE=MyISAM AUTO_INCREMENT=492 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `TC_MED_FORMULAS`
--

CREATE TABLE `TC_MED_FORMULAS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `name` varchar(50) NOT NULL COMMENT '方劑名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '拼音符號',
  `src_classical` varchar(50) NOT NULL COMMENT '出處典籍',
  `heat_idx` tinyint(3) NOT NULL DEFAULT 0 COMMENT '熱性指數',
  `cold_idx` tinyint(3) NOT NULL DEFAULT 0 COMMENT '寒性指數',
  `indications` text NOT NULL DEFAULT '' COMMENT '主治',
  `efficacy` text NOT NULL DEFAULT '' COMMENT '功效',
  `med_instruction` text NOT NULL DEFAULT '' COMMENT '傳統服藥法',
  `side_effect` text NOT NULL DEFAULT '' COMMENT '注意事項及副作用',
  `related_disease` varchar(1024) NOT NULL DEFAULT '' COMMENT '相關疾病',
  `same_name_formula` text NOT NULL DEFAULT '' COMMENT '相同名稱方劑',
  `similar_composition_formula` text NOT NULL DEFAULT '' COMMENT '相似配方組成方劑',
  `related_classical` text NOT NULL DEFAULT '' COMMENT '相關典籍',
  `sci_medicine` tinyint(3) NOT NULL DEFAULT 0,
  `remark` varchar(256) DEFAULT NULL COMMENT '備註',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=40978 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `TC_MED_INVENTORY`
--

CREATE TABLE `TC_MED_INVENTORY` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL COMMENT '進貨編號（例：MM0012）',
  `category` enum('M','F') NOT NULL COMMENT 'M=藥材, F=方劑',
  `sci_medicine` tinyint(1) NOT NULL DEFAULT 0 COMMENT '1=科中',
  `item_id` int(10) unsigned NOT NULL COMMENT '對應既有 TC_MED_MATERIALS/FORMULAS 的 id',
  `supplier_id` int(10) unsigned NOT NULL,
  `unit_id` int(10) unsigned NOT NULL,
  `qty` decimal(18,3) NOT NULL,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `location` varchar(50) NOT NULL DEFAULT '' COMMENT '存放位置（例如 A架12）',
  `received_at` datetime NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_inventory_code` (`code`),
  KEY `idx_inventory_item` (`category`,`item_id`),
  KEY `idx_inventory_supplier` (`supplier_id`),
  KEY `fk_inventory_unit` (`unit_id`),
  KEY `idx_inv_sci_medicine` (`sci_medicine`),
  CONSTRAINT `fk_inventory_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `TC_MED_SUPPLIERS` (`id`),
  CONSTRAINT `fk_inventory_unit` FOREIGN KEY (`unit_id`) REFERENCES `TC_MED_UNITS` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `TC_MED_MATERIALS`
--

CREATE TABLE `TC_MED_MATERIALS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '序號',
  `name` varchar(50) NOT NULL COMMENT '藥材名稱',
  `phonetic` varchar(20) NOT NULL DEFAULT '' COMMENT '注音符號',
  `pinyin` varchar(20) NOT NULL DEFAULT '' COMMENT '拼音符號',
  `en_name` varchar(50) NOT NULL COMMENT '藥材英文名稱',
  `family` varchar(50) NOT NULL COMMENT '科別',
  `property` varchar(50) NOT NULL COMMENT '溫熱寒涼性質',
  `taste` varchar(50) NOT NULL COMMENT '藥材的味道',
  `meridians` varchar(50) NOT NULL COMMENT '歸經',
  `effect` varchar(50) NOT NULL COMMENT '功效',
  `toxicity` varchar(50) NOT NULL COMMENT '毒性',
  `pregnant` varchar(50) NOT NULL COMMENT '孕婦',
  `disable` tinyint(1) NOT NULL DEFAULT 0 COMMENT '官方禁用',
  `sci_medicine` tinyint(3) NOT NULL DEFAULT 0,
  `remark` varchar(256) DEFAULT NULL COMMENT '備註',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=840 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `TC_MED_SUPPLIERS`
--

CREATE TABLE `TC_MED_SUPPLIERS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '廠商名稱',
  `phone` varchar(30) NOT NULL DEFAULT '',
  `scope` varchar(200) NOT NULL DEFAULT '' COMMENT '營業項目/業務範圍',
  `remark` varchar(255) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_suppliers_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `TC_MED_UNITS`
--

CREATE TABLE `TC_MED_UNITS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category` enum('weight','volume','count') NOT NULL DEFAULT 'weight' COMMENT '單位類型',
  `name` varchar(50) NOT NULL COMMENT '單位名稱（克、毫升、顆…）',
  `abbr` varchar(20) NOT NULL DEFAULT '' COMMENT '縮寫',
  `base_ratio` decimal(12,3) NOT NULL DEFAULT 1.000 COMMENT '對 base 單位的倍率',
  `remark` varchar(255) DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=啟用,0=停用',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_units_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `TRAININGS_CERTIFICATIONS`
--

CREATE TABLE `TRAININGS_CERTIFICATIONS` (
  `training_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '培訓記錄編號',
  `employee_id` varchar(10) NOT NULL COMMENT '員工編號',
  `course_name` varchar(100) NOT NULL COMMENT '培訓課程名稱',
  `certification_id` varchar(50) DEFAULT NULL COMMENT '證照編號(如護士執照)',
  `issue_date` date NOT NULL COMMENT '發證日期',
  `expiry_date` date DEFAULT NULL COMMENT '證照到期日',
  PRIMARY KEY (`training_id`),
  KEY `employee_id` (`employee_id`),
  KEY `idx_expiry` (`expiry_date`),
  CONSTRAINT `TRAININGS_CERTIFICATIONS_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `EMPLOYEES` (`employee_id`),
  CONSTRAINT `chk_expiry_date` CHECK (`expiry_date` is null or `expiry_date` > `issue_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `TREATMENTS`
--

CREATE TABLE `TREATMENTS` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '治療建議序號',
  `type` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '0: 藥材，1: 方劑, 2: 疾病, 3: 証, 4: 望, 5: 聞, 6: 問, 7: 切, 9: 其他',
  `rid` int(10) unsigned NOT NULL COMMENT '需求編號(requirement id)，type 相關的編號',
  `treatment` text DEFAULT NULL COMMENT '治則',
  `phonetic` varchar(256) NOT NULL DEFAULT '' COMMENT 'treatment的注音符號',
  `pinyin` varchar(256) NOT NULL DEFAULT '' COMMENT 'treatment的注音鍵',
  `treatmentdetail` text DEFAULT NULL COMMENT '治療內容',
  `remark` varchar(1024) DEFAULT NULL COMMENT '注記或說明',
  `created_at` timestamp NULL DEFAULT current_timestamp() COMMENT '創建時間',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新時間',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=239 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `employee_id_sequence`
--

CREATE TABLE `employee_id_sequence` (
  `current_year` int(11) NOT NULL,
  `next_seq` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`current_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `patient_id_seq`
--

CREATE TABLE `patient_id_seq` (
  `current_year` int(11) NOT NULL,
  `next_seq` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`current_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `physical_examination`
--

CREATE TABLE `physical_examination` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '流水編號',
  `patient_id` varchar(10) NOT NULL COMMENT '病歷號碼',
  `opd_id` bigint(20) unsigned DEFAULT 0 COMMENT '看診編號',
  `datetime` datetime NOT NULL COMMENT '量測日期時間',
  `sbp` smallint(5) unsigned DEFAULT NULL COMMENT '舒張壓 SBP',
  `dbp` smallint(5) unsigned DEFAULT NULL COMMENT '收縮壓 DBP',
  `pulse` smallint(5) unsigned DEFAULT NULL COMMENT '脈搏',
  `body_temperature` decimal(4,1) DEFAULT NULL COMMENT '體溫',
  `respiratory_rate` smallint(5) unsigned DEFAULT NULL COMMENT '呼吸速率',
  `physical_other` text DEFAULT NULL COMMENT '其他理學檢查',
  `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT '建立時間',
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '最後更新時間',
  PRIMARY KEY (`id`),
  KEY `idx_medrec` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='理學檢查表';

--
-- Dumping events for database 'clinic'
--

--
-- Dumping routines for database 'clinic'
--


