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

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

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

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

// ================= INCOME (PAYMENTS ONLY) =================
$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;

// ================= PENDING =================
$pending = $conn->query("
SELECT IFNULL(SUM(i.total - IFNULL(paid.total_paid,0)),0) as pending
FROM invoices i
LEFT JOIN (
    SELECT invoice_id, SUM(amount) as total_paid
    FROM payments
    GROUP BY invoice_id
) paid ON i.id = paid.invoice_id
")->fetch_assoc()['pending'];

// ================= RENEWAL ALERTS =================
$today  = date('Y-m-d');
$next7  = date('Y-m-d', strtotime('+7 days'));
$next30 = date('Y-m-d', strtotime('+30 days'));
$next60 = date('Y-m-d', strtotime('+60 days'));

// next 7 days (critical)
$renew7 = $conn->query("
SELECT COUNT(*) as total FROM services
WHERE renewal_date BETWEEN '$today' AND '$next7'
")->fetch_assoc()['total'];

// next 30 days
$renew30 = $conn->query("
SELECT COUNT(*) as total FROM services
WHERE renewal_date BETWEEN '$today' AND '$next30'
")->fetch_assoc()['total'];

// next 60 days
$renew60 = $conn->query("
SELECT COUNT(*) as total FROM services
WHERE renewal_date BETWEEN '$today' AND '$next60'
")->fetch_assoc()['total'];

// ================= OUTSTANDING =================
$outstanding = $conn->query("
SELECT 
c.name,
c.phone,
SUM(i.total - IFNULL(paid.total_paid,0)) as due
FROM customers c
JOIN invoices i ON i.customer_id = c.id
LEFT JOIN (
    SELECT invoice_id, SUM(amount) as total_paid
    FROM payments
    GROUP BY invoice_id
) paid ON i.id = paid.invoice_id
GROUP BY c.id
HAVING due > 0
ORDER BY due DESC
LIMIT 5
");
?>

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

<div class="topbar d-flex justify-content-between align-items-center">
    <h5>Dashboard</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">

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

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

<div class="col-md-3">
<div class="card p-3 text-dark" style="background:#f6c23e;">
<h6>Pending</h6>
<h4>₹<?= number_format($pending,2) ?></h4>
</div>
</div>

</div>

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

<div class="col-md-4">
<div class="card p-3 text-white" style="background:#e74a3b;">
<h6>Next 7 Days</h6>
<h4><?= $renew7 ?></h4>
<a href="renewals.php" class="text-white">View</a>
</div>
</div>

<div class="col-md-4">
<div class="card p-3 text-dark" style="background:#f6c23e;">
<h6>Next 30 Days</h6>
<h4><?= $renew30 ?></h4>
<a href="renewals.php">View</a>
</div>
</div>

<div class="col-md-4">
<div class="card p-3 text-white" style="background:#36b9cc;">
<h6>Next 60 Days</h6>
<h4><?= $renew60 ?></h4>
<a href="renewals.php" class="text-white">View</a>
</div>
</div>

</div>

<!-- ================= OUTSTANDING ================= -->
<div class="card p-4 shadow-sm">
<h5>Top Customer Outstanding</h5>

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

<thead>
<tr>
<th>Customer</th>
<th>Phone</th>
<th>Due</th>
</tr>
</thead>

<tbody>

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

</tbody>
</table>
</div>

</div>

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

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