<?php
include("layout.php");
include("db.php");

// Handle filter input
$selectedMonth = isset($_GET['month']) ? intval($_GET['month']) : date('n');
$selectedYear = isset($_GET['year']) ? intval($_GET['year']) : date('Y');

// Fetch totals based on selected month/year
$salesQuery = "
    SELECT COALESCE(SUM(net_amount), 0) AS total_sales 
    FROM sales 
    WHERE MONTH(sale_date) = $selectedMonth AND YEAR(sale_date) = $selectedYear
";
$purchasesQuery = "
    SELECT COALESCE(SUM(net_amount), 0) AS total_purchases 
    FROM purchases 
    WHERE MONTH(purchase_date) = $selectedMonth AND YEAR(purchase_date) = $selectedYear
";
$expensesQuery = "
    SELECT COALESCE(SUM(amount), 0) AS total_expenses 
    FROM expenses 
    WHERE MONTH(expense_date) = $selectedMonth AND YEAR(expense_date) = $selectedYear
";

// Execute queries
$totalSales = mysqli_fetch_assoc(mysqli_query($conn, $salesQuery))['total_sales'];
$totalPurchases = mysqli_fetch_assoc(mysqli_query($conn, $purchasesQuery))['total_purchases'];
$totalExpenses = mysqli_fetch_assoc(mysqli_query($conn, $expensesQuery))['total_expenses'];

$netProfit = $totalSales - $totalPurchases - $totalExpenses;
?>

<div class="page-wrapper">
    <div class="content">
        <div class="page-header">
            <div class="add-item d-flex justify-content-between w-100">
                <div class="page-title">
                    <h4>Profit & Loss Report</h4>
                    <h6>Summary for selected month and year</h6>
                </div>
            </div>
        </div>

        <!-- Filter Form -->
        <form method="GET" class="mb-4">
            <div class="row">
                <div class="col-md-3">
                    <label>Month</label>
                    <select name="month" class="form-control">
                        <?php
                        for ($m = 1; $m <= 12; $m++) {
                            $monthName = date('F', mktime(0, 0, 0, $m, 10));
                            $selected = ($m == $selectedMonth) ? "selected" : "";
                            echo "<option value='$m' $selected>$monthName</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label>Year</label>
                    <select name="year" class="form-control">
                        <?php
                        $currentYear = date('Y');
                        for ($y = $currentYear - 5; $y <= $currentYear + 1; $y++) {
                            $selected = ($y == $selectedYear) ? "selected" : "";
                            echo "<option value='$y' $selected>$y</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="col-md-3 d-flex align-items-end">
                    <button type="submit" class="btn btn-primary">Filter</button>
                </div>
            </div>
        </form>

        <!-- Summary Table -->
        <div class="card">
            <div class="card-body">
                <h5 class="mb-3">Report for <?= date('F', mktime(0, 0, 0, $selectedMonth, 10)) . " $selectedYear" ?></h5>
                <table class="table table-bordered">
                    <tr>
                        <th>Total Sales</th>
                        <td><?= number_format($totalSales, 2) ?></td>
                    </tr>
                    <tr>
                        <th>Total Purchases</th>
                        <td><?= number_format($totalPurchases, 2) ?></td>
                    </tr>
                    <tr>
                        <th>Total Expenses</th>
                        <td><?= number_format($totalExpenses, 2) ?></td>
                    </tr>
                    <tr>
                        <th>Net Profit</th>
                        <td><strong><?= number_format($netProfit, 2) ?></strong></td>
                    </tr>
                </table>
            </div>
        </div>
    </div>
</div>
