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

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

$batches = [];
$error = '';

if (!cb_table_exists($db, 'batches')) {
    $error = 'batches table not found.';
} else {
    $batchNameCol = cb_first_col($db, 'batches', ['batch_name', 'name', 'title']);
    $batchCodeCol = cb_first_col($db, 'batches', ['batch_code', 'code']);
    $startCol     = cb_first_col($db, 'batches', ['batch_start_date', 'start_date', 'registration_open_date']);
    $endCol       = cb_first_col($db, 'batches', ['batch_end_date', 'end_date', 'registration_close_date']);
    $statusCol    = cb_first_col($db, 'batches', ['status']);
    $deliveryCol  = cb_first_col($db, 'batches', ['delivery_mode']);
    $mediumCol    = cb_first_col($db, 'batches', ['medium_of_instruction', 'medium']);

    $hasBranchId  = cb_col_exists($db, 'batches', 'branch_id');
    $hasCourseId  = cb_col_exists($db, 'batches', 'course_id');

    $select = [
        'b.id AS id'
    ];

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

    if ($hasBranchId) {
        $select[] = "b.branch_id AS branch_id";
    } else {
        $select[] = "NULL AS branch_id";
    }

    if ($hasCourseId) {
        $select[] = "b.course_id AS course_id";
    } else {
        $select[] = "NULL AS course_id";
    }

    $joins = [];

    // Course name safe join
    if ($hasCourseId && cb_table_exists($db, 'courses')) {
        $courseNameCol = cb_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";
    }

    // Branch / Centre name safe join
    $branchJoined = false;

    if ($hasBranchId && cb_table_exists($db, 'branches')) {
        $branchNameCol = cb_first_col($db, 'branches', ['branch_name', 'name', 'title']);
        if ($branchNameCol) {
            $select[] = "br.`{$branchNameCol}` AS branch_name";
            $joins[] = "LEFT JOIN branches br ON br.id = b.branch_id";
            $branchJoined = true;
        }
    }

    if (!$branchJoined && $hasBranchId && cb_table_exists($db, 'centres')) {
        $centreNameCol = cb_first_col($db, 'centres', ['centre_name', 'center_name', 'name', 'title']);
        if ($centreNameCol) {
            $select[] = "ce.`{$centreNameCol}` AS branch_name";
            $joins[] = "LEFT JOIN centres ce ON ce.id = b.branch_id";
            $branchJoined = true;
        }
    }

    if (!$branchJoined) {
        $select[] = "'' AS branch_name";
    }

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

    /*
      Coordinator branch scope:
      - If coordinator session has branch_ids and batches.branch_id exists,
        only show those branch batches.
      - If no branch_ids found, show all batches instead of breaking page.
    */
    $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 ($hasBranchId && $branchIds) {
        $placeholders = implode(',', array_fill(0, count($branchIds), '?'));
        $where[] = "b.branch_id IN ({$placeholders})";

        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) {
        $error = 'SQL error: ' . $db->error;
    } else {
        if ($params) {
            $stmt->bind_param($types, ...$params);
        }

        $batches = cb_fetch_all($stmt);
        $stmt->close();
    }
}

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">Batches</h1>
        <div class="page-subtitle">View assigned ELMS batches and course details</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-danger rounded-4">
        <strong>Error:</strong> <?= h($error) ?>
    </div>
<?php endif; ?>

<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="batchSearch" class="form-control border-start-0" placeholder="Search batch, course, branch, status...">
            </div>
        </div>

        <div class="col-lg-3">
            <select id="statusFilter" class="form-select">
                <option value="">All Status</option>
                <option value="active">Active</option>
                <option value="inactive">Inactive</option>
                <option value="pending">Pending</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(count($batches)) ?> Batches
            </span>
        </div>
    </div>
</div>

<div class="eden-card overflow-hidden">
    <div class="table-responsive">
        <table class="table table-hover align-middle mb-0" id="batchesTable">
            <thead class="table-light">
                <tr>
                    <th style="width:80px;">ID</th>
                    <th>Batch</th>
                    <th>Course</th>
                    <th>Branch</th>
                    <th>Delivery</th>
                    <th>Medium</th>
                    <th>Start</th>
                    <th>End</th>
                    <th>Status</th>
                </tr>
            </thead>

            <tbody>
            <?php if (!$batches): ?>
                <tr>
                    <td colspan="9" class="text-center text-muted py-5">
                        <i class="bi bi-inbox fs-2 d-block mb-2"></i>
                        No batches found.
                    </td>
                </tr>
            <?php else: ?>
                <?php foreach ($batches as $row): ?>
                    <?php
                        $status = trim((string)($row['status'] ?? 'Active'));
                        $statusLower = strtolower($status);

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

                        $batchCode = trim((string)($row['batch_code'] ?? ''));
                        $branchText = trim((string)($row['branch_name'] ?? ''));
                        if ($branchText === '' && !empty($row['branch_id'])) {
                            $branchText = 'Branch #' . (int)$row['branch_id'];
                        }
                    ?>
                    <tr data-status="<?= h($statusLower) ?>">
                        <td class="text-muted">#<?= (int)$row['id'] ?></td>

                        <td>
                            <div class="fw-bold"><?= h($row['batch_name'] ?? ('Batch #' . $row['id'])) ?></div>
                            <?php if ($batchCode !== ''): ?>
                                <div class="small text-muted"><?= h($batchCode) ?></div>
                            <?php endif; ?>
                        </td>

                        <td>
                            <?php if (!empty($row['course_name'])): ?>
                                <?= h($row['course_name']) ?>
                            <?php elseif (!empty($row['course_id'])): ?>
                                <span class="text-muted">Course #<?= (int)$row['course_id'] ?></span>
                            <?php else: ?>
                                <span class="text-muted">-</span>
                            <?php endif; ?>
                        </td>

                        <td>
                            <?= $branchText !== '' ? h($branchText) : '<span class="text-muted">-</span>' ?>
                        </td>

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

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

                        <td>
                            <?= !empty($row['start_date']) ? h(date('Y-m-d', strtotime((string)$row['start_date']))) : '<span class="text-muted">-</span>' ?>
                        </td>

                        <td>
                            <?= !empty($row['end_date']) ? h(date('Y-m-d', strtotime((string)$row['end_date']))) : '<span class="text-muted">-</span>' ?>
                        </td>

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

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

    function filterTable() {
        const q = (searchInput.value || '').toLowerCase().trim();
        const st = (statusFilter.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.includes(st);

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

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

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