<?php

class EpargnerModel
{
    private $db;

    public function __construct()
    {
        // Connexion à la base de données via PDO
        $this->db = new PDO('mysql:host=localhost;dbname=dykqnvjf_gespes360;charset=utf8mb4', 'dykqnvjf_gespes360', 'dykqnvjf_gespes360');
        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    // 🔍 Récupérer tous les financements d’épargnes
    public function getAllEpargneFinancements($id_user)
    {
        $sql = "SELECT e.*, r.*, g.*, ec.*
                FROM epargner e
                LEFT JOIN revenus r ON e.id_revenu = r.id_revenu
                LEFT JOIN gains g ON e.id_gain = g.id_gain
                LEFT JOIN epargne_ct ec ON e.id_epargne_ct = ec.id_epargne_ct
                WHERE ec.id_utilisateur = :id_user
                ORDER BY e.date_epargne DESC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute(['id_user' => $id_user]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // 📄 Récupérer un financement par son ID
    public function getById($id)
    {
        $sql = "SELECT * FROM epargner WHERE id_epargner = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->execute(['id' => $id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    // 💾 Enregistrer un financement
    public function store($data)
    {
        $sql = "INSERT INTO epargner (
            id_epargne_ct, id_gain, id_revenu, libelle_epargner, montant,
            date_epargne, heure_epargne, origine_fonds, moyen_transfer,
            justificatif_depot, responsable_enregistrement, note_strategique
        ) VALUES (
            :id_epargne_ct, :id_gain, :id_revenu, :libelle_epargner, :montant,
            :date_epargne, :heure_epargne, :origine_fonds, :moyen_transfer,
            :justificatif_depot, :responsable_enregistrement, :note_strategique
        )";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($data);
    }

    // 🔄 Mettre à jour un financement
    public function update($id, $data)
    {
        $sql = "UPDATE epargner SET
                    id_epargne_ct = :id_epargne_ct,
                    id_gain = :id_gain,
                    id_revenu = :id_revenu,
                    libelle_epargner = :libelle_epargner,
                    montant = :montant,
                    date_epargne = :date_epargne,
                    heure_epargne = :heure_epargne,
                    origine_fonds = :origine_fonds,
                    moyen_transfer = :moyen_transfer,
                    justificatif_depot = :justificatif_depot,
                    responsable_enregistrement = :responsable_enregistrement,
                    note_strategique = :note_strategique
                WHERE id_epargner = :id";

        $stmt = $this->db->prepare($sql);
        $data['id'] = $id;
        $stmt->execute($data);
    }

    // 🗑️ Supprimer un financement
    public function delete($id)
    {
        $sql = "DELETE FROM epargner WHERE id_epargner = :id";
        $stmt = $this->db->prepare($sql);
        $stmt->execute(['id' => $id]);
    }

    // 📊 Récupérer les épargnes de l’utilisateur
    public function getEpargnesByUser($id_user)
    {
        $stmt = $this->db->prepare("SELECT * FROM epargne_ct WHERE id_utilisateur = ? ORDER BY date_enregistrement DESC");
        $stmt->execute([$id_user]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // 💰 Récupérer les revenus de l’utilisateur
    public function getRevenusByUser($id_user)
    {
        $stmt = $this->db->prepare("SELECT * FROM revenus WHERE id_receveur = ? ORDER BY date_enregistrement DESC");
        $stmt->execute([$id_user]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    // 💸 Récupérer les gains de l’utilisateur
    public function getGainsByUser($id_user)
    {
        $sql = "
            SELECT *
            FROM gains g
            INNER JOIN biens b ON g.id_bien = b.id_bien
            LEFT JOIN epargne_ct e ON b.id_epargne_ct = e.id_epargne_ct
            LEFT JOIN dettes d ON b.id_dette = d.id_dette
            WHERE e.id_utilisateur = :id_user OR d.id_contracteur = :id_user
        ";

        $stmt = $this->db->prepare($sql);
        $stmt->execute(['id_user' => $id_user]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
?>
