--
-- Host: localhost    Database: sample
-- ------------------------------------------------------


--
-- Dumping routines for database 'sample'
--

--
-- 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 COMMENT='連絡人變更記錄表';

--
-- Dumping data for table `CONTACT_HISTORY`
--


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

CREATE TABLE `DEPARTMENTS` (
  `department_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部門編號',
  `name` varchar(50) NOT NULL COMMENT '部門名稱',
  `en_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=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='部門基本資料表';

--
-- Dumping data for table `DEPARTMENTS`
--

INSERT INTO `DEPARTMENTS` VALUES
(1,'診間一','Consultation Room 1','ㄓㄐㄧ','5ru',0,'S202500001','101','2026-01-14 03:18:03','2026-01-14 11:51:26'),
(2,'診間二','Consultation Room 2','ㄓㄐㄦ','5r-',0,'S202500002','102','2026-01-14 03:22:03','2026-01-16 10:27:33'),
(4,'掛號室','Registration Room','ㄍㄏㄕ','ecg',0,'S202600001','105','2026-01-14 05:17:45','2026-01-14 11:52:19');

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

CREATE TABLE `JOBTITLES` (
  `position_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '職稱編號',
  `title` varchar(50) NOT NULL COMMENT '職稱名稱',
  `en_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=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='職稱基本資料表';

--
-- Dumping data for table `JOBTITLES`
--

INSERT INTO `JOBTITLES` VALUES
(1,'醫師','doctor','ㄧㄕ','ug','醫療','50000 以上','2026-01-13 13:04:58','2026-01-18 12:25:11'),
(2,'護士','nurse','ㄏㄕ','cg','醫療','30000 ~ 60000','2026-01-13 13:05:32','2026-01-16 10:28:47'),
(3,'藥劑師','Pharmacist','ㄧㄐㄕ','urg','醫療','40000 ~ 70000','2026-01-14 05:21:36','2026-01-16 10:28:55'),
(4,'職員','staff','ㄓㄩ','5m','行政','30000 ~ 50000','2026-01-23 04:48:27','2026-01-23 04:48:27');

--
-- 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 COMMENT='員工職稱設定記錄表';

--
-- Dumping data for table `EMPLOYEE_POSITIONS`
--


--
-- 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 '雇用時間',
  `jobtitle_id` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `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;

--
-- Dumping data for table `EMPLOYEES`
--

INSERT INTO `EMPLOYEES` VALUES
('S202500001',3,'好市多','ㄏㄕㄉ','cg2','Costco','M','1998-12-21','USA','未婚','A','身分證','U112233445','0901234567','','','costco@gmail.com','台灣','台北市','內湖區','123456','民族街24-1號4樓','博士','','',2018,'AAAAAA','大潤發','兄弟','0911223344','2025-04-20',1,1,'2025-04-19 19:34:56','2026-01-18 12:24:45'),
('S202500002',5,'君肇','ㄐㄓ','r5','fan','M','2025-11-01','ROC','未婚','未知','身分證','X123456789','0912345678','0422053366','','fan@do-way.com','ROC','台中市','北區','404','學士路91號','學士','中國醫藥大學','中醫',2027,'無','Weiwei','朋友','0975402559','2025-11-01',1,2,'2025-11-01 13:32:07','2026-01-18 12:24:54'),
('S202600001',6,'簡志華','ㄐㄓㄏ','r5c','Charles Chien','M','1968-09-26','中華民國','已婚','O','身分證','F122303533','0937191388','0224968033','','cch926@gmail.com','台灣','瑞芳區','','224','民族街24-1號4樓','高中','勤益工專','自動控制',1987,'1111111111','Judy Lin','Wife','0910120955','2026-01-01',3,4,'2026-01-14 05:38:14','2026-02-02 06:58:09'),
('S202600002',7,'簡單','ㄐㄕ','rg','Simple Chien','M','1999-01-01','中華民國','未婚','A','身分證','F112233443','0910345678','0222334455','','simple.chien@gmail.com','中華民國','新北市','瑞芳區','224','民族街24-1號4樓','碩士','三七大學','醫技',2024,'無','簡一','朋友','0998765432','2026-01-01',4,4,'2026-01-14 05:42:49','2026-02-02 06:58:20'),
('S202600006',9,'DDD','ddd','','DDD','M','2025-12-02','ROC','未婚','B','身分證','Z123456789','0987654321','','','ddd@do-way.com','ROS','New Taipei City','RFES','224','wwwwwww','博士','ASDF','AAS',2026,'','DW','Friend','0988776655','2026-02-03',1,1,'2026-02-03 12:56:53','2026-02-05 04:52:40');
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_roles`
--

CREATE TABLE `employee_roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` varchar(10) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `employee_roles`
--


--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_key` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `role_key` (`role_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `roles`
--



