<?php
/**
 * Personnel Model
 * จัดการข้อมูลบุคลากร
 */

require_once 'config/database.php';

class Personnel {
    private $conn;
    private $table = 'personnel';
    
    public function __construct() {
        $database = new Database();
        $this->conn = $database->getConnection();
    }
    
    // ดึงข้อมูลบุคลากรทั้งหมด
    public function getAll($limit = 20, $offset = 0, $search = '', $rank_filter = '', $gender_filter = '') {
        try {
            $sql = "
                SELECT 
                    p.id,
                    p.personnel_id,
                    CONCAT(p.first_name, ' ', p.last_name) AS full_name,
                    p.nickname,
                    p.image_path,
                    p.is_active,
                    p.is_public,
                    pr.rank_name,
                    pr.rank_level,
                    pp.gender,
                    pt.subject_group,
                    pw.work_status,
                    p.created_at,
                    p.updated_at
                FROM {$this->table} p
                LEFT JOIN personnel_personal pp ON p.id = pp.personnel_id
                LEFT JOIN personnel_rank pr ON p.id = pr.personnel_id AND pr.is_current = TRUE
                LEFT JOIN personnel_teaching pt ON p.id = pt.personnel_id AND pt.is_current = TRUE
                LEFT JOIN personnel_work pw ON p.id = pw.personnel_id
                WHERE p.is_active = TRUE
            ";
            
            $params = [];
            
            if (!empty($search)) {
                $sql .= " AND (CONCAT(p.first_name, ' ', p.last_name) LIKE :search 
                         OR p.nickname LIKE :search 
                         OR p.personnel_id LIKE :search)";
                $params[':search'] = "%$search%";
            }
            
            if (!empty($rank_filter)) {
                $sql .= " AND pr.rank_level = :rank_filter";
                $params[':rank_filter'] = $rank_filter;
            }
            
            if (!empty($gender_filter)) {
                $sql .= " AND pp.gender = :gender_filter";
                $params[':gender_filter'] = $gender_filter;
            }
            
            $sql .= " ORDER BY pr.rank_level DESC, p.first_name ASC LIMIT :limit OFFSET :offset";
            
            $stmt = $this->conn->prepare($sql);
            
            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
            $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
            
            $stmt->execute();
            return $stmt->fetchAll();
            
        } catch (Exception $e) {
            error_log("Get all personnel error: " . $e->getMessage());
            return false;
        }
    }
    
    // นับจำนวนบุคลากร
    public function count($search = '', $rank_filter = '', $gender_filter = '') {
        try {
            $sql = "SELECT COUNT(*) as total FROM {$this->table} p
                    LEFT JOIN personnel_personal pp ON p.id = pp.personnel_id
                    LEFT JOIN personnel_rank pr ON p.id = pr.personnel_id AND pr.is_current = TRUE
                    WHERE p.is_active = TRUE";
            
            $params = [];
            
            if (!empty($search)) {
                $sql .= " AND (CONCAT(p.first_name, ' ', p.last_name) LIKE :search 
                         OR p.nickname LIKE :search 
                         OR p.personnel_id LIKE :search)";
                $params[':search'] = "%$search%";
            }
            
            if (!empty($rank_filter)) {
                $sql .= " AND pr.rank_level = :rank_filter";
                $params[':rank_filter'] = $rank_filter;
            }
            
            if (!empty($gender_filter)) {
                $sql .= " AND pp.gender = :gender_filter";
                $params[':gender_filter'] = $gender_filter;
            }
            
            $stmt = $this->conn->prepare($sql);
            
            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }
            
            $stmt->execute();
            $result = $stmt->fetch();
            return $result['total'];
            
        } catch (Exception $e) {
            error_log("Count personnel error: " . $e->getMessage());
            return 0;
        }
    }
    
    // ดึงข้อมูลบุคลากรตาม ID
    public function getById($id) {
        try {
            $sql = "
                SELECT 
                    p.*,
                    pp.citizen_id,
                    pp.birth_date,
                    pp.birth_place,
                    pp.gender,
                    pp.nationality,
                    pp.religion,
                    pp.marital_status,
                    pp.address,
                    pp.phone,
                    pp.email,
                    pp.emergency_contact,
                    pp.emergency_phone,
                    pr.rank_name,
                    pr.rank_level,
                    pr.appointment_date,
                    pr.salary_level,
                    pt.subject_group,
                    pt.class_level,
                    pt.teaching_hours,
                    pt.is_homeroom_teacher,
                    pt.homeroom_class,
                    pw.start_date,
                    pw.retirement_date,
                    pw.work_status,
                    pw.department,
                    pw.work_location,
                    pw.work_phone,
                    pw.work_email
                FROM {$this->table} p
                LEFT JOIN personnel_personal pp ON p.id = pp.personnel_id
                LEFT JOIN personnel_rank pr ON p.id = pr.personnel_id AND pr.is_current = TRUE
                LEFT JOIN personnel_teaching pt ON p.id = pt.personnel_id AND pt.is_current = TRUE
                LEFT JOIN personnel_work pw ON p.id = pw.personnel_id
                WHERE p.id = :id
            ";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->bindValue(':id', $id, PDO::PARAM_INT);
            $stmt->execute();
            
            return $stmt->fetch();
            
        } catch (Exception $e) {
            error_log("Get personnel by ID error: " . $e->getMessage());
            return false;
        }
    }
    
    // สร้างบุคลากรใหม่
    public function create($data) {
        try {
            $this->conn->beginTransaction();
            
            // สร้างข้อมูลหลัก
            $sql = "
                INSERT INTO {$this->table} 
                (personnel_id, title_prefix, first_name, last_name, nickname, image_path, is_public) 
                VALUES (:personnel_id, :title_prefix, :first_name, :last_name, :nickname, :image_path, :is_public)
            ";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->execute([
                ':personnel_id' => $data['personnel_id'],
                ':title_prefix' => $data['title_prefix'] ?? null,
                ':first_name' => $data['first_name'],
                ':last_name' => $data['last_name'],
                ':nickname' => $data['nickname'] ?? null,
                ':image_path' => $data['image_path'] ?? null,
                ':is_public' => $data['is_public'] ?? true
            ]);
            
            $personnel_id = $this->conn->lastInsertId();
            
            // สร้างข้อมูลส่วนตัว
            if (isset($data['personal'])) {
                $this->createPersonal($personnel_id, $data['personal']);
            }
            
            // สร้างข้อมูลวิทยฐานะ
            if (isset($data['rank'])) {
                $this->createRank($personnel_id, $data['rank']);
            }
            
            // สร้างข้อมูลการสอน
            if (isset($data['teaching'])) {
                $this->createTeaching($personnel_id, $data['teaching']);
            }
            
            // สร้างข้อมูลการทำงาน
            if (isset($data['work'])) {
                $this->createWork($personnel_id, $data['work']);
            }
            
            $this->conn->commit();
            return $personnel_id;
            
        } catch (Exception $e) {
            $this->conn->rollBack();
            error_log("Create personnel error: " . $e->getMessage());
            return false;
        }
    }
    
    // อัปเดตข้อมูลบุคลากร
    public function update($id, $data) {
        try {
            $this->conn->beginTransaction();
            
            // อัปเดตข้อมูลหลัก
            $sql = "
                UPDATE {$this->table} 
                SET title_prefix = :title_prefix, 
                    first_name = :first_name, 
                    last_name = :last_name, 
                    nickname = :nickname, 
                    image_path = :image_path, 
                    is_public = :is_public,
                    updated_at = CURRENT_TIMESTAMP
                WHERE id = :id
            ";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->execute([
                ':id' => $id,
                ':title_prefix' => $data['title_prefix'] ?? null,
                ':first_name' => $data['first_name'],
                ':last_name' => $data['last_name'],
                ':nickname' => $data['nickname'] ?? null,
                ':image_path' => $data['image_path'] ?? null,
                ':is_public' => $data['is_public'] ?? true
            ]);
            
            // อัปเดตข้อมูลส่วนตัว
            if (isset($data['personal'])) {
                $this->updatePersonal($id, $data['personal']);
            }
            
            // อัปเดตข้อมูลวิทยฐานะ
            if (isset($data['rank'])) {
                $this->updateRank($id, $data['rank']);
            }
            
            // อัปเดตข้อมูลการสอน
            if (isset($data['teaching'])) {
                $this->updateTeaching($id, $data['teaching']);
            }
            
            // อัปเดตข้อมูลการทำงาน
            if (isset($data['work'])) {
                $this->updateWork($id, $data['work']);
            }
            
            $this->conn->commit();
            return true;
            
        } catch (Exception $e) {
            $this->conn->rollBack();
            error_log("Update personnel error: " . $e->getMessage());
            return false;
        }
    }
    
    // ลบข้อมูลบุคลากร (Soft Delete)
    public function delete($id) {
        try {
            $sql = "UPDATE {$this->table} SET is_active = FALSE WHERE id = :id";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindValue(':id', $id, PDO::PARAM_INT);
            return $stmt->execute();
            
        } catch (Exception $e) {
            error_log("Delete personnel error: " . $e->getMessage());
            return false;
        }
    }
    
    // ดึงสถิติบุคลากร
    public function getStats() {
        try {
            $sql = "
                SELECT 
                    COUNT(*) as total_personnel,
                    SUM(CASE WHEN is_active = TRUE THEN 1 ELSE 0 END) as active_personnel,
                    SUM(CASE WHEN is_public = TRUE THEN 1 ELSE 0 END) as public_personnel,
                    SUM(CASE WHEN pp.gender = 'ชาย' THEN 1 ELSE 0 END) as male_count,
                    SUM(CASE WHEN pp.gender = 'หญิง' THEN 1 ELSE 0 END) as female_count,
                    SUM(CASE WHEN pr.rank_level = 'ครูชำนาญการพิเศษ' THEN 1 ELSE 0 END) as specialized_count,
                    SUM(CASE WHEN pr.rank_level = 'ครูชำนาญการ' THEN 1 ELSE 0 END) as expert_count,
                    SUM(CASE WHEN pr.rank_level = 'ครู' THEN 1 ELSE 0 END) as teacher_count,
                    SUM(CASE WHEN pr.rank_level = 'ครูผู้ช่วย' THEN 1 ELSE 0 END) as assistant_count
                FROM {$this->table} p
                LEFT JOIN personnel_personal pp ON p.id = pp.personnel_id
                LEFT JOIN personnel_rank pr ON p.id = pr.personnel_id AND pr.is_current = TRUE
            ";
            
            $stmt = $this->conn->prepare($sql);
            $stmt->execute();
            return $stmt->fetch();
            
        } catch (Exception $e) {
            error_log("Get personnel stats error: " . $e->getMessage());
            return false;
        }
    }
    
    // ฟังก์ชันช่วยเหลือสำหรับสร้างข้อมูลย่อย
    private function createPersonal($personnel_id, $data) {
        $sql = "
            INSERT INTO personnel_personal 
            (personnel_id, citizen_id, birth_date, birth_place, gender, nationality, religion, 
             marital_status, address, phone, email, emergency_contact, emergency_phone) 
            VALUES (:personnel_id, :citizen_id, :birth_date, :birth_place, :gender, :nationality, 
                    :religion, :marital_status, :address, :phone, :email, :emergency_contact, :emergency_phone)
        ";
        
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute([
            ':personnel_id' => $personnel_id,
            ':citizen_id' => $data['citizen_id'] ?? null,
            ':birth_date' => $data['birth_date'] ?? null,
            ':birth_place' => $data['birth_place'] ?? null,
            ':gender' => $data['gender'] ?? null,
            ':nationality' => $data['nationality'] ?? 'ไทย',
            ':religion' => $data['religion'] ?? null,
            ':marital_status' => $data['marital_status'] ?? null,
            ':address' => $data['address'] ?? null,
            ':phone' => $data['phone'] ?? null,
            ':email' => $data['email'] ?? null,
            ':emergency_contact' => $data['emergency_contact'] ?? null,
            ':emergency_phone' => $data['emergency_phone'] ?? null
        ]);
    }
    
    private function createRank($personnel_id, $data) {
        $sql = "
            INSERT INTO personnel_rank 
            (personnel_id, rank_name, rank_level, appointment_date, appointment_number, 
             appointment_authority, salary_level, is_current) 
            VALUES (:personnel_id, :rank_name, :rank_level, :appointment_date, :appointment_number, 
                    :appointment_authority, :salary_level, :is_current)
        ";
        
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute([
            ':personnel_id' => $personnel_id,
            ':rank_name' => $data['rank_name'],
            ':rank_level' => $data['rank_level'],
            ':appointment_date' => $data['appointment_date'] ?? null,
            ':appointment_number' => $data['appointment_number'] ?? null,
            ':appointment_authority' => $data['appointment_authority'] ?? null,
            ':salary_level' => $data['salary_level'] ?? null,
            ':is_current' => $data['is_current'] ?? true
        ]);
    }
    
    private function createTeaching($personnel_id, $data) {
        $sql = "
            INSERT INTO personnel_teaching 
            (personnel_id, subject_group, subject_name, class_level, teaching_hours, 
             is_homeroom_teacher, homeroom_class, academic_year, semester, is_current) 
            VALUES (:personnel_id, :subject_group, :subject_name, :class_level, :teaching_hours, 
                    :is_homeroom_teacher, :homeroom_class, :academic_year, :semester, :is_current)
        ";
        
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute([
            ':personnel_id' => $personnel_id,
            ':subject_group' => $data['subject_group'],
            ':subject_name' => $data['subject_name'] ?? null,
            ':class_level' => $data['class_level'] ?? null,
            ':teaching_hours' => $data['teaching_hours'] ?? null,
            ':is_homeroom_teacher' => $data['is_homeroom_teacher'] ?? false,
            ':homeroom_class' => $data['homeroom_class'] ?? null,
            ':academic_year' => $data['academic_year'] ?? date('Y') + 543,
            ':semester' => $data['semester'] ?? 'ตลอดปี',
            ':is_current' => $data['is_current'] ?? true
        ]);
    }
    
    private function createWork($personnel_id, $data) {
        $sql = "
            INSERT INTO personnel_work 
            (personnel_id, start_date, position_start_date, retirement_date, work_status, 
             department, work_location, work_phone, work_email) 
            VALUES (:personnel_id, :start_date, :position_start_date, :retirement_date, :work_status, 
                    :department, :work_location, :work_phone, :work_email)
        ";
        
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute([
            ':personnel_id' => $personnel_id,
            ':start_date' => $data['start_date'] ?? date('Y-m-d'),
            ':position_start_date' => $data['position_start_date'] ?? null,
            ':retirement_date' => $data['retirement_date'] ?? null,
            ':work_status' => $data['work_status'] ?? 'ทำงาน',
            ':department' => $data['department'] ?? null,
            ':work_location' => $data['work_location'] ?? null,
            ':work_phone' => $data['work_phone'] ?? null,
            ':work_email' => $data['work_email'] ?? null
        ]);
    }
    
    // ฟังก์ชันช่วยเหลือสำหรับอัปเดตข้อมูลย่อย
    private function updatePersonal($personnel_id, $data) {
        $sql = "
            UPDATE personnel_personal 
            SET citizen_id = :citizen_id, birth_date = :birth_date, birth_place = :birth_place, 
                gender = :gender, nationality = :nationality, religion = :religion, 
                marital_status = :marital_status, address = :address, phone = :phone, 
                email = :email, emergency_contact = :emergency_contact, 
                emergency_phone = :emergency_phone, updated_at = CURRENT_TIMESTAMP
            WHERE personnel_id = :personnel_id
        ";
        
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute([
            ':personnel_id' => $personnel_id,
            ':citizen_id' => $data['citizen_id'] ?? null,
            ':birth_date' => $data['birth_date'] ?? null,
            ':birth_place' => $data['birth_place'] ?? null,
            ':gender' => $data['gender'] ?? null,
            ':nationality' => $data['nationality'] ?? 'ไทย',
            ':religion' => $data['religion'] ?? null,
            ':marital_status' => $data['marital_status'] ?? null,
            ':address' => $data['address'] ?? null,
            ':phone' => $data['phone'] ?? null,
            ':email' => $data['email'] ?? null,
            ':emergency_contact' => $data['emergency_contact'] ?? null,
            ':emergency_phone' => $data['emergency_phone'] ?? null
        ]);
    }
    
    private function updateRank($personnel_id, $data) {
        // ตั้งค่า is_current = false สำหรับข้อมูลเก่า
        $sql = "UPDATE personnel_rank SET is_current = FALSE WHERE personnel_id = :personnel_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([':personnel_id' => $personnel_id]);
        
        // สร้างข้อมูลใหม่
        return $this->createRank($personnel_id, $data);
    }
    
    private function updateTeaching($personnel_id, $data) {
        // ตั้งค่า is_current = false สำหรับข้อมูลเก่า
        $sql = "UPDATE personnel_teaching SET is_current = FALSE WHERE personnel_id = :personnel_id";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute([':personnel_id' => $personnel_id]);
        
        // สร้างข้อมูลใหม่
        return $this->createTeaching($personnel_id, $data);
    }
    
    private function updateWork($personnel_id, $data) {
        $sql = "
            UPDATE personnel_work 
            SET start_date = :start_date, position_start_date = :position_start_date, 
                retirement_date = :retirement_date, work_status = :work_status, 
                department = :department, work_location = :work_location, 
                work_phone = :work_phone, work_email = :work_email, 
                updated_at = CURRENT_TIMESTAMP
            WHERE personnel_id = :personnel_id
        ";
        
        $stmt = $this->conn->prepare($sql);
        return $stmt->execute([
            ':personnel_id' => $personnel_id,
            ':start_date' => $data['start_date'] ?? date('Y-m-d'),
            ':position_start_date' => $data['position_start_date'] ?? null,
            ':retirement_date' => $data['retirement_date'] ?? null,
            ':work_status' => $data['work_status'] ?? 'ทำงาน',
            ':department' => $data['department'] ?? null,
            ':work_location' => $data['work_location'] ?? null,
            ':work_phone' => $data['work_phone'] ?? null,
            ':work_email' => $data['work_email'] ?? null
        ]);
    }
}
?>
