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

class Employees {
    private \PDO $pdo;
    
    public function __construct() {
        try {
            $host   = defined('DB_HOST')   ? DB_HOST   : '127.0.0.1';
            $db     = defined('DB_NAME')   ? DB_NAME   : '';
            $user   = defined('DB_USER')   ? DB_USER   : 'root';
            $pass   = defined('DB_PASS')   ? DB_PASS   : '';
            $port   = defined('DB_PORT')   ? (int)DB_PORT : 3306;
            $socket = defined('DB_SOCKET') ? DB_SOCKET : null;

            $options = [
                \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
                \PDO::ATTR_EMULATE_PREPARES   => false,
            ];

            if (!empty($socket)) {
                $dsn = "mysql:unix_socket={$socket};dbname={$db};charset=utf8mb4";
            } else {
                $dsn = "mysql:host={$host};port={$port};dbname={$db};charset=utf8mb4";
            }

            $this->pdo = new \PDO($dsn, $user, $pass, $options);
        } catch (\PDOException $e) {
            throw new \Exception('Erro na conexão com o banco de dados: ' . $e->getMessage());
        }
    }
    
    public function getAll() {
        try {
            $stmt = $this->pdo->prepare("SELECT * FROM employees WHERE status = 'ativo' ORDER BY full_name");
            $stmt->execute();
            return $stmt->fetchAll();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao buscar colaboradores: ' . $e->getMessage());
        }
    }
    
    public function findById($id) {
        try {
            $stmt = $this->pdo->prepare("SELECT * FROM employees WHERE id = ?");
            $stmt->execute([(int)$id]);
            return $stmt->fetch();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao buscar colaborador: ' . $e->getMessage());
        }
    }
    
    public function create(array $data) {
        try {
            $defaults = [
                'full_name'      => null,
                'cpf'            => null,
                'rg'             => null,
                'birth_date'     => null,
                'address'        => null,
                'city'           => null,
                'state'          => null,
                'postal_code'    => null,
                'phone_number'   => null,
                'job_title'      => null,
                'salary'         => 0,
                'hire_date'      => null,
                'contract_type'  => 'CLT',
                'cost_center'    => null,
                'department'     => null,
                'work_location'  => null,
                'latitude'       => null,
                'longitude'      => null,
                'bank_name'      => null,
                'agency_number'  => null,
                'account_number' => null,
                'status'         => 'ativo',
                'photo_path'     => null,
            ];
            $p = array_merge($defaults, $data);

            $sql = "INSERT INTO employees (
                        full_name, cpf, rg, birth_date, address, city, state, postal_code, 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, updated_at
                    ) VALUES (
                        :full_name, :cpf, :rg, :birth_date, :address, :city, :state, :postal_code, :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(), NOW()
                    )";
            
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                'full_name'      => $p['full_name'],
                'cpf'            => $p['cpf'],
                'rg'             => $p['rg'],
                'birth_date'     => $p['birth_date'],
                'address'        => $p['address'],
                'city'           => $p['city'],
                'state'          => $p['state'],
                'postal_code'    => $p['postal_code'],
                'phone_number'   => $p['phone_number'],
                'job_title'      => $p['job_title'],
                'salary'         => $p['salary'],
                'hire_date'      => $p['hire_date'],
                'contract_type'  => $p['contract_type'],
                'cost_center'    => $p['cost_center'],
                'department'     => $p['department'],
                'work_location'  => $p['work_location'],
                'latitude'       => $p['latitude'],
                'longitude'      => $p['longitude'],
                'bank_name'      => $p['bank_name'],
                'agency_number'  => $p['agency_number'],
                'account_number' => $p['account_number'],
                'status'         => $p['status'],
                'photo_path'     => $p['photo_path'],
            ]);
            
            return (int)$this->pdo->lastInsertId();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao criar colaborador: ' . $e->getMessage());
        }
    }
    
    public function update($id, array $data) {
        try {
            $defaults = [
                'full_name'      => null,
                'cpf'            => null,
                'rg'             => null,
                'birth_date'     => null,
                'address'        => null,
                'city'           => null,
                'state'          => null,
                'postal_code'    => null,
                'phone_number'   => null,
                'job_title'      => null,
                'salary'         => 0,
                'hire_date'      => null,
                'contract_type'  => 'CLT',
                'cost_center'    => null,
                'department'     => null,
                'work_location'  => null,
                'latitude'       => null,
                'longitude'      => null,
                'bank_name'      => null,
                'agency_number'  => null,
                'account_number' => null,
                'status'         => 'ativo',
                'photo_path'     => null,
            ];
            $p = array_merge($defaults, $data);

            $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,
                        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
                    LIMIT 1";
            
            $stmt = $this->pdo->prepare($sql);
            return $stmt->execute([
                'full_name'      => $p['full_name'],
                'cpf'            => $p['cpf'],
                'rg'             => $p['rg'],
                'birth_date'     => $p['birth_date'],
                'address'        => $p['address'],
                'city'           => $p['city'],
                'state'          => $p['state'],
                'postal_code'    => $p['postal_code'],
                'phone_number'   => $p['phone_number'],
                'job_title'      => $p['job_title'],
                'salary'         => $p['salary'],
                'hire_date'      => $p['hire_date'],
                'contract_type'  => $p['contract_type'],
                'cost_center'    => $p['cost_center'],
                'department'     => $p['department'],
                'work_location'  => $p['work_location'],
                'latitude'       => $p['latitude'],
                'longitude'      => $p['longitude'],
                'bank_name'      => $p['bank_name'],
                'agency_number'  => $p['agency_number'],
                'account_number' => $p['account_number'],
                'status'         => $p['status'],
                'photo_path'     => $p['photo_path'],
                'id'             => (int)$id,
            ]);
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao atualizar colaborador: ' . $e->getMessage());
        }
    }
    
    public function delete($id) {
        try {
            $stmt = $this->pdo->prepare("DELETE FROM employees WHERE id = ?");
            return $stmt->execute([(int)$id]);
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao deletar colaborador: ' . $e->getMessage());
        }
    }
    
    public function validateCpf($cpf) {
        $cpf = preg_replace('/[^0-9]/', '', (string)$cpf);
        if (strlen($cpf) != 11) return false;
        if (preg_match('/^(\d)\1{10}$/', $cpf)) return false;
        $sum=0; for ($i=0;$i<9;$i++) $sum += intval($cpf[$i])*(10-$i);
        $r=$sum%11; $d1 = ($r<2)?0:11-$r; if (intval($cpf[9])!=$d1) return false;
        $sum=0; for ($i=0;$i<10;$i++) $sum += intval($cpf[$i])*(11-$i);
        $r=$sum%11; $d2 = ($r<2)?0:11-$r; return intval($cpf[10])==$d2;
    }
    
    public function cpfExists($cpf, $excludeId = null) {
        try {
            $params = [$cpf];
            $sql = "SELECT COUNT(*) FROM employees WHERE cpf = ?";
            if ($excludeId) { $sql .= " AND id <> ?"; $params[] = (int)$excludeId; }
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return (int)$stmt->fetchColumn() > 0;
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao verificar CPF: ' . $e->getMessage());
        }
    }
    
    public function countActive() {
        try {
            $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM employees WHERE status = 'ativo'");
            $stmt->execute();
            return (int)$stmt->fetchColumn();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao contar colaboradores: ' . $e->getMessage());
        }
    }
    
    public function search($filters = []) {
        try {
            $sql = "SELECT * FROM employees WHERE 1=1";
            $params = [];
            if (!empty($filters['name']))       { $sql .= " AND full_name LIKE ?";   $params[] = '%'.$filters['name'].'%'; }
            if (!empty($filters['cpf']))        { $sql .= " AND cpf LIKE ?";         $params[] = '%'.$filters['cpf'].'%'; }
            if (!empty($filters['job_title']))  { $sql .= " AND job_title LIKE ?";   $params[] = '%'.$filters['job_title'].'%'; }
            if (!empty($filters['status']))     { $sql .= " AND status = ?";         $params[] = $filters['status']; }
            if (!empty($filters['city']))       { $sql .= " AND city LIKE ?";        $params[] = '%'.$filters['city'].'%'; }
            if (!empty($filters['state']))      { $sql .= " AND state = ?";          $params[] = $filters['state']; }
            if (!empty($filters['department'])) { $sql .= " AND department LIKE ?";  $params[] = '%'.$filters['department'].'%'; }
            if (!empty($filters['work_location'])) { $sql .= " AND work_location LIKE ?"; $params[] = '%'.$filters['work_location'].'%'; }
            $sql .= " ORDER BY full_name";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetchAll();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao buscar colaboradores: ' . $e->getMessage());
        }
    }
    
    public function findByLocation($latitude, $longitude, $radius = 10) {
        try {
            $sql = "SELECT *, 
                        (6371 * acos(
                            cos(radians(?)) * cos(radians(latitude)) *
                            cos(radians(longitude) - radians(?)) +
                            sin(radians(?)) * sin(radians(latitude))
                        )) AS distance 
                    FROM employees 
                    WHERE latitude IS NOT NULL AND longitude IS NOT NULL 
                    HAVING distance < ? 
                    ORDER BY distance";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([(float)$latitude, (float)$longitude, (float)$latitude, (float)$radius]);
            return $stmt->fetchAll();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao buscar por localização: ' . $e->getMessage());
        }
    }
    
    public function getLocationStats() {
        try {
            $sql = "SELECT 
                        city, 
                        state, 
                        department,
                        work_location,
                        COUNT(*) as total,
                        SUM(CASE WHEN status = 'ativo' THEN 1 ELSE 0 END) as ativos
                    FROM employees 
                    GROUP BY city, state, department, work_location
                    ORDER BY total DESC";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            return $stmt->fetchAll();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao obter estatísticas: ' . $e->getMessage());
        }
    }
    
    public function deleteMultiple($ids) {
        try {
            if (empty($ids)) return false;
            $ids = array_map('intval', $ids);
            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            $sql = "DELETE FROM employees WHERE id IN ($placeholders)";
            $stmt = $this->pdo->prepare($sql);
            return $stmt->execute($ids);
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao deletar colaboradores: ' . $e->getMessage());
        }
    }
    
    public function updateMultiple($ids, $data) {
        try {
            if (empty($ids) || empty($data)) return false;

            $setParts = []; $params = [];
            foreach ($data as $field => $value) {
                $setParts[] = "{$field} = ?";
                $params[]   = $value;
            }
            $setParts[] = "updated_at = NOW()";

            $ids = array_map('intval', $ids);
            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            $sql = "UPDATE employees SET " . implode(', ', $setParts) . " WHERE id IN ($placeholders)";

            $params = array_merge($params, $ids);
            $stmt = $this->pdo->prepare($sql);
            return $stmt->execute($params);
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao atualizar colaboradores: ' . $e->getMessage());
        }
    }
    
    public function getByDepartment($department) {
        try {
            $stmt = $this->pdo->prepare("SELECT * FROM employees WHERE department = ? AND status = 'ativo' ORDER BY full_name");
            $stmt->execute([$department]);
            return $stmt->fetchAll();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao buscar por departamento: ' . $e->getMessage());
        }
    }
    
    public function getByStatus($status) {
        try {
            $stmt = $this->pdo->prepare("SELECT * FROM employees WHERE status = ? ORDER BY full_name");
            $stmt->execute([$status]);
            return $stmt->fetchAll();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao buscar por status: ' . $e->getMessage());
        }
    }
    
    public function getReport($filters = []) {
        try {
            $sql = "SELECT 
                        COUNT(*) as total,
                        SUM(CASE WHEN status = 'ativo' THEN 1 ELSE 0 END) as ativos,
                        SUM(CASE WHEN status = 'inativo' THEN 1 ELSE 0 END) as inativos,
                        AVG(salary) as salario_medio,
                        MIN(hire_date) as primeira_contratacao,
                        MAX(hire_date) as ultima_contratacao
                    FROM employees WHERE 1=1";
            $params = [];
            if (!empty($filters['department'])) { $sql .= " AND department = ?"; $params[] = $filters['department']; }
            if (!empty($filters['city']))       { $sql .= " AND city = ?";       $params[] = $filters['city']; }
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt->fetch();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao gerar relatório: ' . $e->getMessage());
        }
    }
    
    public function listEmployees() {
        return $this->getAll();
    }
    
    public function getPaginated($page = 1, $limit = 10, $filters = []) {
        try {
            $offset = max(0, ((int)$page - 1) * (int)$limit);
            $sql = "SELECT * FROM employees WHERE 1=1";
            $params = [];
            if (!empty($filters['name']))       { $sql .= " AND full_name LIKE ?";   $params[] = '%'.$filters['name'].'%'; }
            if (!empty($filters['status']))     { $sql .= " AND status = ?";         $params[] = $filters['status']; }
            if (!empty($filters['department'])) { $sql .= " AND department LIKE ?";  $params[] = '%'.$filters['department'].'%'; }
            $sql .= " ORDER BY full_name LIMIT ? OFFSET ?";
            $params[] = (int)$limit;
            $params[] = (int)$offset;
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return [
                'data'  => $stmt->fetchAll(),
                'total' => $this->countFiltered($filters),
                'page'  => (int)$page,
                'limit' => (int)$limit
            ];
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao buscar colaboradores paginados: ' . $e->getMessage());
        }
    }
    
    private function countFiltered($filters = []) {
        try {
            $sql = "SELECT COUNT(*) FROM employees WHERE 1=1";
            $params = [];
            if (!empty($filters['name']))       { $sql .= " AND full_name LIKE ?";   $params[] = '%'.$filters['name'].'%'; }
            if (!empty($filters['status']))     { $sql .= " AND status = ?";         $params[] = $filters['status']; }
            if (!empty($filters['department'])) { $sql .= " AND department LIKE ?";  $params[] = '%'.$filters['department'].'%'; }
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return (int)$stmt->fetchColumn();
        } catch (\PDOException $e) {
            throw new \Exception('Erro ao contar colaboradores: ' . $e->getMessage());
        }
    }
}
