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

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

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

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, 'UTF-8');
    }
}

function cm_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 cm_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 cm_first_col(mysqli $db, string $table, array $cols): ?string {
    foreach ($cols as $col) {
        if (cm_col_exists($db, $table, $col)) {
            return $col;
        }
    }
    return null;
}

function cm_fetch_all_stmt(mysqli_stmt $stmt): array {
    $stmt->execute();
    $res = $stmt->get_result();
    if (!$res) return [];
    return $res->fetch_all(MYSQLI_ASSOC);
}

function cm_fmt_dt($date): string {
    $date = trim((string)$date);

    if ($date === '' || $date === '0000-00-00' || $date === '0000-00-00 00:00:00') {
        return '<span class="text-muted">-</span>';
    }

    $time = strtotime($date);
    return $time ? h(date('Y-m-d H:i', $time)) : h($date);
}

$error = '';
$rows = [];

$hasAssessments = cm_table_exists($db, 'lms_assessments');
$hasBatches     = cm_table_exists($db, 'batches');
$hasMarkers     = cm_table_exists($db, 'lms_assessment_markers');
$hasSubmissions = cm_table_exists($db, 'lms_submissions');
$hasCourses     = cm_table_exists($db, 'courses');
$hasModules     = cm_table_exists($db, 'course_modules');

if (!$hasAssessments) {
    $error = 'lms_assessments table not found.';
} else {
    /*
    |--------------------------------------------------------------------------
    | Ensure workflow columns
    |--------------------------------------------------------------------------
    */

    if ($hasSubmissions) {
        if (!cm_col_exists($db, 'lms_submissions', 'workflow_status')) {
            $db->query("ALTER TABLE lms_submissions ADD COLUMN workflow_status VARCHAR(50) NOT NULL DEFAULT 'submitted'");
        }

        if (!cm_col_exists($db, 'lms_submissions', 'final_score')) {
            $db->query("ALTER TABLE lms_submissions ADD COLUMN final_score DECIMAL(10,2) NULL");
        }

        if (!cm_col_exists($db, 'lms_submissions', 'lecturer_feedback')) {
            $db->query("ALTER TABLE lms_submissions ADD COLUMN lecturer_feedback TEXT NULL");
        }

        if (!cm_col_exists($db, 'lms_submissions', 'released_at')) {
            $db->query("ALTER TABLE lms_submissions ADD COLUMN released_at DATETIME NULL");
        }
    }

    $assessmentTitleCol = cm_first_col($db, 'lms_assessments', ['title', 'name']) ?? 'id';

    $courseNameCol = $hasCourses ? cm_first_col($db, 'courses', ['course_name', 'name', 'title']) : null;
    $moduleNameCol = $hasModules ? cm_first_col($db, 'course_modules', ['title', 'module_name', 'name']) : null;

    $select = [
        "a.id AS assessment_id",
        "a.`{$assessmentTitleCol}` AS assessment_title",
        cm_col_exists($db, 'lms_assessments', 'available_from') ? "a.available_from" : "NULL AS available_from",
        cm_col_exists($db, 'lms_assessments', 'due_at') ? "a.due_at" : "NULL AS due_at",
        cm_col_exists($db, 'lms_assessments', 'release_at') ? "a.release_at" : "NULL AS release_at",
        cm_col_exists($db, 'lms_assessments', 'active') ? "a.active" : "1 AS active",
        cm_col_exists($db, 'lms_assessments', 'max_score') ? "a.max_score" : "100 AS max_score"
    ];

    $joins = [];

    if ($hasCourses && $courseNameCol && cm_col_exists($db, 'lms_assessments', 'course_id')) {
        $select[] = "c.`{$courseNameCol}` AS course_name";
        $joins[] = "LEFT JOIN courses c ON c.id = a.course_id";
    } else {
        $select[] = "'' AS course_name";
    }

    if ($hasModules && $moduleNameCol && cm_col_exists($db, 'lms_assessments', 'module_id')) {
        $select[] = "cm.`{$moduleNameCol}` AS module_name";
        $joins[] = "LEFT JOIN course_modules cm ON cm.id = a.module_id";
    } else {
        $select[] = "'' AS module_name";
    }

    if ($hasMarkers) {
        $select[] = "COUNT(DISTINCT m.lecturer_id) AS marker_count";
        $joins[] = "LEFT JOIN lms_assessment_markers m ON m.assessment_id = a.id";
    } else {
        $select[] = "NULL AS marker_count";
    }

    if ($hasSubmissions) {
        $select[] = "COUNT(DISTINCT s.id) AS submission_count";

        $select[] = "
            SUM(
                CASE 
                    WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('submitted','pending') 
                    THEN 1 ELSE 0 
                END
            ) AS submitted_count
        ";

        $select[] = "
            SUM(
                CASE 
                    WHEN LOWER(COALESCE(s.workflow_status,'')) = 'needs_correction' 
                    THEN 1 ELSE 0 
                END
            ) AS correction_count
        ";

        $select[] = "
            SUM(
                CASE 
                    WHEN LOWER(COALESCE(s.workflow_status,'')) = 'graded_provisional' 
                    THEN 1 ELSE 0 
                END
            ) AS provisional_count
        ";

        $select[] = "
            SUM(
                CASE 
                    WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('under_review','coord_review','coordinator_review','admin_review') 
                    THEN 1 ELSE 0 
                END
            ) AS review_count
        ";

        $select[] = "
            SUM(
                CASE 
                    WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('coord_approved','approved') 
                    THEN 1 ELSE 0 
                END
            ) AS approved_count
        ";

        $select[] = "
            SUM(
                CASE 
                    WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('released','published') 
                    THEN 1 ELSE 0 
                END
            ) AS released_count
        ";

        $select[] = "
            SUM(
                CASE 
                    WHEN LOWER(COALESCE(s.workflow_status,'')) IN 
                    ('graded_provisional','under_review','coord_review','coordinator_review','admin_review','coord_approved','approved','released','published') 
                    THEN 1 ELSE 0 
                END
            ) AS marked_or_reviewed_count
        ";

        $select[] = "
            MAX(
                CASE 
                    WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('graded_provisional','under_review','coord_approved','approved','released','published') 
                    THEN COALESCE(s.graded_at, s.updated_at, s.submitted_at)
                    ELSE NULL
                END
            ) AS last_marked_at
        ";

        $joins[] = "LEFT JOIN lms_submissions s ON s.assessment_id = a.id";
    } else {
        $select[] = "0 AS submission_count";
        $select[] = "0 AS submitted_count";
        $select[] = "0 AS correction_count";
        $select[] = "0 AS provisional_count";
        $select[] = "0 AS review_count";
        $select[] = "0 AS approved_count";
        $select[] = "0 AS released_count";
        $select[] = "0 AS marked_or_reviewed_count";
        $select[] = "NULL AS last_marked_at";
    }

    /*
    |--------------------------------------------------------------------------
    | Branch scope
    |--------------------------------------------------------------------------
    */

    $scopeSql = '';
    $scopeParams = [];
    $scopeTypes = '';

    if (
        cm_table_exists($db, 'lms_assessment_batches') &&
        $hasBatches &&
        cm_col_exists($db, 'batches', 'branch_id')
    ) {
        $joins[] = "LEFT JOIN lms_assessment_batches ab ON ab.assessment_id = a.id";
        $joins[] = "LEFT JOIN batches b ON b.id = ab.batch_id";

        $branchIdsRaw = $_SESSION['branch_ids'] ?? ($_SESSION['user']['branch_ids'] ?? []);
        $branchIds = [];

        if (is_array($branchIdsRaw)) {
            $branchIds = array_values(array_filter(array_map('intval', $branchIdsRaw)));
        } elseif (is_string($branchIdsRaw) && trim($branchIdsRaw) !== '') {
            $decoded = json_decode($branchIdsRaw, true);
            if (is_array($decoded)) {
                $branchIds = array_values(array_filter(array_map('intval', $decoded)));
            } else {
                $branchIds = array_values(array_filter(array_map('intval', explode(',', $branchIdsRaw))));
            }
        }

        $singleBranchId = $_SESSION['branch_id'] ?? ($_SESSION['user']['branch_id'] ?? null);
        if (!$branchIds && !empty($singleBranchId)) {
            $branchIds[] = (int)$singleBranchId;
        }

        $branchIds = array_values(array_unique(array_filter($branchIds)));

        if ($branchIds) {
            $ph = implode(',', array_fill(0, count($branchIds), '?'));
            $scopeSql = " WHERE (b.branch_id IN ({$ph}) OR b.branch_id IS NULL OR b.id IS NULL) ";
            foreach ($branchIds as $bid) {
                $scopeParams[] = $bid;
                $scopeTypes .= 'i';
            }
        }
    }

    $sql = "
        SELECT " . implode(",\n               ", $select) . "
        FROM lms_assessments a
        " . implode("\n        ", $joins) . "
        {$scopeSql}
        GROUP BY a.id
        ORDER BY a.id DESC
        LIMIT 300
    ";

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

    if (!$stmt) {
        $error = 'Marking SQL error: ' . $db->error;
    } else {
        if ($scopeParams) {
            $stmt->bind_param($scopeTypes, ...$scopeParams);
        }

        $rows = cm_fetch_all_stmt($stmt);
        $stmt->close();
    }
}

/*
|--------------------------------------------------------------------------
| KPI totals
|--------------------------------------------------------------------------
*/

$totalAssessments = count($rows);
$totalMarkers = 0;
$totalSubmissions = 0;
$totalSubmitted = 0;
$totalCorrection = 0;
$totalProvisional = 0;
$totalReview = 0;
$totalApproved = 0;
$totalReleased = 0;
$totalMarkedOrReviewed = 0;

foreach ($rows as $r) {
    if ($r['marker_count'] !== null) {
        $totalMarkers += (int)$r['marker_count'];
    }

    $totalSubmissions += (int)($r['submission_count'] ?? 0);
    $totalSubmitted += (int)($r['submitted_count'] ?? 0);
    $totalCorrection += (int)($r['correction_count'] ?? 0);
    $totalProvisional += (int)($r['provisional_count'] ?? 0);
    $totalReview += (int)($r['review_count'] ?? 0);
    $totalApproved += (int)($r['approved_count'] ?? 0);
    $totalReleased += (int)($r['released_count'] ?? 0);
    $totalMarkedOrReviewed += (int)($r['marked_or_reviewed_count'] ?? 0);
}

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

<div class="d-flex justify-content-between align-items-start mb-4">
    <div>
        <h1 class="page-title">Marking</h1>
        <div class="page-subtitle">View markers, assessment marking progress and workflow status</div>
    </div>

    <div class="d-flex gap-2">
        <a href="/elms/coordinator/dashboard.php" class="btn btn-light border rounded-4">
            <i class="bi bi-arrow-left me-1"></i> Dashboard
        </a>

        <a href="/elms/coordinator/submissions.php" class="btn btn-light border rounded-4">
            <i class="bi bi-inboxes me-1"></i> Submissions
        </a>

        <a href="/elms/coordinator/approval.php" class="btn btn-eden">
            <i class="bi bi-send-check me-1"></i> Approval Center
        </a>
    </div>
</div>

<?php if ($error): ?>
    <div class="alert alert-warning rounded-4">
        <strong>Notice:</strong> <?= h($error) ?>
    </div>
<?php endif; ?>

<?php if (!$hasMarkers): ?>
    <div class="alert alert-info rounded-4">
        <strong>Note:</strong> <code>lms_assessment_markers</code> table not found. Marker count will show as “—”.
    </div>
<?php endif; ?>

<div class="row g-3 mb-4">
    <div class="col-md-3">
        <div class="eden-card p-3">
            <div class="text-muted small">Assessments</div>
            <div class="fs-3 fw-bold"><?= number_format($totalAssessments) ?></div>
            <i class="bi bi-clipboard-check fs-4 text-danger"></i>
        </div>
    </div>

    <div class="col-md-3">
        <div class="eden-card p-3">
            <div class="text-muted small">Assigned Markers</div>
            <div class="fs-3 fw-bold"><?= $hasMarkers ? number_format($totalMarkers) : '—' ?></div>
            <i class="bi bi-person-badge fs-4 text-danger"></i>
        </div>
    </div>

    <div class="col-md-3">
        <div class="eden-card p-3">
            <div class="text-muted small">Submissions</div>
            <div class="fs-3 fw-bold"><?= number_format($totalSubmissions) ?></div>
            <i class="bi bi-inboxes fs-4 text-danger"></i>
        </div>
    </div>

    <div class="col-md-3">
        <div class="eden-card p-3">
            <div class="text-muted small">Marked / Reviewed</div>
            <div class="fs-3 fw-bold"><?= number_format($totalMarkedOrReviewed) ?></div>
            <i class="bi bi-check2-circle fs-4 text-danger"></i>
        </div>
    </div>
</div>

<div class="row g-3 mb-4">
    <div class="col-md-2">
        <div class="eden-card p-3">
            <div class="text-muted small">Submitted</div>
            <div class="fs-3 fw-bold"><?= number_format($totalSubmitted) ?></div>
        </div>
    </div>

    <div class="col-md-2">
        <div class="eden-card p-3">
            <div class="text-muted small">Correction</div>
            <div class="fs-3 fw-bold"><?= number_format($totalCorrection) ?></div>
        </div>
    </div>

    <div class="col-md-2">
        <div class="eden-card p-3">
            <div class="text-muted small">Provisional</div>
            <div class="fs-3 fw-bold"><?= number_format($totalProvisional) ?></div>
        </div>
    </div>

    <div class="col-md-2">
        <div class="eden-card p-3">
            <div class="text-muted small">Review</div>
            <div class="fs-3 fw-bold"><?= number_format($totalReview) ?></div>
        </div>
    </div>

    <div class="col-md-2">
        <div class="eden-card p-3">
            <div class="text-muted small">Approved</div>
            <div class="fs-3 fw-bold"><?= number_format($totalApproved) ?></div>
        </div>
    </div>

    <div class="col-md-2">
        <div class="eden-card p-3">
            <div class="text-muted small">Released</div>
            <div class="fs-3 fw-bold"><?= number_format($totalReleased) ?></div>
        </div>
    </div>
</div>

<div class="eden-card p-3 mb-4">
    <div class="row g-3 align-items-center">
        <div class="col-lg-6">
            <div class="input-group">
                <span class="input-group-text bg-white border-end-0">
                    <i class="bi bi-search"></i>
                </span>
                <input type="text" id="markingSearch" class="form-control border-start-0" placeholder="Search assessment, course, module, marking status...">
            </div>
        </div>

        <div class="col-lg-3">
            <select id="markingFilter" class="form-select">
                <option value="">All Marking Status</option>
                <option value="no_markers">No Markers</option>
                <option value="not_started">Not Started</option>
                <option value="marking_pending">Marking Pending</option>
                <option value="in_progress">In Progress</option>
                <option value="under_review">Under Review</option>
                <option value="ready_release">Ready to Release</option>
                <option value="released">Released</option>
                <option value="completed">Completed</option>
            </select>
        </div>

        <div class="col-lg-3 text-lg-end">
            <span class="badge text-bg-danger rounded-pill px-3 py-2">
                <?= number_format($totalAssessments) ?> Records
            </span>
        </div>
    </div>
</div>

<div class="eden-card overflow-hidden">
    <div class="px-3 py-3 border-bottom bg-white d-flex justify-content-between align-items-center">
        <strong>Markers & Marking Progress</strong>
        <span class="text-muted small">Workflow aligned</span>
    </div>

    <div class="table-responsive">
        <table class="table table-hover align-middle mb-0" id="markingTable">
            <thead class="table-light">
                <tr>
                    <th style="width:70px;">ID</th>
                    <th>Assessment</th>
                    <th>Course / Module</th>
                    <th>Open</th>
                    <th>Due</th>
                    <th class="text-center">Markers</th>
                    <th class="text-center">Submissions</th>
                    <th class="text-center">Marked/Reviewed</th>
                    <th class="text-center">Review</th>
                    <th class="text-center">Approved</th>
                    <th class="text-center">Released</th>
                    <th>Progress</th>
                    <th>Last Marked</th>
                    <th>Status</th>
                </tr>
            </thead>

            <tbody>
            <?php if (!$rows): ?>
                <tr>
                    <td colspan="14" class="text-center text-muted py-5">
                        <i class="bi bi-inbox fs-2 d-block mb-2"></i>
                        No marking data found.
                    </td>
                </tr>
            <?php else: ?>
                <?php foreach ($rows as $r): ?>
                    <?php
                        $markers = $r['marker_count'];
                        $submissionCount = (int)($r['submission_count'] ?? 0);
                        $markedReviewed = (int)($r['marked_or_reviewed_count'] ?? 0);
                        $submittedCount = (int)($r['submitted_count'] ?? 0);
                        $correctionCount = (int)($r['correction_count'] ?? 0);
                        $reviewCount = (int)($r['review_count'] ?? 0);
                        $approvedCount = (int)($r['approved_count'] ?? 0);
                        $releasedCount = (int)($r['released_count'] ?? 0);

                        $percent = $submissionCount > 0 ? round(($markedReviewed / $submissionCount) * 100) : 0;

                        if ($markers !== null && (int)$markers <= 0) {
                            $markingStatus = 'No Markers';
                            $markingKey = 'no_markers';
                            $badgeClass = 'text-bg-danger';
                        } elseif ($submissionCount <= 0) {
                            $markingStatus = 'Not Started';
                            $markingKey = 'not_started';
                            $badgeClass = 'text-bg-secondary';
                        } elseif ($submittedCount > 0 || $correctionCount > 0) {
                            $markingStatus = 'Marking Pending';
                            $markingKey = 'marking_pending';
                            $badgeClass = 'text-bg-warning';
                        } elseif ($reviewCount > 0) {
                            $markingStatus = 'Under Review';
                            $markingKey = 'under_review';
                            $badgeClass = 'text-bg-info';
                        } elseif ($approvedCount > 0) {
                            $markingStatus = 'Ready to Release';
                            $markingKey = 'ready_release';
                            $badgeClass = 'text-bg-primary';
                        } elseif ($submissionCount > 0 && $releasedCount >= $submissionCount) {
                            $markingStatus = 'Released';
                            $markingKey = 'released';
                            $badgeClass = 'text-bg-success';
                        } elseif ($markedReviewed > 0) {
                            $markingStatus = 'In Progress';
                            $markingKey = 'in_progress';
                            $badgeClass = 'text-bg-warning';
                        } elseif ($markedReviewed >= $submissionCount && $submissionCount > 0) {
                            $markingStatus = 'Completed';
                            $markingKey = 'completed';
                            $badgeClass = 'text-bg-success';
                        } else {
                            $markingStatus = 'Not Started';
                            $markingKey = 'not_started';
                            $badgeClass = 'text-bg-secondary';
                        }

                        $courseName = trim((string)($r['course_name'] ?? ''));
                        $moduleName = trim((string)($r['module_name'] ?? ''));
                    ?>

                    <tr data-status="<?= h($markingKey) ?>">
                        <td class="text-muted">#<?= (int)$r['assessment_id'] ?></td>

                        <td>
                            <div class="fw-bold"><?= h($r['assessment_title'] ?? 'Untitled Assessment') ?></div>
                            <div class="small text-muted">
                                Max Score: <?= h((string)($r['max_score'] ?? '100')) ?>
                            </div>
                        </td>

                        <td>
                            <?php if ($courseName !== ''): ?>
                                <div class="fw-semibold"><?= h($courseName) ?></div>
                            <?php else: ?>
                                <span class="text-muted">No course</span>
                            <?php endif; ?>

                            <?php if ($moduleName !== ''): ?>
                                <div class="small text-muted"><?= h($moduleName) ?></div>
                            <?php endif; ?>
                        </td>

                        <td><?= cm_fmt_dt($r['available_from'] ?? '') ?></td>

                        <td><?= cm_fmt_dt($r['due_at'] ?? '') ?></td>

                        <td class="text-center">
                            <?php if ($markers === null): ?>
                                <span class="text-muted">—</span>
                            <?php else: ?>
                                <span class="badge text-bg-light border rounded-pill">
                                    <?= (int)$markers ?>
                                </span>
                            <?php endif; ?>
                        </td>

                        <td class="text-center">
                            <span class="badge text-bg-danger rounded-pill">
                                <?= $submissionCount ?>
                            </span>
                        </td>

                        <td class="text-center">
                            <span class="badge text-bg-success rounded-pill">
                                <?= $markedReviewed ?>
                            </span>
                        </td>

                        <td class="text-center">
                            <span class="badge text-bg-info rounded-pill">
                                <?= $reviewCount ?>
                            </span>
                        </td>

                        <td class="text-center">
                            <span class="badge text-bg-primary rounded-pill">
                                <?= $approvedCount ?>
                            </span>
                        </td>

                        <td class="text-center">
                            <span class="badge text-bg-success rounded-pill">
                                <?= $releasedCount ?>
                            </span>
                        </td>

                        <td style="min-width:160px;">
                            <div class="d-flex justify-content-between small mb-1">
                                <span><?= $markedReviewed ?>/<?= $submissionCount ?></span>
                                <span><?= $percent ?>%</span>
                            </div>
                            <div class="progress" style="height:8px;">
                                <div class="progress-bar" role="progressbar" style="width: <?= $percent ?>%;"></div>
                            </div>
                        </td>

                        <td>
                            <?= cm_fmt_dt($r['last_marked_at'] ?? '') ?>
                        </td>

                        <td>
                            <span class="badge <?= h($badgeClass) ?> rounded-pill">
                                <?= h($markingStatus) ?>
                            </span>

                            <?php if ($approvedCount > 0): ?>
                                <div class="mt-1">
                                    <a href="/elms/coordinator/approval.php" class="small fw-bold text-decoration-none">
                                        Release now
                                    </a>
                                </div>
                            <?php elseif ($reviewCount > 0): ?>
                                <div class="mt-1">
                                    <a href="/elms/coordinator/approval.php" class="small fw-bold text-decoration-none">
                                        Review
                                    </a>
                                </div>
                            <?php endif; ?>
                        </td>
                    </tr>
                <?php endforeach; ?>
            <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

<script>
document.addEventListener('DOMContentLoaded', function () {
    const searchInput = document.getElementById('markingSearch');
    const markingFilter = document.getElementById('markingFilter');
    const rows = Array.from(document.querySelectorAll('#markingTable tbody tr[data-status]'));

    function filterTable() {
        const q = (searchInput.value || '').toLowerCase().trim();
        const st = (markingFilter.value || '').toLowerCase().trim();

        rows.forEach(row => {
            const text = row.innerText.toLowerCase();
            const rowStatus = (row.dataset.status || '').toLowerCase();

            const matchSearch = q === '' || text.includes(q);
            const matchStatus = st === '' || rowStatus === st;

            row.style.display = (matchSearch && matchStatus) ? '' : 'none';
        });
    }

    searchInput.addEventListener('input', filterTable);
    markingFilter.addEventListener('change', filterTable);
});
</script>

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