<?php

// Masukkan use statement untuk class PHPSpreadsheet yang dibutuhkan
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class EmployeeController {

    private $db;

    public function __construct() {
        global $db;
        $this->db = $db;
    }

    private function buildWhereClauses($search_term, $department_id, $status) {
        // Logika ini tetap sama
        $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)) {
            $where_clauses[] = " e.status = '{$status}' ";
        }
        return $where_clauses;
    }

    public function index() {
        // Metode ini tetap sama
        // ...
        $total_employees_count = find_by_sql("SELECT COUNT(*) AS total FROM employees")[0]['total'];
        $active_employees_count = find_by_sql("SELECT COUNT(*) AS total FROM employees WHERE status = 'Active'")[0]['total'];
        $on_leave_employees_count = find_by_sql("SELECT COUNT(*) AS total FROM employees WHERE status = 'On Leave'")[0]['total'];
        $total_departments_count = find_by_sql("SELECT COUNT(*) AS total FROM departments")[0]['total'];
        $page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
        $per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 10;
        $department_id = isset($_GET['department_id']) ? (int)$_GET['department_id'] : 0;
        $search_term = isset($_GET['search']) ? $this->db->escape($_GET['search']) : '';
        $status = isset($_GET['status']) ? $this->db->escape($_GET['status']) : '';
        $where_clauses = $this->buildWhereClauses($search_term, $department_id, $status);
        $sql_employees = "SELECT e.*, 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_employees .= " WHERE " . implode(' AND ', $where_clauses);
        }
        $sql_employees .= " ORDER BY e.first_name ASC";
        $sql_count = "SELECT COUNT(*) AS total FROM employees e";
        if (!empty($where_clauses)) {
            $sql_count .= " WHERE " . implode(' AND ', $where_clauses);
        }
        $total_employees_filtered = find_by_sql($sql_count)[0]['total'];
        $total_pages = ceil($total_employees_filtered / $per_page);
        $start = ($page - 1) * $per_page;
        $sql_employees .= " LIMIT {$per_page} OFFSET {$start}";
        $employees = find_by_sql($sql_employees);
        $departments = find_all('departments');
        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,
            'employees' => $employees,
            'departments' => $departments,
            'total_pages' => $total_pages,
            'page' => $page,
            'per_page' => $per_page,
            'search_term' => $search_term,
            'department_id' => $department_id,
            'status' => $status,
        ];
    }
    
    public function export() {
        // Re-use logic for building WHERE clauses
        $department_id = isset($_GET['department_id']) ? (int)$_GET['department_id'] : 0;
        $search_term = isset($_GET['search']) ? $this->db->escape($_GET['search']) : '';
        $status = isset($_GET['status']) ? $this->db->escape($_GET['status']) : '';
        $where_clauses = $this->buildWhereClauses($search_term, $department_id, $status);
    
        // Build the SQL query for export, without pagination
        $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);
    
        // Membuat objek spreadsheet baru
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
    
        // Menentukan judul kolom
        $headers = ['No.', 'Nama Lengkap', 'Email', 'Nomor Telepon', 'Departemen', 'Jabatan', 'Manajer', 'Tanggal Masuk', 'Status', 'Kontak Darurat'];
        $sheet->fromArray($headers, null, 'A1');
    
        // Memasukkan data dari database
        $i = 2; // Mulai dari baris ke-2
        $row_number = 1;
        while ($row = $this->db->fetch_assoc($result)) {
            $rowData = [
                $row_number++,
                $row['first_name'] . ' ' . $row['last_name'],
                $row['email'],
                $row['phone_number'],
                $row['department'],
                $row['job_title'],
                $row['manager_first_name'] . ' ' . $row['manager_last_name'],
                $row['hire_date'],
                $row['status'],
                $row['kontak_darurat']
            ];
            $sheet->fromArray($rowData, null, 'A' . $i++);
        }
    
        // Mengatur header HTTP untuk download
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="employee_data.xlsx"');
        header('Cache-Control: max-age=0');
    
        // Menulis file ke output
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
    }
}