<?php
// /elms/lecturer/dashboard.php
declare(strict_types=1);

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

@date_default_timezone_set('Asia/Colombo');

$PAGE_TITLE = 'Dashboard';
$ACTIVE = 'dashboard';

$lecturerId = (int)($LECTURER_USER['id'] ?? 0);

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

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

function lec_fetch_all_stmt(mysqli_stmt $stmt): array {
    $rows = [];

    if (method_exists($stmt, 'get_result')) {
        $res = $stmt->get_result();
        if ($res) {
            $rows = $res->fetch_all(MYSQLI_ASSOC);
            $res->free();
        }
    }

    return $rows;
}

function lec_count(mysqli $db, string $sql, string $types = '', array $vals = []): int {
    $stmt = $db->prepare($sql);
    if (!$stmt) return 0;

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

    $stmt->execute();

    $count = 0;
    $res = $stmt->get_result();
    if ($res) {
        $row = $res->fetch_row();
        $count = (int)($row[0] ?? 0);
        $res->free();
    }

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

function lec_in_list(array $items): string {
    return "'" . implode("','", array_map(static function ($v) {
        return str_replace("'", "''", (string)$v);
    }, $items)) . "'";
}

/* ---------------- Schema detection ---------------- */
$toGrade = 0;
$provisional = 0;
$underReview = 0;
$gradedToday = 0;

$queue = [];
$corrections = [];
$recent = [];

$days = max(1, min(90, (int)($_GET['days'] ?? 7)));

if (isset($mysqli) && $mysqli instanceof mysqli) {
    $hasAssess = lec_tbl_exists($mysqli, 'lms_assessments');
    $hasSubs   = lec_tbl_exists($mysqli, 'lms_submissions');

    if ($hasAssess && $hasSubs) {
        $studentsTbl = lec_tbl_exists($mysqli, 'studentss')
            ? 'studentss'
            : (lec_tbl_exists($mysqli, 'students') ? 'students' : null);

        $subHas = static fn(string $c): bool => lec_col_exists($mysqli, 'lms_submissions', $c);
        $assHas = static fn(string $c): bool => lec_col_exists($mysqli, 'lms_assessments', $c);

        $hasSubmittedAt = $subHas('submitted_at');
        $hasCreatedAt   = $subHas('created_at');
        $hasUpdatedAt   = $subHas('updated_at');
        $hasGradedAt    = $subHas('graded_at');
        $hasWorkflow    = $subHas('workflow_status');

        $submittedExpr = $hasSubmittedAt ? 's.submitted_at'
            : ($hasCreatedAt ? 's.created_at'
            : ($hasUpdatedAt ? 's.updated_at'
            : ($hasGradedAt ? 's.graded_at' : 'NULL')));

        $updatedExpr = $hasUpdatedAt ? 's.updated_at'
            : ($hasSubmittedAt ? 's.submitted_at'
            : ($hasCreatedAt ? 's.created_at'
            : ($hasGradedAt ? 's.graded_at' : 'NULL')));

        $gradedExpr = $hasGradedAt ? 's.graded_at' : $updatedExpr;

        $orderParts = [];
        if ($hasSubmittedAt) $orderParts[] = 's.submitted_at';
        if ($hasCreatedAt)   $orderParts[] = 's.created_at';
        if ($hasUpdatedAt)   $orderParts[] = 's.updated_at';
        if ($hasGradedAt)    $orderParts[] = 's.graded_at';

        $orderExpr = $orderParts ? 'COALESCE(' . implode(',', $orderParts) . ') DESC' : 's.id DESC';

        /*
         * Lecturer filter:
         * 1. lms_submissions.lecturer_id
         * 2. lms_assessments.lecturer_id
         * 3. lecturer_courses / lecturer_batches mapping
         * 4. fallback show all, because old schema may not have lecturer mapping
         */
        $filterJoin = '';
        $filterWhere = '1';
        $filterTypes = '';
        $filterVals = [];

        $hasLectCourses = lec_tbl_exists($mysqli, 'lecturer_courses');
        $hasLectBatches = lec_tbl_exists($mysqli, 'lecturer_batches');

        if ($lecturerId > 0) {
            if ($subHas('lecturer_id')) {
                $filterWhere = 's.lecturer_id = ?';
                $filterTypes = 'i';
                $filterVals[] = $lecturerId;
            } elseif ($assHas('lecturer_id')) {
                $filterWhere = 'a.lecturer_id = ?';
                $filterTypes = 'i';
                $filterVals[] = $lecturerId;
            } elseif ($hasLectCourses || $hasLectBatches) {
                $ors = [];

                if ($hasLectCourses && $assHas('course_id')) {
                    $filterJoin .= ' LEFT JOIN lecturer_courses lc ON lc.course_id = a.course_id ';
                    $ors[] = 'lc.lecturer_id = ?';
                    $filterTypes .= 'i';
                    $filterVals[] = $lecturerId;
                }

                if ($hasLectBatches && $subHas('batch_id')) {
                    $filterJoin .= ' LEFT JOIN lecturer_batches lb ON lb.batch_id = s.batch_id ';
                    $ors[] = 'lb.lecturer_id = ?';
                    $filterTypes .= 'i';
                    $filterVals[] = $lecturerId;
                }

                if ($ors) {
                    $filterWhere = '(' . implode(' OR ', $ors) . ')';
                }
            }
        }

        $ST_TO_GRADE     = ['submitted', 'needs_correction', 'pending'];
        $ST_PROVISIONAL  = ['graded_provisional'];
        $ST_UNDER_REVIEW = ['under_review', 'coord_review', 'coordinator_review', 'admin_review'];
        $ST_GRADED_ANY   = ['graded_provisional', 'under_review', 'coord_approved', 'approved', 'released', 'published'];

        if ($hasWorkflow) {
            $toGrade = lec_count(
                $mysqli,
                "SELECT COUNT(*)
                 FROM lms_submissions s
                 JOIN lms_assessments a ON a.id = s.assessment_id
                 {$filterJoin}
                 WHERE {$filterWhere}
                   AND LOWER(s.workflow_status) IN (" . lec_in_list($ST_TO_GRADE) . ")",
                $filterTypes,
                $filterVals
            );

            $provisional = lec_count(
                $mysqli,
                "SELECT COUNT(*)
                 FROM lms_submissions s
                 JOIN lms_assessments a ON a.id = s.assessment_id
                 {$filterJoin}
                 WHERE {$filterWhere}
                   AND LOWER(s.workflow_status) IN (" . lec_in_list($ST_PROVISIONAL) . ")",
                $filterTypes,
                $filterVals
            );

            $underReview = lec_count(
                $mysqli,
                "SELECT COUNT(*)
                 FROM lms_submissions s
                 JOIN lms_assessments a ON a.id = s.assessment_id
                 {$filterJoin}
                 WHERE {$filterWhere}
                   AND LOWER(s.workflow_status) IN (" . lec_in_list($ST_UNDER_REVIEW) . ")",
                $filterTypes,
                $filterVals
            );

            if ($hasGradedAt || $hasUpdatedAt) {
                $dateCol = $hasGradedAt ? 's.graded_at' : 's.updated_at';

                $gradedToday = lec_count(
                    $mysqli,
                    "SELECT COUNT(*)
                     FROM lms_submissions s
                     JOIN lms_assessments a ON a.id = s.assessment_id
                     {$filterJoin}
                     WHERE {$filterWhere}
                       AND {$dateCol} >= CURDATE()
                       AND LOWER(s.workflow_status) IN (" . lec_in_list($ST_GRADED_ANY) . ")",
                    $filterTypes,
                    $filterVals
                );
            }
        } else {
            $toGrade = lec_count(
                $mysqli,
                "SELECT COUNT(*)
                 FROM lms_submissions s
                 JOIN lms_assessments a ON a.id = s.assessment_id
                 {$filterJoin}
                 WHERE {$filterWhere}",
                $filterTypes,
                $filterVals
            );
        }

        /* Latest To Grade */
        $joinStudent = '';
        $studentExpr = "CONCAT('#', s.student_id)";

        if ($studentsTbl) {
            $stuNameCol = lec_col_exists($mysqli, $studentsTbl, 'full_name') ? 'full_name' : 'name';
            $joinStudent = "LEFT JOIN `{$studentsTbl}` st ON st.id = s.student_id";
            $studentExpr = "COALESCE(st.`{$stuNameCol}`, CONCAT('#', s.student_id))";
        }

        $queueWhere = $hasWorkflow
            ? "AND LOWER(s.workflow_status) IN (" . lec_in_list($ST_TO_GRADE) . ")"
            : "";

        $sql = "
            SELECT 
                s.id,
                s.batch_id,
                {$submittedExpr} AS submitted_at,
                a.title AS assessment,
                {$studentExpr} AS student
            FROM lms_submissions s
            JOIN lms_assessments a ON a.id = s.assessment_id
            {$joinStudent}
            {$filterJoin}
            WHERE {$filterWhere}
            {$queueWhere}
            ORDER BY {$orderExpr}
            LIMIT 10
        ";

        $stmt = $mysqli->prepare($sql);
        if ($stmt) {
            if ($filterTypes !== '') {
                $stmt->bind_param($filterTypes, ...$filterVals);
            }
            $stmt->execute();
            $queue = lec_fetch_all_stmt($stmt);
            $stmt->close();
        }

        /* Corrections */
        if ($hasWorkflow) {
            $sql = "
                SELECT 
                    s.id,
                    a.title AS assessment,
                    {$updatedExpr} AS updated_at
                FROM lms_submissions s
                JOIN lms_assessments a ON a.id = s.assessment_id
                {$filterJoin}
                WHERE {$filterWhere}
                  AND LOWER(s.workflow_status) = 'needs_correction'
                ORDER BY {$updatedExpr} DESC
                LIMIT 10
            ";

            $stmt = $mysqli->prepare($sql);
            if ($stmt) {
                if ($filterTypes !== '') {
                    $stmt->bind_param($filterTypes, ...$filterVals);
                }
                $stmt->execute();
                $corrections = lec_fetch_all_stmt($stmt);
                $stmt->close();
            }
        }

        /* Recent grading */
        if ($hasWorkflow && ($hasUpdatedAt || $hasGradedAt || $hasCreatedAt || $hasSubmittedAt)) {
            $sql = "
                SELECT 
                    a.title AS assessment,
                    {$gradedExpr} AS graded_at
                FROM lms_submissions s
                JOIN lms_assessments a ON a.id = s.assessment_id
                {$filterJoin}
                WHERE {$filterWhere}
                  AND LOWER(s.workflow_status) IN (" . lec_in_list($ST_GRADED_ANY) . ")
                  AND {$gradedExpr} >= DATE_SUB(NOW(), INTERVAL ? DAY)
                ORDER BY {$gradedExpr} DESC
                LIMIT 10
            ";

            $stmt = $mysqli->prepare($sql);
            if ($stmt) {
                $types = $filterTypes . 'i';
                $vals = array_merge($filterVals, [$days]);
                $stmt->bind_param($types, ...$vals);
                $stmt->execute();
                $recent = lec_fetch_all_stmt($stmt);
                $stmt->close();
            }
        }
    }
}

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

<div class="row g-3 mb-3">
    <div class="col-md-3">
        <div class="lux-card h-100">
            <div class="lux-card-body">
                <div class="d-flex align-items-center gap-3">
                    <div class="avatar" style="background:rgba(184,134,11,.14);color:#8a6508;">
                        <i class="bi bi-inbox-fill"></i>
                    </div>
                    <div>
                        <div class="text-muted small fw-bold text-uppercase">To Grade</div>
                        <div class="fs-3 fw-bold"><?= (int)$toGrade ?></div>
                    </div>
                </div>
                <a href="/elms/lecturer/assessments/" class="btn btn-sm btn-lux mt-3">
                    Open Assessments
                </a>
            </div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="lux-card h-100">
            <div class="lux-card-body">
                <div class="d-flex align-items-center gap-3">
                    <div class="avatar" style="background:rgba(34,197,94,.12);color:#15803d;">
                        <i class="bi bi-check2-circle"></i>
                    </div>
                    <div>
                        <div class="text-muted small fw-bold text-uppercase">Provisional</div>
                        <div class="fs-3 fw-bold"><?= (int)$provisional ?></div>
                    </div>
                </div>
                <div class="text-muted small mt-3">Marked but not finally released.</div>
            </div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="lux-card h-100">
            <div class="lux-card-body">
                <div class="d-flex align-items-center gap-3">
                    <div class="avatar" style="background:rgba(59,130,246,.12);color:#1d4ed8;">
                        <i class="bi bi-hourglass-split"></i>
                    </div>
                    <div>
                        <div class="text-muted small fw-bold text-uppercase">Under Review</div>
                        <div class="fs-3 fw-bold"><?= (int)$underReview ?></div>
                    </div>
                </div>
                <div class="text-muted small mt-3">Coordinator/Admin review queue.</div>
            </div>
        </div>
    </div>

    <div class="col-md-3">
        <div class="lux-card h-100">
            <div class="lux-card-body">
                <div class="d-flex align-items-center gap-3">
                    <div class="avatar" style="background:rgba(139,92,246,.12);color:#6d28d9;">
                        <i class="bi bi-pencil-square"></i>
                    </div>
                    <div>
                        <div class="text-muted small fw-bold text-uppercase">Graded Today</div>
                        <div class="fs-3 fw-bold"><?= (int)$gradedToday ?></div>
                    </div>
                </div>
                <div class="text-muted small mt-3"><?= date('Y-m-d H:i') ?> Sri Lanka</div>
            </div>
        </div>
    </div>
</div>

<div class="lux-card mb-3">
    <div class="lux-card-body">
        <form method="get" class="row g-2 align-items-end">
            <div class="col-md-3">
                <label class="form-label small fw-bold">Recent grading range</label>
                <input type="number" name="days" value="<?= (int)$days ?>" min="1" max="90" class="form-control">
            </div>
            <div class="col-md-2">
                <button class="btn btn-lux w-100">
                    <i class="bi bi-funnel"></i> Apply
                </button>
            </div>
        </form>
    </div>
</div>

<div class="row g-3">
    <div class="col-lg-8">
        <div class="lux-card h-100">
            <div class="lux-card-header d-flex justify-content-between align-items-center">
                <div>
                    <h5 class="mb-1 fw-bold">To Grade</h5>
                    <div class="text-muted small">Latest submitted assessments waiting for marking.</div>
                </div>
                <a href="/elms/lecturer/assessments/" class="btn btn-sm btn-lux">View All</a>
            </div>

            <div class="lux-card-body">
                <?php if (!$queue): ?>
                    <div class="text-center py-5">
                        <i class="bi bi-check2-circle fs-1 text-muted"></i>
                        <h6 class="mt-3 fw-bold">Nothing to grade</h6>
                        <p class="text-muted mb-0">New submissions will appear here.</p>
                    </div>
                <?php else: ?>
                    <div class="table-responsive">
                        <table class="table align-middle">
                            <thead>
                            <tr>
                                <th>Student</th>
                                <th>Assessment</th>
                                <th>Batch</th>
                                <th>Submitted</th>
                                <th class="text-end">Action</th>
                            </tr>
                            </thead>
                            <tbody>
                            <?php foreach ($queue as $q): ?>
                                <tr>
                                    <td class="fw-bold"><?= lecturer_h($q['student'] ?? '-') ?></td>
                                    <td><?= lecturer_h($q['assessment'] ?? '-') ?></td>
                                    <td><span class="badge-lux"><?= lecturer_h((string)($q['batch_id'] ?? '-')) ?></span></td>
                                    <td class="text-muted small"><?= lecturer_h((string)($q['submitted_at'] ?? '-')) ?></td>
                                    <td class="text-end">
                                        <a href="/elms/lecturer/grade.php?id=<?= (int)$q['id'] ?>" class="btn btn-sm btn-lux">
                                            Grade
                                        </a>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>

    <div class="col-lg-4">
        <div class="lux-card mb-3">
            <div class="lux-card-header">
                <h5 class="mb-1 fw-bold">Corrections Requested</h5>
                <div class="text-muted small">Items sent back for correction.</div>
            </div>

            <div class="lux-card-body">
                <?php if (!$corrections): ?>
                    <p class="text-muted mb-0">No pending corrections.</p>
                <?php else: ?>
                    <div class="d-grid gap-2">
                        <?php foreach ($corrections as $c): ?>
                            <div class="border rounded-4 p-3 bg-white">
                                <div class="fw-bold"><?= lecturer_h($c['assessment'] ?? '-') ?></div>
                                <div class="text-muted small"><?= lecturer_h((string)($c['updated_at'] ?? '-')) ?></div>
                            </div>
                        <?php endforeach; ?>
                    </div>
                <?php endif; ?>
            </div>
        </div>

        <div class="lux-card">
            <div class="lux-card-header">
                <h5 class="mb-1 fw-bold">Recent Grading</h5>
                <div class="text-muted small">Last <?= (int)$days ?> days.</div>
            </div>

            <div class="lux-card-body">
                <?php if (!$recent): ?>
                    <p class="text-muted mb-0">No recent grading actions.</p>
                <?php else: ?>
                    <div class="d-grid gap-2">
                        <?php foreach ($recent as $r): ?>
                            <div class="border rounded-4 p-3 bg-white">
                                <div class="fw-bold"><?= lecturer_h($r['assessment'] ?? '-') ?></div>
                                <div class="text-muted small"><?= lecturer_h((string)($r['graded_at'] ?? '-')) ?></div>
                            </div>
                        <?php endforeach; ?>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

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