-- --------------------------------------------------------
-- 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()));