<?php 
// Set page title before including header
$pageTitle = "Staff Information Page - E&SE Kotli";
include_once 'header_new.php'; 
?>
<h1 class="text-center text-uppercase stylish-header"><strong>Staff Information</strong></h1>
<div class="container"> 
    <div class="row"> 
        <div class="col-md-6">
            <h2 class="text-center">Male Staff</h2>
            <div class="chart-container" style="position: relative; height:350px; width:100%">
                <canvas id="malePieChart"></canvas>
            </div>
        </div>
        <div class="col-md-6">
            <h2 class="text-center">Female Staff</h2>
            <div class="chart-container" style="position: relative; height:350px; width:100%">
                <canvas id="femalePieChart"></canvas>
            </div>
        </div>
    </div>
    
    <div class="row mt-4">
        <div class="col-md-12">
            <div class="card shadow-sm">
                <div class="card-header bg-primary text-white">
                    <h5 class="mb-0">District Kotli Staff Details</h5>
                </div>
                <div class="card-body">
                    <div class="table-responsive" align="center" >
                        <table id="staffTable" class="table table-striped table-bordered table-hover" style="width:80%" align="center">
                            <thead class="thead-dark">
                                <tr>
                                    <th>Designation</th>
                                    <th>Male</th>
                                    <th>Female</th>
                                    <th>Total</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                include_once 'connect.php';
									$sql = "
										SELECT 
											des_id,
											SUM(male_staff)   AS male,
											SUM(female_staff) AS female
										FROM school_posts
										GROUP BY des_id
									";

                                $result = $conn->query($sql);
                                $totalMale = 0;
                                $totalFemale = 0;

                                $maleData = [];
                                $femaleData = [];
                                $labels = [];

                                if ($result->num_rows > 0) {
                                    while($row = $result->fetch_assoc()) {
                                        echo "<tr>";
                                        echo "<td>" . htmlspecialchars($row["designation"]) . "</td>";
                                        echo "<td>" . $row["male"] . "</td>";
                                        echo "<td>" . $row["female"] . "</td>";
                                        echo "<td>" . ($row["male"] + $row["female"]) . "</td>";
                                        echo "</tr>";
                                        $labels[] = $row["designation"];
                                        $maleData[] = (int)$row["male"];
                                        $femaleData[] = (int)$row["female"];
                                        $totalMale += (int)$row["male"];
                                        $totalFemale += (int)$row["female"];
                                    }
                                } else {
                                    echo "<tr><td colspan='4'>No results found</td></tr>";
                                }
                                $conn->close();
                                ?>
                            </tbody>
                            <tfoot>
                                <tr class="table-info">
                                    <th>Total</th>
                                    <th><?php echo $totalMale; ?></th>
                                    <th><?php echo $totalFemale; ?></th>
                                    <th><?php echo ($totalMale + $totalFemale); ?></th>
                                </tr>
                            </tfoot>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Load Chart.js before DataTables to avoid conflicts -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

<!-- DataTables CSS -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.5/css/dataTables.bootstrap5.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/2.2.2/css/buttons.bootstrap5.min.css">

<script>
    // First render the charts
    document.addEventListener('DOMContentLoaded', function() {
        const maleCtx = document.getElementById('malePieChart').getContext('2d');
        const femaleCtx = document.getElementById('femalePieChart').getContext('2d');

        const maleData = <?php echo json_encode($maleData); ?>;
        const femaleData = <?php echo json_encode($femaleData); ?>;
        const labels = <?php echo json_encode($labels); ?>;

        // Function to generate consistent colors for both charts
        function generateColors(count) {
            const colors = [];
            for (let i = 0; i < count; i++) {
                const hue = (i * 360 / count) % 360;
                colors.push(`hsl(${hue}, 70%, 60%)`);
            }
            return colors;
        }

        const chartColors = generateColors(labels.length);

        new Chart(maleCtx, {
            type: 'pie',
            data: {
                labels: labels,
                datasets: [{
                    data: maleData,
                    backgroundColor: chartColors,
                    borderColor: '#fff',
                    borderWidth: 2
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'right',
                    },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                const label = context.label || '';
                                const value = context.raw || 0;
                                const total = context.dataset.data.reduce((a, b) => a + b, 0);
                                const percentage = Math.round((value / total) * 100);
                                return `${label}: ${value} (${percentage}%)`;
                            }
                        }
                    }
                }
            }
        });

        new Chart(femaleCtx, {
            type: 'pie',
            data: {
                labels: labels,
                datasets: [{
                    data: femaleData,
                    backgroundColor: chartColors,
                    borderColor: '#fff',
                    borderWidth: 2
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'right',
                    },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                const label = context.label || '';
                                const value = context.raw || 0;
                                const total = context.dataset.data.reduce((a, b) => a + b, 0);
                                const percentage = Math.round((value / total) * 100);
                                return `${label}: ${value} (${percentage}%)`;
                            }
                        }
                    }
                }
            }
        });

        // Then initialize DataTable
        $('#staffTable').DataTable({
            dom: '<"top"Bf>rt<"bottom"lip><"clear">',
            buttons: [
                'copy', 'csv', 'excel', 'pdf', 'print'
            ],
            responsive: true,
            initComplete: function() {
                this.api()
                    .columns()
                    .every(function() {
                        var column = this;
                        var title = $(column.header()).text();
                        
                        if (title !== 'Total') {
                            $(column.header()).append('<br><input type="text" placeholder="Search '+title+'" style="width: 100%; font-size: 12px;" />');
                            
                            $('input', column.header()).on('keyup change', function() {
                                if (column.search() !== this.value) {
                                    column.search(this.value).draw();
                                }
                            });
                        }
                    });
            }
        });
    });
</script>

<!-- DataTables JS and extensions -->
<script type="text/javascript" src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/2.2.2/js/dataTables.buttons.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.bootstrap5.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.html5.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.print.min.js"></script>

</main>
<?php include_once 'footer_new.php'; ?>