<?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 report parameters
$report_type = $_GET['report_type'] ?? 'summary';
$date_from = $_GET['date_from'] ?? date('Y-m-01'); // First day of current month
$date_to = $_GET['date_to'] ?? date('Y-m-d'); // Today
$category_filter = $_GET['category_filter'] ?? '';
$fund_filter = $_GET['fund_filter'] ?? '';
$export_format = $_GET['export_format'] ?? '';

// Check if we should use fund_expenses or expenses table
$table_check = $conn->query("SHOW TABLES LIKE 'expenses'");
$use_fund_expenses = $table_check->num_rows == 0;

// Get available funds for filter
$funds_query = "SELECT * FROM funds WHERE is_active = 1 ORDER BY fund_name";
$funds = $conn->query($funds_query);

// Get available categories for filter
$categories = null;
if (!$use_fund_expenses) {
   $categories_query = "SELECT * FROM expense_categories WHERE is_active = 1 ORDER BY category_name";
   $categories = $conn->query($categories_query);
}

// Initialize arrays for storing data
$summary_stats = [];
$category_breakdown = [];
$monthly_trends = [];
$status_breakdown = [];
$top_expenses = [];

// Build expense data based on available table
if ($use_fund_expenses) {
   // Summary Statistics using fund_expenses
   $summary_query = "SELECT 
       COUNT(*) as total_expenses,
       COALESCE(SUM(amount), 0) as total_amount,
       COALESCE(AVG(amount), 0) as average_amount,
       COALESCE(MIN(amount), 0) as min_amount,
       COALESCE(MAX(amount), 0) as max_amount,
       COUNT(DISTINCT fund_id) as funds_used
       FROM fund_expenses 
       WHERE expense_date BETWEEN ? AND ?";
   
   $params = [$date_from, $date_to];
   $param_types = "ss";
   
   if (!empty($fund_filter)) {
       $summary_query .= " AND fund_id = ?";
       $params[] = $fund_filter;
       $param_types .= "i";
   }
   
   $stmt_summary = $conn->prepare($summary_query);
   $stmt_summary->bind_param($param_types, ...$params);
   $stmt_summary->execute();
   $summary_stats = $stmt_summary->get_result()->fetch_assoc();
   
   // Category breakdown using fund_expenses
   $category_query = "SELECT 
       expense_category as category,
       COUNT(*) as count,
       SUM(amount) as total_amount,
       AVG(amount) as avg_amount
       FROM fund_expenses 
       WHERE expense_date BETWEEN ? AND ?";
   
   if (!empty($fund_filter)) {
       $category_query .= " AND fund_id = ?";
   }
   
   $category_query .= " GROUP BY expense_category ORDER BY total_amount DESC";
   
   $stmt_category = $conn->prepare($category_query);
   $stmt_category->bind_param($param_types, ...$params);
   $stmt_category->execute();
   $category_result = $stmt_category->get_result();
   
   while ($row = $category_result->fetch_assoc()) {
       $category_breakdown[] = $row;
   }
   
   // Monthly trends
   $monthly_query = "SELECT 
       DATE_FORMAT(expense_date, '%Y-%m') as month,
       COUNT(*) as count,
       SUM(amount) as total_amount
       FROM fund_expenses 
       WHERE expense_date BETWEEN ? AND ?";
   
   if (!empty($fund_filter)) {
       $monthly_query .= " AND fund_id = ?";
   }
   
   $monthly_query .= " GROUP BY DATE_FORMAT(expense_date, '%Y-%m') ORDER BY month ASC LIMIT 12";
   
   $stmt_monthly = $conn->prepare($monthly_query);
   $stmt_monthly->bind_param($param_types, ...$params);
   $stmt_monthly->execute();
   $monthly_result = $stmt_monthly->get_result();
   
   while ($row = $monthly_result->fetch_assoc()) {
       $monthly_trends[] = $row;
   }
   
   // Top expenses
   $top_expenses_query = "SELECT 
       fe.*,
       f.fund_name
       FROM fund_expenses fe
       LEFT JOIN funds f ON fe.fund_id = f.fund_id
       WHERE fe.expense_date BETWEEN ? AND ?";
   
   if (!empty($fund_filter)) {
       $top_expenses_query .= " AND fe.fund_id = ?";
   }
   
   $top_expenses_query .= " ORDER BY fe.amount DESC LIMIT 10";
   
   $stmt_top = $conn->prepare($top_expenses_query);
   $stmt_top->bind_param($param_types, ...$params);
   $stmt_top->execute();
   $top_result = $stmt_top->get_result();
   
   while ($row = $top_result->fetch_assoc()) {
       $top_expenses[] = $row;
   }
   
} else {
   // Use expenses table if it exists
   $summary_query = "SELECT 
       COUNT(*) as total_expenses,
       COALESCE(SUM(amount), 0) as total_amount,
       COALESCE(AVG(amount), 0) as average_amount,
       COALESCE(MIN(amount), 0) as min_amount,
       COALESCE(MAX(amount), 0) as max_amount,
       COUNT(DISTINCT category_id) as categories_used,
       COUNT(DISTINCT fund_id) as funds_used
       FROM expenses 
       WHERE expense_date BETWEEN ? AND ?";
   
   $params = [$date_from, $date_to];
   $param_types = "ss";
   
   if (!empty($category_filter)) {
       $summary_query .= " AND category_id = ?";
       $params[] = $category_filter;
       $param_types .= "i";
   }
   
   if (!empty($fund_filter)) {
       $summary_query .= " AND fund_id = ?";
       $params[] = $fund_filter;
       $param_types .= "i";
   }
   
   $stmt_summary = $conn->prepare($summary_query);
   $stmt_summary->bind_param($param_types, ...$params);
   $stmt_summary->execute();
   $summary_stats = $stmt_summary->get_result()->fetch_assoc();
   
   // Category breakdown
   $category_query = "SELECT 
       ec.category_name as category,
       ec.category_type,
       ec.color_code,
       COUNT(*) as count,
       SUM(e.amount) as total_amount,
       AVG(e.amount) as avg_amount
       FROM expenses e
       JOIN expense_categories ec ON e.category_id = ec.category_id
       WHERE e.expense_date BETWEEN ? AND ?";
   
   if (!empty($category_filter)) {
       $category_query .= " AND e.category_id = ?";
   }
   
   if (!empty($fund_filter)) {
       $category_query .= " AND e.fund_id = ?";
   }
   
   $category_query .= " GROUP BY e.category_id ORDER BY total_amount DESC";
   
   $stmt_category = $conn->prepare($category_query);
   $stmt_category->bind_param($param_types, ...$params);
   $stmt_category->execute();
   $category_result = $stmt_category->get_result();
   
   while ($row = $category_result->fetch_assoc()) {
       $category_breakdown[] = $row;
   }
   
   // Status breakdown
   $status_query = "SELECT 
       status,
       COUNT(*) as count,
       SUM(amount) as total_amount
       FROM expenses 
       WHERE expense_date BETWEEN ? AND ?";
   
   if (!empty($category_filter)) {
       $status_query .= " AND category_id = ?";
   }
   
   if (!empty($fund_filter)) {
       $status_query .= " AND fund_id = ?";
   }
   
   $status_query .= " GROUP BY status ORDER BY total_amount DESC";
   
   $stmt_status = $conn->prepare($status_query);
   $stmt_status->bind_param($param_types, ...$params);
   $stmt_status->execute();
   $status_result = $stmt_status->get_result();
   
   while ($row = $status_result->fetch_assoc()) {
       $status_breakdown[] = $row;
   }
   
   // Monthly trends
   $monthly_query = "SELECT 
       DATE_FORMAT(expense_date, '%Y-%m') as month,
       COUNT(*) as count,
       SUM(amount) as total_amount,
       AVG(amount) as avg_amount
       FROM expenses 
       WHERE expense_date BETWEEN ? AND ?";
   
   if (!empty($category_filter)) {
       $monthly_query .= " AND category_id = ?";
   }
   
   if (!empty($fund_filter)) {
       $monthly_query .= " AND fund_id = ?";
   }
   
   $monthly_query .= " GROUP BY DATE_FORMAT(expense_date, '%Y-%m') ORDER BY month ASC LIMIT 12";
   
   $stmt_monthly = $conn->prepare($monthly_query);
   $stmt_monthly->bind_param($param_types, ...$params);
   $stmt_monthly->execute();
   $monthly_result = $stmt_monthly->get_result();
   
   while ($row = $monthly_result->fetch_assoc()) {
       $monthly_trends[] = $row;
   }
   
   // Top expenses
   $top_expenses_query = "SELECT 
       e.*,
       ec.category_name,
       f.fund_name,
       u.full_name as created_by_name
       FROM expenses e
       LEFT JOIN expense_categories ec ON e.category_id = ec.category_id
       LEFT JOIN funds f ON e.fund_id = f.fund_id
       LEFT JOIN users u ON e.created_by = u.user_id
       WHERE e.expense_date BETWEEN ? AND ?";
   
   if (!empty($category_filter)) {
       $top_expenses_query .= " AND e.category_id = ?";
   }
   
   if (!empty($fund_filter)) {
       $top_expenses_query .= " AND e.fund_id = ?";
   }
   
   $top_expenses_query .= " ORDER BY e.amount DESC LIMIT 10";
   
   $stmt_top = $conn->prepare($top_expenses_query);
   $stmt_top->bind_param($param_types, ...$params);
   $stmt_top->execute();
   $top_result = $stmt_top->get_result();
   
   while ($row = $top_result->fetch_assoc()) {
       $top_expenses[] = $row;
   }
}

// Handle export requests
if (!empty($export_format)) {
   switch ($export_format) {
       case 'csv':
           exportToCSV();
           break;
       case 'pdf':
           exportToPDF();
           break;
       case 'excel':
           exportToExcel();
           break;
   }
   exit();
}

function exportToCSV() {
   global $conn, $use_fund_expenses, $date_from, $date_to, $category_filter, $fund_filter;
   
   header('Content-Type: text/csv');
   header('Content-Disposition: attachment; filename="expense_report_' . date('Y-m-d') . '.csv"');
   
   $output = fopen('php://output', 'w');
   
   if ($use_fund_expenses) {
       fputcsv($output, ['Date', 'Category', 'Amount', 'Description', 'Fund', 'Payment Method']);
       
       $query = "SELECT fe.*, f.fund_name 
                 FROM fund_expenses fe 
                 LEFT JOIN funds f ON fe.fund_id = f.fund_id 
                 WHERE fe.expense_date BETWEEN ? AND ?";
       
       $params = [$date_from, $date_to];
       if (!empty($fund_filter)) {
           $query .= " AND fe.fund_id = ?";
           $params[] = $fund_filter;
       }
       $query .= " ORDER BY fe.expense_date DESC";
       
       $stmt = $conn->prepare($query);
       $stmt->bind_param(str_repeat('s', count($params)), ...$params);
       $stmt->execute();
       $result = $stmt->get_result();
       
       while ($row = $result->fetch_assoc()) {
           fputcsv($output, [
               $row['expense_date'],
               $row['expense_category'],
               $row['amount'],
               $row['description'],
               $row['fund_name'],
               $row['payment_method']
           ]);
       }
   } else {
       fputcsv($output, ['Date', 'Title', 'Category', 'Amount', 'Status', 'Vendor', 'Fund', 'Payment Method', 'Created By']);
       
       $query = "SELECT e.*, ec.category_name, f.fund_name, u.full_name as created_by_name
                 FROM expenses e 
                 LEFT JOIN expense_categories ec ON e.category_id = ec.category_id
                 LEFT JOIN funds f ON e.fund_id = f.fund_id
                 LEFT JOIN users u ON e.created_by = u.user_id
                 WHERE e.expense_date BETWEEN ? AND ?";
       
       $params = [$date_from, $date_to];
       $param_types = "ss";
       
       if (!empty($category_filter)) {
           $query .= " AND e.category_id = ?";
           $params[] = $category_filter;
           $param_types .= "i";
       }
       
       if (!empty($fund_filter)) {
           $query .= " AND e.fund_id = ?";
           $params[] = $fund_filter;
           $param_types .= "i";
       }
       
       $query .= " ORDER BY e.expense_date DESC";
       
       $stmt = $conn->prepare($query);
       $stmt->bind_param($param_types, ...$params);
       $stmt->execute();
       $result = $stmt->get_result();
       
       while ($row = $result->fetch_assoc()) {
           fputcsv($output, [
               $row['expense_date'],
               $row['expense_title'],
               $row['category_name'],
               $row['amount'],
               $row['status'],
               $row['vendor_name'],
               $row['fund_name'],
               $row['payment_method'],
               $row['created_by_name']
           ]);
       }
   }
   
   fclose($output);
}

function exportToPDF() {
   echo "<script>alert('PDF export feature will be implemented with a PDF library like TCPDF.');</script>";
}

function exportToExcel() {
   exportToCSV();
}
?>

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

<head>
   <?php includeFileWithVariables('layouts/title-meta.php', array('title' => 'Expense Reports - Personal Fund Management System')); ?>
   <?php include 'layouts/head-css.php'; ?>
   <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
   <link href="https://cdn.jsdelivr.net/npm/sweetalert2@11.7.32/dist/sweetalert2.min.css" rel="stylesheet">
   <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
   
   <style>
       body {
           background-color: #f8fafc;
           font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;
       }
       
       .page-content {
           padding: 1.5rem;
       }
       
       .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 {
           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-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;
       }
       
       .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;
       }
       
       .chart-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;
       }
       
       .chart-title {
           font-size: 1.125rem;
           font-weight: 600;
           color: #1e293b;
           margin-bottom: 1rem;
           padding-bottom: 0.5rem;
           border-bottom: 2px solid #e2e8f0;
       }
       
       .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;
       }
       
       .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: #059669;
           box-shadow: 0 0 0 3px rgba(5, 150, 105, 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;
           text-decoration: none;
           display: inline-flex;
           align-items: center;
           gap: 0.5rem;
       }
       
       .btn-primary { background: #059669; color: white; }
       .btn-primary:hover { background: #047857; color: white; }
       
       .btn-success { background: #10b981; color: white; }
       .btn-success:hover { background: #059669; color: white; }
       
       .btn-secondary { background: #6b7280; color: white; }
       .btn-secondary:hover { background: #4b5563; color: white; }
       
       .btn-outline-secondary {
           background: transparent;
           border: 1px solid #e2e8f0;
           color: #64748b;
       }
       
       .btn-outline-secondary:hover {
           background: #f8fafc;
           border-color: #64748b;
       }
       
       .progress {
           height: 8px;
           border-radius: 4px;
           background: #f1f5f9;
       }
       
       .progress-bar {
           border-radius: 4px;
       }
       
       .export-dropdown {
           position: relative;
           display: inline-block;
       }
       
       .export-content {
           display: none;
           position: absolute;
           background-color: white;
           min-width: 160px;
           box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
           border-radius: 8px;
           z-index: 1;
           right: 0;
           top: 100%;
           margin-top: 0.5rem;
       }
       
       .export-content.show {
           display: block;
       }
       
       .export-content a {
           color: #374151;
           padding: 12px 16px;
           text-decoration: none;
           display: block;
           border-radius: 8px;
           margin: 4px;
       }
       
       .export-content a:hover {
           background-color: #f8fafc;
       }
       
       @media (max-width: 768px) {
           .page-content {
               padding: 1rem;
           }
           
           .stats-row {
               grid-template-columns: 1fr;
           }
           
           .table-header {
               flex-direction: column;
               gap: 1rem;
               align-items: stretch;
           }
           
           .data-table {
               font-size: 0.75rem;
           }
           
           .data-table th,
           .data-table td {
               padding: 0.5rem;
           }
       }
   </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">Expense Reports</h1>
                       <p class="page-subtitle">Comprehensive expense analysis and reporting</p>
                   </div>

                   <!-- Filter Card -->
                   <div class="filter-card">
                       <form method="GET" class="row g-3">
                           <input type="hidden" name="report_type" value="<?php echo htmlspecialchars($report_type); ?>">
                           
                           <div class="col-md-3">
                               <label class="form-label">Date From</label>
                               <input type="date" class="form-control" name="date_from" value="<?php echo htmlspecialchars($date_from); ?>">
                           </div>
                           <div class="col-md-3">
                               <label class="form-label">Date To</label>
                               <input type="date" class="form-control" name="date_to" value="<?php echo htmlspecialchars($date_to); ?>">
                           </div>
                           
                           <?php if (!$use_fund_expenses && $categories): ?>
                           <div class="col-md-2">
                               <label class="form-label">Category</label>
                               <select class="form-select" name="category_filter">
                                   <option value="">All Categories</option>
                                   <?php 
                                   $categories->data_seek(0);
                                   while ($category = $categories->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>
                           <?php endif; ?>
                           
                           <div class="col-md-2">
                               <label class="form-label">Fund</label>
                               <select class="form-select" name="fund_filter">
                                   <option value="">All Funds</option>
                                   <?php 
                                   $funds->data_seek(0);
                                   while ($fund = $funds->fetch_assoc()): 
                                   ?>
                                       <option value="<?php echo $fund['fund_id']; ?>" <?php echo $fund_filter == $fund['fund_id'] ? 'selected' : ''; ?>>
                                           <?php echo htmlspecialchars($fund['fund_name']); ?>
                                       </option>
                                   <?php endwhile; ?>
                               </select>
                           </div>
                           
                           <div class="col-md-2">
                               <label class="form-label">&nbsp;</label>
                               <div class="d-flex gap-2">
                                   <button type="submit" class="btn btn-primary">
                                       <i class="ph-funnel"></i> Filter
                                   </button>
                                   <a href="expense_reports.php" class="btn btn-outline-secondary">Clear</a>
                               </div>
                           </div>
                       </form>
                   </div>

                   <!-- Summary Statistics -->
                   <div class="stats-row">
                       <div class="stat-card">
                           <div class="stat-value"><?php echo number_format($summary_stats['total_expenses'] ?? 0); ?></div>
                           <div class="stat-label">Total Expenses</div>
                       </div>
                       <div class="stat-card">
                           <div class="stat-value">KES <?php echo number_format($summary_stats['total_amount'] ?? 0, 2); ?></div>
                           <div class="stat-label">Total Amount</div>
                       </div>
                       <div class="stat-card">
                           <div class="stat-value">KES <?php echo number_format($summary_stats['average_amount'] ?? 0, 2); ?></div>
                           <div class="stat-label">Average Amount</div>
                       </div>
                       <div class="stat-card">
                           <div class="stat-value">KES <?php echo number_format($summary_stats['max_amount'] ?? 0, 2); ?></div>
                           <div class="stat-label">Highest Expense</div>
                       </div>
                   </div>

                   <div class="row">
                       <!-- Category Breakdown Chart -->
                       <div class="col-lg-6">
                           <div class="chart-card">
                               <div class="d-flex justify-content-between align-items-center mb-3">
                                   <h3 class="chart-title">Category Breakdown</h3>
                                   <div class="export-dropdown">
                                       <button class="btn btn-secondary" onclick="toggleExportDropdown()">
                                           <i class="ph-download"></i> Export
                                       </button>
                                       <div id="exportDropdown" class="export-content">
                                           <a href="?<?php echo http_build_query(array_merge($_GET, ['export_format' => 'csv'])); ?>">
                                               <i class="ph-file-csv me-2"></i> Export CSV
                                           </a>
                                           <a href="?<?php echo http_build_query(array_merge($_GET, ['export_format' => 'pdf'])); ?>">
                                               <i class="ph-file-pdf me-2"></i> Export PDF
                                           </a>
                                           <a href="?<?php echo http_build_query(array_merge($_GET, ['export_format' => 'excel'])); ?>">
                                               <i class="ph-microsoft-excel-logo me-2"></i> Export Excel
                                           </a>
                                       </div>
                                   </div>
                               </div>
                               <canvas id="categoryChart" height="300"></canvas>
                           </div>
                       </div>

                       <!-- Monthly Trends Chart -->
                       <div class="col-lg-6">
                           <div class="chart-card">
                               <h3 class="chart-title">Monthly Trends</h3>
                               <canvas id="monthlyChart" height="300"></canvas>
                           </div>
                       </div>
                   </div>

                   <!-- Category Breakdown Table -->
                   <div class="table-card">
                       <div class="table-header">
                           <h3 class="table-title">Category Analysis</h3>
                       </div>
                       
                       <table class="data-table">
                           <thead>
                               <tr>
                                   <th>Category</th>
                                   <th>Count</th>
                                   <th>Total Amount</th>
                                   <th>Average Amount</th>
                                   <th>Percentage</th>
                                   <th>Progress</th>
                               </tr>
                           </thead>
                           <tbody>
                               <?php 
                               $total_for_percentage = $summary_stats['total_amount'] ?? 1;
                               foreach ($category_breakdown as $category): 
                                   $percentage = $total_for_percentage > 0 ? ($category['total_amount'] / $total_for_percentage) * 100 : 0;
                               ?>
                               <tr>
                                   <td>
                                       <div style="font-weight: 600; color: #1e293b;">
                                           <?php echo htmlspecialchars($category['category']); ?>
                                       </div>
                                       <?php if (isset($category['category_type'])): ?>
                                           <div style="color: #64748b; font-size: 0.75rem;">
                                               <?php echo ucfirst(str_replace('_', ' ', strtolower($category['category_type']))); ?>
                                           </div>
                                       <?php endif; ?>
                                   </td>
                                   <td><?php echo number_format($category['count']); ?></td>
                                   <td style="font-weight: 600; color: #059669;">
                                       KES <?php echo number_format($category['total_amount'], 2); ?>
                                   </td>
                                   <td>KES <?php echo number_format($category['avg_amount'], 2); ?></td>
                                   <td><?php echo number_format($percentage, 1); ?>%</td>
                                   <td>
                                       <div class="progress">
                                           <div class="progress-bar" style="width: <?php echo min($percentage, 100); ?>%; background-color: #059669;"></div>
                                       </div>
                                   </td>
                               </tr>
                               <?php endforeach; ?>
                               <?php if (empty($category_breakdown)): ?>
                               <tr>
                                   <td colspan="6" style="text-align: center; color: #64748b; padding: 2rem;">
                                       No expense data found for the selected period.
                                   </td>
                               </tr>
                               <?php endif; ?>
                           </tbody>
                       </table>
                   </div>

                   <?php if (!$use_fund_expenses && !empty($status_breakdown)): ?>
                   <!-- Status Breakdown -->
                   <div class="table-card">
                       <div class="table-header">
                           <h3 class="table-title">Status Breakdown</h3>
                       </div>
                       
                       <table class="data-table">
                           <thead>
                               <tr>
                                   <th>Status</th>
                                   <th>Count</th>
                                   <th>Total Amount</th>
                                   <th>Percentage</th>
                                   <th>Progress</th>
                               </tr>
                           </thead>
                           <tbody>
                               <?php foreach ($status_breakdown as $status): 
                                   $percentage = $total_for_percentage > 0 ? ($status['total_amount'] / $total_for_percentage) * 100 : 0;
                                   
                                   $status_class = match($status['status']) {
                                       'DRAFT' => 'background: #f1f5f9; color: #64748b;',
                                       'PENDING' => 'background: #fef3c7; color: #92400e;',
                                       'APPROVED' => 'background: #dcfce7; color: #166534;',
                                       'PAID' => 'background: #dcfce7; color: #166534;',
                                       'REJECTED' => 'background: #fee2e2; color: #991b1b;',
                                       'CANCELLED' => 'background: #fee2e2; color: #991b1b;',
                                       default => 'background: #f1f5f9; color: #64748b;'
                                   };
                               ?>
                               <tr>
                                   <td>
                                       <span style="padding: 0.25rem 0.75rem; border-radius: 9999px; font-size: 0.75rem; font-weight: 600; text-transform: uppercase; <?php echo $status_class; ?>">
                                           <?php echo ucfirst(str_replace('_', ' ', strtolower($status['status']))); ?>
                                       </span>
                                   </td>
                                   <td><?php echo number_format($status['count']); ?></td>
                                   <td style="font-weight: 600; color: #059669;">
                                       KES <?php echo number_format($status['total_amount'], 2); ?>
                                   </td>
                                   <td><?php echo number_format($percentage, 1); ?>%</td>
                                   <td>
                                       <div class="progress">
                                           <div class="progress-bar" style="width: <?php echo min($percentage, 100); ?>%; background-color: #059669;"></div>
                                       </div>
                                   </td>
                               </tr>
                               <?php endforeach; ?>
                           </tbody>
                       </table>
                   </div>
                   <?php endif; ?>

                   <!-- Top Expenses -->
                   <div class="table-card">
                       <div class="table-header">
                           <h3 class="table-title">Top 10 Expenses</h3>
                       </div>
                       
                       <table class="data-table">
                           <thead>
                               <tr>
                                   <th>Date</th>
                                   <th>Description</th>
                                   <?php if (!$use_fund_expenses): ?>
                                       <th>Category</th>
                                       <th>Status</th>
                                       <th>Created By</th>
                                   <?php else: ?>
                                       <th>Type</th>
                                       <th>Payment Method</th>
                                   <?php endif; ?>
                                   <th>Fund</th>
                                   <th>Amount</th>
                               </tr>
                           </thead>
                           <tbody>
                               <?php if (!empty($top_expenses)): ?>
                                   <?php foreach ($top_expenses as $expense): ?>
                                   <tr>
                                       <td><?php echo date('M j, Y', strtotime($expense['expense_date'])); ?></td>
                                       <td>
                                           <div style="font-weight: 600; color: #1e293b;">
                                               <?php 
                                               if ($use_fund_expenses) {
                                                   echo htmlspecialchars($expense['expense_type'] ?? 'Fund Expense');
                                               } else {
                                                   echo htmlspecialchars($expense['expense_title']);
                                               }
                                               ?>
                                           </div>
                                           <?php if (!empty($expense['description'])): ?>
                                               <div style="color: #64748b; font-size: 0.75rem;">
                                                   <?php echo htmlspecialchars(substr($expense['description'], 0, 50)) . (strlen($expense['description']) > 50 ? '...' : ''); ?>
                                               </div>
                                           <?php endif; ?>
                                       </td>
                                       
                                       <?php if (!$use_fund_expenses): ?>
                                           <td><?php echo htmlspecialchars($expense['category_name'] ?? 'N/A'); ?></td>
                                           <td>
                                               <?php 
                                               $status_class = match($expense['status'] ?? 'DRAFT') {
                                                   'DRAFT' => 'background: #f1f5f9; color: #64748b;',
                                                   'PENDING' => 'background: #fef3c7; color: #92400e;',
                                                   'APPROVED' => 'background: #dcfce7; color: #166534;',
                                                   'PAID' => 'background: #dcfce7; color: #166534;',
                                                   'REJECTED' => 'background: #fee2e2; color: #991b1b;',
                                                   'CANCELLED' => 'background: #fee2e2; color: #991b1b;',
                                                   default => 'background: #f1f5f9; color: #64748b;'
                                               };
                                               ?>
                                               <span style="padding: 0.25rem 0.5rem; border-radius: 4px; font-size: 0.75rem; font-weight: 600; <?php echo $status_class; ?>">
                                                   <?php echo ucfirst(str_replace('_', ' ', strtolower($expense['status'] ?? 'DRAFT'))); ?>
                                               </span>
                                           </td>
                                           <td><?php echo htmlspecialchars($expense['created_by_name'] ?? 'N/A'); ?></td>
                                       <?php else: ?>
                                           <td><?php echo htmlspecialchars($expense['expense_category'] ?? 'N/A'); ?></td>
                                           <td>
                                               <span style="padding: 0.25rem 0.5rem; border-radius: 4px; font-size: 0.75rem; background: #e0e7ff; color: #3730a3;">
                                                   <?php echo ucfirst(str_replace('_', ' ', strtolower($expense['payment_method'] ?? 'CASH'))); ?>
                                               </span>
                                           </td>
                                       <?php endif; ?>
                                       
                                       <td><?php echo htmlspecialchars($expense['fund_name'] ?? 'N/A'); ?></td>
                                       <td style="font-weight: 700; color: #059669; font-size: 1rem;">
                                           KES <?php echo number_format($expense['amount'], 2); ?>
                                       </td>
                                   </tr>
                                   <?php endforeach; ?>
                               <?php else: ?>
                                   <tr>
                                       <td colspan="<?php echo $use_fund_expenses ? 6 : 7; ?>" style="text-align: center; color: #64748b; padding: 2rem;">
                                           No expense data found for the selected period.
                                       </td>
                                   </tr>
                               <?php endif; ?>
                           </tbody>
                       </table>
                   </div>

                   <!-- Monthly Trends Table -->
                   <div class="table-card">
                       <div class="table-header">
                           <h3 class="table-title">Monthly Summary</h3>
                       </div>
                       
                       <table class="data-table">
                           <thead>
                               <tr>
                                   <th>Month</th>
                                   <th>Count</th>
                                   <th>Total Amount</th>
                                   <?php if (!$use_fund_expenses): ?>
                                       <th>Average Amount</th>
                                   <?php endif; ?>
                                   <th>vs Previous</th>
                               </tr>
                           </thead>
                           <tbody>
                               <?php 
                               if (!empty($monthly_trends)):
                                   $prev_amount = 0;
                                   foreach ($monthly_trends as $month): 
                                       $change_percentage = 0;
                                       $change_class = '';
                                       $change_icon = '';
                                       
                                       if ($prev_amount > 0) {
                                           $change_percentage = (($month['total_amount'] - $prev_amount) / $prev_amount) * 100;
                                           if ($change_percentage > 0) {
                                               $change_class = 'color: #dc2626;';
                                               $change_icon = '↗';
                                           } elseif ($change_percentage < 0) {
                                               $change_class = 'color: #059669;';
                                               $change_icon = '↘';
                                           } else {
                                               $change_class = 'color: #6b7280;';
                                               $change_icon = '→';
                                           }
                                       }
                               ?>
                               <tr>
                                   <td style="font-weight: 600; color: #1e293b;">
                                       <?php echo date('F Y', strtotime($month['month'] . '-01')); ?>
                                   </td>
                                   <td><?php echo number_format($month['count']); ?></td>
                                   <td style="font-weight: 600; color: #059669;">
                                       KES <?php echo number_format($month['total_amount'], 2); ?>
                                   </td>
                                   <?php if (!$use_fund_expenses): ?>
                                       <td>KES <?php echo number_format($month['avg_amount'] ?? 0, 2); ?></td>
                                   <?php endif; ?>
                                   <td>
                                       <?php if ($prev_amount > 0): ?>
                                           <span style="<?php echo $change_class; ?> font-weight: 600;">
                                               <?php echo $change_icon; ?> <?php echo number_format(abs($change_percentage), 1); ?>%
                                           </span>
                                       <?php else: ?>
                                           <span style="color: #6b7280;">-</span>
                                       <?php endif; ?>
                                   </td>
                               </tr>
                               <?php 
                               $prev_amount = $month['total_amount'];
                               endforeach;
                               else:
                               ?>
                               <tr>
                                   <td colspan="<?php echo $use_fund_expenses ? 4 : 5; ?>" style="text-align: center; color: #64748b; padding: 2rem;">
                                       No monthly data available for the selected period.
                                   </td>
                               </tr>
                               <?php endif; ?>
                           </tbody>
                       </table>
                   </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 src="https://cdn.jsdelivr.net/npm/sweetalert2@11.7.32/dist/sweetalert2.all.min.js"></script>
   
   <script>
       // Category Breakdown Chart
       const categoryCtx = document.getElementById('categoryChart').getContext('2d');
       const categoryLabels = [];
       const categoryData = [];
       const categoryColors = [];
       
       <?php foreach ($category_breakdown as $category): ?>
           categoryLabels.push('<?php echo addslashes($category["category"]); ?>');
           categoryData.push(<?php echo $category["total_amount"]; ?>);
           categoryColors.push('<?php echo isset($category["color_code"]) ? $category["color_code"] : "#" . substr(md5($category["category"]), 0, 6); ?>');
       <?php endforeach; ?>
       
       if (categoryLabels.length > 0) {
           new Chart(categoryCtx, {
               type: 'doughnut',
               data: {
                   labels: categoryLabels,
                   datasets: [{
                       data: categoryData,
                       backgroundColor: categoryColors,
                       borderWidth: 2,
                       borderColor: '#ffffff'
                   }]
               },
               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 = new Intl.NumberFormat('en-KE', {
                                       style: 'currency',
                                       currency: 'KES'
                                   }).format(context.raw);
                                   const total = categoryData.reduce((a, b) => a + b, 0);
                                   const percentage = total > 0 ? ((context.raw / total) * 100).toFixed(1) : 0;
                                   return label + ': ' + value + ' (' + percentage + '%)';
                               }
                           }
                       }
                   }
               }
           });
       } else {
           categoryCtx.canvas.parentNode.innerHTML = '<div style="text-align: center; padding: 3rem; color: #64748b;">No category data available</div>';
       }
       
       // Monthly Trends Chart
       const monthlyCtx = document.getElementById('monthlyChart').getContext('2d');
       const monthlyLabels = [];
       const monthlyData = [];
       
       <?php foreach ($monthly_trends as $month): ?>
           monthlyLabels.push('<?php echo date("M Y", strtotime($month["month"] . "-01")); ?>');
           monthlyData.push(<?php echo $month["total_amount"]; ?>);
       <?php endforeach; ?>
       
       if (monthlyLabels.length > 0) {
           new Chart(monthlyCtx, {
               type: 'line',
               data: {
                   labels: monthlyLabels,
                   datasets: [{
                       label: 'Monthly Expenses',
                       data: monthlyData,
                       borderColor: '#059669',
                       backgroundColor: 'rgba(5, 150, 105, 0.1)',
                       borderWidth: 3,
                       fill: true,
                       tension: 0.4,
                       pointBackgroundColor: '#059669',
                       pointBorderColor: '#ffffff',
                       pointBorderWidth: 2,
                       pointRadius: 6
                   }]
               },
               options: {
                   responsive: true,
                   maintainAspectRatio: false,
                   plugins: {
                       legend: {
                           display: false
                       },
                       tooltip: {
                           callbacks: {
                               label: function(context) {
                                   return 'Total: ' + new Intl.NumberFormat('en-KE', {
                                       style: 'currency',
                                       currency: 'KES'
                                   }).format(context.raw);
                               }
                           }
                       }
                   },
                   scales: {
                       y: {
                           beginAtZero: true,
                           ticks: {
                               callback: function(value) {
                                   return 'KES ' + new Intl.NumberFormat().format(value);
                               }
                           }
                       },
                       x: {
                           grid: {
                               display: false
                           }
                       }
                   }
               }
           });
       } else {
           monthlyCtx.canvas.parentNode.innerHTML = '<div style="text-align: center; padding: 3rem; color: #64748b;">No monthly data available</div>';
       }
       
       // Export dropdown functionality
       function toggleExportDropdown() {
           document.getElementById("exportDropdown").classList.toggle("show");
       }
       
       // Close dropdown when clicking outside
       window.onclick = function(event) {
           if (!event.target.matches('.btn')) {
               var dropdowns = document.getElementsByClassName("export-content");
               for (var i = 0; i < dropdowns.length; i++) {
                   var openDropdown = dropdowns[i];
                   if (openDropdown.classList.contains('show')) {
                       openDropdown.classList.remove('show');
                   }
               }
           }
       }
       
       // Show loading state for export operations
       document.querySelectorAll('.export-content a').forEach(function(link) {
           link.addEventListener('click', function(e) {
               Swal.fire({
                   title: 'Generating Report...',
                   text: 'Please wait while we prepare your export.',
                   allowOutsideClick: false,
                   showConfirmButton: false,
                   didOpen: () => {
                       Swal.showLoading();
                   }
               });
               
               // Hide loading after 3 seconds (assuming export completes)
               setTimeout(function() {
                   Swal.close();
               }, 3000);
           });
       });
   </script>

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

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