<?php
include 'dbclass/dbclass.php';

// Establish connection to the database
$con = new mysqli('localhost', 'gdhhsxmy_moonniyurjalanidhislec', 'moonniyurjalanidhislec', 'gdhhsxmy_moonniyurjalanidhislec');

// Check connection
if ($con->connect_error) {
    die('Connection failed: ' . $con->connect_error);
}

// SQL query to select all data from the students table
$sql    = 'SELECT students.*, panchayath.panchayath, ward.ward FROM students INNER JOIN panchayath ON students.panchayath = panchayath.id INNER JOIN ward ON students.ward = ward.id;';
$result = $con->query($sql);

// Count rows to decide about export button
$numRows = $result->num_rows;


?>

<center>
    <h1 class="p-4">REGISTERED STUDENTS</h1>
</center>

<div id="searchExportContainer" class="d-flex justify-content-center p-4" style="gap: 10px; flex-wrap: wrap;">
    <input type="text" id="searchInput" onkeyup="searchTable()" placeholder="Search..." class="form-control" style="width: 50%; max-width: 600px; padding: 10px; font-size: 16px; border: 2px solid #ced4da; border-radius: 0.25rem;">
    <button id="exportBtn" class="btn btn-primary" onclick="exportToExcel()" <?php if ($numRows == 0) {
        echo 'style="display:none;"';
    } ?>>Export to Excel</button>

        
   
</div>

<div class="table-responsive">
    <table id="example" class="table align-middle mb-0 bg-white w-100 center">
        <thead class="bg-light">
            <tr>
                <th>S.NO</th>
                <th>PHOTO</th>
                <th>NAME</th>
                <th>EXAM</th>
                <th>REGNUMBER</th>
                <th>ADDRESS</th>
                <th>PHONE NO.</th>
                <th>PANCHAYATH</th>
                <th>WARD</th>
                <th>CONSUMER NO.</th>
                <th>MARKLIST</th>
                <th>EDIT</th>
            </tr>
        </thead>
        <tbody id="markdetails">
            <?php
            if ($numRows > 0) {
                // Output data of each row
                while ($row = $result->fetch_assoc()) {
                    echo '<tr>
                    <td class="serial-number">' . $row['id'] . '</td>
                    <td>
                        <div class="d-flex align-items-center">';

                    if (!empty($row['photo'])) {
                        echo '<div class="photo-container" onclick="downloadImage(\'' . $row['photo'] . '\', \'student_photo.jpg\')">';
                        echo '<img src="' . $row['photo'] . '" alt="Student Photo" />';
                        echo '<div class="icon"><button>⬇️</button></div>';
                        echo '</div>';
                    } else {
                        echo '<i class="fa fa-user" style="font-size:45px;"></i>';
                    }
                    echo '</div>
                    </td>
                    <td>' . $row['name'] . '</td>
                    <td>' . $row['exam'] . '</td>
                    <td>' . $row['regnumber'] . '</td>
                    <td>' . $row['address'] . '</td>
                    <td>' . $row['number'] . '</td>
                    <td>' . $row['panchayath'] . '</td>
                    <td>' . $row['ward'] . '</td>
                     <td>' . $row['cnsno'] . '</td>
                    <td>';

                    if (!empty($row['marklist'])) {
                        echo '<a id="marklistLink" style="text-decoration:none;color:white;" href="' . $row['marklist'] . '"><button type="button" class="btn btn-danger btn-sm btn-rounded text-white"><i class="fa fa-eye"></i></button></a>';
                    }
                    echo '</td>
                     <form action="edit.php" method="GET">
                    <td>
                        <input type="hidden" name="id" value="' . $row['id'] . '">
                        <button type="submit" class="btn btn-primary btn-sm btn-rounded"><i class="fa fa-pen"></i></button>
                    </td>
                    </form>
                    </tr>';
                }
            } else {
                // No data row if no results
                echo '<tr><td colspan="11" class="text-center">No data available</td></tr>';
            }
$con->close();
?>
        </tbody>
    </table>
</div>

<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.2/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.0/xlsx.full.min.js"></script>

<script>
function exportToExcel() {
    const table = document.getElementById('example');
    const headers = table.querySelectorAll('thead th');

    let photoColumnIndex = -1;
    let marklistColumnIndex = -1;
    let editColumnIndex = -1;

    Array.from(headers).forEach((th, index) => {
        const textContent = th.textContent.trim().toUpperCase();
        if (textContent == 'PHOTO') photoColumnIndex = index;
        else if (textContent == 'MARKLIST') marklistColumnIndex = index;
        else if (textContent == 'EDIT') editColumnIndex = index;
    });

    // Create an array of rows (arrays of cell values)
    let data = [];

    // Add headers (except excluded columns)
    let headerRow = [];
    Array.from(headers).forEach((th, index) => {
        if (index != photoColumnIndex && index != marklistColumnIndex && index != editColumnIndex) {
            headerRow.push(th.textContent.trim());
        }
    });
    data.push(headerRow);

    // Add rows
    const rows = table.querySelectorAll('tbody tr');
    rows.forEach(row => {
        const firstCell = row.querySelector('td');
        if (firstCell && firstCell.colSpan == 11 && row.cells.length == 1) return;  // Skip "No data available"

        let rowData = [];
        Array.from(row.cells).forEach((cell, index) => {
            if (index != photoColumnIndex && index != marklistColumnIndex && index != editColumnIndex) {
                let text = cell.textContent.trim();

                // To prevent Excel interpreting like dates, prefix with apostrophe (')
                // But XLSX ignores apostrophe in cell value for formatting, so we set cell type explicitly below
                rowData.push(text);
            }
        });
        data.push(rowData);
    });

    // Create workbook and worksheet
    const wb = XLSX.utils.book_new();
    const ws = XLSX.utils.aoa_to_sheet(data);

    // Force all cells to text (optional but safer for all data)
    const range = XLSX.utils.decode_range(ws['!ref']);
    for(let R = range.s.r; R <= range.e.r; ++R) {
        for(let C = range.s.c; C <= range.e.c; ++C) {
            const cell_address = {c:C, r:R};
            const cell_ref = XLSX.utils.encode_cell(cell_address);
            const cell = ws[cell_ref];
            if(cell && typeof cell.v == 'string') {
                cell.t = 's'; // force cell type to string (text)
            }
        }
    }

    XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
    XLSX.writeFile(wb, 'students.xlsx');
}



function searchTable() {
    var input, filter, tableBody, tr, td, i, j, txtValue;
    input = document.getElementById("searchInput");
    filter = input.value.toUpperCase();
    tableBody = document.getElementById("markdetails");
    tr = tableBody.getElementsByTagName("tr");
    var visibleRowCount = 0;

    // Remove existing "No data available" row
    for(let k=0; k < tr.length; k++) {
        if(tr[k].querySelector('td[colspan]')) {
            tableBody.deleteRow(k);
            break;
        }
    }

    for (i = 0; i < tr.length; i++) {
        tr[i].style.display = "none"; // hide all initially
    }

    for (i = 0; i < tr.length; i++) {
        td = tr[i].getElementsByTagName("td");
        var isVisible = false;

        for (j = 0; j < td.length; j++) {
            var cell = td[j];
            if (cell) {
                txtValue = cell.textContent || cell.innerText;
                if (txtValue.toUpperCase().indexOf(filter) > -1) {
                    isVisible = true;
                    break;
                }
            }
        }

        if (!isVisible) {
            var img = tr[i].getElementsByTagName("img")[0];
            if (img && img.src.toUpperCase().indexOf(filter) > -1) {
                isVisible = true;
            }
        }

        if (isVisible) {
            tr[i].style.display = "";
            visibleRowCount++;
            tr[i].getElementsByClassName("serial-number")[0].textContent = visibleRowCount;
        }
    }

    if (visibleRowCount == 0) {
        var newRow = tableBody.insertRow(-1);
        var newCell = newRow.insertCell(0);
        newCell.colSpan = 11; // number of columns
        newCell.textContent = "No data available";
        newCell.classList.add('text-center');
    }
    
    const exportBtn = document.getElementById("exportBtn");
if (exportBtn) {
    exportBtn.style.display = visibleRowCount > 0 ? "inline-block" : "none";
}

}
</script>

<script>
function downloadImage(imageSrc, imageName) {
    const link = document.createElement('a');
    link.href = imageSrc;
    link.download = imageName;
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
}
</script>

<style>
.photo-container {
    position: relative;
    display: inline-block;
    width: 45px;
    height: 45px;
}
.photo-container img {
    width: 100%;
    height: 100%;
}
.icon {
    position: absolute;
    top: 50%;
    left: 50%;
    transform: translate(-50%, -50%);
    display: none;
    color: white;
    font-size: 24px;
}
.photo-container:hover .icon {
    display: block;
}
</style>

<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">

