<?php
class StatistiquesModel {
    private $db;
    
    public function __construct() {
        $this->db = new PDO('mysql:host=localhost;dbname=dykqnvjf_gespes360', 'dykqnvjf_gespes360', 'dykqnvjf_gespes360');
        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    
    public function getSessionsStats($id_utilisateur) {
        $query = "SELECT 
                    COUNT(*) as total_sessions,
                    SUM(CASE WHEN date_deconnexion IS NOT NULL THEN 1 ELSE 0 END) as sessions_fermees,
                    SUM(CASE WHEN date_deconnexion IS NULL THEN 1 ELSE 0 END) as sessions_non_fermees,
                    SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, CONCAT(date_connexion, ' ', heure_connexion), 
                    COALESCE(CONCAT(date_deconnexion, ' ', heure_deconnexion), NOW())))) as duree_totale,
                    SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, CONCAT(date_connexion, ' ', heure_connexion), 
                    COALESCE(CONCAT(date_deconnexion, ' ', heure_deconnexion), NOW())))) as duree_moyenne
                  FROM sessions 
                  WHERE id_utilisateur = ?";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getRevenusDonnes($id_utilisateur) {
        $query = "SELECT r.*, u.nom, u.prenom 
                  FROM revenus r
                  JOIN utilisateurs u ON r.id_receveur = u.id_utilisateur
                  WHERE r.id_donneur = ? 
                  ORDER BY r.date_revenu DESC";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getTotauxRevenusDonnes($id_utilisateur) {
        $query = "SELECT 
                    SUM(montant) as total_general,
                    SUM(CASE WHEN YEAR(date_revenu) = YEAR(CURDATE()) THEN montant ELSE 0 END) as total_annee,
                    SUM(CASE WHEN YEAR(date_revenu) = YEAR(CURDATE()) AND MONTH(date_revenu) = MONTH(CURDATE()) THEN montant ELSE 0 END) as total_mois
                  FROM revenus 
                  WHERE id_donneur = ?";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getRevenusRecus($id_utilisateur) {
        $query = "SELECT r.*, u.nom, u.prenom 
                  FROM revenus r
                  JOIN utilisateurs u ON r.id_donneur = u.id_utilisateur
                  WHERE r.id_receveur = ? 
                  ORDER BY r.date_revenu DESC";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getTotauxRevenusRecus($id_utilisateur) {
        $query = "SELECT 
                    SUM(montant) as total_general,
                    SUM(CASE WHEN YEAR(date_revenu) = YEAR(CURDATE()) THEN montant ELSE 0 END) as total_annee,
                    SUM(CASE WHEN YEAR(date_revenu) = YEAR(CURDATE()) AND MONTH(date_revenu) = MONTH(CURDATE()) THEN montant ELSE 0 END) as total_mois
                  FROM revenus 
                  WHERE id_receveur = ?";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getDettesOctroyees($id_utilisateur) {
        $query = "SELECT d.*, u.nom, u.prenom 
                  FROM dettes d
                  JOIN utilisateurs u ON d.id_contracteur = u.id_utilisateur
                  WHERE d.id_octroyeur = ? 
                  ORDER BY d.date_emprunt DESC";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getTotauxDettesOctroyees($id_utilisateur) {
        $query = "SELECT 
                    SUM(montant_emprunt) as total_general,
                    SUM(CASE WHEN YEAR(date_emprunt) = YEAR(CURDATE()) THEN montant_emprunt ELSE 0 END) as total_annee,
                    SUM(CASE WHEN YEAR(date_emprunt) = YEAR(CURDATE()) AND MONTH(date_emprunt) = MONTH(CURDATE()) THEN montant_emprunt ELSE 0 END) as total_mois
                  FROM dettes 
                  WHERE id_octroyeur = ?";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getDettesRecues($id_utilisateur) {
        $query = "SELECT d.*, u.nom, u.prenom 
                  FROM dettes d
                  JOIN utilisateurs u ON d.id_octroyeur = u.id_utilisateur
                  WHERE d.id_contracteur = ? 
                  ORDER BY d.date_emprunt DESC";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function getTotauxDettesRecues($id_utilisateur) {
        $query = "SELECT 
                    SUM(montant_emprunt) as total_general,
                    SUM(CASE WHEN YEAR(date_emprunt) = YEAR(CURDATE()) THEN montant_emprunt ELSE 0 END) as total_annee,
                    SUM(CASE WHEN YEAR(date_emprunt) = YEAR(CURDATE()) AND MONTH(date_emprunt) = MONTH(CURDATE()) THEN montant_emprunt ELSE 0 END) as total_mois
                  FROM dettes 
                  WHERE id_contracteur = ?";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getTotauxGains($id_utilisateur) {
        $query = "SELECT 
                    SUM(g.montant_gain) as total_general,
                    SUM(CASE WHEN YEAR(g.date_gain) = YEAR(CURDATE()) THEN g.montant_gain ELSE 0 END) as total_annee,
                    SUM(CASE WHEN YEAR(g.date_gain) = YEAR(CURDATE()) AND MONTH(g.date_gain) = MONTH(CURDATE()) THEN g.montant_gain ELSE 0 END) as total_mois
                  FROM gains g
                  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 = ? OR d.id_contracteur = ?";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur, $id_utilisateur]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getFinancementsDepenses($id_utilisateur) {
        $query = "SELECT 
                    SUM(f.montant_f) as total_general,
                    SUM(CASE WHEN YEAR(f.date_financer) = YEAR(CURDATE()) THEN f.montant_f ELSE 0 END) as total_annee,
                    SUM(CASE WHEN YEAR(f.date_financer) = YEAR(CURDATE()) AND MONTH(f.date_financer) = MONTH(CURDATE()) THEN f.montant_f ELSE 0 END) as total_mois
                  FROM financer f
                  JOIN depenses d ON f.id_depense = d.id_depense
                  WHERE d.id_faiseur = ?";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    public function getFinancementsEpargnes($id_utilisateur) {
        $query = "SELECT 
                    SUM(e.montant) as total_general,
                    SUM(CASE WHEN YEAR(e.date_epargne) = YEAR(CURDATE()) THEN e.montant ELSE 0 END) as total_annee,
                    SUM(CASE WHEN YEAR(e.date_epargne) = YEAR(CURDATE()) AND MONTH(e.date_epargne) = MONTH(CURDATE()) THEN e.montant ELSE 0 END) as total_mois
                  FROM epargner e
                  JOIN epargne_ct ec ON e.id_epargne_ct = ec.id_epargne_ct
                  WHERE ec.id_utilisateur = ?";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$id_utilisateur]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}
?>