<?php
ob_start();
require_once 'includes/header.php';

$role = strtolower((string)($_SESSION['user_role'] ?? ''));
if (!in_array($role, ['finance', 'finance_manager'], true)) {
    echo "<div class='container py-4'><div class='alert alert-danger'>Access Denied</div></div>";
    require_once 'includes/footer.php';
    exit;
}

if (function_exists('ensureFinanceAccountsTable')) {
    ensureFinanceAccountsTable();
}

$companyId = function_exists('getCurrentCompanyId') ? getCurrentCompanyId() : null;

$type = strtolower(trim((string)($_GET['type'] ?? 'pl')));
if (!in_array($type, ['pl', 'balance'], true)) { $type = 'pl'; }

$year = (int)($_GET['year'] ?? date('Y'));
if ($year < 2000 || $year > 2100) { $year = (int)date('Y'); }
$month = (string)($_GET['month'] ?? date('n'));
$month = trim($month);
$monthNum = ctype_digit($month) ? (int)$month : 0;
$isAllYear = ($month === 'all');
if (!$isAllYear && ($monthNum < 1 || $monthNum > 12)) { $monthNum = (int)date('n'); }

if ($isAllYear) {
    $startDate = sprintf('%04d-01-01', $year);
    $endDate = sprintf('%04d-12-31', $year);
} else {
    $startDate = sprintf('%04d-%02d-01', $year, $monthNum);
    $endDate = date('Y-m-t', strtotime($startDate));
}

function fin_scalar($pdo, $sql, $params) {
    try {
        $st = $pdo->prepare($sql);
        $st->execute($params);
        return (float)($st->fetchColumn() ?: 0);
    } catch (Throwable $e) {
        return 0.0;
    }
}

$pl = [
    'revenue_total' => 0.0,
    'revenue_sales' => 0.0,
    'revenue_installments' => 0.0,
    'exp_total' => 0.0,
    'exp_maintenance' => 0.0,
    'exp_commissions' => 0.0,
    'exp_manual' => 0.0,
    'net_profit' => 0.0,
];

$balance = [
    'assets_cash' => 0.0,
    'assets_receivables' => 0.0,
    'liab_pending_refunds' => 0.0,
    'liab_outstanding_payments' => 0.0,
    'assets_total' => 0.0,
    'liab_total' => 0.0,
    'equity' => 0.0,
];

$estateRows = [];

try {
    global $pdo;
    $hasPayments = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
    $hasManual = $pdo->query("SHOW TABLES LIKE 'expenses_manual'")->rowCount() > 0;
    $hasMaint = $pdo->query("SHOW TABLES LIKE 'maintenance_requests'")->rowCount() > 0;
    $hasComms = $pdo->query("SHOW TABLES LIKE 'commissions'")->rowCount() > 0;

    $payDateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'created_at';
    if (!$payDateCol) {
        $payDateCol = (function_exists('tableHasColumn') && tableHasColumn('payments', 'payment_date')) ? 'payment_date'
            : ((function_exists('tableHasColumn') && tableHasColumn('payments', 'date')) ? 'date' : 'created_at');
    }

    $payStatusSql = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";

    if ($type === 'pl') {
        if ($hasPayments) {
            $sqlRev = "SELECT COALESCE(SUM(amount),0) FROM payments WHERE status IN $payStatusSql AND $payDateCol BETWEEN ? AND ?";
            $paramsRev = [$startDate . " 00:00:00", $endDate . " 23:59:59"];
            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments', 'company_id')) {
                $sqlRev .= " AND (company_id = ? OR company_id IS NULL)";
                $paramsRev[] = $companyId;
            }
            $pl['revenue_total'] = fin_scalar($pdo, $sqlRev, $paramsRev);

            $instWhereParts = [];
            if (function_exists('tableHasColumn') && tableHasColumn('payments', 'reference')) { $instWhereParts[] = "LOWER(COALESCE(reference,'')) LIKE '%install%'"; }
            if (function_exists('tableHasColumn') && tableHasColumn('payments', 'payment_type')) { $instWhereParts[] = "LOWER(COALESCE(payment_type,'')) LIKE '%install%'"; }
            if (function_exists('tableHasColumn') && tableHasColumn('payments', 'type')) { $instWhereParts[] = "LOWER(COALESCE(type,'')) LIKE '%install%'"; }
            if (function_exists('tableHasColumn') && tableHasColumn('payments', 'meta_json')) { $instWhereParts[] = "LOWER(COALESCE(meta_json,'')) LIKE '%install%'"; }
            if (function_exists('tableHasColumn') && tableHasColumn('payments', 'installment_id')) { $instWhereParts[] = "installment_id IS NOT NULL"; }

            if (!empty($instWhereParts)) {
                $sqlInst = "SELECT COALESCE(SUM(amount),0) FROM payments WHERE status IN $payStatusSql AND $payDateCol BETWEEN ? AND ? AND (" . implode(" OR ", $instWhereParts) . ")";
                $paramsInst = [$startDate . " 00:00:00", $endDate . " 23:59:59"];
                if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments', 'company_id')) {
                    $sqlInst .= " AND (company_id = ? OR company_id IS NULL)";
                    $paramsInst[] = $companyId;
                }
                $pl['revenue_installments'] = fin_scalar($pdo, $sqlInst, $paramsInst);
            } else {
                $pl['revenue_installments'] = 0.0;
            }
            $pl['revenue_sales'] = max(0.0, $pl['revenue_total'] - $pl['revenue_installments']);
        }

        if ($hasManual) {
            $dateCol = (function_exists('tableHasColumn') && tableHasColumn('expenses_manual', 'expense_date')) ? 'expense_date'
                : ((function_exists('tableHasColumn') && tableHasColumn('expenses_manual', 'date')) ? 'date' : 'created_at');
            $sql = "SELECT COALESCE(SUM(amount),0) FROM expenses_manual WHERE LOWER(TRIM(status)) = 'approved' AND $dateCol BETWEEN ? AND ?";
            $params = [$startDate, $endDate];
            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('expenses_manual', 'company_id')) {
                $sql .= " AND (company_id = ? OR company_id IS NULL)";
                $params[] = $companyId;
            }
            $pl['exp_manual'] = fin_scalar($pdo, $sql, $params);
        }

        if ($hasMaint) {
            $dateCol = (function_exists('tableHasColumn') && tableHasColumn('maintenance_requests', 'completed_at')) ? 'completed_at'
                : ((function_exists('tableHasColumn') && tableHasColumn('maintenance_requests', 'updated_at')) ? 'updated_at' : 'created_at');
            $costCol = (function_exists('tableHasColumn') && tableHasColumn('maintenance_requests', 'cost')) ? 'cost'
                : ((function_exists('tableHasColumn') && tableHasColumn('maintenance_requests', 'amount')) ? 'amount' : 'cost');
            $sql = "SELECT COALESCE(SUM($costCol),0) FROM maintenance_requests WHERE LOWER(TRIM(status)) = 'completed' AND $dateCol BETWEEN ? AND ?";
            $params = [$startDate . " 00:00:00", $endDate . " 23:59:59"];
            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('maintenance_requests', 'company_id')) {
                $sql .= " AND (company_id = ? OR company_id IS NULL)";
                $params[] = $companyId;
            }
            $pl['exp_maintenance'] = fin_scalar($pdo, $sql, $params);
        }

        if ($hasComms) {
            $dateCol = (function_exists('tableHasColumn') && tableHasColumn('commissions', 'date_paid')) ? 'date_paid'
                : ((function_exists('tableHasColumn') && tableHasColumn('commissions', 'paid_at')) ? 'paid_at' : ((function_exists('tableHasColumn') && tableHasColumn('commissions', 'updated_at')) ? 'updated_at' : 'created_at'));
            $amtCol = (function_exists('tableHasColumn') && tableHasColumn('commissions', 'amount')) ? 'amount' : 'amount';
            $sql = "SELECT COALESCE(SUM($amtCol),0) FROM commissions WHERE LOWER(TRIM(status)) = 'paid' AND $dateCol BETWEEN ? AND ?";
            $params = [$startDate . " 00:00:00", $endDate . " 23:59:59"];
            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('commissions', 'company_id')) {
                $sql .= " AND (company_id = ? OR company_id IS NULL)";
                $params[] = $companyId;
            }
            $pl['exp_commissions'] = fin_scalar($pdo, $sql, $params);
        }

        $pl['exp_total'] = (float)$pl['exp_manual'] + (float)$pl['exp_maintenance'] + (float)$pl['exp_commissions'];
        $pl['net_profit'] = (float)$pl['revenue_total'] - (float)$pl['exp_total'];
    }

    if ($type === 'balance') {
        $q = "SELECT COALESCE(SUM(balance),0) FROM finance_accounts WHERE status = 'active'";
        $p = [];
        if ($companyId && function_exists('tableHasColumn') && tableHasColumn('finance_accounts', 'company_id')) {
            $q .= " AND (company_id = ? OR company_id IS NULL)";
            $p[] = $companyId;
        }
        $balance['assets_cash'] = fin_scalar($pdo, $q, $p);

        $hasInvoices = $pdo->query("SHOW TABLES LIKE 'invoices'")->rowCount() > 0;
        $hasInst = $pdo->query("SHOW TABLES LIKE 'installments'")->rowCount() > 0;
        if ($hasInvoices && function_exists('tableHasColumn') && tableHasColumn('invoices', 'balance_due')) {
            $dateCol = (tableHasColumn('invoices', 'created_at') ? 'created_at' : (tableHasColumn('invoices', 'date') ? 'date' : null));
            $sql = "SELECT COALESCE(SUM(balance_due),0) FROM invoices WHERE COALESCE(balance_due,0) > 0 AND LOWER(TRIM(status)) NOT IN ('paid','settled')";
            $params = [];
            if ($dateCol) {
                $sql .= " AND $dateCol <= ?";
                $params[] = $endDate . " 23:59:59";
            }
            if ($companyId && tableHasColumn('invoices', 'company_id')) {
                $sql .= " AND (company_id = ? OR company_id IS NULL)";
                $params[] = $companyId;
            }
            $balance['assets_receivables'] = fin_scalar($pdo, $sql, $params);
        } elseif ($hasInst && function_exists('tableHasColumn') && tableHasColumn('installments', 'amount_due')) {
            $dateCol = (tableHasColumn('installments', 'created_at') ? 'created_at' : (tableHasColumn('installments', 'due_date') ? 'due_date' : null));
            $sql = "SELECT COALESCE(SUM(GREATEST(0, (amount_due - COALESCE(amount_paid,0)))),0) FROM installments WHERE (amount_due - COALESCE(amount_paid,0)) > 0";
            $params = [];
            if ($dateCol) {
                $sql .= " AND $dateCol <= ?";
                $params[] = $endDate . " 23:59:59";
            }
            if ($companyId && tableHasColumn('installments', 'company_id')) {
                $sql .= " AND (company_id = ? OR company_id IS NULL)";
                $params[] = $companyId;
            }
            $balance['assets_receivables'] = fin_scalar($pdo, $sql, $params);
        }

        $hasRefunds = $pdo->query("SHOW TABLES LIKE 'refunds'")->rowCount() > 0;
        if ($hasRefunds) {
            $amtCol = (function_exists('tableHasColumn') && tableHasColumn('refunds', 'amount')) ? 'amount' : 'amount';
            $dateCol = (function_exists('tableHasColumn') && tableHasColumn('refunds', 'created_at')) ? 'created_at'
                : ((function_exists('tableHasColumn') && tableHasColumn('refunds', 'updated_at')) ? 'updated_at' : null);
            $sql = "SELECT COALESCE(SUM($amtCol),0) FROM refunds WHERE LOWER(TRIM(status)) IN ('requested','pending','under_review','approved')";
            $params = [];
            if ($dateCol) {
                $sql .= " AND $dateCol <= ?";
                $params[] = $endDate . " 23:59:59";
            }
            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('refunds', 'company_id')) {
                $sql .= " AND (company_id = ? OR company_id IS NULL)";
                $params[] = $companyId;
            }
            $balance['liab_pending_refunds'] = fin_scalar($pdo, $sql, $params);
        }

        if ($hasPayments) {
            $pendingSql = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentPendingStatuses()) : "('submitted','pending','pending_gateway','awaiting_verification','pending_verification','pending_confirmation')";
            $sql = "SELECT COALESCE(SUM(amount),0) FROM payments WHERE status IN $pendingSql AND $payDateCol <= ?";
            $params = [$endDate . " 23:59:59"];
            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments', 'company_id')) {
                $sql .= " AND (company_id = ? OR company_id IS NULL)";
                $params[] = $companyId;
            }
            $balance['liab_outstanding_payments'] = fin_scalar($pdo, $sql, $params);
        }

        $balance['assets_total'] = (float)$balance['assets_cash'] + (float)$balance['assets_receivables'];
        $balance['liab_total'] = (float)$balance['liab_pending_refunds'] + (float)$balance['liab_outstanding_payments'];
        $balance['equity'] = (float)$balance['assets_total'] - (float)$balance['liab_total'];
    }

    $revByEstate = [];
    $expByEstate = [];

    if ($hasPayments) {
        $hasAllocations = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
        $hasProperties = $pdo->query("SHOW TABLES LIKE 'properties'")->rowCount() > 0;
        $hasEstates = $pdo->query("SHOW TABLES LIKE 'estates'")->rowCount() > 0;
        if ($hasAllocations && $hasProperties && $hasEstates && function_exists('tableHasColumn') && tableHasColumn('properties', 'estate_id')) {
            $joinInv = '';
            $allocExpr = 'pm.allocation_id';
            $hasInvoices = $pdo->query("SHOW TABLES LIKE 'invoices'")->rowCount() > 0;
            if ($hasInvoices && function_exists('tableHasColumn') && tableHasColumn('payments', 'invoice_id') && tableHasColumn('invoices', 'allocation_id')) {
                $joinInv = " LEFT JOIN invoices inv ON inv.id = pm.invoice_id";
                $allocExpr = "COALESCE(pm.allocation_id, inv.allocation_id)";
            }
            $sql = "SELECT es.id AS estate_id, es.name AS estate_name, COALESCE(SUM(pm.amount),0) AS total
                    FROM payments pm
                    $joinInv
                    JOIN allocations a ON a.id = $allocExpr
                    JOIN properties pr ON pr.id = a.property_id
                    JOIN estates es ON es.id = pr.estate_id
                    WHERE pm.status IN $payStatusSql
                      AND pm.$payDateCol BETWEEN ? AND ?";
            $params = [$startDate . " 00:00:00", $endDate . " 23:59:59"];
            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments', 'company_id')) {
                $sql .= " AND (pm.company_id = ? OR pm.company_id IS NULL)";
                $params[] = $companyId;
            }
            $sql .= " GROUP BY es.id, es.name";
            $st = $pdo->prepare($sql);
            $st->execute($params);
            foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $r) {
                $eid = (int)($r['estate_id'] ?? 0);
                $name = trim((string)($r['estate_name'] ?? ''));
                if ($eid <= 0 || $name === '') continue;
                $revByEstate[$eid] = ['estate_id' => $eid, 'estate_name' => $name, 'revenue' => (float)($r['total'] ?? 0)];
            }
        }
    }

    $paramsU = [];
    $union = function_exists('buildExpensesUnionSql')
        ? buildExpensesUnionSql(['start_date' => $startDate, 'end_date' => $endDate], $paramsU, false)
        : "(SELECT 'manual' AS source, 0 AS reference_id, 0 AS amount, NULL AS date, NULL AS recorded_by_user_id, NULL AS recorded_by, '' AS category, '' AS status, '' AS account_head, '' AS account_sub_head, '' AS account_code, NULL AS paid_through_account_id, NULL AS vendor_id, NULL AS reference_number, NULL AS estate_name_manual, NULL AS estate_id, NULL AS project_id WHERE 1=0)";
    $hasEst = false;
    try { $hasEst = $pdo->query("SHOW TABLES LIKE 'estates'")->rowCount() > 0; } catch (Throwable $e) { $hasEst = false; }
    $join = $hasEst ? " LEFT JOIN estates es ON es.id = e.estate_id" : "";
    $estateSelId = $hasEst ? "es.id AS estate_id_resolved" : "NULL AS estate_id_resolved";
    $estateSelName = $hasEst ? "es.name AS estate_name_resolved" : "NULL AS estate_name_resolved";
    $sql = "SELECT $estateSelId, $estateSelName, e.estate_id, e.estate_name_manual, COALESCE(SUM(e.amount),0) AS total
            FROM $union e$join
            GROUP BY estate_id_resolved, estate_name_resolved, e.estate_id, e.estate_name_manual";
    $st = $pdo->prepare($sql);
    $st->execute($paramsU);
    foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $r) {
        $eid = (int)($r['estate_id_resolved'] ?? 0);
        $name = trim((string)($r['estate_name_resolved'] ?? ''));
        if ($name === '') { $name = trim((string)($r['estate_name_manual'] ?? '')); }
        if ($name === '') { $name = 'Unassigned'; }
        $k = $eid > 0 ? $eid : 0;
        if (!isset($expByEstate[$k])) {
            $expByEstate[$k] = ['estate_id' => $eid > 0 ? $eid : null, 'estate_name' => $name, 'expense' => 0.0];
        }
        $expByEstate[$k]['expense'] += (float)($r['total'] ?? 0);
    }

    $allKeys = array_unique(array_merge(array_keys($revByEstate), array_keys($expByEstate)));
    foreach ($allKeys as $k) {
        $rev = isset($revByEstate[$k]) ? (float)$revByEstate[$k]['revenue'] : 0.0;
        $expName = isset($expByEstate[$k]) ? (string)$expByEstate[$k]['estate_name'] : (isset($revByEstate[$k]) ? (string)$revByEstate[$k]['estate_name'] : 'Unassigned');
        $exp = isset($expByEstate[$k]) ? (float)$expByEstate[$k]['expense'] : 0.0;
        $estateRows[] = ['estate_id' => $k ?: null, 'estate_name' => $expName, 'revenue' => $rev, 'expense' => $exp, 'profit' => $rev - $exp];
    }
    usort($estateRows, function($a, $b) {
        $pa = (float)($a['profit'] ?? 0);
        $pb = (float)($b['profit'] ?? 0);
        if ($pa === $pb) return 0;
        return $pa < $pb ? 1 : -1;
    });
} catch (Throwable $e) {}
?>

<style>
    .fr-card { border: 0; border-radius: 1rem; overflow: hidden; }
    .fr-card .card-body { padding: 1.15rem 1.25rem; }
    .fr-label { font-size: .85rem; color: #64748b; }
    .fr-value { font-size: 1.25rem; font-weight: 800; color: #0f172a; }
    .fr-muted { color: #64748b; font-size: .85rem; }
    .fr-table td, .fr-table th { vertical-align: middle; }
    .fr-tabs a { text-decoration: none; }
    .ai-insight-card{border:1px solid rgba(15,23,42,.08);border-radius:14px;padding:12px;background:#fff}
    .ai-insight-card.risk{border-color:rgba(239,68,68,.35);background:rgba(239,68,68,.06)}
    .ai-insight-card.warning{border-color:rgba(245,158,11,.40);background:rgba(245,158,11,.08)}
    .ai-insight-card.info{border-color:rgba(34,197,94,.32);background:rgba(34,197,94,.07)}
    .ai-insight-badge{display:inline-flex;align-items:center;padding:2px 10px;border-radius:999px;font-size:12px;font-weight:800;letter-spacing:.06em}
    .ai-insight-badge.risk{background:rgba(239,68,68,.12);color:#991b1b}
    .ai-insight-badge.warning{background:rgba(245,158,11,.16);color:#92400e}
    .ai-insight-badge.info{background:rgba(34,197,94,.12);color:#166534}
</style>

<div class="container-fluid px-4 pb-4">
    <div class="d-flex flex-wrap justify-content-between align-items-center mt-4 mb-3 gap-2">
        <div>
            <h2 class="fw-bold mb-1">Financial Reports</h2>
            <div class="fr-muted">Approved/verified-only reporting</div>
        </div>
        <div class="fr-tabs d-flex gap-2">
            <a class="btn btn-sm <?= $type === 'pl' ? 'btn-primary' : 'btn-outline-primary' ?>" href="finance-reports.php?type=pl&amp;year=<?= urlencode((string)$year) ?>&amp;month=<?= urlencode($isAllYear ? 'all' : (string)$monthNum) ?>">P&amp;L</a>
            <a class="btn btn-sm <?= $type === 'balance' ? 'btn-primary' : 'btn-outline-primary' ?>" href="finance-reports.php?type=balance&amp;year=<?= urlencode((string)$year) ?>&amp;month=<?= urlencode($isAllYear ? 'all' : (string)$monthNum) ?>">Balance Sheet</a>
        </div>
    </div>

    <div class="card shadow-sm mb-3">
        <div class="card-body">
            <form class="row g-3 align-items-end" method="GET">
                <input type="hidden" name="type" value="<?= htmlspecialchars($type) ?>">
                <div class="col-6 col-md-4">
                    <label class="form-label">Month</label>
                    <select class="form-select" name="month">
                        <option value="all" <?= $isAllYear ? 'selected' : '' ?>>All Year</option>
                        <?php for ($m = 1; $m <= 12; $m++): ?>
                            <option value="<?= $m ?>" <?= (!$isAllYear && $monthNum === $m) ? 'selected' : '' ?>><?= date('F', strtotime(sprintf('%04d-%02d-01', $year, $m))) ?></option>
                        <?php endfor; ?>
                    </select>
                </div>
                <div class="col-6 col-md-4">
                    <label class="form-label">Year</label>
                    <input type="number" class="form-control" name="year" min="2000" max="2100" value="<?= htmlspecialchars((string)$year) ?>">
                </div>
                <div class="col-12 col-md-4 d-grid">
                    <button class="btn btn-primary" type="submit">Apply</button>
                </div>
            </form>
            <div class="fr-muted mt-2">Period: <?= htmlspecialchars($startDate) ?> to <?= htmlspecialchars($endDate) ?></div>
        </div>
    </div>

    <?php if ($type === 'pl'): ?>
        <?php
            $aiInsightsTop = [];
            try {
                if (function_exists('generate_financial_insights')) {
                    $startObj = DateTimeImmutable::createFromFormat('Y-m-d', substr((string)$startDate, 0, 10)) ?: new DateTimeImmutable((string)$startDate);
                    $endObj = DateTimeImmutable::createFromFormat('Y-m-d', substr((string)$endDate, 0, 10)) ?: new DateTimeImmutable((string)$endDate);
                    if ($endObj < $startObj) { $tmp = $startObj; $startObj = $endObj; $endObj = $tmp; }
                    $days = (int)($endObj->diff($startObj)->days) + 1;
                    if ($days <= 0) $days = 1;
                    $prevStartObj = $startObj->sub(new DateInterval('P' . $days . 'D'));
                    $prevEndObj = $endObj->sub(new DateInterval('P' . $days . 'D'));

                    $payDateColAi = $payDateCol ?? (function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'created_at');
                    if (!$payDateColAi) { $payDateColAi = 'created_at'; }
                    $payStatusSqlAi = $payStatusSql ?? (function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')");

                    $revThis = 0.0;
                    $revPrev = 0.0;
                    try {
                        if (!empty($hasPayments)) {
                            $q = "SELECT COALESCE(SUM(amount),0) FROM payments WHERE status IN $payStatusSqlAi AND $payDateColAi BETWEEN ? AND ?";
                            $p = [$startObj->format('Y-m-d') . ' 00:00:00', $endObj->format('Y-m-d') . ' 23:59:59'];
                            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments', 'company_id')) { $q .= " AND (company_id = ? OR company_id IS NULL)"; $p[] = $companyId; }
                            $revThis = fin_scalar($pdo, $q, $p);

                            $p2 = [$prevStartObj->format('Y-m-d') . ' 00:00:00', $prevEndObj->format('Y-m-d') . ' 23:59:59'];
                            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments', 'company_id')) { $p2[] = $companyId; }
                            $revPrev = fin_scalar($pdo, $q, $p2);
                        }
                    } catch (Throwable $e) { $revThis = 0.0; $revPrev = 0.0; }

                    $expThis = 0.0;
                    $expPrev = 0.0;
                    try {
                        if (function_exists('buildExpensesUnionSql')) {
                            $paramsE = [];
                            $unionE = buildExpensesUnionSql(['start_date' => $startObj->format('Y-m-d'), 'end_date' => $endObj->format('Y-m-d'), 'include_pending_manual' => false], $paramsE, false);
                            $stE = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM $unionE e");
                            $stE->execute($paramsE);
                            $expThis = (float)($stE->fetchColumn() ?: 0);

                            $paramsE2 = [];
                            $unionE2 = buildExpensesUnionSql(['start_date' => $prevStartObj->format('Y-m-d'), 'end_date' => $prevEndObj->format('Y-m-d'), 'include_pending_manual' => false], $paramsE2, false);
                            $stE2 = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM $unionE2 e");
                            $stE2->execute($paramsE2);
                            $expPrev = (float)($stE2->fetchColumn() ?: 0);
                        }
                    } catch (Throwable $e) { $expThis = 0.0; $expPrev = 0.0; }

                    $pendingApprovals = 0;
                    try {
                        if (!empty($hasManual) && function_exists('tableHasColumn') && tableHasColumn('expenses_manual','status')) {
                            $q = "SELECT COUNT(*) FROM expenses_manual WHERE LOWER(TRIM(status)) = 'pending'";
                            $p = [];
                            if ($companyId && tableHasColumn('expenses_manual','company_id')) { $q .= " AND (company_id = ? OR company_id IS NULL)"; $p[] = $companyId; }
                            $st = $pdo->prepare($q);
                            $st->execute($p);
                            $pendingApprovals = (int)($st->fetchColumn() ?: 0);
                        }
                    } catch (Throwable $e) { $pendingApprovals = 0; }

                    $pendingPayments = 0;
                    try {
                        if (!empty($hasPayments) && function_exists('tableHasColumn')) {
                            $stCol = tableHasColumn('payments', 'status') ? 'status' : (tableHasColumn('payments', 'payment_status') ? 'payment_status' : null);
                            if ($stCol) {
                                $pendingStatuses = function_exists('kpiPaymentPendingStatuses') ? (array)kpiPaymentPendingStatuses() : ['submitted', 'pending', 'pending_gateway', 'awaiting_verification', 'pending_verification', 'pending_confirmation'];
                                $ph = implode(',', array_fill(0, count($pendingStatuses), '?'));
                                $q = "SELECT COUNT(*) FROM payments WHERE LOWER(TRIM($stCol)) IN ($ph)";
                                $p = array_map(function ($v) { return strtolower(trim((string)$v)); }, $pendingStatuses);
                                if ($companyId && tableHasColumn('payments', 'company_id')) { $q .= " AND (company_id = ? OR company_id IS NULL)"; $p[] = $companyId; }
                                $st = $pdo->prepare($q);
                                $st->execute($p);
                                $pendingPayments = (int)($st->fetchColumn() ?: 0);
                            }
                        }
                    } catch (Throwable $e) { $pendingPayments = 0; }

                    $bankPositions = [];
                    try {
                        $q = "SELECT id, " . ((function_exists('tableHasColumn') && tableHasColumn('finance_accounts','account_name')) ? 'account_name' : (tableHasColumn('finance_accounts','name') ? 'name' : "CONCAT('Account #', id)")) . " AS account_name, balance FROM finance_accounts WHERE 1=1";
                        $p = [];
                        if ($companyId && function_exists('tableHasColumn') && tableHasColumn('finance_accounts','company_id')) { $q .= " AND (company_id = ? OR company_id IS NULL)"; $p[] = $companyId; }
                        $q .= " ORDER BY account_name";
                        $st = $pdo->prepare($q);
                        $st->execute($p);
                        $bankPositions = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
                    } catch (Throwable $e) { $bankPositions = []; }

                    $lowBalanceThreshold = 0.0;
                    try {
                        $raw = function_exists('getCompanySetting') ? getCompanySetting($companyId, 'low_balance_threshold', '') : '';
                        if ($raw === '' || $raw === null) { $raw = function_exists('getSetting') ? getSetting('low_balance_threshold', '') : ''; }
                        $lowBalanceThreshold = is_numeric($raw) ? (float)$raw : 0.0;
                    } catch (Throwable $e) { $lowBalanceThreshold = 0.0; }

                    $profitByEstate = [];
                    try {
                        foreach ((array)$estateRows as $r) {
                            if (!is_array($r)) continue;
                            $eid = (int)($r['estate_id'] ?? 0);
                            $profitByEstate[] = [
                                'estate_id' => $eid > 0 ? $eid : 0,
                                'estate' => (string)($r['estate_name'] ?? ''),
                                'revenue' => (float)($r['revenue'] ?? 0),
                                'expenses' => (float)($r['expense'] ?? 0),
                                'profit' => (float)($r['profit'] ?? 0),
                            ];
                        }
                    } catch (Throwable $e) { $profitByEstate = []; }

                    $aiAll = (array)generate_financial_insights($pdo, [
                        'company_id' => $companyId,
                        'revenue' => (float)$revThis,
                        'revenue_prev' => (float)$revPrev,
                        'expenses' => (float)$expThis,
                        'expenses_prev' => (float)$expPrev,
                        'profit' => (float)$revThis - (float)$expThis,
                        'pending_approvals' => (int)$pendingApprovals,
                        'pending_payments' => (int)$pendingPayments,
                        'bank_positions' => $bankPositions,
                        'low_balance_threshold' => (float)$lowBalanceThreshold,
                        'profit_by_estate' => $profitByEstate,
                        'period_start' => $startObj->format('Y-m-d'),
                        'period_end' => $endObj->format('Y-m-d'),
                    ]);
                    $aiInsightsTop = array_slice($aiAll, 0, 5);
                }
            } catch (Throwable $e) { $aiInsightsTop = []; }
        ?>

        <div class="row g-3 mb-3">
            <div class="col-12 col-md-4">
                <div class="card fr-card shadow-sm">
                    <div class="card-body">
                        <div class="fr-label">Total Revenue</div>
                        <div class="fr-value text-success">₦<?= number_format((float)$pl['revenue_total'], 2) ?></div>
                    </div>
                </div>
            </div>
            <div class="col-12 col-md-4">
                <div class="card fr-card shadow-sm">
                    <div class="card-body">
                        <div class="fr-label">Total Expenses</div>
                        <div class="fr-value text-danger">₦<?= number_format((float)$pl['exp_total'], 2) ?></div>
                    </div>
                </div>
            </div>
            <div class="col-12 col-md-4">
                <div class="card fr-card shadow-sm">
                    <div class="card-body">
                        <div class="fr-label">Net Profit</div>
                        <?php $np = (float)$pl['net_profit']; ?>
                        <div class="fr-value <?= $np >= 0 ? 'text-success' : 'text-danger' ?>">
                            <?= $np >= 0 ? '₦' . number_format($np, 2) : '-₦' . number_format(abs($np), 2) ?>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <div class="card shadow-sm mb-3">
            <div class="card-header bg-white d-flex align-items-center justify-content-between">
                <div class="fw-bold">AI Financial Insights</div>
                <div class="fr-muted">Top 5</div>
            </div>
            <div class="card-body">
                <?php if (empty($aiInsightsTop)): ?>
                    <div class="text-muted">No insights available for this period yet.</div>
                <?php else: ?>
                    <div class="row g-3">
                        <?php foreach ($aiInsightsTop as $ins): ?>
                            <?php
                                $t = strtolower(trim((string)($ins['type'] ?? 'info')));
                                if (!in_array($t, ['risk','warning','info'], true)) $t = 'info';
                            ?>
                            <div class="col-12 col-lg-6">
                                <div class="ai-insight-card <?= htmlspecialchars($t) ?>">
                                    <div class="d-flex align-items-start justify-content-between gap-2">
                                        <div class="fw-bold"><?= htmlspecialchars((string)($ins['title'] ?? 'Insight')) ?></div>
                                        <span class="ai-insight-badge <?= htmlspecialchars($t) ?>"><?= htmlspecialchars(strtoupper($t)) ?></span>
                                    </div>
                                    <div class="text-muted small mt-1"><?= htmlspecialchars((string)($ins['description'] ?? '')) ?></div>
                                    <div class="mt-2 fw-bold" style="font-size:12px;letter-spacing:.06em;text-transform:uppercase;color:#64748b">Recommendation</div>
                                    <div class="small"><?= htmlspecialchars((string)($ins['recommendation'] ?? '')) ?></div>
                                </div>
                            </div>
                        <?php endforeach; ?>
                    </div>
                <?php endif; ?>
            </div>
        </div>

        <div class="row g-3 mb-3">
            <div class="col-12 col-lg-6">
                <div class="card shadow-sm">
                    <div class="card-header bg-white">
                        <div class="fw-bold">Revenue Breakdown</div>
                    </div>
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-sm fr-table mb-0">
                                <tbody>
                                    <tr>
                                        <td class="fw-semibold">Sales</td>
                                        <td class="text-end text-success">₦<?= number_format((float)$pl['revenue_sales'], 2) ?></td>
                                    </tr>
                                    <tr>
                                        <td class="fw-semibold">Installments</td>
                                        <td class="text-end text-success">₦<?= number_format((float)$pl['revenue_installments'], 2) ?></td>
                                    </tr>
                                    <tr class="table-light">
                                        <td class="fw-bold">Total</td>
                                        <td class="text-end fw-bold text-success">₦<?= number_format((float)$pl['revenue_total'], 2) ?></td>
                                    </tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-12 col-lg-6">
                <div class="card shadow-sm">
                    <div class="card-header bg-white">
                        <div class="fw-bold">Expenses Breakdown</div>
                    </div>
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-sm fr-table mb-0">
                                <tbody>
                                    <tr>
                                        <td class="fw-semibold">Maintenance</td>
                                        <td class="text-end text-danger">₦<?= number_format((float)$pl['exp_maintenance'], 2) ?></td>
                                    </tr>
                                    <tr>
                                        <td class="fw-semibold">Commissions</td>
                                        <td class="text-end text-danger">₦<?= number_format((float)$pl['exp_commissions'], 2) ?></td>
                                    </tr>
                                    <tr>
                                        <td class="fw-semibold">Manual Expenses</td>
                                        <td class="text-end text-danger">₦<?= number_format((float)$pl['exp_manual'], 2) ?></td>
                                    </tr>
                                    <tr class="table-light">
                                        <td class="fw-bold">Total</td>
                                        <td class="text-end fw-bold text-danger">₦<?= number_format((float)$pl['exp_total'], 2) ?></td>
                                    </tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    <?php else: ?>
        <div class="row g-3 mb-3">
            <div class="col-12 col-md-4">
                <div class="card fr-card shadow-sm">
                    <div class="card-body">
                        <div class="fr-label">Total Assets</div>
                        <div class="fr-value">₦<?= number_format((float)$balance['assets_total'], 2) ?></div>
                    </div>
                </div>
            </div>
            <div class="col-12 col-md-4">
                <div class="card fr-card shadow-sm">
                    <div class="card-body">
                        <div class="fr-label">Total Liabilities</div>
                        <div class="fr-value">₦<?= number_format((float)$balance['liab_total'], 2) ?></div>
                    </div>
                </div>
            </div>
            <div class="col-12 col-md-4">
                <div class="card fr-card shadow-sm">
                    <div class="card-body">
                        <div class="fr-label">Equity</div>
                        <?php $eq = (float)$balance['equity']; ?>
                        <div class="fr-value <?= $eq >= 0 ? 'text-success' : 'text-danger' ?>">
                            <?= $eq >= 0 ? '₦' . number_format($eq, 2) : '-₦' . number_format(abs($eq), 2) ?>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <div class="row g-3 mb-3">
            <div class="col-12 col-lg-6">
                <div class="card shadow-sm">
                    <div class="card-header bg-white">
                        <div class="fw-bold">Assets</div>
                    </div>
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-sm fr-table mb-0">
                                <tbody>
                                    <tr>
                                        <td class="fw-semibold">Cash (Finance Accounts)</td>
                                        <td class="text-end">₦<?= number_format((float)$balance['assets_cash'], 2) ?></td>
                                    </tr>
                                    <tr>
                                        <td class="fw-semibold">Receivables</td>
                                        <td class="text-end">₦<?= number_format((float)$balance['assets_receivables'], 2) ?></td>
                                    </tr>
                                    <tr class="table-light">
                                        <td class="fw-bold">Total Assets</td>
                                        <td class="text-end fw-bold">₦<?= number_format((float)$balance['assets_total'], 2) ?></td>
                                    </tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-12 col-lg-6">
                <div class="card shadow-sm">
                    <div class="card-header bg-white">
                        <div class="fw-bold">Liabilities</div>
                    </div>
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-sm fr-table mb-0">
                                <tbody>
                                    <tr>
                                        <td class="fw-semibold">Pending Refunds</td>
                                        <td class="text-end">₦<?= number_format((float)$balance['liab_pending_refunds'], 2) ?></td>
                                    </tr>
                                    <tr>
                                        <td class="fw-semibold">Outstanding Payments (Unverified)</td>
                                        <td class="text-end">₦<?= number_format((float)$balance['liab_outstanding_payments'], 2) ?></td>
                                    </tr>
                                    <tr class="table-light">
                                        <td class="fw-bold">Total Liabilities</td>
                                        <td class="text-end fw-bold">₦<?= number_format((float)$balance['liab_total'], 2) ?></td>
                                    </tr>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    <?php endif; ?>

    <div class="card shadow-sm">
        <div class="card-header bg-white">
            <div class="fw-bold">Per-Estate Report</div>
            <div class="fr-muted">Revenue per estate, expenses per estate, profit per estate</div>
        </div>
        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-sm fr-table mb-0">
                    <thead class="table-light">
                        <tr>
                            <th>Estate</th>
                            <th class="text-end">Revenue</th>
                            <th class="text-end">Expenses</th>
                            <th class="text-end">Profit</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($estateRows)): ?>
                            <tr><td colspan="4" class="text-center text-muted py-3">No data</td></tr>
                        <?php else: ?>
                            <?php foreach ($estateRows as $r): ?>
                                <?php $p = (float)($r['profit'] ?? 0); ?>
                                <tr>
                                    <td><?= htmlspecialchars((string)($r['estate_name'] ?? '')) ?></td>
                                    <td class="text-end text-success">₦<?= number_format((float)($r['revenue'] ?? 0), 2) ?></td>
                                    <td class="text-end text-danger">₦<?= number_format((float)($r['expense'] ?? 0), 2) ?></td>
                                    <td class="text-end <?= $p >= 0 ? 'text-success' : 'text-danger' ?>">
                                        <?= $p >= 0 ? '₦' . number_format($p, 2) : '-₦' . number_format(abs($p), 2) ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

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