<?php
class Employee_model {
    private $db;

    public function __construct() {
        $this->db = new Database;

        if ($this->db == false) {
            echo "<script>console.log('DB connect failed');</script>";
        } else {
            //console log optional
        }
    }

    public function getAllEmployees($limit = 0, $offset = 0, $search = '') {
        $sql = "SELECT * FROM employees";
        $conds = [];

        if ($search !== '') {
            $s = $this->db->escape($search);
            $conds[] = "(name LIKE '%$s%' OR position LIKE '%$s%' OR email LIKE '%$s%')";
        }

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

        $sql .= " ORDER BY id DESC";

        if ($limit > 0) {
            $sql .= " LIMIT $limit OFFSET $offset";
        }

        $result = $this->db->query($sql);
        if ($result && $result->num_rows > 0) {
            return mysqli_fetch_all($result, MYSQLI_ASSOC);
        }
        return [];
    }

    public function countEmployees($search = '') {
        $sql = "SELECT COUNT(*) as cnt FROM employees";
        if ($search !== '') {
            $s = $this->db->escape($search);
            $sql .= " WHERE (name LIKE '%$s%' OR position LIKE '%$s%' OR email LIKE '%$s%')";
        }
        $res = $this->db->query($sql);
        if ($res) {
            $row = $res->fetch_assoc();
            return intval($row['cnt']);
        }
        return 0;
    }

    public function getEmployeeById($id) {
        $id = intval($id);
        $sql = "SELECT * FROM employees WHERE id='$id' LIMIT 1";
        $result = $this->db->query($sql);
        if ($result) return $result->fetch_assoc();
        return null;
    }

    public function insertEmployee($data) {
        // expect validated data and already escaped where needed
        $name = $this->db->escape($data['name']);
        $position = $this->db->escape($data['position']);
        $phone = $this->db->escape($data['phone']);
        $email = $this->db->escape($data['email']);
        $address = $this->db->escape($data['address']);
        $join_date = $this->db->escape($data['join_date']);
        $photo = isset($data['photo']) ? $this->db->escape($data['photo']) : null;

        $sql = "INSERT INTO employees (name, position, phone, email, address, join_date, photo)
                VALUES ('$name','$position','$phone','$email','$address','$join_date','" . ($photo ? $photo : '') . "')";
        return $this->db->query($sql);
    }

    public function updateEmployee($data) {
        $id = intval($data['id']);
        $name = $this->db->escape($data['name']);
        $position = $this->db->escape($data['position']);
        $phone = $this->db->escape($data['phone']);
        $email = $this->db->escape($data['email']);
        $address = $this->db->escape($data['address']);
        $join_date = $this->db->escape($data['join_date']);
        $photo = isset($data['photo']) ? $this->db->escape($data['photo']) : null;

        $sql = "UPDATE employees SET 
                    name='$name', position='$position', phone='$phone', 
                    email='$email', address='$address', join_date='$join_date'";

        if ($photo !== null && $photo !== '') {
            $sql .= ", photo='$photo'";
        }

        $sql .= " WHERE id='$id'";

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

    public function deleteEmployee($id) {
        $id = intval($id);
        // optionally, get photo filename to delete the file
        $row = $this->getEmployeeById($id);
        $sql = "DELETE FROM employees WHERE id='$id'";
        $res = $this->db->query($sql);
        if ($res && $row && !empty($row['photo'])) {
            $file = __DIR__ . "/../../public/img/employees/" . $row['photo'];
            if (file_exists($file)) @unlink($file);
        }
        return $res;
    }
}
?>
