<?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/header.php';
global $pdo;
$role = strtolower($_SESSION['user_role'] ?? 'guest');
if (!in_array($role, ['admin','executive','finance','finance_manager','super_admin','accountant'])) {
    echo "<div class='container py-4'><div class='alert alert-danger'>Access Denied</div></div>";
    require_once __DIR__ . '/includes/footer.php';
    exit;
}
$companyId = function_exists('getCurrentCompanyId') ? getCurrentCompanyId() : null;

$cards = ['properties'=>0,'clients'=>0,'revenue'=>0.0,'outstanding'=>0.0];
$rows = [];
try {
    $hasPay = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
    $hasProps = $pdo->query("SHOW TABLES LIKE 'properties'")->rowCount() > 0;
    $hasAlloc = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
    $hasDeals = $pdo->query("SHOW TABLES LIKE 'deals'")->rowCount() > 0;
    $hasExpenses = $pdo->query("SHOW TABLES LIKE 'expenses'")->rowCount() > 0;

    $from = trim((string)($_GET['from'] ?? ''));
    $to = trim((string)($_GET['to'] ?? ''));
    $fromObj = null; $toObj = null;
    try { $fromObj = $from !== '' ? new DateTime($from) : null; } catch (Throwable $e) { $fromObj = null; }
    try { $toObj = $to !== '' ? new DateTime($to) : null; } catch (Throwable $e) { $toObj = null; }
    if (!$fromObj || !$toObj) {
        $fromObj = new DateTime('first day of this month');
        $toObj = new DateTime('last day of this month');
    }
    $from = $fromObj->format('Y-m-d');
    $to = $toObj->format('Y-m-d');

    $paymentDateCol = 'created_at';
    try {
        if (function_exists('kpiPaymentDateColumn')) {
            $c = kpiPaymentDateColumn('payments');
            if ($c) { $paymentDateCol = $c; }
        } elseif (function_exists('tableHasColumn')) {
            if (tableHasColumn('payments','updated_at')) { $paymentDateCol = 'updated_at'; }
            elseif (tableHasColumn('payments','created_at')) { $paymentDateCol = 'created_at'; }
        }
    } catch (Throwable $e) { $paymentDateCol = 'created_at'; }

    $statusSql = "('approved')";

    if ($hasPay && $hasProps) {
        $allocJoin = ($hasAlloc && function_exists('tableHasColumn') && tableHasColumn('payments','allocation_id') && tableHasColumn('allocations','id'))
            ? "LEFT JOIN allocations a ON a.id = pm.allocation_id"
            : "LEFT JOIN (SELECT NULL AS id, NULL AS property_id, NULL AS user_id, NULL AS company_id) a ON 1=0";
        $dealJoin = ($hasDeals && function_exists('tableHasColumn') && tableHasColumn('payments','deal_id') && tableHasColumn('deals','id'))
            ? "LEFT JOIN deals d ON d.id = pm.deal_id"
            : "LEFT JOIN (SELECT NULL AS id, NULL AS property_id, NULL AS user_id, NULL AS company_id) d ON 1=0";

        $propExpr = "COALESCE(a.property_id, d.property_id" . ((function_exists('tableHasColumn') && tableHasColumn('payments','property_id')) ? ", pm.property_id" : "") . ")";
        $clientExpr = "COALESCE(a.user_id" . ((function_exists('tableHasColumn') && tableHasColumn('payments','user_id')) ? ", pm.user_id" : "") . ", d.user_id)";

        $cmpPay = '';
        $params = [$from . ' 00:00:00', $to . ' 23:59:59'];
        if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) { $cmpPay = " AND (pm.company_id = ? OR pm.company_id IS NULL)"; $params[] = $companyId; }

        $revSub = "
            SELECT
                $propExpr AS property_id,
                COUNT(DISTINCT $clientExpr) AS total_clients,
                COALESCE(SUM(pm.amount),0) AS total_revenue,
                MAX(pm.$paymentDateCol) AS last_payment_at
            FROM payments pm
            $allocJoin
            $dealJoin
            WHERE pm.status IN $statusSql
              AND pm.$paymentDateCol BETWEEN ? AND ?
              $cmpPay
              AND $propExpr IS NOT NULL
            GROUP BY $propExpr
        ";

        $expSub = null;
        $expParams = [$from . ' 00:00:00', $to . ' 23:59:59'];
        $cmpExp = '';
        if ($companyId && $hasExpenses && function_exists('tableHasColumn') && tableHasColumn('expenses', 'company_id')) { $cmpExp = " AND (e.company_id = ? OR e.company_id IS NULL)"; $expParams[] = $companyId; }
        if ($hasExpenses && function_exists('tableHasColumn') && tableHasColumn('expenses', 'property_id') && tableHasColumn('expenses', 'amount') && tableHasColumn('expenses', 'status') && tableHasColumn('expenses', 'created_at')) {
            $expSub = "
                SELECT
                    e.property_id,
                    COALESCE(SUM(e.amount),0) AS total_expense
                FROM expenses e
                WHERE LOWER(TRIM(e.status)) = 'approved'
                  AND e.created_at BETWEEN ? AND ?
                  $cmpExp
                  AND e.property_id IS NOT NULL
                GROUP BY e.property_id
            ";
        }

        $cmpProp = '';
        $propParams = [];
        if ($companyId && function_exists('tableHasColumn') && tableHasColumn('properties','company_id')) { $cmpProp = " WHERE p.company_id = ?"; $propParams[] = $companyId; }

        $sql = "
            SELECT
                p.id,
                COALESCE(p.title, p.property_name) AS property_name,
                COALESCE(r.total_clients,0) AS total_clients,
                COALESCE(r.total_revenue,0) AS total_revenue,
                r.last_payment_at
                " . ($expSub ? ", COALESCE(e2.total_expense,0) AS total_expense, (COALESCE(r.total_revenue,0) - COALESCE(e2.total_expense,0)) AS profit" : ", 0 AS total_expense, COALESCE(r.total_revenue,0) AS profit") . "
            FROM properties p
            LEFT JOIN ($revSub) r ON r.property_id = p.id
            " . ($expSub ? "LEFT JOIN ($expSub) e2 ON e2.property_id = p.id" : "") . "
            $cmpProp
            ORDER BY total_revenue DESC, property_name ASC
        ";
        $st = $pdo->prepare($sql);
        $execParams = $params;
        if ($expSub) { $execParams = array_merge($execParams, $expParams); }
        if (!empty($propParams)) { $execParams = array_merge($execParams, $propParams); }
        $st->execute($execParams);
        $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    } else {
        $rows = [];
    }
    $cards['properties'] = count($rows);
    $cards['clients'] = array_sum(array_map(function($r){ return (int)($r['total_clients'] ?? 0); }, $rows));
    $cards['revenue'] = array_sum(array_map(function($r){ return (float)($r['total_revenue'] ?? 0.0); }, $rows));
    $cards['expenses'] = array_sum(array_map(function($r){ return (float)($r['total_expense'] ?? 0.0); }, $rows));
    $cards['profit'] = array_sum(array_map(function($r){ return (float)($r['profit'] ?? 0.0); }, $rows));
    // Outstanding: sum of (amount_due - paid_amount) across installments
    try {
        $query = "
            SELECT COALESCE(SUM(i.amount_due - i.paid_amount),0) AS outstanding
            FROM installments i
            LEFT JOIN allocations a ON i.allocation_id = a.id
        ";
        $params2 = [];
        if ($companyId && function_exists('tableHasColumn') && tableHasColumn('allocations','company_id')) {
            $query .= " WHERE a.company_id = ?";
            $params2[] = $companyId;
        }
        $st2 = $pdo->prepare($query);
        $st2->execute($params2);
        $cards['outstanding'] = (float)($st2->fetchColumn() ?: 0.0);
    } catch (Throwable $e) { $cards['outstanding'] = 0.0; }
} catch (Throwable $e) {}
?>
<div class="container-fluid px-4">
  <div class="d-flex justify-content-between align-items-center mt-4 mb-3">
    <h1 class="h3 mb-0"><i class="fa-solid fa-chart-line me-2"></i>Property Performance</h1>
    <div><span class="badge bg-primary">Role: <?= htmlspecialchars($role) ?></span></div>
  </div>
  <div class="card shadow-sm rounded-3 mb-3">
    <div class="card-body">
      <form class="row g-2 align-items-end" method="get">
        <div class="col-sm-4 col-md-3">
          <label class="form-label small text-muted">From</label>
          <input type="date" name="from" class="form-control" value="<?= htmlspecialchars($from ?? '') ?>">
        </div>
        <div class="col-sm-4 col-md-3">
          <label class="form-label small text-muted">To</label>
          <input type="date" name="to" class="form-control" value="<?= htmlspecialchars($to ?? '') ?>">
        </div>
        <div class="col-sm-4 col-md-3">
          <button class="btn btn-primary w-100" type="submit">Filter</button>
        </div>
        <div class="col-12 col-md-3">
          <div class="small text-muted">Shows income from finalized payments (approved/verified/paid/completed).</div>
        </div>
      </form>
    </div>
  </div>
  <div class="row g-3">
    <div class="col-md-3"><div class="card p-3 shadow-sm rounded-3"><div class="text-muted small">Total Properties</div><div class="fw-bold fs-5"><?= (int)$cards['properties'] ?></div></div></div>
    <div class="col-md-3"><div class="card p-3 shadow-sm rounded-3"><div class="text-muted small">Total Clients</div><div class="fw-bold fs-5"><?= (int)$cards['clients'] ?></div></div></div>
    <div class="col-md-3"><div class="card p-3 shadow-sm rounded-3"><div class="text-muted small">Total Revenue</div><div class="fw-bold fs-5">₦<?= number_format((float)$cards['revenue']) ?></div></div></div>
    <div class="col-md-3"><div class="card p-3 shadow-sm rounded-3"><div class="text-muted small">Outstanding</div><div class="fw-bold fs-5">₦<?= number_format((float)$cards['outstanding']) ?></div></div></div>
    <div class="col-md-3"><div class="card p-3 shadow-sm rounded-3"><div class="text-muted small">Total Expenses</div><div class="fw-bold fs-5">₦<?= number_format((float)($cards['expenses'] ?? 0)) ?></div></div></div>
    <div class="col-md-3"><div class="card p-3 shadow-sm rounded-3"><div class="text-muted small">Total Profit</div><div class="fw-bold fs-5">₦<?= number_format((float)($cards['profit'] ?? 0)) ?></div></div></div>
  </div>
  <div class="card mt-4 shadow-sm rounded-3">
    <div class="card-header">Property Revenue</div>
    <div class="table-responsive">
      <table class="table table-hover align-middle">
        <thead>
          <tr>
            <th>Property</th>
            <th>Clients</th>
            <th>Revenue</th>
            <th>Expenses</th>
            <th>Profit</th>
            <th>Last Payment</th>
            <th>Action</th>
          </tr>
        </thead>
        <tbody>
          <?php foreach ($rows as $r): ?>
          <tr>
            <td><?= htmlspecialchars($r['property_name'] ?? 'Property') ?></td>
            <td><?= (int)($r['total_clients'] ?? 0) ?></td>
            <td>₦<?= number_format((float)($r['total_revenue'] ?? 0.0)) ?></td>
            <td>₦<?= number_format((float)($r['total_expense'] ?? 0.0)) ?></td>
            <td>₦<?= number_format((float)($r['profit'] ?? 0.0)) ?></td>
            <td><?= htmlspecialchars((string)($r['last_payment_at'] ?? '-')) ?></td>
            <td><a class="btn btn-sm btn-outline-primary" href="property-details.php?id=<?= (int)$r['id'] ?>">View</a></td>
          </tr>
          <?php endforeach; ?>
          <?php if (empty($rows)): ?><tr><td colspan="7" class="text-center text-muted">No properties found.</td></tr><?php endif; ?>
        </tbody>
      </table>
    </div>
  </div>
</div>
<?php require_once __DIR__ . '/includes/footer.php'; ?>