<?php


class ConseilsModel
{
    protected $db;


    public function __construct() {
        require_once '/home/dykqnvjf/public_html/bewise/app/kernel/Database_connexion.php';
        $this->db = (new Database())->connect();
    }


    public function getAll()
    {
        $stmt = $this->db->query("SELECT * FROM conseils ORDER BY id_conseil DESC");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function find($id)
    {
        $stmt = $this->db->prepare("SELECT * FROM conseils WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function insert($data)
    {
        $stmt = $this->db->prepare("INSERT INTO conseils (message_conseil, type_conseil) VALUES (?, ?)");
        return $stmt->execute([$data['message_conseil'], $data['type_conseil']]);
    }

    public function update($id, $data)
    {
        $stmt = $this->db->prepare("UPDATE conseils SET message_conseil = ?, type_conseil = ? WHERE id = ?");
        return $stmt->execute([$data['message_conseil'], $data['type_conseil'], $id]);
    }

    public function delete($id)
    {
        $stmt = $this->db->prepare("DELETE FROM conseils WHERE id = ?");
        return $stmt->execute([$id]);
    }

    public function getRandom()
    {
        $stmt = $this->db->query("SELECT * FROM conseils ORDER BY RAND() LIMIT 1");
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function getConseilsByUtilisateur($id_utilisateur) {
        $sql = "SELECT * FROM conseils WHERE id_utilisateur = :id_utilisateur OR id_utilisateur IS NULL";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':id_utilisateur', $id_utilisateur, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }


    public function getConseilsUtilisateur($userId, $filters = []) {
        $query = "SELECT * FROM conseils WHERE id_utilisateur = :userId";
        $params = [':userId' => $userId];

        // Construction dynamique de la requête
        if (!empty($filters['type'])) {
            $query .= " AND type_conseil = :type";
            $params[':type'] = $filters['type'];
        }

        if (!empty($filters['recherche'])) {
            $query .= " AND (message_conseil LIKE :recherche OR type_conseil LIKE :recherche)";
            $params[':recherche'] = '%'.$filters['recherche'].'%';
        }

        if (!empty($filters['periode'])) {
            $query .= $this->getPeriodeCondition($filters['periode']);
        }

        $query .= " ORDER BY date_generation DESC";

        $stmt = $this->db->prepare($query);
        $stmt->execute($params);

        return $stmt->fetchAll();
    }

    public function getTypesConseils() {
        $query = "SELECT DISTINCT type_conseil FROM conseils ORDER BY type_conseil";
        $stmt = $this->db->query($query);
        return $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
    }

    private function getPeriodeCondition($periode) {
        $conditions = [
            'today' => "DATE(date_generation) = CURDATE()",
            'week' => "YEARWEEK(date_generation, 1) = YEARWEEK(CURDATE(), 1)",
            'month' => "MONTH(date_generation) = MONTH(CURDATE()) AND YEAR(date_generation) = YEAR(CURDATE())",
            'year' => "YEAR(date_generation) = YEAR(CURDATE())"
        ];

        return isset($conditions[$periode]) ? " AND ".$conditions[$periode] : "";
    }

}
?>