<?php
session_start();

require_once __DIR__ . '/../includes/db_connect.php';
require_once __DIR__ . '/../includes/logger.php';

/* ===== Безпечний екранер ===== */
function h(?string $s): string { return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

/* ===== Доступ ===== */
if (!isset($_SESSION['user_id']) || !in_array($_SESSION['role'] ?? '', ['admin','teacher'], true)) {
    header("Location: ../index.php");
    exit();
}

/* ===== CSRF ===== */
if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(16));
}
$CSRF = $_SESSION['csrf_token'];

/* ===== Flash (PRG) ===== */
function flash_get(string $key): ?string {
    if (!isset($_SESSION['flash'][$key])) return null;
    $m = $_SESSION['flash'][$key]; unset($_SESSION['flash'][$key]); return $m;
}
function flash_set(string $key, string $msg): void { $_SESSION['flash'][$key] = $msg; }

/* ===== Пошта (опціонально) ===== */
$MAIL_READY = false;
$MAIL_CFG = [];
try {
    $cfgPath = __DIR__ . '/../includes/config_mail.php';
    if (is_file($cfgPath)) {
        $MAIL_CFG = require $cfgPath;

        @require_once __DIR__ . '/../includes/PHPMailer/src/PHPMailer.php';
        @require_once __DIR__ . '/../includes/PHPMailer/src/SMTP.php';
        @require_once __DIR__ . '/../includes/PHPMailer/src/Exception.php';

        if (class_exists('\PHPMailer\PHPMailer\PHPMailer')) {
            $MAIL_READY = true;
        }
    }
} catch (Throwable $e) {
    $MAIL_READY = false;
}

function email_blocked_domain(string $email): bool {
    $email = trim($email);
    if ($email === '') return false;
    $email_lc = strtolower($email);
    return (substr($email_lc, -strlen('@ej.local')) === '@ej.local');
}
function build_mailer(array $cfg): ?\PHPMailer\PHPMailer\PHPMailer {
    if (!class_exists('\PHPMailer\PHPMailer\PHPMailer')) return null;

    $mail = new \PHPMailer\PHPMailer\PHPMailer(true);
    try {
        $mail->isSMTP();
        $mail->Host       = (string)($cfg['host']       ?? 'localhost');
        $mail->SMTPAuth   = (bool)  ($cfg['smtpauth']   ?? true);
        $mail->Username   = (string)($cfg['username']   ?? '');
        $mail->Password   = (string)($cfg['password']   ?? '');
        $mail->Port       = (int)   ($cfg['port']       ?? 587);
        $enc              = (string)($cfg['encryption'] ?? '');
        if ($enc !== '') $mail->SMTPSecure = $enc;

        $fromEmail = (string)($cfg['from_email'] ?? ($cfg['username'] ?? 'no-reply@example.com'));
        $fromName  = (string)($cfg['from_name']  ?? 'Електронний журнал');
        $mail->setFrom($fromEmail, $fromName);

        $mail->CharSet = 'UTF-8';
        $mail->isHTML(true);
        return $mail;
    } catch (Throwable $e) {
        return null;
    }
}

/* ===== Приватні повідомлення (внутрішні) ===== */
function ej_send_private_message(mysqli $conn, int $sender_id, int $recipient_id, string $subject, string $body): void {
    $subject = mb_substr($subject, 0, 255);
    $sql = "INSERT INTO private_messages (sender_id, recipient_id, subject, body, created_at, read_at, is_deleted_sender, is_deleted_recipient)
            VALUES (?,?,?,?, NOW(), NULL, 0, 0)";
    $st = $conn->prepare($sql);
    if (!$st) return;
    $st->bind_param('iiss', $sender_id, $recipient_id, $subject, $body);
    $st->execute();
    $st->close();
}

/* ===== Перевірка наявності таблиці/колонок ===== */
function ej_has_table(mysqli $conn, string $table): bool {
    $sql = "
        SELECT 1
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = ?
        LIMIT 1
    ";
    $st = $conn->prepare($sql);
    if (!$st) return false;
    $st->bind_param('s', $table);
    $st->execute();
    $res = $st->get_result();
    $ok = ($res && $res->num_rows > 0);
    $st->close();
    return $ok;
}
function ej_has_column(mysqli $conn, string $table, string $column): bool {
    $sql = "
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = ?
          AND COLUMN_NAME = ?
        LIMIT 1
    ";
    $st = $conn->prepare($sql);
    if (!$st) return false;
    $st->bind_param('ss', $table, $column);
    $st->execute();
    $res = $st->get_result();
    $ok = ($res && $res->num_rows > 0);
    $st->close();
    return $ok;
}
function ej_first_existing_column(mysqli $conn, string $table, array $candidates): ?string {
    foreach ($candidates as $col) {
        if (ej_has_column($conn, $table, $col)) return $col;
    }
    return null;
}

/* ===== Сортування ПІБ (єдина функція для таблиці і листів) ===== */
function ej_sort_rows_by_full_name(array &$rows, string $key = 'full_name'): void {
    if (count($rows) <= 1) return;

    $sorted = false;

    // 1) intl Collator (найкращий варіант)
    if (class_exists('Collator')) {
        try {
            $coll = new Collator('uk_UA');
            if ($coll) {
                usort($rows, function($a, $b) use ($coll, $key) {
                    $aa = (string)($a[$key] ?? '');
                    $bb = (string)($b[$key] ?? '');
                    return $coll->compare($aa, $bb);
                });
                $sorted = true;
            }
        } catch (Throwable $e) {
            $sorted = false;
        }
    }

    // 2) locale + strcoll
    if (!$sorted) {
        $prevLocale = setlocale(LC_COLLATE, 0);
        $ok = setlocale(LC_COLLATE, 'uk_UA.UTF-8', 'uk_UA.utf8', 'uk_UA', 'uk_UA.UTF8');
        if ($ok) {
            usort($rows, function($a, $b) use ($key) {
                $aa = mb_strtolower((string)($a[$key] ?? ''), 'UTF-8');
                $bb = mb_strtolower((string)($b[$key] ?? ''), 'UTF-8');
                return strcoll($aa, $bb);
            });
            setlocale(LC_COLLATE, $prevLocale);
            $sorted = true;
        }
    }

    // 3) fallback: власна вага українського алфавіту
    if (!$sorted) {
        $alphabet = ['а','б','в','г','ґ','д','е','є','ж','з','и','і','ї','й','к','л','м','н','о','п','р','с','т','у','ф','х','ц','ч','ш','щ','ь','ю','я'];
        $weight = array_flip($alphabet);

        $normalize = function(string $s) use ($weight): string {
            $s = mb_strtolower($s,'UTF-8');
            $len = mb_strlen($s,'UTF-8');
            $buf = '';
            for ($i=0; $i<$len; $i++){
                $ch = mb_substr($s,$i,1,'UTF-8');
                if (isset($weight[$ch])) $buf .= sprintf('%02d',$weight[$ch]);
                elseif (preg_match('/[a-z]/',$ch)) $buf .= '90' . $ch;
                else $buf .= '99' . $ch;
            }
            return $buf;
        };

        usort($rows, function($a, $b) use ($normalize, $key) {
            $aa = $normalize((string)($a[$key] ?? ''));
            $bb = $normalize((string)($b[$key] ?? ''));
            return strcmp($aa, $bb);
        });
    }
}

/* ===== ЛОГ НОТИФІКАЦІЙ (щоб "1–12" не дублювалися) ===== */
function ej_ensure_grade_notifications(mysqli $conn): bool {
    // Створюємо таблицю, якщо її немає
    if (ej_has_table($conn, 'grade_notifications')) return true;

    $sql = "
        CREATE TABLE IF NOT EXISTS `grade_notifications` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
            `lesson_id` INT UNSIGNED NOT NULL,
            `student_id` INT UNSIGNED NOT NULL,
            `recipient_id` INT UNSIGNED NOT NULL,
            `recipient_role` VARCHAR(16) NOT NULL,
            `notif_type` VARCHAR(8) NOT NULL,
            `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (`id`),
            UNIQUE KEY `uniq_once` (`lesson_id`,`student_id`,`recipient_id`,`recipient_role`,`notif_type`),
            KEY `idx_lesson` (`lesson_id`),
            KEY `idx_student` (`student_id`),
            KEY `idx_recipient` (`recipient_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ";

    // Якщо немає прав на CREATE — просто не будемо робити "once" логіку, але спробуємо
    return (bool)$conn->query($sql);
}
function ej_was_notified_numeric(mysqli $conn, int $lesson_id, int $student_id, int $recipient_id, string $recipient_role): bool {
    if (!ej_has_table($conn, 'grade_notifications')) return false;

    $notif_type = 'NUM';
    $sql = "SELECT 1 FROM grade_notifications
            WHERE lesson_id=? AND student_id=? AND recipient_id=? AND recipient_role=? AND notif_type=?
            LIMIT 1";
    $st = $conn->prepare($sql);
    if (!$st) return false;
    $st->bind_param('iiiss', $lesson_id, $student_id, $recipient_id, $recipient_role, $notif_type);
    $st->execute();
    $res = $st->get_result();
    $ok = ($res && $res->num_rows > 0);
    $st->close();
    return $ok;
}
function ej_mark_notified_numeric(mysqli $conn, int $lesson_id, int $student_id, int $recipient_id, string $recipient_role): void {
    if (!ej_has_table($conn, 'grade_notifications')) return;

    $notif_type = 'NUM';
    $sql = "INSERT IGNORE INTO grade_notifications (lesson_id, student_id, recipient_id, recipient_role, notif_type, created_at)
            VALUES (?,?,?,?,?, NOW())";
    $st = $conn->prepare($sql);
    if (!$st) return;
    $st->bind_param('iiiss', $lesson_id, $student_id, $recipient_id, $recipient_role, $notif_type);
    $st->execute();
    $st->close();
}

/* ===== Вхідні ===== */
$subject_id = (int)($_GET['subject_id'] ?? 0);
if ($subject_id <= 0) { http_response_code(400); die('Невірний ID предмету'); }

/* ===== Дістаємо предмет + групу + (якщо є) teacher_id / class_teacher_id ===== */
$subTeacherCol = ej_first_existing_column($conn, 'subjects', ['teacher_id','teacher_user_id','user_id']);
$grpCuratorCol = ej_first_existing_column($conn, 'groups', ['class_teacher_id','curator_id','mentor_id','tutor_id','teacher_id']);

$sql = "
    SELECT s.subject_name,
           s.group_id AS raw_group_id,
           g.id AS group_id,
           g.group_name
";
if ($subTeacherCol) $sql .= ", s.`{$subTeacherCol}` AS subject_teacher_id";
if ($grpCuratorCol) $sql .= ", g.`{$grpCuratorCol}` AS group_curator_id";
$sql .= "
    FROM subjects s
    LEFT JOIN `groups` g ON g.id = s.group_id
    WHERE s.id = ?
";

$subject_stmt = $conn->prepare($sql);
$subject_stmt->bind_param('i', $subject_id);
$subject_stmt->execute();
$subject_res = $subject_stmt->get_result();
if ($subject_res->num_rows === 0) { http_response_code(404); die('Предмет не знайдено'); }
$subject = $subject_res->fetch_assoc();
$subject_stmt->close();

$group_id = (int)($subject['group_id'] ?? 0);
if ($group_id <= 0) { http_response_code(500); die('Недійсний group_id для цього предмету'); }

/* ===== Права ===== */
$role = (string)($_SESSION['role'] ?? '');
$user_id = (int)($_SESSION['user_id'] ?? 0);

$subject_teacher_id = (int)($subject['subject_teacher_id'] ?? 0);
$group_curator_id   = (int)($subject['group_curator_id'] ?? 0);

$can_edit = false;
$can_view = false;

if ($role === 'admin') {
    $can_view = true;
    $can_edit = true;
} elseif ($role === 'teacher') {
    if ($subTeacherCol && $subject_teacher_id > 0 && $subject_teacher_id === $user_id) {
        $can_view = true;
        $can_edit = true;
    } else {
        if ($grpCuratorCol && $group_curator_id > 0 && $group_curator_id === $user_id) {
            $can_view = true;
            $can_edit = false;
        } else {
            $can_view = true;
            $can_edit = false;
        }
    }
}

if (!$can_view) {
    header("Location: ../index.php");
    exit();
}

/* ===== Прапорці Т/С/Р ===== */
function parse_flags(string $topic): array {
    $t = ltrim($topic);
    $is_thematic = (bool)preg_match('/^\[(т|Т)\]\s*/u', $t);
    $is_semester = (bool)preg_match('/^\[(с|С)\]\s*/u', $t);
    $is_year     = (bool)preg_match('/^\[(р|Р)\]\s*/u', $t);
    return [$is_thematic ? 1 : 0, $is_semester ? 1 : 0, $is_year ? 1 : 0];
}

/** Гарантовано додає префікс до topic, якщо його ще немає. */
function with_prefix(string $topic, string $flag): string {
    $trim = ltrim($topic);
    if ($flag === 'T') {
        if (preg_match('/^\[(т|Т)\]\s*/u', $trim)) return $topic;
        return "[Т] " . $topic;
    } elseif ($flag === 'S') {
        if (preg_match('/^\[(с|С)\]\s*/u', $trim)) return $topic;
        return "[С] " . $topic;
    } elseif ($flag === 'Y') {
        if (preg_match('/^\[(р|Р)\]\s*/u', $trim)) return $topic;
        return "[Р] " . $topic;
    }
    return $topic;
}

/** Всі уроки предмету */
function ej_get_lessons(mysqli $conn, int $subject_id): array {
    $rows = [];
    $q = $conn->prepare("SELECT id, lesson_date, topic FROM lessons WHERE subject_id=? ORDER BY lesson_date ASC, id ASC");
    $q->bind_param('i', $subject_id);
    $q->execute();
    $r = $q->get_result();
    while ($x = $r->fetch_assoc()) {
        $x['id'] = (int)$x['id'];
        [$th, $sem, $yr] = parse_flags($x['topic'] ?? '');
        $x['is_thematic'] = $th;
        $x['is_semester'] = $sem;
        $x['is_year']     = $yr;
        $x['is_auto']     = ($th || $sem || $yr) ? 1 : 0;
        $rows[] = $x;
    }
    $q->close();
    return $rows;
}

/** Учні групи */
function ej_get_students_ids(mysqli $conn, int $group_id): array {
    $students = [];
    $st = $conn->prepare("SELECT id FROM users WHERE role='student' AND group_id=?");
    $st->bind_param('i', $group_id);
    $st->execute();
    $res = $st->get_result();
    while ($row = $res->fetch_assoc()) $students[] = (int)$row['id'];
    $st->close();
    return $students;
}

/** Завантажити оцінки мапою */
function ej_load_grades_map(mysqli $conn, int $subject_id, array $lesson_ids): array {
    $grades = [];
    $lesson_ids = array_values(array_unique(array_map('intval', $lesson_ids)));
    if (!$lesson_ids) return $grades;

    $in = implode(',', $lesson_ids);
    $sql = "SELECT lesson_id, student_id, grade FROM grades WHERE subject_id=? AND lesson_id IN ($in)";
    $q = $conn->prepare($sql);
    if (!$q) return $grades;
    $q->bind_param('i', $subject_id);
    $q->execute();
    $r = $q->get_result();
    while ($g = $r->fetch_assoc()) {
        $lid = (int)$g['lesson_id'];
        $sid = (int)$g['student_id'];
        $val = is_null($g['grade']) ? 0 : (int)$g['grade'];
        $grades[$lid][$sid] = $val;
    }
    $q->close();
    return $grades;
}

/* ===== EMAIL: оцінки за урок (учень/батьки) ===== */

function send_lesson_grade_email(
    array $cfg,
    string $toEmail,
    string $toName,
    string $subjectName,
    ?int $grade,
    ?string $lessonDate,
    ?string $topic,
    ?string $studentName = null,
    bool $forParent = false
): void {
    if (email_blocked_domain($toEmail)) return;

    $mailer = build_mailer($cfg);
    if (!$mailer) return;

    try {
        $title = 'Оцінки за урок: ' . $subjectName;

        $gradeText = is_null($grade) ? '<strong>Не здав</strong>' : '<strong>' . (int)$grade . '</strong>';
        $dateText  = $lessonDate ? date('d.m.Y', strtotime($lessonDate)) : '—';
        $topicText = $topic ? htmlspecialchars($topic, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') : '—';

        $safeStudent = '';
        if ($studentName !== null && $studentName !== '') {
            $safeStudent = htmlspecialchars($studentName, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
        }

        if ($forParent) {
            if ($safeStudent !== '') {
                $body  = "<p>Оцінка вашої дитини (<strong>{$safeStudent}</strong>) з предмету <strong>{$subjectName}</strong>.</p>";
            } else {
                $body  = "<p>Оцінка вашої дитини з предмету <strong>{$subjectName}</strong>.</p>";
            }
        } else {
            $body  = "<p>Ваша оцінка з предмету <strong>{$subjectName}</strong>.</p>";
        }

        $body .= '<ul style="margin:0; padding-left:18px;">';
        $body .= "<li><b>Урок:</b> {$topicText}</li>";
        $body .= "<li><b>Дата:</b> {$dateText}</li>";
        $body .= "<li><b>Оцінка:</b> {$gradeText}</li>";
        $body .= '</ul>';
        $body .= '<p>Це автоматичне повідомлення Електронного журналу.</p>';

        $mailer->clearAllRecipients();
        $mailer->addAddress($toEmail, $toName ?: $toEmail);
        $mailer->Subject = $title;
        $mailer->Body    = $body;

        $alt  = ($forParent ? 'Оцінка вашої дитини' : 'Ваша оцінка') . " з предмету {$subjectName}\n";
        if ($forParent && $studentName) $alt .= '(' . $studentName . ")\n";
        $alt .= "Урок: " . ($topic ?? '—') . "\n";
        $alt .= "Дата: {$dateText}\n";
        $alt .= 'Оцінка: ' . (is_null($grade) ? 'Не здав' : (string)$grade);

        $mailer->AltBody = $alt;
        $mailer->send();
    } catch (Throwable $e) {
        // мовчки
    }
}

/* ===== EMAIL: класному керівнику (таблиця) ===== */
function send_curator_lesson_email(
    array $cfg,
    string $toEmail,
    string $toName,
    string $subjectName,
    string $lessonTopic,
    ?string $lessonDate,
    array $rows
): void {
    if (email_blocked_domain($toEmail)) return;

    $mailer = build_mailer($cfg);
    if (!$mailer) return;

    try {
        $dateText = $lessonDate ? date('d.m.Y', strtotime($lessonDate)) : '—';
        $safeTopic = htmlspecialchars($lessonTopic, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');

        $title = "Оцінки за урок: {$subjectName}";
        $body  = "<p>Оцінки за урок з предмету <strong>{$subjectName}</strong>.</p>";
        $body .= "<p><b>Урок:</b> {$safeTopic}<br><b>Дата:</b> {$dateText}</p>";

        $body .= '<table cellpadding="6" cellspacing="0" border="1" style="border-collapse:collapse; width:100%;">';
        $body .= '<tr><th align="left">Учень</th><th>Оцінка</th></tr>';
        foreach ($rows as $r) {
            $stud = htmlspecialchars((string)($r['student'] ?? ''), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
            $gr   = $r['grade'];
            $gt   = is_null($gr) ? 'Не здав' : (string)(int)$gr;
            $body .= "<tr><td>{$stud}</td><td align=\"center\"><b>{$gt}</b></td></tr>";
        }
        $body .= '</table>';
        $body .= '<p>Це автоматичне повідомлення Електронного журналу.</p>';

        $alt = "Оцінки за урок: {$subjectName}\n";
        $alt .= "Урок: {$lessonTopic}\n";
        $alt .= "Дата: {$dateText}\n\n";
        foreach ($rows as $r) {
            $stud = (string)($r['student'] ?? '');
            $gr   = $r['grade'];
            $gt   = is_null($gr) ? 'Не здав' : (string)(int)$gr;
            $alt .= "{$stud}: {$gt}\n";
        }

        $mailer->clearAllRecipients();
        $mailer->addAddress($toEmail, $toName ?: $toEmail);
        $mailer->Subject = $title;
        $mailer->Body    = $body;
        $mailer->AltBody = $alt;
        $mailer->send();
    } catch (Throwable $e) {
        // мовчки
    }
}

/* ===== Перерахунок Т/С/Р (повний) ===== */
function ej_recompute_all_thematics(mysqli $conn, int $subject_id, array $students, array $lessons, array $grades, int &$recalc_counter): void {
    $thematic_positions = [];
    foreach ($lessons as $idx => $l) if ((int)$l['is_thematic'] === 1) $thematic_positions[] = $idx;
    if (!$thematic_positions) return;

    $upVal = $conn->prepare("
        INSERT INTO grades (subject_id, student_id, lesson_id, grade)
        VALUES (?,?,?,?)
        ON DUPLICATE KEY UPDATE grade=VALUES(grade)
    ");
    $upNull = $conn->prepare("
        INSERT INTO grades (subject_id, student_id, lesson_id, grade)
        VALUES (?,?,?,NULL)
        ON DUPLICATE KEY UPDATE grade=NULL
    ");
    if (!$upVal || !$upNull) return;

    foreach ($thematic_positions as $pos) {
        $prev = -1;
        for ($i = $pos - 1; $i >= 0; $i--) {
            if ((int)$lessons[$i]['is_thematic'] === 1) { $prev = $i; break; }
        }

        $segment = [];
        for ($i = $prev + 1; $i <= $pos - 1; $i++) {
            if ((int)$lessons[$i]['is_auto'] === 0) $segment[] = (int)$lessons[$i]['id'];
        }
        $den = count($segment);
        $them_lesson_id = (int)$lessons[$pos]['id'];

        foreach ($students as $sid) {
            $sum = 0;
            if ($den > 0) foreach ($segment as $lid) $sum += $grades[$lid][$sid] ?? 0;

            if ($den > 0 && $sum > 0) {
                $avg = (int)round($sum / $den);
                if ($avg < 1) $avg = 1;
                if ($avg > 12) $avg = 12;
                $upVal->bind_param('iiii', $subject_id, $sid, $them_lesson_id, $avg);
                $upVal->execute();
            } else {
                $upNull->bind_param('iii', $subject_id, $sid, $them_lesson_id);
                $upNull->execute();
            }
        }

        $recalc_counter++;
    }

    $upVal->close();
    $upNull->close();
}
function ej_recompute_all_semesters(mysqli $conn, int $subject_id, array $students, array $lessons, array $grades, int &$recalc_counter): void {
    $upVal = $conn->prepare("
        INSERT INTO grades (subject_id, student_id, lesson_id, grade)
        VALUES (?,?,?,?)
        ON DUPLICATE KEY UPDATE grade=VALUES(grade)
    ");
    $upNull = $conn->prepare("
        INSERT INTO grades (subject_id, student_id, lesson_id, grade)
        VALUES (?,?,?,NULL)
        ON DUPLICATE KEY UPDATE grade=NULL
    ");
    if (!$upVal || !$upNull) return;

    foreach ($lessons as $pos => $l) {
        if ((int)$l['is_semester'] !== 1) continue;

        $prevSem = -1;
        for ($i = $pos - 1; $i >= 0; $i--) {
            if ((int)$lessons[$i]['is_semester'] === 1) { $prevSem = $i; break; }
        }

        $thematic_ids = [];
        for ($i = $prevSem + 1; $i <= $pos - 1; $i++) {
            if ((int)$lessons[$i]['is_thematic'] === 1) $thematic_ids[] = (int)$lessons[$i]['id'];
        }
        $den = count($thematic_ids);
        $sem_lesson_id = (int)$l['id'];

        foreach ($students as $sid) {
            $sum = 0;
            if ($den > 0) foreach ($thematic_ids as $lid) $sum += $grades[$lid][$sid] ?? 0;

            if ($den > 0 && $sum > 0) {
                $avg = (int)round($sum / $den);
                if ($avg < 1) $avg = 1;
                if ($avg > 12) $avg = 12;
                $upVal->bind_param('iiii', $subject_id, $sid, $sem_lesson_id, $avg);
                $upVal->execute();
            } else {
                $upNull->bind_param('iii', $subject_id, $sid, $sem_lesson_id);
                $upNull->execute();
            }
        }

        $recalc_counter++;
    }

    $upVal->close();
    $upNull->close();
}
function ej_recompute_all_years(mysqli $conn, int $subject_id, array $students, array $lessons, array $grades, int &$recalc_counter): void {
    $upVal = $conn->prepare("
        INSERT INTO grades (subject_id, student_id, lesson_id, grade)
        VALUES (?,?,?,?)
        ON DUPLICATE KEY UPDATE grade=VALUES(grade)
    ");
    $upNull = $conn->prepare("
        INSERT INTO grades (subject_id, student_id, lesson_id, grade)
        VALUES (?,?,?,NULL)
        ON DUPLICATE KEY UPDATE grade=NULL
    ");
    if (!$upVal || !$upNull) return;

    foreach ($lessons as $pos => $l) {
        if ((int)$l['is_year'] !== 1) continue;

        $semester_ids = [];
        for ($i = 0; $i <= $pos - 1; $i++) if ((int)$lessons[$i]['is_semester'] === 1) $semester_ids[] = (int)$lessons[$i]['id'];

        $year_lesson_id = (int)$l['id'];

        if (!$semester_ids) {
            foreach ($students as $sid) {
                $upNull->bind_param('iii', $subject_id, $sid, $year_lesson_id);
                $upNull->execute();
            }
            $recalc_counter++;
            continue;
        }

        $sem1_id = (int)$semester_ids[0];
        $sem2_id = (int)($semester_ids[1] ?? 0);

        foreach ($students as $sid) {
            $s1 = $grades[$sem1_id][$sid] ?? 0;
            $s2 = ($sem2_id > 0) ? ($grades[$sem2_id][$sid] ?? 0) : 0;

            if ($s1 > 0) {
                if ($sem2_id > 0 && $s2 > 0) $val = (int)round(($s1 + $s2) / 2);
                else $val = (int)$s1;

                if ($val < 1) $val = 1;
                if ($val > 12) $val = 12;

                $upVal->bind_param('iiii', $subject_id, $sid, $year_lesson_id, $val);
                $upVal->execute();
            } else {
                $upNull->bind_param('iii', $subject_id, $sid, $year_lesson_id);
                $upNull->execute();
            }
        }

        $recalc_counter++;
    }

    $upVal->close();
    $upNull->close();
}

/* ===== Повний перерахунок (БЕЗ авто-нотифікацій) ===== */
function ej_full_recalc_only(
    mysqli $conn,
    int $subject_id,
    int $group_id
): array {
    $them_recalc = 0; $sem_recalc = 0; $year_recalc = 0;

    $students = ej_get_students_ids($conn, $group_id);
    if (!$students) return ['them'=>$them_recalc,'sem'=>$sem_recalc,'year'=>$year_recalc];

    $lessons = ej_get_lessons($conn, $subject_id);
    $lesson_ids = array_map(fn($x)=> (int)$x['id'], $lessons);
    $grades_map = ej_load_grades_map($conn, $subject_id, $lesson_ids);

    $conn->begin_transaction();
    try {
        ej_recompute_all_thematics($conn, $subject_id, $students, $lessons, $grades_map, $them_recalc);
        $grades_map = ej_load_grades_map($conn, $subject_id, $lesson_ids);

        ej_recompute_all_semesters($conn, $subject_id, $students, $lessons, $grades_map, $sem_recalc);
        $grades_map = ej_load_grades_map($conn, $subject_id, $lesson_ids);

        ej_recompute_all_years($conn, $subject_id, $students, $lessons, $grades_map, $year_recalc);

        $conn->commit();
    } catch (Throwable $e) {
        $conn->rollback();
    }

    return ['them'=>$them_recalc,'sem'=>$sem_recalc,'year'=>$year_recalc];
}

/* ===== Дії (POST) з PRG ===== */
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $csrf = $_POST['csrf_token'] ?? '';
    if (!hash_equals($CSRF, (string)$csrf)) {
        flash_set('error', 'Помилка безпеки (CSRF). Оновіть сторінку.');
        header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
        exit();
    }

    if (!$can_edit) {
        flash_set('error', 'Режим перегляду: редагування заборонено.');
        header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
        exit();
    }

    /* ===== Надіслати оцінки за урок ===== */
    if (isset($_POST['send_grades'])) {
        $lesson_id = (int)($_POST['lesson_id'] ?? 0);

        $ls = $conn->prepare("SELECT id, lesson_date, topic FROM lessons WHERE id=? AND subject_id=? LIMIT 1");
        if (!$ls) {
            flash_set('error', 'Помилка підготовки запиту (урок).');
            header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
            exit();
        }
        $ls->bind_param('ii', $lesson_id, $subject_id);
        $ls->execute();
        $lr = $ls->get_result();
        $lessonRow = $lr ? $lr->fetch_assoc() : null;
        $ls->close();

        if (!$lessonRow) {
            flash_set('error', 'Урок не знайдено.');
            header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
            exit();
        }

        $topic = (string)($lessonRow['topic'] ?? '');
        $lesson_date = $lessonRow['lesson_date'] ?? null;

        [$th,$sem,$yr] = parse_flags($topic);
        if ($th || $sem || $yr) {
            flash_set('error', 'Для тематичних/семестрових/річних уроків розсилку вимкнено.');
            header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
            exit();
        }

        // Вибір кому слати (варіант Б)
        $send_to_students = isset($_POST['send_to_students']) && (string)$_POST['send_to_students'] === '1';
        $send_to_parents  = isset($_POST['send_to_parents'])  && (string)$_POST['send_to_parents']  === '1';
        $send_to_curator  = isset($_POST['send_to_curator'])  && (string)$_POST['send_to_curator']  === '1';

        // Якщо раптом нічого не вибрали — не шлемо нікому
        if (!$send_to_students && !$send_to_parents && !$send_to_curator) {
            flash_set('error', 'Оберіть хоча б одного отримувача (учні / батьки / класний керівник).');
            header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
            exit();
        }

        // Підготуємо таблицю-лог (щоб "1–12" не дублювалось)
        ej_ensure_grade_notifications($conn);

        // учні групи
        $studentsRows = [];
        $st = $conn->prepare("SELECT id, full_name, email FROM users WHERE role='student' AND group_id=?");
        if ($st) {
            $st->bind_param('i', $group_id);
            $st->execute();
            $res = $st->get_result();
            while ($row = $res->fetch_assoc()) $studentsRows[] = $row;
            $st->close();
        }
        if (!$studentsRows) {
            flash_set('error', 'У групі немає учнів.');
            header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
            exit();
        }

        // ✅ ВАЖЛИВО: сортуємо так само, як у журналі (для класного керівника і для порядку розсилки)
        ej_sort_rows_by_full_name($studentsRows, 'full_name');

        // оцінки по уроку
        $gradesMap = []; // sid => ['has'=>bool,'grade'=>?int]
        $qg = $conn->prepare("SELECT student_id, grade FROM grades WHERE subject_id=? AND lesson_id=?");
        if ($qg) {
            $qg->bind_param('ii', $subject_id, $lesson_id);
            $qg->execute();
            $rg = $qg->get_result();
            while ($g = $rg->fetch_assoc()) {
                $sid = (int)$g['student_id'];
                $gradesMap[$sid] = [
                    'has' => true,
                    'grade' => is_null($g['grade']) ? null : (int)$g['grade']
                ];
            }
            $qg->close();
        }

        // класний керівник
        $curEmail = '';
        $curName  = '';
        if ($group_curator_id > 0) {
            $cu = $conn->prepare("SELECT email, full_name FROM users WHERE id=? LIMIT 1");
            if ($cu) {
                $cu->bind_param('i', $group_curator_id);
                $cu->execute();
                $cr = $cu->get_result();
                if ($cr && $row = $cr->fetch_assoc()) {
                    $curEmail = trim((string)($row['email'] ?? ''));
                    $curName  = (string)($row['full_name'] ?? '');
                }
                $cu->close();
            }
        }

        /* ===== Батьки: НОВЕ (parent_links) + fallback (users.child_id) ===== */
        $parent_links_ok = ej_has_table($conn, 'parent_links') &&
            ej_has_column($conn, 'parent_links', 'parent_id') &&
            ej_has_column($conn, 'parent_links', 'student_id');

        $parent_stmt_pl = null;
        $parent_stmt_child = null;

        if ($parent_links_ok) {
            $parent_stmt_pl = $conn->prepare("
                SELECT u.id, u.email, u.full_name
                FROM parent_links pl
                JOIN users u ON u.id = pl.parent_id
                WHERE pl.student_id = ?
            ");
        } elseif (ej_has_column($conn, 'users', 'child_id')) {
            $parent_stmt_child = $conn->prepare("
                SELECT id, email, full_name
                FROM users
                WHERE child_id = ?
            ");
        }

        $subjectName = (string)($subject['subject_name'] ?? 'Предмет');

        $sentStudents = 0;     // фактично відправлено учням (PM)
        $sentStudentsMail = 0; // фактично відправлено учням (email)
        $sentParents  = 0;     // email (як було)
        $sentParentsPM = 0;    // PM батькам
        $sentCurator  = 0;     // PM класному
        $sentCuratorMail = 0; // email класному

        $notifiedParents = []; // щоб не дублювати в межах одного натискання

        // Для класного керівника збираємо ТІЛЬКИ потрібні рядки (щоб не було зайвого спаму)
        $curatorRowsFiltered = [];

        foreach ($studentsRows as $stu) {
            $sid = (int)($stu['id'] ?? 0);
            if ($sid <= 0) continue;

            $studName  = (string)($stu['full_name'] ?? '');
            $studEmail = trim((string)($stu['email'] ?? ''));

            $hasRow = (bool)($gradesMap[$sid]['has'] ?? false);
            $grade  = $gradesMap[$sid]['grade'] ?? null;
            if (!$hasRow) $grade = null; // трактуємо як "Не здав"

            $isNotPassed = is_null($grade);

            /* === УЧЕНЬ === */
            $studentShouldNotify = false;
            if ($send_to_students) {
                if ($isNotPassed) {
                    $studentShouldNotify = true; // "Не здав" — кожен раз
                } else {
                    // 1–12 — тільки 1 раз на урок
                    $already = ej_was_notified_numeric($conn, $lesson_id, $sid, $sid, 'student');
                    $studentShouldNotify = !$already;
                }
            }

            if ($studentShouldNotify) {
                $pmSubject = "Оцінка за урок: {$subjectName}";
                $pmBody  = "Предмет: {$subjectName}\n";
                $pmBody .= "Урок: {$topic}\n";
                $pmBody .= "Дата: " . ($lesson_date ? date('d.m.Y', strtotime((string)$lesson_date)) : '—') . "\n";
                $pmBody .= "Оцінка: " . ($isNotPassed ? 'Не здав' : (string)(int)$grade) . "\n";

                ej_send_private_message($conn, $user_id, $sid, $pmSubject, $pmBody);
                $sentStudents++;

                if (!$isNotPassed) {
                    ej_mark_notified_numeric($conn, $lesson_id, $sid, $sid, 'student');
                }

                if ($MAIL_READY && $studEmail !== '') {
                    send_lesson_grade_email(
                        $MAIL_CFG,
                        $studEmail,
                        $studName,
                        $subjectName,
                        $grade,
                        is_null($lesson_date) ? null : (string)$lesson_date,
                        $topic,
                        $studName,
                        false
                    );
                    $sentStudentsMail++;
                }
            }

            /* === БАТЬКИ === */
            if ($send_to_parents) {
                $parents = [];
                if ($parent_stmt_pl) {
                    $parent_stmt_pl->bind_param('i', $sid);
                    if ($parent_stmt_pl->execute()) {
                        $pr = $parent_stmt_pl->get_result();
                        while ($p = $pr->fetch_assoc()) $parents[] = $p;
                    }
                } elseif ($parent_stmt_child) {
                    $parent_stmt_child->bind_param('i', $sid);
                    if ($parent_stmt_child->execute()) {
                        $pr = $parent_stmt_child->get_result();
                        while ($p = $pr->fetch_assoc()) $parents[] = $p;
                    }
                }

                foreach ($parents as $p) {
                    $pid   = (int)($p['id'] ?? 0);
                    $pEmail = trim((string)($p['email'] ?? ''));
                    $pName  = (string)($p['full_name'] ?? '');

                    if ($pid <= 0) continue;

                    // "Не здав" — кожен раз
                    // "1–12" — тільки 1 раз на урок для цього батька
                    $parentShouldNotify = false;
                    if ($isNotPassed) {
                        $parentShouldNotify = true;
                    } else {
                        $already = ej_was_notified_numeric($conn, $lesson_id, $sid, $pid, 'parent');
                        $parentShouldNotify = !$already;
                    }

                    if (!$parentShouldNotify) {
                        continue;
                    }

                    // PM — не дублюємо в межах одного натискання (на випадок дублів зв'язків)
                    $uniqPm = 'pm:' . $pid . ':' . $sid . ':' . (string)$lesson_id . ':' . ($isNotPassed ? 'NS' : 'NUM');
                    if (!isset($notifiedParents[$uniqPm])) {
                        $pSubj = "Оцінка дитини за урок: {$subjectName}";
                        $pBody  = "Дитина: {$studName}\n";
                        $pBody .= "Предмет: {$subjectName}\n";
                        $pBody .= "Урок: {$topic}\n";
                        $pBody .= "Дата: " . ($lesson_date ? date('d.m.Y', strtotime((string)$lesson_date)) : '—') . "\n";
                        $pBody .= "Оцінка: " . ($isNotPassed ? 'Не здав' : (string)(int)$grade) . "\n";

                        ej_send_private_message($conn, $user_id, $pid, $pSubj, $pBody);
                        $notifiedParents[$uniqPm] = true;
                        $sentParentsPM++;
                    }

                    // Email — теж не дублюємо в межах одного натискання
                    if ($MAIL_READY && $pEmail !== '') {
                        $uniqMail = 'mail:' . strtolower($pEmail) . ':' . $pid . ':' . $sid . ':' . (string)$lesson_id . ':' . ($isNotPassed ? 'NS' : 'NUM');
                        if (!isset($notifiedParents[$uniqMail])) {
                            send_lesson_grade_email(
                                $MAIL_CFG,
                                $pEmail,
                                $pName,
                                $subjectName,
                                $grade,
                                is_null($lesson_date) ? null : (string)$lesson_date,
                                $topic,
                                $studName,
                                true
                            );
                            $notifiedParents[$uniqMail] = true;
                            $sentParents++;
                        }
                    }

                    if (!$isNotPassed) {
                        ej_mark_notified_numeric($conn, $lesson_id, $sid, $pid, 'parent');
                    }
                }
            }

            /* === ДЛЯ КЛАСНОГО: тільки потрібні рядки === */
            if ($send_to_curator && $group_curator_id > 0 && $group_curator_id !== $user_id) {
                if ($isNotPassed) {
                    // "Не здав" — завжди
                    $curatorRowsFiltered[] = ['student' => $studName, 'grade' => $grade];
                } else {
                    // "1–12" — тільки 1 раз (на класного)
                    $alreadyCur = ej_was_notified_numeric($conn, $lesson_id, $sid, $group_curator_id, 'curator');
                    if (!$alreadyCur) {
                        $curatorRowsFiltered[] = ['student' => $studName, 'grade' => $grade];
                    }
                }
            }
        }

        if ($parent_stmt_pl) $parent_stmt_pl->close();
        if ($parent_stmt_child) $parent_stmt_child->close();

        // класному (PM + email) — тільки якщо є що слати
        if ($send_to_curator && $group_curator_id > 0 && $group_curator_id !== $user_id) {
            if (!empty($curatorRowsFiltered)) {
                $pmSubject = "Оцінки за урок: {$subjectName}";
                $pmBody  = "Предмет: {$subjectName}\n";
                $pmBody .= "Урок: {$topic}\n";
                $pmBody .= "Дата: " . ($lesson_date ? date('d.m.Y', strtotime((string)$lesson_date)) : '—') . "\n\n";
                $pmBody .= "Оцінки (оновлення / не здав):\n";
                foreach ($curatorRowsFiltered as $r) {
                    $pmBody .= "- " . ($r['student'] ?? '') . ": " . (is_null($r['grade']) ? 'Не здав' : (string)(int)$r['grade']) . "\n";
                }

                ej_send_private_message($conn, $user_id, $group_curator_id, $pmSubject, $pmBody);
                $sentCurator++;

                if ($MAIL_READY && $curEmail !== '') {
                    send_curator_lesson_email(
                        $MAIL_CFG,
                        $curEmail,
                        $curName,
                        $subjectName,
                        $topic,
                        is_null($lesson_date) ? null : (string)$lesson_date,
                        $curatorRowsFiltered
                    );
                    $sentCuratorMail++;
                }

                // Позначення "curator NUM" робимо нижче у другому проході
            }
        }

        /**
         * Позначення "curator NUM" треба зробити під час формування $curatorRowsFiltered,
         * тому робимо другий прохід по студентам тільки для маркування (легкий, але надійний).
         */
        if ($send_to_curator && $group_curator_id > 0 && $group_curator_id !== $user_id && !empty($curatorRowsFiltered) && ej_has_table($conn, 'grade_notifications')) {
            // будуємо швидкий set по іменам + grade щоб маркувати лише тих, кого додали у фільтр
            $set = [];
            foreach ($curatorRowsFiltered as $r) {
                $key = (string)($r['student'] ?? '') . '|' . (is_null($r['grade']) ? 'NS' : (string)(int)$r['grade']);
                $set[$key] = true;
            }

            foreach ($studentsRows as $stu) {
                $sid = (int)($stu['id'] ?? 0);
                if ($sid <= 0) continue;

                $studName  = (string)($stu['full_name'] ?? '');
                $hasRow = (bool)($gradesMap[$sid]['has'] ?? false);
                $grade  = $gradesMap[$sid]['grade'] ?? null;
                if (!$hasRow) $grade = null;

                if (!is_null($grade)) {
                    $key = $studName . '|' . (string)(int)$grade;
                    if (isset($set[$key])) {
                        ej_mark_notified_numeric($conn, $lesson_id, $sid, $group_curator_id, 'curator');
                    }
                }
            }
        }

        $who = [];
        if ($send_to_students) $who[] = 'учням';
        if ($send_to_parents)  $who[] = 'батькам';
        if ($send_to_curator)  $who[] = 'класному керівнику';
        $whoTxt = $who ? implode(', ', $who) : '—';

        flash_set(
            'success',
            "Розсилку виконано ({$whoTxt}). " .
            "PM учням: {$sentStudents}, Email учням: {$sentStudentsMail}, " .
            "PM батькам: {$sentParentsPM}, Email батькам: {$sentParents}, " .
            "PM класному: {$sentCurator}, Email класному: {$sentCuratorMail}."
        );
        header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
        exit();
    }

    // Додати урок
    if (isset($_POST['add_lesson'])) {
        $lesson_date = trim($_POST['lesson_date'] ?? '');
        $topic = trim($_POST['topic'] ?? '');
        $is_thematic_checked = isset($_POST['is_thematic']);
        $is_semester_checked = isset($_POST['is_semester']);
        $is_year_checked     = isset($_POST['is_year']);

        $is_date_ok = false;
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $lesson_date)) {
            [$y,$m,$d] = array_map('intval', explode('-', $lesson_date));
            $is_date_ok = checkdate($m, $d, $y);
        }
        if (mb_strlen($topic) > 255) $topic = mb_substr($topic, 0, 255);

        if ($is_thematic_checked) $topic = with_prefix($topic, 'T');
        if ($is_semester_checked) $topic = with_prefix($topic, 'S');
        if ($is_year_checked)     $topic = with_prefix($topic, 'Y');

        if ($is_date_ok && $topic !== '') {
            $q = $conn->prepare("INSERT INTO lessons (subject_id, lesson_date, topic) VALUES (?,?,?)");
            $q->bind_param('iss', $subject_id, $lesson_date, $topic);
            if ($q->execute()) {
                $new_lesson_id = (int)$q->insert_id;
                $q->close();

                // ПІСЛЯ створення звичайного уроку — усім учням проставляємо "Не здав" (NULL)
                [$isThNew, $isSemNew, $isYrNew] = parse_flags($topic);
                $isAutoNew = ($isThNew || $isSemNew || $isYrNew);

                if (!$isAutoNew && $new_lesson_id > 0) {
                    $studentIds = ej_get_students_ids($conn, $group_id);
                    if (!empty($studentIds)) {
                        $ins = $conn->prepare("
                            INSERT INTO grades (subject_id, student_id, lesson_id, grade)
                            VALUES (?,?,?,NULL)
                            ON DUPLICATE KEY UPDATE grade=grade
                        ");
                        if ($ins) {
                            foreach ($studentIds as $sid) {
                                $sid = (int)$sid;
                                $ins->bind_param('iii', $subject_id, $sid, $new_lesson_id);
                                $ins->execute();
                            }
                            $ins->close();
                        }
                    }
                }

                // ПІСЛЯ додавання будь-якого уроку — повний перерахунок (БЕЗ нотифікацій)
                $res = ej_full_recalc_only($conn, $subject_id, $group_id);

                $msg = "Урок успішно доданий. Перерахунок: Т={$res['them']}, С={$res['sem']}, Р={$res['year']}.";
                flash_set('success', $msg);
            } else {
                $q->close();
                flash_set('error','Помилка при додаванні уроку.');
            }
        } else {
            flash_set('error','Дата та тема уроку є обов’язковими. Перевірте формат дати.');
        }
        header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
        exit();
    }

    // Редагувати урок
    if (isset($_POST['edit_lesson'])) {
        $lesson_id = (int)($_POST['lesson_id'] ?? 0);
        $new_date  = trim($_POST['edit_lesson_date'] ?? '');
        $new_topic = trim($_POST['edit_topic'] ?? '');

        $is_date_ok = false;
        if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $new_date)) {
            [$y,$m,$d] = array_map('intval', explode('-', $new_date));
            $is_date_ok = checkdate($m, $d, $y);
        }
        if (mb_strlen($new_topic) > 255) $new_topic = mb_substr($new_topic, 0, 255);

        if ($lesson_id > 0 && $is_date_ok && $new_topic !== '') {
            $q = $conn->prepare("UPDATE lessons SET lesson_date=?, topic=? WHERE id=? AND subject_id=?");
            $q->bind_param('ssii', $new_date, $new_topic, $lesson_id, $subject_id);
            if ($q->execute()) {
                $q->close();

                $res = ej_full_recalc_only($conn, $subject_id, $group_id);
                flash_set('success', "Урок оновлено. Перерахунок: Т={$res['them']}, С={$res['sem']}, Р={$res['year']}.");
            } else {
                $q->close();
                flash_set('error','Не вдалося оновити урок.');
            }
        } else {
            flash_set('error','Укажіть коректні дату і тему.');
        }
        $li = isset($_GET['lesson_idx']) ? (int)$_GET['lesson_idx'] : 0;
        header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id) . "&lesson_idx=" . urlencode((string)$li));
        exit();
    }

    // Видалити урок (+ оцінки)
    if (isset($_POST['delete_lesson'])) {
        $lesson_id = (int)($_POST['lesson_id'] ?? 0);
        if ($lesson_id > 0) {
            $conn->begin_transaction();
            try {
                $d1 = $conn->prepare("DELETE FROM grades WHERE lesson_id=? AND subject_id=?");
                $d1->bind_param('ii', $lesson_id, $subject_id); $ok1 = $d1->execute(); $d1->close();

                $d2 = $conn->prepare("DELETE FROM lessons WHERE id=? AND subject_id=?");
                $d2->bind_param('ii', $lesson_id, $subject_id); $ok2 = $d2->execute(); $d2->close();

                if ($ok1 && $ok2) {
                    $conn->commit();

                    $res = ej_full_recalc_only($conn, $subject_id, $group_id);
                    flash_set('success',"Урок і пов’язані оцінки видалено. Перерахунок: Т={$res['them']}, С={$res['sem']}, Р={$res['year']}.");
                } else {
                    $conn->rollback();
                    flash_set('error','Не вдалося видалити урок.');
                }
            } catch (Throwable $e) {
                $conn->rollback();
                flash_set('error','Помилка при видаленні уроку.');
            }
        } else {
            flash_set('error','Невірний ID уроку.');
        }
        header("Location: subject_journal.php?subject_id=" . urlencode((string)$subject_id));
        exit();
    }
}

/* ===== Дані для відображення ===== */
$students = [];
$st = $conn->prepare("SELECT id, full_name FROM users WHERE role='student' AND group_id=?");
$st->bind_param('i', $group_id);
$st->execute();
$r = $st->get_result();
while ($row = $r->fetch_assoc()) $students[] = $row;
$st->close();

/* ✅ Сортуємо ПІБ (однаково для таблиці і листів) */
ej_sort_rows_by_full_name($students, 'full_name');

/* Беремо уроки */
$lessons = [];
$ql = $conn->prepare("SELECT id, lesson_date, topic FROM lessons WHERE subject_id=? ORDER BY lesson_date ASC, id ASC");
$ql->bind_param('i', $subject_id);
$ql->execute();
$rl = $ql->get_result();
while ($x = $rl->fetch_assoc()) {
    [$th,$sem,$yr] = parse_flags($x['topic'] ?? '');
    $x['is_thematic'] = $th; $x['is_semester'] = $sem; $x['is_year'] = $yr;
    $lessons[] = $x;
}
$ql->close();

$total_lessons = count($lessons);

if ($total_lessons > 0) {
    $default_idx = $total_lessons - 1;
    $lesson_idx = isset($_GET['lesson_idx']) ? (int)$_GET['lesson_idx'] : $default_idx;
    if ($lesson_idx < 0) $lesson_idx = 0;
    if ($lesson_idx > $total_lessons - 1) $lesson_idx = $total_lessons - 1;
    $current_lesson = $lessons[$lesson_idx];
    $current_lesson_id = (int)$current_lesson['id'];
} else {
    $lesson_idx = -1; $current_lesson = null; $current_lesson_id = 0;
}

/*
  Відрізняємо:
  - "немає рядка в grades"  -> показуємо "—"
  - "рядок є, grade=NULL"   -> показуємо "Не здав"
*/
$grades_by_student = []; // [sid] => ['has'=>bool,'grade'=>?int]
if ($current_lesson_id > 0) {
    $qg = $conn->prepare("SELECT student_id, grade FROM grades WHERE subject_id=? AND lesson_id=?");
    $qg->bind_param('ii', $subject_id, $current_lesson_id);
    $qg->execute();
    $rg = $qg->get_result();
    while ($g = $rg->fetch_assoc()) {
        $sid = (int)$g['student_id'];
        $grades_by_student[$sid] = [
            'has'   => true,
            'grade' => is_null($g['grade']) ? null : (int)$g['grade'],
        ];
    }
    $qg->close();
}

$error   = flash_get('error');
$success = flash_get('success');

function build_lesson_url(int $subject_id, int $lesson_idx): string {
    return "subject_journal.php?subject_id={$subject_id}&lesson_idx={$lesson_idx}";
}

/* Поточна сторінка */
$current_file = basename($_SERVER['SCRIPT_NAME']); // subject_journal.php
?>
<!DOCTYPE html>
<html lang="uk">
<head>
    <meta charset="UTF-8">
    <meta name="robots" content="noindex, nofollow">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title><?= h($subject['subject_name']); ?> - Журнал</title>

    <link rel="stylesheet" href="../assets/css/style.css">
    <link rel="stylesheet" href="../assets/css/subject_journal.css">

    <?php if ($can_edit): ?>
    <script>
        let gradeChanges = {};
        let initialGrade = {};

        function initGradeTracking() {
            const selects = document.querySelectorAll('select.grade');
            selects.forEach(select => {
                const studentId = select.getAttribute('data-student-id');
                const lessonId  = select.getAttribute('data-lesson-id');
                const key = `${studentId}_${lessonId}`;
                const initialValue = select.value || '-';
                initialGrade[key] = initialValue;
                gradeChanges[key] = initialValue;

                select.addEventListener('change', function() {
                    const newGrade = this.value === '' ? '-' : this.value;
                    gradeChanges[key] = newGrade;
                });
            });
        }

        async function saveGrades(btn) {
            const changed = Object.entries(gradeChanges).filter(([k, v]) => v !== initialGrade[k]);
            if (changed.length === 0) { alert('Немає змін для збереження!'); return; }

            const data = new URLSearchParams();
            data.append('subject_id', '<?= (int)$subject_id; ?>');
            data.append('csrf_token', '<?= h($CSRF); ?>');
            for (const [k, v] of changed) data.append(k, v);

            const originalText = btn.textContent;
            btn.disabled = true; btn.textContent = 'Збереження...';

            try {
                const resp = await fetch('update_grades.php', {
                    method: 'POST',
                    headers: {'Content-Type': 'application/x-www-form-urlencoded'},
                    credentials: 'include',
                    body: data
                });
                const raw = await resp.text();
                let payload;
                try { payload = JSON.parse(raw); } catch(e) { throw new Error('Невірний формат відповіді: ' + raw); }
                if (payload.status === 'success') {
                    alert(
                        `Готово!\n` +
                        `Додано: ${payload.added ?? 0}\n` +
                        `Оновлено: ${payload.updated ?? 0}\n` +
                        `Без змін: ${payload.unchanged ?? 0}\n` +
                        `Переобчислено тематичних: ${payload.them_recalc ?? 0}\n` +
                        `Переобчислено семестрових: ${payload.sem_recalc ?? 0}\n` +
                        `Переобчислено річних: ${payload.year_recalc ?? 0}\n\n` +
                        `УВАГА: листи не надсилаються при збереженні. Використовуйте "Надіслати оцінки".`
                    );
                    location.reload();
                } else {
                    throw new Error(payload.message || 'Невідома помилка');
                }
            } catch (e) {
                console.error(e);
                alert('Помилка при збереженні оцінок: ' + e.message);
            } finally {
                btn.disabled = false; btn.textContent = originalText;
            }
        }

        function openEditModal(lessonId, dateStr, topic) {
            const backdrop = document.getElementById('editModalBackdrop');
            document.getElementById('edit_lesson_id').value = lessonId;
            document.getElementById('edit_lesson_date').value = dateStr;
            document.getElementById('edit_topic').value = topic;
            backdrop.style.display = 'flex';
        }
        function closeEditModal(){ document.getElementById('editModalBackdrop').style.display='none'; }
        window.addEventListener('keydown',(e)=>{ if(e.key==='Escape') closeEditModal(); });

        function confirmSendGrades(form){
            const chkStudents = form.querySelector('input[name="send_to_students"]');
            const chkParents  = form.querySelector('input[name="send_to_parents"]');
            const chkCurator  = form.querySelector('input[name="send_to_curator"]');

            const to = [];
            if (chkStudents && chkStudents.checked) to.push('учням');
            if (chkParents && chkParents.checked) to.push('батькам');
            if (chkCurator && chkCurator.checked) to.push('класному керівнику');

            if (to.length === 0) {
                alert('Оберіть хоча б одного отримувача (учні / батьки / класний керівник).');
                return false;
            }
            return confirm('Надіслати оцінки за цей урок: ' + to.join(', ') + ' ?\n\nПравило: 1–12 — лише один раз; "Не здав" — кожного разу.');
        }

        window.addEventListener('DOMContentLoaded', initGradeTracking);
    </script>
    <?php endif; ?>
</head>
<body>

<?php require __DIR__ . '/../includes/header.php'; ?>

<div class="container">
    <h1><?= h($subject['subject_name']); ?> (Група: <?= h($subject['group_name']); ?>)</h1>

    <?php if (!$can_edit): ?>
        <div class="ro-banner">
            Режим перегляду: ви відкрили предмет не як викладач цього предмету (наприклад, як класний керівник). Редагування вимкнено.
        </div>
    <?php endif; ?>

    <div class="back-journal">
        <a href="view_journal.php?journal_id=<?= (int)$group_id; ?>">&larr; Назад до журналу групи</a>
    </div>
    <p class="muted">
        Матриця без горизонтальної прокрутки: поруч із ПІБ — лише один обраний урок. Перемикай кнопками нижче.
    </p>

    <?php if ($error): ?><p class="error" style="color:#b00020"><?= h($error); ?></p><?php endif; ?>
    <?php if ($success): ?><p class="success" style="color:#0a7c2f"><?= h($success); ?></p><?php endif; ?>

    <div class="info-row">
        <div>
            <span class="badge">Уроків загалом: <?= $total_lessons; ?></span>
            <?php if ($total_lessons>0): ?>
                <span class="badge">Показано: <?= $lesson_idx+1; ?> / <?= $total_lessons; ?></span>
            <?php endif; ?>
        </div>
        <div class="nav-lessons">
            <?php
                $prev_idx = max(0, $lesson_idx - 1);
                $next_idx = min(max(0,$total_lessons-1), $lesson_idx + 1);
            ?>
            <a class="<?= ($lesson_idx<=0 || $total_lessons===0)?'disabled':''; ?>"
               href="<?= h(build_lesson_url($subject_id, $prev_idx)); ?>">← Попередній урок</a>
            <a class="<?= ($lesson_idx >= $total_lessons-1 || $total_lessons===0)?'disabled':''; ?>"
               href="<?= h(build_lesson_url($subject_id, $next_idx)); ?>">Наступний урок →</a>
        </div>
    </div>

    <table>
        <tr class="tbl-head">
            <th class="col-num">№</th>
            <th class="col-name">ПІБ учня</th>
            <th>
                <?php if ($current_lesson): ?>
                    <?php
                        $ltopic = h($current_lesson['topic']);
                        $ldate  = date('Y-m-d', strtotime($current_lesson['lesson_date']));
                        $ldateHuman = date('d.m.Y', strtotime($current_lesson['lesson_date']));
                        $isTh = (int)($current_lesson['is_thematic'] ?? 0);
                        $isSem = (int)($current_lesson['is_semester'] ?? 0);
                        $isYr = (int)($current_lesson['is_year'] ?? 0);
                    ?>
                    <div class="topic-compact" title="<?= $ltopic; ?>"><?= $ltopic; ?>
                        <?php if ($isTh): ?><span class="badge-green">Тематична</span><?php endif; ?>
                        <?php if ($isSem): ?><span class="badge-purple">Семестрова</span><?php endif; ?>
                        <?php if ($isYr): ?><span class="badge-red">Річна</span><?php endif; ?>
                    </div>
                    <div class="lesson-date"><?= $ldateHuman; ?></div>

                    <?php if ($can_edit): ?>
                    <div class="lesson-actions">
                        <button type="button" class="icon-btn"
                                onclick="openEditModal('<?= (int)$current_lesson['id']; ?>','<?= h($ldate); ?>','<?= $ltopic; ?>')">
                            ✎
                        </button>
                    </div>
                    <?php endif; ?>

                <?php else: ?>
                    <div class="topic-compact">Уроки відсутні</div>
                <?php endif; ?>
            </th>
        </tr>

        <?php $i=1; foreach ($students as $stu): ?>
            <tr>
                <td class="col-num"><span class="num"><?= $i++; ?></span></td>
                <td class="col-name"><span class="student-name"><?= h($stu['full_name']); ?></span></td>
                <td>
                    <?php if ($current_lesson): ?>
                        <?php
                            $sid = (int)$stu['id'];
                            $lid = $current_lesson_id;

                            $hasRow = (bool)($grades_by_student[$sid]['has'] ?? false);
                            $val    = $grades_by_student[$sid]['grade'] ?? null;

                            $isAutoLesson =
                                ((int)($current_lesson['is_thematic'] ?? 0) === 1) ||
                                ((int)($current_lesson['is_semester'] ?? 0) === 1) ||
                                ((int)($current_lesson['is_year'] ?? 0) === 1);

                            $isReadOnlyCell = !$can_edit || $isAutoLesson;
                        ?>
                        <?php if ($isReadOnlyCell): ?>
                            <?php if ($isAutoLesson): ?>
                                <span class="auto-grade"><?= is_null($val) ? '—' : (int)$val; ?></span>
                            <?php else: ?>
                                <span class="auto-grade">
                                    <?php
                                        if (!$hasRow) echo '—';
                                        elseif (is_null($val)) echo 'Не здав';
                                        else echo (int)$val;
                                    ?>
                                </span>
                            <?php endif; ?>
                        <?php else: ?>
                            <select class="grade grade-select" data-student-id="<?= $sid; ?>" data-lesson-id="<?= $lid; ?>">
                                <option value=""><?= $hasRow ? 'Не здав' : '-'; ?></option>
                                <?php for ($g=1; $g<=12; $g++): ?>
                                    <option value="<?= $g; ?>" <?= ($val !== null && (int)$val === $g) ? 'selected' : ''; ?>><?= $g; ?></option>
                                <?php endfor; ?>
                            </select>
                        <?php endif; ?>
                    <?php else: ?>
                        —
                    <?php endif; ?>
                </td>
            </tr>
        <?php endforeach; ?>

        <?php
            $showSave =
                $can_edit &&
                $current_lesson &&
                ((int)($current_lesson['is_thematic'] ?? 0) !== 1) &&
                ((int)($current_lesson['is_semester'] ?? 0) !== 1) &&
                ((int)($current_lesson['is_year'] ?? 0) !== 1);
        ?>
        <?php if ($showSave): ?>
        <tr>
            <td colspan="3" class="save-button">
                <div class="btn-row">
                    <button type="button" onclick="saveGrades(this)">Зберегти зміни</button>

                    <form method="POST" action="" onsubmit="return confirmSendGrades(this)" style="margin:0;">
                        <input type="hidden" name="csrf_token" value="<?= h($CSRF); ?>">
                        <input type="hidden" name="lesson_id" value="<?= (int)$current_lesson_id; ?>">

                        <div class="send-options">
                            <label class="chk">
                                <input type="checkbox" name="send_to_students" value="1" checked>
                                Учні
                            </label>
                            <label class="chk">
                                <input type="checkbox" name="send_to_parents" value="1" checked>
                                Батьки
                            </label>
                            <label class="chk">
                                <input type="checkbox" name="send_to_curator" value="1" checked>
                                Класний керівник
                            </label>
                        </div>

                        <button type="submit" name="send_grades" class="btn-send">Надіслати оцінки</button>
                    </form>
                </div>
            </td>
        </tr>
        <?php endif; ?>
    </table>

    <?php if ($can_edit): ?>
    <!-- Додати урок -->
    <div class="add-lesson" style="margin-top:18px">
        <h2>Додати урок</h2>
        <form method="POST" action="">
            <input type="hidden" name="csrf_token" value="<?= h($CSRF); ?>">
            <div style="display:flex;flex-wrap:wrap;gap:12px;align-items:flex-end">
                <div>
                    <label for="lesson_date">Дата уроку:</label><br>
                    <input type="date" name="lesson_date" id="lesson_date" required>
                </div>
                <div style="min-width:260px;flex:1">
                    <label for="topic">Тема уроку:</label><br>
                    <input type="text" name="topic" id="topic" maxlength="255" required style="width:100%">
                </div>
                <div class="opts-row">

                    <label class="chk">
                        <input type="checkbox" name="is_thematic" value="1">
                        Тематичне оцінювання (авто-розрахунок)
                    </label>
                    <label class="chk">
                        <input type="checkbox" name="is_semester" value="1">
                        Семестрове оцінювання (авто-розрахунок)
                    </label>
                    <label class="chk">
                        <input type="checkbox" name="is_year" value="1">
                        Річне оцінювання (авто-розрахунок)
                    </label>
                </div>
                <div>
                    <button type="submit" name="add_lesson">Додати урок</button>
                </div>
            </div>
        </form>
    </div>

    <!-- Видалити урок -->
    <div class="delete-lesson" style="margin-top:12px">
        <h2>Видалити урок</h2>
        <form method="POST" action="" onsubmit="return confirm('Видалити обраний урок та всі його оцінки?');">
            <input type="hidden" name="csrf_token" value="<?= h($CSRF); ?>">
            <div style="display:flex;flex-wrap:wrap;gap:12px;align-items:flex-end">
                <div style="min-width:280px;flex:1">
                    <label for="lesson_id">Оберіть урок для видалення:</label><br>
                    <select name="lesson_id" id="lesson_id" required style="width:100%">
                        <?php foreach ($lessons as $ls): ?>
                            <?php [$th,$sem,$yr] = parse_flags($ls['topic'] ?? ''); ?>
                            <option value="<?= (int)$ls['id']; ?>">
                                <?= h($ls['topic']); ?>
                                (<?= date('d.m.Y', strtotime($ls['lesson_date'])); ?>)
                                <?php if ((int)$th===1): ?> — тематична<?php endif; ?>
                                <?php if ((int)$sem===1): ?> — семестрова<?php endif; ?>
                                <?php if ((int)$yr===1): ?> — річна<?php endif; ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div>
                    <button type="submit" name="delete_lesson">Видалити урок</button>
                </div>
            </div>
        </form>
    </div>
    <?php endif; ?>
</div>

<?php if ($can_edit): ?>
<!-- Модалка редагування уроку -->
<div id="editModalBackdrop" class="modal-backdrop" onclick="if(event.target===this)closeEditModal()">
    <div class="modal" role="dialog" aria-modal="true">
        <header>Редагувати урок</header>
        <form method="POST" action="">
            <input type="hidden" name="csrf_token" value="<?= h($CSRF); ?>">
            <input type="hidden" id="edit_lesson_id" name="lesson_id" value="">
            <div class="field">
                <label for="edit_lesson_date">Дата уроку</label>
                <input type="date" id="edit_lesson_date" name="edit_lesson_date" required>
            </div>
            <div class="field">
                <label for="edit_topic">Тема уроку</label>
                <input type="text" id="edit_topic" name="edit_topic" maxlength="255" required>
            </div>
            <div class="actions">
                <button type="button" class="btn-outline" onclick="closeEditModal()">Скасувати</button>
                <button type="submit" name="edit_lesson">Зберегти</button>
            </div>
        </form>
    </div>
</div>
<?php endif; ?>

</body>
</html>
