<?php
session_start();
include 'layouts/dbconn.php';

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: login.php');
    exit();
}

// Check if user is admin (role_id = 1)
if (!isset($_SESSION['role_id']) || $_SESSION['role_id'] != 1) {
    header('Location: login.php');
    exit();
}

$user_id = $_SESSION['user_id'];

// Get user details
$sql_user = "SELECT u.id, u.first_name, u.middle_name, u.last_name, u.email, u.phone, r.role_name 
             FROM users u 
             INNER JOIN roles r ON u.role_id = r.id 
             WHERE u.id = ?";
$stmt_user = $conn->prepare($sql_user);
$stmt_user->bind_param("i", $user_id);
$stmt_user->execute();
$result_user = $stmt_user->get_result();
$user = $result_user->fetch_assoc();
$full_name = trim($user['first_name'] . ' ' . ($user['middle_name'] ? $user['middle_name'] . ' ' : '') . $user['last_name']);
$stmt_user->close();

// === DASHBOARD STATISTICS ===

// Total Properties
$sql_total_properties = "SELECT COUNT(*) as total FROM properties";
$result_properties = $conn->query($sql_total_properties);
$total_properties = $result_properties->fetch_assoc()['total'] ?? 0;

// Total Units
$sql_total_units = "SELECT COUNT(*) as total FROM units";
$result_units = $conn->query($sql_total_units);
$total_units = $result_units->fetch_assoc()['total'] ?? 0;

// Vacant Units
$sql_vacant_units = "SELECT COUNT(*) as total FROM units WHERE status = 'vacant'";
$result_vacant = $conn->query($sql_vacant_units);
$vacant_units = $result_vacant->fetch_assoc()['total'] ?? 0;

// Occupied Units
$occupied_units = $total_units - $vacant_units;

// Occupancy Rate
$occupancy_rate = $total_units > 0 ? ($occupied_units / $total_units) * 100 : 0;

// Total Tenants (Users with role_id = 4)
$sql_total_tenants = "SELECT COUNT(*) as total FROM users WHERE role_id = 4 AND status = 'active'";
$result_tenants = $conn->query($sql_total_tenants);
$total_tenants = $result_tenants->fetch_assoc()['total'] ?? 0;

// Active Leases
$sql_active_leases = "SELECT COUNT(*) as total FROM leases WHERE status = 'active'";
$result_active_leases = $conn->query($sql_active_leases);
$active_leases = $result_active_leases->fetch_assoc()['total'] ?? 0;

// Pending Maintenance Requests
$sql_pending_maintenance = "SELECT COUNT(*) as total FROM maintenance_requests 
                            WHERE status IN ('Open', 'Assigned', 'In Progress')";
$result_pending_maintenance = $conn->query($sql_pending_maintenance);
$pending_maintenance = $result_pending_maintenance->fetch_assoc()['total'] ?? 0;

// Leases Expiring Soon (Next 30 days)
$sql_expiring_leases = "SELECT COUNT(*) as total FROM leases 
                       WHERE status = 'active' 
                       AND end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)";
$result_expiring = $conn->query($sql_expiring_leases);
$expiring_leases = $result_expiring->fetch_assoc()['total'] ?? 0;

// Expected Rent This Month (from active leases)
$sql_expected_rent = "SELECT COALESCE(SUM(rent_amount), 0) as total FROM leases WHERE status = 'active'";
$result_expected = $conn->query($sql_expected_rent);
$expected_rent = $result_expected->fetch_assoc()['total'] ?? 0;

// Collected Rent This Month (from payments)
$current_month = date('m');
$current_year = date('Y');
$sql_collected_rent = "SELECT COALESCE(SUM(p.amount), 0) as total 
                      FROM payments p
                      INNER JOIN invoices i ON p.invoice_id = i.id
                      WHERE p.status = 'verified' 
                      AND i.type = 'Rent'
                      AND MONTH(p.payment_date) = $current_month 
                      AND YEAR(p.payment_date) = $current_year";
$result_collected = $conn->query($sql_collected_rent);
$collected_rent = $result_collected->fetch_assoc()['total'] ?? 0;

// Collection Efficiency
$collection_efficiency = $expected_rent > 0 ? ($collected_rent / $expected_rent) * 100 : 0;

// Outstanding Balance
$sql_outstanding = "SELECT COALESCE(SUM(balance), 0) as total FROM invoices WHERE status IN ('unpaid', 'partial', 'overdue')";
$result_outstanding = $conn->query($sql_outstanding);
$outstanding_balance = $result_outstanding->fetch_assoc()['total'] ?? 0;

// Property Types Distribution
$sql_property_types = "SELECT type, COUNT(*) as count FROM properties GROUP BY type";
$result_property_types = $conn->query($sql_property_types);
$property_types_data = [];
while($row = $result_property_types->fetch_assoc()) {
    $property_types_data[] = $row;
}

// Unit Status Distribution
$sql_unit_status = "SELECT status, COUNT(*) as count FROM units GROUP BY status";
$result_unit_status = $conn->query($sql_unit_status);
$unit_status_data = [];
while($row = $result_unit_status->fetch_assoc()) {
    $unit_status_data[] = $row;
}

// Maintenance Request Status
$sql_maintenance_status = "SELECT status, COUNT(*) as count FROM maintenance_requests GROUP BY status";
$result_maintenance_status = $conn->query($sql_maintenance_status);
$maintenance_status_data = [];
while($row = $result_maintenance_status->fetch_assoc()) {
    $maintenance_status_data[] = $row;
}

// Recent Maintenance Requests
$sql_recent_maintenance = "SELECT mr.*, p.name as property_name, u.unit_number,
                          req.first_name, req.last_name
                         FROM maintenance_requests mr 
                         LEFT JOIN properties p ON mr.property_id = p.id 
                         LEFT JOIN units u ON mr.unit_id = u.id 
                         LEFT JOIN users req ON mr.requester_id = req.id 
                         ORDER BY mr.created_at DESC 
                         LIMIT 5";
$result_recent_maintenance = $conn->query($sql_recent_maintenance);

// Monthly Revenue Trend (Last 6 months)
$sql_revenue_trend = "SELECT 
                        DATE_FORMAT(p.payment_date, '%b %Y') as month_year,
                        YEAR(p.payment_date) as year,
                        MONTH(p.payment_date) as month,
                        COALESCE(SUM(p.amount), 0) as revenue 
                      FROM payments p 
                      WHERE p.status = 'verified' 
                      AND p.payment_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
                      GROUP BY YEAR(p.payment_date), MONTH(p.payment_date)
                      ORDER BY year, month";
$result_revenue_trend = $conn->query($sql_revenue_trend);
$revenue_trend_data = [];
while($row = $result_revenue_trend->fetch_assoc()) {
    $revenue_trend_data[] = $row;
}

// Top 5 Properties by Revenue
$sql_top_properties = "SELECT 
                        p.id,
                        p.name as property_name,
                        p.location_area,
                        COUNT(DISTINCT u.id) as unit_count,
                        COALESCE(SUM(pay.amount), 0) as total_revenue 
                      FROM properties p
                      LEFT JOIN units u ON p.id = u.property_id
                      LEFT JOIN leases l ON u.id = l.unit_id
                      LEFT JOIN invoices i ON l.id = i.lease_id
                      LEFT JOIN payments pay ON i.id = pay.invoice_id AND pay.status = 'verified'
                      GROUP BY p.id
                      HAVING total_revenue > 0
                      ORDER BY total_revenue DESC
                      LIMIT 5";
$result_top_properties = $conn->query($sql_top_properties);

// Upcoming Lease Expirations
$sql_upcoming_expirations = "SELECT 
                             l.id as lease_id,
                             l.end_date,
                             p.name as property_name,
                             u.unit_number,
                             t.first_name,
                             t.last_name,
                             t.email,
                             t.phone
                           FROM leases l
                           JOIN units u ON l.unit_id = u.id
                           JOIN properties p ON u.property_id = p.id
                           JOIN users t ON l.tenant_id = t.id
                           WHERE l.status = 'active'
                           AND l.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)
                           ORDER BY l.end_date ASC
                           LIMIT 5";
$result_upcoming_expirations = $conn->query($sql_upcoming_expirations);

// Recent Payments
$sql_recent_payments = "SELECT p.*, i.invoice_number, i.type as invoice_type,
                       payer.first_name, payer.last_name, payer.email
                       FROM payments p
                       LEFT JOIN invoices i ON p.invoice_id = i.id
                       LEFT JOIN users payer ON p.payer_id = payer.id
                       ORDER BY p.payment_date DESC
                       LIMIT 10";
$result_recent_payments = $conn->query($sql_recent_payments);

// Invoice Status Summary
$sql_invoice_summary = "SELECT 
                        status,
                        COUNT(*) as count,
                        COALESCE(SUM(total_amount), 0) as total_amount,
                        COALESCE(SUM(balance), 0) as total_balance
                        FROM invoices
                        GROUP BY status";
$result_invoice_summary = $conn->query($sql_invoice_summary);
$invoice_summary_data = [];
while($row = $result_invoice_summary->fetch_assoc()) {
    $invoice_summary_data[] = $row;
}

// Landlords Count
$sql_landlords = "SELECT COUNT(*) as total FROM users WHERE role_id = 2 AND status = 'active'";
$result_landlords = $conn->query($sql_landlords);
$total_landlords = $result_landlords->fetch_assoc()['total'] ?? 0;

// Property Managers Count
$sql_managers = "SELECT COUNT(*) as total FROM users WHERE role_id = 3 AND status = 'active'";
$result_managers = $conn->query($sql_managers);
$total_managers = $result_managers->fetch_assoc()['total'] ?? 0;

// Helper Functions
function getPropertyTypeColor($type) {
    $colors = [
        'Apartment Complex' => 'primary',
        'Commercial Tower' => 'success',
        'Gated Community' => 'info',
        'Mall' => 'warning',
        'Single Unit' => 'danger',
        'Land' => 'secondary'
    ];
    return $colors[$type] ?? 'dark';
}

function getUnitStatusColor($status) {
    $colors = [
        'vacant' => 'danger',
        'occupied' => 'success',
        'maintained' => 'warning',
        'reserved' => 'info',
        'sold' => 'secondary'
    ];
    return $colors[$status] ?? 'secondary';
}

function getMaintenanceStatusColor($status) {
    $colors = [
        'Open' => 'info',
        'Assigned' => 'warning',
        'In Progress' => 'primary',
        'On Hold' => 'secondary',
        'Completed' => 'success',
        'Cancelled' => 'danger'
    ];
    return $colors[$status] ?? 'secondary';
}

function getInvoiceStatusColor($status) {
    $colors = [
        'unpaid' => 'warning',
        'partial' => 'info',
        'paid' => 'success',
        'overdue' => 'danger',
        'void' => 'secondary'
    ];
    return $colors[$status] ?? 'secondary';
}
?>
<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Admin Dashboard')); ?>
    <?php include 'layouts/head-css.php'; ?>
    
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/apexcharts"></script>
    
    <style>
        .stats-card {
            position: relative;
            overflow: hidden;
            transition: all 0.3s ease;
        }
        .stats-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 8px 25px rgba(0,0,0,0.1);
        }
        .chart-container {
            position: relative;
            height: 300px;
        }
        .small-chart-container {
            position: relative;
            height: 220px;
        }
        .activity-list {
            max-height: 400px;
            overflow-y: auto;
        }
        .activity-list::-webkit-scrollbar {
            width: 6px;
        }
        .activity-list::-webkit-scrollbar-thumb {
            background: #888;
            border-radius: 3px;
        }
        .quick-link-card {
            transition: all 0.3s ease;
            cursor: pointer;
            border: 1px solid #e9ecef;
        }
        .quick-link-card:hover {
            transform: translateY(-3px);
            box-shadow: 0 5px 15px rgba(0,0,0,0.1);
            border-color: #007bff;
        }
        .metric-badge {
            position: absolute;
            top: 10px;
            right: 10px;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>

        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <?php includeFileWithVariables('layouts/page-title.php', array('pagetitle' => 'Dashboard', 'title' => 'Admin Dashboard')); ?>

                    <!-- Welcome Card -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center justify-content-between">
                                        <div>
                                            <h5 class="mb-2">Welcome back, <?php echo htmlspecialchars($full_name); ?>!</h5>
                                            <p class="text-muted mb-0">Here's your property portfolio overview and key metrics.</p>
                                        </div>
                                        <div class="dropdown">
                                            <button class="btn btn-primary dropdown-toggle" type="button" data-bs-toggle="dropdown">
                                                <i class="ri-add-line me-1"></i> Quick Actions
                                            </button>
                                            <ul class="dropdown-menu">
                                                <li><a class="dropdown-item" href="add_property.php"><i class="ri-building-line me-2"></i> Add Property</a></li>
                                                <li><a class="dropdown-item" href="add_unit.php"><i class="ri-home-4-line me-2"></i> Add Unit</a></li>
                                                <li><a class="dropdown-item" href="add_tenant.php"><i class="ri-user-add-line me-2"></i> Add Tenant</a></li>
                                                <li><a class="dropdown-item" href="create_lease.php"><i class="ri-file-text-line me-2"></i> Create Lease</a></li>
                                            </ul>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Key Metrics Row 1 -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Total Properties</p>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-soft-primary rounded-circle fs-3">
                                                <i class="ri-building-line text-primary"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="mt-4">
                                        <h4 class="fs-22 fw-semibold mb-2"><?php echo number_format($total_properties); ?></h4>
                                        <p class="text-muted mb-0">
                                            <span class="badge bg-soft-success text-success">
                                                <?php echo number_format($total_units); ?> Total Units
                                            </span>
                                        </p>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Occupancy Rate</p>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-soft-success rounded-circle fs-3">
                                                <i class="ri-percent-line text-success"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="mt-4">
                                        <h4 class="fs-22 fw-semibold mb-2"><?php echo number_format($occupancy_rate, 1); ?>%</h4>
                                        <p class="text-muted mb-0">
                                            <span class="text-success"><?php echo $occupied_units; ?> Occupied</span> / 
                                            <span class="text-danger"><?php echo $vacant_units; ?> Vacant</span>
                                        </p>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Rent Collection</p>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-soft-info rounded-circle fs-3">
                                                <i class="ri-money-dollar-circle-line text-info"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="mt-4">
                                        <h4 class="fs-22 fw-semibold mb-2">KSh <?php echo number_format($collected_rent); ?></h4>
                                        <p class="text-muted mb-0">
                                            <span class="badge bg-soft-info text-info">
                                                <?php echo number_format($collection_efficiency, 1); ?>% Efficiency
                                            </span>
                                        </p>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Outstanding Balance</p>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-soft-warning rounded-circle fs-3">
                                                <i class="ri-alert-line text-warning"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="mt-4">
                                        <h4 class="fs-22 fw-semibold mb-2">KSh <?php echo number_format($outstanding_balance); ?></h4>
                                        <p class="text-muted mb-0">
                                            <span class="text-warning">Unpaid invoices</span>
                                        </p>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Key Metrics Row 2 -->
                    <div class="row">
                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Active Tenants</p>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-soft-primary rounded-circle fs-3">
                                                <i class="ri-user-3-line text-primary"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="mt-4">
                                        <h4 class="fs-22 fw-semibold mb-2"><?php echo number_format($total_tenants); ?></h4>
                                        <p class="text-muted mb-0">
                                            <span class="badge bg-soft-success text-success">
                                                <?php echo $active_leases; ?> Active Leases
                                            </span>
                                        </p>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Expiring Leases</p>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-soft-danger rounded-circle fs-3">
                                                <i class="ri-calendar-event-line text-danger"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="mt-4">
                                        <h4 class="fs-22 fw-semibold mb-2"><?php echo number_format($expiring_leases); ?></h4>
                                        <p class="text-muted mb-0">Within 30 days</p>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Maintenance Requests</p>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-soft-warning rounded-circle fs-3">
                                                <i class="ri-tools-line text-warning"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="mt-4">
                                        <h4 class="fs-22 fw-semibold mb-2"><?php echo number_format($pending_maintenance); ?></h4>
                                        <p class="text-muted mb-0">Pending resolution</p>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-3 col-md-6">
                            <div class="card stats-card">
                                <div class="card-body">
                                    <div class="d-flex align-items-center">
                                        <div class="flex-grow-1">
                                            <p class="text-uppercase fw-medium text-muted mb-0">Landlords/Managers</p>
                                        </div>
                                        <div class="avatar-sm">
                                            <span class="avatar-title bg-soft-secondary rounded-circle fs-3">
                                                <i class="ri-group-line text-secondary"></i>
                                            </span>
                                        </div>
                                    </div>
                                    <div class="mt-4">
                                        <h4 class="fs-22 fw-semibold mb-2"><?php echo number_format($total_landlords + $total_managers); ?></h4>
                                        <p class="text-muted mb-0">
                                            <?php echo $total_landlords; ?> Landlords / <?php echo $total_managers; ?> Managers
                                        </p>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Charts Row -->
                    <div class="row">
                        <div class="col-xl-8">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Revenue Trend (Last 6 Months)</h4>
                                </div>
                                <div class="card-body">
                                    <div class="chart-container">
                                        <canvas id="revenueChart"></canvas>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-4">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Property Types</h4>
                                </div>
                                <div class="card-body">
                                    <div class="small-chart-container">
                                        <canvas id="propertyTypesChart"></canvas>
                                    </div>
                                    <div class="mt-3">
                                        <?php foreach($property_types_data as $type): ?>
                                        <div class="d-flex justify-content-between align-items-center mb-2">
                                            <div>
                                                <i class="ri-checkbox-blank-circle-fill text-<?php echo getPropertyTypeColor($type['type']); ?> me-2"></i>
                                                <span><?php echo $type['type']; ?></span>
                                            </div>
                                            <span class="fw-semibold"><?php echo $type['count']; ?></span>
                                        </div>
                                        <?php endforeach; ?>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Status Charts -->
                    <div class="row">
                        <div class="col-xl-4">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Unit Status</h4>
                                </div>
                                <div class="card-body">
                                    <div class="small-chart-container">
                                        <canvas id="unitStatusChart"></canvas>
                                    </div>
                                    <div class="mt-3">
                                        <?php foreach($unit_status_data as $status): ?>
                                        <div class="d-flex justify-content-between align-items-center mb-2">
                                            <div>
                                                <i class="ri-checkbox-blank-circle-fill text-<?php echo getUnitStatusColor($status['status']); ?> me-2"></i>
                                                <span><?php echo ucfirst($status['status']); ?></span>
                                            </div>
                                            <span class="fw-semibold"><?php echo $status['count']; ?></span>
                                        </div>
                                        <?php endforeach; ?>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-4">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Maintenance Status</h4>
                                </div>
                                <div class="card-body">
                                    <div class="small-chart-container">
                                        <canvas id="maintenanceChart"></canvas>
                                    </div>
                                    <div class="mt-3">
                                        <?php foreach($maintenance_status_data as $status): ?>
                                        <div class="d-flex justify-content-between align-items-center mb-2">
                                            <div>
                                                <i class="ri-checkbox-blank-circle-fill text-<?php echo getMaintenanceStatusColor($status['status']); ?> me-2"></i>
                                                <span><?php echo $status['status']; ?></span>
                                            </div>
                                            <span class="fw-semibold"><?php echo $status['count']; ?></span>
                                        </div>
                                        <?php endforeach; ?>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-4">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Invoice Summary</h4>
                                </div>
                                <div class="card-body">
                                    <div class="small-chart-container">
                                        <canvas id="invoiceChart"></canvas>
                                    </div>
                                    <div class="mt-3">
                                        <?php foreach($invoice_summary_data as $invoice): ?>
                                        <div class="d-flex justify-content-between align-items-center mb-2">
                                            <div>
                                                <i class="ri-checkbox-blank-circle-fill text-<?php echo getInvoiceStatusColor($invoice['status']); ?> me-2"></i>
                                                <span><?php echo ucfirst($invoice['status']); ?></span>
                                            </div>
                                            <span class="fw-semibold"><?php echo $invoice['count']; ?></span>
                                        </div>
                                        <?php endforeach; ?>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Tables Row -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Top Properties by Revenue</h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive activity-list">
                                        <table class="table table-hover align-middle mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Property</th>
                                                    <th>Location</th>
                                                    <th>Units</th>
                                                    <th>Revenue</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php 
                                                if($result_top_properties && $result_top_properties->num_rows > 0):
                                                    while($property = $result_top_properties->fetch_assoc()): 
                                                ?>
                                                <tr>
                                                    <td>
                                                        <div class="d-flex align-items-center">
                                                            <div class="avatar-xs me-2">
                                                                <span class="avatar-title rounded-circle bg-soft-primary text-primary">
                                                                    <i class="ri-building-line"></i>
                                                                </span>
                                                            </div>
                                                            <h5 class="fs-14 mb-0"><?php echo htmlspecialchars($property['property_name']); ?></h5>
                                                        </div>
                                                    </td>
                                                    <td><?php echo htmlspecialchars($property['location_area']); ?></td>
                                                    <td><?php echo $property['unit_count']; ?></td>
                                                    <td class="fw-semibold">KSh <?php echo number_format($property['total_revenue']); ?></td>
                                                </tr>
                                                <?php 
                                                    endwhile;
                                                else:
                                                ?>
                                                <tr>
                                                    <td colspan="4" class="text-center text-muted">No revenue data available</td>
                                                </tr>
                                                <?php endif; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Recent Maintenance Requests</h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive activity-list">
                                        <table class="table table-hover align-middle mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Property/Unit</th>
                                                    <th>Requester</th>
                                                    <th>Priority</th>
                                                    <th>Status</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php 
                                                if($result_recent_maintenance && $result_recent_maintenance->num_rows > 0):
                                                    while($request = $result_recent_maintenance->fetch_assoc()): 
                                                ?>
                                                <tr>
                                                    <td>
                                                        <div>
                                                            <h5 class="fs-14 mb-0"><?php echo htmlspecialchars($request['property_name']); ?></h5>
                                                            <p class="text-muted mb-0">Unit: <?php echo $request['unit_number']; ?></p>
                                                        </div>
                                                    </td>
                                                    <td><?php echo htmlspecialchars($request['first_name'] . ' ' . $request['last_name']); ?></td>
                                                    <td>
                                                        <span class="badge bg-soft-<?php echo $request['priority'] == 'Emergency' ? 'danger' : ($request['priority'] == 'High' ? 'warning' : 'info'); ?>">
                                                            <?php echo $request['priority']; ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-soft-<?php echo getMaintenanceStatusColor($request['status']); ?> text-<?php echo getMaintenanceStatusColor($request['status']); ?>">
                                                            <?php echo $request['status']; ?>
                                                        </span>
                                                    </td>
                                                </tr>
                                                <?php 
                                                    endwhile;
                                                else:
                                                ?>
                                                <tr>
                                                    <td colspan="4" class="text-center text-muted">No maintenance requests</td>
                                                </tr>
                                                <?php endif; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Upcoming Expirations & Recent Payments -->
                    <div class="row">
                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Upcoming Lease Expirations</h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive">
                                        <table class="table table-hover align-middle mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Tenant</th>
                                                    <th>Property/Unit</th>
                                                    <th>Expiry Date</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php 
                                                if($result_upcoming_expirations && $result_upcoming_expirations->num_rows > 0):
                                                    while($lease = $result_upcoming_expirations->fetch_assoc()): 
                                                        $exp_date = strtotime($lease['end_date']);
                                                        $days_left = round(($exp_date - time()) / (60 * 60 * 24));
                                                ?>
                                                <tr>
                                                    <td>
                                                        <div class="d-flex align-items-center">
                                                            <div class="avatar-xs me-2">
                                                                <span class="avatar-title rounded-circle bg-soft-success text-success">
                                                                    <?php echo strtoupper(substr($lease['first_name'], 0, 1) . substr($lease['last_name'], 0, 1)); ?>
                                                                </span>
                                                            </div>
                                                            <div>
                                                                <?php echo htmlspecialchars($lease['first_name'] . ' ' . $lease['last_name']); ?>
                                                            </div>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <div>
                                                            <h5 class="fs-14 mb-0"><?php echo htmlspecialchars($lease['property_name']); ?></h5>
                                                            <p class="text-muted mb-0">Unit: <?php echo $lease['unit_number']; ?></p>
                                                        </div>
                                                    </td>
                                                    <td>
                                                        <span class="badge bg-soft-<?php echo $days_left <= 15 ? 'danger' : 'warning'; ?>">
                                                            <?php echo date('M d, Y', $exp_date); ?>
                                                            (<?php echo $days_left; ?> days)
                                                        </span>
                                                    </td>
                                                </tr>
                                                <?php 
                                                    endwhile;
                                                else:
                                                ?>
                                                <tr>
                                                    <td colspan="3" class="text-center text-muted">No upcoming expirations</td>
                                                </tr>
                                                <?php endif; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>

                        <div class="col-xl-6">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Recent Payments</h4>
                                </div>
                                <div class="card-body">
                                    <div class="table-responsive activity-list">
                                        <table class="table table-hover align-middle mb-0">
                                            <thead>
                                                <tr>
                                                    <th>Payer</th>
                                                    <th>Invoice</th>
                                                    <th>Amount</th>
                                                    <th>Date</th>
                                                </tr>
                                            </thead>
                                            <tbody>
                                                <?php 
                                                if($result_recent_payments && $result_recent_payments->num_rows > 0):
                                                    while($payment = $result_recent_payments->fetch_assoc()): 
                                                ?>
                                                <tr>
                                                    <td><?php echo htmlspecialchars($payment['first_name'] . ' ' . $payment['last_name']); ?></td>
                                                    <td>
                                                        <div>
                                                            <span class="fw-medium"><?php echo $payment['invoice_number']; ?></span>
                                                            <p class="text-muted mb-0"><?php echo $payment['invoice_type']; ?></p>
                                                        </div>
                                                    </td>
                                                    <td class="fw-semibold">KSh <?php echo number_format($payment['amount']); ?></td>
                                                    <td><?php echo date('M d, Y', strtotime($payment['payment_date'])); ?></td>
                                                </tr>
                                                <?php 
                                                    endwhile;
                                                else:
                                                ?>
                                                <tr>
                                                    <td colspan="4" class="text-center text-muted">No recent payments</td>
                                                </tr>
                                                <?php endif; ?>
                                            </tbody>
                                        </table>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Quick Access -->
                    <div class="row">
                        <div class="col-12">
                            <div class="card">
                                <div class="card-header">
                                    <h4 class="card-title mb-0">Quick Access</h4>
                                </div>
                                <div class="card-body">
                                    <div class="row g-3">
                                        <div class="col-xxl-2 col-lg-3 col-md-4 col-6">
                                            <div class="card mb-0 quick-link-card" onclick="window.location.href='view_properties.php'">
                                                <div class="card-body text-center py-3">
                                                    <div class="avatar-sm mx-auto mb-2">
                                                        <span class="avatar-title rounded-circle bg-soft-primary text-primary fs-3">
                                                            <i class="ri-building-line"></i>
                                                        </span>
                                                    </div>
                                                    <h5 class="fs-14 mb-0">Properties</h5>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-xxl-2 col-lg-3 col-md-4 col-6">
                                            <div class="card mb-0 quick-link-card" onclick="window.location.href='view_units.php'">
                                                <div class="card-body text-center py-3">
                                                    <div class="avatar-sm mx-auto mb-2">
                                                        <span class="avatar-title rounded-circle bg-soft-success text-success fs-3">
                                                            <i class="ri-home-4-line"></i>
                                                        </span>
                                                    </div>
                                                    <h5 class="fs-14 mb-0">Units</h5>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-xxl-2 col-lg-3 col-md-4 col-6">
                                            <div class="card mb-0 quick-link-card" onclick="window.location.href='view_tenants.php'">
                                                <div class="card-body text-center py-3">
                                                    <div class="avatar-sm mx-auto mb-2">
                                                        <span class="avatar-title rounded-circle bg-soft-info text-info fs-3">
                                                            <i class="ri-user-3-line"></i>
                                                        </span>
                                                    </div>
                                                    <h5 class="fs-14 mb-0">Tenants</h5>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-xxl-2 col-lg-3 col-md-4 col-6">
                                            <div class="card mb-0 quick-link-card" onclick="window.location.href='view_leases.php'">
                                                <div class="card-body text-center py-3">
                                                    <div class="avatar-sm mx-auto mb-2">
                                                        <span class="avatar-title rounded-circle bg-soft-warning text-warning fs-3">
                                                            <i class="ri-file-text-line"></i>
                                                        </span>
                                                    </div>
                                                    <h5 class="fs-14 mb-0">Leases</h5>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-xxl-2 col-lg-3 col-md-4 col-6">
                                            <div class="card mb-0 quick-link-card" onclick="window.location.href='invoices.php'">
                                                <div class="card-body text-center py-3">
                                                    <div class="avatar-sm mx-auto mb-2">
                                                        <span class="avatar-title rounded-circle bg-soft-danger text-danger fs-3">
                                                            <i class="ri-file-list-line"></i>
                                                        </span>
                                                    </div>
                                                    <h5 class="fs-14 mb-0">Invoices</h5>
                                                </div>
                                            </div>
                                        </div>
                                        <div class="col-xxl-2 col-lg-3 col-md-4 col-6">
                                            <div class="card mb-0 quick-link-card" onclick="window.location.href='reports.php'">
                                                <div class="card-body text-center py-3">
                                                    <div class="avatar-sm mx-auto mb-2">
                                                        <span class="avatar-title rounded-circle bg-soft-secondary text-secondary fs-3">
                                                            <i class="ri-bar-chart-line"></i>
                                                        </span>
                                                    </div>
                                                    <h5 class="fs-14 mb-0">Reports</h5>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                </div>
            </div>

            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="assets/js/app.js"></script>

    <script>
    // Revenue Chart
    const revenueCtx = document.getElementById('revenueChart').getContext('2d');
    new Chart(revenueCtx, {
        type: 'line',
        data: {
            labels: <?php echo json_encode(array_column($revenue_trend_data, 'month_year')); ?>,
            datasets: [{
                label: 'Revenue (KSh)',
                data: <?php echo json_encode(array_column($revenue_trend_data, 'revenue')); ?>,
                borderColor: 'rgb(75, 192, 192)',
                backgroundColor: 'rgba(75, 192, 192, 0.2)',
                borderWidth: 2,
                tension: 0.4,
                fill: true
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: { position: 'top' },
                tooltip: {
                    callbacks: {
                        label: function(context) {
                            return 'KSh ' + context.parsed.y.toLocaleString();
                        }
                    }
                }
            },
            scales: {
                y: {
                    beginAtZero: true,
                    ticks: {
                        callback: function(value) {
                            return 'KSh ' + value.toLocaleString();
                        }
                    }
                }
            }
        }
    });

    // Property Types Chart
    const propertyTypesCtx = document.getElementById('propertyTypesChart').getContext('2d');
    new Chart(propertyTypesCtx, {
        type: 'doughnut',
        data: {
            labels: <?php echo json_encode(array_column($property_types_data, 'type')); ?>,
            datasets: [{
                data: <?php echo json_encode(array_column($property_types_data, 'count')); ?>,
                backgroundColor: ['#556ee6', '#34c38f', '#50a5f1', '#f1b44c', '#f46a6a', '#74788d'],
                borderWidth: 0
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: { legend: { display: false } },
            cutout: '60%'
        }
    });

    // Unit Status Chart
    const unitStatusCtx = document.getElementById('unitStatusChart').getContext('2d');
    new Chart(unitStatusCtx, {
        type: 'pie',
        data: {
            labels: <?php echo json_encode(array_column($unit_status_data, 'status')); ?>,
            datasets: [{
                data: <?php echo json_encode(array_column($unit_status_data, 'count')); ?>,
                backgroundColor: ['#f46a6a', '#34c38f', '#f1b44c', '#50a5f1', '#74788d'],
                borderWidth: 0
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: { legend: { display: false } }
        }
    });

    // Maintenance Chart
    const maintenanceCtx = document.getElementById('maintenanceChart').getContext('2d');
    new Chart(maintenanceCtx, {
        type: 'polarArea',
        data: {
            labels: <?php echo json_encode(array_column($maintenance_status_data, 'status')); ?>,
            datasets: [{
                data: <?php echo json_encode(array_column($maintenance_status_data, 'count')); ?>,
                backgroundColor: ['#50a5f1', '#f1b44c', '#556ee6', '#74788d', '#34c38f', '#f46a6a'],
                borderWidth: 0
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: { legend: { display: false } }
        }
    });

    // Invoice Chart
    const invoiceCtx = document.getElementById('invoiceChart').getContext('2d');
    new Chart(invoiceCtx, {
        type: 'bar',
        data: {
            labels: <?php echo json_encode(array_column($invoice_summary_data, 'status')); ?>,
            datasets: [{
                label: 'Invoices',
                data: <?php echo json_encode(array_column($invoice_summary_data, 'count')); ?>,
                backgroundColor: ['#f1b44c', '#50a5f1', '#34c38f', '#f46a6a', '#74788d'],
                borderWidth: 0
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: { legend: { display: false } },
            scales: { y: { beginAtZero: true } }
        }
    });
    </script>
</body>
</html>