<?php
// /elms/coordinator/release.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 crl_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 crl_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 crl_first_col(mysqli $db, string $table, array $cols): ?string {
    foreach ($cols as $col) {
        if (crl_col_exists($db, $table, $col)) {
            return $col;
        }
    }
    return null;
}

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

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

$hasAssessments = crl_table_exists($db, 'lms_assessments');
$hasSubmissions = crl_table_exists($db, 'lms_submissions');
$hasCourses     = crl_table_exists($db, 'courses');
$hasModules     = crl_table_exists($db, 'course_modules');
$hasBatches     = crl_table_exists($db, 'batches');
$hasAssessmentBatches = crl_table_exists($db, 'lms_assessment_batches');

if (!$hasAssessments) {
    $error = 'lms_assessments table not found.';
} else {
    $assessmentTitleCol = crl_first_col($db, 'lms_assessments', ['title', 'name']) ?? 'id';

    $hasDueAt      = crl_col_exists($db, 'lms_assessments', 'due_at');
    $hasReleaseAt  = crl_col_exists($db, 'lms_assessments', 'release_at');
    $hasActive     = crl_col_exists($db, 'lms_assessments', 'active');
    $hasMaxScore   = crl_col_exists($db, 'lms_assessments', 'max_score');

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

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

    $joins = [];

    if ($hasCourses && $courseNameCol && crl_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 && crl_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 ($hasSubmissions) {
        $hasWorkflow   = crl_col_exists($db, 'lms_submissions', 'workflow_status');
        $hasReleasedAt = crl_col_exists($db, 'lms_submissions', 'released_at');
        $hasUpdatedAt  = crl_col_exists($db, 'lms_submissions', 'updated_at');
        $hasSubmittedAt = crl_col_exists($db, 'lms_submissions', 'submitted_at');

        $releasedDateParts = [];
        if ($hasReleasedAt) $releasedDateParts[] = 's.released_at';
        if ($hasUpdatedAt) $releasedDateParts[] = 's.updated_at';
        if ($hasSubmittedAt) $releasedDateParts[] = 's.submitted_at';

        $releasedDateExpr = $releasedDateParts
            ? 'COALESCE(' . implode(',', $releasedDateParts) . ')'
            : 'NULL';

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

        if ($hasWorkflow) {
            $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 reviewed_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 ('under_review','coord_review','coordinator_review','admin_review') 
                        THEN 1 ELSE 0 
                    END
                ) AS under_review_count
            ";

            $select[] = "
                MAX(
                    CASE 
                        WHEN LOWER(COALESCE(s.workflow_status,'')) IN ('released','published') 
                        THEN {$releasedDateExpr}
                        ELSE NULL 
                    END
                ) AS latest_released_at
            ";
        } else {
            $select[] = "0 AS reviewed_count";
            $select[] = "0 AS approved_count";
            $select[] = "0 AS released_count";
            $select[] = "0 AS under_review_count";
            $select[] = "NULL AS latest_released_at";
        }

        $select[] = "0 AS graded_status_count";

        $joins[] = "LEFT JOIN lms_submissions s ON s.assessment_id = a.id";
    } else {
        $select[] = "0 AS submission_count";
        $select[] = "0 AS reviewed_count";
        $select[] = "0 AS approved_count";
        $select[] = "0 AS released_count";
        $select[] = "0 AS under_review_count";
        $select[] = "NULL AS latest_released_at";
        $select[] = "0 AS graded_status_count";
    }

    $where = [];
    $params = [];
    $types = '';

    if (
        $hasAssessmentBatches &&
        $hasBatches &&
        crl_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), '?'));
            $where[] = "(b.branch_id IN ({$ph}) OR b.branch_id IS NULL OR b.id IS NULL)";
            foreach ($branchIds as $bid) {
                $params[] = $bid;
                $types .= 'i';
            }
        }
    }

    $sql = "
        SELECT " . implode(",\n               ", $select) . "
        FROM lms_assessments a
        " . implode("\n        ", $joins) . "
    ";

    if ($where) {
        $sql .= " WHERE " . implode(' AND ', $where);
    }

    $orderDue = $hasDueAt ? "a.due_at ASC," : "";
    $sql .= "
        GROUP BY a.id
        ORDER BY {$orderDue} a.id DESC
        LIMIT 400
    ";

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

    if (!$stmt) {
        $error = 'Release SQL error: ' . $db->error;
    } else {
        if ($params) {
            $stmt->bind_param($types, ...$params);
        }

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

$awaiting = 0;
$released = 0;
$upcoming = 0;
$inactive = 0;

foreach ($rows as $r) {
    $active = (int)($r['active'] ?? 1) === 1;

    $submissionCount = (int)($r['submission_count'] ?? 0);
    $approvedCount   = (int)($r['approved_count'] ?? 0);
    $releasedCount   = (int)($r['released_count'] ?? 0);
    $underReview     = (int)($r['under_review_count'] ?? 0);

    if (!$active) {
        $inactive++;
    } elseif ($submissionCount > 0 && $releasedCount >= $submissionCount) {
        $released++;
    } elseif ($approvedCount > 0 || $underReview > 0) {
        $awaiting++;
    } else {
        $upcoming++;
    }
}

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">Release Center</h1>
        <div class="page-subtitle">Check assessments awaiting release and result publishing 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/approval.php" class="btn btn-eden">
            <i class="bi bi-send-check me-1"></i> Approval Action
        </a>
    </div>
</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">Awaiting Release</div>
            <div class="fs-3 fw-bold"><?= number_format($awaiting) ?></div>
            <i class="bi bi-hourglass-split 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-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">Upcoming</div>
            <div class="fs-3 fw-bold"><?= number_format($upcoming) ?></div>
            <i class="bi bi-calendar-event fs-4 text-danger"></i>
        </div>
    </div>

    <div class="col-md-3">
        <div class="eden-card p-3">
            <div class="text-muted small">Inactive</div>
            <div class="fs-3 fw-bold"><?= number_format($inactive) ?></div>
            <i class="bi bi-pause-circle 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="releaseSearch" class="form-control border-start-0" placeholder="Search assessment, course, module, status...">
            </div>
        </div>

        <div class="col-lg-3">
            <select id="releaseFilter" class="form-select">
                <option value="">All Release Status</option>
                <option value="awaiting">Awaiting Release</option>
                <option value="released">Released</option>
                <option value="upcoming">Upcoming</option>
                <option value="inactive">Inactive</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)) ?> Assessments
            </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>Assessment Release Status</strong>
        <span class="text-muted small">Coordinator view</span>
    </div>

    <div class="table-responsive">
        <table class="table table-hover align-middle mb-0" id="releaseTable">
            <thead class="table-light">
                <tr>
                    <th style="width:70px;">ID</th>
                    <th>Assessment</th>
                    <th>Course / Module</th>
                    <th>Due Date</th>
                    <th>Latest Released</th>
                    <th class="text-center">Submissions</th>
                    <th class="text-center">Reviewed</th>
                    <th class="text-center">Released</th>
                    <th>Status</th>
                </tr>
            </thead>

            <tbody>
            <?php if (!$rows): ?>
                <tr>
                    <td colspan="9" class="text-center text-muted py-5">
                        <i class="bi bi-inbox fs-2 d-block mb-2"></i>
                        No release data found.
                    </td>
                </tr>
            <?php else: ?>
                <?php foreach ($rows as $r): ?>
                    <?php
                        $active = (int)($r['active'] ?? 1) === 1;
                        $dueAtRaw = (string)($r['due_at'] ?? '');
                        $releaseAtRaw = (string)($r['latest_released_at'] ?? '');

                        $submissionCount = (int)($r['submission_count'] ?? 0);
                        $approvedCount   = (int)($r['approved_count'] ?? 0);
                        $releasedCount   = (int)($r['released_count'] ?? 0);
                        $underReview     = (int)($r['under_review_count'] ?? 0);

                        if (!$active) {
                            $statusText = 'Inactive';
                            $statusKey = 'inactive';
                            $badgeClass = 'text-bg-secondary';
                        } elseif ($submissionCount > 0 && $releasedCount >= $submissionCount) {
                            $statusText = 'Released';
                            $statusKey = 'released';
                            $badgeClass = 'text-bg-success';
                        } elseif ($approvedCount > 0) {
                            $statusText = 'Ready to Release';
                            $statusKey = 'awaiting';
                            $badgeClass = 'text-bg-danger';
                        } elseif ($underReview > 0) {
                            $statusText = 'Under Review';
                            $statusKey = 'awaiting';
                            $badgeClass = 'text-bg-warning';
                        } else {
                            $statusText = 'Upcoming';
                            $statusKey = 'upcoming';
                            $badgeClass = 'text-bg-warning';
                        }

                        $reviewedCount = max(
                            (int)($r['reviewed_count'] ?? 0),
                            (int)($r['graded_status_count'] ?? 0)
                        );

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

                    <tr data-status="<?= h($statusKey) ?>">
                        <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>
                            <?= $dueAtRaw !== '' ? h(date('Y-m-d H:i', strtotime($dueAtRaw))) : '<span class="text-muted">-</span>' ?>
                        </td>

                        <td>
                            <?= $releaseAtRaw !== '' ? h(date('Y-m-d H:i', strtotime($releaseAtRaw))) : '<span class="text-muted">Not released</span>' ?>
                        </td>

                        <td class="text-center">
                            <span class="badge text-bg-danger rounded-pill">
                                <?= (int)($r['submission_count'] ?? 0) ?>
                            </span>
                        </td>

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

                        <td class="text-center">
                            <span class="badge text-bg-success rounded-pill">
                                <?= (int)($r['released_count'] ?? 0) ?>
                            </span>
                        </td>

                        <td>
                            <span class="badge <?= h($badgeClass) ?> rounded-pill">
                                <?= h($statusText) ?>
                            </span>
                        </td>
                    </tr>
                <?php endforeach; ?>
            <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

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

    function filterTable() {
        const q = (searchInput.value || '').toLowerCase().trim();
        const st = (releaseFilter.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);
    releaseFilter.addEventListener('change', filterTable);
});
</script>

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