<?php
require_once '../includes/auth.php';
requirePermission('ledgers', 'view');
require_once '../db.php';
require_once '../includes/header.php';
require_once '../includes/sidebar.php';


// Create client_payments table if not exists
$conn->query("
    CREATE TABLE IF NOT EXISTS client_payments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        client_id INT NOT NULL,
        company_id INT NOT NULL,
        payment_date DATE NOT NULL,
        amount DECIMAL(12,2) NOT NULL,
        payment_mode VARCHAR(30) DEFAULT 'Cash',
        reference_no VARCHAR(100),
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
");

// Company filter
$fco = isset($_GET['co']) ? (int)$_GET['co'] : $cid;
$companies = $conn->query("SELECT id, company_name FROM company_master ORDER BY company_name");

// Search filter
$search = trim($_GET['search'] ?? '');

// Build client list query
$coWhere   = $fco > 0 ? "AND c.company_id = $fco" : "";
$srchWhere = '';
if ($search !== '') {
    $s = $conn->real_escape_string($search);
    $srchWhere = "AND (c.name LIKE '%$s%' OR c.phone LIKE '%$s%')";
}

$clients = $conn->query("
    SELECT c.id, c.name, c.phone,
        COALESCE((SELECT SUM(si.net_total) FROM sales_invoices si
                  WHERE si.client_id = c.id AND si.status='final'
                    AND ($fco = 0 OR si.company_id = $fco)), 0) AS total_invoiced,
        COALESCE((SELECT SUM(sr.total_amount) FROM sales_returns sr
                  WHERE sr.client_id = c.id AND sr.status='final'
                    AND ($fco = 0 OR sr.company_id = $fco)), 0) AS total_returns,
        COALESCE((SELECT SUM(cp.amount) FROM client_payments cp
                  WHERE cp.client_id = c.id
                    AND ($fco = 0 OR cp.company_id = $fco)), 0) AS total_received
    FROM clients c
    WHERE c.status = 1 $coWhere $srchWhere
    ORDER BY c.name
");

// Compute outstanding
$totalOutstanding = 0;
$clientRows = [];
while ($row = $clients->fetch_assoc()) {
    $row['balance'] = $row['total_invoiced'] - $row['total_returns'] - $row['total_received'];
    $totalOutstanding += $row['balance'];
    $clientRows[] = $row;
}
?>

<div class="page-header">
    <h2><i class="fas fa-users" style="color:var(--primary);margin-right:8px;"></i>Client Ledger</h2>
</div>

<!-- Summary banner -->
<div class="panel" style="margin-bottom:16px;border-left:4px solid <?= $totalOutstanding > 0 ? 'var(--danger)' : 'var(--success)' ?>;">
    <div class="panel-body" style="padding:14px 20px;display:flex;align-items:center;gap:12px;">
        <i class="fas fa-wallet" style="font-size:22px;color:<?= $totalOutstanding > 0 ? 'var(--danger)' : 'var(--success)' ?>;"></i>
        <div>
            <div style="font-size:12px;color:var(--text-secondary);font-weight:600;text-transform:uppercase;letter-spacing:.05em;">Total Outstanding Balance (All Clients)</div>
            <div style="font-size:22px;font-weight:700;color:<?= $totalOutstanding > 0 ? 'var(--danger)' : 'var(--success)' ?>;">
                &#8377; <?= number_format($totalOutstanding, 2) ?>
            </div>
        </div>
    </div>
</div>

<!-- Filters -->
<div class="panel" style="margin-bottom:14px;">
    <div class="panel-body" style="padding:10px 16px;">
        <form method="get" style="display:flex;align-items:center;gap:8px;flex-wrap:wrap;">
            <label style="font-size:13px;font-weight:600;color:var(--text-secondary);margin:0;">Company:</label>
            <select name="co" onchange="this.form.submit()" style="font-size:13px;padding:5px 10px;border:1px solid var(--border);border-radius:6px;">
                <option value="0" <?= $fco == 0 ? 'selected' : '' ?>>All Companies</option>
                <?php
                $companies->data_seek(0);
                while ($co = $companies->fetch_assoc()): ?>
                <option value="<?= $co['id'] ?>" <?= $fco == $co['id'] ? 'selected' : '' ?>>
                    <?= htmlspecialchars($co['company_name']) ?>
                </option>
                <?php endwhile; ?>
            </select>
            <input type="text" name="search" placeholder="Search client name, phone..."
                   value="<?= htmlspecialchars($search) ?>"
                   style="font-size:13px;padding:5px 10px;border:1px solid var(--border);border-radius:6px;min-width:200px;">
            <button type="submit" class="btn btn-primary btn-sm"><i class="fas fa-search"></i> Search</button>
            <?php if ($search || $fco != $cid): ?>
            <a href="index.php" class="btn btn-secondary btn-sm"><i class="fas fa-times"></i> Clear</a>
            <?php endif; ?>
        </form>
    </div>
</div>

<!-- Client Table -->
<div class="panel">
    <div class="panel-header">
        <span class="panel-title">Client Balances</span>
        <span style="font-size:12px;color:var(--text-muted);"><?= count($clientRows) ?> clients</span>
    </div>
    <div style="overflow-x:auto;">
        <table>
            <thead>
                <tr>
                    <th>Client</th>
                    <th>Phone</th>
                    <th style="text-align:right;">Total Invoiced</th>
                    <th style="text-align:right;">Total Returns</th>
                    <th style="text-align:right;">Total Received</th>
                    <th style="text-align:right;">Outstanding Balance</th>
                    <th style="text-align:center;">Actions</th>
                </tr>
            </thead>
            <tbody>
            <?php if (!empty($clientRows)): foreach ($clientRows as $row): ?>
            <tr>
                <td>
                    <a href="view.php?client_id=<?= $row['id'] ?>&co=<?= $fco ?>"
                       style="font-weight:600;color:var(--primary);">
                        <?= htmlspecialchars($row['name']) ?>
                    </a>
                </td>
                <td style="color:var(--text-secondary);"><?= htmlspecialchars($row['phone']) ?></td>
                <td style="text-align:right;">&#8377; <?= number_format($row['total_invoiced'], 2) ?></td>
                <td style="text-align:right;">&#8377; <?= number_format($row['total_returns'], 2) ?></td>
                <td style="text-align:right;">&#8377; <?= number_format($row['total_received'], 2) ?></td>
                <td style="text-align:right;font-weight:700;color:<?= $row['balance'] > 0 ? 'var(--danger)' : 'var(--success)' ?>;">
                    &#8377; <?= number_format($row['balance'], 2) ?>
                </td>
                <td>
                    <div class="action-links" style="justify-content:center;gap:6px;">
                        <a href="view.php?client_id=<?= $row['id'] ?>&co=<?= $fco ?>"
                           class="action-btn view" title="View Ledger">
                            <i class="fas fa-eye"></i>
                        </a>
                        <a href="add_payment.php?client_id=<?= $row['id'] ?>"
                           class="btn btn-success btn-sm" title="Record Receipt"
                           style="padding:3px 10px;font-size:12px;">
                            <i class="fas fa-plus"></i> Receive
                        </a>
                    </div>
                </td>
            </tr>
            <?php endforeach; else: ?>
            <tr><td colspan="7" style="text-align:center;padding:40px;color:var(--text-muted);">
                <i class="fas fa-inbox" style="font-size:28px;display:block;margin-bottom:8px;"></i>
                No clients found.
            </td></tr>
            <?php endif; ?>
            </tbody>
        </table>
    </div>
</div>

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