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

class EchelleService extends AbstractPdo {
    protected $table = 'echelle';
    protected $primaryKey = 'id';

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

    public function search($searchData, $page = 1, $perPage = 10) {
        $filters = [];

        $searchFields = ['id', 'libelle', 'code', 'description'];
        foreach ($searchFields as $field) {
            if (!empty($searchData[$field])) {
                $filters[$field] = ['value' => $searchData[$field], 'like' => true];
            }
        }

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

        $offset   = ($page - 1) * $perPage;
        $echelles = $this->findWithGradeAndEchellons($filters, $perPage, $offset);
        $total    = $this->countWithGrade($filters);

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

    public function findWithGradeAndEchellons($filters = [], $limit = null, $offset = null) {
        $sql = "SELECT e.*,
                       GROUP_CONCAT(DISTINCT g.libelle ORDER BY g.libelle SEPARATOR ', ') as grade_libelle,
                       GROUP_CONCAT(DISTINCT g.code ORDER BY g.code SEPARATOR ', ') as grade_code,
                       COUNT(DISTINCT ecl.id) as echellon_count
                FROM echelle e
                LEFT JOIN grade g ON g.id = e.grade_id
                LEFT JOIN echellon ecl ON ecl.echelle_id = e.id";

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

        foreach ($filters as $key => $filter) {
            if ($key === 'echellon_count') {
                $having[] = "COUNT(DISTINCT ecl.id) = :echellon_count";
                $params[':echellon_count'] = (int)$filter['value'];
                continue;
            }

            $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 .= " GROUP BY e.id";
        if (!empty($having)) $sql .= " HAVING " . implode(' AND ', $having);
        $sql .= " ORDER BY e.libelle 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();
    }

    public function countWithGrade($filters = []) {
        $sql = "SELECT COUNT(DISTINCT e.id) as count FROM echelle e LEFT JOIN grade g ON g.id = e.grade_id";

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

        foreach ($filters as $key => $filter) {
            if ($key === 'echellon_count') continue;
            $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);
        return (int)$stmt->fetch()['count'];
    }

    public function getEchelle($id) {
        $sql = "SELECT e.*,
                       GROUP_CONCAT(DISTINCT g.libelle ORDER BY g.libelle SEPARATOR ', ') as grade_libelle,
                       GROUP_CONCAT(DISTINCT g.code ORDER BY g.code SEPARATOR ', ') as grade_code
                FROM echelle e
                LEFT JOIN grade g ON g.id = e.grade_id
                WHERE e.id = :id
                GROUP BY e.id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $echelle = $stmt->fetch();

        if ($echelle) {
            $echelle['echellons']      = $this->getEchellonsByEchelleId($id);
            $echelle['echellon_count'] = count($echelle['echellons']);
        }

        return $echelle;
    }

    public function getEchellonsByEchelleId($echelleId) {
        $sql  = "SELECT * FROM echellon WHERE echelle_id = :echelle_id ORDER BY id ASC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':echelle_id' => $echelleId]);
        return $stmt->fetchAll();
    }

    public function getEchelleByCode($code) {
        return $this->findOneBy(['code' => $code]);
    }

    public function createEchelle($data) {
        if (empty($data['libelle']))       throw new Exception("Le libellé est obligatoire");
        if (empty($data['code']))          throw new Exception("Le code est obligatoire");
        if (empty($data['grade_id']))      throw new Exception("Le grade est obligatoire");
        if (!isset($data['echellon_depart']) || $data['echellon_depart'] === '') throw new Exception("L'échelon de départ est obligatoire");
        if (!isset($data['echellon_fin'])  || $data['echellon_fin']   === '') throw new Exception("L'échelon de fin est obligatoire");
        if ((int)$data['echellon_depart'] >= (int)$data['echellon_fin']) throw new Exception("L'échelon de départ doit être inférieur à l'échelon de fin");

        $echelleData = [
            'libelle'         => $data['libelle'],
            'code'            => $data['code'],
            'grade_id'        => intval($data['grade_id']),
            'description'     => $data['description'] ?? null,
            'echellon_depart' => $data['echellon_depart'],
            'echellon_fin'    => $data['echellon_fin']
        ];

        $echelleId = $this->insert($echelleData);
        $this->generateEchellons($echelleId, (int)$data['echellon_depart'], (int)$data['echellon_fin']);

        return $echelleId;
    }

    private function generateEchellons($echelleId, $depart, $fin) {
        $echellonService = new EchellonService();

        // Récupérer le code de l'échelle
        $echelle     = $this->findOne($echelleId);
        $echelleCode = $echelle['code'] ?? 'E' . $echelleId;

        $this->deleteEchellonsByEchelleId($echelleId);

        $echellonIds = [];
        for ($i = $depart; $i <= $fin; $i++) {
            $id = $echellonService->createEchellon([
                'libelle'     => $echelleCode . '.' . $i,
                'code'        => $echelleCode . '-' . $i,
                'description' => 'Échelon ' . $i,
                'echelle_id'  => $echelleId
            ]);
            $echellonIds[] = $id;
        }

        for ($i = 0; $i < count($echellonIds) - 1; $i++) {
            $echellonService->updateEchellonSuivant($echellonIds[$i], $echellonIds[$i + 1]);
        }

        return $echellonIds;
    }

    private function deleteEchellonsByEchelleId($echelleId) {
        $sql  = "DELETE FROM echellon WHERE echelle_id = :echelle_id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':echelle_id' => $echelleId]);
    }

    public function updateEchelle($id, $data) {
        $existing = $this->findOne($id);
        if (!$existing) throw new Exception("Échelle non trouvée");

        if (isset($data['echellon_depart']) && isset($data['echellon_fin'])) {
            if ((int)$data['echellon_depart'] >= (int)$data['echellon_fin']) {
                throw new Exception("L'échelon de départ doit être inférieur à l'échelon de fin");
            }
        }

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

        $updateData = [];
        foreach (['libelle', 'code', 'grade_id', 'description', 'echellon_depart', 'echellon_fin'] as $field) {
            if (isset($data[$field])) $updateData[$field] = $data[$field];
        }

        $result = $this->update($id, $updateData);

        if (isset($data['echellon_fin']) && (int)$data['echellon_fin'] > (int)$existing['echellon_fin']) {
            $echelle      = $this->findOne($id);
            $echelleCode  = $echelle['code'];
            $echellonService = new EchellonService();

            $lastSql  = "SELECT * FROM echellon WHERE echelle_id = :id ORDER BY id DESC LIMIT 1";
            $lastStmt = $this->pdo->prepare($lastSql);
            $lastStmt->execute([':id' => $id]);
            $lastEchelon = $lastStmt->fetch();

            $newIds = [];
            for ($i = (int)$existing['echellon_fin'] + 1; $i <= (int)$data['echellon_fin']; $i++) {
                $newId = $echellonService->createEchellon([
                    'libelle'    => $echelleCode . '.' . $i,
                    'code'       => $echelleCode . '-' . $i,
                    'description'=> 'Échelon ' . $i,
                    'echelle_id' => $id
                ]);
                $newIds[] = $newId;
            }

            if ($lastEchelon && !empty($newIds)) $echellonService->updateEchellonSuivant($lastEchelon['id'], $newIds[0]);
            for ($i = 0; $i < count($newIds) - 1; $i++) $echellonService->updateEchellonSuivant($newIds[$i], $newIds[$i + 1]);

        } elseif (isset($data['echellon_fin']) && (int)$data['echellon_fin'] < (int)$existing['echellon_fin']) {
            $sql  = "SELECT * FROM echellon WHERE echelle_id = :id ORDER BY id DESC";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([':id' => $id]);
            $allEchellons = $stmt->fetchAll();
            $nbToDelete = (int)$existing['echellon_fin'] - (int)$data['echellon_fin'];
            for ($i = 0; $i < $nbToDelete && isset($allEchellons[$i]); $i++) {
                $delStmt = $this->pdo->prepare("DELETE FROM echellon WHERE id = :id");
                $delStmt->execute([':id' => $allEchellons[$i]['id']]);
            }
        }

        return $result;
    }

    private function checkDuplicates($data, $excludeId = null) {
        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 une autre échelle");
            }
        }
    }

    public function deleteEchelle($id) {
        $sql  = "SELECT COUNT(*) as count FROM employee WHERE echellon_id IN (SELECT id FROM echellon WHERE echelle_id = :id)";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        if ((int)$stmt->fetch()['count'] > 0) throw new Exception("Cette échelle est utilisée par des employés et ne peut pas être supprimée");

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

    public function getStats() {
        $total           = (int)$this->pdo->query("SELECT COUNT(*) FROM echelle")->fetchColumn();
        $total_echellons = (int)$this->pdo->query("SELECT COUNT(*) FROM echellon")->fetchColumn();
        return ['total' => $total, 'total_echellons' => $total_echellons];
    }

    public function exportToCsv() {
        $echelles = $this->findWithGradeAndEchellons([], null, null);
        if (empty($echelles)) return null;
        $csv = "ID,Libellé,Code,Grades,Description,Échelon Départ,Échelon Fin,Nb Échelons\n";
        foreach ($echelles as $e) {
            $csv .= implode(',', [
                $e['id'],
                '"' . addslashes($e['libelle']) . '"',
                '"' . addslashes($e['code']) . '"',
                '"' . addslashes($e['grade_libelle'] ?? '') . '"',
                '"' . addslashes($e['description'] ?? '') . '"',
                $e['echellon_depart'],
                $e['echellon_fin'],
                $e['echellon_count'] ?? 0,
            ]) . "\n";
        }
        return $csv;
    }

    public function getAllEchelles() {
        $sql  = "SELECT e.*, GROUP_CONCAT(DISTINCT g.libelle SEPARATOR ', ') as grade_libelle
                 FROM echelle e LEFT JOIN grade g ON g.id = e.grade_id
                 GROUP BY e.id ORDER BY e.libelle ASC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getEchellesByGrade($gradeId) {
        $sql  = "SELECT e.* FROM echelle e WHERE e.grade_id = :grade_id ORDER BY e.libelle ASC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':grade_id' => $gradeId]);
        return $stmt->fetchAll();
    }
}
?>
