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

// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "dms_db"; // Updated to the new database

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Handle AJAX request to insert data into the database
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $data = json_decode(file_get_contents('php://input'), true);

    foreach ($data as $row) {
        $username = $conn->real_escape_string($row['username']);
        $full_name = $conn->real_escape_string($row['full_name']);
        $barangay = $conn->real_escape_string($row['barangay']);
        $month = $conn->real_escape_string($row['month']);
        $ttl_consumption = $conn->real_escape_string($row['ttl_consumption']);
        
        // Insert into database
        $sql = "INSERT INTO consumption_data (username, full_name, barangay, month, ttl_consumption) 
                VALUES ('$username', '$full_name', '$barangay', '$month', '$ttl_consumption')";
        
        // Execute the query
        if ($conn->query($sql)) {
            // Check if consumption is >= 10 and send notification if needed
            if ($ttl_consumption >= 10) {
                $check_sql = "SELECT * FROM notified_emails WHERE username = '$username' AND month = '$month' AND notification_type = 'high_consumption'";
                $check_result = $conn->query($check_sql);
                
                if ($check_result->num_rows == 0) {
                    $message = "Dear $full_name, your water consumption for $month is $ttl_consumption cubic meters, which exceeds our recommended threshold. Please consider conserving water.";
                    $notification_type = "high_consumption";
                    
                    $insert_sql = "INSERT INTO notified_emails (username, month, message, notification_type) 
                                  VALUES ('$username', '$month', '$message', '$notification_type')";
                    
                    if (!$conn->query($insert_sql)) {
                        error_log("Error inserting notification: " . $conn->error);
                    }
                    
                    // Optional: Send email notification
                    $email = $username;
                    $subject = "High Water Consumption Notification";
                    $headers = "From: admin@watersystem.com";
                    mail($email, $subject, $message, $headers);
                }
            }
        } else {
            // Log the error or handle it as needed
            error_log("Error inserting data: " . $conn->error);
        }
    }

    echo json_encode(["status" => "success"]);
    exit();
}

// Handle AJAX request to delete data from the database
if ($_SERVER['REQUEST_METHOD'] == 'DELETE') {
    parse_str(file_get_contents("php://input"), $data);
    $username = $conn->real_escape_string($data['username']);
    $month = $conn->real_escape_string($data['month']);

    // Delete from database
    $sql = "DELETE FROM consumption_data WHERE username = '$username' AND month = '$month'";
    
    if ($conn->query($sql)) {
        // Also delete any related notifications
        $delete_notif_sql = "DELETE FROM notified_emails WHERE username = '$username' AND month = '$month' AND notification_type = 'high_consumption'";
        $conn->query($delete_notif_sql);
        
        echo json_encode(["status" => "success"]);
    } else {
        echo json_encode(["status" => "error", "message" => $conn->error]);
    }
    exit();
}

// Fetch data for displaying in the table
$sql = "SELECT * FROM consumption_data";
$result = $conn->query($sql);
?>


<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>ADMIN</title>
    <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' />
    <link rel="stylesheet" href="css/css/spinners.css" type="text/css">

    <script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>
    <style>
        body {
            background-color: #f8f9fa; /* Light background for contrast */
        }
        h2 {
            color: #343a40; /* Darker text color */
        }
        .table {
            margin-top: 20px;
        }
        .table th, .table td {
            text-align: center;
        }
        .table th {
            background-color: #007bff; /* Bootstrap primary color */
            color: white;
        }
        .modal {
            display: none;
            position: fixed;
            z-index: 10000000;
            padding-top: 100px;
            left: 0;
            top: 0;
            width: 100%;
            height: 100%;
            overflow: auto;
            background-color: rgba(0, 0, 0, 0.4);
        }
        .modal-content {
            background-color: #fefefe;
            margin: auto;
            padding: 20px;
            border: 1px solid #888;
            width: 80%;
        }
        .close {
            color: #aaaaaa;
            float: right;
            font-size: 28px;
            font-weight: bold;
        }
        .close:hover,
        .close:focus {
            color: #000;
            text-decoration: none;
            cursor: pointer;
        }
    </style>
</head>
<body>
    <div id="wrapper">
        
        <nav class="navbar navbar-default navbar-cls-top" role="navigation" style="margin-bottom: 0">
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".sidebar-collapse">
                    <span class="sr-only">Toggle navigation</span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
                <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;"> 
        <!-- LOGOUT -->
        <?php echo $_SESSION['login_user']; ?> &nbsp; <a id="myBtn" class="btn btn-danger square-btn-adjust">Logout</a> </div>
        
        <!-- The Modal -->
        <div id="myModal" class="modal">

        <!-- Modal content -->
        <div class="modal-content" style="border-radius: 20px; height: 235px; width: 300px;">
        <span class="close">&times;</span>
        <h3>Logout</h3>
        <h4>Are you sure you want to logout?</h4>
        <br><br><br>
        <a onclick="document.getElementById('myModal').style.display='none'" class="logout" style="cursor: pointer; width: 100px; float:right; width: 70px; padding: 10px; color:white; margin-top:-20px; text-align: center; background-color: gray; border-radius: 5px; width: 100px;">No</a>
        <a href="logout.php" id="myBtn" class="logout"  style="cursor: pointer; width: 70px; padding: 10px; color:white; text-align: center; float:right; margin-top:-20px; margin-right: 5px; background-color: #4c9be1; border-radius: 5px; width: 100px;">Yes</a>

        </div>
        </div>
            </div>
        </nav>
        <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> Data Analytics</a></li>
                    <li><a class="active-menu" 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="" 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 class="" 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 Announcements</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> VMC</a></li>       
                </ul>
            </div>
        </nav>  
        <div id="page-wrapper" style="min-height: 30px;">
            <div id="page-inner" style="min-height: 30px; height: 600px;">
                <h2 style="color: white; background-color: gray; padding: 18px; margin-top: -0.1px; font-family: Arial; font-weight: bold; font-size: 27px;">&nbsp DASHBOARD</h2>  
                <hr />         
                <div class="row">
                    <div class="container" style=" width: 800px;">
                        <b><h2 class="text-center">WATER BILL MANAGEMENT</h2></b>

                        <!-- Excel Upload Form -->
                        <div class="card p-4 mb-4">
                            <form id="upload-form">
                                <div class="mb-3">
                                    <label for="excel_file" class="form-label">Upload Excel File</label>
                                    <input type="file" class="form-control" id="excel_file" accept=".xlsx, .xls">
                                </div>
                                <br>
                                <button type="submit" class="btn btn-primary w-100">Submit</button>
                            </form>
                        </div>

                        <table class="table table-striped">
                            <thead>
                                <tr>
                                    <th>Username</th>
                                    <th>Full Name</th>
                                    <th>Barangay</th>
                                    <th>Month</th>
                                    <th>Total Consumption (Cubic Meters)</th>
                                    <th>Action</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php if ($result->num_rows > 0): ?>
                                    <?php while($row = $result->fetch_assoc()): ?>
                                        <tr>
                                            <td><?php echo htmlspecialchars($row['username']); ?></td>
                                            <td><?php echo htmlspecialchars($row['full_name']); ?></td>
                                            <td><?php echo htmlspecialchars($row['barangay']); ?></td>
                                            <td><?php echo htmlspecialchars($row['month']); ?></td>
                                            <td><?php echo htmlspecialchars($row['ttl_consumption']); ?></td>
                                            <td>
                                                <button class="btn btn-danger delete-btn" data-username="<?php echo htmlspecialchars($row['username']); ?>" data-month="<?php echo htmlspecialchars($row['month']); ?>">Delete</button>
                                            </td>
                                        </tr>
                                    <?php endwhile; ?>
                                <?php else: ?>
                                    <tr>
                                        <td colspan="6">No data available.</td>
                                    </tr>
                                <?php endif; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="assets/js/jquery-1.10.2.js"></script>
    <script src="assets/js/bootstrap.js"></script>
    <script src="assets/js/custom.js"></script>
    
    <script>
    // Get the modal
    var modal = document.getElementById("myModal");

    // Get the button that opens the modal
    var btn = document.getElementById("myBtn");

    // Get the <span> element that closes the modal
    var span = document.getElementsByClassName("close")[0];

    // When the user clicks the button, open the modal 
    btn.onclick = function() {
    modal.style.display = "block";
    }

    // When the user clicks on <span> (x), close the modal
    span.onclick = function() {
    modal.style.display = "none";
    }

    // When the user clicks anywhere outside of the modal, close it
    window.onclick = function(event) {
    if (event.target == modal) {
        modal.style.display = "none";
    }
    }
    </script>

    <script>
document.getElementById('upload-form').addEventListener('submit', function(event) {
    event.preventDefault();

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

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

    let reader = new FileReader();

    reader.onload = function(e) {
        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 is complete
            if (row.length >= 5) { // Ensure the row has at least 5 columns of data
                postData.push({
                    username: String(row[0]),     // Adjust index according to your Excel structure
                    full_name: String(row[1]),    // Adjust index according to your Excel structure
                    barangay: String(row[2]),      // Adjust index according to your Excel structure
                    month: String(row[3]),         // Adjust index according to your Excel structure
                    ttl_consumption: Number(row[4]) // Adjust index according to your Excel structure
                });
            }
        }

        // Send data to server via AJAX
        fetch('bill_list.php', {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json'
            },
            body: JSON.stringify(postData)
        })
        .then(response => response.json())
        .then(data => {
            if (data.status === 'success') {
                alert('Data uploaded successfully');
                // Reload the page to update the dashboard
                window.location.reload();
            } else {
                                // Reload the page to update the dashboard
                window.location.reload();
            }
        })
        .catch(error => {
            console.error('Error:', error);
                                // Reload the page to update the dashboard
                window.location.reload();
        });
    };

    reader.readAsArrayBuffer(file);
});

</script>

<script>
    document.querySelectorAll('.delete-btn').forEach(button => {
        button.addEventListener('click', function() {
            const username = this.getAttribute('data-username');
            const month = this.getAttribute('data-month');

            if (confirm(`Are you sure you want to delete the record for ${username} for the month of ${month}?`)) {
                fetch('bill_list.php', {
                    method: 'DELETE',
                    headers: {
                        'Content-Type': 'application/x-www-form-urlencoded',
                    },
                    body: `username=${username}&month=${month}`
                })
                .then(response => response.json())
                .then(data => {
                    if (data.status === 'success') {
                        alert('Record deleted successfully');
                        window.location.reload(); // Reload the page to see the changes
                    } else {
                        alert('Error deleting record: ' + data.message);
                    }
                })
                .catch(error => {
                    console.error('Error:', error);
                    alert('Error deleting record');
                });
            }
        });
    });
</script>


<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.11.6/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.min.js"></script>
</body>
</html>
