-- สร้างฐานข้อมูล (ถ้ายังไม่มี)
-- CREATE DATABASE IF NOT EXISTS employee_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE employee_db;

-- 1. ตารางข้อมูลพนักงาน (ตารางหลัก - อัปเดตเพิ่ม role)
CREATE TABLE IF NOT EXISTS `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_code` varchar(20) NOT NULL UNIQUE COMMENT 'รหัสพนักงาน',
  `password` varchar(255) NOT NULL COMMENT 'รหัสผ่าน (ควรเข้ารหัส)',
  `contact_number` varchar(20) DEFAULT NULL COMMENT 'เบอร์โทรศัพท์',
  `nationality` varchar(50) DEFAULT NULL COMMENT 'สัญชาติ',
  `gender` enum('Male','Female','Other') DEFAULT NULL COMMENT 'เพศ',
  `start_date` date DEFAULT NULL COMMENT 'วันที่เริ่มงาน',
  `company_name` varchar(100) DEFAULT NULL COMMENT 'ชื่อบริษัท',
  `division` varchar(100) DEFAULT NULL COMMENT 'ฝ่าย',
  `department` varchar(100) DEFAULT NULL COMMENT 'แผนก',
  `department_name` varchar(255) DEFAULT NULL COMMENT 'ชื่อแผนกแบบเต็ม',
  `position` varchar(100) DEFAULT NULL COMMENT 'ตำแหน่ง',
  `role` enum('Admin','Manager','User') NOT NULL DEFAULT 'User' COMMENT 'สิทธิ์การใช้งาน',
  `profile_img` varchar(255) DEFAULT NULL COMMENT 'ชื่อไฟล์รูปโปรไฟล์',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 2. ตารางข้อมูลบัตรประจำตัว (อ้างอิงรหัสพนักงาน)
CREATE TABLE IF NOT EXISTS `id_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_code` varchar(20) NOT NULL COMMENT 'อ้างอิงรหัสพนักงาน',
  `id_number` varchar(20) DEFAULT NULL COMMENT 'เลขบัตรประชาชน',
  `title_th` varchar(20) DEFAULT NULL COMMENT 'คำนำหน้า (ไทย)',
  `fname_th` varchar(50) DEFAULT NULL COMMENT 'ชื่อ (ไทย)',
  `lname_th` varchar(50) DEFAULT NULL COMMENT 'นามสกุล (ไทย)',
  `title_en` varchar(20) DEFAULT NULL COMMENT 'คำนำหน้า (อังกฤษ)',
  `fname_en` varchar(50) DEFAULT NULL COMMENT 'ชื่อ (อังกฤษ)',
  `lname_en` varchar(50) DEFAULT NULL COMMENT 'นามสกุล (อังกฤษ)',
  `house_no` varchar(50) DEFAULT NULL COMMENT 'บ้านเลขที่',
  `moo` varchar(10) DEFAULT NULL COMMENT 'หมู่',
  `sub_district` varchar(50) DEFAULT NULL COMMENT 'ตำบล/แขวง',
  `district` varchar(50) DEFAULT NULL COMMENT 'อำเภอ/เขต',
  `province` varchar(50) DEFAULT NULL COMMENT 'จังหวัด',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`emp_code`) REFERENCES `employees`(`emp_code`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 3. ตารางเอกสารเดินทาง/พาสปอร์ต
CREATE TABLE IF NOT EXISTS `travel_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL COMMENT 'อ้างอิง ID พนักงาน',
  `passport_type` varchar(50) DEFAULT NULL,
  `country_code` varchar(10) DEFAULT NULL,
  `document_type` varchar(50) DEFAULT NULL,
  `document_number` varchar(50) DEFAULT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `nationality` varchar(50) DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `place_of_birth` varchar(100) DEFAULT NULL,
  `place_of_issue` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 4. ตารางข้อมูลวีซ่า
CREATE TABLE IF NOT EXISTS `visas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL COMMENT 'อ้างอิง ID พนักงาน',
  `visa_type` varchar(50) DEFAULT NULL,
  `visa_number` varchar(50) NOT NULL,
  `place_of_issue` varchar(100) DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 5. ตารางประวัติการเดินทางเข้าประเทศ
CREATE TABLE IF NOT EXISTS `arrival_records` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL COMMENT 'อ้างอิง ID พนักงาน',
  `immigration_office` varchar(100) DEFAULT NULL,
  `arrival_date` date DEFAULT NULL,
  `permitted_stay_until` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 6. ตารางข้อมูลที่อยู่ในประเทศไทย
CREATE TABLE IF NOT EXISTS `addresses_thailand` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL COMMENT 'อ้างอิง ID พนักงาน',
  `house_no` varchar(50) DEFAULT NULL,
  `moo_building` varchar(100) DEFAULT NULL,
  `soi` varchar(100) DEFAULT NULL,
  `road` varchar(100) DEFAULT NULL,
  `sub_district` varchar(50) DEFAULT NULL,
  `district` varchar(50) DEFAULT NULL,
  `province` varchar(50) DEFAULT NULL,
  `zip_code` varchar(10) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;