<?php 
include('session.php');
include('config.php');

// Check database connection
if (!$db) {
    die("Connection failed: " . mysqli_connect_error());
}

// Handle file upload
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_FILES['excelFile'])) {
    $target_dir = "uploads/";
    if (!file_exists($target_dir)) {
        mkdir($target_dir, 0755, true);
    }
    
    $target_file = $target_dir . basename($_FILES["excelFile"]["name"]);
    $uploadOk = 1;
    $fileType = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));

    // Check if file is an Excel file
    if($fileType != "xlsx" && $fileType != "xls") {
        echo "<script>alert('Only Excel files are allowed.');</script>";
        $uploadOk = 0;
    }

    // Check file size (5MB max)
    if ($_FILES["excelFile"]["size"] > 5000000) {
        echo "<script>alert('File is too large. Maximum 5MB allowed.');</script>";
        $uploadOk = 0;
    }

    // Check if upload was successful
    if ($uploadOk == 1) {
        if (move_uploaded_file($_FILES["excelFile"]["tmp_name"], $target_file)) {
            // Process the Excel file
            require 'vendor/autoload.php'; // Include PhpSpreadsheet
            
            try {
                $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($target_file);
                $worksheet = $spreadsheet->getActiveSheet();
                $rows = $worksheet->toArray();
                
                // Clear existing data
                $db->query("TRUNCATE TABLE pump_forecast_data");
                
                // Skip header row and insert data
                for ($i = 1; $i < count($rows); $i++) {
                    if (!empty($rows[$i][0])) {
                        $pump = $rows[$i][0];
                        $barangays = $rows[$i][1];
                        $total_supply = $rows[$i][2];
                        $monthly_demand = $rows[$i][3];
                        
                        $stmt = $db->prepare("INSERT INTO pump_forecast_data (pump, barangays, total_supply, monthly_demand) VALUES (?, ?, ?, ?)");
                        $stmt->bind_param("isdd", $pump, $barangays, $total_supply, $monthly_demand);
                        $stmt->execute();
                    }
                }
                
                echo "<script>alert('Data uploaded successfully!');</script>";
            } catch (Exception $e) {
                echo "<script>alert('Error processing Excel file: " . addslashes($e->getMessage()) . "');</script>";
            }
        } else {
            echo "<script>alert('Error uploading file.');</script>";
        }
    }
}

// Get data for selected year
$selected_year = isset($_GET['year']) ? $_GET['year'] : date('Y');
$data = array();

$stmt = $db->prepare("SELECT * FROM pump_forecast_data WHERE YEAR(created_at) = ?");
$stmt->bind_param("s", $selected_year);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    $row['months_supply'] = round($row['total_supply'] / $row['monthly_demand']);
    $data[] = $row;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Water Pump Demand Forecasting</title>
    <link rel="stylesheet" href="assets/css/bootstrap.css">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
    <link rel="icon" type="image/png" href="images/logo_2.png"/>
    <link href="assets/css/bootstrap.css" rel="stylesheet" />
    <link href="assets/css/font-awesome.css" rel="stylesheet" />
    <link href="assets/js/morris/morris-0.4.3.min.css" rel="stylesheet" />
    <link href="assets/css/custom.css" rel="stylesheet" />
    <link href='http://fonts.googleapis.com/css?family=Open+Sans' rel='stylesheet' type='text/css' />
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
</head>
<body>
    <div id="wrapper">
        <!-- NAVBAR -->
        <nav class="navbar navbar-default navbar-cls-top" role="navigation" style="margin-bottom: 0">
            <div class="navbar-header">
                <a class="navbar-brand" href="admin.php" style="background-color: orange;">ADMIN</a> 
            </div>
            <div style="color: white; padding: 15px 50px 5px 50px; float: right; font-size: 16px;"> 
                <?php echo $_SESSION['login_user']; ?> &nbsp; 
                <a id="myBtn" class="btn btn-danger square-btn-adjust">Logout</a>
            </div>
        </nav>

        <!-- SIDEBAR -->
        <nav class="navbar-default navbar-side" role="navigation" style="height: 150%;">
            <div class="sidebar-collapse">
                <ul class="nav" id="main-menu">
                    <li class="text-center">
                        <img src="images/logo_1.jpg" class=" img-responsive" style="border-radius: 50%; width: 230px; padding: 20px;"/>
                    </li>
                    <li>
                        <a href="admin.php"><i class="fa fa-dashboard fa-3x"></i> Dashboard</a>
                    </li>
                    <li>
                        <a href="data_analytics.php" style="height: 80px; padding-top:35px;"><img src="images/monitoring.png" width="71px" height="50px" style="margin-left: -18px; margin-top: -18px; float: left;"/><i class="fa fa-3x"></i> <span style="margin-left: -10px;">Data Analytics</span></a>
                    </li>
                    <li><a href="bill_list.php" style="height: 80px; padding-top:35px;"><img src="images/bill.png" width="60px" height="63px" style="margin-left: -10px; margin-top: -20px; float: left;"/><i class="fa fa-3x"></i> Bill Management</a></li>
                    <li><a class="active-menu" href="demand_forecasting.php" style="height: 80px; padding-top:35px;"><img src="images/forecasting_icon.png" width="80px" height="58px" style="margin-left: -20px; margin-top: -20px; float: left;"/><i class="fa fa-3x"></i> Demand Forecasting</a></li>                         
                    <li><a href="prioritization.php" style="height: 80px; padding-top:35px;"><img src="images/a.png" width="60px" height="58px" style="margin-left: -10px; margin-top: -20px; float: left; border-radius: 50%;"/><i class="fa fa-3x"></i>Prioritization</a></li>                         
                    <li>
                        <a href="complaints.php" style="height: 80px; padding-top:35px;"><img src="images/task_gray.png" width="61px" height="50px" style="margin-left: -11px; margin-top: -18px; float: left;"/><i class="fa fa-3x"></i> Complaints</a>
                    </li>
                    <li>
                        <a href="announcements-admin.php" style="height: 80px; padding-top:35px;"><img src="images/announcement.png" width="61px" height="50px" style="margin-left: -11px; margin-top: -18px; float: left;"/> &nbsp &nbspAnnouncements</a>
                    </li> 
                    <li>
                        <a href="accounts.php" style="height: 80px; padding-top:35px;"><img src="images/accounts_white1.png" width="60px" height="63px" style="margin-left: -10px; margin-top: -20px; float: left;"/><i class="fa fa-3x"></i> Accounts</a>
                    </li>     
                    <li>
                        <a href="vmc-su.php"><i class="fa fa-sitemap fa-3x"></i> &nbsp VMC</a>
                    </li>       
                </ul>
            </div>
        </nav>

        <div id="page-wrapper">
            <div id="page-inner">
                <h2 class="text-center">Water Pump Demand Forecasting</h2>

                <!-- Excel Upload Form -->
                <div class="row mb-3">
                    <div class="col-md-12">
                        <form id="upload-form" class="form-inline">
                            <div class="form-group mr-3">
                                <label for="excelFile" class="mr-2">Upload Excel Data:</label>
                                <input type="file" name="excelFile" id="excelFile" class="form-control-file" accept=".xlsx,.xls" required>
                            </div>
                            <button type="submit" class="btn btn-primary">Upload</button>
                        </form>
                        <small class="text-muted">Excel format: Pump #, Barangays Supplied, Total Supply (m³), Monthly Demand (m³)</small>
                    </div>
                </div>

                <!-- Download Buttons -->
                <div class="row mb-3">
                    <div class="col-md-12 text-right">
                        <button id="downloadPDF" class="btn btn-danger">
                            <i class="fa fa-file-pdf-o"></i> Download PDF Report
                        </button>
                        <button id="downloadExcel" class="btn btn-success">
                            <i class="fa fa-file-excel-o"></i> Download Excel
                        </button>
                    </div>
                </div>

                <!-- Year Selection -->
                <div class="form-group">
                    <label for="yearSelect">Select Year:</label>
                    <select id="yearSelect" class="form-control" onchange="updateYear()">
                        <?php 
                        $years = range(date('Y') - 2, date('Y') + 3); // 2 years past and 3 years future
                        foreach ($years as $year): ?>
                            <option value="<?= $year ?>" <?= $year == $selected_year ? 'selected' : '' ?>><?= $year ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>

                <!-- Report Content -->
                <div id="reportContent">
                    <!-- Table -->
                    <table class="table table-bordered" id="dataTableContainer">
                        <thead>
                            <tr>
                                <th>Pump #</th>
                                <th>Barangays Supplied</th>
                                <th>Total Supply (m³)</th>
                                <th>Monthly Demand (m³)</th>
                                <th>Months of Supply</th>
                            </tr>
                        </thead>
                        <tbody id="dataTable">
                            <?php foreach ($data as $row): ?>
                            <tr>
                                <td><?= $row['pump'] ?></td>
                                <td><?= $row['barangays'] ?></td>
                                <td><?= number_format($row['total_supply'], 0) ?></td>
                                <td><?= number_format($row['monthly_demand'], 0) ?></td>
                                <td><?= $row['months_supply'] ?> months</td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>

                    <!-- Graphs -->
                    <div class="row">
                        <div class="col-md-6">
                            <h4>Total Water Supply per Pump</h4>
                            <canvas id="totalSupplyChart"></canvas>
                        </div>
                        <div class="col-md-6">
                            <h4>Monthly Demand per Pump</h4>
                            <canvas id="monthlyDemandChart"></canvas>
                        </div>
                    </div>

                    <div class="row mt-4">
                        <div class="col-md-6">
                            <h4>Estimated Months of Supply</h4>
                            <canvas id="supplyDurationChart"></canvas>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script>
        // Initialize charts with PHP data
        let totalSupplyChart, monthlyDemandChart, supplyDurationChart;
        
        function initializeCharts() {
            // Get data from PHP
            const data = <?php echo json_encode($data); ?>;
            const labels = data.map(d => "Pump " + d.pump);
            const totalSupply = data.map(d => d.total_supply);
            const monthlyDemand = data.map(d => d.monthly_demand);
            const monthsSupply = data.map(d => d.months_supply);

            // Bar Chart - Total Water Supply
            totalSupplyChart = new Chart(document.getElementById("totalSupplyChart"), {
                type: 'bar',
                data: {
                    labels: labels,
                    datasets: [{
                        label: "Total Supply (m³)",
                        data: totalSupply,
                        backgroundColor: ["#3e95cd", "#8e5ea2", "#3cba9f", "#e8c3b9", "#c45850", "#3e95cd", "#8e5ea2"],
                        borderWidth: 1
                    }]
                },
                options: {
                    responsive: true,
                    scales: { y: { beginAtZero: true } }
                }
            });

            // Line Chart - Monthly Demand
            monthlyDemandChart = new Chart(document.getElementById("monthlyDemandChart"), {
                type: 'line',
                data: {
                    labels: labels,
                    datasets: [{
                        label: "Monthly Demand (m³)",
                        data: monthlyDemand,
                        borderColor: "#c45850",
                        backgroundColor: "rgba(255, 99, 132, 0.2)",
                        fill: true,
                        tension: 0.3
                    }]
                },
                options: {
                    responsive: true,
                    scales: { y: { beginAtZero: true } }
                }
            });

            // Pie Chart - Estimated Months of Supply
            supplyDurationChart = new Chart(document.getElementById("supplyDurationChart"), {
                type: 'pie',
                data: {
                    labels: labels,
                    datasets: [{
                        label: "Months of Supply",
                        data: monthsSupply,
                        backgroundColor: ["#3e95cd", "#8e5ea2", "#3cba9f", "#e8c3b9", "#c45850", "#3e95cd", "#8e5ea2"],
                    }]
                },
                options: {
                    responsive: true,
                    plugins: {
                        legend: { position: 'right' }
                    }
                }
            });
        }

        function updateYear() {
            const year = document.getElementById('yearSelect').value;
            window.location.href = `demand_forecasting.php?year=${year}`;
        }

        // Download as PDF
        document.getElementById('downloadPDF').addEventListener('click', function() {
            const { jsPDF } = window.jspdf;
            const doc = new jsPDF('p', 'pt', 'a4');
            
            // Add title
            const year = document.getElementById('yearSelect').value;
            doc.setFontSize(18);
            doc.text(`Water Pump Demand Forecasting Report - ${year}`, 40, 40);
            
            // Add date
            const today = new Date();
            doc.setFontSize(10);
            doc.text(`Generated on: ${today.toLocaleDateString()}`, 40, 60);
            
            // Capture the report content
            html2canvas(document.getElementById('reportContent')).then(canvas => {
                const imgData = canvas.toDataURL('image/png');
                const imgWidth = doc.internal.pageSize.getWidth() - 80;
                const imgHeight = (canvas.height * imgWidth) / canvas.width;
                
                // Add the captured image to PDF
                doc.addImage(imgData, 'PNG', 40, 80, imgWidth, imgHeight);
                
                // Save the PDF
                doc.save(`Water_Pump_Demand_Forecast_${year}.pdf`);
            });
        });

         // Excel Upload Handler
                document.getElementById('upload-form').addEventListener('submit', function(event) {
                    event.preventDefault();

                    let fileInput = document.getElementById('excelFile');
                    let file = fileInput.files[0];

                    if (!file) {
                        alert('Please select a file');
                        return;
                    }

                    // Check file size (5MB max)
                    if (file.size > 5 * 1024 * 1024) {
                        alert('File is too large. Maximum 5MB allowed.');
                        return;
                    }

                    let reader = new FileReader();

                    reader.onload = function(e) {
                        try {
                            let data = new Uint8Array(e.target.result);
                            let workbook = XLSX.read(data, {type: 'array'});
                            let sheetName = workbook.SheetNames[0];
                            let sheet = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], {header: 1, raw: false});

                            // Prepare data to send to server
                            let postData = [];
                            for (let i = 1; i < sheet.length; i++) { // Skip the header row
                                let row = sheet[i];
                                // Check if the row has enough data
                                if (row.length >= 4) {
                                    postData.push({
                                        pump: parseInt(row[0]) || 0,
                                        barangays: String(row[1]) || '',
                                        total_supply: parseFloat(row[2]) || 0,
                                        monthly_demand: parseFloat(row[3]) || 0
                                    });
                                }
                            }

                            // Send data to server via AJAX
                            fetch('save_pump_data.php', {
                                method: 'POST',
                                headers: {
                                    'Content-Type': 'application/json'
                                },
                                body: JSON.stringify({data: postData})
                            })
                            .then(response => response.json())
                            .then(data => {
                                if (data.status === 'success') {
                                    alert('Data uploaded successfully');
                                    window.location.reload();
                                } else {
                                    alert('Error: ' + (data.message || 'Unknown error'));
                                }
                            })
                            .catch(error => {
                                console.error('Error:', error);
                                alert('Error uploading data');
                            });
                        } catch (error) {
                            console.error('Error processing Excel:', error);
                            alert('Error processing Excel file');
                        }
                    };

                    reader.onerror = function() {
                        alert('Error reading file');
                    };

                    reader.readAsArrayBuffer(file);
                });

        // Download as Excel
        document.getElementById('downloadExcel').addEventListener('click', function() {
            // Get table data
            const table = document.getElementById('dataTableContainer');
            const rows = table.querySelectorAll('tr');
            const data = [];
            
            // Extract data from table
            for (let i = 0; i < rows.length; i++) {
                const row = [], cols = rows[i].querySelectorAll('td, th');
                
                for (let j = 0; j < cols.length; j++) {
                    row.push(cols[j].innerText);
                }
                
                data.push(row);
            }
            
            // Create workbook
            const wb = XLSX.utils.book_new();
            const ws = XLSX.utils.aoa_to_sheet(data);
            
            // Add worksheet to workbook
            XLSX.utils.book_append_sheet(wb, ws, "Demand Forecast");
            
            // Generate year for filename
            const year = document.getElementById('yearSelect').value;
            
            // Save the file
            XLSX.writeFile(wb, `Water_Pump_Demand_Forecast_${year}.xlsx`);
        });

        // Initialize charts when page loads
        document.addEventListener('DOMContentLoaded', function() {
            initializeCharts();
        });
    </script>
</body>
</html>