<?php
if (session_status() === PHP_SESSION_NONE) { session_start(); }
require_once __DIR__ . '/includes/db.php';
require_once __DIR__ . '/includes/functions.php';
global $pdo;
$clientId = isset($_GET['client_id']) ? (int)$_GET['client_id'] : (isset($_GET['id']) ? (int)$_GET['id'] : 0);
$needsPicker = ($clientId <= 0);
$role = strtolower($_SESSION['user_role'] ?? 'guest');
$companyId = function_exists('getCurrentCompanyId') ? getCurrentCompanyId() : ($_SESSION['company_id'] ?? null);
$isAdminTier = in_array($role, ['admin','super_admin']);
$applyCompanyFilter = $companyId && !$isAdminTier;
function colExists($table, $col) {
    static $CACHE = [];
    global $pdo;
    $t = strtolower($table);
    $c = strtolower($col);
    if (isset($CACHE[$t])) return in_array($c, $CACHE[$t], true);
    try { $stmt = $pdo->query("SHOW COLUMNS FROM `$table`"); $cols = $stmt ? $stmt->fetchAll(PDO::FETCH_COLUMN) : []; $cols = array_map('strtolower', $cols ?: []); $CACHE[$t] = $cols; return in_array($c, $cols, true); } catch (Throwable $e) { $CACHE[$t] = []; return false; }
}
$from = isset($_GET['from']) && $_GET['from'] !== '' ? $_GET['from'] : null;
$to = isset($_GET['to']) && $_GET['to'] !== '' ? $_GET['to'] : null;
$client = ['name'=>'Client','email'=>'','phone'=>''];
try {
    $selName = colExists('users','name') ? 'name' : (colExists('users','username') ? 'username' : 'NULL');
    $selEmail = colExists('users','email') ? 'email' : 'NULL';
    $selPhone = colExists('users','phone') ? 'phone' : 'NULL';
    $st = $pdo->prepare("SELECT {$selName} AS nm, {$selEmail} AS em, {$selPhone} AS ph FROM users WHERE id = ? LIMIT 1");
    $st->execute([$clientId]);
    $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];
    $client['name'] = (string)($row['nm'] ?? $client['name']);
    $client['email'] = (string)($row['em'] ?? '');
    $client['phone'] = (string)($row['ph'] ?? '');
} catch (Throwable $e) {}
$deals = [];
$allocs = [];
try {
    $hasAlloc = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
    if ($hasAlloc) {
        $selRef = colExists('allocations','allocation_ref') ? 'allocation_ref' : "NULL AS allocation_ref";
        $selPlotNo = colExists('allocations','plot_number') ? 'plot_number' : "NULL AS plot_number";
        $selPlotSize = colExists('allocations','plot_size') ? 'plot_size' : "NULL AS plot_size";
        $selStatus = colExists('allocations','status') ? 'status' : "'pending' AS status";
        $selCreated = colExists('allocations','created_at') ? 'created_at' : "NOW() AS created_at";
        $cmpClauseA = '';
        $paramsA = [$clientId];
        if ($applyCompanyFilter && colExists('allocations','company_id')) { $cmpClauseA = " AND (a.company_id = ? OR a.company_id IS NULL)"; $paramsA[] = $companyId; }
        $qA = "
            SELECT a.id, a.property_id, {$selRef}, {$selPlotNo}, {$selPlotSize}, {$selStatus}, {$selCreated},
                   p.title AS property_title, p.price AS property_price, p.address AS property_address
            FROM allocations a
            LEFT JOIN properties p ON a.property_id = p.id
            WHERE a.user_id = ? {$cmpClauseA}
            ORDER BY a.created_at DESC, a.id DESC
        ";
        $stA = $pdo->prepare($qA); $stA->execute($paramsA);
        $allocs = $stA->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
} catch (Throwable $e) {}
try {
    $hasDS = $pdo->query("SHOW TABLES LIKE 'deals_submit'")->rowCount() > 0;
    if ($hasDS) {
        $cidCol = colExists('deals_submit','client_id') ? 'client_id' : (colExists('deals_submit','user_id') ? 'user_id' : null);
        $selAmt = colExists('deals_submit','amount_offered') ? 'amount_offered' : (colExists('deals_submit','offer_amount') ? 'offer_amount' : '0');
        $selTitle = colExists('deals_submit','project_desc') ? 'project_desc' : (colExists('deals_submit','property') ? 'property' : (colExists('deals_submit','estate') ? 'estate' : 'NULL'));
        if ($cidCol) {
            $sql = "SELECT id, {$selAmt} AS amount_offered, {$selTitle} AS title FROM deals_submit WHERE {$cidCol} = ?";
            if ($applyCompanyFilter && colExists('deals_submit','company_id')) { $sql .= " AND (company_id = ? OR company_id IS NULL)"; $st = $pdo->prepare($sql); $st->execute([$clientId, $companyId]); } else { $st = $pdo->prepare($sql); $st->execute([$clientId]); }
            $deals = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
        }
    }
} catch (Throwable $e) {}
$payments = [];
try {
    $hasP = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
    if ($hasP) {
        $hasUser = colExists('payments','user_id');
        $hasClient = colExists('payments','client_id');
        if ($hasUser || $hasClient) {
            $dateCol = colExists('payments','created_at') ? 'created_at' : (colExists('payments','date') ? 'date' : null);
            $dateSel = $dateCol ? $dateCol : "NULL";
            $conds = [];
            $params = [];
            if ($hasUser && $hasClient) {
                $conds[] = "(user_id = ? OR client_id = ?)";
                $params[] = $clientId; $params[] = $clientId;
            } elseif ($hasUser) {
                $conds[] = "user_id = ?";
                $params[] = $clientId;
            } else {
                $conds[] = "client_id = ?";
                $params[] = $clientId;
            }
            $conds[] = "status = 'approved'";
            if (colExists('payments','deal_id')) { $conds[] = "deal_id > 0"; }
            if ($applyCompanyFilter && colExists('payments','company_id')) { $conds[] = "(company_id = ? OR company_id IS NULL)"; $params[] = $companyId; }
            if ($from && $dateCol) { $conds[] = "{$dateCol} >= ?"; $params[] = $from . " 00:00:00"; }
            if ($to && $dateCol) { $conds[] = "{$dateCol} <= ?"; $params[] = $to . " 23:59:59"; }
            $selAlloc = colExists('payments','allocation_id') ? 'allocation_id' : 'NULL AS allocation_id';
            $selMethod = colExists('payments','payment_method') ? 'payment_method' : (colExists('payments','method') ? 'method' : "NULL AS payment_method");
            $sql = "SELECT id, deal_id, {$selAlloc}, amount, {$selMethod} AS method, {$dateSel} AS created_at FROM payments WHERE " . implode(' AND ', $conds);
            if ($dateCol) { $sql .= " ORDER BY {$dateCol} ASC"; } else { $sql .= " ORDER BY id ASC"; }
            $st = $pdo->prepare($sql); $st->execute($params); $payments = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
        }
    }
} catch (Throwable $e) {}
$payments = array_values(array_filter($payments, function($p){
    if (!isset($p['deal_id']) || (int)$p['deal_id'] <= 0) return false;
    if (!isset($p['amount']) || (float)$p['amount'] <= 0) return false;
    if (!isset($p['created_at']) || $p['created_at'] === null || trim((string)$p['created_at']) === '') return false;
    $ts = strtotime((string)$p['created_at']);
    if ($ts === false || $ts <= 0) return false;
    return true;
}));
$receiptMap = [];
try {
    $hasRecTbl = $pdo->query("SHOW TABLES LIKE 'receipts'")->rowCount() > 0;
    if ($hasRecTbl && !empty($payments)) {
        $ids = array_values(array_filter(array_map(function($p){ return (int)($p['id'] ?? 0); }, $payments)));
        $ids = array_values(array_unique(array_filter($ids)));
        if (!empty($ids) && colExists('receipts','payment_id')) {
            $in = implode(',', array_fill(0, count($ids), '?'));
            $cmpClauseR = '';
            $cmpParamsR = [];
            if ($applyCompanyFilter && colExists('receipts','company_id')) { $cmpClauseR = " AND (company_id = ? OR company_id IS NULL)"; $cmpParamsR[] = $companyId; }
            $qr = $pdo->prepare("SELECT id, payment_id FROM receipts WHERE payment_id IN ($in)".$cmpClauseR);
            $qr->execute(array_merge($ids, $cmpParamsR));
            while ($row = $qr->fetch(PDO::FETCH_ASSOC)) {
                $receiptMap[(int)$row['payment_id']] = (int)$row['id'];
            }
        }
    }
} catch (Throwable $e) {}
$paidByDeal = [];
foreach ($payments as $p) {
    $did = (int)($p['deal_id'] ?? 0);
    if (!isset($paidByDeal[$did])) $paidByDeal[$did] = 0.0;
    $paidByDeal[$did] += (float)($p['amount'] ?? 0);
}
$rows = [];
// Build allocation rows first (primary)
if (!empty($allocs)) {
    // prefetch paid by allocation
    $paidByAlloc = [];
    try {
        if (colExists('payments','allocation_id')) {
            $ids = array_map(function($a){ return (int)($a['id'] ?? 0); }, $allocs);
            $ids = array_values(array_unique(array_filter($ids)));
            if (!empty($ids)) {
                $in = implode(',', array_fill(0, count($ids), '?'));
                $cmpClause = '';
                $cmpParams = [];
                if ($applyCompanyFilter && colExists('payments','company_id')) { $cmpClause = " AND (company_id = ? OR company_id IS NULL)"; $cmpParams[] = $companyId; }
                $qsum = $pdo->prepare("SELECT allocation_id, COALESCE(SUM(amount),0) AS total_paid FROM payments WHERE allocation_id IN ($in) AND status IN ('approved','paid'){$cmpClause} GROUP BY allocation_id");
                $qsum->execute(array_merge($ids, $cmpParams));
                while ($r = $qsum->fetch(PDO::FETCH_ASSOC)) { $paidByAlloc[(int)$r['allocation_id']] = (float)($r['total_paid'] ?? 0); }
            }
        }
    } catch (Throwable $e) {}
    foreach ($allocs as $a) {
        $aid = (int)$a['id'];
        $title = (string)($a['property_title'] ?? 'Property');
        $size = trim((string)($a['plot_size'] ?? ''));
        if ($size !== '') { $title = $size . ' of ' . $title; }
        $amount = (float)($a['property_price'] ?? 0);
        $paid = (float)($paidByAlloc[$aid] ?? 0.0);
        $bal = max(0.0, $amount - $paid);
        $rows[] = [
            'type' => 'allocation',
            'id' => $aid,
            'ref' => (string)($a['allocation_ref'] ?? ''),
            'title' => $title,
            'amount' => $amount,
            'paid' => $paid,
            'balance' => $bal
        ];
    }
}
// Add deals_submit fallback rows if present (and not already represented as allocations)
foreach ($deals as $d) {
    $did = (int)$d['id'];
    $amt = (float)($d['amount_offered'] ?? 0);
    $pd = (float)($paidByDeal[$did] ?? 0.0);
    $bal = max(0.0, $amt - $pd);
    $rows[] = [
        'type' => 'deal',
        'id' => $did,
        'ref' => '',
        'title' => (string)($d['title'] ?? 'Deal #' . $did),
        'amount' => $amt,
        'paid' => $pd,
        'balance' => $bal
    ];
}
$totalInvestment = 0.0; $totalPaid = 0.0; $totalBalance = 0.0;
foreach ($rows as $r) {
    $totalInvestment += $r['amount'];
    $totalPaid += $r['paid'];
    $totalBalance += $r['balance'];
}
?>
<?php require_once __DIR__ . '/includes/header.php'; ?>
<div class="container-fluid px-4 pb-4">
    <div class="d-flex justify-content-between align-items-center mt-4 mb-3">
        <div>
            <h2 class="text-navy fw-bold mb-1">Client Master Ledger</h2>
            <div class="text-muted">Full financial overview across all deals</div>
        </div>
        <form class="d-flex align-items-end gap-2" method="get">
            <?php if (!$needsPicker): ?>
            <input type="hidden" name="client_id" value="<?= (int)$clientId ?>">
            <?php endif; ?>
            <div>
                <label class="form-label mb-1">From</label>
                <input type="date" name="from" class="form-control form-control-sm" value="<?= htmlspecialchars($from ?: '') ?>">
            </div>
            <div>
                <label class="form-label mb-1">To</label>
                <input type="date" name="to" class="form-control form-control-sm" value="<?= htmlspecialchars($to ?: '') ?>">
            </div>
            <div class="pt-3">
                <button class="btn btn-dark btn-sm">Filter</button>
            </div>
        </form>
    </div>
    <?php if ($needsPicker): ?>
    <div class="card shadow-sm">
        <div class="card-body">
            <form class="row g-3" method="get">
                <div class="col-md-6">
                    <label class="form-label">Select Client</label>
                    <select name="client_id" class="form-select" required>
                        <option value="">Choose a client…</option>
                        <?php
                        try {
                            $q = "SELECT id, name, email FROM users WHERE role = 'client' ORDER BY name ASC LIMIT 500";
                            $st = $pdo->query($q);
                            $opts = $st ? $st->fetchAll(PDO::FETCH_ASSOC) : [];
                            foreach ($opts as $o) {
                                $lab = trim(($o['name'] ?? 'Client') . ' • ' . ($o['email'] ?? ''));
                                echo '<option value="'.(int)$o['id'].'">'.htmlspecialchars($lab).'</option>';
                            }
                        } catch (Throwable $e) {}
                        ?>
                    </select>
                </div>
                <div class="col-md-3">
                    <label class="form-label">From</label>
                    <input type="date" name="from" class="form-control" value="<?= htmlspecialchars($from ?: '') ?>">
                </div>
                <div class="col-md-3">
                    <label class="form-label">To</label>
                    <input type="date" name="to" class="form-control" value="<?= htmlspecialchars($to ?: '') ?>">
                </div>
                <div class="col-12">
                    <button class="btn btn-dark">Open Master Ledger</button>
                </div>
            </form>
        </div>
    </div>
    <?php require_once __DIR__ . '/includes/footer.php'; exit; ?>
    <?php endif; ?>
    <div class="card shadow-sm mb-3">
        <div class="card-body">
            <div class="row g-3">
                <div class="col-md-3">
                    <div class="p-3 border rounded">
                        <div class="text-muted small">Client</div>
                        <div class="fw-bold"><?= htmlspecialchars($client['name']) ?></div>
                        <div class="text-muted small"><?= htmlspecialchars(trim($client['email'] . ($client['phone'] ? ' • '.$client['phone'] : ''))) ?></div>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="p-3 border rounded">
                        <div class="text-muted small">Total Investment</div>
                        <div class="h5 mb-0"><?= formatCurrency($totalInvestment) ?></div>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="p-3 border rounded">
                        <div class="text-muted small">Total Paid</div>
                        <div class="h5 mb-0 text-success"><?= formatCurrency($totalPaid) ?></div>
                    </div>
                </div>
                <div class="col-md-3">
                    <div class="p-3 border rounded">
                        <div class="text-muted small">Total Balance</div>
                        <div class="h5 mb-0 text-danger"><?= formatCurrency($totalBalance) ?></div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <div class="card shadow-sm mb-3">
        <div class="card-header bg-white">
            <div class="fw-semibold">Allocations & Deals</div>
        </div>
        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-hover align-middle mb-0">
                    <thead class="table-light">
                        <tr>
                            <th style="width: 140px;">Ref/ID</th>
                            <th>Property / Project</th>
                            <th class="text-end" style="width: 180px;">Amount</th>
                            <th class="text-end" style="width: 180px;">Paid</th>
                            <th class="text-end" style="width: 180px;">Balance</th>
                            <th class="text-end" style="width: 180px;">Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($rows)): ?>
                            <tr><td colspan="6" class="text-center py-4 text-muted">No deals for this client</td></tr>
                        <?php else: foreach ($rows as $r): ?>
                            <tr>
                                <td>
                                    <?php if (($r['type'] ?? '') === 'allocation'): ?>
                                        <?= htmlspecialchars($r['ref'] !== '' ? $r['ref'] : ('AL-' . date('Y') . '-' . str_pad((string)$r['id'], 6, '0', STR_PAD_LEFT))) ?>
                                    <?php else: ?>
                                        #<?= (int)$r['id'] ?>
                                    <?php endif; ?>
                                </td>
                                <td><?= htmlspecialchars($r['title']) ?></td>
                                <td class="text-end"><?= formatCurrency($r['amount']) ?></td>
                                <td class="text-end text-success"><?= formatCurrency($r['paid']) ?></td>
                                <td class="text-end text-danger"><?= formatCurrency($r['balance']) ?></td>
                                <td class="text-end">
                                    <?php if (($r['type'] ?? '') === 'allocation'): ?>
                                        <a class="btn btn-sm btn-outline-primary" href="allocations.php?status=all&view=cards" target="_blank">Open Allocation</a>
                                        <a class="btn btn-sm btn-outline-dark" href="allocation-letters.php?action=preview&allocation_id=<?= (int)$r['id'] ?>" target="_blank">Letter</a>
                                    <?php else: ?>
                                        <a class="btn btn-sm btn-outline-dark" href="client_ledger.php?deal_id=<?= (int)$r['id'] ?>" target="_blank">Open Deal Ledger</a>
                                    <?php endif; ?>
                                </td>
                            </tr>
                        <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
    <div class="card shadow-sm">
        <div class="card-header bg-white d-flex justify-content-between">
            <div class="fw-semibold">Approved Payments Timeline</div>
            <div class="text-muted small">All allocations & deals</div>
        </div>
        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-hover align-middle mb-0">
                    <thead class="table-light">
                        <tr>
                            <th style="width: 160px;">Date</th>
                            <th style="width: 140px;">Ref/ID</th>
                            <th>Property / Method</th>
                            <th class="text-end" style="width: 180px;">Amount</th>
                            <th style="width: 160px;">Receipt</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (count($payments) === 0): ?>
                            <tr><td colspan="5" class="text-center py-4 text-muted">No approved payments</td></tr>
                        <?php else: foreach ($payments as $p): ?>
                            <tr>
                                <td class="text-muted"><?= htmlspecialchars(date('Y-m-d H:i', strtotime($p['created_at']))) ?></td>
                                <td>
                                    <?php if (isset($p['allocation_id']) && (int)$p['allocation_id'] > 0): ?>
                                        <?php
                                            $aref = '';
                                            try {
                                                $selRefX = colExists('allocations','allocation_ref') ? 'allocation_ref' : "NULL";
                                                $stx = $pdo->prepare("SELECT {$selRefX} FROM allocations WHERE id = ? LIMIT 1");
                                                $stx->execute([(int)$p['allocation_id']]); $aref = (string)($stx->fetchColumn() ?: '');
                                            } catch (Throwable $e) {}
                                        ?>
                                        <?= htmlspecialchars($aref !== '' ? $aref : ('AL-' . date('Y') . '-' . str_pad((string)$p['allocation_id'], 6, '0', STR_PAD_LEFT))) ?>
                                    <?php else: ?>
                                        #<?= (int)($p['deal_id'] ?? 0) ?>
                                    <?php endif; ?>
                                </td>
                                <td class="text-muted">
                                    <?= htmlspecialchars((string)($p['method'] ?? '')) ?>
                                </td>
                                <td class="text-end text-success"><?= formatCurrency((float)$p['amount']) ?></td>
                                <td>
                                    <?php $rid = $receiptMap[(int)($p['id'] ?? 0)] ?? 0; ?>
                                    <?php if ($rid > 0): ?>
                                        <a class="btn btn-sm btn-outline-success" target="_blank" href="receipt_view.php?id=<?= (int)$rid ?>">View Receipt</a>
                                    <?php else: ?>
                                        <span class="text-muted">Not Generated</span>
                                    <?php endif; ?>
                                </td>
                            </tr>
                        <?php endforeach; endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>
<?php require_once __DIR__ . '/includes/footer.php'; ?>