<?php
declare(strict_types=1);

namespace App\Services;

use App\Core\Database;
use PDO;
use Throwable;

final class AlertTaskService
{
    private PDO $pdo;
    public function __construct(){ $this->pdo=Database::connection(); }

    public function synchronize(int $organisationId): array
    {
        $billing=$this->syncBilling($organisationId);
        $professors=$this->syncProfessorDays($organisationId);
        return ['billing'=>$billing,'professors'=>$professors];
    }

    private function syncBilling(int $org): int
    {
        $users=$this->usersWithPermission($org,'treasury.treasurer');
        if(!$users)$users=$this->usersWithPermission($org,'facturation.create');
        $q=$this->pdo->prepare("SELECT id,libelle FROM cda_facturation_a_preparer WHERE organisation_id=? AND statut='a_facturer' AND invoice_id IS NULL AND deleted_at IS NULL");
        $q->execute([$org]);$items=$q->fetchAll()?:[];$count=0;
        $insert=$this->pdo->prepare("INSERT INTO cda_alert_tasks(organisation_id,alert_type,entity_type,entity_id,assigned_user_id,title,message,action_url,priority,due_at) VALUES(?,'invoice_to_prepare','billing_item',?,?,?,'Un élément validé attend la création de sa facture.','/public/facturation/a-preparer','high',NOW()) ON DUPLICATE KEY UPDATE title=VALUES(title),message=VALUES(message),action_url=VALUES(action_url),updated_at=NOW()");
        foreach($items as $item)foreach($users as $user){$insert->execute([$org,(string)$item['id'],$user,'Facture à établir — '.(string)$item['libelle']]);$count++;}
        $close=$this->pdo->prepare("UPDATE cda_alert_tasks a SET a.status='completed',a.completed_at=COALESCE(a.completed_at,NOW()) WHERE a.organisation_id=? AND a.alert_type='invoice_to_prepare' AND a.status='open' AND NOT EXISTS(SELECT 1 FROM cda_facturation_a_preparer f WHERE f.id=CAST(a.entity_id AS UNSIGNED) AND f.organisation_id=a.organisation_id AND f.statut='a_facturer' AND f.invoice_id IS NULL AND f.deleted_at IS NULL)");
        $close->execute([$org]);return $count;
    }

    private function syncProfessorDays(int $org): int
    {
        $q=$this->pdo->prepare("SELECT p.id professeur_id,COALESCE(p.utilisateur_id,u.id) utilisateur_id,p.nom,p.prenom,s.date_seance,COUNT(*) seances FROM seances s JOIN professeurs p ON p.id=s.professeur_id AND p.organisation_id=? AND p.deleted_at IS NULL LEFT JOIN utilisateurs u ON u.organisation_id=p.organisation_id AND u.email COLLATE utf8mb4_unicode_ci=p.email COLLATE utf8mb4_unicode_ci AND u.actif=1 AND u.deleted_at IS NULL WHERE s.date_seance<CURDATE() AND s.deleted_at IS NULL AND s.statut NOT IN('annulee','deplacee') AND s.date_validation IS NULL AND (p.utilisateur_id IS NOT NULL OR u.id IS NOT NULL) GROUP BY p.id,COALESCE(p.utilisateur_id,u.id),p.nom,p.prenom,s.date_seance");
        $q->execute([$org]);$days=$q->fetchAll()?:[];$count=0;
        $insert=$this->pdo->prepare("INSERT INTO cda_alert_tasks(organisation_id,alert_type,entity_type,entity_id,assigned_user_id,title,message,action_url,priority,due_at) VALUES(?,'professor_day_missing','professor_day',?,?,?,? ,?,'high',?) ON DUPLICATE KEY UPDATE title=VALUES(title),message=VALUES(message),action_url=VALUES(action_url),due_at=VALUES(due_at),updated_at=NOW()");
        foreach($days as $d){$entity=$d['professeur_id'].':'.$d['date_seance'];$title='Journée du '.$this->dateFr((string)$d['date_seance']).' à mettre à jour';$message=(int)$d['seances'].' séance(s) restent à renseigner ou valider.';$url='/public/presences?date='.$d['date_seance'];$due=$d['date_seance'].' 23:59:59';$insert->execute([$org,$entity,(int)$d['utilisateur_id'],$title,$message,$url,$due]);$count++;}
        $close=$this->pdo->prepare("UPDATE cda_alert_tasks a SET a.status='completed',a.completed_at=COALESCE(a.completed_at,NOW()) WHERE a.organisation_id=? AND a.alert_type='professor_day_missing' AND a.status='open' AND NOT EXISTS(SELECT 1 FROM seances s JOIN professeurs p ON p.id=s.professeur_id WHERE p.id=CAST(SUBSTRING_INDEX(a.entity_id,':',1) AS UNSIGNED) AND s.date_seance=STR_TO_DATE(SUBSTRING_INDEX(a.entity_id,':',-1),'%Y-%m-%d') AND p.organisation_id=a.organisation_id AND s.deleted_at IS NULL AND s.statut NOT IN('annulee','deplacee') AND s.date_validation IS NULL)");
        $close->execute([$org]);return $count;
    }

    public function sendDailyDigests(): array
    {
        $result=['sent'=>0,'failed'=>0,'skipped'=>0];
        $users=$this->pdo->query("SELECT u.id,u.nom,u.prenom,u.email,u.organisation_id,COALESCE(p.daily_email_enabled,1) enabled,COALESCE(p.digest_hour,8) digest_hour FROM utilisateurs u LEFT JOIN cda_alert_preferences p ON p.user_id=u.id WHERE u.actif=1 AND u.statut='actif' AND u.deleted_at IS NULL")->fetchAll()?:[];
        foreach($users as $u){
            if((int)$u['enabled']!==1||date('G')<(int)$u['digest_hour']){$result['skipped']++;continue;}
            $exists=$this->pdo->prepare('SELECT 1 FROM cda_alert_mail_log WHERE user_id=? AND digest_date=CURDATE()');$exists->execute([$u['id']]);if($exists->fetchColumn()){$result['skipped']++;continue;}
            $q=$this->pdo->prepare("SELECT title,message,action_url,due_at FROM cda_alert_tasks WHERE assigned_user_id=? AND status='open' AND (due_at IS NULL OR due_at<=NOW()) ORDER BY FIELD(priority,'urgent','high','normal','low'),due_at");$q->execute([$u['id']]);$tasks=$q->fetchAll()?:[];if(!$tasks){$result['skipped']++;continue;}
            $email=filter_var($u['email'],FILTER_VALIDATE_EMAIL);$status='failed';$error=null;
            if($email){$body='<h2>Vos alertes AXIOM</h2><p>Bonjour '.htmlspecialchars($u['prenom']).',</p><p>'.count($tasks).' tâche(s) nécessitent votre attention :</p><ul>';foreach($tasks as $t)$body.='<li><strong>'.htmlspecialchars($t['title']).'</strong>'.($t['message']?'<br>'.htmlspecialchars($t['message']):'').'</li>';$body.='</ul><p>Connectez-vous à AXIOM pour les traiter.</p><p>Vous pouvez désactiver ce récapitulatif dans votre écran Alertes.</p>';$headers="MIME-Version: 1.0\r\nContent-Type: text/html; charset=UTF-8\r\n";if(@mail($email,'Vos tâches AXIOM du '.date('d/m/Y'),$body,$headers))$status='sent';else$error='Le serveur de messagerie a refusé l’envoi.';}else$error='Adresse e-mail invalide.';
            try{$this->pdo->prepare('INSERT INTO cda_alert_mail_log(user_id,digest_date,task_count,status,error_message) VALUES(?,CURDATE(),?,?,?)')->execute([$u['id'],count($tasks),$status,$error]);}catch(Throwable){$result['skipped']++;continue;}
            $result[$status==='sent'?'sent':'failed']++;
        }
        return $result;
    }

    private function usersWithPermission(int $org,string $code): array
    {
        $q=$this->pdo->prepare("SELECT u.id FROM utilisateurs u WHERE u.organisation_id=? AND u.actif=1 AND u.statut='actif' AND u.deleted_at IS NULL AND COALESCE((SELECT up.accordee FROM utilisateur_permissions up JOIN permissions po ON po.id=up.permission_id AND po.actif=1 WHERE up.utilisateur_id=u.id AND po.code=? LIMIT 1),(SELECT 1 FROM utilisateur_roles ur JOIN roles r ON r.id=ur.role_id AND r.actif=1 AND r.deleted_at IS NULL JOIN role_permissions rp ON rp.role_id=r.id JOIN permissions pr ON pr.id=rp.permission_id AND pr.actif=1 WHERE ur.utilisateur_id=u.id AND pr.code=? LIMIT 1),0)=1");
        $q->execute([$org,$code,$code]);return array_map('intval',$q->fetchAll(PDO::FETCH_COLUMN)?:[]);
    }

    private function dateFr(string $date): string { $d=\DateTimeImmutable::createFromFormat('Y-m-d',$date);return $d?$d->format('d/m/Y'):$date; }
}
