<?php
declare(strict_types=1);
namespace Gabinete\Models;
use PDO;

/**
 * Model de colaboradores (inclui column photo_path).
 * Namespace evita colisão com qualquer "Employees" antigo.
 */
class Employee
{
    private PDO $pdo;

    public function __construct()
    {
        $host = \defined('DB_HOST') ? \DB_HOST : '127.0.0.1';
        $port = \defined('DB_PORT') ? (int) \DB_PORT : 3306;
        $db = \defined('DB_NAME') ? \DB_NAME : 'gabinete_bd';
        $user = \defined('DB_USER') ? \DB_USER : 'root';
        $pass = \defined('DB_PASS') ? \DB_PASS : '';

        $dsn = "mysql:host={$host};port={$port};dbname={$db};charset=utf8mb4";
        $this->pdo = new PDO($dsn, $user, $pass, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
    }

    /* ========================= CRUD ========================= */

    public function getAll(): array
    {
        $sql = "SELECT id, full_name, job_title, status, photo_path
                  FROM employees
                 ORDER BY full_name";
        return $this->pdo->query($sql)->fetchAll();
    }

    public function findById(int $id): ?array
    {
        $st = $this->pdo->prepare("SELECT * FROM employees WHERE id = ?");
        $st->execute([$id]);
        $row = $st->fetch();
        return $row ?: null;
    }

    public function create(array $data): int|false
    {
        $defaults = [
            'city' => null,
            'state' => null,
            'postal_code' => null,
            'cost_center' => null,
            'department' => null,
            'work_location' => null,
            'latitude' => null,
            'longitude' => null,
            'photo_path' => null,
        ];
        $p = array_merge($defaults, $data);
        $p['salary'] = isset($p['salary']) ? (float) $p['salary'] : 0.0;
        $p['latitude'] = isset($p['latitude']) ? $this->nf($p['latitude']) : null;
        $p['longitude'] = isset($p['longitude']) ? $this->nf($p['longitude']) : null;

        $sql = "INSERT INTO employees (
            full_name, cpf, rg, birth_date, address, city, state, postal_code, bairro, phone_number,
            job_title, salary, hire_date, contract_type, cost_center, department, work_location,
            latitude, longitude, bank_name, agency_number, account_number, status, photo_path, created_at
        ) VALUES (
            :full_name, :cpf, :rg, :birth_date, :address, :city, :state, :postal_code, :bairro, :phone_number,
            :job_title, :salary, :hire_date, :contract_type, :cost_center, :department, :work_location,
            :latitude, :longitude, :bank_name, :agency_number, :account_number, :status, :photo_path, NOW()
        )";
        $st = $this->pdo->prepare($sql);
        if (!$st->execute($p))
            return false;
        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        $defaults = [
            'city' => null,
            'state' => null,
            'postal_code' => null,
            'cost_center' => null,
            'department' => null,
            'work_location' => null,
            'latitude' => null,
            'longitude' => null,
            'photo_path' => null,
        ];
        $p = array_merge($defaults, $data);
        $p['id'] = $id;
        $p['salary'] = isset($p['salary']) ? (float) $p['salary'] : 0.0;
        $p['latitude'] = isset($p['latitude']) ? $this->nf($p['latitude']) : null;
        $p['longitude'] = isset($p['longitude']) ? $this->nf($p['longitude']) : null;

        $sql = "UPDATE employees SET
            full_name=:full_name, cpf=:cpf, rg=:rg, birth_date=:birth_date, address=:address,
            city=:city, state=:state, postal_code=:postal_code, bairro=:bairro, phone_number=:phone_number,
            job_title=:job_title, salary=:salary, hire_date=:hire_date, contract_type=:contract_type,
            cost_center=:cost_center, department=:department, work_location=:work_location,
            latitude=:latitude, longitude=:longitude, bank_name=:bank_name, agency_number=:agency_number,
            account_number=:account_number, status=:status, photo_path=:photo_path, updated_at=NOW()
        WHERE id=:id";
        $st = $this->pdo->prepare($sql);
        return $st->execute($p);
    }

    public function delete(int $id): bool
    {
        $st = $this->pdo->prepare("DELETE FROM employees WHERE id = ?");
        return $st->execute([$id]);
    }

    /* ========================= BUSCAS/UTILS ========================= */

    public function search(array $filters = []): array
    {
        $sql = "SELECT id, full_name, job_title, status, photo_path FROM employees WHERE 1=1";
        $p = [];
        if (!empty($filters['name'])) {
            $sql .= " AND full_name LIKE ?";
            $p[] = '%' . $filters['name'] . '%';
        }
        if (!empty($filters['cpf'])) {
            $sql .= " AND cpf LIKE ?";
            $p[] = '%' . $filters['cpf'] . '%';
        }
        if (!empty($filters['job_title'])) {
            $sql .= " AND job_title LIKE ?";
            $p[] = '%' . $filters['job_title'] . '%';
        }
        if (!empty($filters['status'])) {
            $sql .= " AND status = ?";
            $p[] = $filters['status'];
        }
        if (!empty($filters['city'])) {
            $sql .= " AND city LIKE ?";
            $p[] = '%' . $filters['city'] . '%';
        }
        if (!empty($filters['state'])) {
            $sql .= " AND state = ?";
            $p[] = $filters['state'];
        }
        if (!empty($filters['department'])) {
            $sql .= " AND department LIKE ?";
            $p[] = '%' . $filters['department'] . '%';
        }
        if (!empty($filters['work_location'])) {
            $sql .= " AND work_location LIKE ?";
            $p[] = '%' . $filters['work_location'] . '%';
        }
        $sql .= " ORDER BY full_name";
        $st = $this->pdo->prepare($sql);
        $st->execute($p);
        return $st->fetchAll();
    }

    public function countActive(): int
    {
        $st = $this->pdo->query("SELECT COUNT(*) FROM employees WHERE status = 'ativo'");
        return (int) $st->fetchColumn();
    }

    /* ========================= LOTE/RELATÓRIOS ========================= */

    public function deleteMultiple(array $ids): bool
    {
        if (empty($ids))
            return false;
        $in = implode(',', array_fill(0, count($ids), '?'));
        $st = $this->pdo->prepare("DELETE FROM employees WHERE id IN ($in)");
        return $st->execute(array_values($ids));
    }

    public function updateMultiple(array $ids, array $data): bool
    {
        if (empty($ids) || empty($data))
            return false;
        $set = [];
        $p = [];
        foreach ($data as $k => $v) {
            $set[] = "$k = ?";
            $p[] = $v;
        }
        $set[] = "updated_at = NOW()";
        $in = implode(',', array_fill(0, count($ids), '?'));
        $sql = "UPDATE employees SET " . implode(', ', $set) . " WHERE id IN ($in)";
        $p = array_merge($p, array_values($ids));
        $st = $this->pdo->prepare($sql);
        return $st->execute($p);
    }

    public function getReport(array $filters = []): array
    {
        $sql = "SELECT COUNT(*) total,
                       SUM(CASE WHEN status='ativo' THEN 1 ELSE 0 END) ativos,
                       SUM(CASE WHEN status='inativo' THEN 1 ELSE 0 END) inativos,
                       AVG(salary) salario_medio,
                       MIN(hire_date) primeira_contratacao,
                       MAX(hire_date) ultima_contratacao
                FROM employees WHERE 1=1";
        $p = [];
        if (!empty($filters['department'])) {
            $sql .= " AND department = ?";
            $p[] = $filters['department'];
        }
        if (!empty($filters['city'])) {
            $sql .= " AND city = ?";
            $p[] = $filters['city'];
        }
        $st = $this->pdo->prepare($sql);
        $st->execute($p);
        return $st->fetch() ?: [];
    }

    /* ========================= PRIVADOS ========================= */

    private function nf($v): ?float
    {
        if ($v === null || $v === '' || !is_numeric($v))
            return null;
        return (float) $v;
    }

    /** Compat com legado */
    public function listEmployees(): array
    {
        return $this->getAll();
    }
}
