<?php

namespace App\Models;

use CodeIgniter\Model;

class EnfantModel extends Model
 {
    protected $table            = 'enfant';
    protected $primaryKey       = 'id';
    protected $allowedFields    = ['nom', 'prenom', 'age','sexe','type_parent','id_personnel'];
    protected $useTimestamps    = true; // Active les champs created_at et updated_at

    public function getAgentsAvecEnfantEtStructure()
    {       
    return $this->db->table('enfant')
        ->select('enfant.*, structure.NOM_STRUCTURE,personnel.nom, personnel.prenom, personnel.telephone,enfant.created_at')
        ->join('personnel', 'personnel.id = enfant.id_personnel')
        ->join('structure', 'structure.ID_STRUCTURE = personnel.ID_STRUCTURE')
        ->get()
        ->getResultArray();
    }
    
    public function countEnfants()
    {
    return $this->countAllResults();
    }

    public function countByGender()
    {
    return $this->db->query("
        SELECT 
            SUM(sexe = 'f') AS filles,
            SUM(sexe = 'h') AS garcons
        FROM enfant
    ")->getRowArray();
    }
    public function getStatsByAgeAndSexe()
    {
    return $this->select('age, sexe, COUNT(*) as total')
                ->where('age >=', 1)
                ->where('age <=', 18)
                ->groupBy(['age', 'sexe'])
                ->orderBy('age', 'ASC')
                ->findAll();
    }


    public function countTranchesAge()
    {
    return $this->db->query("
        SELECT 
            SUM(age BETWEEN 0 AND 4) AS age_0_4,
            SUM(age BETWEEN 5 AND 8) AS age_5_8,
            SUM(age BETWEEN 9 AND 12) AS age_9_12,
            SUM(age BETWEEN 13 AND 16) AS age_13_16
        FROM enfant
        ")->getRowArray();
    }
    public function getUserEnfantData($id)
   {
    return $this->select('enfant.*, 
                          personnel.nom, 
                          personnel.prenom, 
                          personnel.telephone, 
                          personnel.matricule, 
                          users.email, 
                          structure.NOM_STRUCTURE,
                          enfant.id AS enfant_id,
                          enfant.nom AS enf_nom, 
                          enfant.prenom AS enf_prenom, 
                          enfant.sexe, 
                          enfant.age, 
                          enfant.type_parent')
                ->join('personnel', 'personnel.id = enfant.id_personnel')
                ->join('structure', 'structure.ID_STRUCTURE = personnel.ID_STRUCTURE')
                ->join('users', 'users.id_personnel = personnel.id')
                ->where('enfant.id', $id)
                ->first();

   }
    
 }
 