-- Database Schema: ระบบขออนุมัติเดินทางประชุม-อบรม
-- หมายเหตุ: การสร้างตารางนี้อ้างอิงกับ System Database ส่วน HR Database (`hr_person`) ถือว่าเป็นตารางที่มีอยู่แล้ว (External/Reference)

-- ==============================================
-- 1. System Roles & Permissions
-- ==============================================
CREATE TABLE `system_roles` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL UNIQUE COMMENT 'เช่น Admin, User, Approver_L1',
    `description` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `system_user_roles` (
    `user_id` VARCHAR(50) NOT NULL COMMENT 'เชื่อมกับ hr_person.HR_CID',
    `role_id` INT NOT NULL,
    PRIMARY KEY (`user_id`, `role_id`),
    CONSTRAINT `fk_user_roles_role_id` FOREIGN KEY (`role_id`) REFERENCES `system_roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- 2. Trip Requests (การขอเดินทาง)
-- ==============================================
CREATE TABLE `trip_requests` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` VARCHAR(50) NOT NULL COMMENT 'ผู้ขอเดินทาง เชื่อมกับ hr_person.HR_CID',
    `doc_number` VARCHAR(50) DEFAULT NULL COMMENT 'เลขอ้างอิงหนังสือ',
    `doc_no` VARCHAR(50) DEFAULT NULL COMMENT 'เลขที่หนังสือ',
    `doc_date` DATE DEFAULT NULL COMMENT 'ลงวันที่ของหนังสืออ้างถึง',
    `doc_link` VARCHAR(500) DEFAULT NULL COMMENT 'ลิงค์ไฟล์เอกสาร',
    `title` VARCHAR(255) NOT NULL COMMENT 'ชื่อเรื่อง',
    `objective` TEXT NOT NULL COMMENT 'วัตถุประสงค์',
    `personnel_type` VARCHAR(50) DEFAULT NULL COMMENT 'ประเภทบุคลากร',
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `location` VARCHAR(255) NOT NULL COMMENT 'สถานที่ไปราชการ/อบรม',
    `organizer` VARCHAR(255) DEFAULT NULL COMMENT 'ผู้จัด',
    `depart_from` ENUM('สำนักงาน', 'บ้านพัก', 'อื่นๆ') DEFAULT 'สำนักงาน',
    `depart_address` TEXT DEFAULT NULL,
    `depart_datetime` DATETIME DEFAULT NULL,
    `arrive_at` ENUM('สำนักงาน', 'บ้านพัก', 'อื่นๆ') DEFAULT 'สำนักงาน',
    `arrive_address` TEXT DEFAULT NULL,
    `arrive_datetime` DATETIME DEFAULT NULL,
    `vehicle_type` VARCHAR(100) DEFAULT NULL COMMENT 'เครื่องบิน, รถยนต์ส่วนตัว, ฯลฯ',
    `vehicle_plate` VARCHAR(50) DEFAULT NULL,
    `budget_source` VARCHAR(100) DEFAULT NULL COMMENT 'แหล่งงบ',
    `budget_remark` TEXT DEFAULT NULL,
    `budget` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'งบประมาณที่ขอ',
    `cancel_reason` TEXT DEFAULT NULL COMMENT 'เหตุผลการยกเลิก',
    `revise_reason` TEXT DEFAULT NULL COMMENT 'เหตุผลการแก้ไข',
    `status` ENUM('Draft', 'Pending', 'Approved', 'Rejected', 'Cancelled', 'Pending Cancel', 'Pending Revise') DEFAULT 'Draft',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- 3. Trip Approvals (ประวัติการอนุมัติ / Workflow)
-- ==============================================
CREATE TABLE `trip_approvals` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `request_id` INT NOT NULL,
    `approver_id` VARCHAR(50) NOT NULL COMMENT 'ผู้อนุมัติ เชื่อมกับ hr_person.HR_CID',
    `step` INT NOT NULL DEFAULT 1 COMMENT 'ลำดับขั้นการอนุมัติ',
    `status` ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
    `comments` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_trip_approvals_request_id` FOREIGN KEY (`request_id`) REFERENCES `trip_requests` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- 4. PDF Templates & Fields (ดีไซน์เนอร์ลากวาง)
-- ==============================================
CREATE TABLE `pdf_templates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL COMMENT 'ชื่อแบบฟอร์ม',
    `file_path` VARCHAR(255) NOT NULL COMMENT 'พาร์ทไฟล์ PDF ต้นฉบับ',
    `version` INT DEFAULT 1 COMMENT 'เวอร์ชันของแบบฟอร์ม',
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `pdf_template_fields` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `template_id` INT NOT NULL,
    `field_name` VARCHAR(50) NOT NULL COMMENT 'ชื่อตัวแปร เช่น {firstname}',
    `field_type` ENUM('Text', 'Image', 'QR', 'Signature', 'Checkbox') DEFAULT 'Text',
    `page_num` INT DEFAULT 1 COMMENT 'หน้าที่ตัวแปรนี้อยู่',
    `x_pos` DECIMAL(8,4) NOT NULL COMMENT 'พิกัดแนวแกน X (mm)',
    `y_pos` DECIMAL(8,4) NOT NULL COMMENT 'พิกัดแนวแกน Y (mm)',
    `width` DECIMAL(8,4) DEFAULT 0 COMMENT 'ความกว้าง (ถ้าเป็นรูปหรือกล่อง)',
    `height` DECIMAL(8,4) DEFAULT 0 COMMENT 'ความสูง (ถ้าเป็นรูปหรือกล่อง)',
    `font_size` INT DEFAULT 14 COMMENT 'ขนาดตัวอักษร',
    `font_family` VARCHAR(50) DEFAULT 'freeserif' COMMENT 'แบบอักษร',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_pdf_fields_template_id` FOREIGN KEY (`template_id`) REFERENCES `pdf_templates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- 5. Google Sheets Integration Settings
-- ==============================================
CREATE TABLE `google_sheet_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `spreadsheet_id` VARCHAR(100) NOT NULL,
    `api_key` VARCHAR(255) DEFAULT NULL COMMENT 'Google API Key',
    `sheet_name` VARCHAR(100) NOT NULL,
    `sync_range` VARCHAR(50) NOT NULL COMMENT 'เช่น A2:B',
    `column_mapping` JSON NOT NULL COMMENT 'เก็บ JSON การแมปคอลัมน์ (เช่น {"0": "doc_number", "1": "title"})',
    `last_synced_at` TIMESTAMP NULL DEFAULT NULL,
    `status` ENUM('Active', 'Inactive', 'Error') DEFAULT 'Active',
    `error_message` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- 6. Audit Logs
-- ==============================================
CREATE TABLE `audit_logs` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `user_id` VARCHAR(50) DEFAULT NULL COMMENT 'ผู้กระทำ (NULL ได้หากทำโดยระบบ)',
    `action` VARCHAR(50) NOT NULL COMMENT 'เช่น CREATE, UPDATE, DELETE, LOGIN, APPROVE',
    `entity_type` VARCHAR(50) NOT NULL COMMENT 'ชื่อตาราง เช่น trip_requests',
    `entity_id` INT DEFAULT NULL COMMENT 'ID ของตารางที่ถูกกระทำ',
    `details` TEXT DEFAULT NULL COMMENT 'เก็บ JSON ข้อมูลที่เปลี่ยนแปลง',
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- 7. Initial Data (ข้อมูลเริ่มต้น)
-- ==============================================
INSERT INTO `system_roles` (`name`, `description`) VALUES 
('Admin', 'ผู้ดูแลระบบ'),
('User', 'ผู้ใช้งานทั่วไป (ขอเดินทาง)'),
('Approver_L1', 'ผู้อนุมัติระดับที่ 1 (หัวหน้างาน)'),
('Approver_L2', 'ผู้อนุมัติระดับที่ 2 (ผู้อำนวยการ)');

-- ==============================================
-- 8. Approval Hierarchy (การตั้งค่าสายงานอนุมัติรายบุคคล)
-- ==============================================
CREATE TABLE `approval_hierarchy` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` VARCHAR(50) NOT NULL COMMENT 'รหัสพนักงานผู้ขอ',
    `approver_id` VARCHAR(50) NOT NULL COMMENT 'รหัสพนักงานผู้อนุมัติ',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================
-- 9. System Settings (ตั้งค่าระบบ เช่น LINE Notify Token)
-- ==============================================
CREATE TABLE `system_settings` (
    `setting_key` VARCHAR(50) PRIMARY KEY,
    `setting_value` TEXT,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;