<?php
namespace App\Models;
use App\Core\Database; use PDO; use Throwable;
final class Person{
 public static function search(string $q=''):array{$p=[];$w='p.deleted_at IS NULL AND p.est_adherent=1';if($q!==''){$w.=' AND (p.nom LIKE :q OR p.prenom LIKE :q OR CONCAT(p.nom," ",p.prenom) LIKE :q OR CONCAT(p.prenom," ",p.nom) LIKE :q OR p.code_personne LIKE :q OR p.email LIKE :q OR p.telephone_principal LIKE :q)';$p['q']='%'.$q.'%';}$s=Database::connection()->prepare("SELECT p.*,TIMESTAMPDIFF(YEAR,p.date_naissance,CURDATE()) age,(SELECT COUNT(*) FROM liens_familiaux lf WHERE lf.enfant_id=p.id AND lf.deleted_at IS NULL AND lf.actif=1) nb_responsables FROM personnes p WHERE $w ORDER BY p.nom,p.prenom");$s->execute($p);return $s->fetchAll();}
 public static function find(int $id):?array{$s=Database::connection()->prepare('SELECT p.*,TIMESTAMPDIFF(YEAR,p.date_naissance,CURDATE()) age FROM personnes p WHERE p.id=:id AND p.deleted_at IS NULL LIMIT 1');$s->execute(['id'=>$id]);return $s->fetch()?:null;}
 public static function guardians(int $id):array{$s=Database::connection()->prepare('SELECT lf.*,p.nom,p.prenom,p.email,p.telephone_principal FROM liens_familiaux lf JOIN personnes p ON p.id=lf.responsable_id WHERE lf.enfant_id=:id AND lf.deleted_at IS NULL AND lf.actif=1 ORDER BY lf.priorite_contact,lf.id');$s->execute(['id'=>$id]);return $s->fetchAll();}
 public static function duplicateExists(string $n,string $p,string $d):bool{$s=Database::connection()->prepare('SELECT 1 FROM personnes WHERE deleted_at IS NULL AND nom=:n AND prenom=:p AND date_naissance=:d LIMIT 1');$s->execute(['n'=>$n,'p'=>$p,'d'=>$d]);return(bool)$s->fetchColumn();}
 public static function create(array $a,array $guardians,int $uid):int{$pdo=Database::connection();$pdo->beginTransaction();try{$code=self::code((int)$a['organisation_id'],$a['nom'],$a['prenom']);$s=$pdo->prepare('INSERT INTO personnes(organisation_id,code_personne,civilite,nom,prenom,date_naissance,genre,est_adherent,email,telephone_principal,telephone_secondaire,adresse_ligne1,adresse_ligne2,code_postal,ville,pays,droit_image,rgpd_accepte,rgpd_accepte_at,notes,created_by,updated_by) VALUES(:o,:c,:ci,:n,:p,:d,:g,1,:e,:t1,:t2,:a1,:a2,:cp,:v,:pa,:di,:r,:ra,:no,:u,:u)');$s->execute(['o'=>$a['organisation_id'],'c'=>$code,'ci'=>$a['civilite'],'n'=>$a['nom'],'p'=>$a['prenom'],'d'=>$a['date_naissance'],'g'=>$a['genre'],'e'=>$a['email']?:null,'t1'=>$a['telephone_principal']?:null,'t2'=>$a['telephone_secondaire']?:null,'a1'=>$a['adresse_ligne1']?:null,'a2'=>$a['adresse_ligne2']?:null,'cp'=>$a['code_postal']?:null,'v'=>$a['ville']?:null,'pa'=>$a['pays']?:'France','di'=>$a['droit_image'],'r'=>$a['rgpd_accepte'],'ra'=>$a['rgpd_accepte']?date('Y-m-d H:i:s'):null,'no'=>$a['notes']?:null,'u'=>$uid]);$child=(int)$pdo->lastInsertId();foreach($guardians as $i=>$g){if($g['nom']===''&&$g['prenom']==='')continue;$gid=self::guardian($pdo,(int)$a['organisation_id'],$g,$uid);$l=$pdo->prepare('INSERT INTO liens_familiaux(enfant_id,responsable_id,type_lien,autorite_parentale,responsable_legal,payeur_principal,autorise_recuperer,recoit_emails,recoit_sms,recoit_factures,recoit_documents,priorite_contact,actif) VALUES(:e,:r,:t,:ap,:rl,:pp,:ar,:em,:sm,:fa,:do,:pr,1)');$l->execute(['e'=>$child,'r'=>$gid,'t'=>$g['type_lien'],'ap'=>$g['autorite_parentale'],'rl'=>$g['responsable_legal'],'pp'=>$g['payeur_principal'],'ar'=>$g['autorise_recuperer'],'em'=>$g['recoit_emails'],'sm'=>$g['recoit_sms'],'fa'=>$g['recoit_factures'],'do'=>$g['recoit_documents'],'pr'=>$i+1]);}$pdo->commit();return$child;}catch(Throwable $e){$pdo->rollBack();throw$e;}}
 private static function guardian(PDO $pdo,int $o,array $g,int $u):int{$code=self::code($o,$g['nom'],$g['prenom']);$s=$pdo->prepare('INSERT INTO personnes(organisation_id,code_personne,civilite,nom,prenom,date_naissance,genre,est_adherent,est_responsable,est_payeur,email,telephone_principal,telephone_secondaire,created_by,updated_by) VALUES(:o,:c,:ci,:n,:p,:d,"non_renseigne",0,1,:pay,:e,:t1,:t2,:u,:u)');$s->execute(['o'=>$o,'c'=>$code,'ci'=>$g['civilite'],'n'=>$g['nom'],'p'=>$g['prenom'],'d'=>$g['date_naissance']?:'1900-01-01','pay'=>$g['payeur_principal'],'e'=>$g['email']?:null,'t1'=>$g['telephone_principal']?:null,'t2'=>$g['telephone_secondaire']?:null,'u'=>$u]);return(int)$pdo->lastInsertId();}
 private static function code(int $o,string $n,string $p):string{$pdo=Database::connection();$s=$pdo->prepare('CALL cda_generate_unique_code(:o,:n,:p,@c)');$s->execute(['o'=>$o,'n'=>$n,'p'=>$p]);$s->closeCursor();return(string)$pdo->query('SELECT @c')->fetchColumn();}
}
