<?php
session_name('super_admin_session');
if (!is_dir(dirname(__DIR__) . '/session_data/super')) { mkdir(dirname(__DIR__) . '/session_data/super', 0700, true); } session_save_path(dirname(__DIR__) . '/session_data/super');
session_set_cookie_params(['path' => '/', 'httponly' => true, 'samesite' => 'Strict']);
session_start();
error_reporting(0);
include('includes/dbconnection.php');
require_once __DIR__ . '/includes/session_term_helper.php';
// Fetch current session and term (with gap/fallback support)
$stResult = resolveCurrentSessionTerm($con);
$currentSessionId   = $stResult['session_id'];
$currentSessionName = $stResult['session_name'];
$currentTermId      = $stResult['term_id'];
$currentTermName    = $stResult['term_name'];

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
require 'phpmailer/src/Exception.php';
require 'phpmailer/src/PHPMailer.php';
require 'phpmailer/src/SMTP.php';
require_once 'vendor/autoload.php';

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();
// ----------------------
// Handle notification trigger
// ----------------------
$notification_sent = false;
if (isset($_POST['notify_unsubmitted_teachers'])) {
    $term = $_POST['term'];
    $session = $_POST['session'];

    // Resolve IDs from names for dual-match
    $termIdRow = mysqli_fetch_row(mysqli_query($con, "SELECT id FROM school_terms WHERE term_name = '" . mysqli_real_escape_string($con, $term) . "' LIMIT 1"));
    $termId = $termIdRow[0] ?? $term;
    $sessionIdRow = mysqli_fetch_row(mysqli_query($con, "SELECT id FROM school_sessions WHERE session_name = '" . mysqli_real_escape_string($con, $session) . "' LIMIT 1"));
    $sessionId = $sessionIdRow[0] ?? $session;

    $query = "
        SELECT 
            u.email, 
            u.name, 
            u.lastname, 
            cs.class, 
            cs.section,
            COUNT(DISTINCT s.id) AS total_students,
            COUNT(DISTINCT CASE WHEN p.id IS NOT NULL AND a.id IS NOT NULL THEN s.id END) AS submitted_count
        FROM teacher_assignments ta
        JOIN tblusers u ON ta.teacher_id = u.id
        JOIN class_section cs ON ta.class_id = cs.id
        JOIN students s ON s.class = cs.class AND s.section = cs.section AND s.status < 3
        LEFT JOIN psychomotor p ON p.student_id = s.id 
            AND (TRIM(p.term) = ? OR TRIM(p.term) = ?)
            AND (TRIM(p.session) = ? OR TRIM(p.session) = ?)
        LEFT JOIN affective a ON a.student_id = s.id 
            AND (TRIM(a.term) = ? OR TRIM(a.term) = ?)
            AND (TRIM(a.session) = ? OR TRIM(a.session) = ?)
        WHERE ta.is_class_teacher = 1 AND u.status = 2 AND u.permission = 'Teacher'
        GROUP BY u.id, cs.class, cs.section
        HAVING submitted_count < total_students
    ";
    $stmt = $con->prepare($query);
    $stmt->bind_param("ssssssss", $term, $termId, $session, $sessionId, $term, $termId, $session, $sessionId);
    $stmt->execute();
    $result = $stmt->get_result();

    while ($row = $result->fetch_assoc()) {
        $teacherName = $row['lastname'].' '.$row['name'];
        $email = $row['email'];
        $class = $row['class'];
        $section = $row['section'];

        $mail = new PHPMailer(true);
try {
    $mail->isSMTP();
    $mail->Host = $_ENV['SMTP_HOST'];
    $mail->SMTPAuth = true;
    $mail->Username = $_ENV['SMTP_NO_REPLY'];
    $mail->Password = $_ENV['SMTP_NO_REPLY_PASSWORD'];
    $mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS;
    $mail->Port = (int) $_ENV['SMTP_PORT'];
    $mail->Timeout = 10;

    $mail->setFrom($_ENV['SMTP_NO_REPLY'], $_ENV['SCHOOL_NAME']);
    $mail->addAddress($email, $teacherName);

   $mail->isHTML(true);
            $mail->Subject = 'Reminder: Ratings Not Fully Submitted';
            $mail->Body = "
                Dear <strong>$teacherName</strong>,<br><br>
                This is a reminder that you have <b style='color:red;'>not fully submitted ratings</b> for 
                <strong>$class - $section</strong> as of today (" . date('d-m-Y') . ").<br>
                Please complete both <b>psychomotor</b> and <b>affective</b> domains.<br><br>
                Regards,<br>School Admin
            ";
            $mail->send();
} catch (Exception $e) {
            error_log("Email failed for $email: {$mail->ErrorInfo}");
}

    }
    $notification_sent = true;
}

// ----------------------
// Current term & session
// ----------------------
$currentSessionQuery = "
    SELECT id, session_name 
    FROM school_sessions 
    WHERE start_date <= CURDATE() 
      AND end_date >= CURDATE() 
    ORDER BY start_date ASC 
    LIMIT 1";
$currentSessionResult = $con->query($currentSessionQuery);
$currentSession = $currentSessionResult ? $currentSessionResult->fetch_assoc() : null;
$currentSessionId = $currentSession['id'] ?? null;
$currentSessionName = $currentSession['session_name'] ?? null;

$termQuery = "SELECT id, term_name FROM school_terms WHERE session_id = '$currentSessionId'
              AND start_date <= CURDATE() 
              AND end_date >= CURDATE() 
              ORDER BY start_date ASC 
              LIMIT 1";
$termRow = mysqli_fetch_assoc(mysqli_query($con, $termQuery));
$currentTermId = $termRow['id'] ?? null;
$currentTermName = $termRow['term_name'] ?? null;

// Helper macro: match term/session by both name and ID (handles inconsistent stored values)
$termMatch    = "(TRIM(p.term)    = '$currentTermName'    OR TRIM(p.term)    = '$currentTermId')";
$sessionMatch = "(TRIM(p.session) = '$currentSessionName' OR TRIM(p.session) = '$currentSessionId')";
$termMatchA    = "(TRIM(a.term)    = '$currentTermName'    OR TRIM(a.term)    = '$currentTermId')";
$sessionMatchA = "(TRIM(a.session) = '$currentSessionName' OR TRIM(a.session) = '$currentSessionId')";

// ----------------------
// Daily ratings status per teacher
// ----------------------
$teacher_ratings_status = mysqli_query($con, "
    SELECT 
        ta.teacher_id,
        CONCAT(u.lastname,' ',u.name) AS teacher_name,
        cs.class,
        cs.section,
        COUNT(DISTINCT s.id) AS total_students,
        COUNT(DISTINCT CASE WHEN p.id IS NOT NULL AND a.id IS NOT NULL THEN s.id END) AS fully_submitted_count,
        COUNT(DISTINCT CASE WHEN p.id IS NULL THEN s.id END) AS missing_psychomotor,
        COUNT(DISTINCT CASE WHEN a.id IS NULL THEN s.id END) AS missing_affective,
        MAX(GREATEST(
            IFNULL(p.created_at, '1970-01-01 00:00:00'),
            IFNULL(a.created_at, '1970-01-01 00:00:00')
        )) AS last_submission_time
    FROM teacher_assignments ta
    JOIN tblusers u ON ta.teacher_id = u.id
    JOIN class_section cs ON ta.class_id = cs.id
    JOIN students s ON s.class = cs.class AND s.section = cs.section AND s.status < 3
    LEFT JOIN psychomotor p ON p.student_id = s.id 
        AND (TRIM(p.term) = '$currentTermName' OR TRIM(p.term) = '$currentTermId')
        AND (TRIM(p.session) = '$currentSessionName' OR TRIM(p.session) = '$currentSessionId')
    LEFT JOIN affective a ON a.student_id = s.id 
        AND (TRIM(a.term) = '$currentTermName' OR TRIM(a.term) = '$currentTermId')
        AND (TRIM(a.session) = '$currentSessionName' OR TRIM(a.session) = '$currentSessionId')
    WHERE ta.is_class_teacher = 1 AND u.status = 2 AND u.permission = 'Teacher'
    GROUP BY ta.teacher_id, cs.class, cs.section
    ORDER BY cs.class, cs.section
");

// ----------------------
// Weekly ratings chart (only fully completed submissions)
// ----------------------
$weekly_ratings = mysqli_query($con, "
    SELECT 
        ta.teacher_id,
        CONCAT(u.lastname,' ',u.name) AS teacher_name,
        cs.class,
        cs.section,
        COUNT(DISTINCT DATE(r.submission_date)) AS submitted_days
    FROM teacher_assignments ta
    JOIN tblusers u ON ta.teacher_id = u.id
    JOIN class_section cs ON ta.class_id = cs.id
    LEFT JOIN (
        SELECT s.id AS student_id, DATE(GREATEST(p.created_at, a.created_at)) AS submission_date
        FROM students s
        LEFT JOIN psychomotor p ON p.student_id = s.id 
            AND (TRIM(p.term) = '$currentTermName' OR TRIM(p.term) = '$currentTermId')
            AND (TRIM(p.session) = '$currentSessionName' OR TRIM(p.session) = '$currentSessionId')
        LEFT JOIN affective a ON a.student_id = s.id 
            AND (TRIM(a.term) = '$currentTermName' OR TRIM(a.term) = '$currentTermId')
            AND (TRIM(a.session) = '$currentSessionName' OR TRIM(a.session) = '$currentSessionId')
        WHERE p.id IS NOT NULL AND a.id IS NOT NULL
    ) r ON r.student_id IN (
        SELECT s.id FROM students s WHERE s.class = cs.class AND s.section = cs.section
    )
    WHERE ta.is_class_teacher = 1 AND u.status = 2 AND u.permission = 'Teacher'
    GROUP BY ta.teacher_id, cs.class, cs.section
");

// ----------------------
// Chart.js Data
// ----------------------
$chart_labels = [];
$chart_data = [];
$chart_colors = [];
$week_start = new DateTime('monday this week');
$today = new DateTime('today');
$period = new DatePeriod($week_start, new DateInterval('P1D'), (clone $today)->modify('+1 day'));
$expected_school_days = 0;
foreach ($period as $d) if ((int)$d->format('N') <= 5) $expected_school_days++;

mysqli_data_seek($weekly_ratings, 0);
while ($row = mysqli_fetch_assoc($weekly_ratings)) {
    $name = $row['teacher_name'] . " (" . $row['class'] . $row['section'] . ")";
    $days = (int)$row['submitted_days'];
    $chart_labels[] = $name;
    $chart_data[] = $days;

    $pct = $expected_school_days > 0 ? $days / $expected_school_days : 0;
    $chart_colors[] = $pct <= 0.4 ? '#dc3545' : ($pct <= 0.79 ? '#ffc107' : '#28a745');
}

$chart_labels_json = json_encode($chart_labels);
$chart_data_json = json_encode($chart_data);
$chart_colors_json = json_encode($chart_colors);
$expected_school_days_js = (int)$expected_school_days;
?>

<!DOCTYPE html>
<html>
<?php @include("includes/head.php"); ?>
<body class="hold-transition sidebar-mini layout-fixed">
<div class="wrapper">
<?php @include("includes/header.php"); ?>
<?php @include("includes/sidebar.php"); ?>

<div class="content-wrapper">
                <!-- Content Header (Page header) -->

                         <section class="content-header premium-header">
    <div class="container-fluid">
        <div class="header-glass-card">
            <div class="row align-items-center">
                <div class="col-md-8">
                    <div class="title-section">
                        <div class="icon-box bg-primary-gradient shadow-sm">
                            <i class="fas fa-start"></i>
                        </div>
                        <div>
                            <h1 class="premium-title text-primary">
                            Result Management
                                <span class="premium-subtitle text-secondary">Rating Monitor</span>
                            </h1>
                            <div class="mt-1">
                              <?php if ($currentSessionName): ?>
                                <span class="badge badge-primary mr-1">
                                  <i class="fas fa-calendar-alt"></i>
                                  <?= htmlspecialchars($currentSessionName) ?>
                                </span>
                              <?php endif; ?>
                              <?php if ($currentTermName): ?>
                                <span class="badge badge-success">
                                  <i class="fas fa-bookmark"></i>
                                  <?= htmlspecialchars($currentTermName) ?>
                                </span>
                              <?php else: ?>
                                <span class="badge badge-warning">
                                  <i class="fas fa-exclamation-triangle"></i> No active term
                                </span>
                              <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="col-md-4">
                    <nav aria-label="breadcrumb">
                        <ol class="breadcrumb premium-breadcrumb">
                            <li class="breadcrumb-item"><a href="dashboard.php" class="text-primary">Home</a></li>
                            <li class="breadcrumb-item active" aria-current="page">Rating</li>
                        </ol>
                    </nav>
                </div>
            </div>
        </div>
    </div>
</section>  
    <!-- Daily Ratings Status -->
    <section class="content">
        <div class="card card-primary card-outline">
            <div class="card-header bg-primary text-white d-flex justify-content-between">
                <h5 class="m-0"><i class="fas fa-star"></i> Ratings Status for <?= $currentTermName ?> - <?= $currentSessionName ?> Session</h5>
                <form method="POST" class="m-0">
                    <input type="hidden" name="term" value="<?= $currentTermName ?>">
                    <input type="hidden" name="session" value="<?= $currentSessionName ?>">
                    <button type="submit" name="notify_unsubmitted_teachers" class="btn btn-warning btn-sm">
                        <i class="fas fa-bell"></i> Notify Unsubmitted Teachers
                    </button>
                </form>
            </div>
            <div class="card-body">
                <?php if ($notification_sent): ?>
                    <div class="alert alert-success">Notifications sent to teachers with incomplete ratings.</div>
                <?php endif; ?>
                <?php if (mysqli_num_rows($teacher_ratings_status) > 0): ?>
                    <table class="table table-bordered table-hover">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>Teacher</th>
                                <th>Class</th>
                                <th>Arm</th>
                                <th>Fully Submitted</th>
                                <th>Missing Psychomotor</th>
                                <th>Missing Affective</th>
                                <th>Total Students</th>
                                <th>Last Submission</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                        <?php 
                        $sn = 1; 
                        while ($row = mysqli_fetch_assoc($teacher_ratings_status)):
                            $fully_submitted = $row['fully_submitted_count'] == $row['total_students'];
                        ?>
                        <tr <?php if (!$fully_submitted) echo 'style="background-color: #fff3cd;"'; ?>>
                            <td><?= $sn++; ?></td>
                            <td><?= $row['teacher_name']; ?></td>
                            <td><?= $row['class']; ?></td>
                            <td><?= $row['section']; ?></td>
                            <td><?= $row['fully_submitted_count']; ?></td>
                            <td><?= $row['missing_psychomotor']; ?></td>
                            <td><?= $row['missing_affective']; ?></td>
                            <td><?= $row['total_students']; ?></td>
                            <td><?= $row['last_submission_time'] ?? 'N/A'; ?></td>
                            <td>
                                <?php if ($fully_submitted): ?>
                                    <span class="badge badge-success">Completed</span>
                                <?php else: ?>
                                    <span class="badge badge-warning">Incomplete</span>
                                <?php endif; ?>
                            </td>
                        </tr>
                        <?php endwhile; ?>
                        </tbody>
                    </table>
                <?php else: ?>
                    <div class="alert alert-info">No teacher data available.</div>
                <?php endif; ?>
            </div>
        </div>
    </section>
    </div>
    <?php @include("includes/footer.php"); ?>
</div>
    <?php @include("includes/foot.php"); ?>
</body>
</html>
