<?php
// coordinator/planner.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 cp_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 cp_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 cp_first_col(mysqli $db, string $table, array $cols): ?string {
    foreach ($cols as $col) {
        if (cp_col_exists($db, $table, $col)) {
            return $col;
        }
    }
    return null;
}

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

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

$hasBatches     = cp_table_exists($db, 'batches');
$hasCourses     = cp_table_exists($db, 'courses');
$hasAssessments = cp_table_exists($db, 'lms_assessments');
$hasAssessmentBatches = cp_table_exists($db, 'lms_assessment_batches');

if (!$hasBatches && !$hasAssessments) {
    $error = 'batches or lms_assessments table not found.';
} else {
    $items = [];

    /*
      Branch scope:
      batches.branch_id තිබ්බොත් coordinator branch_ids filter කරනවා.
      But branch_id NULL නම් hide නොවෙන්න OR branch_id IS NULL දාන්නේ.
    */
    $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)));

    /*
      1) Batch planner items
    */
    if ($hasBatches) {
        $batchNameCol = cp_first_col($db, 'batches', ['batch_name', 'name', 'title']);
        $batchCodeCol = cp_first_col($db, 'batches', ['batch_code', 'code']);
        $startCol     = cp_first_col($db, 'batches', ['batch_start_date', 'start_date', 'registration_open_date']);
        $endCol       = cp_first_col($db, 'batches', ['batch_end_date', 'end_date', 'registration_close_date']);
        $statusCol    = cp_first_col($db, 'batches', ['status']);
        $deliveryCol  = cp_first_col($db, 'batches', ['delivery_mode']);
        $hasBranchId  = cp_col_exists($db, 'batches', 'branch_id');
        $hasCourseId  = cp_col_exists($db, 'batches', 'course_id');

        $select = [
            "b.id",
            $batchNameCol ? "b.`{$batchNameCol}` AS title" : "CONCAT('Batch #', b.id) AS title",
            $batchCodeCol ? "b.`{$batchCodeCol}` AS code" : "'' AS code",
            $startCol ? "b.`{$startCol}` AS start_date" : "NULL AS start_date",
            $endCol ? "b.`{$endCol}` AS end_date" : "NULL AS end_date",
            $statusCol ? "b.`{$statusCol}` AS status" : "'Active' AS status",
            $deliveryCol ? "b.`{$deliveryCol}` AS delivery_mode" : "'' AS delivery_mode",
            "'batch' AS item_type"
        ];

        $joins = [];

        if ($hasCourseId && $hasCourses) {
            $courseNameCol = cp_first_col($db, 'courses', ['course_name', 'name', 'title']);
            if ($courseNameCol) {
                $select[] = "c.`{$courseNameCol}` AS course_name";
                $joins[] = "LEFT JOIN courses c ON c.id = b.course_id";
            } else {
                $select[] = "'' AS course_name";
            }
        } else {
            $select[] = "'' AS course_name";
        }

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

        if ($hasBranchId && $branchIds) {
            $ph = implode(',', array_fill(0, count($branchIds), '?'));
            $where[] = "(b.branch_id IN ({$ph}) OR b.branch_id IS NULL)";
            foreach ($branchIds as $bid) {
                $params[] = $bid;
                $types .= 'i';
            }
        }

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

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

        $sql .= " ORDER BY b.id DESC LIMIT 300";

        $stmt = $db->prepare($sql);
        if ($stmt) {
            if ($params) {
                $stmt->bind_param($types, ...$params);
            }

            $batchRows = cp_fetch_all_stmt($stmt);
            $stmt->close();

            foreach ($batchRows as $r) {
                $items[] = $r;
            }
        }
    }

    /*
      2) Assessment planner items
    */
    if ($hasAssessments) {
        $select = [
            "a.id",
            "a.title AS title",
            "'' AS code",
            cp_col_exists($db, 'lms_assessments', 'available_from') ? "a.available_from AS start_date" : "NULL AS start_date",
            cp_col_exists($db, 'lms_assessments', 'due_at') ? "a.due_at AS end_date" : "NULL AS end_date",
            cp_col_exists($db, 'lms_assessments', 'active') ? "IF(a.active=1,'Active','Inactive') AS status" : "'Active' AS status",
            "'' AS delivery_mode",
            "'assessment' AS item_type"
        ];

        $joins = [];

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

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

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

            $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);
        }

        $sql .= " GROUP BY a.id ORDER BY a.id DESC LIMIT 300";

        $stmt = $db->prepare($sql);
        if ($stmt) {
            if ($params) {
                $stmt->bind_param($types, ...$params);
            }

            $assessmentRows = cp_fetch_all_stmt($stmt);
            $stmt->close();

            foreach ($assessmentRows as $r) {
                $items[] = $r;
            }
        }
    }

    usort($items, function ($a, $b) {
        $ad = strtotime((string)($a['start_date'] ?? '')) ?: 0;
        $bd = strtotime((string)($b['start_date'] ?? '')) ?: 0;
        return $bd <=> $ad;
    });

    $rows = array_slice($items, 0, 500);
}

$today = date('Y-m-d');
$upcoming = 0;
$overdue = 0;
$active = 0;

foreach ($rows as $r) {
    $type = strtolower((string)($r['item_type'] ?? ''));
    $status = strtolower((string)($r['status'] ?? ''));

    if (in_array($status, ['active', 'ongoing', 'open'], true)) {
        $active++;
    }

    $start = !empty($r['start_date']) ? date('Y-m-d', strtotime((string)$r['start_date'])) : '';
    $end   = !empty($r['end_date']) ? date('Y-m-d', strtotime((string)$r['end_date'])) : '';

    if ($start !== '' && $start > $today) {
        $upcoming++;
    }

    if ($type === 'assessment' && $end !== '' && $end < $today && in_array($status, ['active', 'open'], true)) {
        $overdue++;
    }
}

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">Planner</h1>
        <div class="page-subtitle">View upcoming batches, assessment releases and due dates</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">Planner Items</div>
            <div class="fs-3 fw-bold"><?= number_format(count($rows)) ?></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">Active</div>
            <div class="fs-3 fw-bold"><?= number_format($active) ?></div>
            <i class="bi bi-check-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-clock-history fs-4 text-danger"></i>
        </div>
    </div>

    <div class="col-md-3">
        <div class="eden-card p-3">
            <div class="text-muted small">Overdue Assessments</div>
            <div class="fs-3 fw-bold"><?= number_format($overdue) ?></div>
            <i class="bi bi-exclamation-triangle 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-5">
            <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="plannerSearch" class="form-control border-start-0" placeholder="Search title, course, status...">
            </div>
        </div>

        <div class="col-lg-3">
            <select id="typeFilter" class="form-select">
                <option value="">All Types</option>
                <option value="batch">Batches</option>
                <option value="assessment">Assessments</option>
            </select>
        </div>

        <div class="col-lg-2">
            <select id="timeFilter" class="form-select">
                <option value="">All Time</option>
                <option value="upcoming">Upcoming</option>
                <option value="today">Today</option>
                <option value="past">Past</option>
            </select>
        </div>

        <div class="col-lg-2 text-lg-end">
            <span class="badge text-bg-danger rounded-pill px-3 py-2">
                <?= number_format(count($rows)) ?> Items
            </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>Planner Timeline</strong>
        <span class="text-muted small">Batches + Assessments</span>
    </div>

    <div class="table-responsive">
        <table class="table table-hover align-middle mb-0" id="plannerTable">
            <thead class="table-light">
                <tr>
                    <th style="width:80px;">ID</th>
                    <th>Item</th>
                    <th>Course</th>
                    <th>Start / Open</th>
                    <th>End / Due</th>
                    <th>Delivery</th>
                    <th>Status</th>
                    <th>Time</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 planner items found.
                    </td>
                </tr>
            <?php else: ?>
                <?php foreach ($rows as $r): ?>
                    <?php
                        $type = strtolower((string)($r['item_type'] ?? ''));
                        $typeText = $type === 'assessment' ? 'Assessment' : 'Batch';

                        $startRaw = (string)($r['start_date'] ?? '');
                        $endRaw   = (string)($r['end_date'] ?? '');

                        $startDate = $startRaw !== '' ? date('Y-m-d', strtotime($startRaw)) : '';
                        $endDate   = $endRaw !== '' ? date('Y-m-d', strtotime($endRaw)) : '';

                        $timeKey = '';
                        $timeText = '-';
                        $timeBadge = 'text-bg-light border';

                        if ($startDate !== '' && $startDate > $today) {
                            $timeKey = 'upcoming';
                            $timeText = 'Upcoming';
                            $timeBadge = 'text-bg-warning';
                        } elseif (($startDate !== '' && $startDate === $today) || ($endDate !== '' && $endDate === $today)) {
                            $timeKey = 'today';
                            $timeText = 'Today';
                            $timeBadge = 'text-bg-primary';
                        } elseif ($endDate !== '' && $endDate < $today) {
                            $timeKey = 'past';
                            $timeText = $type === 'assessment' ? 'Due Passed' : 'Ended';
                            $timeBadge = $type === 'assessment' ? 'text-bg-danger' : 'text-bg-secondary';
                        } elseif ($startDate !== '' && $startDate <= $today && ($endDate === '' || $endDate >= $today)) {
                            $timeKey = 'today';
                            $timeText = 'Ongoing';
                            $timeBadge = 'text-bg-success';
                        }

                        $statusRaw = (string)($r['status'] ?? 'Active');
                        $statusKey = strtolower($statusRaw);

                        $statusBadge = match ($statusKey) {
                            'active', 'ongoing', 'open' => 'text-bg-success',
                            'inactive', 'closed'       => 'text-bg-secondary',
                            'pending'                  => 'text-bg-warning',
                            'completed', 'complete'    => 'text-bg-primary',
                            default                    => 'text-bg-light border'
                        };

                        $typeBadge = $type === 'assessment' ? 'text-bg-danger' : 'text-bg-dark';
                    ?>

                    <tr data-type="<?= h($type) ?>" data-time="<?= h($timeKey) ?>">
                        <td class="text-muted">#<?= (int)$r['id'] ?></td>

                        <td>
                            <div class="fw-bold"><?= h($r['title'] ?? 'Untitled') ?></div>
                            <div class="small mt-1">
                                <span class="badge <?= h($typeBadge) ?> rounded-pill"><?= h($typeText) ?></span>
                                <?php if (!empty($r['code'])): ?>
                                    <span class="text-muted ms-1"><?= h($r['code']) ?></span>
                                <?php endif; ?>
                            </div>
                        </td>

                        <td>
                            <?= !empty($r['course_name']) ? h($r['course_name']) : '<span class="text-muted">-</span>' ?>
                        </td>

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

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

                        <td>
                            <?= !empty($r['delivery_mode']) ? h($r['delivery_mode']) : '<span class="text-muted">-</span>' ?>
                        </td>

                        <td>
                            <span class="badge <?= h($statusBadge) ?> rounded-pill">
                                <?= h(ucwords(str_replace('_', ' ', $statusRaw))) ?>
                            </span>
                        </td>

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

<script>
document.addEventListener('DOMContentLoaded', function () {
    const searchInput = document.getElementById('plannerSearch');
    const typeFilter = document.getElementById('typeFilter');
    const timeFilter = document.getElementById('timeFilter');
    const rows = Array.from(document.querySelectorAll('#plannerTable tbody tr[data-type]'));

    function filterTable() {
        const q = (searchInput.value || '').toLowerCase().trim();
        const type = (typeFilter.value || '').toLowerCase().trim();
        const time = (timeFilter.value || '').toLowerCase().trim();

        rows.forEach(row => {
            const text = row.innerText.toLowerCase();
            const rowType = (row.dataset.type || '').toLowerCase();
            const rowTime = (row.dataset.time || '').toLowerCase();

            const matchSearch = q === '' || text.includes(q);
            const matchType = type === '' || rowType === type;
            const matchTime = time === '' || rowTime === time;

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

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

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