<?php
session_start();
include 'layouts/main.php';
include 'layouts/dbconn.php';

if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Fetch invoices and bucket them by age
$sql = "SELECT i.*, u.full_name, p.patient_number,
        DATEDIFF(NOW(), i.due_date) as days_overdue 
        FROM invoices i
        JOIN patients p ON i.patient_id = p.patient_id
        LEFT JOIN users u ON p.user_id = u.user_id
        WHERE i.status != 'PAID'
        ORDER BY days_overdue DESC";
$result = $conn->query($sql);

$buckets = [
    'current' => 0,
    'days_30' => 0,
    'days_60' => 0,
    'days_90' => 0,
    'days_90_plus' => 0
];

$invoices = [];
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $days = $row['days_overdue'];
        $amount = $row['balance_due'];

        if ($days <= 0)
            $buckets['current'] += $amount;
        elseif ($days <= 30)
            $buckets['days_30'] += $amount;
        elseif ($days <= 60)
            $buckets['days_60'] += $amount;
        elseif ($days <= 90)
            $buckets['days_90'] += $amount;
        else
            $buckets['days_90_plus'] += $amount;

        $invoices[] = $row;
    }
}
?>

<!DOCTYPE html>
<html>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Aging Report')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <link href="assets/libs/datatables.net-bs5/css/dataTables.bootstrap5.min.css" rel="stylesheet" type="text/css" />
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>

        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">

                    <div class="row">
                        <div class="col-12">
                            <div class="page-title-box d-sm-flex align-items-center justify-content-between">
                                <h4 class="mb-sm-0">Accounts Receivable Aging Report</h4>
                                <div class="page-title-right">
                                    <button class="btn btn-primary btn-sm"><i class="ri-download-line"></i> Export
                                        CSV</button>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="row">
                        <div class="col-lg-12">
                            <div class="card">
                                <div class="card-body">
                                    <div class="row text-center mb-4">
                                        <div class="col">
                                            <h5 class="text-success">Current</h5>
                                            <p class="fs-16 fw-bold">KES
                                                <?php echo number_format($buckets['current'], 2); ?></p>
                                        </div>
                                        <div class="col">
                                            <h5 class="text-warning">1-30 Days</h5>
                                            <p class="fs-16 fw-bold">KES
                                                <?php echo number_format($buckets['days_30'], 2); ?></p>
                                        </div>
                                        <div class="col">
                                            <h5 class="text-warning">31-60 Days</h5>
                                            <p class="fs-16 fw-bold">KES
                                                <?php echo number_format($buckets['days_60'], 2); ?></p>
                                        </div>
                                        <div class="col">
                                            <h5 class="text-danger">61-90 Days</h5>
                                            <p class="fs-16 fw-bold">KES
                                                <?php echo number_format($buckets['days_90'], 2); ?></p>
                                        </div>
                                        <div class="col">
                                            <h5 class="text-danger">90+ Days</h5>
                                            <p class="fs-16 fw-bold">KES
                                                <?php echo number_format($buckets['days_90_plus'], 2); ?></p>
                                        </div>
                                    </div>

                                    <table id="agingTable"
                                        class="table table-bordered dt-responsive nowrap table-striped align-middle"
                                        style="width:100%">
                                        <thead>
                                            <tr>
                                                <th>Patient</th>
                                                <th>Invoice #</th>
                                                <th>Due Date</th>
                                                <th>Days Overdue</th>
                                                <th>Balance Due</th>
                                                <th>Bucket</th>
                                                <th>Action</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($invoices as $inv): ?>
                                                <tr>
                                                    <td><?php echo htmlspecialchars($inv['first_name'] . ' ' . $inv['last_name']); ?>
                                                    </td>
                                                    <td><?php echo htmlspecialchars($inv['invoice_number']); ?></td>
                                                    <td><?php echo date('d M Y', strtotime($inv['due_date'])); ?></td>
                                                    <td>
                                                        <?php
                                                        $d = $inv['days_overdue'];
                                                        echo $d > 0 ? "<span class='text-danger'>{$d} days</span>" : "<span class='text-success'>Current</span>";
                                                        ?>
                                                    </td>
                                                    <td>KES <?php echo number_format($inv['balance_due'], 2); ?></td>
                                                    <td>
                                                        <?php
                                                        if ($d <= 0)
                                                            echo 'Current';
                                                        elseif ($d <= 30)
                                                            echo '1-30 Days';
                                                        elseif ($d <= 60)
                                                            echo '31-60 Days';
                                                        elseif ($d <= 90)
                                                            echo '61-90 Days';
                                                        else
                                                            echo '<span class="badge bg-danger">90+ Days</span>';
                                                        ?>
                                                    </td>
                                                    <td>
                                                        <a href="overdue_invoices.php"
                                                            class="btn btn-sm btn-ghost-primary">View</a>
                                                    </td>
                                                </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>

                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>
    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/libs/datatables.net/js/jquery.dataTables.min.js"></script>
    <script src="assets/libs/datatables.net-bs5/js/dataTables.bootstrap5.min.js"></script>
    <script>
        $(document).ready(function () {
            $('#agingTable').DataTable({
                order: [[3, 'desc']] // Order by days overdue desc
            });
        });
    </script>
</body>

</html>