<?php
// /elms/admin/assessments/gradebook.php
declare(strict_types=1);

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

if (!defined('APP_ROOT')) {
    define('APP_ROOT', dirname(__DIR__, 2));
}

require_once APP_ROOT . '/includes/admin_auth.php';
require_once APP_ROOT . '/includes/db.php';

$mysqli = $mysqli ?? ($conn ?? null);
if (!$mysqli instanceof mysqli) {
    die('Database connection not found.');
}

$mysqli->set_charset('utf8mb4');

$PAGE_TITLE = 'Gradebook Analytics';
$ACTIVE = 'gradebook';

function gb_e($v): string {
    return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8');
}

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

function gb_col_exists(mysqli $db, string $table, string $col): bool {
    $table = $db->real_escape_string($table);
    $col = $db->real_escape_string($col);
    $res = $db->query("SHOW COLUMNS FROM `{$table}` LIKE '{$col}'");
    return $res && $res->num_rows > 0;
}

function gb_first_col(mysqli $db, string $table, array $cols): string {
    foreach ($cols as $col) {
        if (gb_col_exists($db, $table, $col)) {
            return $col;
        }
    }
    return '';
}

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

    if ($s === '') return 'Submitted';
    if (str_contains($s, 'released')) return 'Released';
    if (str_contains($s, 'need') || str_contains($s, 'correction')) return 'Needs Correction';
    if (str_contains($s, 'marked') || str_contains($s, 'graded') || str_contains($s, 'review')) return 'Marked';
    if (str_contains($s, 'submit')) return 'Submitted';

    return ucwords(str_replace(['_', '-'], ' ', $status));
}

function gb_status_class(string $status): string {
    $s = strtolower($status);

    if (str_contains($s, 'released')) return 'success';
    if (str_contains($s, 'need') || str_contains($s, 'correction')) return 'warning';
    if (str_contains($s, 'marked') || str_contains($s, 'graded')) return 'primary';
    if (str_contains($s, 'submitted')) return 'info';

    return 'secondary';
}

function gb_file_url(?string $path): string {
    $path = trim((string)$path);
    if ($path === '') return '';

    if (preg_match('~^https?://~i', $path)) return $path;
    if (str_starts_with($path, '/')) return $path;

    $path = ltrim($path, './');

    if (str_starts_with($path, 'elms/')) {
        return '/' . $path;
    }

    return '/elms/' . $path;
}

if (!gb_table_exists($mysqli, 'lms_submissions') || !gb_table_exists($mysqli, 'lms_assessments') || !gb_table_exists($mysqli, 'studentss')) {
    require_once APP_ROOT . '/includes/admin_layout_top.php';
    echo '<div class="alert alert-danger">Required tables missing: lms_submissions, lms_assessments, studentss.</div>';
    require_once APP_ROOT . '/includes/admin_layout_bottom.php';
    exit;
}

$subTable = 'lms_submissions';
$assTable = 'lms_assessments';
$stuTable = 'studentss';

$subIdCol = gb_first_col($mysqli, $subTable, ['id', 'submission_id']);
$subAssessmentCol = gb_first_col($mysqli, $subTable, ['assessment_id', 'lms_assessment_id', 'assignment_id']);
$subStudentCol = gb_first_col($mysqli, $subTable, ['student_id', 'studentss_id', 'learner_id']);
$subScoreCol = gb_first_col($mysqli, $subTable, ['final_score', 'score', 'marks', 'mark', 'total_score', 'grade_score']);
$subFeedbackCol = gb_first_col($mysqli, $subTable, ['feedback', 'lecturer_feedback', 'marker_feedback', 'remarks', 'comment']);
$subStatusCol = gb_first_col($mysqli, $subTable, ['workflow_status', 'status', 'marking_status', 'grade_status']);
$subSubmittedCol = gb_first_col($mysqli, $subTable, ['submitted_at', 'submitted_date', 'created_at', 'date_submitted', 'uploaded_at']);
$subMarkedCol = gb_first_col($mysqli, $subTable, ['marked_at', 'graded_at', 'updated_at', 'released_at']);
$subFileCol = gb_first_col($mysqli, $subTable, [
    'corrected_pdf',
    'marked_pdf',
    'corrected_file',
    'marked_file',
    'feedback_file',
    'graded_file',
    'file_path',
    'submission_file',
    'attachment',
    'file_url'
]);

$assIdCol = gb_first_col($mysqli, $assTable, ['id', 'assessment_id']);
$assTitleCol = gb_first_col($mysqli, $assTable, ['title', 'assessment_title', 'name', 'assessment_name']);
$assBatchCol = gb_first_col($mysqli, $assTable, ['batch_id', 'lms_batch_id']);
$subBatchCol = gb_first_col($mysqli, $subTable, ['batch_id', 'lms_batch_id']);

$batchSource = '';
if ($subBatchCol !== '') {
    $batchSource = 'submission';
} elseif ($assBatchCol !== '') {
    $batchSource = 'assessment';
}
$assCourseCol = gb_first_col($mysqli, $assTable, ['course_id', 'lms_course_id']);

$stuIdCol = gb_first_col($mysqli, $stuTable, ['id', 'student_id']);
$stuNameCol = gb_first_col($mysqli, $stuTable, ['full_name', 'student_name', 'name', 'first_name']);
$stuRegCol = gb_first_col($mysqli, $stuTable, ['center_reg_no', 'reg_no', 'registration_no', 'reference_no', 'student_no']);
$stuSearchCols = array_filter([
    $stuNameCol,
    $stuRegCol,
    gb_first_col($mysqli, $stuTable, ['nic', 'nic_no']),
    gb_first_col($mysqli, $stuTable, ['mobile', 'phone']),
    gb_first_col($mysqli, $stuTable, ['email', 'academic_email'])
]);

$hasBatches = gb_table_exists($mysqli, 'batches') && $batchSource !== '';
$batchIdCol = $hasBatches ? gb_first_col($mysqli, 'batches', ['id', 'batch_id']) : '';
$batchLabelCol = $hasBatches ? gb_first_col($mysqli, 'batches', ['batch_name', 'name', 'code', 'batch_code', 'title']) : '';

$hasCourses = gb_table_exists($mysqli, 'courses') && $assCourseCol !== '';
$courseIdCol = $hasCourses ? gb_first_col($mysqli, 'courses', ['id', 'course_id']) : '';
$courseLabelCol = $hasCourses ? gb_first_col($mysqli, 'courses', ['course_name', 'name', 'title', 'code']) : '';

$assessmentFilter = isset($_GET['assessment_id']) ? (int)$_GET['assessment_id'] : 0;
$batchFilter = isset($_GET['batch_id']) ? (int)$_GET['batch_id'] : 0;
$statusFilter = trim((string)($_GET['status'] ?? ''));
$studentSearch = trim((string)($_GET['q'] ?? ''));
$dateFrom = trim((string)($_GET['date_from'] ?? ''));
$dateTo = trim((string)($_GET['date_to'] ?? ''));
$export = isset($_GET['export']) && $_GET['export'] === 'csv';

$where = [];
if ($assessmentFilter > 0 && $subAssessmentCol !== '') {
    $where[] = "s.`{$subAssessmentCol}` = {$assessmentFilter}";
}
if ($batchFilter > 0 && $hasBatches) {
    if ($batchSource === 'submission') {
        $where[] = "s.`{$subBatchCol}` = {$batchFilter}";
    } elseif ($batchSource === 'assessment') {
        $where[] = "a.`{$assBatchCol}` = {$batchFilter}";
    }
}
if ($dateFrom !== '' && $subSubmittedCol !== '') {
    $safe = $mysqli->real_escape_string($dateFrom);
    $where[] = "DATE(s.`{$subSubmittedCol}`) >= '{$safe}'";
}
if ($dateTo !== '' && $subSubmittedCol !== '') {
    $safe = $mysqli->real_escape_string($dateTo);
    $where[] = "DATE(s.`{$subSubmittedCol}`) <= '{$safe}'";
}
if ($studentSearch !== '' && !empty($stuSearchCols)) {
    $safe = $mysqli->real_escape_string($studentSearch);
    $likes = [];
    foreach ($stuSearchCols as $c) {
        $likes[] = "st.`{$c}` LIKE '%{$safe}%'";
    }
    $where[] = '(' . implode(' OR ', $likes) . ')';
}

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

$select = [];
$select[] = "s.`{$subIdCol}` AS submission_id";
$select[] = $stuNameCol ? "st.`{$stuNameCol}` AS student_name" : "'' AS student_name";
$select[] = $stuRegCol ? "st.`{$stuRegCol}` AS reg_no" : "'' AS reg_no";
$select[] = $assTitleCol ? "a.`{$assTitleCol}` AS assessment_title" : "CONCAT('Assessment #', a.`{$assIdCol}`) AS assessment_title";
$select[] = $subSubmittedCol ? "s.`{$subSubmittedCol}` AS submitted_date" : "NULL AS submitted_date";
$select[] = $subScoreCol ? "s.`{$subScoreCol}` AS final_score" : "NULL AS final_score";
$select[] = $subStatusCol ? "s.`{$subStatusCol}` AS raw_status" : "'' AS raw_status";
$select[] = $subFeedbackCol ? "s.`{$subFeedbackCol}` AS feedback" : "'' AS feedback";
$select[] = $subMarkedCol ? "s.`{$subMarkedCol}` AS marked_date" : "NULL AS marked_date";
$select[] = $subFileCol ? "s.`{$subFileCol}` AS corrected_file" : "'' AS corrected_file";
$select[] = ($hasBatches && $batchLabelCol) ? "b.`{$batchLabelCol}` AS batch_label" : "'' AS batch_label";
$select[] = ($hasCourses && $courseLabelCol) ? "c.`{$courseLabelCol}` AS course_label" : "'' AS course_label";

$sql = "
    SELECT " . implode(",\n           ", $select) . "
    FROM `{$subTable}` s
    LEFT JOIN `{$assTable}` a ON a.`{$assIdCol}` = s.`{$subAssessmentCol}`
    LEFT JOIN `{$stuTable}` st ON st.`{$stuIdCol}` = s.`{$subStudentCol}`
";

if ($hasBatches && $batchIdCol) {
    if ($batchSource === 'submission') {
        $sql .= " LEFT JOIN `batches` b ON b.`{$batchIdCol}` = s.`{$subBatchCol}` ";
    } elseif ($batchSource === 'assessment') {
        $sql .= " LEFT JOIN `batches` b ON b.`{$batchIdCol}` = a.`{$assBatchCol}` ";
    }
}
if ($hasCourses && $courseIdCol && $assCourseCol) {
    $sql .= " LEFT JOIN `courses` c ON c.`{$courseIdCol}` = a.`{$assCourseCol}` ";
}

$orderCol = $subSubmittedCol ?: $subIdCol;
$sql .= " {$whereSql} ORDER BY s.`{$orderCol}` DESC";

$res = $mysqli->query($sql);
$rows = [];

if ($res) {
    while ($r = $res->fetch_assoc()) {
        $r['status_label'] = gb_status_label((string)$r['raw_status']);

        if ($statusFilter !== '' && strtolower($r['status_label']) !== strtolower($statusFilter)) {
            continue;
        }

        $rows[] = $r;
    }
}

$total = count($rows);
$marked = 0;
$needs = 0;
$released = 0;
$scores = [];

foreach ($rows as $r) {
    $status = strtolower((string)$r['status_label']);

    if (str_contains($status, 'marked')) $marked++;
    if (str_contains($status, 'need')) $needs++;
    if (str_contains($status, 'released')) $released++;

    if ($r['final_score'] !== null && $r['final_score'] !== '' && is_numeric($r['final_score'])) {
        $scores[] = (float)$r['final_score'];
    }
}

$avgScore = $scores ? round(array_sum($scores) / count($scores), 2) : 0;
$highScore = $scores ? max($scores) : 0;

if ($export) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename=gradebook_analytics_' . date('Ymd_His') . '.csv');

    $out = fopen('php://output', 'w');
    fputcsv($out, [
        'Student',
        'Reg No',
        'Assessment',
        'Batch',
        'Course',
        'Submitted Date',
        'Final Score',
        'Status',
        'Feedback',
        'Marked Date',
        'Corrected File'
    ]);

    foreach ($rows as $r) {
        fputcsv($out, [
            $r['student_name'],
            $r['reg_no'],
            $r['assessment_title'],
            $r['batch_label'],
            $r['course_label'],
            $r['submitted_date'],
            $r['final_score'],
            $r['status_label'],
            $r['feedback'],
            $r['marked_date'],
            gb_file_url($r['corrected_file'] ?? '')
        ]);
    }

    fclose($out);
    exit;
}

$assessmentOptions = [];
if ($assIdCol) {
    $aSql = "SELECT `{$assIdCol}` AS id, " . ($assTitleCol ? "`{$assTitleCol}`" : "`{$assIdCol}`") . " AS title FROM `{$assTable}` ORDER BY title ASC";
    $aRes = $mysqli->query($aSql);
    if ($aRes) {
        while ($a = $aRes->fetch_assoc()) {
            $assessmentOptions[] = $a;
        }
    }
}

$batchOptions = [];
if ($hasBatches && $batchIdCol && $batchLabelCol) {
    $bSql = "SELECT `{$batchIdCol}` AS id, `{$batchLabelCol}` AS title FROM `batches` ORDER BY title ASC";
    $bRes = $mysqli->query($bSql);
    if ($bRes) {
        while ($b = $bRes->fetch_assoc()) {
            $batchOptions[] = $b;
        }
    }
}

require_once APP_ROOT . '/includes/admin_layout_top.php';
?>

<style>
    .gb-wrap {
        display: grid;
        gap: 18px;
    }

    .gb-head {
        background: linear-gradient(135deg, #111827, #1d4ed8);
        color: #fff;
        border-radius: 22px;
        padding: 24px;
        box-shadow: 0 14px 35px rgba(15, 23, 42, .18);
    }

    .gb-head h2 {
        margin: 0;
        font-weight: 800;
        letter-spacing: -.02em;
    }

    .gb-head p {
        margin: 6px 0 0;
        opacity: .82;
    }

    .gb-kpis {
        display: grid;
        grid-template-columns: repeat(6, minmax(0, 1fr));
        gap: 14px;
    }

    .gb-kpi {
        background: #fff;
        border: 1px solid #e5e7eb;
        border-radius: 18px;
        padding: 17px;
        box-shadow: 0 8px 24px rgba(15, 23, 42, .055);
    }

    .gb-kpi span {
        display: block;
        color: #64748b;
        font-size: 12px;
        font-weight: 700;
        text-transform: uppercase;
        letter-spacing: .05em;
    }

    .gb-kpi strong {
        display: block;
        margin-top: 8px;
        color: #0f172a;
        font-size: 26px;
        line-height: 1;
        font-weight: 900;
    }

    .gb-card {
        background: #fff;
        border: 1px solid #e5e7eb;
        border-radius: 20px;
        box-shadow: 0 8px 24px rgba(15, 23, 42, .055);
        overflow: hidden;
    }

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

    .gb-card-head h5 {
        margin: 0;
        color: #0f172a;
        font-weight: 800;
    }

    .gb-filter {
        padding: 20px;
    }

    .gb-filter .form-label {
        font-size: 12px;
        font-weight: 800;
        color: #475569;
        text-transform: uppercase;
        letter-spacing: .04em;
    }

    .gb-table {
        margin: 0;
        vertical-align: middle;
    }

    .gb-table thead th {
        background: #f8fafc;
        color: #334155;
        font-size: 12px;
        text-transform: uppercase;
        letter-spacing: .04em;
        border-bottom: 1px solid #e5e7eb;
        white-space: nowrap;
    }

    .gb-table td {
        color: #1f2937;
        font-size: 14px;
    }

    .gb-student {
        font-weight: 800;
        color: #0f172a;
    }

    .gb-muted {
        color: #64748b;
        font-size: 12px;
    }

    .gb-feedback {
        max-width: 280px;
        white-space: nowrap;
        overflow: hidden;
        text-overflow: ellipsis;
    }

    .badge-gb {
        border-radius: 999px;
        padding: 7px 10px;
        font-weight: 800;
        font-size: 12px;
    }

    .btn-gb {
        border-radius: 12px;
        font-weight: 800;
    }

    .empty-state {
        padding: 45px 20px;
        text-align: center;
        color: #64748b;
    }

    @media (max-width: 1200px) {
        .gb-kpis {
            grid-template-columns: repeat(3, minmax(0, 1fr));
        }
    }

    @media (max-width: 768px) {
        .gb-kpis {
            grid-template-columns: repeat(2, minmax(0, 1fr));
        }

        .gb-card-head {
            flex-direction: column;
            align-items: flex-start;
        }
    }
</style>

<div class="gb-wrap">

    <div class="gb-head">
        <h2>Gradebook Analytics</h2>
        <p>Read-only submissions summary. Marking, feedback, status changes and PDF corrections are handled from Submissions → Mark / Grade.</p>
    </div>

    <div class="gb-kpis">
        <div class="gb-kpi">
            <span>Total Submissions</span>
            <strong><?= number_format($total) ?></strong>
        </div>
        <div class="gb-kpi">
            <span>Marked</span>
            <strong><?= number_format($marked) ?></strong>
        </div>
        <div class="gb-kpi">
            <span>Needs Correction</span>
            <strong><?= number_format($needs) ?></strong>
        </div>
        <div class="gb-kpi">
            <span>Released</span>
            <strong><?= number_format($released) ?></strong>
        </div>
        <div class="gb-kpi">
            <span>Average Score</span>
            <strong><?= gb_e($avgScore) ?></strong>
        </div>
        <div class="gb-kpi">
            <span>Highest Score</span>
            <strong><?= gb_e($highScore) ?></strong>
        </div>
    </div>

    <div class="gb-card">
        <div class="gb-card-head">
            <h5>Filters</h5>
            <a class="btn btn-outline-primary btn-sm btn-gb"
               href="?<?= gb_e(http_build_query(array_merge($_GET, ['export' => 'csv']))) ?>">
                Export CSV
            </a>
        </div>

        <form method="get" class="gb-filter">
            <div class="row g-3">
                <div class="col-lg-3 col-md-6">
                    <label class="form-label">Assessment</label>
                    <select name="assessment_id" class="form-select">
                        <option value="0">All Assessments</option>
                        <?php foreach ($assessmentOptions as $a): ?>
                            <option value="<?= (int)$a['id'] ?>" <?= $assessmentFilter === (int)$a['id'] ? 'selected' : '' ?>>
                                <?= gb_e($a['title']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <div class="col-lg-2 col-md-6">
                    <label class="form-label">Batch</label>
                    <select name="batch_id" class="form-select" <?= !$hasBatches ? 'disabled' : '' ?>>
                        <option value="0">All Batches</option>
                        <?php foreach ($batchOptions as $b): ?>
                            <option value="<?= (int)$b['id'] ?>" <?= $batchFilter === (int)$b['id'] ? 'selected' : '' ?>>
                                <?= gb_e($b['title']) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <div class="col-lg-2 col-md-6">
                    <label class="form-label">Status</label>
                    <select name="status" class="form-select">
                        <option value="">All Status</option>
                        <?php foreach (['Submitted', 'Marked', 'Needs Correction', 'Released'] as $st): ?>
                            <option value="<?= gb_e($st) ?>" <?= strtolower($statusFilter) === strtolower($st) ? 'selected' : '' ?>>
                                <?= gb_e($st) ?>
                            </option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <div class="col-lg-3 col-md-6">
                    <label class="form-label">Student Search</label>
                    <input type="text" name="q" class="form-control"
                           placeholder="Name / Reg No / NIC / Mobile"
                           value="<?= gb_e($studentSearch) ?>">
                </div>

                <div class="col-lg-1 col-md-6">
                    <label class="form-label">From</label>
                    <input type="date" name="date_from" class="form-control" value="<?= gb_e($dateFrom) ?>">
                </div>

                <div class="col-lg-1 col-md-6">
                    <label class="form-label">To</label>
                    <input type="date" name="date_to" class="form-control" value="<?= gb_e($dateTo) ?>">
                </div>

                <div class="col-12 d-flex gap-2">
                    <button class="btn btn-primary btn-gb" type="submit">Apply Filters</button>
                    <a href="gradebook.php" class="btn btn-light border btn-gb">Reset</a>
                </div>
            </div>
        </form>
    </div>

    <div class="gb-card">
        <div class="gb-card-head">
            <h5>Submissions Summary</h5>
            <span class="gb-muted"><?= number_format($total) ?> record(s)</span>
        </div>

        <div class="table-responsive">
            <table class="table gb-table align-middle">
                <thead>
                    <tr>
                        <th>Student</th>
                        <th>Reg No</th>
                        <th>Assessment</th>
                        <th>Submitted Date</th>
                        <th>Final Score</th>
                        <th>Status</th>
                        <th>Feedback</th>
                        <th>Marked Date</th>
                        <th>Open Corrected PDF/File</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (!$rows): ?>
                        <tr>
                            <td colspan="9">
                                <div class="empty-state">
                                    No submissions found for selected filters.
                                </div>
                            </td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($rows as $r): ?>
                            <?php
                                $fileUrl = gb_file_url($r['corrected_file'] ?? '');
                                $status = (string)$r['status_label'];
                                $badge = gb_status_class($status);
                            ?>
                            <tr>
                                <td>
                                    <div class="gb-student"><?= gb_e($r['student_name'] ?: 'Unknown Student') ?></div>
                                    <?php if (!empty($r['course_label']) || !empty($r['batch_label'])): ?>
                                        <div class="gb-muted">
                                            <?= gb_e(trim(($r['course_label'] ?? '') . ' ' . (($r['batch_label'] ?? '') ? '• ' . $r['batch_label'] : ''))) ?>
                                        </div>
                                    <?php endif; ?>
                                </td>

                                <td><?= gb_e($r['reg_no'] ?: '-') ?></td>

                                <td><?= gb_e($r['assessment_title'] ?: '-') ?></td>

                                <td>
                                    <?= !empty($r['submitted_date']) ? gb_e(date('Y-m-d H:i', strtotime((string)$r['submitted_date']))) : '-' ?>
                                </td>

                                <td>
                                    <?php if ($r['final_score'] !== null && $r['final_score'] !== ''): ?>
                                        <strong><?= gb_e($r['final_score']) ?></strong>
                                    <?php else: ?>
                                        <span class="text-muted">Pending</span>
                                    <?php endif; ?>
                                </td>

                                <td>
                                    <span class="badge text-bg-<?= gb_e($badge) ?> badge-gb">
                                        <?= gb_e($status) ?>
                                    </span>
                                </td>

                                <td>
                                    <div class="gb-feedback" title="<?= gb_e($r['feedback'] ?? '') ?>">
                                        <?= gb_e($r['feedback'] ?: '-') ?>
                                    </div>
                                </td>

                                <td>
                                    <?= !empty($r['marked_date']) ? gb_e(date('Y-m-d H:i', strtotime((string)$r['marked_date']))) : '-' ?>
                                </td>

                                <td>
                                    <?php if ($fileUrl): ?>
                                        <a href="<?= gb_e($fileUrl) ?>" target="_blank" class="btn btn-sm btn-outline-dark btn-gb">
                                            Open File
                                        </a>
                                    <?php else: ?>
                                        <span class="text-muted">No file</span>
                                    <?php endif; ?>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>

</div>

<?php require_once APP_ROOT . '/includes/admin_layout_bottom.php'; ?>