<?php
require_once '../config/database.php';
require_once '../includes/auth_check.php';
require_once '../includes/functions.php';

header('Content-Type: text/html; charset=utf-8');

checkRole(['super_admin','school_admin','teacher']);
requirePermission('students', 'view');

$schoolFilter = currentSchoolId();
if (isSuperAdmin() && !empty($_GET['school_id'])) {
    $schoolFilter = (int)$_GET['school_id'];
}

$class_id     = (int)($_GET['class_id']   ?? 0);
$student_id   = (int)($_GET['student_id'] ?? 0);
$custom_text  = trim($_GET['custom_text'] ?? '');
$print        = isset($_GET['print']);

$schoolsList = $pdo->query("SELECT id,name FROM schools ORDER BY name")->fetchAll();

$classes = $pdo->query("SELECT id,name FROM classes c ORDER BY " . getClassSortSQL('c'))->fetchAll();

$students = [];
if ($class_id) {
    $st = $pdo->prepare("SELECT id, first_name, last_name FROM students WHERE class_id=? ORDER BY last_name, first_name");
    $st->execute([$class_id]);
    $students = $st->fetchAll();
}

$studentInfo = null; $schoolInfo = null; $director = null; $journey = [];

if ($student_id) {
    $stmt = $pdo->prepare("SELECT s.*, c.name AS class_name FROM students s JOIN classes c ON s.class_id=c.id WHERE s.id=?");
    $stmt->execute([$student_id]);
    $studentInfo = $stmt->fetch();

    if ($studentInfo) {
        $stmt = $pdo->prepare("SELECT * FROM schools WHERE id=?");
        $stmt->execute([$studentInfo['school_id']]);
        $schoolInfo = $stmt->fetch();

        $stmt = $pdo->prepare("SELECT * FROM directors WHERE school_id=? AND is_active=1 LIMIT 1");
        $stmt->execute([$studentInfo['school_id']]);
        $director = $stmt->fetch();

        $stmt = $pdo->prepare("
            SELECT DISTINCT g.academic_year, g.class_id, c.name AS class_name, c.level
            FROM grades g
            JOIN classes c ON g.class_id=c.id
            WHERE g.student_id=?
            ORDER BY g.academic_year, c.name
        ");
        $stmt->execute([$student_id]);
        $journey = $stmt->fetchAll();

        // Fallback vers la table attendance si aucun grade
        if (empty($journey)) {
            $stmt = $pdo->prepare("
                SELECT DISTINCT a.academic_year, a.class_id, c.name AS class_name, c.level
                FROM attendance a
                JOIN classes c ON a.class_id=c.id
                WHERE a.student_id=?
                ORDER BY a.academic_year, c.name
            ");
            $stmt->execute([$student_id]);
            $journey = $stmt->fetchAll();
        }

        // Fallback vers la classe actuelle de l'élève
        if (empty($journey)) {
            $ayFallback = getDefaultAcademicYear($pdo, $studentInfo['school_id']);
            $journey[] = [
                'academic_year' => $ayFallback,
                'class_id' => $studentInfo['class_id'],
                'class_name' => $studentInfo['class_name'],
                'level' => null,
                'trimestres' => [],
            ];
        }

        foreach ($journey as &$j) {
            $ay = $j['academic_year'];
            $clId = $j['class_id'];

            $stmt = $pdo->prepare("
                SELECT g.trimestre,
                    SUM(CASE
                        WHEN g.note_interro IS NOT NULL AND g.note_devoir IS NOT NULL AND g.note_compo IS NOT NULL THEN (g.note_interro*1+g.note_devoir*2+g.note_compo*3)/6 * COALESCE(cs.coefficient,1)
                        WHEN g.note_interro IS NOT NULL AND g.note_compo IS NOT NULL THEN (g.note_interro*1+g.note_compo*3)/4 * COALESCE(cs.coefficient,1)
                        WHEN g.note_devoir IS NOT NULL AND g.note_compo IS NOT NULL THEN (g.note_devoir*2+g.note_compo*3)/5 * COALESCE(cs.coefficient,1)
                        WHEN g.note_interro IS NOT NULL AND g.note_devoir IS NOT NULL THEN (g.note_interro*1+g.note_devoir*2)/3 * COALESCE(cs.coefficient,1)
                        WHEN g.note_interro IS NOT NULL THEN g.note_interro * COALESCE(cs.coefficient,1)
                        WHEN g.note_devoir IS NOT NULL THEN g.note_devoir * COALESCE(cs.coefficient,1)
                        WHEN g.note_compo IS NOT NULL THEN g.note_compo * COALESCE(cs.coefficient,1)
                        ELSE 0 END) / NULLIF(SUM(COALESCE(cs.coefficient,1)),0) AS moy
                FROM grades g
                LEFT JOIN class_subjects cs ON cs.subject_id=g.subject_id AND cs.class_id=g.class_id
                WHERE g.student_id=? AND g.academic_year=? AND g.class_id=?
                GROUP BY g.trimestre
                ORDER BY g.trimestre
            ");
            $stmt->execute([$student_id, $ay, $clId]);
            $j['trimestres'] = $stmt->fetchAll();
        }
        unset($j);
    }
}

function getMentionLabel(float $moy): string {
    return autoAppreciation($moy);
}
?>
<?php if ($print && $studentInfo): ?>
<!DOCTYPE html>
<html lang="fr">
<head>
    <meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Attestation scolaire — <?= e($studentInfo['last_name'].' '.$studentInfo['first_name']) ?></title>
    <link rel="stylesheet" href="../assets/css/style.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        @page { size: A4 landscape; margin: 0; }
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body { background: #fff; font-family: system-ui, -apple-system, sans-serif; }
        .cert-page { width:297mm; min-height:210mm; margin:0 auto; padding:12mm 20mm; position:relative; }
        .certificate-wrapper { width:100%; background:#fff; border:2px solid #d4a843; border-radius:6px; padding:1.5rem 2.2rem; position:relative; }
        .certificate-wrapper::before {
            content: ''; position: absolute; top: 4mm; left: 4mm; right: 4mm; bottom: 4mm;
            border: 1px solid #d4a843; border-radius: 4px; pointer-events: none;
        }
        .cert-wrapper-inner { position: relative; z-index: 1; }
        .cert-header { text-align: center; border-bottom: 2px solid #d4a843; padding-bottom: .8rem; margin-bottom: 1rem; }
        .cert-logo { max-height: 65px; margin-bottom: .4rem; }
        .cert-school-name { font-size: 22px; font-weight: 700; color: #1a202c; text-transform: uppercase; letter-spacing: 1px; }
        .cert-school-info { font-size: 16px; color: #4a5568; }
        .cert-title-wrap { text-align: center; }
        .cert-title { text-align: center; font-size: 26px; font-weight: 800; color: #d4a843; text-transform: uppercase; letter-spacing: 3px; margin: .8rem 0 1.2rem; border-bottom: 2px solid #d4a843; display: inline-block; padding-bottom: .3rem; }
        .cert-body { font-size: 20px; line-height: 1.4; color: #2d3748; }
        .cert-intro { text-align: justify; margin-bottom: 1rem; padding: .6rem .8rem; background: #fefcf5; border-left: 4px solid #d4a843; border-radius: 4px; }
        .cert-student-name { font-size: 22px; font-weight: 700; color: #1a202c; }
        .journey-table { width: 100%; border-collapse: collapse; margin: .8rem 0; font-size: 18px; }
        .journey-table th { background: #f7f1e0; color: #5d4a1e; font-weight: 600; padding: .4rem .5rem; border: 1px solid #e8dcc8; text-align: center; }
        .journey-table td { padding: .35rem .5rem; border: 1px solid #e8dcc8; text-align: center; }
        .journey-table tr:nth-child(even) { background: #fefcf5; }
        .journey-table td:first-child { text-align: left; }
        .cert-custom-text { margin: .8rem 0; padding: .6rem .8rem; background: #fafafa; border: 1px dashed #cbd5e0; border-radius: 6px; font-style: italic; }
        .cert-footer { display: flex; justify-content: space-between; align-items: flex-start; margin-top: 1.5rem; padding-top: .8rem; border-top: 1px solid #e2e8f0; }
        .cert-sign-line { border-top: 1px solid #000; width: 180px; }
        .mention-badge { display: inline-block; padding: .15rem .6rem; border-radius: 20px; font-size: 12px; font-weight: 600; }
        .mention-tb { background: #ffd700; color: #5d4a1e; }
        .mention-bien { background: #c6f6d5; color: #22543d; }
        .mention-ab { background: #bee3f8; color: #2a4365; }
        .mention-passable { background: #fefcbf; color: #744210; }
        .mention-insuffisant { background: #fed7d7; color: #9b2c2c; }
        @media print {
            html, body { width:297mm; height:210mm; margin:0; padding:0; }
            .cert-page { width:297mm; min-height:210mm; padding:12mm 20mm; margin:0; }
            .certificate-wrapper { border: 2px solid #d4a843 !important; box-shadow: none !important; border-radius:6px !important; }
            .certificate-wrapper::before { border: 1px solid #d4a843 !important; top:4mm !important; left:4mm !important; right:4mm !important; bottom:4mm !important; }
        }
    </style>
</head>
<body>
<div class="cert-page">
<div class="certificate-wrapper">
    <div class="cert-wrapper-inner">
        <div class="cert-header">
            <?php if (!empty($schoolInfo['logo'])): ?>
            <img src="<?= e(logoUrl($schoolInfo['logo'])) ?>" class="cert-logo" alt="Logo">
            <?php endif; ?>
            <div class="cert-school-name"><?= e($schoolInfo['name'] ?? '') ?></div>
            <div class="cert-school-info">
                <?php if (!empty($schoolInfo['address'])): ?><?= e($schoolInfo['address']) ?><br><?php endif; ?>
                <?php if (!empty($schoolInfo['phone'])): ?>Tél. <?= e($schoolInfo['phone']) ?><?php endif; ?>
                <?php if (!empty($schoolInfo['email'])): ?> | Email : <?= e($schoolInfo['email']) ?><?php endif; ?>
            </div>
        </div>
        <div class="cert-title-wrap"><div class="cert-title">Attestation Scolaire</div></div>
        <div class="cert-body">
            <div class="cert-intro">
                Je soussigné<?= $director ? ($director['gender'] === 'F' ? 'e' : '') : '' ?>,
                <strong><?= $director ? e($director['last_name'].' '.$director['first_name']) : 'le Directeur' ?></strong>,
                Directeur<?= $director && $director['gender'] === 'F' ? 'rice' : '' ?> de l'école <strong><?= e($schoolInfo['name'] ?? '') ?></strong>,
                atteste que l'élève <span class="cert-student-name"><?= e(strtoupper($studentInfo['last_name']).' '.$studentInfo['first_name']) ?></span>,
                né<?= $studentInfo['gender'] === 'F' ? 'e' : '' ?> le <strong><?= formatDate($studentInfo['birth_date']) ?></strong>,
                a fréquenté notre établissement et a suivi le parcours scolaire suivant :
            </div>
            <?php if (!empty($journey)): ?>
            <table class="journey-table">
                <thead><tr><th>Année</th><th>Classe(s) fréquentée(s)</th></tr></thead>
                <tbody>
                    <?php foreach ($journey as $j): ?>
                    <tr>
                        <td><?= e($j['academic_year']) ?></td>
                        <td><?= e($j['class_name']) ?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
            <?php else: ?>
            <p style="text-align:center;color:#a0aec0;padding:1rem;">Aucune donnée de parcours trouvée.</p>
            <?php endif; ?>
            <?php if (!empty($custom_text)): ?>
            <div class="cert-custom-text"><?= nl2br(e($custom_text)) ?></div>
            <?php endif; ?>
        </div>
        <div class="cert-footer">
            <div style="font-size:16px;color:#4a5568;">Fait à <?= e(explode(',', $schoolInfo['address'] ?? 'Lomé')[0]) ?>, le <?= date('d/m/Y') ?></div>
            <div style="text-align:center;">
                <div class="cert-sign-line" style="margin:0 auto .3rem;"></div>
                <div style="font-size:16px;color:#4a5568;">Signature du Directeur<?= $director && $director['gender'] === 'F' ? 'rice' : '' ?></div>
            </div>
        </div>
    </div>
</div>
</div>
<script>window.onload=function(){window.print()};</script>
</body>
</html>
<?php else: ?>
<!DOCTYPE html>
<html lang="fr">
<head>
    <meta charset="UTF-8"><meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Attestation scolaire - Nukunu Mofiala</title>
    <link rel="stylesheet" href="../assets/css/style.css">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        .certificate-wrapper {
            max-width: 297mm; margin: 0 auto; background: #fff;
            border: 2px solid #d4a843; border-radius: 6px;
            padding: 1.5rem 2.2rem; box-shadow: 0 4px 20px rgba(0,0,0,.1); position: relative;
        }
        .certificate-wrapper::before {
            content: ''; position: absolute; top: 4mm; left: 4mm; right: 4mm; bottom: 4mm;
            border: 1px solid #d4a843; border-radius: 4px; pointer-events: none;
        }
        .cert-header { text-align: center; border-bottom: 2px solid #d4a843; padding-bottom: 1rem; margin-bottom: 1.2rem; }
        .cert-logo { max-height: 70px; margin-bottom: .5rem; }
        .cert-school-name { font-size: 22px; font-weight: 700; color: #1a202c; text-transform: uppercase; letter-spacing: 1px; }
        .cert-school-info { font-size: 16px; color: #4a5568; }
        .cert-title-wrap { text-align: center; }
        .cert-title { text-align: center; font-size: 26px; font-weight: 800; color: #d4a843; text-transform: uppercase; letter-spacing: 3px; margin: 1rem 0 1.5rem; border-bottom: 2px solid #d4a843; display: inline-block; padding-bottom: .4rem; }
        .cert-body { font-size: 20px; line-height: 1.4; color: #2d3748; }
        .cert-intro { text-align: justify; margin-bottom: 1.2rem; padding: .8rem 1rem; background: #fefcf5; border-left: 4px solid #d4a843; border-radius: 4px; }
        .cert-student-name { font-size: 22px; font-weight: 700; color: #1a202c; }
        .journey-table { width: 100%; border-collapse: collapse; margin: 1rem 0; font-size: 18px; }
        .journey-table th { background: #f7f1e0; color: #5d4a1e; font-weight: 600; padding: .5rem .6rem; border: 1px solid #e8dcc8; text-align: center; }
        .journey-table td { padding: .45rem .6rem; border: 1px solid #e8dcc8; text-align: center; }
        .journey-table tr:nth-child(even) { background: #fefcf5; }
        .journey-table td:first-child { text-align: left; }
        .cert-custom-text { margin: 1rem 0; padding: .8rem 1rem; background: #fafafa; border: 1px dashed #cbd5e0; border-radius: 6px; font-style: italic; }
        .cert-footer { display: flex; justify-content: space-between; align-items: flex-start; margin-top: 2rem; padding-top: 1rem; border-top: 1px solid #e2e8f0; }
        .cert-sign-line { border-top: 1px solid #000; width: 200px; }
        .mention-badge { display: inline-block; padding: .15rem .6rem; border-radius: 20px; font-size: 12px; font-weight: 600; }
        .mention-tb { background: #ffd700; color: #5d4a1e; }
        .mention-bien { background: #c6f6d5; color: #22543d; }
        .mention-ab { background: #bee3f8; color: #2a4365; }
        .mention-passable { background: #fefcbf; color: #744210; }
        .mention-insuffisant { background: #fed7d7; color: #9b2c2c; }
        @media print {
            @page { size: A4 landscape; margin: 0; }
            body { background: #fff !important; }
            .no-print { display: none !important; }
            .dashboard .sidebar, .dashboard .topbar { display: none !important; }
            .main-content { margin-left: 0 !important; padding: 0 !important; }
            .certificate-wrapper { border: 2px solid #d4a843 !important; box-shadow: none !important; border-radius:6px !important; }
            .certificate-wrapper::before { border: 1px solid #d4a843 !important; top:4mm !important; left:4mm !important; right:4mm !important; bottom:4mm !important; }
        }
    </style>
</head>
<body>
<div class="dashboard">
    <?php include '../includes/sidebar.php'; ?>
    <div class="main-content">
        <?php $pageTitle = '<i class="fas fa-certificate" style="color:#d4a843;"></i> Attestation scolaire'; include '../includes/topbar.php'; ?>
        <?= showFlash() ?>

        <div class="card no-print" style="margin-bottom:1.5rem;">
            <div class="card-header">
                <span class="card-title"><i class="fas fa-search"></i> Sélectionner un élève</span>
            </div>
            <form method="GET" style="display:flex;flex-wrap:wrap;gap:.75rem;align-items:end;">
                <?php if (!$schoolFilter): ?>
                <div class="form-group" style="margin:0;flex:0 0 180px;">
                    <label>École</label>
                    <select name="school_id" onchange="this.form.submit()">
                        <option value="">— Toutes —</option>
                        <?php foreach ($schoolsList as $sc): ?>
                        <option value="<?= $sc['id'] ?>" <?= ($_GET['school_id']??'')==$sc['id']?'selected':'' ?>><?= e($sc['name']) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <?php endif; ?>
                <div class="form-group" style="margin:0;flex:0 0 180px;">
                    <label>Classe</label>
                    <select name="class_id" onchange="this.form.submit()">
                        <option value="">— Choisir —</option>
                        <?php foreach ($classes as $cl): ?>
                        <option value="<?= $cl['id'] ?>" <?= $class_id===$cl['id']?'selected':'' ?>><?= e($cl['name']) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <?php if (!empty($students)): ?>
                <div class="form-group" style="margin:0;flex:0 0 200px;">
                    <label>Élève</label>
                    <select name="student_id" required>
                        <option value="">— Choisir —</option>
                        <?php foreach ($students as $st): ?>
                        <option value="<?= $st['id'] ?>" <?= $student_id===$st['id']?'selected':'' ?>><?= e($st['last_name'].' '.$st['first_name']) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <?php endif; ?>
                <div class="form-group" style="margin:0;flex:1;min-width:250px;">
                    <label>Texte libre <small>(optionnel)</small></label>
                    <textarea name="custom_text" rows="2" style="width:100%;padding:.5rem;border:1px solid #e2e8f0;border-radius:6px;font-size:.85rem;resize:vertical;"><?= e($custom_text) ?></textarea>
                </div>
                <button type="submit" class="btn-submit" style="background:#d4a843;border-color:#b8922e;">
                    <i class="fas fa-certificate"></i> Générer l'attestation
                </button>
            </form>
        </div>

        <?php if (!$student_id): ?>
        <div class="card" style="text-align:center;padding:3rem;color:#999;">
            <i class="fas fa-certificate" style="font-size:3rem;display:block;margin-bottom:1rem;color:#d4a843;"></i>
            Sélectionnez un élève pour générer son attestation scolaire.
        </div>
        <?php elseif (!$studentInfo): ?>
        <div class="card" style="text-align:center;padding:2rem;color:#999;">Élève introuvable.</div>
        <?php else: ?>
        <div class="no-print" style="text-align:right;margin-bottom:.75rem;">
            <a href="?student_id=<?= $student_id ?>&custom_text=<?= urlencode($custom_text) ?>&print=1"
               class="btn-action btn-edit" target="_blank">
                <i class="fas fa-print"></i> Imprimer / PDF
            </a>
        </div>

        <div class="certificate-wrapper">
            <div class="cert-wrapper-inner">
                <div class="cert-header">
                    <?php if (!empty($schoolInfo['logo'])): ?>
                    <img src="<?= e(logoUrl($schoolInfo['logo'])) ?>" class="cert-logo" alt="Logo">
                    <?php endif; ?>
                    <div class="cert-school-name"><?= e($schoolInfo['name'] ?? '') ?></div>
                    <div class="cert-school-info">
                        <?php if (!empty($schoolInfo['address'])): ?><?= e($schoolInfo['address']) ?><br><?php endif; ?>
                        <?php if (!empty($schoolInfo['phone'])): ?>Tél. <?= e($schoolInfo['phone']) ?><?php endif; ?>
                        <?php if (!empty($schoolInfo['email'])): ?> | Email : <?= e($schoolInfo['email']) ?><?php endif; ?>
                    </div>
                </div>
                <div class="cert-title-wrap"><div class="cert-title">Attestation Scolaire</div></div>
                <div class="cert-body">
                    <div class="cert-intro">
                        Je soussigné<?= $director ? ($director['gender'] === 'F' ? 'e' : '') : '' ?>,
                        <strong><?= $director ? e($director['last_name'].' '.$director['first_name']) : 'le Directeur' ?></strong>,
                        Directeur<?= $director && $director['gender'] === 'F' ? 'rice' : '' ?> de l'école <strong><?= e($schoolInfo['name'] ?? '') ?></strong>,
                        atteste que l'élève <span class="cert-student-name"><?= e(strtoupper($studentInfo['last_name']).' '.$studentInfo['first_name']) ?></span>,
                        né<?= $studentInfo['gender'] === 'F' ? 'e' : '' ?> le <strong><?= formatDate($studentInfo['birth_date']) ?></strong>,
                        a fréquenté notre établissement et a suivi le parcours scolaire suivant :
                    </div>
                    <?php if (!empty($journey)): ?>
                    <table class="journey-table">
                        <thead><tr><th>Année</th><th>Classe(s) fréquentée(s)</th></tr></thead>
                        <tbody>
                            <?php foreach ($journey as $j): ?>
                            <tr>
                                <td><?= e($j['academic_year']) ?></td>
                                <td><?= e($j['class_name']) ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                    <?php else: ?>
                    <p style="text-align:center;color:#a0aec0;padding:1rem;">Aucune donnée de parcours trouvée.</p>
                    <?php endif; ?>
                    <?php if (!empty($custom_text)): ?>
                    <div class="cert-custom-text"><?= nl2br(e($custom_text)) ?></div>
                    <?php endif; ?>
                </div>
                <div class="cert-footer">
            <div style="font-size:16px;color:#4a5568;">Fait à <?= e(explode(',', $schoolInfo['address'] ?? 'Lomé')[0]) ?>, le <?= date('d/m/Y') ?></div>
            <div style="text-align:center;">
                <div class="cert-sign-line" style="margin:0 auto .3rem;"></div>
                <div style="font-size:16px;color:#4a5568;">Signature du Directeur<?= $director && $director['gender'] === 'F' ? 'rice' : '' ?></div>
            </div>
        </div>
    </div>
</div>
<?php endif; ?>
    </div>
</div>
</body>
</html>
<?php endif; ?>
