<?php
include 'db.php';
include 'layout.php';

$customer_id = isset($_GET['customer_id']) ? intval($_GET['customer_id']) : 0;
$customer_name = '';

if ($customer_id) {
    $res = $conn->query("SELECT name FROM customers WHERE id = $customer_id");
    if ($res && $res->num_rows) {
        $row = $res->fetch_assoc();
        $customer_name = $row['name'];
    }
}
?>

<div class="main-wrapper">
    <div class="page-wrapper">
        <div class="content">

            <div class="page-header">
                <div class="page-title">
                    <h4>Customer Ledger</h4>
                    <h6>Only Outstanding Invoices & Relevant Payments</h6>
                </div>
            </div>

            <form method="GET" class="row mb-4">
                <div class="col-md-6">
                    <select name="customer_id" class="form-control select2" required>
                        <option value="">-- Select Customer --</option>
                        <?php
                        $customers = $conn->query("SELECT id, name FROM customers WHERE active = 1 ORDER BY name ASC");
                        while ($c = $customers->fetch_assoc()) {
                            $sel = ($customer_id == $c['id']) ? 'selected' : '';
                            echo "<option value='{$c['id']}' $sel>{$c['name']}</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="col-md-2">
                    <button type="submit" class="btn btn-primary">View Ledger</button>
                </div>
            </form>

            <?php if ($customer_id): ?>
                <div class="card">
                    <div class="card-header">
                        <h5>Ledger for: <strong><?= htmlspecialchars($customer_name) ?></strong></h5>
                    </div>
                    <div class="card-body">
                        <table class="table table-bordered">
                            <thead class="table-light">
                                <tr>
                                    <th>Date</th>
                                    <th>Type</th>
                                    <th>Ref ID</th>
                                    <th>Description</th>
                                    <th class="text-end">Debit (Outstanding)</th>
                                    <th class="text-end">Credit (Payment)</th>
                                    <th class="text-end">Running Balance</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php
                                $ledger = [];

                                // Invoices from cus_outstanding (only unpaid or partial)
                                $invoices = $conn->query("
                                    SELECT o.id, o.created_at AS t_date, o.amount, o.sale_id, o.status, s.invoice_number
                                    FROM cus_outstanding o
                                    JOIN sales s ON o.sale_id = s.id
                                    WHERE o.customer_id = $customer_id AND o.status IN ('unpaid', 'partial')
                                ");
                                while ($row = $invoices->fetch_assoc()) {
                                    $ledger[] = [
                                        'date' => $row['t_date'],
                                        'type' => 'Invoice',
                                        'ref_id' => $row['sale_id'],
                                        'desc' => 'Outstanding from Invoice #' . $row['invoice_number'],
                                        'debit' => $row['amount'],
                                        'credit' => 0
                                    ];
                                }

                                // Payments made by the customer
                                $payments = $conn->query("
                                    SELECT id, payment_date AS t_date, amount_paid, remarks
                                    FROM cus_payments
                                    WHERE customer_id = $customer_id
                                ");
                                while ($row = $payments->fetch_assoc()) {
                                    $ledger[] = [
                                        'date' => $row['t_date'],
                                        'type' => 'Payment',
                                        'ref_id' => $row['id'],
                                        'desc' => $row['remarks'] ?: 'Payment Received',
                                        'debit' => 0,
                                        'credit' => $row['amount_paid']
                                    ];
                                }

                                // Sort by date
                                usort($ledger, fn($a, $b) => strtotime($a['date']) <=> strtotime($b['date']));

                                // Totals
                                $balance = 0;
                                $total_debit = 0;
                                $total_credit = 0;

                                foreach ($ledger as $entry):
                                    $balance += $entry['debit'];
                                    $balance -= $entry['credit'];
                                    $total_debit += $entry['debit'];
                                    $total_credit += $entry['credit'];
                                ?>
                                    <tr>
                                        <td><?= date('Y-m-d', strtotime($entry['date'])) ?></td>
                                        <td><?= $entry['type'] ?></td>
                                        <td>#<?= $entry['ref_id'] ?></td>
                                        <td><?= htmlspecialchars($entry['desc']) ?></td>
                                        <td class="text-end"><?= $entry['debit'] > 0 ? number_format($entry['debit'], 2) : '' ?></td>
                                        <td class="text-end"><?= $entry['credit'] > 0 ? number_format($entry['credit'], 2) : '' ?></td>
                                        <td class="text-end"><?= number_format($balance, 2) ?></td>
                                    </tr>
                                <?php endforeach; ?>

                                <?php if (empty($ledger)): ?>
                                    <tr>
                                        <td colspan="7" class="text-center">No outstanding or payments for this customer.</td>
                                    </tr>
                                <?php else: ?>
                                    <tr class="table-secondary fw-bold">
                                        <td colspan="4" class="text-end">Total</td>
                                        <td class="text-end"><?= number_format($total_debit, 2) ?></td>
                                        <td class="text-end"><?= number_format($total_credit, 2) ?></td>
                                        <td class="text-end"><?= number_format($total_debit - $total_credit, 2) ?></td>
                                    </tr>
                                <?php endif; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            <?php endif; ?>

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

<script>
$(document).ready(function() {
    $('.select2').select2();
});
</script>
