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

$selectedMonth = $_GET['month'] ?? date('n');
$selectedYear = $_GET['year'] ?? date('Y');

// 🧾 Revenue
$salesRes = $conn->query("
    SELECT 
        COALESCE(SUM(net_amount), 0) AS revenue
    FROM sales
    WHERE MONTH(sale_date) = $selectedMonth AND YEAR(sale_date) = $selectedYear
");
$revenue = $salesRes->fetch_assoc()['revenue'] ?? 0;

// 📦 COGS — total quantity × cost_price at sale time
$cogsRes = $conn->query("
    SELECT 
        SUM(si.quantity * i.cost_price) AS cogs
    FROM sale_items si
    JOIN items i ON si.item_id = i.id
    JOIN sales s ON si.sale_id = s.id
    WHERE MONTH(s.sale_date) = $selectedMonth AND YEAR(s.sale_date) = $selectedYear
");
$cogs = $cogsRes->fetch_assoc()['cogs'] ?? 0;

// 💼 Expenses (only approved expenses)
$expenseRes = $conn->query("
    SELECT 
        COALESCE(SUM(amount), 0) AS total_expense
    FROM expenses
    WHERE MONTH(expense_date) = $selectedMonth AND YEAR(expense_date) = $selectedYear
    AND status = 'approved'
");
$expenses = $expenseRes->fetch_assoc()['total_expense'] ?? 0;

$grossProfit = $revenue - $cogs;
$netProfit = $grossProfit - $expenses;
?>

<div class="page-wrapper">
    <div class="content">
        <div class="page-header">
            <div class="d-flex justify-content-between align-items-center w-100">
                <div class="page-title">
                    <h4>Profit & Loss Statement</h4>
                    <h6>Financial summary for selected month and year</h6>
                </div>
            </div>
        </div>

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

        <div class="card">
            <div class="card-body">
                <h5>Profit & Loss – <?= date('F', mktime(0, 0, 0, $selectedMonth, 10)) ?> <?= $selectedYear ?></h5>
                <table class="table table-bordered">
                    <thead class="table-light">
                        <tr>
                            <th>Description</th>
                            <th class="text-end">Amount (Rs)</th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr>
                            <td><strong>Revenue</strong></td>
                            <td class="text-end"><?= number_format($revenue, 2) ?></td>
                        </tr>
                        <tr>
                            <td>Cost of Goods Sold (COGS)</td>
                            <td class="text-end"><?= number_format($cogs, 2) ?></td>
                        </tr>
                        <tr class="table-secondary">
                            <td><strong>Gross Profit</strong></td>
                            <td class="text-end"><strong><?= number_format($grossProfit, 2) ?></strong></td>
                        </tr>
                        <tr>
                            <td>Operating Expenses</td>
                            <td class="text-end"><?= number_format($expenses, 2) ?></td>
                        </tr>
                        <tr class="table-success">
                            <td><strong>Net Profit</strong></td>
                            <td class="text-end"><strong><?= number_format($netProfit, 2) ?></strong></td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
