<?php
session_start();
include('includes/dbconn.php'); // Database connection script

$companyId = $_SESSION['company_id']; // From session

// Set the start_date to the beginning of the current year
//$start_date = date('01-01-Y'); // First day of the current year
//$end_date = date('Y-m-d'); // Today's date

// Set the start_date to the beginning of the current year
$start_date = date('Y-01-01'); // First day of the current year
$end_date = date('Y-m-d'); // Today's date

// Display format for input fields
$start_date_display = date('Y-m-d', strtotime($start_date));
$end_date_display = date('Y-m-d', strtotime($end_date));

// Check if the filter button has been clicked
if (isset($_GET['filter'])) {
    // Check if specific dates are provided
    if (!empty($_GET['start_date']) && !empty($_GET['end_date'])) {
        $start_date = date('Y-m-d', strtotime($_GET['start_date']));
        $end_date = date('Y-m-d', strtotime($_GET['end_date']));
    }

    // Prepare SQL with date range filtering
    $expenseSql = "SELECT expense_date, description, amount, category 
                   FROM expenses 
                   WHERE company_id = ? AND expense_date BETWEEN ? AND ?
                   ORDER BY expense_date DESC";
    $expenseStmt = $conn->prepare($expenseSql);
    $expenseStmt->bind_param("iss", $companyId, $start_date, $end_date);
} else {
    // If no filter is set, fetch all expenses from the start of the year
    $expenseSql = "SELECT expense_date, description, amount, category 
                   FROM expenses 
                   WHERE company_id = ? AND expense_date >= ?
                   ORDER BY expense_date DESC";
    $expenseStmt = $conn->prepare($expenseSql);
    $expenseStmt->bind_param("is", $companyId, $start_date);
}

$expenseStmt->execute();
$expenseResult = $expenseStmt->get_result();

$expenses = [];
$categoryTotals = [];

// Loop through the expenses and accumulate the totals for each category
while ($row = $expenseResult->fetch_assoc()) {
    // Push each expense to the array
    $expenses[] = $row;
    // Initialize the category total if not set
    if (!isset($categoryTotals[$row['category']])) {
        $categoryTotals[$row['category']] = 0;
    }
    // Add the amount to the appropriate category total
    $categoryTotals[$row['category']] += $row['amount'];
}

// Get the categories from the accumulated totals
$categories = array_keys($categoryTotals);

// Close the prepared statement
$expenseStmt->close();
?>

<!DOCTYPE html>
<html lang="en">

<head>

    <?php
    $title = "Expense Reports";
    include 'partials/title-meta.php';
    ?>

    <link href="assets/libs/mohithg-switchery/switchery.min.css" rel="stylesheet" type="text/css" />
    <link href="assets/libs/multiselect/css/multi-select.css" rel="stylesheet" type="text/css" />
    <link href="assets/libs/select2/css/select2.min.css" rel="stylesheet" type="text/css" />
    <link href="assets/libs/selectize/css/selectize.bootstrap3.css" rel="stylesheet" type="text/css" />
    <link href="assets/libs/bootstrap-touchspin/jquery.bootstrap-touchspin.min.css" rel="stylesheet" type="text/css" />
    <!-- Bootstrap Datepicker CSS -->
    <link href="assets/libs/bootstrap-datepicker/css/bootstrap-datepicker.min.css" rel="stylesheet" type="text/css" />
    
    <!-- Bootstrap Datepicker JS -->
    <script src="assets/libs/bootstrap-datepicker/js/bootstrap-datepicker.min.js"></script>

    <?php
    include 'partials/head-css.php';
    ?>
    
    <style>
        table {
            width: 100%;
            border-collapse: collapse;
        }
        table, th, td {
            border: 1px solid black;
        }
        th, td {
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
        }
        tr:nth-child(even) {
            background-color: #f9f9f9;
        }
    </style>

</head>

<?php
include 'partials/body.php';
?>

<!-- Begin page -->
<div id="wrapper">

    <?php
    $pagetitle = "Expense Reports";
    include 'partials/menu.php';
    ?>

    <!-- ============================================================== -->
    <!-- Start Page Content here -->
    <!-- ============================================================== -->

    <div class="content-page">
        <div class="content">

            <!-- Start Content-->
            <div class="container-fluid">

                <div class="card">
                    <div class="card-body">
                        <h4 class="header-title">Expense Reports</h4>
                            <!-- <form method="get" action="">
                         
                                <label for="start_date">FROM</label>
                                <input type="date" name="start_date" id="start_date" value="" class="form-control" placeholder="dd / mm / yyyy" autocomplete="off">
                                
                                <label for="end_date">TO</label>
                                <input type="date" name="end_date" id="end_date" value="" class="form-control" placeholder="dd / mm / yyyy" autocomplete="off">
                                
                                
                                <button class="btn btn-primary" type="submit" name="filter">Filter</button>
                                <br>
                            </form> -->
                            
                            <form method="GET" action="">
                                <div class="report-header" style="width: 40%; margin-left: auto;">
                                    <table class="header-table" style="width: 100%; border-collapse: collapse; margin-bottom: 10px;">
                                        <tr>
                                            <td style="border: 1px solid #ccc; padding: 5px; font-size: 0.8em;">FROM</td>
                                            <td style="border: 1px solid #ccc; padding: 5px; font-size: 0.8em;">TO</td>
                                        </tr>
                                        <tr>
                                        <td style="border: 1px solid #ccc; padding: 5px; font-size: 0.8em;">
                                            <input type="date" name="start_date" id="start_date" value="<?php echo $start_date_display; ?>" class="form-control" placeholder="dd / mm / yyyy" autocomplete="off">
                                        </td>
                                        <td style="border: 1px solid #ccc; padding: 5px; font-size: 0.8em;">
                                            <input type="date" name="end_date" id="end_date" value="<?php echo $end_date_display; ?>" class="form-control" placeholder="dd / mm / yyyy" autocomplete="off">
                                        </td>
                                    </tr>


                                    </table>
                                    <button class="btn btn-primary" type="submit" name="filter">Filter</button>
                                </div>
                            </form>

                                <br>

                            <table>
                                <thead>
                                    <tr>
                                        <th>Date</th>
                                        <th>Description</th>
                                        <?php foreach ($categories as $category): ?>
                                            <th><?php echo htmlspecialchars($category); ?></th>
                                        <?php endforeach; ?>
                                        <th>Total</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($expenses as $row): ?>
                                        <tr>
                                            <td><?php echo htmlspecialchars($row['expense_date']); ?></td>
                                            <td><?php echo htmlspecialchars($row['description']); ?></td>
                                            <?php
                                            // Initialize row total
                                            $rowTotal = 0;
                                            // Display columns for each category
                                            foreach ($categories as $category):
                                                $amount = $category == $row['category'] ? $row['amount'] : 0;
                                                $rowTotal += $amount;
                                                echo '<td>' . number_format($amount, 2) . '</td>';
                                            endforeach;
                                            ?>
                                            <td><?php echo number_format($rowTotal, 2); ?></td>
                                        </tr>
                                    <?php endforeach; ?>
                                </tbody>
                                <tfoot>
                                    <tr>
                                        <th colspan="2">Category Totals</th>
                                        <?php foreach ($categoryTotals as $total): ?>
                                            <th><?php echo number_format($total, 2); ?></th>
                                        <?php endforeach; ?>
                                        <th><?php echo number_format(array_sum($categoryTotals), 2); ?></th>
                                    </tr>
                                </tfoot>
                            </table>


                    </div> <!-- end card-body-->
                </div> <!-- end card-->

            </div> <!-- container -->

        </div> <!-- content -->

        <?php
        include 'partials/footer.php';
        ?>

    </div>

    <!-- ============================================================== -->
    <!-- End Page content -->
    <!-- ============================================================== -->

</div>
<!-- END wrapper -->

<?php
include 'partials/right-sidebar.php';
?>

<?php
include 'partials/footer-scripts.php';
?>

<!-- Plugin js-->
<script src="assets/libs/parsleyjs/parsley.min.js"></script>

<!-- Validation init js-->
<script src="assets/js/pages/form-validation.init.js"></script>

<!-- App js -->
<script src="assets/js/app.min.js"></script>
<script src="assets/libs/selectize/js/standalone/selectize.min.js"></script>
<script src="assets/libs/mohithg-switchery/switchery.min.js"></script>
<script src="assets/libs/multiselect/js/jquery.multi-select.js"></script>
<script src="assets/libs/select2/js/select2.min.js"></script>
<script src="assets/libs/jquery-mockjax/jquery.mockjax.min.js"></script>
<script src="assets/libs/devbridge-autocomplete/jquery.autocomplete.min.js"></script>
<script src="assets/libs/bootstrap-touchspin/jquery.bootstrap-touchspin.min.js"></script>
<script src="assets/libs/bootstrap-maxlength/bootstrap-maxlength.min.js"></script>
<script>
$(document).ready(function(){
    $('.datepicker').datepicker({
        format: 'dd/mm/yyyy',
        autoclose: true,
        todayHighlight: true
    });
});
</script>

<script>
    $(document).ready(function() {
        $('#client_name').select2();
    });
</script>
<script>
function calculateTotal() {
    var quantity = document.getElementById('quantity').value;
    var unitPrice = document.getElementById('unit_price').value;
    var total = quantity * unitPrice;
    
    document.getElementById('total_amount').value = total.toFixed(2); // Set total amount with two decimal places
}
</script>

<script>
    // Get today's date
    var today = new Date();

    // Format it as YYYY-MM-DD (which is the format commonly used in databases)
    var formattedDate = today.toISOString().substr(0, 10);

    // Set the default value of the input field to today's date
    document.getElementById("start_date").value = formattedDate;
</script>

</body>

    <?php
//$categoryStmt->close();
//$expenseStmt->close();
?>

</html>
