<?php
// report.php
require 'connect.php';

// Check if month and year are selected, otherwise use current month and year
$selected_month = isset($_POST['month']) ? $_POST['month'] : date('m');
$selected_year = isset($_POST['year']) ? $_POST['year'] : date('Y');

// Get the first day of the selected month and the last day of the previous month
$first_day_selected_month = date('Y-m-01', strtotime("$selected_year-$selected_month-01"));
$first_day_last_month = date('Y-m-01', strtotime("first day of last month"));

// Fetch starting balance from the previous month
$starting_balance_sql = "
    SELECT SUM(CASE WHEN type = 'Deposit' THEN amount ELSE -amount END) AS total_balance 
    FROM transactions 
    WHERE date < '$first_day_selected_month'
";
$starting_balance_result = mysqli_query($conn, $starting_balance_sql);
$starting_balance_row = mysqli_fetch_assoc($starting_balance_result);
$starting_balance = $starting_balance_row['total_balance'] ?? 0;

// Fetch transactions for the selected month
$sql = "
    SELECT * FROM transactions 
    WHERE date >= '$first_day_selected_month' 
    AND date < DATE_ADD('$first_day_selected_month', INTERVAL 1 MONTH)
    ORDER BY date ASC
";
$result = mysqli_query($conn, $sql);

// Initialize the current balance to the starting balance
$current_balance = $starting_balance;
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Monthly Transaction Report</title>
    <link rel="stylesheet" href="css/bootstrap.min.css">
    <script>
        function printReport() {
            window.print();
        }
    </script>
</head>
<body>
    <div class="container mt-5">
        <h2 class="text-center mb-4">Monthly Transaction Report</h2>
        
        <!-- Month and Year Selection Form -->
        <form method="post" action="report.php" class="mb-4">
            <div class="form-row">
                <div class="col-md-4 mb-3">
                    <label for="month">Month:</label>
                    <select name="month" id="month" class="form-control">
                        <?php
                        // Generate the month dropdown
                        $months = [
                            '01' => 'January', '02' => 'February', '03' => 'March', '04' => 'April',
                            '05' => 'May', '06' => 'June', '07' => 'July', '08' => 'August',
                            '09' => 'September', '10' => 'October', '11' => 'November', '12' => 'December'
                        ];
                        foreach ($months as $month_num => $month_name) {
                            echo "<option value='$month_num' " . ($month_num == $selected_month ? 'selected' : '') . ">$month_name</option>";
                        }
                        ?>
                    </select>
                </div>

                <div class="col-md-4 mb-3">
                    <label for="year">Year:</label>
                    <select name="year" id="year" class="form-control">
                        <?php
                        // Generate the year dropdown
                        $current_year = date('Y');
                        for ($year = $current_year; $year >= 2000; $year--) {
                            echo "<option value='$year' " . ($year == $selected_year ? 'selected' : '') . ">$year</option>";
                        }
                        ?>
                    </select>
                </div>

                <div class="col-md-4 mb-3">
                    <button type="submit" class="btn btn-primary mt-4">Generate Report</button>
                </div>
            </div>
        </form>
        
        <!-- Display starting balance -->
        <p><strong>Starting Balance: </strong><?php echo number_format($starting_balance, 2, '.', ','); ?></p>
        
        <!-- Table displaying transactions -->
        <table class="table table-bordered table-striped">
            <thead>
                <tr>
                    <th>Date</th>
                    <th>Type</th>
                    <th>Amount</th>
                    <th>Name</th>
                    <th>Purpose/Usage</th>
                    <th>Balance After Transaction</th>
                </tr>
            </thead>
            <tbody>
                <?php
                if (mysqli_num_rows($result) > 0) {
                    while($row = mysqli_fetch_assoc($result)) {
                        // Update the balance after each transaction
                        if ($row['type'] == 'Deposit') {
                            $current_balance += $row['amount'];
                        } else {
                            $current_balance -= $row['amount'];
                        }
                        
                        // Display each transaction and the updated balance
                        echo "<tr>";
                        echo "<td>" . $row['date'] . "</td>";
                        echo "<td>" . $row['type'] . "</td>";
                        echo "<td>" . number_format($row['amount'], 2, '.', ',') . "</td>";
                        echo "<td>" . $row['name'] . "</td>";
                        echo "<td>" . $row['purpose_usage'] . "</td>";
                        echo "<td>" . number_format($current_balance, 2, '.', ',') . "</td>";
                        echo "</tr>";
                    }
                } else {
                    echo "<tr><td colspan='6' class='text-center'>No transactions found for this month.</td></tr>";
                }
                ?>
            </tbody>
        </table>

        <!-- Display closing balance -->
        <p><strong>Closing Balance: </strong><?php echo number_format($current_balance, 2, '.', ','); ?></p>

        <!-- Print Button -->
        <button onclick="printReport()" class="btn btn-secondary">Print Report</button>
        <a href="banking.php" class="btn btn-secondary ml-2">Go Back</a>
    </div>

    <script src="js/jquery-3.3.1.slim.min.js"></script>
    <script src="js/popper.min.js"></script>
    <script src="js/bootstrap.min.js"></script>
</body>
</html>
