<?php
// /elms/coordinator/reports.php
declare(strict_types=1);

$PAGE_TITLE = 'Reports';
$ACTIVE = 'reports';

require_once __DIR__ . '/../includes/coordinator_auth.php';

$db = null;
if (isset($mysqli) && $mysqli instanceof mysqli) {
    $db = $mysqli;
} elseif (isset($conn) && $conn instanceof mysqli) {
    $db = $conn;
}

if (!$db) {
    http_response_code(500);
    exit('Database connection not found.');
}

$db->set_charset('utf8mb4');

if (!function_exists('h')) {
    function h($value): string {
        return htmlspecialchars((string)$value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    }
}

function cr_table_exists(mysqli $db, string $table): bool {
    $safe = $db->real_escape_string($table);
    $res = $db->query("SHOW TABLES LIKE '{$safe}'");
    $ok = $res && $res->num_rows > 0;
    if ($res) $res->free();
    return $ok;
}

function cr_col_exists(mysqli $db, string $table, string $col): bool {
    $safeTable = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    $safeCol = $db->real_escape_string($col);
    $res = $db->query("SHOW COLUMNS FROM `{$safeTable}` LIKE '{$safeCol}'");
    $ok = $res && $res->num_rows > 0;
    if ($res) $res->free();
    return $ok;
}

function cr_first_col(mysqli $db, string $table, array $cols): ?string {
    if (!cr_table_exists($db, $table)) return null;

    foreach ($cols as $col) {
        if (cr_col_exists($db, $table, $col)) {
            return $col;
        }
    }

    return null;
}

function cr_count(mysqli $db, string $table): int {
    if (!cr_table_exists($db, $table)) return 0;

    $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    $res = $db->query("SELECT COUNT(*) AS c FROM `{$safe}`");

    if (!$res) return 0;

    $row = $res->fetch_assoc();
    $res->free();

    return (int)($row['c'] ?? 0);
}

function cr_grade(float $score, float $maxScore = 100): string {
    if ($maxScore <= 0) $maxScore = 100;

    $pct = ($score / $maxScore) * 100;

    if ($pct >= 75) return 'A';
    if ($pct >= 65) return 'B';
    if ($pct >= 55) return 'C';
    if ($pct >= 40) return 'S';
    return 'F';
}

function cr_grade_badge(string $grade): string {
    return match ($grade) {
        'A' => 'text-bg-success',
        'B' => 'text-bg-primary',
        'C' => 'text-bg-info',
        'S' => 'text-bg-warning',
        default => 'text-bg-danger',
    };
}

function cr_status_badge(string $status): string {
    $status = strtolower(trim($status));

    return match ($status) {
        'submitted', 'pending' => 'text-bg-warning',
        'needs_correction' => 'text-bg-danger',
        'graded_provisional' => 'text-bg-primary',
        'under_review', 'coord_review', 'coordinator_review', 'admin_review' => 'text-bg-info',
        'coord_approved', 'approved' => 'text-bg-success',
        'released', 'published' => 'text-bg-success',
        default => 'text-bg-light border text-dark',
    };
}

function cr_courses(mysqli $db): array {
    if (!cr_table_exists($db, 'courses')) return [];

    $nameCol = cr_first_col($db, 'courses', ['course_name', 'name', 'title', 'label']) ?? 'id';

    $res = $db->query("
        SELECT id, COALESCE(`{$nameCol}`, CONCAT('Course #', id)) AS label
        FROM courses
        ORDER BY label ASC
    ");

    $rows = [];

    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $rows[(int)$row['id']] = (string)$row['label'];
        }

        $res->free();
    }

    return $rows;
}

function cr_batches(mysqli $db, int $courseId = 0): array {
    if (!cr_table_exists($db, 'batches')) return [];

    $nameCol = cr_first_col($db, 'batches', ['batch_name', 'name', 'title', 'label']) ?? 'id';
    $hasCourse = cr_col_exists($db, 'batches', 'course_id');

    if ($courseId > 0 && $hasCourse) {
        $stmt = $db->prepare("
            SELECT id, COALESCE(`{$nameCol}`, CONCAT('Batch #', id)) AS label
            FROM batches
            WHERE course_id = ?
            ORDER BY label ASC
        ");
        $stmt->bind_param('i', $courseId);
        $stmt->execute();
        $res = $stmt->get_result();
    } else {
        $res = $db->query("
            SELECT id, COALESCE(`{$nameCol}`, CONCAT('Batch #', id)) AS label
            FROM batches
            ORDER BY label ASC
        ");
    }

    $rows = [];

    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $rows[(int)$row['id']] = (string)$row['label'];
        }

        $res->free();
    }

    if (isset($stmt)) $stmt->close();

    return $rows;
}

function cr_assessments(mysqli $db, int $courseId = 0): array {
    if (!cr_table_exists($db, 'lms_assessments')) return [];

    if ($courseId > 0 && cr_col_exists($db, 'lms_assessments', 'course_id')) {
        $stmt = $db->prepare("
            SELECT id, COALESCE(title, CONCAT('Assessment #', id)) AS title
            FROM lms_assessments
            WHERE course_id = ?
            ORDER BY id DESC
        ");
        $stmt->bind_param('i', $courseId);
        $stmt->execute();
        $res = $stmt->get_result();
    } else {
        $res = $db->query("
            SELECT id, COALESCE(title, CONCAT('Assessment #', id)) AS title
            FROM lms_assessments
            ORDER BY id DESC
        ");
    }

    $rows = [];

    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $rows[(int)$row['id']] = (string)$row['title'];
        }

        $res->free();
    }

    if (isset($stmt)) $stmt->close();

    return $rows;
}

function cr_assigned_student_count(mysqli $db, int $assessmentId, int $courseId = 0, int $batchFilter = 0): int {
    $studentIds = [];

    if ($assessmentId <= 0) return 0;

    if (cr_table_exists($db, 'lms_assessment_students')) {
        $stmt = $db->prepare("SELECT student_id FROM lms_assessment_students WHERE assessment_id = ?");
        if ($stmt) {
            $stmt->bind_param('i', $assessmentId);
            $stmt->execute();
            $res = $stmt->get_result();

            while ($row = $res->fetch_assoc()) {
                $sid = (int)($row['student_id'] ?? 0);
                if ($sid > 0) $studentIds[$sid] = $sid;
            }

            $stmt->close();
        }
    }

    $targetBatchIds = [];

    if (cr_table_exists($db, 'lms_assessment_batches')) {
        $stmt = $db->prepare("SELECT batch_id FROM lms_assessment_batches WHERE assessment_id = ?");
        if ($stmt) {
            $stmt->bind_param('i', $assessmentId);
            $stmt->execute();
            $res = $stmt->get_result();

            while ($row = $res->fetch_assoc()) {
                $bid = (int)($row['batch_id'] ?? 0);

                if ($batchFilter > 0 && $bid !== $batchFilter) {
                    continue;
                }

                if ($bid > 0) {
                    $targetBatchIds[$bid] = $bid;
                }
            }

            $stmt->close();
        }
    }

    if ($targetBatchIds && cr_table_exists($db, 'enrollments')) {
        $batchList = implode(',', array_map('intval', array_values($targetBatchIds)));

        $res = $db->query("
            SELECT DISTINCT student_id
            FROM enrollments
            WHERE batch_id IN ({$batchList})
              AND student_id IS NOT NULL
        ");

        if ($res) {
            while ($row = $res->fetch_assoc()) {
                $sid = (int)($row['student_id'] ?? 0);
                if ($sid > 0) $studentIds[$sid] = $sid;
            }

            $res->free();
        }
    }

    /*
     * If no explicit target is found, treat it as course-level assessment.
     */
    if (!$studentIds && !$targetBatchIds && $courseId > 0) {
        if ($batchFilter > 0 && cr_table_exists($db, 'enrollments')) {
            $stmt = $db->prepare("
                SELECT DISTINCT student_id
                FROM enrollments
                WHERE batch_id = ?
                  AND student_id IS NOT NULL
            ");

            if ($stmt) {
                $stmt->bind_param('i', $batchFilter);
                $stmt->execute();
                $res = $stmt->get_result();

                while ($row = $res->fetch_assoc()) {
                    $sid = (int)($row['student_id'] ?? 0);
                    if ($sid > 0) $studentIds[$sid] = $sid;
                }

                $stmt->close();
            }
        } else {
            if (cr_table_exists($db, 'studentss') && cr_col_exists($db, 'studentss', 'course_id')) {
                $stmt = $db->prepare("SELECT id FROM studentss WHERE course_id = ?");
                if ($stmt) {
                    $stmt->bind_param('i', $courseId);
                    $stmt->execute();
                    $res = $stmt->get_result();

                    while ($row = $res->fetch_assoc()) {
                        $sid = (int)($row['id'] ?? 0);
                        if ($sid > 0) $studentIds[$sid] = $sid;
                    }

                    $stmt->close();
                }
            }

            if (cr_table_exists($db, 'enrollments') && cr_table_exists($db, 'batches')) {
                $stmt = $db->prepare("
                    SELECT DISTINCT e.student_id
                    FROM enrollments e
                    INNER JOIN batches b ON b.id = e.batch_id
                    WHERE b.course_id = ?
                      AND e.student_id IS NOT NULL
                ");

                if ($stmt) {
                    $stmt->bind_param('i', $courseId);
                    $stmt->execute();
                    $res = $stmt->get_result();

                    while ($row = $res->fetch_assoc()) {
                        $sid = (int)($row['student_id'] ?? 0);
                        if ($sid > 0) $studentIds[$sid] = $sid;
                    }

                    $stmt->close();
                }
            }
        }
    }

    return count($studentIds);
}

/* -------------------------------------------------------
   Filters
------------------------------------------------------- */

$filterCourse = (int)($_GET['course_id'] ?? 0);
$filterBatch = (int)($_GET['batch_id'] ?? 0);
$filterAssessment = (int)($_GET['assessment_id'] ?? 0);

$courses = cr_courses($db);
$batches = cr_batches($db, $filterCourse);
$assessments = cr_assessments($db, $filterCourse);

/* -------------------------------------------------------
   Base stats
------------------------------------------------------- */

$stats = [
    'students'    => cr_count($db, 'studentss'),
    'batches'     => cr_count($db, 'batches'),
    'courses'     => cr_count($db, 'courses'),
    'assessments' => cr_count($db, 'lms_assessments'),
    'submissions' => cr_count($db, 'lms_submissions'),
];

/* -------------------------------------------------------
   Assessment Progress Report
------------------------------------------------------- */

$assessmentProgress = [];

if (cr_table_exists($db, 'lms_assessments')) {
    $courseNameCol = cr_first_col($db, 'courses', ['course_name', 'name', 'title', 'label']) ?? 'id';

    $where = [];
    $types = '';
    $params = [];

    if ($filterCourse > 0 && cr_col_exists($db, 'lms_assessments', 'course_id')) {
        $where[] = "a.course_id = ?";
        $types .= 'i';
        $params[] = $filterCourse;
    }

    if ($filterAssessment > 0) {
        $where[] = "a.id = ?";
        $types .= 'i';
        $params[] = $filterAssessment;
    }

    if ($filterBatch > 0 && cr_table_exists($db, 'lms_assessment_batches')) {
        $where[] = "(
            EXISTS (
                SELECT 1
                FROM lms_assessment_batches abf
                WHERE abf.assessment_id = a.id
                  AND abf.batch_id = ?
            )
            OR EXISTS (
                SELECT 1
                FROM lms_submissions sf
                WHERE sf.assessment_id = a.id
                  AND sf.batch_id = ?
            )
        )";
        $types .= 'ii';
        $params[] = $filterBatch;
        $params[] = $filterBatch;
    }

    $whereSql = $where ? "WHERE " . implode(" AND ", $where) : "";

    $sql = "
        SELECT
            a.id,
            a.title,
            a.course_id,
            a.max_score,
            a.active,
            a.due_at,
            COALESCE(c.`{$courseNameCol}`, CONCAT('Course #', a.course_id)) AS course_name,

            COUNT(DISTINCT s.id) AS submitted_count,

            SUM(CASE WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('submitted','pending') THEN 1 ELSE 0 END) AS status_submitted,
            SUM(CASE WHEN LOWER(COALESCE(s.workflow_status,'')) = 'needs_correction' THEN 1 ELSE 0 END) AS status_correction,
            SUM(CASE WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('graded_provisional') THEN 1 ELSE 0 END) AS status_marked,
            SUM(CASE WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('under_review','coord_review','coordinator_review','admin_review') THEN 1 ELSE 0 END) AS status_review,
            SUM(CASE WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('coord_approved','approved') THEN 1 ELSE 0 END) AS status_approved,
            SUM(CASE WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('released','published') THEN 1 ELSE 0 END) AS status_released,

            AVG(CASE WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('released','published') THEN s.final_score END) AS released_avg
        FROM lms_assessments a
        LEFT JOIN courses c ON c.id = a.course_id
        LEFT JOIN lms_submissions s ON s.assessment_id = a.id
        {$whereSql}
        GROUP BY a.id
        ORDER BY a.id DESC
        LIMIT 200
    ";

    $stmt = $db->prepare($sql);

    if ($stmt) {
        if ($types !== '') {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $res = $stmt->get_result();

        while ($row = $res->fetch_assoc()) {
            $assigned = cr_assigned_student_count(
                $db,
                (int)$row['id'],
                (int)($row['course_id'] ?? 0),
                $filterBatch
            );

            $submitted = (int)($row['submitted_count'] ?? 0);
            $released = (int)($row['status_released'] ?? 0);

            $row['assigned_count'] = $assigned;
            $row['pending_count'] = max(0, $assigned - $submitted);
            $row['completion_pct'] = $assigned > 0 ? round(($submitted / $assigned) * 100, 1) : 0;
            $row['release_pct'] = $assigned > 0 ? round(($released / $assigned) * 100, 1) : 0;

            $assessmentProgress[] = $row;
        }

        $stmt->close();
    }
}

/* -------------------------------------------------------
   Student Performance Summary
------------------------------------------------------- */

$studentPerformance = [];

if (cr_table_exists($db, 'lms_submissions') && cr_table_exists($db, 'lms_assessments')) {
    $studentNameCol = cr_first_col($db, 'studentss', ['full_name', 'name', 'student_name']) ?? 'id';
    $regCol = cr_first_col($db, 'studentss', ['center_reg_no', 'reference_no', 'reg_no']);

    $where = ["LOWER(COALESCE(s.workflow_status,'')) IN ('released','published')"];
    $types = '';
    $params = [];

    if ($filterCourse > 0) {
        $where[] = "a.course_id = ?";
        $types .= 'i';
        $params[] = $filterCourse;
    }

    if ($filterAssessment > 0) {
        $where[] = "s.assessment_id = ?";
        $types .= 'i';
        $params[] = $filterAssessment;
    }

    if ($filterBatch > 0 && cr_col_exists($db, 'lms_submissions', 'batch_id')) {
        $where[] = "s.batch_id = ?";
        $types .= 'i';
        $params[] = $filterBatch;
    }

    $whereSql = "WHERE " . implode(" AND ", $where);

    $regSelect = $regCol ? "st.`{$regCol}` AS reg_no," : "'' AS reg_no,";

    $sql = "
        SELECT
            s.student_id,
            COALESCE(st.`{$studentNameCol}`, CONCAT('Student #', s.student_id)) AS student_name,
            {$regSelect}
            COUNT(*) AS released_count,
            AVG(CASE WHEN a.max_score > 0 THEN (s.final_score / a.max_score) * 100 ELSE s.final_score END) AS avg_pct,
            MAX(CASE WHEN a.max_score > 0 THEN (s.final_score / a.max_score) * 100 ELSE s.final_score END) AS best_pct,
            MIN(CASE WHEN a.max_score > 0 THEN (s.final_score / a.max_score) * 100 ELSE s.final_score END) AS lowest_pct
        FROM lms_submissions s
        INNER JOIN lms_assessments a ON a.id = s.assessment_id
        LEFT JOIN studentss st ON st.id = s.student_id
        {$whereSql}
        GROUP BY s.student_id
        ORDER BY avg_pct DESC, released_count DESC
        LIMIT 200
    ";

    $stmt = $db->prepare($sql);

    if ($stmt) {
        if ($types !== '') {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $res = $stmt->get_result();

        while ($row = $res->fetch_assoc()) {
            $avgPct = (float)($row['avg_pct'] ?? 0);
            $row['grade'] = cr_grade($avgPct, 100);
            $studentPerformance[] = $row;
        }

        $stmt->close();
    }
}

/* -------------------------------------------------------
   Released Results Table
------------------------------------------------------- */

$releasedResults = [];

if (cr_table_exists($db, 'lms_submissions') && cr_table_exists($db, 'lms_assessments')) {
    $studentNameCol = cr_first_col($db, 'studentss', ['full_name', 'name', 'student_name']) ?? 'id';
    $regCol = cr_first_col($db, 'studentss', ['center_reg_no', 'reference_no', 'reg_no']);
    $courseNameCol = cr_first_col($db, 'courses', ['course_name', 'name', 'title', 'label']) ?? 'id';
    $batchNameCol = cr_first_col($db, 'batches', ['batch_name', 'name', 'title', 'label']) ?? 'id';

    $where = ["LOWER(COALESCE(s.workflow_status,'')) IN ('released','published')"];
    $types = '';
    $params = [];

    if ($filterCourse > 0) {
        $where[] = "a.course_id = ?";
        $types .= 'i';
        $params[] = $filterCourse;
    }

    if ($filterAssessment > 0) {
        $where[] = "s.assessment_id = ?";
        $types .= 'i';
        $params[] = $filterAssessment;
    }

    if ($filterBatch > 0 && cr_col_exists($db, 'lms_submissions', 'batch_id')) {
        $where[] = "s.batch_id = ?";
        $types .= 'i';
        $params[] = $filterBatch;
    }

    $whereSql = "WHERE " . implode(" AND ", $where);

    $regSelect = $regCol ? "st.`{$regCol}` AS reg_no," : "'' AS reg_no,";

    $sql = "
        SELECT
            s.id,
            s.student_id,
            s.assessment_id,
            s.batch_id,
            s.final_score,
            s.lecturer_feedback,
            s.released_at,
            s.workflow_status,

            COALESCE(st.`{$studentNameCol}`, CONCAT('Student #', s.student_id)) AS student_name,
            {$regSelect}
            COALESCE(a.title, CONCAT('Assessment #', s.assessment_id)) AS assessment_title,
            COALESCE(a.max_score, 100) AS max_score,
            COALESCE(c.`{$courseNameCol}`, CONCAT('Course #', a.course_id)) AS course_name,
            COALESCE(b.`{$batchNameCol}`, CONCAT('Batch #', s.batch_id)) AS batch_name
        FROM lms_submissions s
        INNER JOIN lms_assessments a ON a.id = s.assessment_id
        LEFT JOIN studentss st ON st.id = s.student_id
        LEFT JOIN courses c ON c.id = a.course_id
        LEFT JOIN batches b ON b.id = s.batch_id
        {$whereSql}
        ORDER BY s.released_at DESC, s.id DESC
        LIMIT 300
    ";

    $stmt = $db->prepare($sql);

    if ($stmt) {
        if ($types !== '') {
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $res = $stmt->get_result();

        while ($row = $res->fetch_assoc()) {
            $score = (float)($row['final_score'] ?? 0);
            $max = (float)($row['max_score'] ?? 100);
            $row['grade'] = cr_grade($score, $max);
            $row['percent'] = $max > 0 ? round(($score / $max) * 100, 1) : 0;
            $releasedResults[] = $row;
        }

        $stmt->close();
    }
}

/* -------------------------------------------------------
   Summary KPIs from reports
------------------------------------------------------- */

$totalAssigned = 0;
$totalSubmitted = 0;
$totalPending = 0;
$totalReleased = 0;
$totalUnderReview = 0;
$totalApproved = 0;

foreach ($assessmentProgress as $ap) {
    $totalAssigned += (int)($ap['assigned_count'] ?? 0);
    $totalSubmitted += (int)($ap['submitted_count'] ?? 0);
    $totalPending += (int)($ap['pending_count'] ?? 0);
    $totalReleased += (int)($ap['status_released'] ?? 0);
    $totalUnderReview += (int)($ap['status_review'] ?? 0);
    $totalApproved += (int)($ap['status_approved'] ?? 0);
}

$avgPerformance = 0;
if ($studentPerformance) {
    $sum = 0;
    foreach ($studentPerformance as $sp) {
        $sum += (float)($sp['avg_pct'] ?? 0);
    }
    $avgPerformance = round($sum / count($studentPerformance), 1);
}

require_once __DIR__ . '/../includes/coordinator_layout_top.php';
?>

<style>
    .report-head {
        border: 1px solid #e5e7eb;
        background:
            radial-gradient(circle at top right, rgba(185, 28, 28, .10), transparent 28%),
            linear-gradient(180deg, #ffffff 0%, #f8fafc 100%);
        border-radius: 18px;
        padding: 20px;
        margin-bottom: 18px;
    }

    .report-title {
        font-size: 26px;
        font-weight: 900;
        letter-spacing: -.03em;
        margin: 0;
        color: #111827;
    }

    .report-subtitle {
        color: #6b7280;
        font-size: 14px;
        margin-top: 4px;
    }

    .report-card {
        background: #fff;
        border: 1px solid #e5e7eb;
        border-radius: 18px;
        box-shadow: 0 10px 28px rgba(15, 23, 42, .055);
        overflow: hidden;
    }

    .report-card-head {
        padding: 15px 18px;
        border-bottom: 1px solid #eef2f7;
        background: #fbfcfe;
        display: flex;
        align-items: center;
        justify-content: space-between;
        gap: 12px;
        flex-wrap: wrap;
    }

    .report-card-body {
        padding: 18px;
    }

    .report-kpi {
        background: #fff;
        border: 1px solid #e5e7eb;
        border-radius: 16px;
        padding: 15px;
        height: 100%;
    }

    .report-kpi-label {
        color: #6b7280;
        font-size: 12px;
        font-weight: 800;
        text-transform: uppercase;
        letter-spacing: .04em;
    }

    .report-kpi-value {
        font-size: 28px;
        font-weight: 900;
        line-height: 1.1;
        margin-top: 4px;
        color: #111827;
    }

    .report-kpi-icon {
        font-size: 24px;
        color: #b91c1c;
        opacity: .9;
    }

    .progress-thin {
        height: 8px;
        border-radius: 999px;
    }

    .table-report th {
        background: #f8fafc;
        color: #475569;
        font-size: 11px;
        text-transform: uppercase;
        letter-spacing: .04em;
        white-space: nowrap;
    }

    .table-report td {
        vertical-align: middle;
        font-size: 13px;
    }

    .filter-box {
        border: 1px solid #e5e7eb;
        border-radius: 16px;
        background: #fff;
        padding: 16px;
        margin-bottom: 18px;
    }

    .section-anchor {
        scroll-margin-top: 90px;
    }
</style>

<div class="report-head">
    <div class="d-flex flex-column flex-lg-row justify-content-between gap-3">
        <div>
            <h1 class="report-title">Reports & Monitoring</h1>
            <div class="report-subtitle">
                Assessment progress, student performance, released results, and course/batch/assessment filtering.
            </div>
        </div>

        <div class="d-flex flex-wrap gap-2 align-items-start">
            <a href="#assessment-progress" class="btn btn-light border rounded-pill fw-bold">
                Assessment Progress
            </a>
            <a href="#student-performance" class="btn btn-light border rounded-pill fw-bold">
                Student Summary
            </a>
            <a href="#released-results" class="btn btn-danger rounded-pill fw-bold">
                Released Results
            </a>
        </div>
    </div>
</div>

<div class="filter-box">
    <form method="get" class="row g-3 align-items-end">
        <div class="col-lg-4">
            <label class="form-label fw-bold">Course</label>
            <select name="course_id" class="form-select" onchange="this.form.submit()">
                <option value="0">All Courses</option>
                <?php foreach ($courses as $id => $label): ?>
                    <option value="<?= (int)$id ?>" <?= $filterCourse === (int)$id ? 'selected' : '' ?>>
                        <?= h($label) ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>

        <div class="col-lg-3">
            <label class="form-label fw-bold">Batch</label>
            <select name="batch_id" class="form-select">
                <option value="0">All Batches</option>
                <?php foreach ($batches as $id => $label): ?>
                    <option value="<?= (int)$id ?>" <?= $filterBatch === (int)$id ? 'selected' : '' ?>>
                        <?= h($label) ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>

        <div class="col-lg-3">
            <label class="form-label fw-bold">Assessment</label>
            <select name="assessment_id" class="form-select">
                <option value="0">All Assessments</option>
                <?php foreach ($assessments as $id => $label): ?>
                    <option value="<?= (int)$id ?>" <?= $filterAssessment === (int)$id ? 'selected' : '' ?>>
                        <?= h($label) ?>
                    </option>
                <?php endforeach; ?>
            </select>
        </div>

        <div class="col-lg-2 d-grid">
            <button class="btn btn-danger rounded-pill fw-bold">
                <i class="bi bi-funnel me-1"></i>
                Apply
            </button>
        </div>

        <div class="col-12">
            <a href="/elms/coordinator/reports.php" class="btn btn-sm btn-outline-secondary rounded-pill">
                Clear Filters
            </a>
        </div>
    </form>
</div>

<div class="row g-3 mb-4">
    <div class="col-md-3">
        <div class="report-kpi">
            <div class="d-flex justify-content-between">
                <div>
                    <div class="report-kpi-label">Assessments</div>
                    <div class="report-kpi-value"><?= number_format(count($assessmentProgress)) ?></div>
                </div>
                <i class="bi bi-clipboard-check report-kpi-icon"></i>
            </div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="report-kpi">
            <div class="d-flex justify-content-between">
                <div>
                    <div class="report-kpi-label">Assigned</div>
                    <div class="report-kpi-value"><?= number_format($totalAssigned) ?></div>
                </div>
                <i class="bi bi-people report-kpi-icon"></i>
            </div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="report-kpi">
            <div class="d-flex justify-content-between">
                <div>
                    <div class="report-kpi-label">Submitted</div>
                    <div class="report-kpi-value"><?= number_format($totalSubmitted) ?></div>
                </div>
                <i class="bi bi-cloud-check report-kpi-icon"></i>
            </div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="report-kpi">
            <div class="d-flex justify-content-between">
                <div>
                    <div class="report-kpi-label">Released</div>
                    <div class="report-kpi-value"><?= number_format($totalReleased) ?></div>
                </div>
                <i class="bi bi-award report-kpi-icon"></i>
            </div>
        </div>
    </div>
</div>

<div class="row g-3 mb-4">
    <div class="col-md-3">
        <div class="report-kpi">
            <div class="report-kpi-label">Pending</div>
            <div class="report-kpi-value text-warning"><?= number_format($totalPending) ?></div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="report-kpi">
            <div class="report-kpi-label">Under Review</div>
            <div class="report-kpi-value text-info"><?= number_format($totalUnderReview) ?></div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="report-kpi">
            <div class="report-kpi-label">Approved</div>
            <div class="report-kpi-value text-success"><?= number_format($totalApproved) ?></div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="report-kpi">
            <div class="report-kpi-label">Average Performance</div>
            <div class="report-kpi-value text-primary"><?= number_format($avgPerformance, 1) ?>%</div>
        </div>
    </div>
</div>

<div class="report-card mb-4 section-anchor" id="assessment-progress">
    <div class="report-card-head">
        <div>
            <h5 class="fw-bold mb-1">Assessment Progress Report</h5>
            <div class="text-muted small">
                Assigned, submitted, pending, review, approved, and released status by assessment.
            </div>
        </div>

        <span class="badge text-bg-danger rounded-pill">
            <?= number_format(count($assessmentProgress)) ?> records
        </span>
    </div>

    <div class="table-responsive">
        <table class="table table-hover align-middle table-report mb-0">
            <thead>
                <tr>
                    <th>Assessment</th>
                    <th>Course</th>
                    <th class="text-center">Assigned</th>
                    <th class="text-center">Submitted</th>
                    <th class="text-center">Pending</th>
                    <th class="text-center">Review</th>
                    <th class="text-center">Approved</th>
                    <th class="text-center">Released</th>
                    <th style="min-width:170px;">Completion</th>
                    <th>Status</th>
                </tr>
            </thead>

            <tbody>
                <?php if (!$assessmentProgress): ?>
                    <tr>
                        <td colspan="10" class="text-center text-muted py-5">
                            <i class="bi bi-inbox fs-2 d-block mb-2"></i>
                            No assessment progress records found.
                        </td>
                    </tr>
                <?php else: ?>
                    <?php foreach ($assessmentProgress as $row): ?>
                        <?php
                            $completion = (float)($row['completion_pct'] ?? 0);
                            $active = (int)($row['active'] ?? 1) === 1;
                        ?>
                        <tr>
                            <td>
                                <div class="fw-bold"><?= h($row['title'] ?? '-') ?></div>
                                <div class="small text-muted">ID: <?= (int)$row['id'] ?></div>
                            </td>

                            <td><?= h($row['course_name'] ?? '-') ?></td>

                            <td class="text-center">
                                <span class="badge text-bg-light border"><?= (int)$row['assigned_count'] ?></span>
                            </td>

                            <td class="text-center">
                                <span class="badge text-bg-primary"><?= (int)$row['submitted_count'] ?></span>
                            </td>

                            <td class="text-center">
                                <span class="badge text-bg-warning"><?= (int)$row['pending_count'] ?></span>
                            </td>

                            <td class="text-center">
                                <span class="badge text-bg-info"><?= (int)$row['status_review'] ?></span>
                            </td>

                            <td class="text-center">
                                <span class="badge text-bg-success"><?= (int)$row['status_approved'] ?></span>
                            </td>

                            <td class="text-center">
                                <span class="badge text-bg-success"><?= (int)$row['status_released'] ?></span>
                            </td>

                            <td>
                                <div class="d-flex justify-content-between small mb-1">
                                    <span><?= number_format($completion, 1) ?>%</span>
                                    <span><?= (int)$row['submitted_count'] ?>/<?= (int)$row['assigned_count'] ?></span>
                                </div>
                                <div class="progress progress-thin">
                                    <div class="progress-bar bg-danger" style="width: <?= min(100, max(0, $completion)) ?>%"></div>
                                </div>
                            </td>

                            <td>
                                <?php if ($active): ?>
                                    <span class="badge text-bg-success">Active</span>
                                <?php else: ?>
                                    <span class="badge text-bg-secondary">Inactive</span>
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<div class="report-card mb-4 section-anchor" id="student-performance">
    <div class="report-card-head">
        <div>
            <h5 class="fw-bold mb-1">Student Performance Summary</h5>
            <div class="text-muted small">
                Released results only. Average percentage and grade by student.
            </div>
        </div>

        <span class="badge text-bg-primary rounded-pill">
            <?= number_format(count($studentPerformance)) ?> students
        </span>
    </div>

    <div class="table-responsive">
        <table class="table table-hover align-middle table-report mb-0">
            <thead>
                <tr>
                    <th>Student</th>
                    <th>Reg No</th>
                    <th class="text-center">Released Count</th>
                    <th class="text-center">Average</th>
                    <th class="text-center">Best</th>
                    <th class="text-center">Lowest</th>
                    <th class="text-center">Grade</th>
                </tr>
            </thead>

            <tbody>
                <?php if (!$studentPerformance): ?>
                    <tr>
                        <td colspan="7" class="text-center text-muted py-5">
                            <i class="bi bi-people fs-2 d-block mb-2"></i>
                            No released student performance records found.
                        </td>
                    </tr>
                <?php else: ?>
                    <?php foreach ($studentPerformance as $row): ?>
                        <?php $grade = (string)($row['grade'] ?? 'F'); ?>
                        <tr>
                            <td>
                                <div class="fw-bold"><?= h($row['student_name'] ?? '-') ?></div>
                                <div class="small text-muted">Student ID: <?= (int)$row['student_id'] ?></div>
                            </td>

                            <td><?= h($row['reg_no'] ?? '-') ?></td>

                            <td class="text-center">
                                <span class="badge text-bg-light border"><?= (int)$row['released_count'] ?></span>
                            </td>

                            <td class="text-center fw-bold">
                                <?= number_format((float)($row['avg_pct'] ?? 0), 1) ?>%
                            </td>

                            <td class="text-center">
                                <?= number_format((float)($row['best_pct'] ?? 0), 1) ?>%
                            </td>

                            <td class="text-center">
                                <?= number_format((float)($row['lowest_pct'] ?? 0), 1) ?>%
                            </td>

                            <td class="text-center">
                                <span class="badge <?= h(cr_grade_badge($grade)) ?>">
                                    <?= h($grade) ?>
                                </span>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<div class="report-card section-anchor" id="released-results">
    <div class="report-card-head">
        <div>
            <h5 class="fw-bold mb-1">Released Results Table</h5>
            <div class="text-muted small">
                Student released marks, percentage, grade, feedback, course, batch, and assessment.
            </div>
        </div>

        <span class="badge text-bg-success rounded-pill">
            <?= number_format(count($releasedResults)) ?> released
        </span>
    </div>

    <div class="table-responsive">
        <table class="table table-hover align-middle table-report mb-0">
            <thead>
                <tr>
                    <th>Student</th>
                    <th>Reg No</th>
                    <th>Course</th>
                    <th>Batch</th>
                    <th>Assessment</th>
                    <th class="text-center">Marks</th>
                    <th class="text-center">Percent</th>
                    <th class="text-center">Grade</th>
                    <th>Feedback</th>
                    <th>Released At</th>
                </tr>
            </thead>

            <tbody>
                <?php if (!$releasedResults): ?>
                    <tr>
                        <td colspan="10" class="text-center text-muted py-5">
                            <i class="bi bi-award fs-2 d-block mb-2"></i>
                            No released results found.
                        </td>
                    </tr>
                <?php else: ?>
                    <?php foreach ($releasedResults as $row): ?>
                        <?php $grade = (string)($row['grade'] ?? 'F'); ?>
                        <tr>
                            <td>
                                <div class="fw-bold"><?= h($row['student_name'] ?? '-') ?></div>
                                <div class="small text-muted">ID: <?= (int)$row['student_id'] ?></div>
                            </td>

                            <td><?= h($row['reg_no'] ?? '-') ?></td>

                            <td><?= h($row['course_name'] ?? '-') ?></td>

                            <td>
                                <?php if (!empty($row['batch_id'])): ?>
                                    <?= h($row['batch_name'] ?? ('Batch #' . (int)$row['batch_id'])) ?>
                                <?php else: ?>
                                    <span class="text-muted">—</span>
                                <?php endif; ?>
                            </td>

                            <td>
                                <div class="fw-semibold"><?= h($row['assessment_title'] ?? '-') ?></div>
                                <div class="small text-muted">Assessment ID: <?= (int)$row['assessment_id'] ?></div>
                            </td>

                            <td class="text-center">
                                <span class="fw-bold">
                                    <?= h((string)($row['final_score'] ?? '0')) ?> / <?= h((string)($row['max_score'] ?? '100')) ?>
                                </span>
                            </td>

                            <td class="text-center">
                                <?= number_format((float)($row['percent'] ?? 0), 1) ?>%
                            </td>

                            <td class="text-center">
                                <span class="badge <?= h(cr_grade_badge($grade)) ?>">
                                    <?= h($grade) ?>
                                </span>
                            </td>

                            <td style="max-width:260px;">
                                <div class="small text-muted text-truncate">
                                    <?= h($row['lecturer_feedback'] ?? '') ?>
                                </div>
                            </td>

                            <td class="small text-muted">
                                <?php if (!empty($row['released_at'])): ?>
                                    <?= h(date('Y-m-d H:i', strtotime((string)$row['released_at']))) ?>
                                <?php else: ?>
                                    —
                                <?php endif; ?>
                            </td>
                        </tr>
                    <?php endforeach; ?>
                <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<?php require_once __DIR__ . '/../includes/coordinator_layout_bottom.php'; ?>