<?php
// app/model/service/EmployeeService.php
require_once __DIR__ . '/../core/AbstractPdo.php';

class EmployeeService extends AbstractPdo {
    protected $table = 'employee';
    protected $primaryKey = 'id';

    public function __construct() {
        parent::__construct();
    }

    /**
     * Rechercher des employés avec critères
     */
    public function search($searchData, $page = 1, $perPage = 10) {
        $filters = [];

        $searchFields = ['nom', 'prenom', 'cin', 'email', 'login', 'specialite', 'status'];
        foreach ($searchFields as $field) {
            if (!empty($searchData[$field])) {
                $filters[$field] = [
                    'value' => $searchData[$field],
                    'like' => true
                ];
            }
        }

        if (!empty($searchData['grade_id'])) {
            $filters['grade_id'] = [
                'value' => $searchData['grade_id'],
                'like' => false
            ];
        }

        if (!empty($searchData['echellon_id'])) {
            $filters['echellon_id'] = [
                'value' => $searchData['echellon_id'],
                'like' => false
            ];
        }

        $offset = ($page - 1) * $perPage;
        $employees = $this->findWithDetails($filters, $perPage, $offset);
        $total = $this->countWithFilters($filters);

        return [
            'data' => $employees,
            'total' => $total,
            'page' => $page,
            'perPage' => $perPage,
            'totalPages' => ceil($total / $perPage)
        ];
    }

    /**
     * Authenticate admin user with encrypted password
     */
    public function login($email, $password)
    {
        // Check if admin exists with this email
        $sql = "SELECT * FROM {$this->table} WHERE email = :email";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['email' => $email]);
        $user = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$user) {
            return [
                'success' => false,
                'message' => 'Email ou mot de passe incorrect'
            ];
        }

        // Check if account is blocked
        if ($user['blocked'] == 1) {
            return [
                'success' => false,
                'message' => 'Votre compte est bloqué. Veuillez contacter l\'administrateur.'
            ];
        }

        // Verify password using password_verify()
        if (!password_verify($password, $user['password'])) {
            // Increment failed login attempts
            $this->incrementFailedAttempts($user['id']);

            // Check if should be blocked (3 failed attempts)
            $sql = "SELECT cnx_fail_number FROM {$this->table} WHERE id = :id";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['id' => $user['id']]);
            $failCount = $stmt->fetch(PDO::FETCH_ASSOC)['cnx_fail_number'];

            if ($failCount >= 3) {
                $this->blockStudent($user['id']);
                return [
                    'success' => false,
                    'message' => 'Trop de tentatives échouées. Votre compte a été bloqué.'
                ];
            }

            return [
                'success' => false,
                'message' => 'Email ou mot de passe incorrect. Tentative ' . $failCount . '/3'
            ];
        }

        // Login successful - reset failed attempts
        $this->resetFailedAttempts($user['id']);

        // Return admin data (without password)
        unset($user['password']);

        return [
            'success' => true,
            'data' => $user,
            'message' => 'Login successful'
        ];
    }

    /**
     * Find employees with grade and echellon information
     */
    public function findWithDetails($filters = [], $limit = null, $offset = null) {
        $sql = "SELECT e.*,
                       g.libelle as grade_libelle,
                       g.code as grade_code,
                       ec.libelle as echellon_libelle,
                       ec.code as echellon_code,
                       tg.libelle as type_grade_libelle
                FROM employee e
                LEFT JOIN grade g ON e.grade_id = g.id
                LEFT JOIN echellon ec ON e.echellon_id = ec.id
                LEFT JOIN type_grade tg ON e.type_grade_id = tg.id";

        $where = [];
        $params = [];

        foreach ($filters as $key => $filter) {
            $field = "e.{$key}";

            if (isset($filter['like']) && $filter['like'] && !empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} LIKE :{$paramKey}";
                $params[":{$paramKey}"] = '%' . $filter['value'] . '%';
            } elseif (!empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} = :{$paramKey}";
                $params[":{$paramKey}"] = $filter['value'];
            }
        }

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

        $sql .= " ORDER BY e.nom ASC, e.prenom ASC";

        if ($limit !== null) {
            $sql .= " LIMIT " . (int)$limit;
            if ($offset !== null) {
                $sql .= " OFFSET " . (int)$offset;
            }
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Count employees with filters
     */
    public function countWithFilters($filters = []) {
        $sql = "SELECT COUNT(DISTINCT e.id) as count FROM employee e";

        $where = [];
        $params = [];

        foreach ($filters as $key => $filter) {
            $field = "e.{$key}";

            if (isset($filter['like']) && $filter['like'] && !empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} LIKE :{$paramKey}";
                $params[":{$paramKey}"] = '%' . $filter['value'] . '%';
            } elseif (!empty($filter['value'])) {
                $paramKey = str_replace('.', '_', $key);
                $where[] = "{$field} = :{$paramKey}";
                $params[":{$paramKey}"] = $filter['value'];
            }
        }

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

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch();
        return (int)$result['count'];
    }

    /**
     * Get employee by ID with details
     */
    public function getEmployee($id) {
        $sql = "SELECT e.*,
                       g.libelle as grade_libelle,
                       g.code as grade_code,
                       ec.libelle as echellon_libelle,
                       ec.code as echellon_code,
                       tg.libelle as type_grade_libelle
                FROM employee e
                LEFT JOIN grade g ON e.grade_id = g.id
                LEFT JOIN echellon ec ON e.echellon_id = ec.id
                LEFT JOIN type_grade tg ON e.type_grade_id = tg.id
                WHERE e.id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }

    /**
     * Get employee by code
     */
    public function getEmployeeByCode($code) {
        return $this->findOneBy(['code' => $code]);
    }

    /**
     * Get employee by login
     */
    public function getEmployeeByLogin($login) {
        return $this->findOneBy(['login' => $login]);
    }

    /**
     * Create a new employee
     */
    public function createEmployee($data) {
        // Validation
        if (empty($data['nom'])) {
            throw new Exception("Le nom est obligatoire");
        }
        if (empty($data['prenom'])) {
            throw new Exception("Le prénom est obligatoire");
        }
        if (empty($data['cin'])) {
            throw new Exception("Le CIN est obligatoire");
        }
        if (empty($data['email'])) {
            throw new Exception("L'email est obligatoire");
        }
        if (empty($data['login'])) {
            throw new Exception("Le login est obligatoire");
        }
        if (empty($data['password'])) {
            throw new Exception("Le mot de passe est obligatoire");
        }

        // Check unique constraints
        $this->checkDuplicates($data);

        // Hash password
        $hashedPassword = password_hash($data['password'], PASSWORD_DEFAULT);

        // Prepare data
        $employeeData = [
            'code' => $data['code'] ?? $this->generateCode($data['nom'], $data['prenom']),
            'nom' => $data['nom'],
            'nom_arab' => $data['nom_arab'] ?? null,
            'prenom' => $data['prenom'],
            'prenom_arab' => $data['prenom_arab'] ?? null,
            'cin' => $data['cin'],
            'email' => $data['email'],
            'login' => $data['login'],
            'password' => $hashedPassword,
            'specialite' => $data['specialite'] ?? null,
            'diplome' => $data['diplome'] ?? null,
            'genre' => $data['genre'] ?? null,
            'etablissement_diplome' => $data['etablissement_diplome'] ?? null,
            'lieu_naissance' => $data['lieu_naissance'] ?? null,
            'date_naissance' => $data['date_naissance'] ?? null,
            'fonctionnaire' => isset($data['fonctionnaire']) ? (int)$data['fonctionnaire'] : 0,
            'nombre_annee_fonctionnaire' => $data['nombre_annee_fonctionnaire'] ?? 0,
            'date_recrutement' => $data['date_recrutement'] ?? null,
            'date_affectation_enseignement' => $data['date_affectation_enseignement'] ?? null,
            'status' => $data['status'] ?? 'active',
            'phone' => $data['phone'] ?? null,
            'grade_id' => !empty($data['grade_id']) ? $data['grade_id'] : null,
            'type_grade_id' => !empty($data['type_grade_id']) ? $data['type_grade_id'] : null,
            'echellon_id' => !empty($data['echellon_id']) ? $data['echellon_id'] : null,
            'blocked' => isset($data['blocked']) ? (int)$data['blocked'] : 0,
            'cnx_fail_number' => isset($data['cnx_fail_number']) ? (int)$data['cnx_fail_number'] : 0
        ];

        return $this->insert($employeeData);
    }

    /**
     * Update an employee
     */
    public function updateEmployee($id, $data) {
        $existing = $this->findOne($id);
        if (!$existing) {
            throw new Exception("Employé non trouvé");
        }

        $this->checkDuplicates($data, $id);

        $updateData = [];
        $fields = [
            'code', 'nom', 'nom_arab', 'prenom', 'prenom_arab', 'cin', 'email', 'login',
            'specialite', 'diplome', 'genre', 'etablissement_diplome', 'lieu_naissance',
            'date_naissance', 'fonctionnaire', 'nombre_annee_fonctionnaire',
            'date_recrutement', 'date_affectation_enseignement', 'status', 'phone',
            'grade_id', 'type_grade_id', 'echellon_id', 'blocked', 'cnx_fail_number'
        ];

        foreach ($fields as $field) {
            if (isset($data[$field])) {
                $updateData[$field] = $data[$field];
            }
        }

        // Handle password change separately
        if (!empty($data['password'])) {
            $updateData['password'] = password_hash($data['password'], PASSWORD_DEFAULT);
        }

        return $this->update($id, $updateData);
    }

    /**
     * Update password only
     * Cette méthode permet de changer uniquement le mot de passe d'un employé
     * sans modifier les autres informations
     */
    public function updatePassword($id, $newPassword) {
        $existing = $this->findOne($id);
        if (!$existing) {
            throw new Exception("Employé non trouvé");
        }

        if (strlen($newPassword) < 6) {
            throw new Exception("Le mot de passe doit contenir au moins 6 caractères");
        }

        $hashedPassword = password_hash($newPassword, PASSWORD_DEFAULT);
        return $this->update($id, ['password' => $hashedPassword]);
    }

    /**
     * Check duplicates
     */
    private function checkDuplicates($data, $excludeId = null) {
        $uniqueFields = ['cin', 'email', 'login'];

        foreach ($uniqueFields as $field) {
            if (isset($data[$field]) && !empty($data[$field])) {
                $criteria = [$field => $data[$field]];
                $existing = $this->findOneBy($criteria);

                if ($existing && ($excludeId === null || $existing['id'] != $excludeId)) {
                    throw new Exception("Le champ '$field' est déjà utilisé par un autre employé");
                }
            }
        }

        // Check code if provided
        if (isset($data['code']) && !empty($data['code'])) {
            $existing = $this->findOneBy(['code' => $data['code']]);
            if ($existing && ($excludeId === null || $existing['id'] != $excludeId)) {
                throw new Exception("Le code est déjà utilisé par un autre employé");
            }
        }
    }

    /**
     * Generate employee code
     */
    private function generateCode($nom, $prenom) {
        $base = strtoupper(substr($nom, 0, 3) . substr($prenom, 0, 3));
        $counter = 1;
        $code = $base . str_pad($counter, 3, '0', STR_PAD_LEFT);

        while ($this->findOneBy(['code' => $code])) {
            $counter++;
            $code = $base . str_pad($counter, 3, '0', STR_PAD_LEFT);
        }

        return $code;
    }

    /**
     * Delete an employee
     */
    public function deleteEmployee($id) {
        // Check if employee has related records
        $sql = "SELECT COUNT(*) as count FROM avancement WHERE employee_id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $result = $stmt->fetch();

        if ($result['count'] > 0) {
            throw new Exception("Cet employé est associé à des avancements et ne peut pas être supprimé");
        }

        return $this->delete($id);
    }

    /**
     * Get statistics
     */
    public function getStats() {
        $sql = "SELECT COUNT(*) as total FROM {$this->table}";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetch();

        $sql2 = "SELECT COUNT(*) as active FROM {$this->table} WHERE status = 'active'";
        $stmt2 = $this->pdo->prepare($sql2);
        $stmt2->execute();
        $result2 = $stmt2->fetch();

        $sql3 = "SELECT COUNT(*) as blocked FROM {$this->table} WHERE blocked = 1";
        $stmt3 = $this->pdo->prepare($sql3);
        $stmt3->execute();
        $result3 = $stmt3->fetch();

        return [
            'total' => (int)$result['total'],
            'active' => (int)$result2['active'],
            'blocked' => (int)$result3['blocked']
        ];
    }

    /**
     * Export employees to CSV
     */
    public function exportToCsv() {
        $employees = $this->findWithDetails([], null, null);

        if (empty($employees)) {
            return null;
        }

        $csv = "ID,Code,Nom,Prénom,CIN,Email,Login,Spécialité,Diplôme,Genre,Status,Téléphone,Grade,Échelon\n";
        foreach ($employees as $e) {
            $csv .= implode(',', [
                $e['id'],
                '"' . addslashes($e['code']) . '"',
                '"' . addslashes($e['nom']) . '"',
                '"' . addslashes($e['prenom']) . '"',
                '"' . addslashes($e['cin']) . '"',
                '"' . addslashes($e['email']) . '"',
                '"' . addslashes($e['login']) . '"',
                '"' . addslashes($e['specialite'] ?? '') . '"',
                '"' . addslashes($e['diplome'] ?? '') . '"',
                '"' . addslashes($e['genre'] ?? '') . '"',
                '"' . addslashes($e['status'] ?? '') . '"',
                '"' . addslashes($e['phone'] ?? '') . '"',
                '"' . addslashes($e['grade_libelle'] ?? '') . '"',
                '"' . addslashes($e['echellon_libelle'] ?? '') . '"'
            ]) . "\n";
        }

        return $csv;
    }

    /**
     * Get all employees
     */
    public function getAllEmployees() {
        return $this->findWithDetails([], null, null);
    }

    /**
     * Get employees by grade
     */
    public function getEmployeesByGrade($gradeId) {
        return $this->findAllBy(['grade_id' => $gradeId], 'nom ASC');
    }

    /**
     * Get employees by echellon
     */
    public function getEmployeesByEchellon($echellonId) {
        return $this->findAllBy(['echellon_id' => $echellonId], 'nom ASC');
    }

    /**
     * Verify password for employee
     */
    public function verifyPassword($id, $password) {
        $employee = $this->findOne($id);
        if (!$employee) {
            return false;
        }
        return password_verify($password, $employee['password']);
    }

    /**
     * Increment failed login attempts
     */
    public function incrementFailedAttempts($userId) {
        $sql = "UPDATE {$this->table} SET cnx_fail_number = cnx_fail_number + 1 WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $userId]);
    }

    /**
     * Reset failed login attempts
     */
    public function resetFailedAttempts($userId) {
        $sql = "UPDATE {$this->table} SET cnx_fail_number = 0 WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $userId]);
    }

    /**
     * Block a student account
     */
    public function blockStudent($userId) {
        $sql = "UPDATE {$this->table} SET blocked = 1 WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $userId]);
    }
}
?>