<?php
// Asumsi kelas MySqli_DB sudah di-load

class Employee {

    private $db;

    public function __construct() {
        // Asumsi kelas koneksi database sudah ada
        $this->db = new MySqli_DB();
    }

    // Mengambil metrik dashboard
    public function getDashboardMetrics() {
        $total_employees_count = $this->db->query("SELECT COUNT(*) AS total FROM employees")->fetch_assoc()['total'];
        $active_employees_count = $this->db->query("SELECT COUNT(*) AS total FROM employees WHERE status = 'Active'")->fetch_assoc()['total'];
        $on_leave_employees_count = $this->db->query("SELECT COUNT(*) AS total FROM employees WHERE status = 'On Leave'")->fetch_assoc()['total'];
        $total_departments_count = $this->db->query("SELECT COUNT(*) AS total FROM departments")->fetch_assoc()['total'];

        return [
            'total_employees_count' => $total_employees_count,
            'active_employees_count' => $active_employees_count,
            'on_leave_employees_count' => $on_leave_employees_count,
            'total_departments_count' => $total_departments_count,
        ];
    }

    // Mengambil data karyawan dengan filter dan pagination
    public function findEmployees($limit, $offset, $search_term, $department_id, $status) {
        $where_clauses = [];
        if (!empty($search_term)) {
            $search_term = $this->db->escape($search_term);
            $where_clauses[] = " (e.first_name LIKE '%{$search_term}%' OR e.last_name LIKE '%{$search_term}%' OR e.id LIKE '%{$search_term}%') ";
        }
        if ($department_id > 0) {
            $where_clauses[] = " e.department_id = {$department_id} ";
        }
        if (!empty($status)) {
            $status = $this->db->escape($status);
            $where_clauses[] = " e.status = '{$status}' ";
        }

        $sql = "SELECT e.id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_title, e.status, e.kontak_darurat, d.name AS department, m.first_name AS manager_first_name, m.last_name AS manager_last_name
                FROM employees e
                LEFT JOIN departments d ON e.department_id = d.department_id
                LEFT JOIN employees m ON e.manager_id = m.id";

        if (!empty($where_clauses)) {
            $sql .= " WHERE " . implode(' AND ', $where_clauses);
        }

        $sql .= " LIMIT {$limit} OFFSET {$offset}";

        $result = $this->db->query($sql);
        return $this->db->fetch_all($result);
    }
    
    // Mengambil semua departemen
    public function findDepartments() {
        $sql = "SELECT * FROM departments";
        $result = $this->db->query($sql);
        return $this->db->fetch_all($result);
    }

    // Menghitung total data karyawan yang difilter
    public function getTotalFilteredCount($search_term, $department_id, $status) {
        $where_clauses = [];
        if (!empty($search_term)) {
            $search_term = $this->db->escape($search_term);
            $where_clauses[] = " (first_name LIKE '%{$search_term}%' OR last_name LIKE '%{$search_term}%' OR id LIKE '%{$search_term}%') ";
        }
        if ($department_id > 0) {
            $where_clauses[] = " department_id = {$department_id} ";
        }
        if (!empty($status)) {
            $status = $this->db->escape($status);
            $where_clauses[] = " status = '{$status}' ";
        }

        $sql = "SELECT COUNT(*) AS total FROM employees";
        if (!empty($where_clauses)) {
            $sql .= " WHERE " . implode(' AND ', $where_clauses);
        }

        return $this->db->query($sql)->fetch_assoc()['total'];
    }

    // Mengambil data untuk ekspor Excel
    public function findEmployeesForExport($search_term, $department_id, $status) {
        $where_clauses = [];
        if (!empty($search_term)) {
            $search_term = $this->db->escape($search_term);
            $where_clauses[] = " (e.first_name LIKE '%{$search_term}%' OR e.last_name LIKE '%{$search_term}%' OR e.id LIKE '%{$search_term}%') ";
        }
        if ($department_id > 0) {
            $where_clauses[] = " e.department_id = {$department_id} ";
        }
        if (!empty($status)) {
            $status = $this->db->escape($status);
            $where_clauses[] = " e.status = '{$status}' ";
        }

        $sql = "SELECT e.id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date, e.job_title, e.status, e.kontak_darurat, d.name AS department, m.first_name AS manager_first_name, m.last_name AS manager_last_name
                FROM employees e
                LEFT JOIN departments d ON e.department_id = d.department_id
                LEFT JOIN employees m ON e.manager_id = m.id";

        if (!empty($where_clauses)) {
            $sql .= " WHERE " . implode(' AND ', $where_clauses);
        }

        $result = $this->db->query($sql);
        return $this->db->fetch_all($result);
    }
}
