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

$agency_id = $_SESSION['agency_id'];
$search = $_GET['search'] ?? '';

// Improved Query: Calculates Balance manually from the Payments table
$sql = "SELECT c.id, c.name, c.phone_number, c.email,
        (SELECT SUM(amount) FROM payments WHERE client_id = c.id AND transaction_type = 'Debit') as total_debit,
        (SELECT SUM(amount) FROM payments WHERE client_id = c.id AND transaction_type = 'Credit') as total_credit,
        (SELECT MAX(payment_date) FROM payments WHERE client_id = c.id) as last_activity
        FROM clients c 
        WHERE c.agency_id = $agency_id";

if (!empty($search)) {
    $sql .= " AND (c.name LIKE '%$search%' OR c.phone_number LIKE '%$search%' OR c.email LIKE '%$search%')";
}

$results = $conn->query($sql);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Manual Ledger | Digital Khata</title>
    <link href="https://fonts.googleapis.com/css2?family=Poppins:wght@300;400;500;600&display=swap" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <style>
        :root { --primary: #4e73df; --secondary: #1e293b; --success: #1cc88a; --danger: #e74a3b; --bg-color: #f8f9fc; --sidebar-width: 260px; }
        * { margin: 0; padding: 0; box-sizing: border-box; font-family: 'Poppins', sans-serif; }
        body { background-color: var(--bg-color); display: flex; }
        .sidebar { width: var(--sidebar-width); background: var(--secondary); height: 100vh; position: fixed; color: #fff; padding: 20px 0; }
        .nav-link { display: flex; align-items: center; padding: 12px 15px; color: rgba(255,255,255,0.7); text-decoration: none; border-radius: 8px; margin: 5px 20px; }
        .active { background: rgba(255,255,255,0.1); color: #fff; }
        .main-content { margin-left: var(--sidebar-width); width: calc(100% - var(--sidebar-width)); padding: 40px; }
        
        .card { background: #fff; padding: 25px; border-radius: 12px; box-shadow: 0 4px 6px rgba(0,0,0,0.02); }
        .search-container { display: flex; gap: 10px; margin-bottom: 25px; }
        .search-input { flex: 1; padding: 12px; border: 1px solid #ddd; border-radius: 10px; font-size: 14px; }
        .btn-search { background: var(--secondary); color: white; padding: 0 25px; border-radius: 10px; border: none; cursor: pointer; }

        table { width: 100%; border-collapse: collapse; }
        th { text-align: left; padding: 15px; background: #f8f9fc; color: #64748b; font-size: 11px; text-transform: uppercase; letter-spacing: 1px; }
        td { padding: 15px; border-bottom: 1px solid #f1f5f9; font-size: 14px; }
        
        .debit { color: var(--danger); font-weight: 600; }
        .credit { color: var(--success); font-weight: 600; }
        .balance { font-weight: 700; padding: 4px 8px; border-radius: 5px; }
        .btn-action { padding: 8px 15px; border-radius: 8px; text-decoration: none; font-size: 12px; font-weight: 600; }
    </style>
</head>
<body>

    <div class="sidebar">
        <div style="padding: 0 25px 30px; font-weight: 600; font-size: 18px;"><i class="fas fa-plane-departure text-primary"></i> TRAVEL SAAS</div>
        <a href="dashboard.php" class="nav-link"><i class="fas fa-home"></i> Dashboard</a>
        <a href="clients.php" class="nav-link "><i class="fas fa-users"></i> My Clients</a>
        <a href="queries.php" class="nav-link "><i class="fas fa-question-circle"></i> Enquiries</a>
        <a href="staff.php" class="nav-link"><i class="fas fa-user-tie"></i> Manage Staff</a>
        <a href="marketplace.php" class="nav-link"><i class="fas fa-shopping-cart"></i> B2B Marketplace</a>
        <a href="umrah_vouchers.php" class="nav-link"><i class="fas fa-kaaba"></i> Umrah Vouchers</a>
        <a href="ticket_vouchers.php" class="nav-link "><i class="fas fa-ticket-alt"></i> Ticket Vouchers</a>
        <a href="posters.php" class="nav-link"><i class="fas fa-image"></i> Poster Maker</a>
        <a href="ledger.php" class="nav-link active"><i class="fas fa-wallet"></i>Clients Account</a>
        <?php if($_SESSION['user_role'] == 'AgencyOwner'): ?>
            
            <a href="agency_profile.php" class="nav-link"><i class="fas fa-cog"></i> Settings</a>
        <?php endif; ?>
        <div style="position: absolute; bottom: 20px; width: 100%;">
            <a href="logout.php" class="nav-link" style="color: #ff7675;"><i class="fas fa-sign-out-alt"></i> Logout</a>
        </div>
    </div>

    <div class="main-content">
        <h1>Accounts Ledger</h1>
        <p style="color: #64748b; margin-bottom: 30px;">Manage manual Debit (Charges) and Credit (Payments) for all clients.</p>

        <form class="search-container" method="GET" action="">
            <input type="text" name="search" class="search-input" placeholder="Search by Client Name, Phone, or Email..." value="<?php echo htmlspecialchars($search); ?>">
            <button type="submit" class="btn-search">Search</button>
            <?php if($search): ?><a href="ledger.php" class="btn-search" style="background:#eee; color:#333; display:flex; align-items:center; text-decoration:none;">Clear</a><?php endif; ?>
        </form>

        <div class="card">
            <table>
                <thead>
                    <tr>
                        <th>Client Details</th>
                        <th>Total Debit (+)</th>
                        <th>Total Credit (-)</th>
                        <th>Final Balance</th>
                        <th>Last Entry</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                    <?php while($row = $results->fetch_assoc()): 
                        $debit = $row['total_debit'] ?? 0;
                        $credit = $row['total_credit'] ?? 0;
                        $balance = $debit - $credit;
                    ?>
                    <tr>
                        <td>
                            <b><?php echo $row['name']; ?></b><br>
                            <small style="color:#64748b;"><?php echo $row['phone_number']; ?></small>
                        </td>
                        <td class="debit">PKR <?php echo number_format($debit); ?></td>
                        <td class="credit">PKR <?php echo number_format($credit); ?></td>
                        <td>
                            <span class="balance" style="background: <?php echo ($balance > 0 ? '#fff5f5' : '#f0fff4'); ?>; color: <?php echo ($balance > 0 ? 'var(--danger)' : 'var(--success)'); ?>;">
                                PKR <?php echo number_format($balance); ?>
                            </span>
                        </td>
                        <td style="font-size: 12px; color: #94a3b8;">
                            <?php echo $row['last_activity'] ? date('d M, Y', strtotime($row['last_activity'])) : 'No activity'; ?>
                        </td>
                        <td>
                            <a href="add_transaction.php?client_id=<?php echo $row['id']; ?>" class="btn-action" style="background: var(--primary); color:white;"><i class="fas fa-plus"></i> Entry</a>
                            <a href="view_client_ledger.php?id=<?php echo $row['id']; ?>" class="btn-action" style="background: #f1f5f9; color: #475569;"><i class="fas fa-list"></i> History</a>
                        </td>
                    </tr>
                    <?php endwhile; ?>
                    <?php if($results->num_rows == 0) echo "<tr><td colspan='6' style='text-align:center; padding:40px; color:#94a3b8;'>No clients found.</td></tr>"; ?>
                </tbody>
            </table>
        </div>
    </div>
</body>
</html>