<?php

class FinancerModel
{
    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 liés à une dépense
    public function getAllDepenseFinancements()
    {
        $sql = "SELECT f.*, r.*, g.*, d.*
                FROM financer f
                LEFT JOIN revenus r ON f.id_revenu = r.id_revenu
                LEFT JOIN gains g ON f.id_gain = g.id_gain
                LEFT JOIN depenses d ON f.id_depense = d.id_depense
                WHERE f.id_depense IS NOT NULL
                ORDER BY f.date_financer DESC";

        $stmt = $this->db->query($sql);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

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


    public function store_financement($data) {
      $sql = "INSERT INTO financer (
        libelle_financement, montant_f, date_financer, heure_financer,
        type_financement, objectif_financement, duree_financement,
        impact_financier, methode_allocation, id_revenu, id_gain, id_depense
      ) VALUES (
        :libelle_financement, :montant_f, :date_financer, :heure_financer,
        :type_financement, :objectif_financement, :duree_financement,
        :impact_financier, :methode_allocation, :id_revenu, :id_gain, :id_depense
      )";

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



    // 🔄 Mettre à jour un financement
    public function update($id, $data)
    {
        $sql = "UPDATE financer SET
                    libelle_financement = :libelle_financement,
                    montant_f = :montant_f,
                    date_financer = :date_financer,
                    heure_financer = :heure_financer,
                    type_financement = :type_financement,
                    objectif_financement = :objectif_financement,
                    duree_financement = :duree_financement,
                    impact_financier = :impact_financier,
                    methode_allocation = :methode_allocation,
                    id_revenu = :id_revenu,
                    id_gain = :id_gain,
                    id_depense = :id_depense
                WHERE id_financer = :id";

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

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

    public function getRevenusByUser($id_user) {
      $stmt = $this->db->prepare("SELECT * FROM revenus WHERE id_receveur = ?");
      $stmt->execute([$id_user]);
      return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    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);
    }



    public function getDepensesByUser($id_user) {
      $stmt = $this->db->prepare("SELECT id_depense, libelles_depenses FROM depenses WHERE id_faiseur = ?");
      $stmt->execute([$id_user]);
      return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

}
?>