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

// Set timezone to East Africa Time
date_default_timezone_set('Africa/Nairobi');

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

// Fetch user details
$user_id = $_SESSION['user_id'];
$sql_user_details = "SELECT u.*, ur.role_name 
                     FROM users u 
                     INNER JOIN user_roles ur ON u.role_id = ur.role_id 
                     WHERE u.user_id = ?";
$stmt_user = $conn->prepare($sql_user_details);
$stmt_user->bind_param("i", $user_id);
$stmt_user->execute();
$user_details = $stmt_user->get_result()->fetch_assoc();

if ($user_details) {
    $username = $user_details['username'];
    $role_name = $user_details['role_name'];
    $full_name = $user_details['full_name'];
    $email = $user_details['email'];
}

// Get date filters
$period_filter = $_GET['period'] ?? 'last_30_days';
$category_filter = $_GET['category_filter'] ?? '';

// Calculate date ranges
$today = date('Y-m-d');
switch ($period_filter) {
    case 'last_7_days':
        $start_date = date('Y-m-d', strtotime('-7 days'));
        break;
    case 'last_30_days':
        $start_date = date('Y-m-d', strtotime('-30 days'));
        break;
    case 'last_90_days':
        $start_date = date('Y-m-d', strtotime('-90 days'));
        break;
    case 'last_year':
        $start_date = date('Y-m-d', strtotime('-1 year'));
        break;
    case 'this_month':
        $start_date = date('Y-m-01');
        break;
    case 'this_year':
        $start_date = date('Y-01-01');
        break;
    default:
        $start_date = date('Y-m-d', strtotime('-30 days'));
}

// Build category filter condition
$category_condition = "";
$category_params = [];
if (!empty($category_filter)) {
    $category_condition = " AND f.category_id = ?";
    $category_params[] = $category_filter;
}

// Get overall fund performance metrics
$performance_query = "SELECT 
    COUNT(DISTINCT f.fund_id) as total_funds,
    SUM(f.current_balance) as total_balance,
    SUM(f.total_contributions) as total_contributions,
    SUM(f.total_withdrawals) as total_withdrawals,
    AVG(f.current_balance) as avg_fund_balance,
    COUNT(DISTINCT ft.contributor_id) as total_contributors,
    COUNT(ft.transaction_id) as total_transactions,
    SUM(CASE WHEN ft.transaction_type = 'CONTRIBUTION' AND ft.transaction_date >= ? THEN ft.amount ELSE 0 END) as period_contributions,
    SUM(CASE WHEN ft.transaction_type = 'WITHDRAWAL' AND ft.transaction_date >= ? THEN ft.amount ELSE 0 END) as period_withdrawals
    FROM funds f 
    LEFT JOIN fund_transactions ft ON f.fund_id = ft.fund_id AND ft.status = 'COMPLETED'
    WHERE f.is_active = 1" . $category_condition;

$stmt = $conn->prepare($performance_query);
$params = [$start_date, $start_date];
if (!empty($category_params)) {
    $params = array_merge($params, $category_params);
}
$stmt->bind_param(str_repeat('s', count($params)), ...$params);
$stmt->execute();
$overall_performance = $stmt->get_result()->fetch_assoc();

// Get fund performance by category
$category_performance_query = "SELECT 
    fc.category_name,
    fc.color_code,
    COUNT(DISTINCT f.fund_id) as fund_count,
    SUM(f.current_balance) as category_balance,
    SUM(f.total_contributions) as category_contributions,
    AVG(f.current_balance) as avg_balance,
    SUM(CASE WHEN ft.transaction_type = 'CONTRIBUTION' AND ft.transaction_date >= ? THEN ft.amount ELSE 0 END) as period_contributions,
    SUM(CASE WHEN ft.transaction_type = 'WITHDRAWAL' AND ft.transaction_date >= ? THEN ft.amount ELSE 0 END) as period_withdrawals
    FROM fund_categories fc
    LEFT JOIN funds f ON fc.category_id = f.category_id AND f.is_active = 1
    LEFT JOIN fund_transactions ft ON f.fund_id = ft.fund_id AND ft.status = 'COMPLETED'
    WHERE fc.is_active = 1" . $category_condition . "
    GROUP BY fc.category_id, fc.category_name, fc.color_code
    HAVING fund_count > 0
    ORDER BY category_balance DESC";

$stmt = $conn->prepare($category_performance_query);
$params = [$start_date, $start_date];
if (!empty($category_params)) {
    $params = array_merge($params, $category_params);
}
$stmt->bind_param(str_repeat('s', count($params)), ...$params);
$stmt->execute();
$category_performance = $stmt->get_result();

// Get top performing funds
$top_funds_query = "SELECT 
    f.fund_name,
    f.fund_code,
    fc.category_name,
    fc.color_code,
    f.current_balance,
    f.total_contributions,
    f.total_withdrawals,
    f.target_amount,
    u.full_name as manager_name,
    COUNT(DISTINCT ft.contributor_id) as contributor_count,
    SUM(CASE WHEN ft.transaction_type = 'CONTRIBUTION' AND ft.transaction_date >= ? THEN ft.amount ELSE 0 END) as period_contributions,
    CASE 
        WHEN f.target_amount > 0 THEN (f.current_balance / f.target_amount) * 100 
        ELSE 0 
    END as target_progress
    FROM funds f
    LEFT JOIN fund_categories fc ON f.category_id = fc.category_id
    LEFT JOIN users u ON f.fund_manager = u.user_id
    LEFT JOIN fund_transactions ft ON f.fund_id = ft.fund_id AND ft.status = 'COMPLETED'
    WHERE f.is_active = 1" . $category_condition . "
    GROUP BY f.fund_id
    ORDER BY f.current_balance DESC
    LIMIT 10";

$stmt = $conn->prepare($top_funds_query);
$params = [$start_date];
if (!empty($category_params)) {
    $params = array_merge($params, $category_params);
}
$stmt->bind_param(str_repeat('s', count($params)), ...$params);
$stmt->execute();
$top_funds = $stmt->get_result();

// Get recent activity
$recent_activity_query = "SELECT 
    ft.transaction_date,
    ft.transaction_type,
    ft.amount,
    f.fund_name,
    CONCAT(c.first_name, ' ', c.last_name) as contributor_name,
    ft.description
    FROM fund_transactions ft
    JOIN funds f ON ft.fund_id = f.fund_id
    JOIN contributors c ON ft.contributor_id = c.contributor_id
    WHERE ft.status = 'COMPLETED' AND ft.transaction_date >= ?" . $category_condition . "
    ORDER BY ft.transaction_date DESC, ft.created_at DESC
    LIMIT 15";

$stmt = $conn->prepare($recent_activity_query);
$params = [$start_date];
if (!empty($category_params)) {
    $params = array_merge($params, $category_params);
}
$stmt->bind_param(str_repeat('s', count($params)), ...$params);
$stmt->execute();
$recent_activity = $stmt->get_result();

// Get monthly trends for chart
$monthly_trends_query = "SELECT 
    DATE_FORMAT(ft.transaction_date, '%Y-%m') as month,
    SUM(CASE WHEN ft.transaction_type = 'CONTRIBUTION' THEN ft.amount ELSE 0 END) as contributions,
    SUM(CASE WHEN ft.transaction_type = 'WITHDRAWAL' THEN ft.amount ELSE 0 END) as withdrawals,
    COUNT(DISTINCT ft.contributor_id) as active_contributors
    FROM fund_transactions ft
    JOIN funds f ON ft.fund_id = f.fund_id
    WHERE ft.status = 'COMPLETED' 
    AND ft.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)" . $category_condition . "
    GROUP BY DATE_FORMAT(ft.transaction_date, '%Y-%m')
    ORDER BY month DESC
    LIMIT 12";

$stmt = $conn->prepare($monthly_trends_query);
if (!empty($category_params)) {
    $stmt->bind_param('i', $category_filter);
}
$stmt->execute();
$monthly_trends = $stmt->get_result();

// Get fund categories for filter
$categories_query = "SELECT category_id, category_name FROM fund_categories WHERE is_active = 1 ORDER BY category_name";
$categories_result = $conn->query($categories_query);

// Calculate growth rates
$net_flow = ($overall_performance['period_contributions'] ?? 0) - ($overall_performance['period_withdrawals'] ?? 0);
$growth_rate = 0;
if ($overall_performance['total_contributions'] > 0) {
    $growth_rate = (($overall_performance['period_contributions'] ?? 0) / max($overall_performance['total_contributions'], 1)) * 100;
}
?>

<?php include 'layouts/main.php'; ?>

<head>
    <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Fund Performance Overview - Personal Fund Tracker')); ?>
    <?php include 'layouts/head-css.php'; ?>
    <!-- Bootstrap CSS -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    
    <style>
        /* Use the same styling from list_consultations.php */
        body {
            background-color: #f8fafc;
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
        }
        
        .page-content {
            padding: 1.5rem;
        }
        
        /* Page Header */
        .page-header {
            background: linear-gradient(135deg, #059669 0%, #10b981 100%);
            color: white;
            border-radius: 12px;
            padding: 2rem;
            margin-bottom: 2rem;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1);
        }
        
        .page-title {
            font-size: 1.75rem;
            font-weight: 600;
            margin-bottom: 0.5rem;
        }
        
        .page-subtitle {
            opacity: 0.9;
            margin: 0;
        }
        
        /* Filter Card */
        .filter-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            margin-bottom: 2rem;
        }
        
        /* Stats Cards */
        .stats-row {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 1.5rem;
            margin-bottom: 2rem;
        }
        
        .stat-card {
            background: white;
            border-radius: 12px;
            padding: 1.5rem;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            text-align: center;
            position: relative;
        }
        
        .stat-card.positive {
            border-left: 4px solid #059669;
        }
        
        .stat-card.negative {
            border-left: 4px solid #dc2626;
        }
        
        .stat-value {
            font-size: 2rem;
            font-weight: 700;
            color: #1e293b;
            margin-bottom: 0.5rem;
        }
        
        .stat-label {
            font-size: 0.875rem;
            color: #64748b;
            text-transform: uppercase;
            letter-spacing: 0.05em;
        }
        
        .stat-change {
            font-size: 0.75rem;
            margin-top: 0.5rem;
        }
        
        .stat-change.positive {
            color: #059669;
        }
        
        .stat-change.negative {
            color: #dc2626;
        }
        
        /* Chart Cards */
        .chart-card {
            background: white;
            border-radius: 12px;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            overflow: hidden;
            margin-bottom: 2rem;
        }
        
        .chart-header {
            padding: 1.5rem;
            border-bottom: 1px solid #e2e8f0;
            background: #f8fafc;
        }
        
        .chart-title {
            font-size: 1.125rem;
            font-weight: 600;
            color: #1e293b;
            margin: 0;
        }
        
        .chart-body {
            padding: 1.5rem;
        }
        
        /* Table Card */
        .table-card {
            background: white;
            border-radius: 12px;
            box-shadow: 0 1px 3px 0 rgba(0, 0, 0, 0.1);
            border: 1px solid #e2e8f0;
            overflow: hidden;
            margin-bottom: 2rem;
        }
        
        .table-header {
            padding: 1.5rem;
            border-bottom: 1px solid #e2e8f0;
            background: #f8fafc;
            display: flex;
            justify-content: space-between;
            align-items: center;
        }
        
        .table-title {
            font-size: 1.125rem;
            font-weight: 600;
            color: #1e293b;
            margin: 0;
        }
        
        .data-table {
            width: 100%;
            border-collapse: collapse;
            margin: 0;
        }
        
        .data-table th {
            background: #f8fafc;
            padding: 1rem;
            text-align: left;
            font-weight: 600;
            font-size: 0.875rem;
            color: #64748b;
            border-bottom: 1px solid #e2e8f0;
        }
        
        .data-table td {
            padding: 1rem;
            border-bottom: 1px solid #f1f5f9;
            font-size: 0.875rem;
        }
        
        .data-table tbody tr:hover {
            background: #f8fafc;
        }
        
        /* Progress Bars */
        .progress-bar-custom {
            background: #f1f5f9;
            border-radius: 4px;
            height: 8px;
            overflow: hidden;
            margin-top: 0.5rem;
        }
        
        .progress-fill {
            height: 100%;
            background: #059669;
            transition: width 0.3s ease;
        }
        
        /* Category Badges */
        .category-badge {
            padding: 0.25rem 0.5rem;
            border-radius: 4px;
            font-size: 0.75rem;
            font-weight: 500;
            color: white;
        }
        
        /* Transaction Type Badges */
        .transaction-badge {
            padding: 0.25rem 0.75rem;
            border-radius: 9999px;
            font-size: 0.75rem;
            font-weight: 600;
            text-transform: uppercase;
            letter-spacing: 0.05em;
        }
        
        .transaction-contribution { background: #dcfce7; color: #166534; }
        .transaction-withdrawal { background: #fee2e2; color: #991b1b; }
        
        /* Grid Layout */
        .grid-2 {
            display: grid;
            grid-template-columns: 1fr 1fr;
            gap: 2rem;
        }
        
        .grid-3 {
            display: grid;
            grid-template-columns: 2fr 1fr;
            gap: 2rem;
        }
        
        /* Form Controls */
        .form-control, .form-select {
            border: 1px solid #e2e8f0;
            border-radius: 8px;
            padding: 0.75rem;
            font-size: 0.875rem;
        }
        
        .form-control:focus, .form-select:focus {
            border-color: #3b82f6;
            box-shadow: 0 0 0 3px rgba(59, 130, 246, 0.1);
        }
        
        .btn {
            padding: 0.75rem 1.5rem;
            border-radius: 8px;
            font-weight: 600;
            font-size: 0.875rem;
            border: none;
            cursor: pointer;
            transition: all 0.2s ease;
        }
        
        .btn-primary {
            background: #3b82f6;
            color: white;
        }
        
        .btn-primary:hover {
            background: #1d4ed8;
        }
        
        /* Responsive */
        @media (max-width: 768px) {
            .page-content {
                padding: 1rem;
            }
            
            .stats-row {
                grid-template-columns: 1fr;
            }
            
            .grid-2, .grid-3 {
                grid-template-columns: 1fr;
            }
            
            .table-header {
                flex-direction: column;
                gap: 1rem;
                align-items: stretch;
            }
        }
        
        /* Footer spacing fix */
        .main-content {
            padding-bottom: 2rem;
        }
        
        .page-content {
            margin-bottom: 3rem;
        }
    </style>
</head>

<body>
    <div id="layout-wrapper">
        <?php include 'layouts/menu.php'; ?>
        
        <div class="main-content">
            <div class="page-content">
                <div class="container-fluid">
                    <!-- Page Header -->
                    <div class="page-header">
                        <h1 class="page-title">Fund Performance Overview</h1>
                        <p class="page-subtitle">Analytics and insights for all fund performance</p>
                    </div>

                    <!-- Filter Card -->
                    <div class="filter-card">
                        <form method="GET" class="row g-3">
                            <div class="col-md-4">
                                <label class="form-label">Time Period</label>
                                <select class="form-select" name="period" onchange="this.form.submit()">
                                    <option value="last_7_days" <?php echo $period_filter == 'last_7_days' ? 'selected' : ''; ?>>Last 7 Days</option>
                                    <option value="last_30_days" <?php echo $period_filter == 'last_30_days' ? 'selected' : ''; ?>>Last 30 Days</option>
                                    <option value="last_90_days" <?php echo $period_filter == 'last_90_days' ? 'selected' : ''; ?>>Last 90 Days</option>
                                    <option value="this_month" <?php echo $period_filter == 'this_month' ? 'selected' : ''; ?>>This Month</option>
                                    <option value="this_year" <?php echo $period_filter == 'this_year' ? 'selected' : ''; ?>>This Year</option>
                                    <option value="last_year" <?php echo $period_filter == 'last_year' ? 'selected' : ''; ?>>Last Year</option>
                                </select>
                            </div>
                            <div class="col-md-4">
                                <label class="form-label">Category Filter</label>
                                <select class="form-select" name="category_filter" onchange="this.form.submit()">
                                    <option value="">All Categories</option>
                                    <?php while ($category = $categories_result->fetch_assoc()): ?>
                                        <option value="<?php echo $category['category_id']; ?>" <?php echo $category_filter == $category['category_id'] ? 'selected' : ''; ?>>
                                            <?php echo htmlspecialchars($category['category_name']); ?>
                                        </option>
                                    <?php endwhile; ?>
                                </select>
                            </div>
                            <div class="col-md-4">
                                <label class="form-label">&nbsp;</label>
                                <div>
                                    <a href="fund_performance_overview.php" class="btn btn-primary">Reset Filters</a>
                                </div>
                            </div>
                        </form>
                    </div>

                    <!-- Key Performance Stats -->
                    <div class="stats-row">
                        <div class="stat-card">
                            <div class="stat-value"><?php echo $overall_performance['total_funds'] ?? 0; ?></div>
                            <div class="stat-label">Active Funds</div>
                        </div>
                        <div class="stat-card <?php echo $net_flow >= 0 ? 'positive' : 'negative'; ?>">
                            <div class="stat-value">KES <?php echo number_format($overall_performance['total_balance'] ?? 0); ?></div>
                            <div class="stat-label">Total Fund Balance</div>
                            <div class="stat-change <?php echo $net_flow >= 0 ? 'positive' : 'negative'; ?>">
                                <?php echo $net_flow >= 0 ? '↗' : '↘'; ?> KES <?php echo number_format(abs($net_flow)); ?> this period
                            </div>
                        </div>
                        <div class="stat-card positive">
                            <div class="stat-value">KES <?php echo number_format($overall_performance['period_contributions'] ?? 0); ?></div>
                            <div class="stat-label">Period Contributions</div>
                            <div class="stat-change positive">
                                Growth: <?php echo number_format($growth_rate, 1); ?>%
                            </div>
                        </div>
                        <div class="stat-card">
                            <div class="stat-value"><?php echo $overall_performance['total_contributors'] ?? 0; ?></div>
                            <div class="stat-label">Active Contributors</div>
                        </div>
                        <div class="stat-card">
                            <div class="stat-value">KES <?php echo number_format($overall_performance['avg_fund_balance'] ?? 0); ?></div>
                            <div class="stat-label">Average Fund Size</div>
                        </div>
                    </div>

                    <!-- Charts Section -->
                    <div class="grid-3">
                        <!-- Monthly Trends Chart -->
                        <div class="chart-card">
                            <div class="chart-header">
                                <h3 class="chart-title">Monthly Trends</h3>
                            </div>
                            <div class="chart-body">
                                <canvas id="monthlyTrendsChart" height="300"></canvas>
                            </div>
                        </div>

                        <!-- Category Performance -->
                        <div class="chart-card">
                            <div class="chart-header">
                                <h3 class="chart-title">Performance by Category</h3>
                            </div>
                            <div class="chart-body">
                                <canvas id="categoryChart" height="300"></canvas>
                            </div>
                        </div>
                    </div>

                    <!-- Performance Tables -->
                    <div class="grid-2">
                        <!-- Top Performing Funds -->
                        <div class="table-card">
                            <div class="table-header">
                                <h3 class="table-title">Top Performing Funds</h3>
                            </div>
                            
                            <table class="data-table">
                                <thead>
                                    <tr>
                                        <th>Fund</th>
                                        <th>Category</th>
                                        <th>Balance</th>
                                        <th>Progress</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php while ($fund = $top_funds->fetch_assoc()): ?>
                                    <tr>
                                        <td>
                                            <div style="font-weight: 600; color: #1e293b;">
                                                <?php echo htmlspecialchars($fund['fund_name']); ?>
                                            </div>
                                            <div style="color: #64748b; font-size: 0.75rem;">
                                                <?php echo htmlspecialchars($fund['fund_code']); ?>
                                            </div>
                                        </td>
                                        <td>
                                            <span class="category-badge" style="background-color: <?php echo $fund['color_code'] ?? '#3b82f6'; ?>;">
                                                <?php echo htmlspecialchars($fund['category_name']); ?>
                                            </span>
                                        </td>
                                        <td>
                                            <div style="font-weight: 600; color: #1e293b;">
                                                KES <?php echo number_format($fund['current_balance']); ?>
                                            </div>
                                            <div style="color: #64748b; font-size: 0.75rem;">
                                                <?php echo $fund['contributor_count']; ?> contributors
                                            </div>
                                        </td>
                                        <td>
                                            <?php if ($fund['target_amount'] > 0): ?>
                                                <div style="font-size: 0.75rem; color: #64748b; margin-bottom: 0.25rem;">
                                                    <?php echo number_format($fund['target_progress'], 1); ?>% of target
                                                </div>
                                                <div class="progress-bar-custom">
                                                    <div class="progress-fill" style="width: <?php echo min($fund['target_progress'], 100); ?>%;"></div>
                                                </div>
                                            <?php else: ?>
                                                <span style="color: #64748b; font-size: 0.75rem;">No target set</span>
                                            <?php endif; ?>
                                        </td>
                                    </tr>
                                    <?php endwhile; ?>
                                </tbody>
                            </table>
                        </div>

                        <!-- Recent Activity -->
                        <div class="table-card">
                            <div class="table-header">
                                <h3 class="table-title">Recent Activity</h3>
                            </div>
                            
                            <table class="data-table">
                                <thead>
                                    <tr>
                                        <th>Date</th>
                                        <th>Type</th>
                                        <th>Amount</th>
                                        <th>Fund</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php while ($activity = $recent_activity->fetch_assoc()): ?>
                                    <tr>
                                        <td>
                                            <div style="font-weight: 500; color: #1e293b;">
                                                <?php echo date('M j', strtotime($activity['transaction_date'])); ?>
                                            </div>
                                            <div style="color: #64748b; font-size: 0.75rem;">
                                                <?php echo date('Y', strtotime($activity['transaction_date'])); ?>
                                            </div>
                                        </td>
                                        <td>
                                            <span class="transaction-badge transaction-<?php echo strtolower($activity['transaction_type']); ?>">
                                                <?php echo ucfirst(strtolower($activity['transaction_type'])); ?>
                                            </span>
                                        </td>
                                        <td>
                                            <div style="font-weight: 600; color: <?php echo $activity['transaction_type'] == 'CONTRIBUTION' ? '#059669' : '#dc2626'; ?>;">
                                                <?php echo $activity['transaction_type'] == 'CONTRIBUTION' ? '+' : '-'; ?>KES <?php echo number_format($activity['amount']); ?>
                                            </div>
                                        </td>
                                        <td>
                                            <div style="font-weight: 500; color: #1e293b;">
                                                <?php echo htmlspecialchars($activity['fund_name']); ?>
                                            </div>
                                            <div style="color: #64748b; font-size: 0.75rem;">
                                                <?php echo htmlspecialchars($activity['contributor_name']); ?>
                                            </div>
                                        </td>
                                    </tr>
                                    <?php endwhile; ?>
                                </tbody>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
            <?php include 'layouts/footer.php'; ?>
        </div>
    </div>

    <?php include 'layouts/vendor-scripts.php'; ?>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    
    <script>
        // Monthly Trends Chart
        const monthlyCtx = document.getElementById('monthlyTrendsChart').getContext('2d');
        
        // Prepare monthly trends data
        <?php 
        $months = [];
        $contributions = [];
        $withdrawals = [];
        $monthly_trends->data_seek(0);
        while ($trend = $monthly_trends->fetch_assoc()) {
            $months[] = $trend['month'];
            $contributions[] = $trend['contributions'];
            $withdrawals[] = $trend['withdrawals'];
        }
        ?>
        
        const monthlyTrendsChart = new Chart(monthlyCtx, {
            type: 'line',
            data: {
                labels: <?php echo json_encode(array_reverse($months)); ?>,
                datasets: [
                    {
                        label: 'Contributions',
                        data: <?php echo json_encode(array_reverse($contributions)); ?>,
                        borderColor: '#059669',
                        backgroundColor: 'rgba(5, 150, 105, 0.1)',
                        tension: 0.4,
                        fill: true
                    },
                    {
                        label: 'Withdrawals',
                        data: <?php echo json_encode(array_reverse($withdrawals)); ?>,
                        borderColor: '#dc2626',
                        backgroundColor: 'rgba(220, 38, 38, 0.1)',
                        tension: 0.4,
                        fill: true
                    }
                ]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'top',
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            callback: function(value) {
                                return 'KES ' + value.toLocaleString();
                            }
                        }
                    }
                }
            }
        });

        // Category Performance Chart
        const categoryCtx = document.getElementById('categoryChart').getContext('2d');
        
        // Prepare category data
        <?php 
        $category_names = [];
        $category_balances = [];
        $category_colors = [];
        $category_performance->data_seek(0);
        while ($cat = $category_performance->fetch_assoc()) {
            $category_names[] = $cat['category_name'];
            $category_balances[] = $cat['category_balance'];
            $category_colors[] = $cat['color_code'];
        }
        ?>
        
        const categoryChart = new Chart(categoryCtx, {
            type: 'doughnut',
            data: {
                labels: <?php echo json_encode($category_names); ?>,
                datasets: [{
                    data: <?php echo json_encode($category_balances); ?>,
                    backgroundColor: <?php echo json_encode($category_colors); ?>,
                    borderWidth: 2,
                    borderColor: '#fff'
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'bottom',
                        labels: {
                            padding: 20,
                            usePointStyle: true
                        }
                    },
                    tooltip: {
                        callbacks: {
                            label: function(context) {
                                const label = context.label || '';
                                const value = context.parsed;
                                const total = context.dataset.data.reduce((a, b) => a + b, 0);
                                const percentage = ((value / total) * 100).toFixed(1);
                                return label + ': KES ' + value.toLocaleString() + ' (' + percentage + '%)';
                            }
                        }
                    }
                }
            }
        });
    </script>

    <!-- App js -->
    <script src="assets/js/app.js"></script>
</body>
</html>

<?php $conn->close(); ?>