<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <link rel="icon" href="download.jpeg">
    <link href="https://fonts.googleapis.com/css2?family=Poppins:wght@300;500;600&display=swap" rel="stylesheet">
    <title>🎭 MAWA 🎶</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.3/css/all.min.css" />
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet">

    <style>
     body {
      font-family: 'Poppins', sans-serif;
      background: linear-gradient(rgba(0, 0, 0, 0.7), rgba(0, 0, 0, 0.7)),
                  url('fb1c39f459d6727cb164eb106f3fe298.jpg') no-repeat center center fixed;
      background-size: cover;
      margin: 0;
      padding: 20px;
      color: #f5d774;
    }
    table, th, td {
        color: #f5d774;
    }
    </style>
</head>
<body>
<?php
$con = new mysqli('localhost', 'gdhhsxmy_mawa', 'gdhhsxmy_mawa', 'gdhhsxmy_mawa');
if ($con->connect_error) {
    die('Connection failed: ' . $con->connect_error);
}

$sql = "
SELECT 
    a.*, 
    d.district_name, 
    t.taluk_name,
    GROUP_CONCAT(i.item_name ORDER BY i.id SEPARATOR ', ') AS items
FROM applications a
INNER JOIN districts d ON a.district = d.district_id
INNER JOIN taluks t ON a.taluk = t.taluk_id
LEFT JOIN application_items ai ON a.id = ai.application_id
LEFT JOIN items i ON ai.item = i.id
GROUP BY a.id
ORDER BY a.id DESC
";
$result   = $con->query($sql);
$students = [];
while ($row = $result->fetch_assoc()) {
    $students[] = $row;
}
$con->close();
?>

<div class="container mt-4">
    <h1 class="text-center">REGISTERED CANDIDATES (<span id="studentCount">0</span>)</h1>

    <div class="d-flex justify-content-center gap-2 flex-wrap my-3">
        <input type="text" id="searchInput" class="form-control" placeholder="Search..." style="max-width: 300px;">
        <select id="districtFilter" class="form-select" style="width: 200px;"><option value="">All Districts</option></select>
        <select id="talukFilter" class="form-select" style="width: 200px;"><option value="">All Taluks</option></select>
        <button id="exportBtn" class="btn btn-primary" onclick="exportToExcel()">Export to Excel</button>
    </div>

    <div class="table-responsive">
       <table class="table table-bordered table-sm align-middle text-center" id="studentsTable">
          <thead>
  <tr>
    <th style="width: 50px;">S.NO</th>
    <th style="width: 150px;">FULL NAME</th>
    <th style="width: 150px;">PARENT NAME</th>
    <th style="width: 50px;">AGE</th>
    <th style="width: 80px;">GENDER</th>
    <th>ADDRESS</th>
    <th style="width: 120px;">CONTACT</th>
    <th style="width: 120px;">OTHER CONTACT</th>
    <th style="width: 120px;">WHATSAPP</th>
    <th style="width: 150px;">EMAIL</th>
    <th style="width: 130px;">DISTRICT</th>
    <th style="width: 130px;">TALUK</th>
    <th style="width: 150px;">LOCAL BODY</th>
    <th style="width: 120px;">CATEGORY</th>
    <th style="width: 120px;">AGE GROUP</th>
    <th style="width: 180px;">ITEMS</th>
    <th style="width: 150px;">CANDIDATE FOOD</th>
    <th style="width: 150px;">PARENT FOOD</th>
    <th style="width: 150px;">APPLIED ON</th>
  </tr>
</thead>

          <tbody id="tableBody"></tbody>
       </table>
    </div>
    <div class="d-flex justify-content-center mt-3 flex-wrap gap-2" id="paginationControls"></div>
</div>

<script>
const students = <?php echo json_encode($students); ?>;
let currentPage = 1;
const rowsPerPage = 10;
let filteredData = students.slice();

function renderTable() {
    const tbody = document.getElementById("tableBody");
    tbody.innerHTML = "";
    const start = (currentPage - 1) * rowsPerPage;
    const end = start + rowsPerPage;
    const pageData = filteredData.slice(start, end);

    if (pageData.length == 0) {
        tbody.innerHTML = '<tr><td colspan="15" class="text-center">No data available</td></tr>';
        return;
    }

    pageData.forEach((row, index) => {
        const tr = document.createElement("tr");
        tr.innerHTML = `
    <td>${start + index + 1}</td>
    <td>${row.fullname}</td>
    <td>${row.parentname}</td>
    <td>${row.age}</td>
    <td>${row.gender}</td>
    <td>${row.address}</td>
    <td>${row.contact}</td>
    <td>${row.othercontact ?? ''}</td>
    <td>${row.whatsapp ?? ''}</td>
    <td>${row.email ?? ''}</td>
    <td>${row.district_name}</td>
    <td>${row.taluk_name}</td>
    <td>${row.localbody}</td>
    <td>${row.category}</td>
    <td>${row.agegroup ?? 'NA'}</td>
    <td>${row.items ?? ''}</td>
    <td>${row.food_candidate}</td>
    <td>${row.food_parent}</td>
    <td>${formatDate(row.created_at)}</td>

`;

        tbody.appendChild(tr);
    });
    renderPagination();
}

function renderPagination() {
    const totalPages = Math.ceil(filteredData.length / rowsPerPage);
    const container = document.getElementById("paginationControls");
    container.innerHTML = "";

    if (currentPage > 1) {
        const prev = document.createElement("button");
        prev.textContent = "Previous";
        prev.className = "btn btn-outline-primary btn-sm";
        prev.onclick = () => { currentPage--; renderTable(); };
        container.appendChild(prev);
    }

    for (let i = 1; i <= totalPages; i++) {
        const btn = document.createElement("button");
        btn.textContent = i;
        btn.className = `btn btn-sm ${i == currentPage ? 'btn-primary' : 'btn-outline-primary'}`;
        btn.onclick = () => { currentPage = i; renderTable(); };
        container.appendChild(btn);
    }

    if (currentPage < totalPages) {
        const next = document.createElement("button");
        next.textContent = "Next";
        next.className = "btn btn-outline-primary btn-sm";
        next.onclick = () => { currentPage++; renderTable(); };
        container.appendChild(next);
    }
}

function applyFilters() {
    const text = document.getElementById("searchInput").value.toLowerCase();
    const district = document.getElementById("districtFilter").value.toLowerCase();
    const taluk = document.getElementById("talukFilter").value.toLowerCase();

    filteredData = students.filter(s => {
        const matchText = Object.values(s).some(val => (val + "").toLowerCase().includes(text));
        const matchDistrict = !district || s.district_name.toLowerCase() == district;
        const matchTaluk = !taluk || s.taluk_name.toLowerCase() == taluk;
        return matchText && matchDistrict && matchTaluk;
    });

    currentPage = 1;
    renderTable();
    document.getElementById("studentCount").textContent = filteredData.length;
}

document.getElementById("searchInput").addEventListener("input", applyFilters);
document.getElementById("districtFilter").addEventListener("change", applyFilters);
document.getElementById("talukFilter").addEventListener("change", applyFilters);

window.onload = () => {
    const districtSelect = document.getElementById("districtFilter");
    const talukSelect = document.getElementById("talukFilter");
    const dSet = new Set(), tSet = new Set();
    students.forEach(s => { dSet.add(s.district_name); tSet.add(s.taluk_name); });
    [...dSet].sort().forEach(d => districtSelect.innerHTML += `<option value="${d}">${d}</option>`);
    [...tSet].sort().forEach(t => talukSelect.innerHTML += `<option value="${t}">${t}</option>`);
    renderTable();
    document.getElementById("studentCount").textContent = students.length;
};

function exportToExcel() {
    const headers = [
        "S.NO", "FULL NAME", "PARENT NAME", "AGE", "GENDER", "ADDRESS",
        "CONTACT", "OTHER CONTACT", "WHATSAPP", "EMAIL",
        "DISTRICT", "TALUK", "LOCAL BODY",
        "CATEGORY", "AGE GROUP", "ITEMS",
        "CANDIDATE FOOD", "PARENT FOOD", "APPLIED ON"
    ];

    const rows = filteredData.map((row, index) => [
        index + 1,
        row.fullname,
        row.parentname,
        row.age,
        row.gender,
        row.address,
        row.contact,
        row.othercontact ?? '',
        row.whatsapp ?? '',
        row.email ?? '',
        row.district_name,
        row.taluk_name,
        row.localbody,
        row.category,
        row.agegroup ?? 'NA',
        row.items ?? '',
        row.food_candidate,
        row.food_parent,
        formatDate(row.created_at)

    ]);

    if (rows.length === 0) {
        alert("No data to export.");
        return;
    }

    const worksheet = XLSX.utils.aoa_to_sheet([headers, ...rows]);
    worksheet['!cols'] = headers.map(() => ({ wch: 20 }));

    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, "Applications");
    XLSX.writeFile(workbook, "applications.xlsx");
}

function formatDate(dateStr) {
    if (!dateStr) return '';
    const d = new Date(dateStr);
    if (isNaN(d)) return dateStr; // fallback if invalid
    const options = {
        day: '2-digit', month: '2-digit', year: 'numeric',
        hour: '2-digit', minute: '2-digit',
        hour12: true
    };
    return d.toLocaleString('en-GB', options); // DD/MM/YYYY, hh:mm AM/PM
}

</script>

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.0/xlsx.full.min.js"></script>
</body>
</html>
