-- --------------------------------------------------------
-- Database: `bot_nong_la_db`
-- (ถ้ายังไม่มี database ให้สร้างก่อน: CREATE DATABASE bot_nong_la_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE bot_nong_la_db;)
-- --------------------------------------------------------
--
-- Table structure for table `employees`
--
DROP TABLE IF EXISTS `employees`;
CREATE TABLE IF NOT EXISTS `employees` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`employee_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`chat_user_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Telegram User ID',
`first_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`last_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`manager_id` int(10) UNSIGNED DEFAULT NULL,
`is_manager` tinyint(1) DEFAULT 0 COMMENT '0=No, 1=Yes',
`is_hr` tinyint(1) DEFAULT 0 COMMENT '0=No, 1=Yes',
`created_at` timestamp NULL DEFAULT current_timestamp(),
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `employee_code` (`employee_code`),
UNIQUE KEY `chat_user_id` (`chat_user_id`),
UNIQUE KEY `email` (`email`),
KEY `manager_id_fk_idx` (`manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `leave_types`
--
DROP TABLE IF EXISTS `leave_types`;
CREATE TABLE IF NOT EXISTS `leave_types` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`alias` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'e.g., vacation, sick, wfh',
`requires_approval` tinyint(1) DEFAULT 1,
`deducts_balance` tinyint(1) DEFAULT 1 COMMENT 'Does this type deduct from balance?',
`created_at` timestamp NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `alias` (`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `leave_requests`
--
DROP TABLE IF EXISTS `leave_requests`;
CREATE TABLE IF NOT EXISTS `leave_requests` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`employee_id` int(10) UNSIGNED NOT NULL,
`leave_type_id` int(10) UNSIGNED NOT NULL,
`start_date` date NOT NULL,
`end_date` date NOT NULL,
`reason` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`status` enum('PENDING','APPROVED','REJECTED','CANCELLED') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PENDING',
`approved_by_id` int(10) UNSIGNED DEFAULT NULL COMMENT 'Employee ID of approver',
`approved_at` timestamp NULL DEFAULT NULL,
`requested_at` timestamp NULL DEFAULT current_timestamp(),
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
KEY `lr_employee_id_fk_idx` (`employee_id`),
KEY `lr_leave_type_id_fk_idx` (`leave_type_id`),
KEY `lr_approved_by_id_fk_idx` (`approved_by_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
-- Table structure for table `leave_balances`
--
DROP TABLE IF EXISTS `leave_balances`;
CREATE TABLE IF NOT EXISTS `leave_balances` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`employee_id` int(10) UNSIGNED NOT NULL,
`leave_type_id` int(10) UNSIGNED NOT NULL,
`balance_days` decimal(5,2) NOT NULL DEFAULT 0.00,
`year` year NOT NULL,
`last_updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `employee_type_year_unique` (`employee_id`,`leave_type_id`,`year`),
KEY `lb_leave_type_id_fk_idx` (`leave_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- --------------------------------------------------------
-- Constraints for dumped tables
-- --------------------------------------------------------
--
-- Constraints for table `employees`
--
ALTER TABLE `employees`
ADD CONSTRAINT `employees_manager_id_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
--
-- Constraints for table `leave_requests`
--
ALTER TABLE `leave_requests`
ADD CONSTRAINT `lr_approved_by_id_fk` FOREIGN KEY (`approved_by_id`) REFERENCES `employees` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT `lr_employee_id_fk` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `lr_leave_type_id_fk` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
--
-- Constraints for table `leave_balances`
--
ALTER TABLE `leave_balances`
ADD CONSTRAINT `lb_employee_id_fk` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `lb_leave_type_id_fk` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
-- --------------------------------------------------------
-- Example Initial Data (แนะนำให้ใส่ข้อมูลเหล่านี้หลังสร้างตารางแล้ว)
-- --------------------------------------------------------
INSERT INTO `leave_types` (`id`, `name`, `alias`, `requires_approval`, `deducts_balance`) VALUES
(1, 'ลาพักร้อน', 'vacation', 1, 1),
(2, 'ลาป่วย', 'sick', 1, 1),
(3, 'ลากิจ', 'personal', 1, 1),
(4, 'Work From Home', 'wfh', 1, 0);
-- Example Employee (HR and Manager)
INSERT INTO `employees` (`id`, `employee_code`, `chat_user_id`, `first_name`, `last_name`, `email`, `manager_id`, `is_manager`, `is_hr`) VALUES
(1, 'HR001', 'YOUR_HR_TELEGRAM_USER_ID', 'Admin', 'Bot', 'hr@example.com', NULL, 1, 1);
-- Example Employee (Normal User, reports to HR001)
INSERT INTO `employees` (`id`, `employee_code`, `chat_user_id`, `first_name`, `last_name`, `email`, `manager_id`, `is_manager`, `is_hr`) VALUES
(2, 'EMP001', 'YOUR_EMP_TELEGRAM_USER_ID', 'Test', 'User', 'testuser@example.com', 1, 0, 0);
-- Example Leave Balance for EMP001
INSERT INTO `leave_balances` (`employee_id`, `leave_type_id`, `balance_days`, `year`) VALUES
(2, 1, 10.00, YEAR(CURDATE())),
(2, 3, 5.00, YEAR(CURDATE()));