--
-- Host: hanyu-db.mysql.database.azure.com    Database: nsysu_tutor
-- ------------------------------------------------------


--
-- Table structure for table `log_use`
--

CREATE TABLE `log_use` (
  `rid` int NOT NULL AUTO_INCREMENT,
  `codeNo` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '程式代碼',
  `remark` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '備註',
  `user_ip` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '連線ip',
  `user_id` int DEFAULT NULL COMMENT '使用者ID',
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='使用記錄表';

--
-- Dumping data for table `log_use`
--


--
-- Table structure for table `meeting_rooms`
--

CREATE TABLE `meeting_rooms` (
  `room_id` int NOT NULL AUTO_INCREMENT COMMENT '教室ID',
  `room_name` varchar(100) NOT NULL COMMENT '教室名稱',
  `location` varchar(50) DEFAULT NULL COMMENT '位置描述',
  `update_date` datetime DEFAULT NULL COMMENT '更新日期',
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
  `updated_by` varchar(100) DEFAULT NULL COMMENT '更新人員',
  PRIMARY KEY (`room_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='教室基本資料';

--
-- Dumping data for table `meeting_rooms`
--


--
-- Table structure for table `reservations`
--

CREATE TABLE `reservations` (
  `reservation_id` int NOT NULL AUTO_INCREMENT COMMENT '預約ID',
  `room_id` int NOT NULL COMMENT '教室ID',
  `user_id` int NOT NULL COMMENT '預約人ID',
  `room_no` varchar(100) DEFAULT NULL COMMENT '教室代號',
  `title` varchar(100) NOT NULL COMMENT '會議標題',
  `description` varchar(500) DEFAULT NULL COMMENT '會議說明',
  `start_time` datetime NOT NULL COMMENT '開始時間',
  `end_time` datetime NOT NULL COMMENT '結束時間',
  `borrower` varchar(100) DEFAULT NULL COMMENT '借用人',
  `status` varchar(30) DEFAULT 'pending' COMMENT '預約狀態ENUM(pending,approved,rejected,cancelled,completed)',
  `update_date` datetime DEFAULT NULL COMMENT '更新日期',
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
  `updated_by` varchar(100) DEFAULT NULL COMMENT '更新人員',
  `root_id` int DEFAULT '0' COMMENT '預約ID-表頭',
  PRIMARY KEY (`reservation_id`),
  KEY `fk_reservations_roomid` (`room_id`),
  KEY `fk_reservations_userid` (`user_id`),
  KEY `idx_reservations_rootid` (`root_id`),
  CONSTRAINT `fk_reservations_roomid` FOREIGN KEY (`room_id`) REFERENCES `meeting_rooms` (`room_id`),
  CONSTRAINT `fk_reservations_userid` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='預約記錄';

--
-- Dumping data for table `reservations`
--


--
-- Table structure for table `system_logs`
--

CREATE TABLE `system_logs` (
  `log_id` int NOT NULL AUTO_INCREMENT COMMENT '紀錄ID',
  `user_id` int DEFAULT NULL COMMENT '操作人員ID',
  `action` varchar(100) NOT NULL COMMENT '操作動作',
  `target_table` varchar(100) DEFAULT NULL COMMENT '目標資料表',
  `target_id` int DEFAULT NULL COMMENT '目標記錄ID',
  `action_detail` text COMMENT '操作詳情',
  `ip_address` varchar(50) DEFAULT NULL COMMENT 'IP位址',
  `action_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '操作時間',
  PRIMARY KEY (`log_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `system_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系統操作紀錄';

--
-- Dumping data for table `system_logs`
--


--
-- Table structure for table `system_news`
--

CREATE TABLE `system_news` (
  `news_id` int NOT NULL AUTO_INCREMENT COMMENT '公告編號',
  `news_title` varchar(255) NOT NULL COMMENT '公告標題',
  `news_content` text NOT NULL COMMENT '公告內容',
  `publish_date` datetime DEFAULT NULL COMMENT '發佈日期',
  `publish_unit` varchar(255) DEFAULT NULL COMMENT '發佈單位',
  `attachment_url` varchar(999) DEFAULT NULL COMMENT '附件下載URL',
  `remove_date` datetime DEFAULT NULL COMMENT '下架日期',
  `update_date` datetime DEFAULT NULL COMMENT '更新日期',
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
  `updated_by` varchar(100) DEFAULT NULL COMMENT '更新人員',
  PRIMARY KEY (`news_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='系統公告表';

--
-- Dumping data for table `system_news`
--


--
-- Table structure for table `time_slots`
--

CREATE TABLE `time_slots` (
  `slot_id` int NOT NULL AUTO_INCREMENT COMMENT '時段ID',
  `slot_name` varchar(100) NOT NULL COMMENT '時段名稱',
  `start_time` time NOT NULL COMMENT '開始時間',
  `end_time` time NOT NULL COMMENT '結束時間',
  `update_date` datetime DEFAULT NULL COMMENT '更新日期',
  `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '建檔日期',
  `updated_by` varchar(100) DEFAULT NULL COMMENT '更新人員',
  PRIMARY KEY (`slot_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='標準預約時段定義';

--
-- Dumping data for table `time_slots`
--



