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

$pageTitle = "Service Profit (Paid)";
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];
}

// ================= DATE RANGE (BETTER THAN MONTH()) =================
$start_date = $year . '-' . str_pad($month,2,'0',STR_PAD_LEFT) . '-01';
$end_date   = date('Y-m-t', strtotime($start_date));

// ================= DATA =================
$result = $conn->query("
SELECT 
    c.name AS customer,
    s.service_name,
    SUM(ii.amount) AS total_selling,
    SUM(s.cost) AS total_cost,
    (SUM(ii.amount) - SUM(s.cost)) AS profit
FROM invoice_items ii
JOIN invoices i ON ii.invoice_id = i.id
JOIN services s ON ii.service_id = s.id
JOIN customers c ON i.customer_id = c.id
WHERE i.payment_status = 'paid'
AND i.invoice_date BETWEEN '$start_date' AND '$end_date'
GROUP BY s.id
ORDER BY profit 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-money-bill"></i> Service Profit (Paid)</h5>

    <!-- MONTH FILTER -->
    <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">

<div class="table-responsive">

<table class="table table-bordered table-striped align-middle">

<thead class="table-dark">
<tr>
    <th>Customer</th>
    <th>Service</th>
    <th>Selling</th>
    <th>Cost</th>
    <th>Profit</th>
</tr>
</thead>

<tbody>

<?php if($result->num_rows > 0){ ?>
<?php while($row = $result->fetch_assoc()){ ?>

<tr>
    <td><?= e($row['customer']) ?></td>
    <td><?= e($row['service_name']) ?></td>

    <td>₹<?= number_format($row['total_selling'],2) ?></td>
    <td>₹<?= number_format($row['total_cost'],2) ?></td>

    <td>
        <?php 
        $profit = $row['profit'];
        $color = $profit >= 0 ? 'success' : 'danger';
        ?>
        <span class="badge bg-<?= $color ?>">
            ₹<?= number_format($profit,2) ?>
        </span>
    </td>
</tr>

<?php } ?>
<?php } else { ?>
<tr>
<td colspan="5" class="text-center">No data found</td>
</tr>
<?php } ?>

</tbody>

</table>

</div>

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

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