<?php
require_once "../config/auth.php";
require_once "../config/db.php";
require_once "../config/helpers.php";

$pageTitle = "Profit Report";
require_once "../includes/header.php";
require_once "../includes/sidebar.php";
require_once "../includes/topbar.php";

// ================= FILTER =================
$month = date('m');
$year  = date('Y');

if(isset($_GET['month_picker'])){
    $date = explode('-', $_GET['month_picker']);
    $year = $date[0];
    $month = $date[1];
}

// ================= INCOME (REAL PAYMENTS) =================
$income = $conn->query("
SELECT IFNULL(SUM(amount),0) as total 
FROM payments 
WHERE MONTH(payment_date)='$month'
AND YEAR(payment_date)='$year'
")->fetch_assoc()['total'];

// ================= EXPENSE =================
$expense = $conn->query("
SELECT IFNULL(SUM(amount),0) as total 
FROM expenses 
WHERE MONTH(expense_date)='$month'
AND YEAR(expense_date)='$year'
")->fetch_assoc()['total'];

// ================= SERVICE COST =================
$cost = $conn->query("
SELECT IFNULL(SUM(s.cost),0) as total_cost
FROM payments p
JOIN invoices i ON p.invoice_id = i.id
JOIN invoice_items it ON it.invoice_id = i.id
JOIN services s ON s.service_name = it.description
WHERE MONTH(p.payment_date)='$month'
AND YEAR(p.payment_date)='$year'
")->fetch_assoc()['total_cost'];

// ================= PROFIT =================
$profit = $income - $cost;

// ================= CUSTOMER-WISE (REAL INCOME) =================
$customers = $conn->query("
SELECT 
c.name,
IFNULL(SUM(p.amount),0) as total_income
FROM customers c
LEFT JOIN invoices i ON i.customer_id = c.id
LEFT JOIN payments p ON p.invoice_id = i.id
    AND MONTH(p.payment_date)='$month'
    AND YEAR(p.payment_date)='$year'
GROUP BY c.id
ORDER BY total_income DESC
");
?>

<div class="main-content">
<div class="col-lg-12">

<div class="topbar d-flex justify-content-between align-items-center">
    <h5><i class="fa fa-chart-line"></i> Profit Report</h5>

    <form method="GET" class="d-flex gap-2">
        <input type="month" name="month_picker" class="form-control"
        value="<?= $year.'-'.str_pad($month,2,'0',STR_PAD_LEFT) ?>">
        <button class="btn btn-primary btn-sm">Apply</button>
    </form>
</div>

<div class="p-4">

<!-- ================= SUMMARY ================= -->
<div class="row g-3 mb-4">

<div class="col-md-3">
    <div class="card p-3 text-white" style="background:#4e73df;">
        <h6>Total Income</h6>
        <h4>₹<?= number_format($income,2) ?></h4>
    </div>
</div>

<div class="col-md-3">
    <div class="card p-3 text-white" style="background:#e74a3b;">
        <h6>Expenses</h6>
        <h4>₹<?= number_format($expense,2) ?></h4>
    </div>
</div>

<div class="col-md-3">
    <div class="card p-3 text-white" style="background:#6c757d;">
        <h6>Service Cost</h6>
        <h4>₹<?= number_format($cost,2) ?></h4>
    </div>
</div>

<div class="col-md-3">
    <div class="card p-3 text-white" style="background:#1cc88a;">
        <h6>Profit</h6>
        <h4>₹<?= number_format($profit,2) ?></h4>
    </div>
</div>

</div>

<!-- ================= CUSTOMER TABLE ================= -->
<div class="card p-3 shadow-sm">

<h5>Customer-wise Income (Actual Received)</h5>

<div class="table-responsive">
<table class="table table-bordered table-striped mt-3">

<thead class="table-dark">
<tr>
<th>Customer</th>
<th>Received</th>
</tr>
</thead>

<tbody>

<?php if($customers->num_rows > 0){ ?>
<?php while($row = $customers->fetch_assoc()){ ?>
<tr>
<td><?= e($row['name']) ?></td>
<td>₹<?= number_format($row['total_income'],2) ?></td>
</tr>
<?php } ?>
<?php } else { ?>
<tr>
<td colspan="2" class="text-center">No data found</td>
</tr>
<?php } ?>

</tbody>

</table>
</div>

</div>

</div>
</div>
</div>

<?php require_once "../includes/footer.php"; ?>