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

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

$db = $conn ?? $mysqli ?? null;
if (!$db instanceof mysqli) {
    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 cv_table_exists(mysqli $db, string $table): bool {
    $safe = $db->real_escape_string($table);
    $res = $db->query("SHOW TABLES LIKE '{$safe}'");
    return $res && $res->num_rows > 0;
}

function cv_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}'");
    return $res && $res->num_rows > 0;
}

function cv_first_col(mysqli $db, string $table, array $cols): ?string {
    foreach ($cols as $col) {
        if (cv_col_exists($db, $table, $col)) return $col;
    }
    return null;
}

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

if (!cv_table_exists($db, 'lms_submissions')) {
    $error = 'lms_submissions table not found.';
} else {
    $hasAssessments = cv_table_exists($db, 'lms_assessments');
    $hasStudents    = cv_table_exists($db, 'studentss');
    $hasBatches     = cv_table_exists($db, 'batches');

    $studentNameCol = $hasStudents ? cv_first_col($db, 'studentss', ['full_name', 'name', 'student_name']) : null;
    $studentRegCol  = $hasStudents ? cv_first_col($db, 'studentss', ['center_reg_no', 'reference_no', 'reg_no']) : null;
    $batchNameCol   = $hasBatches ? cv_first_col($db, 'batches', ['batch_name', 'name', 'title']) : null;
    $batchCodeCol   = $hasBatches ? cv_first_col($db, 'batches', ['batch_code', 'code']) : null;

    $select = [
        "s.id",
        "s.student_id",
        "s.assessment_id",
        "s.batch_id",
        cv_col_exists($db, 'lms_submissions', 'submitted_at') ? "s.submitted_at" : "NULL AS submitted_at",
        cv_col_exists($db, 'lms_submissions', 'status') ? "s.status" : "'submitted' AS status",
        cv_col_exists($db, 'lms_submissions', 'workflow_status') ? "s.workflow_status" : "'submitted' AS workflow_status",
        cv_col_exists($db, 'lms_submissions', 'score') ? "s.score" : "NULL AS score",
        cv_col_exists($db, 'lms_submissions', 'provisional_score') ? "s.provisional_score" : "NULL AS provisional_score",
        cv_col_exists($db, 'lms_submissions', 'final_score') ? "s.final_score" : "NULL AS final_score",
        cv_col_exists($db, 'lms_submissions', 'verified_at') ? "s.verified_at" : "NULL AS verified_at",
        cv_col_exists($db, 'lms_submissions', 'verified_by') ? "s.verified_by" : "NULL AS verified_by"
    ];

    $joins = [];

    if ($hasAssessments) {
        $select[] = "a.title AS assessment_title";
        $select[] = cv_col_exists($db, 'lms_assessments', 'max_score') ? "a.max_score" : "100 AS max_score";
        $joins[] = "LEFT JOIN lms_assessments a ON a.id = s.assessment_id";
    } else {
        $select[] = "CONCAT('Assessment #', s.assessment_id) AS assessment_title";
        $select[] = "100 AS max_score";
    }

    if ($hasStudents && $studentNameCol) {
        $select[] = "st.`{$studentNameCol}` AS student_name";
        $joins[] = "LEFT JOIN studentss st ON st.id = s.student_id";
    } else {
        $select[] = "CONCAT('Student #', s.student_id) AS student_name";
    }

    if ($hasStudents && $studentRegCol) {
        $select[] = "st.`{$studentRegCol}` AS student_reg_no";
    } else {
        $select[] = "'' AS student_reg_no";
    }

    if ($hasBatches && $batchNameCol) {
        $select[] = "b.`{$batchNameCol}` AS batch_name";
        $joins[] = "LEFT JOIN batches b ON b.id = s.batch_id";
    } elseif ($hasBatches) {
        $select[] = "CONCAT('Batch #', s.batch_id) AS batch_name";
        $joins[] = "LEFT JOIN batches b ON b.id = s.batch_id";
    } else {
        $select[] = "CONCAT('Batch #', s.batch_id) AS batch_name";
    }

    if ($hasBatches && $batchCodeCol) {
        $select[] = "b.`{$batchCodeCol}` AS batch_code";
    } else {
        $select[] = "'' AS batch_code";
    }

    $where = [];
    $where[] = "LOWER(COALESCE(s.workflow_status,'')) IN ('graded_provisional','coord_approved','approved','released','submitted','')";

    $sql = "
        SELECT " . implode(",\n               ", $select) . "
        FROM lms_submissions s
        " . implode("\n        ", $joins) . "
        WHERE " . implode(' AND ', $where) . "
        ORDER BY s.id DESC
        LIMIT 500
    ";

    $res = $db->query($sql);
    if (!$res) {
        $error = 'Verification SQL error: ' . $db->error;
    } else {
        $rows = $res->fetch_all(MYSQLI_ASSOC);
    }
}

$pending = 0;
$approved = 0;
$released = 0;

foreach ($rows as $r) {
    $wf = strtolower((string)($r['workflow_status'] ?? ''));
    if (in_array($wf, ['', 'submitted', 'graded_provisional'], true)) $pending++;
    elseif (in_array($wf, ['coord_approved', 'approved'], true)) $approved++;
    elseif ($wf === 'released') $released++;
}

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">Verification</h1>
        <div class="page-subtitle">Review grading workflow and coordinator approval status</div>
    </div>

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

<?php if ($error): ?>
    <div class="alert alert-warning rounded-4"><strong>Notice:</strong> <?= h($error) ?></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">Records</div><div class="fs-3 fw-bold"><?= number_format(count($rows)) ?></div><i class="bi bi-shield-check fs-4 text-danger"></i></div></div>
    <div class="col-md-3"><div class="eden-card p-3"><div class="text-muted small">Pending</div><div class="fs-3 fw-bold"><?= number_format($pending) ?></div><i class="bi bi-hourglass fs-4 text-danger"></i></div></div>
    <div class="col-md-3"><div class="eden-card p-3"><div class="text-muted small">Approved</div><div class="fs-3 fw-bold"><?= number_format($approved) ?></div><i class="bi bi-check2-circle fs-4 text-danger"></i></div></div>
    <div class="col-md-3"><div class="eden-card p-3"><div class="text-muted small">Released</div><div class="fs-3 fw-bold"><?= number_format($released) ?></div><i class="bi bi-send-check fs-4 text-danger"></i></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="verifySearch" class="form-control border-start-0" placeholder="Search student, assessment, batch...">
            </div>
        </div>
        <div class="col-lg-3">
            <select id="workflowFilter" class="form-select">
                <option value="">All Workflow</option>
                <option value="submitted">Submitted</option>
                <option value="graded_provisional">Graded Provisional</option>
                <option value="coord_approved">Coordinator Approved</option>
                <option value="approved">Approved</option>
                <option value="released">Released</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(count($rows)) ?> Records</span>
        </div>
    </div>
</div>

<div class="eden-card overflow-hidden">
    <div class="table-responsive">
        <table class="table table-hover align-middle mb-0" id="verifyTable">
            <thead class="table-light">
                <tr>
                    <th>ID</th>
                    <th>Student</th>
                    <th>Assessment</th>
                    <th>Batch</th>
                    <th>Submitted</th>
                    <th>Score</th>
                    <th>Status</th>
                    <th>Workflow</th>
                </tr>
            </thead>
            <tbody>
            <?php if (!$rows): ?>
                <tr><td colspan="8" class="text-center text-muted py-5"><i class="bi bi-inbox fs-2 d-block mb-2"></i>No verification data found.</td></tr>
            <?php else: foreach ($rows as $r): ?>
                <?php
                    $wf = strtolower((string)($r['workflow_status'] ?? 'submitted'));
                    $status = strtolower((string)($r['status'] ?? 'submitted'));
                    $badge = match($wf) {
                        'released' => 'text-bg-success',
                        'approved' => 'text-bg-primary',
                        'coord_approved' => 'text-bg-info',
                        'graded_provisional' => 'text-bg-warning',
                        default => 'text-bg-light border'
                    };
                    $score = $r['final_score'] ?? $r['provisional_score'] ?? $r['score'] ?? null;
                ?>
                <tr data-workflow="<?= h($wf) ?>">
                    <td class="text-muted">#<?= (int)$r['id'] ?></td>
                    <td>
                        <div class="fw-bold"><?= h($r['student_name']) ?></div>
                        <?php if (!empty($r['student_reg_no'])): ?><div class="small text-muted"><?= h($r['student_reg_no']) ?></div><?php endif; ?>
                    </td>
                    <td><?= h($r['assessment_title']) ?></td>
                    <td>
                        <?= h($r['batch_name']) ?>
                        <?php if (!empty($r['batch_code'])): ?><div class="small text-muted"><?= h($r['batch_code']) ?></div><?php endif; ?>
                    </td>
                    <td><?= !empty($r['submitted_at']) ? h(date('Y-m-d H:i', strtotime((string)$r['submitted_at']))) : '<span class="text-muted">-</span>' ?></td>
                    <td><?= $score !== null && $score !== '' ? h($score) . ' / ' . h($r['max_score']) : '<span class="text-muted">Not marked</span>' ?></td>
                    <td><span class="badge text-bg-light border rounded-pill"><?= h(ucwords(str_replace('_', ' ', $status))) ?></span></td>
                    <td><span class="badge <?= h($badge) ?> rounded-pill"><?= h(ucwords(str_replace('_', ' ', $wf))) ?></span></td>
                </tr>
            <?php endforeach; endif; ?>
            </tbody>
        </table>
    </div>
</div>

<script>
document.addEventListener('DOMContentLoaded', function () {
    const q = document.getElementById('verifySearch');
    const f = document.getElementById('workflowFilter');
    const rows = Array.from(document.querySelectorAll('#verifyTable tbody tr[data-workflow]'));
    function run(){
        const search = (q.value || '').toLowerCase().trim();
        const wf = (f.value || '').toLowerCase().trim();
        rows.forEach(row => {
            const okSearch = search === '' || row.innerText.toLowerCase().includes(search);
            const okWf = wf === '' || row.dataset.workflow === wf;
            row.style.display = okSearch && okWf ? '' : 'none';
        });
    }
    q.addEventListener('input', run);
    f.addEventListener('change', run);
});
</script>

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