<?php
session_start();
include 'php/db_connection.php';
include 'sidebar.php';

// ইউজার লগইন চেক
if (!isset($_SESSION['user_id'])) {
    header("Location: login.php");
    exit();
}

// SQL Query: Sales + Due Collections একত্রে
$sql = "SELECT 
            s.customer_id,
            s.customer_name,
            s.customer_phone,
            s.customer_address,
            SUM(s.grand_total) AS total_grand,
            SUM(s.final_due) AS total_due,
            SUM(s.`change`) AS total_change,
            (SUM(s.final_due) - IFNULL(SUM(d.collected_amount), 0)) AS total_duedata,
            IFNULL(SUM(d.collected_amount), 0) AS total_collected
        FROM sales s
        LEFT JOIN due_collections d ON s.id = d.sale_id
        GROUP BY s.customer_id, s.customer_name, s.customer_phone, s.customer_address
        HAVING (SUM(s.final_due) - IFNULL(SUM(d.collected_amount), 0)) > 0";


// Query চালানো হচ্ছে
$result = mysqli_query($con, $sql);
if (!$result) {
    die("Query Failed: " . mysqli_error($con));
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Due List (Based on Change)</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="bg-light">

<div class="container mt-4">
    <h2 class="mb-4">Due List (Based on Due Change)</h2>

    <table class="table table-bordered table-striped">
        <thead class="table-dark">
            <tr>
                <th>ID</th>
                <th>Customer Name</th>
                <th>Phone</th>
                <th>Address</th>
                <th>Total Change (৳)</th>
                <th>Total Due (৳)</th>
                <th>Total Collected (৳)</th>
                <th>Due (৳)</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
        <?php while($row = mysqli_fetch_assoc($result)): ?>
            <tr>
                <td><?= htmlspecialchars($row['customer_id']) ?></td>
                <td><?= htmlspecialchars($row['customer_name']) ?></td>
                <td><?= htmlspecialchars($row['customer_phone']) ?></td>
                <td><?= htmlspecialchars($row['customer_address']) ?></td>
                <td><?= number_format((float)$row['total_change'], 2) ?></td>
                <td><?= number_format((float)$row['total_due'], 2) ?></td>
                <td><?= number_format((float)$row['total_collected'], 2) ?></td>
                <td><?= number_format((float)$row['total_duedata'], 2) ?></td>
                <td>
                    <a href="receive_due.php?customer_id=<?= urlencode($row['customer_id']) ?>" 
                       class="btn btn-success btn-sm">Receive Due</a>
                </td>
            </tr>
        <?php endwhile; ?>
        </tbody>
    </table>

    <a href="../../index.php" class="btn btn-secondary">Back to Dashboard</a>
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
