<?php
// model/service/AvancementService.php

namespace model\service;

use AbstractPdo;

require_once __DIR__ . '/../core/AbstractPdo.php';

class AvancementService extends AbstractPdo
{
    protected $table = 'avancement';
    protected $primaryKey = 'id';

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

    // ====================== Business Methods ======================

    public function getStats()
    {
        $total    = (int)$this->pdo->query("SELECT COUNT(*) FROM avancement")->fetchColumn();
        $enCours  = (int)$this->pdo->query("SELECT COUNT(*) FROM avancement a JOIN etat_avancement e ON e.id = a.etat_avancement_id WHERE e.code = 'ATT'")->fetchColumn();
        $approuve = (int)$this->pdo->query("SELECT COUNT(*) FROM avancement a JOIN etat_avancement e ON e.id = a.etat_avancement_id WHERE e.code = 'APP'")->fetchColumn();
        $refuse   = (int)$this->pdo->query("SELECT COUNT(*) FROM avancement a JOIN etat_avancement e ON e.id = a.etat_avancement_id WHERE e.code = 'REJ'")->fetchColumn();
        return ['total' => $total, 'en_cours' => $enCours, 'approuve' => $approuve, 'refuse' => $refuse];
    }

    public function findAllAvancementService()
    {
        $sql = "SELECT a.*,
                CONCAT(e.prenom, ' ', e.nom) AS employee_nom,
                g.libelle AS grade_libelle,
                at2.libelle AS avancement_type_libelle,
                ea.libelle AS etat_avancement_libelle,
                es.libelle AS echellon_source_libelle,
                es.code AS echellon_source_code,
                ed.libelle AS echellon_destination_libelle,
                ed.code AS echellon_destination_code
                FROM avancement a
                LEFT JOIN employee e ON a.employee_id = e.id
                LEFT JOIN grade g ON e.grade_id = g.id
                LEFT JOIN avancement_type at2 ON a.avancement_type_id = at2.id
                LEFT JOIN etat_avancement ea ON a.etat_avancement_id = ea.id
                LEFT JOIN echellon es ON a.echellon_source_id = es.id
                LEFT JOIN echellon ed ON a.echellon_destination_id = ed.id
                ORDER BY a.id DESC";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function findByIdWithJoins($id)
    {
        $sql = "SELECT a.*,
                CONCAT(e.prenom, ' ', e.nom) AS employee_nom,
                g.libelle AS grade_libelle,
                g.code AS grade_code,
                at2.libelle AS avancement_type_libelle,
                ea.libelle AS etat_avancement_libelle, 
                ea.code AS etat_code,
                es.libelle AS echellon_source_libelle,
                es.code AS echellon_source_code,
                ed.libelle AS echellon_destination_libelle,
                ed.code AS echellon_destination_code
                FROM avancement a
                LEFT JOIN employee e ON a.employee_id = e.id
                LEFT JOIN grade g ON e.grade_id = g.id
                LEFT JOIN avancement_type at2 ON a.avancement_type_id = at2.id
                LEFT JOIN etat_avancement ea ON a.etat_avancement_id = ea.id
                LEFT JOIN echellon es ON a.echellon_source_id = es.id
                LEFT JOIN echellon ed ON a.echellon_destination_id = ed.id
                WHERE a.id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        return $stmt->fetch();
    }

    public function getLastAvancementPerEmployee()
    {
        $now = new \DateTime();
        $sql = "SELECT 
                    e.id as employee_id,
                    e.nom, e.prenom, e.specialite,
                    g.libelle as grade_libelle,
                    ec.code as echellon_code,
                    ec.echellon_suivant_id,
                    ec_next.code as echellon_suivant_code,
                    a.id as avancement_id,
                    a.libelle, a.date_avancement, a.date_demande,
                    at2.libelle as type_libelle,
                    ea.libelle as etat_libelle, ea.code as etat_code,
                    es.code as echelon_source_code,
                    ed.code as echelon_dest_code
                FROM employee e
                LEFT JOIN grade g ON g.id = e.grade_id
                LEFT JOIN echellon ec ON ec.id = e.echellon_id
                LEFT JOIN echellon ec_next ON ec_next.id = ec.echellon_suivant_id
                LEFT JOIN avancement a ON a.employee_id = e.id
                    AND a.date_avancement = (
                        SELECT MAX(a2.date_avancement) 
                        FROM avancement a2 
                        WHERE a2.employee_id = e.id
                    )
                LEFT JOIN etat_avancement ea ON ea.id = a.etat_avancement_id
                LEFT JOIN avancement_type at2 ON at2.id = a.avancement_type_id
                LEFT JOIN echellon es ON es.id = a.echellon_source_id
                LEFT JOIN echellon ed ON ed.id = a.echellon_destination_id
                ORDER BY e.nom ASC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        $employees = $stmt->fetchAll();

        foreach ($employees as &$emp) {
            $emp['statut']         = 'none';
            $emp['date_prochaine'] = null;
            $emp['date_limite']    = null;

            if ($emp['date_avancement']) {
                $lastDate  = new \DateTime($emp['date_avancement']);
                $nextDate  = (clone $lastDate)->modify('+1 year');
                $alertDate = (clone $nextDate)->modify('-2 months');
                $emp['date_prochaine'] = $nextDate->format('Y-m-d');
                $emp['date_limite']    = $alertDate->format('Y-m-d');
                $emp['statut']         = $now >= $alertDate ? 'alert' : 'ok';
            }
        }

        return $employees;
    }

    public function create($data)
    {
        $this->validate($data);

        $libelle = $this->generateCode(
            intval($data['employee_id']),
            intval($data['echellon_source_id']),
            intval($data['echellon_destination_id'])
        );

        $insert = [
            'libelle'                => $libelle,
            'description'            => isset($data['description'])    ? trim($data['description']) : null,
            'employee_id'            => intval($data['employee_id']),
            'etat_avancement_id'     => intval($data['etat_avancement_id']),
            'avancement_type_id'     => intval($data['avancement_type_id']),
            'echellon_source_id'     => intval($data['echellon_source_id']),
            'echellon_destination_id'=> intval($data['echellon_destination_id']),
            'date_demande'           => !empty($data['date_demande'])   ? $data['date_demande']   : null,
            'date_avancement'        => !empty($data['date_avancement'])
                ? $data['date_avancement']
                : (!empty($data['date_demande']) ? $data['date_demande'] : date('Y-m-d')),
            'date_decision'          => !empty($data['date_decision'])  ? $data['date_decision']  : null,
        ];

        $newId = $this->insert($insert);

        if ($this->getEtatCode($insert['etat_avancement_id']) === 'APP') {
            $this->applyToEmployee(
                $insert['employee_id'],
                $insert['echellon_destination_id'],
                $insert['date_avancement']
            );
        }

        return $newId;
    }

    public function update($id, $data)
    {
        $existing = $this->findOne($id);
        if (!$existing) throw new \Exception("Avancement non trouvé");

        $wasCurrent = ($this->getEtatCode($existing['etat_avancement_id']) === 'APP')
            && $this->isMostRecentApproved($id, $existing['employee_id']);

        $libelle = $this->generateCode(
            intval($data['employee_id']),
            intval($data['echellon_source_id']),
            intval($data['echellon_destination_id'])
        );

        $update = [
            'libelle'                => $libelle,
            'description'            => isset($data['description'])    ? trim($data['description']) : null,
            'employee_id'            => intval($data['employee_id']),
            'etat_avancement_id'     => intval($data['etat_avancement_id']),
            'avancement_type_id'     => intval($data['avancement_type_id']),
            'echellon_source_id'     => intval($data['echellon_source_id']),
            'echellon_destination_id'=> intval($data['echellon_destination_id']),
            'date_demande'           => !empty($data['date_demande'])   ? $data['date_demande']   : null,
            'date_avancement'        => !empty($data['date_avancement'])
                ? $data['date_avancement']
                : (!empty($data['date_demande']) ? $data['date_demande'] : date('Y-m-d')),
            'date_decision'          => !empty($data['date_decision'])  ? $data['date_decision']  : null,
            'date_modification'      => date('Y-m-d'),
        ];

        $result = parent::update($id, $update);

        $isNowApp = ($this->getEtatCode($update['etat_avancement_id']) === 'APP');

        if ($wasCurrent) {
            $this->revertEmployee($existing['employee_id'], $existing['echellon_source_id'], $id);
        }

        if ($isNowApp && $this->isMostRecentApproved($id, $update['employee_id'])) {
            $this->applyToEmployee($update['employee_id'], $update['echellon_destination_id'], $update['date_avancement']);
        }

        return $result;
    }

    public function deleteById($id)
    {
        $existing = $this->findOne($id);
        if (!$existing) throw new \Exception("Avancement non trouvé");

        $wasCurrent = ($this->getEtatCode($existing['etat_avancement_id']) === 'APP')
            && $this->isMostRecentApproved($id, $existing['employee_id']);

        if ($wasCurrent) {
            $this->revertEmployee($existing['employee_id'], $existing['echellon_source_id'], $id);
        }

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

    public function countAll()
    {
        return (int)$this->pdo->query("SELECT COUNT(*) FROM avancement")->fetchColumn();
    }

    // ====================== Search with Request Handling ======================

    /**
     * Handle search request with automatic filter extraction from $_GET
     */
    public function handleSearchRequest()
    {
        $filters = $this->extractSearchFilters();
        $page = $this->extractPage();
        $perPage = $this->extractPerPage();

        return $this->search($filters, $page, $perPage);
    }

    /**
     * Extract search filters from $_GET
     */
    private function extractSearchFilters()
    {
        return [
            'id'                 => $this->getParam('id', ''),
            'libelle'            => $this->getParam('libelle', ''),
            'employee_id'        => $this->getParam('employee_id', 0),
            'avancement_type_id' => $this->getParam('avancement_type_id', 0),
            'etat_avancement_id' => $this->getParam('etat_avancement_id', 0),
            'grade_id'           => $this->getParam('grade_id', 0),
            'type_grade_id'      => $this->getParam('type_grade_id', 0),
            'date_debut'         => $this->getParam('date_debut', ''),
            'date_fin'           => $this->getParam('date_fin', ''),
        ];
    }

    /**
     * Extract page number from $_GET
     */
    private function extractPage()
    {
        return max(1, intval($this->getParam('page', 1)));
    }

    /**
     * Extract items per page from $_GET
     */
    private function extractPerPage()
    {
        return max(1, intval($this->getParam('per_page', 10)));
    }

    /**
     * Safe parameter extraction
     */
    private function getParam($key, $default = null)
    {
        return isset($_GET[$key]) ? $_GET[$key] : $default;
    }

    /**
     * Core search method with filters
     */
    public function search($filters, $page = 1, $perPage = 10)
    {
        $where  = [];
        $params = [];

        if (!empty($filters['id']))                 { $where[] = 'a.id = :id';                              $params[':id']                 = intval($filters['id']); }
        if (!empty($filters['libelle']))            { $where[] = 'a.libelle LIKE :libelle';                 $params[':libelle']            = '%'.$filters['libelle'].'%'; }
        if (!empty($filters['employee_id']))        { $where[] = 'a.employee_id = :employee_id';            $params[':employee_id']        = intval($filters['employee_id']); }
        if (!empty($filters['avancement_type_id'])) { $where[] = 'a.avancement_type_id = :type_id';         $params[':type_id']            = intval($filters['avancement_type_id']); }
        if (!empty($filters['etat_avancement_id'])) { $where[] = 'a.etat_avancement_id = :etat_id';         $params[':etat_id']            = intval($filters['etat_avancement_id']); }
        if (!empty($filters['date_debut']))         { $where[] = 'a.date_demande >= :date_debut';           $params[':date_debut']         = $filters['date_debut']; }
        if (!empty($filters['date_fin']))           { $where[] = 'a.date_demande <= :date_fin';             $params[':date_fin']           = $filters['date_fin']; }
        if (!empty($filters['grade_id']))           { $where[] = 'g.id = :grade_id';                        $params[':grade_id']           = intval($filters['grade_id']); }
        if (!empty($filters['type_grade_id']))      { $where[] = 'tg.id = :type_grade_id';                  $params[':type_grade_id']      = intval($filters['type_grade_id']); }

        $whereClause = $where ? 'WHERE ' . implode(' AND ', $where) : '';
        $offset      = ($page - 1) * $perPage;

        $countSql  = "SELECT COUNT(*) as total FROM avancement a 
                      LEFT JOIN employee e ON a.employee_id = e.id 
                      LEFT JOIN grade g ON e.grade_id = g.id 
                      LEFT JOIN type_grade tg ON g.type_grade_id = tg.id 
                      $whereClause";
        $countStmt = $this->pdo->prepare($countSql);
        $countStmt->execute($params);
        $total      = (int)$countStmt->fetch()['total'];
        $totalPages = $total > 0 ? (int)ceil($total / $perPage) : 1;

        $sql = "SELECT a.*,
                CONCAT(e.prenom, ' ', e.nom) AS employee_nom,
                g.libelle AS grade_libelle,
                g.code AS grade_code,
                tg.libelle AS type_grade_libelle,
                tg.code AS type_grade_code,
                at2.libelle AS avancement_type_libelle,
                ea.libelle AS etat_avancement_libelle, 
                ea.code AS etat_code,
                es.libelle AS echellon_source_libelle,
                es.code AS echellon_source_code,
                ed.libelle AS echellon_destination_libelle,
                ed.code AS echellon_destination_code
                FROM avancement a
                LEFT JOIN employee e ON a.employee_id = e.id
                LEFT JOIN grade g ON e.grade_id = g.id
                LEFT JOIN type_grade tg ON g.type_grade_id = tg.id
                LEFT JOIN avancement_type at2 ON a.avancement_type_id = at2.id
                LEFT JOIN etat_avancement ea ON a.etat_avancement_id = ea.id
                LEFT JOIN echellon es ON a.echellon_source_id = es.id
                LEFT JOIN echellon ed ON a.echellon_destination_id = ed.id
                $whereClause
                ORDER BY a.id DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->pdo->prepare($sql);
        foreach ($params as $key => $val) { $stmt->bindValue($key, $val); }
        $stmt->bindValue(':limit',  $perPage, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset,  \PDO::PARAM_INT);
        $stmt->execute();
        $data = $stmt->fetchAll();

        return ['data' => $data, 'total' => $total, 'page' => $page, 'totalPages' => $totalPages, 'per_page' => $perPage];
    }

    public function exportCsv()
    {
        $data = $this->findAllAvancementService();
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="avancements_' . date('Ymd_His') . '.csv"');
        $output = fopen('php://output', 'w');
        fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));
        fputcsv($output, ['ID', 'Libellé', 'Employé', 'Grade', 'Type', 'État', 'Échelon Source', 'Échelon Destination', 'Date Avancement', 'Date Demande', 'Date Décision'], ';');
        foreach ($data as $row) {
            fputcsv($output, [
                $row['id'], $row['libelle'], $row['employee_nom'] ?? '',
                $row['grade_libelle'] ?? '', $row['avancement_type_libelle'] ?? '',
                $row['etat_avancement_libelle'] ?? '', $row['echellon_source_libelle'] ?? '',
                $row['echellon_destination_libelle'] ?? '', $row['date_avancement'] ?? '',
                $row['date_demande'] ?? '', $row['date_decision'] ?? ''
            ], ';');
        }
        fclose($output);
        exit;
    }

    // ====================== Reference Data Methods ======================

    public function getEmployees()
    {
        $sql = "SELECT e.id, e.nom, e.prenom, e.echellon_id, e.grade_id, e.type_grade_id, 
                g.libelle as grade_libelle, g.code as grade_code, 
                ec.libelle as echellon_libelle, ec.echellon_suivant_id, 
                ec_next.libelle as echellon_suivant_libelle 
                FROM employee e 
                LEFT JOIN grade g ON g.id = e.grade_id 
                LEFT JOIN echellon ec ON ec.id = e.echellon_id 
                LEFT JOIN echellon ec_next ON ec_next.id = ec.echellon_suivant_id 
                ORDER BY e.nom";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getEtats()
    {
        $sql = "SELECT * FROM etat_avancement ORDER BY id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getTypes()
    {
        $sql = "SELECT * FROM avancement_type ORDER BY id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function getEchelons()
    {
        $sql = "SELECT * FROM echellon ORDER BY id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    // ====================== Helper Methods ======================

    private function generateCode($employeeId, $echellonSourceId, $echellonDestinationId)
    {
        $stmt = $this->pdo->prepare(
            "SELECT g.code FROM employee e LEFT JOIN grade g ON g.id = e.grade_id WHERE e.id = :id"
        );
        $stmt->execute([':id' => $employeeId]);
        $gradeCode = $stmt->fetchColumn() ?: 'GRADE';

        $stmt = $this->pdo->prepare("SELECT code FROM echellon WHERE id = :id");
        $stmt->execute([':id' => $echellonSourceId]);
        $srcCode = $stmt->fetchColumn() ?: 'SRC';

        $stmt->execute([':id' => $echellonDestinationId]);
        $destCode = $stmt->fetchColumn() ?: 'DEST';

        return strtoupper("{$gradeCode}-{$srcCode}-{$destCode}");
    }

    private function getEtatCode($etatId)
    {
        $stmt = $this->pdo->prepare("SELECT code FROM etat_avancement WHERE id = :id");
        $stmt->execute([':id' => $etatId]);
        return $stmt->fetchColumn() ?: null;
    }

    private function isMostRecentApproved($avancementId, $employeeId)
    {
        $stmt = $this->pdo->prepare(
            "SELECT a.id FROM avancement a
             JOIN etat_avancement ea ON ea.id = a.etat_avancement_id
             WHERE a.employee_id = :employee_id AND ea.code = 'APP'
             ORDER BY a.date_avancement DESC, a.id DESC
             LIMIT 1"
        );
        $stmt->execute([':employee_id' => $employeeId]);
        $mostRecentId = $stmt->fetchColumn();
        return $mostRecentId && (int)$mostRecentId === (int)$avancementId;
    }

    private function getPreviousApprovedDate($employeeId, $excludeId)
    {
        $stmt = $this->pdo->prepare(
            "SELECT a.date_avancement FROM avancement a
             JOIN etat_avancement ea ON ea.id = a.etat_avancement_id
             WHERE a.employee_id = :employee_id AND ea.code = 'APP' AND a.id != :exclude_id
             ORDER BY a.date_avancement DESC, a.id DESC
             LIMIT 1"
        );
        $stmt->execute([':employee_id' => $employeeId, ':exclude_id' => $excludeId]);
        return $stmt->fetchColumn() ?: null;
    }

    private function applyToEmployee($employeeId, $echellonDestinationId, $dateAvancement)
    {
        $sql = "UPDATE employee SET echellon_id = :echellon_id  WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':echellon_id'  => $echellonDestinationId,
            ':id'           => $employeeId
        ]);
    }

    private function revertEmployee($employeeId, $echellonSourceId, $excludeAvancementId)
    {
        $previousDate = $this->getPreviousApprovedDate($employeeId, $excludeAvancementId);

        $sql = "UPDATE employee SET echellon_id = :echellon_id WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':echellon_id'  => $echellonSourceId,
            ':id'           => $employeeId
        ]);
    }

    private function validate($data)
    {
        if (empty($data['employee_id']))     throw new \Exception("L'employé est obligatoire");
        if (empty($data['etat_avancement_id'])) throw new \Exception("L'état d'avancement est obligatoire");
        if (empty($data['avancement_type_id'])) throw new \Exception("Le type d'avancement est obligatoire");
        if (empty($data['echellon_source_id'])) throw new \Exception("L'échelon source est obligatoire");
        if (empty($data['echellon_destination_id'])) throw new \Exception("L'échelon destination est obligatoire");
    }
}
?>