<?php
if (session_status() === PHP_SESSION_NONE) { session_start(); }
require_once __DIR__ . '/includes/db.php';
require_once __DIR__ . '/includes/functions.php';
require_once __DIR__ . '/includes/installments.php';

$role = strtolower((string)($_SESSION['user_role'] ?? ''));
if (!in_array($role, ['super_admin','admin','finance','accountant','manager'], true)) {
    header('Location: dashboard.php');
    exit;
}
$isAdminReadOnly = ($role === 'admin');

$companyId = function_exists('getCurrentCompanyId') ? (int)getCurrentCompanyId() : (int)($_SESSION['company_id'] ?? 0);

$startDate = isset($_GET['start']) ? trim((string)$_GET['start']) : '';
$endDate = isset($_GET['end']) ? trim((string)$_GET['end']) : '';
if ($startDate === '' || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $startDate)) { $startDate = date('Y-m-01'); }
if ($endDate === '' || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $endDate)) { $endDate = date('Y-m-t'); }

$estateId = isset($_GET['estate']) && ctype_digit((string)$_GET['estate']) ? (int)$_GET['estate'] : 0;
$statusFilter = isset($_GET['status']) ? strtolower(trim((string)$_GET['status'])) : 'open';
if (!in_array($statusFilter, ['open','all'], true)) { $statusFilter = 'open'; }

$estates = [];
try {
    $hasEstates = $pdo->query("SHOW TABLES LIKE 'estates'")->rowCount() > 0;
    if ($hasEstates) {
        $q = "SELECT id, name FROM estates";
        $params = [];
        if ($companyId && function_exists('tableHasColumn') && tableHasColumn('estates','company_id')) { $q .= " WHERE company_id = ? OR company_id IS NULL"; $params[] = $companyId; }
        $q .= " ORDER BY name ASC";
        $st = $pdo->prepare($q);
        $st->execute($params);
        $estates = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
} catch (Throwable $e) { $estates = []; }

$rows = [];
$summary = [
    'count' => 0,
    'total_outstanding' => 0.0,
    'total_paid' => 0.0,
    'total_value' => 0.0
];
$topDebtors = [];
$byEstate = [];

try {
    $hasAlloc = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
    $hasUsers = $pdo->query("SHOW TABLES LIKE 'users'")->rowCount() > 0;
    $hasProps = $pdo->query("SHOW TABLES LIKE 'properties'")->rowCount() > 0;
    $hasPayments = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
    if ($hasAlloc && $hasUsers && $hasProps) {
        $allocUserCol = function_exists('tableHasColumn') && tableHasColumn('allocations','user_id') ? 'user_id' : (tableHasColumn('allocations','client_id') ? 'client_id' : 'user_id');
        $allocPropCol = function_exists('tableHasColumn') && tableHasColumn('allocations','property_id') ? 'property_id' : null;
        $allocDealCol = function_exists('tableHasColumn') && tableHasColumn('allocations','deal_id') ? 'deal_id' : null;
        $allocEstateCol = function_exists('tableHasColumn') && tableHasColumn('allocations','estate_id') ? 'estate_id' : null;
        $propEstateCol = function_exists('tableHasColumn') && tableHasColumn('properties','estate_id') ? 'estate_id' : null;

        $paidJoin = '';
        $paidSelect = ", 0 AS paid_amount, NULL AS last_payment_date";
        $paidParams = [];
        if ($hasPayments && function_exists('tableHasColumn') && tableHasColumn('payments','amount') && tableHasColumn('payments','status')) {
            $dateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : null;
            $hasPayAlloc = tableHasColumn('payments','allocation_id');
            $hasPayDeal = tableHasColumn('payments','deal_id');
            $finalStatuses = function_exists('kpiPaymentFinalizedStatuses') ? kpiPaymentFinalizedStatuses() : ['verified','approved','paid','completed','success'];
            $statusSql = function_exists('kpiSqlList') ? kpiSqlList($finalStatuses) : "('verified','approved','paid','completed','success')";
            if ($dateCol && ($hasPayAlloc || ($hasPayDeal && $allocDealCol))) {
                $subWhere = ["status IN $statusSql", "$dateCol BETWEEN ? AND ?"];
                $paidParams = array_merge([$startDate . ' 00:00:00', $endDate . ' 23:59:59'], function_exists('kpiCompanyFilter') ? kpiCompanyFilter('payments', $companyId, true)[1] : []);
                [$payCompanyClause, $payCompanyParams] = function_exists('kpiCompanyFilter') ? kpiCompanyFilter('payments', $companyId, true) : ['', []];
                $paidParams = array_merge([$startDate . ' 00:00:00', $endDate . ' 23:59:59'], $payCompanyParams);
                $sub = "SELECT ";
                $keyExpr = $hasPayAlloc ? "allocation_id" : "deal_id";
                $sub .= "$keyExpr AS k, COALESCE(SUM(amount),0) AS paid_amount, MAX($dateCol) AS last_payment_date FROM payments WHERE " . implode(' AND ', $subWhere) . $payCompanyClause . " GROUP BY $keyExpr";
                $paidSelect = ", COALESCE(paid.paid_amount,0) AS paid_amount, paid.last_payment_date";
                if ($hasPayAlloc) {
                    $paidJoin = " LEFT JOIN ($sub) paid ON paid.k = a.id";
                } else {
                    $paidJoin = " LEFT JOIN ($sub) paid ON paid.k = a." . $allocDealCol;
                }
            }
        }

        $q = "SELECT a.*, a.id AS allocation_id, u.id AS client_id, u.name AS client_name, p.title AS property_title, p.id AS property_id";
        if ($propEstateCol) { $q .= ", p.$propEstateCol AS property_estate_id"; }
        if ($allocEstateCol) { $q .= ", a.$allocEstateCol AS allocation_estate_id"; }
        $q .= $paidSelect;
        $q .= " FROM allocations a";
        $q .= " JOIN users u ON u.id = a.$allocUserCol";
        if ($allocPropCol) { $q .= " LEFT JOIN properties p ON p.id = a.$allocPropCol"; } else { $q .= " LEFT JOIN properties p ON 1=0"; }
        $q .= $paidJoin;
        $where = ["1=1"];
        $params = $paidParams;

        if ($companyId && function_exists('tableHasColumn') && tableHasColumn('allocations','company_id')) {
            $where[] = "(a.company_id = ? OR a.company_id IS NULL)";
            $params[] = $companyId;
        }

        if ($statusFilter === 'open') {
            $where[] = "LOWER(TRIM(a.status)) NOT IN ('revoked','cancelled','canceled','rejected')";
        }

        if ($estateId > 0) {
            if ($allocEstateCol) { $where[] = "a.$allocEstateCol = ?"; $params[] = $estateId; }
            elseif ($propEstateCol) { $where[] = "p.$propEstateCol = ?"; $params[] = $estateId; }
        }

        $q .= " WHERE " . implode(' AND ', $where);
        $q .= " ORDER BY a.id DESC LIMIT 800";
        $st = $pdo->prepare($q);
        $st->execute($params);
        $allocs = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $estateNameById = [];
        foreach ($estates as $es) { $estateNameById[(int)$es['id']] = (string)$es['name']; }

        foreach ($allocs as $aRow) {
            $allocId = (int)($aRow['allocation_id'] ?? 0);
            if ($allocId <= 0) { continue; }
            $total = (float)allocationTotalAmount($pdo, $aRow);
            if (!is_finite($total) || $total <= 0) { continue; }
            $paid = (float)($aRow['paid_amount'] ?? 0);
            if (!is_finite($paid) || $paid < 0) { $paid = 0.0; }
            $balance = $total - $paid;
            if ($balance <= 0.01) { continue; }

            $estateIdRow = (int)($aRow['allocation_estate_id'] ?? $aRow['property_estate_id'] ?? 0);
            $estateName = $estateIdRow > 0 ? (string)($estateNameById[$estateIdRow] ?? ('Estate #' . $estateIdRow)) : '-';
            $propTitle = (string)($aRow['property_title'] ?? '');
            if ($propTitle === '') { $propTitle = 'Property #' . (int)($aRow['property_id'] ?? 0); }
            $rows[] = [
                'allocation_id' => $allocId,
                'client_id' => (int)($aRow['client_id'] ?? 0),
                'client_name' => (string)($aRow['client_name'] ?? '-'),
                'property_title' => $propTitle,
                'estate_id' => $estateIdRow,
                'estate_name' => $estateName,
                'total_price' => $total,
                'paid_amount' => $paid,
                'balance' => $balance,
                'last_payment_date' => (string)($aRow['last_payment_date'] ?? '')
            ];

            $summary['count']++;
            $summary['total_outstanding'] += $balance;
            $summary['total_paid'] += $paid;
            $summary['total_value'] += $total;

            $byEstate[$estateName] = (float)($byEstate[$estateName] ?? 0) + $balance;
        }

        usort($rows, function($a, $b) {
            $ba = (float)($a['balance'] ?? 0);
            $bb = (float)($b['balance'] ?? 0);
            if ($ba === $bb) { return 0; }
            return $ba < $bb ? 1 : -1;
        });

        $topDebtors = array_slice($rows, 0, 10);
        arsort($byEstate);
        $byEstate = array_slice($byEstate, 0, 10, true);
    }
} catch (Throwable $e) {}

include __DIR__ . '/includes/header.php';
?>

<div class="container-fluid px-4">
    <div class="d-flex align-items-center justify-content-between flex-wrap gap-2 my-3">
        <div>
            <h3 class="mb-0">Outstanding Balances</h3>
            <div class="text-muted small">Track unpaid balances by client and allocation.</div>
        </div>
        <div class="d-flex gap-2">
            <a class="btn btn-outline-secondary" href="finance-dashboard.php">Back to Dashboard</a>
        </div>
    </div>

    <div class="card shadow-sm mb-3">
        <div class="card-body">
            <form class="row g-2 align-items-end" method="get">
                <div class="col-12 col-md-3">
                    <label class="form-label small text-muted mb-1">Start</label>
                    <input type="date" class="form-control" name="start" value="<?= htmlspecialchars($startDate) ?>">
                </div>
                <div class="col-12 col-md-3">
                    <label class="form-label small text-muted mb-1">End</label>
                    <input type="date" class="form-control" name="end" value="<?= htmlspecialchars($endDate) ?>">
                </div>
                <div class="col-12 col-md-3">
                    <label class="form-label small text-muted mb-1">Estate</label>
                    <select class="form-select" name="estate">
                        <option value="0">All Estates</option>
                        <?php foreach ($estates as $es): ?>
                            <option value="<?= (int)$es['id'] ?>" <?= ((int)$es['id'] === $estateId) ? 'selected' : '' ?>><?= htmlspecialchars((string)$es['name']) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div class="col-12 col-md-2">
                    <label class="form-label small text-muted mb-1">Status</label>
                    <select class="form-select" name="status">
                        <option value="open" <?= $statusFilter === 'open' ? 'selected' : '' ?>>Open Only</option>
                        <option value="all" <?= $statusFilter === 'all' ? 'selected' : '' ?>>All</option>
                    </select>
                </div>
                <div class="col-12 col-md-1 d-grid">
                    <button class="btn btn-primary" type="submit">Apply</button>
                </div>
            </form>
        </div>
    </div>

    <div class="row g-3 mb-3">
        <div class="col-12 col-md-3">
            <div class="card shadow-sm h-100">
                <div class="card-body">
                    <div class="small text-muted">Outstanding Total</div>
                    <div class="h5 mb-0"><?= formatCurrency((float)$summary['total_outstanding']) ?></div>
                </div>
            </div>
        </div>
        <div class="col-12 col-md-3">
            <div class="card shadow-sm h-100">
                <div class="card-body">
                    <div class="small text-muted">Accounts With Balance</div>
                    <div class="h5 mb-0"><?= number_format((int)$summary['count']) ?></div>
                </div>
            </div>
        </div>
        <div class="col-12 col-md-3">
            <div class="card shadow-sm h-100">
                <div class="card-body">
                    <div class="small text-muted">Average Balance</div>
                    <div class="h5 mb-0"><?= formatCurrency(((int)$summary['count'] > 0) ? ((float)$summary['total_outstanding'] / (int)$summary['count']) : 0) ?></div>
                </div>
            </div>
        </div>
        <div class="col-12 col-md-3">
            <div class="card shadow-sm h-100">
                <div class="card-body">
                    <div class="small text-muted">Total Value (Selected)</div>
                    <div class="h5 mb-0"><?= formatCurrency((float)$summary['total_value']) ?></div>
                </div>
            </div>
        </div>
    </div>

    <div class="row g-3 mb-3">
        <div class="col-12 col-lg-6">
            <div class="card shadow-sm h-100">
                <div class="card-header bg-white"><strong>Top Debtors</strong></div>
                <div class="card-body">
                    <canvas id="chartTopDebtors" height="160"></canvas>
                </div>
            </div>
        </div>
        <div class="col-12 col-lg-6">
            <div class="card shadow-sm h-100">
                <div class="card-header bg-white"><strong>Outstanding by Estate</strong></div>
                <div class="card-body">
                    <canvas id="chartByEstate" height="160"></canvas>
                </div>
            </div>
        </div>
    </div>

    <div class="card shadow-sm">
        <div class="card-header bg-white d-flex justify-content-between align-items-center">
            <strong>Outstanding List</strong>
            <span class="text-muted small"><?= number_format(count($rows)) ?> record(s)</span>
        </div>
        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-hover mb-0 align-middle">
                    <thead class="table-light">
                        <tr>
                            <th>Client</th>
                            <th>Property / Estate</th>
                            <th class="text-end">Total</th>
                            <th class="text-end">Paid</th>
                            <th class="text-end">Balance</th>
                            <th>Last Payment</th>
                            <th class="text-end">Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($rows)): ?>
                            <tr><td colspan="7" class="text-center text-muted py-4">No outstanding balances found for the selected filters.</td></tr>
                        <?php else: ?>
                            <?php foreach ($rows as $r): ?>
                                <tr>
                                    <td>
                                        <div class="fw-semibold"><?= htmlspecialchars((string)$r['client_name']) ?></div>
                                        <div class="text-muted small">Client #<?= (int)$r['client_id'] ?></div>
                                    </td>
                                    <td>
                                        <div class="fw-semibold"><?= htmlspecialchars((string)$r['property_title']) ?></div>
                                        <div class="text-muted small"><?= htmlspecialchars((string)$r['estate_name']) ?></div>
                                    </td>
                                    <td class="text-end fw-semibold"><?= formatCurrency((float)$r['total_price']) ?></td>
                                    <td class="text-end fw-semibold text-success"><?= formatCurrency((float)$r['paid_amount']) ?></td>
                                    <td class="text-end fw-bold text-danger"><?= formatCurrency((float)$r['balance']) ?></td>
                                    <td class="text-muted"><?= htmlspecialchars((string)$r['last_payment_date']) ?></td>
                                    <td class="text-end">
                                        <a class="btn btn-sm btn-outline-primary" href="client-profile.php?client_id=<?= (int)$r['client_id'] ?>">View Client</a>
                                        <?php if (!$isAdminReadOnly): ?>
                                            <button type="button" class="btn btn-sm btn-outline-warning js-remind" data-client-id="<?= (int)$r['client_id'] ?>">Send Reminder</button>
                                            <a class="btn btn-sm btn-outline-success" href="finance-payments.php?uid=<?= (int)$r['client_id'] ?>">Record Payment</a>
                                        <?php endif; ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<script>
const topDebtors = <?= json_encode(array_map(function($r){ return ['label'=>$r['client_name'].' — '.$r['property_title'],'value'=>(float)$r['balance']]; }, $topDebtors), JSON_UNESCAPED_SLASHES) ?>;
const byEstate = <?= json_encode(array_map(function($k,$v){ return ['label'=>$k,'value'=>(float)$v]; }, array_keys($byEstate), array_values($byEstate)), JSON_UNESCAPED_SLASHES) ?>;

function makeBarChart(ctx, items) {
    if (typeof Chart === 'undefined') return;
    const labels = items.map(x => x.label);
    const values = items.map(x => x.value);
    new Chart(ctx, {
        type: 'bar',
        data: { labels: labels, datasets: [{ label: 'Amount', data: values, backgroundColor: 'rgba(37,99,235,.55)', borderColor: 'rgba(37,99,235,.95)', borderWidth: 1 }] },
        options: {
            responsive: true,
            plugins: { legend: { display: false } },
            scales: {
                x: { ticks: { color: '#475569' } },
                y: { ticks: { color: '#475569' } }
            }
        }
    });
}

makeBarChart(document.getElementById('chartTopDebtors'), topDebtors);
makeBarChart(document.getElementById('chartByEstate'), byEstate);

document.querySelectorAll('.js-remind').forEach(btn => {
    btn.addEventListener('click', async () => {
        const clientId = btn.getAttribute('data-client-id');
        const fd = new FormData();
        fd.append('client_id', String(clientId || ''));
        fd.append('type', 'payment_reminder');
        btn.disabled = true;
        try {
            const res = await fetch('ajax_send_reminder.php', { method: 'POST', body: fd, credentials: 'same-origin' });
            const json = await res.json();
            alert(json && json.message ? json.message : 'Reminder sent');
        } catch (e) {
            alert('Unable to send reminder.');
        } finally {
            btn.disabled = false;
        }
    });
});
</script>

<?php include __DIR__ . '/includes/footer.php'; ?>