<?php
namespace App\Models;

use App\Core\Database;

final class Attendance
{
    public static function professorForUser(int $user): int
    {
        if (\App\Core\Permission::activeSpace() === 'professeur' && \App\Core\Permission::isSuperAdmin()) {
            return (int)(\App\Core\PortalSimulation::professorId() ?? 0);
        }
        $q = Database::connection()->prepare(
            "SELECT id FROM professeurs WHERE utilisateur_id=? AND deleted_at IS NULL LIMIT 1"
        );
        $q->execute([$user]);
        return (int)$q->fetchColumn();
    }

    public static function professorToken(int $professorId, int $organisationId): string
    {
        $q = Database::connection()->prepare(
            "SELECT public_token FROM professeurs
             WHERE id=? AND organisation_id=? AND deleted_at IS NULL LIMIT 1"
        );
        $q->execute([$professorId, $organisationId]);
        return (string)$q->fetchColumn();
    }

    public static function professorByToken(string $token, int $organisationId): int
    {
        if (!preg_match('/\A[a-f0-9]{64}\z/', strtolower($token))) {
            return 0;
        }
        $q = Database::connection()->prepare(
            "SELECT id FROM professeurs
             WHERE public_token=? AND organisation_id=? AND deleted_at IS NULL LIMIT 1"
        );
        $q->execute([strtolower($token), $organisationId]);
        return (int)$q->fetchColumn();
    }

    public static function workDays(int $professor, string $date, int $organisationId): array
    {
        if (!$professor) return [];
        $q = Database::connection()->prepare(
            "SELECT e.id,e.date_evenement,e.heure_debut,e.heure_fin,e.type_evenement,
                    d.libelle discipline,s.libelle salle
             FROM cda_professor_year_events e
             INNER JOIN professeurs p ON p.id=e.professeur_id
             LEFT JOIN disciplines d ON d.id=e.discipline_id
             LEFT JOIN salles s ON s.id=e.salle_id
             WHERE e.professeur_id=? AND p.organisation_id=?
               AND e.deleted_at IS NULL AND e.statut<>'annule'
               AND e.date_evenement=?
             ORDER BY e.heure_debut"
        );
        $q->execute([$professor, $organisationId, $date]);
        return $q->fetchAll() ?: [];
    }

    public static function sessions(string $date, int $professor, int $organisationId): array
    {
        $sql =
            "SELECT se.*,g.libelle groupe,a.libelle atelier,
                    CONCAT(p.prenom,' ',p.nom) professeur,s.libelle salle,
                    (SELECT COUNT(*) FROM seance_participants sp WHERE sp.seance_id=se.id) participants,
                    (SELECT COUNT(*) FROM presences pr
                     JOIN seance_participants sp2 ON sp2.id=pr.seance_participant_id
                     WHERE sp2.seance_id=se.id) pointes
             FROM seances se
             JOIN groupes_cours g ON g.id=se.groupe_id
             JOIN ateliers a ON a.id=g.atelier_id
             LEFT JOIN professeurs p ON p.id=se.professeur_id
             LEFT JOIN salles s ON s.id=se.salle_id
             WHERE se.deleted_at IS NULL AND a.organisation_id=? AND se.date_seance=?"
            . ($professor ? " AND se.professeur_id=?" : "")
            . " ORDER BY se.heure_debut";
        $q = Database::connection()->prepare($sql);
        $q->execute($professor
            ? [$organisationId, $date, $professor]
            : [$organisationId, $date]);
        return $q->fetchAll() ?: [];
    }

    public static function groups(int $professor, int $organisationId): array
    {
        $sql =
            "SELECT DISTINCT g.id,a.libelle atelier_libelle,g.libelle groupe_libelle,
                    CONCAT(a.libelle,' — ',g.libelle) libelle
             FROM groupes_cours g
             JOIN ateliers a ON a.id=g.atelier_id
             LEFT JOIN groupe_planning_versions v
                    ON v.groupe_id=g.id AND v.date_fin_effet IS NULL
             WHERE g.deleted_at IS NULL AND a.organisation_id=?"
            . ($professor ? " AND v.professeur_principal_id=?" : "")
            . " ORDER BY atelier_libelle,groupe_libelle";
        $q = Database::connection()->prepare($sql);
        $q->execute($professor ? [$organisationId, $professor] : [$organisationId]);
        return $q->fetchAll() ?: [];
    }

    public static function create(int $group, string $date, int $organisationId, int $ownProfessor = 0): string
    {
        $pdo = Database::connection();
        $q = $pdo->prepare(
            "SELECT g.*,v.id planning_version_id,v.heure_debut,v.heure_fin,
                    v.professeur_principal_id,v.salle_id
             FROM groupes_cours g
             JOIN ateliers a ON a.id=g.atelier_id
             JOIN groupe_planning_versions v
                  ON v.groupe_id=g.id AND v.date_fin_effet IS NULL
             WHERE g.id=? AND a.organisation_id=? AND g.deleted_at IS NULL"
            . ($ownProfessor > 0 ? " AND v.professeur_principal_id=?" : "")
            . " LIMIT 1"
        );
        $params = [$group, $organisationId];
        if ($ownProfessor > 0) $params[] = $ownProfessor;
        $q->execute($params);
        $g = $q->fetch();
        if (!$g) throw new \RuntimeException('Groupe ou planning actif introuvable.');

        $minutes = (strtotime($g['heure_fin']) - strtotime($g['heure_debut'])) / 60;
        $token = bin2hex(random_bytes(32));
        $pdo->beginTransaction();
        try {
            $s = $pdo->prepare(
                "INSERT INTO seances(
                    groupe_id,planning_version_id,professeur_id,salle_id,date_seance,
                    heure_debut,heure_fin,duree_prevue_minutes,statut,public_token
                 ) VALUES(?,?,?,?,?,?,?,?,'confirmee',?)"
            );
            $s->execute([
                $group,$g['planning_version_id'],$g['professeur_principal_id'],$g['salle_id'],
                $date,$g['heure_debut'],$g['heure_fin'],$minutes,$token,
            ]);
            $id = (int)$pdo->lastInsertId();
            $pdo->prepare(
                "INSERT INTO seance_participants(
                    seance_id,membre_id,heure_individuelle_debut,heure_individuelle_fin
                 )
                 SELECT ?,membre_id,heure_individuelle_debut,heure_individuelle_fin
                 FROM groupe_participants
                 WHERE groupe_id=? AND deleted_at IS NULL AND statut='actif'"
            )->execute([$id,$group]);
            $pdo->commit();
            return $token;
        } catch (\Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }

    public static function resolveSession(string $token, int $organisationId, int $ownProfessor = 0): ?array
    {
        if (!preg_match('/\A[a-f0-9]{64}\z/', strtolower($token))) return null;
        $sql =
            "SELECT se.*,g.libelle groupe,a.libelle atelier,
                    CONCAT(p.prenom,' ',p.nom) professeur,s.libelle salle
             FROM seances se
             JOIN groupes_cours g ON g.id=se.groupe_id
             JOIN ateliers a ON a.id=g.atelier_id
             LEFT JOIN professeurs p ON p.id=se.professeur_id
             LEFT JOIN salles s ON s.id=se.salle_id
             WHERE se.public_token=? AND se.deleted_at IS NULL AND a.organisation_id=?"
            . ($ownProfessor > 0 ? " AND se.professeur_id=?" : "")
            . " LIMIT 1";
        $q = Database::connection()->prepare($sql);
        $params = [strtolower($token), $organisationId];
        if ($ownProfessor > 0) $params[] = $ownProfessor;
        $q->execute($params);
        return $q->fetch() ?: null;
    }

    public static function sheet(array $session): array
    {
        $id = (int)$session['id'];
        $q = Database::connection()->prepare(
            "SELECT sp.id,m.nom,m.prenom,pr.id presence_id,pr.statut,pr.heure_arrivee,
                    pr.motif_absence,pr.observation,pr.validee
             FROM seance_participants sp
             JOIN membres_famille m ON m.id=sp.membre_id
             LEFT JOIN presences pr ON pr.seance_participant_id=sp.id
             WHERE sp.seance_id=? ORDER BY m.nom,m.prenom"
        );
        $q->execute([$id]);
        $d = Database::connection()->prepare(
            "SELECT * FROM emargements_documents
             WHERE seance_id=? AND deleted_at IS NULL ORDER BY depose_at DESC"
        );
        $d->execute([$id]);
        return [
            'session'=>$session,
            'participants'=>$q->fetchAll() ?: [],
            'documents'=>$d->fetchAll() ?: [],
        ];
    }

    public static function mark(
        int $sessionId,
        int $participantId,
        string $status,
        string $motif,
        string $observation,
        ?int $user
    ): bool {
        $allowed = ['present','absent','absent_justifie','retard','dispense','cours_annule'];
        if (!in_array($status, $allowed, true)) $status = 'present';
        $s = Database::connection()->prepare(
            "INSERT INTO presences(seance_participant_id,statut,motif_absence,observation,pointe_par)
             SELECT sp.id,?,?,?,?
             FROM seance_participants sp
             WHERE sp.id=? AND sp.seance_id=?
             ON DUPLICATE KEY UPDATE
                statut=VALUES(statut),motif_absence=VALUES(motif_absence),
                observation=VALUES(observation),pointe_par=VALUES(pointe_par),pointe_at=NOW()"
        );
        $s->execute([$status,$motif ?: null,$observation ?: null,$user,$participantId,$sessionId]);
        return $s->rowCount() > 0;
    }

    public static function validate(int $sessionId, ?int $user): void
    {
        $pdo = Database::connection();
        $pdo->beginTransaction();
        try {
            $pdo->prepare(
                "UPDATE presences pr
                 JOIN seance_participants sp ON sp.id=pr.seance_participant_id
                 SET pr.validee=1,pr.validee_par=?,pr.validee_at=NOW()
                 WHERE sp.seance_id=?"
            )->execute([$user,$sessionId]);
            $pdo->prepare(
                "UPDATE seances SET statut='realisee',date_validation=NOW(),validee_par=?
                 WHERE id=? AND deleted_at IS NULL"
            )->execute([$user,$sessionId]);
            $pdo->commit();
        } catch (\Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }
}
