-- =============================================
-- ระบบบริหารงานบุคคล โรงเรียนแม่สายประสิทธิ์ศาสตร์
-- Import Script for Production Database (Fixed for older MySQL)
-- =============================================

USE `cp117896_hr`;

-- สร้างตารางผู้ใช้งานระบบ (Admin Users)
CREATE TABLE `users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(100) NOT NULL,
    `role` ENUM('admin', 'super_admin') DEFAULT 'admin',
    `is_active` BOOLEAN DEFAULT TRUE,
    `last_login` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_username` (`username`),
    INDEX `idx_email` (`email`),
    INDEX `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- สร้างตารางข้อมูลบุคลากรหลัก
CREATE TABLE `personnel` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` VARCHAR(20) NOT NULL UNIQUE COMMENT 'รหัสบุคลากร',
    `title_prefix` VARCHAR(20) DEFAULT NULL COMMENT 'คำนำหน้าชื่อ',
    `first_name` VARCHAR(100) NOT NULL COMMENT 'ชื่อ',
    `last_name` VARCHAR(100) NOT NULL COMMENT 'นามสกุล',
    `nickname` VARCHAR(50) DEFAULT NULL COMMENT 'ชื่อเล่น',
    `image_path` VARCHAR(255) DEFAULT NULL COMMENT 'ที่อยู่รูปภาพ',
    `is_active` BOOLEAN DEFAULT TRUE COMMENT 'สถานะการทำงาน',
    `is_public` BOOLEAN DEFAULT TRUE COMMENT 'แสดงในหน้าสาธารณะ',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_personnel_id` (`personnel_id`),
    INDEX `idx_name` (`first_name`, `last_name`),
    INDEX `idx_active` (`is_active`),
    INDEX `idx_public` (`is_public`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- สร้างตารางข้อมูลส่วนตัว
CREATE TABLE `personnel_personal` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` INT(11) NOT NULL,
    `citizen_id` VARCHAR(13) DEFAULT NULL COMMENT 'เลขบัตรประชาชน',
    `birth_date` DATE DEFAULT NULL COMMENT 'วันเกิด',
    `birth_place` VARCHAR(100) DEFAULT NULL COMMENT 'สถานที่เกิด',
    `gender` ENUM('ชาย', 'หญิง') DEFAULT NULL COMMENT 'เพศ',
    `nationality` VARCHAR(50) DEFAULT 'ไทย' COMMENT 'สัญชาติ',
    `religion` VARCHAR(50) DEFAULT NULL COMMENT 'ศาสนา',
    `marital_status` ENUM('โสด', 'สมรส', 'หย่า', 'หม้าย') DEFAULT NULL COMMENT 'สถานะสมรส',
    `address` TEXT DEFAULT NULL COMMENT 'ที่อยู่',
    `phone` VARCHAR(20) DEFAULT NULL COMMENT 'เบอร์โทรศัพท์',
    `email` VARCHAR(100) DEFAULT NULL COMMENT 'อีเมล',
    `emergency_contact` VARCHAR(100) DEFAULT NULL COMMENT 'ผู้ติดต่อกรณีฉุกเฉิน',
    `emergency_phone` VARCHAR(20) DEFAULT NULL COMMENT 'เบอร์ติดต่อฉุกเฉิน',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`personnel_id`) REFERENCES `personnel`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `uk_citizen_id` (`citizen_id`),
    INDEX `idx_gender` (`gender`),
    INDEX `idx_birth_date` (`birth_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- สร้างตารางข้อมูลวิทยฐานะ
CREATE TABLE `personnel_rank` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` INT(11) NOT NULL,
    `rank_name` VARCHAR(100) NOT NULL COMMENT 'ชื่อวิทยฐานะ',
    `rank_level` ENUM('ครูผู้ช่วย', 'ครู', 'ครูชำนาญการ', 'ครูชำนาญการพิเศษ', 'ครูเชี่ยวชาญ', 'ครูเชี่ยวชาญพิเศษ') NOT NULL COMMENT 'ระดับวิทยฐานะ',
    `appointment_date` DATE DEFAULT NULL COMMENT 'วันที่แต่งตั้ง',
    `appointment_number` VARCHAR(50) DEFAULT NULL COMMENT 'เลขที่คำสั่งแต่งตั้ง',
    `appointment_authority` VARCHAR(200) DEFAULT NULL COMMENT 'ผู้มีอำนาจแต่งตั้ง',
    `salary_level` INT(3) DEFAULT NULL COMMENT 'ระดับเงินเดือน',
    `is_current` BOOLEAN DEFAULT TRUE COMMENT 'วิทยฐานะปัจจุบัน',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`personnel_id`) REFERENCES `personnel`(`id`) ON DELETE CASCADE,
    INDEX `idx_rank_level` (`rank_level`),
    INDEX `idx_current` (`is_current`),
    INDEX `idx_appointment_date` (`appointment_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- สร้างตารางข้อมูลการสอน
CREATE TABLE `personnel_teaching` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` INT(11) NOT NULL,
    `subject_group` VARCHAR(100) NOT NULL COMMENT 'กลุ่มสาระการเรียนรู้',
    `subject_name` VARCHAR(200) DEFAULT NULL COMMENT 'วิชาที่สอน',
    `class_level` VARCHAR(50) DEFAULT NULL COMMENT 'ระดับชั้น',
    `teaching_hours` INT(3) DEFAULT NULL COMMENT 'ชั่วโมงสอนต่อสัปดาห์',
    `is_homeroom_teacher` BOOLEAN DEFAULT FALSE COMMENT 'เป็นครูประจำชั้น',
    `homeroom_class` VARCHAR(20) DEFAULT NULL COMMENT 'ชั้นที่ดูแล',
    `academic_year` VARCHAR(9) NOT NULL COMMENT 'ปีการศึกษา (เช่น 2567)',
    `semester` ENUM('1', '2', 'ตลอดปี') DEFAULT 'ตลอดปี' COMMENT 'ภาคเรียน',
    `is_current` BOOLEAN DEFAULT TRUE COMMENT 'ข้อมูลปัจจุบัน',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`personnel_id`) REFERENCES `personnel`(`id`) ON DELETE CASCADE,
    INDEX `idx_subject_group` (`subject_group`),
    INDEX `idx_academic_year` (`academic_year`),
    INDEX `idx_current` (`is_current`),
    INDEX `idx_homeroom` (`is_homeroom_teacher`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- สร้างตารางข้อมูลการทำงานและเกษียณ
CREATE TABLE `personnel_work` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` INT(11) NOT NULL,
    `start_date` DATE NOT NULL COMMENT 'วันที่เริ่มทำงาน',
    `position_start_date` DATE DEFAULT NULL COMMENT 'วันที่เริ่มตำแหน่งปัจจุบัน',
    `retirement_date` DATE DEFAULT NULL COMMENT 'วันที่เกษียณ',
    `work_status` ENUM('ทำงาน', 'ลาพัก', 'ลาออก', 'เกษียณ', 'เสียชีวิต') DEFAULT 'ทำงาน' COMMENT 'สถานะการทำงาน',
    `department` VARCHAR(100) DEFAULT NULL COMMENT 'แผนก/ฝ่าย',
    `work_location` VARCHAR(200) DEFAULT NULL COMMENT 'สถานที่ทำงาน',
    `work_phone` VARCHAR(20) DEFAULT NULL COMMENT 'เบอร์โทรที่ทำงาน',
    `work_email` VARCHAR(100) DEFAULT NULL COMMENT 'อีเมลที่ทำงาน',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`personnel_id`) REFERENCES `personnel`(`id`) ON DELETE CASCADE,
    INDEX `idx_work_status` (`work_status`),
    INDEX `idx_start_date` (`start_date`),
    INDEX `idx_retirement_date` (`retirement_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- สร้างตารางข้อมูลอื่นๆ และวินัย
CREATE TABLE `personnel_other` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `personnel_id` INT(11) NOT NULL,
    `special_skills` TEXT DEFAULT NULL COMMENT 'ความสามารถพิเศษ',
    `awards` TEXT DEFAULT NULL COMMENT 'รางวัลที่ได้รับ',
    `training_courses` TEXT DEFAULT NULL COMMENT 'การอบรม/สัมมนา',
    `disciplinary_actions` TEXT DEFAULT NULL COMMENT 'การดำเนินการทางวินัย',
    `notes` TEXT DEFAULT NULL COMMENT 'หมายเหตุ',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`personnel_id`) REFERENCES `personnel`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- สร้างตารางข้อมูลการเข้าสู่ระบบ (Logs)
CREATE TABLE `login_logs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `ip_address` VARCHAR(45) NOT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `login_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `logout_time` TIMESTAMP NULL,
    `is_successful` BOOLEAN DEFAULT TRUE,
    `failure_reason` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_login_time` (`login_time`),
    INDEX `idx_ip_address` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- สร้างตารางข้อมูลการเปลี่ยนแปลง (Audit Logs) - แก้ไข JSON เป็น TEXT
CREATE TABLE `audit_logs` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) DEFAULT NULL,
    `table_name` VARCHAR(50) NOT NULL,
    `record_id` INT(11) NOT NULL,
    `action` ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    `old_values` TEXT DEFAULT NULL,
    `new_values` TEXT DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_table_record` (`table_name`, `record_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- เพิ่มผู้ดูแลระบบเริ่มต้น
INSERT INTO `users` (`username`, `email`, `password`, `full_name`, `role`) VALUES
('admin', 'admin@maesai.ac.th', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ผู้ดูแลระบบ', 'super_admin');

-- เพิ่มข้อมูลครูจากระบบเดิม
INSERT INTO `personnel` (`personnel_id`, `first_name`, `last_name`, `nickname`, `image_path`, `is_public`) VALUES
('P001', 'เขมินทรา', 'ชุมมัธยา', 'เขมินทรา', 'images/khamintra.jpg', TRUE),
('P002', 'หทัยชนก', 'วงศ์ใหญ่', 'หทัยชนก', 'images/hattayachanok.jpg', TRUE),
('P003', 'นัทพงศ์', 'ยะถา', 'นัทพงศ์', 'images/natpong.jpg', TRUE),
('P004', 'ฐาปกรณ์', 'รื่นเริง', 'ฐาปกรณ์', 'images/thapakorn.jpg', TRUE),
('P005', 'เดชพงษ์', 'อุณชาติ', 'เดชพงษ์', 'images/detpong.jpg', TRUE),
('P006', 'สุวิชา', 'จิตบาล', 'สุวิชา', 'images/suwicha.jpg', TRUE),
('P007', 'กนกวรรณ', 'ศรีจันทร์', 'กนกวรรณ', 'images/kanokwan.jpg', TRUE),
('P008', 'ปิยมาส', 'ปัญญา', 'ปิยมาส', 'images/piyamas.jpg', TRUE),
('P009', 'จารุตา', 'งามจันทราทิพย์', 'จารุตา', 'images/jaruta.jpg', TRUE),
('P010', 'ศัตยา', 'ทรินทร์', 'ศัตยา', 'images/sattaya.jpg', TRUE),
('P011', 'ชัยวัฒน์', 'ข้ามหก', 'ชัยวัฒน์', 'images/chaiwat.jpg', TRUE),
('P012', 'ว่าที่ร้อยตรีวีรภัทร', 'ตาสาย', 'วีรภัทร', 'images/veeraphat.jpg', TRUE),
('P013', 'เอื้ออารีย์', 'ปันศรี', 'เอื้ออารีย์', 'images/ueaari.jpg', TRUE),
('P014', 'อาภัสรา', 'คันธะวงค์', 'อาภัสรา', 'images/aphasara.jpg', TRUE),
('P015', 'อัมพวา', 'ดวงเอ้ย', 'อัมพวา', 'images/ampawa.jpg', TRUE),
('P016', 'เกศแก้ว', 'ใจซื่อดี', 'เกศแก้ว', 'images/ketsakaew.jpg', TRUE),
('P017', 'ชัญญานุช', 'ไชยวงค์', 'ชัญญานุช', 'images/chanyanut.jpg', TRUE);

-- เพิ่มข้อมูลวิทยฐานะ
INSERT INTO `personnel_rank` (`personnel_id`, `rank_name`, `rank_level`, `is_current`) VALUES
(1, 'ครูชำนาญการพิเศษ', 'ครูชำนาญการพิเศษ', TRUE),
(2, 'ครูชำนาญการพิเศษ', 'ครูชำนาญการพิเศษ', TRUE),
(3, 'ครูชำนาญการพิเศษ', 'ครูชำนาญการพิเศษ', TRUE),
(4, 'ครูชำนาญการ', 'ครูชำนาญการ', TRUE),
(5, 'ครูชำนาญการ', 'ครูชำนาญการ', TRUE),
(6, 'ครูชำนาญการ', 'ครูชำนาญการ', TRUE),
(7, 'ครูชำนาญการ', 'ครูชำนาญการ', TRUE),
(8, 'ครูชำนาญการ', 'ครูชำนาญการ', TRUE),
(9, 'ครูชำนาญการ', 'ครูชำนาญการ', TRUE),
(10, 'ครูชำนาญการ', 'ครูชำนาญการ', TRUE),
(11, 'ครูชำนาญการ', 'ครูชำนาญการ', TRUE),
(12, 'ครู', 'ครู', TRUE),
(13, 'ครู', 'ครู', TRUE),
(14, 'ครูผู้ช่วย', 'ครูผู้ช่วย', TRUE),
(15, 'ครูผู้ช่วย', 'ครูผู้ช่วย', TRUE),
(16, 'ครูผู้ช่วย', 'ครูผู้ช่วย', TRUE),
(17, 'ครูผู้ช่วย', 'ครูผู้ช่วย', TRUE);

-- เพิ่มข้อมูลการสอน
INSERT INTO `personnel_teaching` (`personnel_id`, `subject_group`, `academic_year`, `is_current`) VALUES
(1, 'การงานอาชีพ', '2567', TRUE),
(2, 'การงานอาชีพ', '2567', TRUE),
(3, 'การงานอาชีพ', '2567', TRUE),
(4, 'การงานอาชีพ', '2567', TRUE),
(5, 'การงานอาชีพ', '2567', TRUE),
(6, 'การงานอาชีพ', '2567', TRUE),
(7, 'การงานอาชีพ', '2567', TRUE),
(8, 'การงานอาชีพ', '2567', TRUE),
(9, 'การงานอาชีพ', '2567', TRUE),
(10, 'การงานอาชีพ', '2567', TRUE),
(11, 'การงานอาชีพ', '2567', TRUE),
(12, 'การงานอาชีพ', '2567', TRUE),
(13, 'การงานอาชีพ', '2567', TRUE),
(14, 'การงานอาชีพ', '2567', TRUE),
(15, 'การงานอาชีพ', '2567', TRUE),
(16, 'การงานอาชีพ', '2567', TRUE),
(17, 'การงานอาชีพ', '2567', TRUE);

-- เพิ่มข้อมูลการทำงาน
INSERT INTO `personnel_work` (`personnel_id`, `start_date`, `work_status`) VALUES
(1, '2020-01-01', 'ทำงาน'),
(2, '2020-01-01', 'ทำงาน'),
(3, '2020-01-01', 'ทำงาน'),
(4, '2020-01-01', 'ทำงาน'),
(5, '2020-01-01', 'ทำงาน'),
(6, '2020-01-01', 'ทำงาน'),
(7, '2020-01-01', 'ทำงาน'),
(8, '2020-01-01', 'ทำงาน'),
(9, '2020-01-01', 'ทำงาน'),
(10, '2020-01-01', 'ทำงาน'),
(11, '2020-01-01', 'ทำงาน'),
(12, '2020-01-01', 'ทำงาน'),
(13, '2020-01-01', 'ทำงาน'),
(14, '2020-01-01', 'ทำงาน'),
(15, '2020-01-01', 'ทำงาน'),
(16, '2020-01-01', 'ทำงาน'),
(17, '2020-01-01', 'ทำงาน');
