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

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

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

$PAGE_TITLE = 'Tracking Sheet';
$ACTIVE = 'reports';

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

$db = $mysqli ?? ($conn ?? null);
if (!$db instanceof mysqli) {
    http_response_code(500);
    exit('Database connection not initialized.');
}
$db->set_charset('utf8mb4');

if (!function_exists('h')) {
    function h($v): string {
        return htmlspecialchars((string)$v, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    }
}
function ts_tbl(mysqli $db, string $t): bool {
    $t = $db->real_escape_string($t);
    $r = $db->query("SHOW TABLES LIKE '{$t}'");
    $ok = $r && $r->num_rows > 0;
    if ($r) $r->free();
    return $ok;
}
function ts_col(mysqli $db, string $t, string $c): bool {
    $t = preg_replace('/[^a-zA-Z0-9_]/', '', $t);
    $c = $db->real_escape_string($c);
    $r = $db->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'");
    $ok = $r && $r->num_rows > 0;
    if ($r) $r->free();
    return $ok;
}
function ts_pick_table(mysqli $db, array $tables): ?string {
    foreach ($tables as $t) {
        if (ts_tbl($db, $t)) return $t;
    }
    return null;
}
function ts_pick_col(mysqli $db, string $table, array $cols, string $fallback = 'id'): string {
    foreach ($cols as $c) {
        if (ts_col($db, $table, $c)) return $c;
    }
    return $fallback;
}
function ts_status_chip(bool $hasSub, ?string $status, $score): array {
    if (!$hasSub) return ['NS', 'Not Submitted', 'chip-ns'];

    $s = strtolower(trim((string)$status));

    if (in_array($s, ['released', 'published', 'marked', 'graded', 'graded_provisional', 'coord_approved', 'approved'], true)) {
        if ($score !== null && $score !== '' && is_numeric($score)) {
            return ((float)$score >= 40) ? ['P', 'Pass', 'chip-p'] : ['F', 'Fail', 'chip-f'];
        }
        return ['S', 'Submitted', 'chip-s'];
    }

    if (str_contains($s, 'need') || str_contains($s, 'correction')) return ['RN', 'Referred', 'chip-rn'];
    if (str_contains($s, 'defer')) return ['D', 'Deferred', 'chip-d'];
    if (str_contains($s, 'pending') || str_contains($s, 'review') || str_contains($s, 'submit')) return ['S', 'Submitted', 'chip-s'];

    if ($score !== null && $score !== '' && is_numeric($score)) {
        return ((float)$score >= 40) ? ['P', 'Pass', 'chip-p'] : ['F', 'Fail', 'chip-f'];
    }

    return ['S', 'Submitted', 'chip-s'];
}

$T_BATCHES  = ts_pick_table($db, ['batches', 'centre_batches', 'center_batches']);
$T_COURSES  = ts_pick_table($db, ['courses']);
$T_ASSESS   = ts_pick_table($db, ['lms_assessments', 'assessments']);
$T_ASS_BATCH = ts_pick_table($db, ['lms_assessment_batches']);
$T_SUBMIT   = ts_pick_table($db, ['lms_submissions', 'lms_student_assessments', 'submissions']);
$T_ENROLL   = ts_pick_table($db, ['enrollments', 'lms_enrollments', 'student_batches', 'batch_students']);
$T_STUDENTS = ts_pick_table($db, ['studentss', 'students']);

$hasBatchModules = ts_tbl($db, 'batch_modules');
$hasCourseModules = ts_tbl($db, 'course_modules');

$batch_id = isset($_GET['batch_id']) && $_GET['batch_id'] !== '' ? (int)$_GET['batch_id'] : 0;
$export = isset($_GET['export']) && $_GET['export'] === 'csv';

$batchRows = [];
if ($T_BATCHES) {
    $batchNameCol = ts_pick_col($db, $T_BATCHES, ['batch_name', 'name', 'batch_code', 'code', 'title'], 'id');
    $courseNameCol = $T_COURSES ? ts_pick_col($db, $T_COURSES, ['course_name', 'name', 'title', 'code'], 'id') : 'id';

    $courseJoin = '';
    $courseSelect = "'' AS course_name";

    if ($T_COURSES && ts_col($db, $T_BATCHES, 'course_id')) {
        $courseJoin = "LEFT JOIN `$T_COURSES` c ON c.id = b.course_id";
        $courseSelect = "COALESCE(c.`$courseNameCol`, '') AS course_name";
    }

    $sql = "
        SELECT b.id, COALESCE(b.`$batchNameCol`, CONCAT('Batch #', b.id)) AS batch_name, $courseSelect
        FROM `$T_BATCHES` b
        $courseJoin
        ORDER BY b.id DESC
        LIMIT 500
    ";
    if ($rs = $db->query($sql)) {
        while ($row = $rs->fetch_assoc()) $batchRows[] = $row;
        $rs->free();
    }
}

$assTitleCol = $T_ASSESS ? ts_pick_col($db, $T_ASSESS, ['title', 'name', 'assessment_title', 'code', 'short_code'], 'id') : 'id';
$assModuleCol = $T_ASSESS ? ts_pick_col($db, $T_ASSESS, ['module_id', 'course_module_id', 'lms_module_id'], '') : '';

$studNameCol = $T_STUDENTS ? ts_pick_col($db, $T_STUDENTS, ['full_name', 'student_name', 'name'], 'id') : 'id';
$studRefCol  = $T_STUDENTS ? ts_pick_col($db, $T_STUDENTS, ['center_reg_no', 'reference_no', 'reg_no', 'student_ref'], 'id') : 'id';

$subAssessmentCol = $T_SUBMIT ? ts_pick_col($db, $T_SUBMIT, ['assessment_id', 'lms_assessment_id'], 'assessment_id') : 'assessment_id';
$subStudentCol = $T_SUBMIT ? ts_pick_col($db, $T_SUBMIT, ['student_id', 'studentss_id'], 'student_id') : 'student_id';
$subStatusCol = $T_SUBMIT ? ts_pick_col($db, $T_SUBMIT, ['workflow_status', 'status', 'marking_status'], '') : '';
$subScoreCol = $T_SUBMIT ? ts_pick_col($db, $T_SUBMIT, ['final_score', 'score', 'marks', 'mark'], '') : '';

$enrollStudentCol = $T_ENROLL ? ts_pick_col($db, $T_ENROLL, ['student_id', 'studentss_id'], 'student_id') : 'student_id';
$enrollBatchCol = $T_ENROLL ? ts_pick_col($db, $T_ENROLL, ['batch_id', 'lms_batch_id'], 'batch_id') : 'batch_id';

$gridStages = [];
$assMeta = [];
$students = [];
$subMap = [];
$gradeMap = [];

if ($batch_id > 0 && $T_ASSESS && $T_SUBMIT && $T_ENROLL && $T_STUDENTS) {

    if ($T_ASS_BATCH) {

    $sqlA = "
        SELECT DISTINCT
            a.id AS aid,
            COALESCE(a.`$assTitleCol`, CONCAT('A#', a.id)) AS title,
            0 AS stg
        FROM `$T_ASSESS` a
        JOIN `$T_ASS_BATCH` ab ON ab.assessment_id = a.id
        WHERE ab.batch_id = ?
        ORDER BY a.id ASC
    ";

} elseif ($hasBatchModules && $hasCourseModules && $assModuleCol !== '') {

    $cmSortCol = ts_pick_col($db, 'course_modules', ['sort_order', 'semester_id', 'semester'], 'id');

    $sqlA = "
        SELECT DISTINCT
            a.id AS aid,
            COALESCE(a.`$assTitleCol`, CONCAT('A#', a.id)) AS title,
            COALESCE(cm.`$cmSortCol`, 0) AS stg
        FROM `$T_ASSESS` a
        JOIN batch_modules bm ON bm.course_module_id = a.`$assModuleCol`
        JOIN course_modules cm ON cm.id = bm.course_module_id
        WHERE bm.batch_id = ?
        ORDER BY stg ASC, a.id ASC
    ";

} elseif (ts_col($db, $T_ASSESS, 'batch_id')) { 
        $sqlA = "
            SELECT DISTINCT
                a.id AS aid,
                COALESCE(a.`$assTitleCol`, CONCAT('A#', a.id)) AS title,
                0 AS stg
            FROM `$T_ASSESS` a
            WHERE a.batch_id = ?
            ORDER BY a.id ASC
        ";
    } else {
        $sqlA = "";
    }

    if ($sqlA !== '') {
        $st = $db->prepare($sqlA);
        if (!$st) die('SQL A failed: ' . h($db->error) . '<pre>' . h($sqlA) . '</pre>');
        $st->bind_param('i', $batch_id);
        $st->execute();
        $rs = $st->get_result();
        while ($r = $rs->fetch_assoc()) {
            $aid = (int)$r['aid'];
            $stage = (int)$r['stg'];
            $assMeta[$aid] = ['title' => $r['title'], 'stage' => $stage];
            $gridStages[$stage][] = $aid;
        }
        $st->close();
    }

    $sqlS = "
        SELECT 
            e.`$enrollStudentCol` AS sid,
            COALESCE(stu.`$studNameCol`, CONCAT('Student #', e.`$enrollStudentCol`)) AS sname,
            COALESCE(stu.`$studRefCol`, '') AS sref
        FROM `$T_ENROLL` e
        LEFT JOIN `$T_STUDENTS` stu ON stu.id = e.`$enrollStudentCol`
        WHERE e.`$enrollBatchCol` = ?
        ORDER BY sname ASC
    ";
    $st = $db->prepare($sqlS);
    if (!$st) die('SQL S failed: ' . h($db->error) . '<pre>' . h($sqlS) . '</pre>');
    $st->bind_param('i', $batch_id);
    $st->execute();
    $rs = $st->get_result();
    while ($r = $rs->fetch_assoc()) {
        $students[(int)$r['sid']] = ['name' => $r['sname'], 'ref' => $r['sref']];
    }
    $st->close();

    $statusSel = $subStatusCol ? "s.`$subStatusCol` AS sub_status" : "'' AS sub_status";
    $scoreSel  = $subScoreCol ? "s.`$subScoreCol` AS sub_score" : "NULL AS sub_score";

    if ($T_ASS_BATCH) {

    $sqlSub = "
        SELECT DISTINCT
            s.`$subAssessmentCol` AS assessment_id,
            s.`$subStudentCol` AS student_id,
            $statusSel,
            $scoreSel
        FROM `$T_SUBMIT` s
        JOIN `$T_ASS_BATCH` ab ON ab.assessment_id = s.`$subAssessmentCol`
        WHERE ab.batch_id = ?
    ";

} elseif ($hasBatchModules && $hasCourseModules && $assModuleCol !== '') {
        $sqlSub = "
            SELECT DISTINCT
                s.`$subAssessmentCol` AS assessment_id,
                s.`$subStudentCol` AS student_id,
                $statusSel,
                $scoreSel
            FROM `$T_SUBMIT` s
            WHERE s.batch_id = ?
        ";
    } else {
        $sqlSub = "";
    }

    if ($sqlSub !== '') {
        $st = $db->prepare($sqlSub);
        if (!$st) die('SQL SUB failed: ' . h($db->error) . '<pre>' . h($sqlSub) . '</pre>');
        $st->bind_param('i', $batch_id);
        $st->execute();
        $rs = $st->get_result();
        while ($r = $rs->fetch_assoc()) {
            $key = (int)$r['assessment_id'] . '-' . (int)$r['student_id'];
            $subMap[$key] = 1;
            $gradeMap[$key] = [
                'status' => (string)($r['sub_status'] ?? ''),
                'score' => $r['sub_score'] ?? null,
            ];
        }
        $st->close();
    }
}

ksort($gridStages);

if ($export && $batch_id > 0) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="tracking_sheet_batch_' . $batch_id . '_' . date('Ymd_His') . '.csv"');

    $out = fopen('php://output', 'w');
    $header = ['Student ID', 'Student Name', 'Reg No'];

    foreach ($gridStages as $stage => $ids) {
        foreach ($ids as $aid) {
            $header[] = 'Stage ' . $stage . ' - ' . ($assMeta[$aid]['title'] ?? ('A#' . $aid));
        }
    }

    fputcsv($out, $header);

    foreach ($students as $sid => $info) {
        $row = [$sid, $info['name'], $info['ref']];
        foreach ($gridStages as $ids) {
            foreach ($ids as $aid) {
                $key = $aid . '-' . $sid;
                $chip = ts_status_chip(isset($subMap[$key]), $gradeMap[$key]['status'] ?? '', $gradeMap[$key]['score'] ?? null);
                $row[] = $chip[0];
            }
        }
        fputcsv($out, $row);
    }

    fclose($out);
    exit;
}

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

<style>
    .ts-card {
        background: #fff;
        border: 1px solid #e5e7eb;
        border-radius: 20px;
        box-shadow: 0 8px 24px rgba(15,23,42,.055);
        overflow: hidden;
    }
    .ts-head {
        padding: 18px 20px;
        border-bottom: 1px solid #e5e7eb;
        display: flex;
        justify-content: space-between;
        gap: 12px;
        flex-wrap: wrap;
        align-items: center;
    }
    .chip {
        display: inline-block;
        padding: .18rem .5rem;
        border-radius: 999px;
        font-size: .72rem;
        font-weight: 800;
        color: #111827;
    }
    .chip-ns { background:#ffe08a; }
    .chip-s { background:#cbd5e1; }
    .chip-p { background:#86efac; }
    .chip-f { background:#fda4af; }
    .chip-rn { background:#fecaca; }
    .chip-d { background:#fde68a; }
    .chip-n { background:#d1d5db; }
    .stage-head th {
        background:#f8fafc;
        font-size:12px;
        text-transform:uppercase;
        letter-spacing:.04em;
    }
    .ass-head {
        max-width:160px;
        white-space:nowrap;
        overflow:hidden;
        text-overflow:ellipsis;
        font-size:12px;
    }
</style>

<div class="ts-card">
    <div class="ts-head">
        <div>
            <h4 class="fw-bold mb-1">Tracking Sheet</h4>
            <div class="text-muted small">Batch-wise student assessment progress grid.</div>
        </div>

        <form method="get" class="d-flex flex-wrap align-items-center gap-2">
            <select name="batch_id" class="form-select form-select-sm" style="min-width:340px">
                <option value="">Select Batch</option>
                <?php foreach ($batchRows as $b): ?>
                    <option value="<?= (int)$b['id'] ?>" <?= $batch_id === (int)$b['id'] ? 'selected' : '' ?>>
                        #<?= (int)$b['id'] ?> — <?= h($b['batch_name']) ?><?= !empty($b['course_name']) ? ' · ' . h($b['course_name']) : '' ?>
                    </option>
                <?php endforeach; ?>
            </select>

            <button class="btn btn-sm btn-primary">Apply</button>

            <?php if ($batch_id): ?>
                <a class="btn btn-sm btn-outline-success" href="?batch_id=<?= (int)$batch_id ?>&export=csv">
                    Export CSV
                </a>
            <?php endif; ?>
        </form>
    </div>

    <div class="p-3">
        <div class="mb-3 small">
            <strong>Legend:</strong>
            <span class="chip chip-ns mx-1">NS</span> Not Submitted
            <span class="chip chip-s mx-1">S</span> Submitted
            <span class="chip chip-p mx-1">P</span> Pass
            <span class="chip chip-f mx-1">F</span> Fail
            <span class="chip chip-rn mx-1">RN</span> Referred
            <span class="chip chip-d mx-1">D</span> Deferred
        </div>

        <?php if (!$batch_id): ?>
            <div class="text-muted py-4">Select a batch to view tracking sheet.</div>
        <?php elseif (!$gridStages): ?>
            <div class="alert alert-warning mb-0">No assessments found for this batch.</div>
        <?php else: ?>
            <div class="table-responsive">
                <table class="table table-sm align-middle">
                    <thead>
                        <tr class="stage-head">
                            <th style="min-width:90px">Student ID</th>
                            <th style="min-width:250px">Student</th>
                            <th>Reg No</th>
                            <?php foreach ($gridStages as $stage => $ids): ?>
                                <th class="text-center" colspan="<?= count($ids) ?>">Stage <?= (int)$stage ?></th>
                            <?php endforeach; ?>
                        </tr>
                        <tr>
                            <th></th>
                            <th></th>
                            <th></th>
                            <?php foreach ($gridStages as $ids): ?>
                                <?php foreach ($ids as $aid): ?>
                                    <th class="ass-head" title="<?= h($assMeta[$aid]['title'] ?? '') ?>">
                                        <?= h($assMeta[$aid]['title'] ?? ('A#' . $aid)) ?>
                                    </th>
                                <?php endforeach; ?>
                            <?php endforeach; ?>
                        </tr>
                    </thead>

                    <tbody>
                        <?php if (!$students): ?>
                            <tr>
                                <td colspan="<?= 3 + array_sum(array_map('count', $gridStages)) ?>" class="text-center text-muted py-4">
                                    No students enrolled.
                                </td>
                            </tr>
                        <?php else: ?>
                            <?php foreach ($students as $sid => $info): ?>
                                <tr>
                                    <td><?= (int)$sid ?></td>
                                    <td class="fw-semibold"><?= h($info['name']) ?></td>
                                    <td><?= h($info['ref']) ?></td>

                                    <?php foreach ($gridStages as $ids): ?>
                                        <?php foreach ($ids as $aid): ?>
                                            <?php
                                                $key = $aid . '-' . $sid;
                                                $chip = ts_status_chip(
                                                    isset($subMap[$key]),
                                                    $gradeMap[$key]['status'] ?? '',
                                                    $gradeMap[$key]['score'] ?? null
                                                );
                                            ?>
                                            <td>
                                                <span class="chip <?= h($chip[2]) ?>" title="<?= h($chip[1]) ?>">
                                                    <?= h($chip[0]) ?>
                                                </span>
                                            </td>
                                        <?php endforeach; ?>
                                    <?php endforeach; ?>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        <?php endif; ?>
    </div>
</div>

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