<?php
require_once '../config/database.php';
require_once '../config/auth_enhanced.php';

if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorized access']);
    exit;
}

header('Content-Type: application/json');

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Budget overview for current year
    $currentYear = date('Y');
    
    $stmt = $pdo->prepare("
        SELECT 
            ec.id,
            ec.name,
            ec.budget_limit,
            ec.description,
            COALESCE(SUM(CASE WHEN YEAR(t.created_at) = ? THEN t.amount ELSE 0 END), 0) as spent_this_year,
            COALESCE(SUM(CASE WHEN MONTH(t.created_at) = MONTH(NOW()) AND YEAR(t.created_at) = ? THEN t.amount ELSE 0 END), 0) as spent_this_month,
            COUNT(CASE WHEN YEAR(t.created_at) = ? THEN t.id END) as transactions_this_year
        FROM expense_categories ec
        LEFT JOIN transactions t ON ec.id = t.category_id AND t.type = 'expense'
        GROUP BY ec.id, ec.name, ec.budget_limit, ec.description
        ORDER BY ec.budget_limit DESC
    ");
    $stmt->execute([$currentYear, $currentYear, $currentYear]);
    $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Calculate percentages and status for each category
    $total_budget = 0;
    $total_spent = 0;
    $budget_data = [];
    
    foreach ($categories as $category) {
        $budget_limit = (float)$category['budget_limit'];
        $spent = (float)$category['spent_this_year'];
        
        $total_budget += $budget_limit;
        $total_spent += $spent;
        
        $percentage = $budget_limit > 0 ? round(($spent / $budget_limit) * 100, 2) : 0;
        $remaining = $budget_limit - $spent;
        
        // Determine status
        $status = 'safe';
        $status_class = 'success';
        
        if ($percentage >= 100) {
            $status = 'exceeded';
            $status_class = 'danger';
        } elseif ($percentage >= 90) {
            $status = 'critical';
            $status_class = 'danger';
        } elseif ($percentage >= 75) {
            $status = 'warning';
            $status_class = 'warning';
        } elseif ($percentage >= 50) {
            $status = 'caution';
            $status_class = 'info';
        }
        
        $budget_data[] = [
            'id' => $category['id'],
            'name' => $category['name'],
            'description' => $category['description'],
            'budget_limit' => $budget_limit,
            'spent_this_year' => $spent,
            'spent_this_month' => (float)$category['spent_this_month'],
            'remaining' => $remaining,
            'percentage' => $percentage,
            'status' => $status,
            'status_class' => $status_class,
            'transactions_count' => (int)$category['transactions_this_year'],
            'formatted' => [
                'budget_limit' => '$' . number_format($budget_limit, 2),
                'spent_this_year' => '$' . number_format($spent, 2),
                'spent_this_month' => '$' . number_format($category['spent_this_month'], 2),
                'remaining' => '$' . number_format($remaining, 2)
            ]
        ];
    }
    
    // Overall budget summary
    $overall_percentage = $total_budget > 0 ? round(($total_spent / $total_budget) * 100, 2) : 0;
    $overall_remaining = $total_budget - $total_spent;
    
    // Monthly spending trend (last 6 months)
    $stmt = $pdo->prepare("
        SELECT 
            DATE_FORMAT(t.created_at, '%Y-%m') as month,
            ec.name as category_name,
            SUM(t.amount) as amount
        FROM transactions t
        LEFT JOIN expense_categories ec ON t.category_id = ec.id
        WHERE t.type = 'expense' 
        AND t.created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
        GROUP BY DATE_FORMAT(t.created_at, '%Y-%m'), ec.id, ec.name
        ORDER BY month ASC, ec.name ASC
    ");
    $stmt->execute();
    $monthly_trends = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Budget alerts (categories that are over budget or close to limit)
    $alerts = [];
    foreach ($budget_data as $category) {
        if ($category['percentage'] >= 90) {
            $alerts[] = [
                'category' => $category['name'],
                'message' => $category['percentage'] >= 100 
                    ? 'Budget exceeded by $' . number_format($category['spent_this_year'] - $category['budget_limit'], 2)
                    : 'Only $' . number_format($category['remaining'], 2) . ' remaining (' . (100 - $category['percentage']) . '% left)',
                'severity' => $category['percentage'] >= 100 ? 'danger' : 'warning',
                'percentage' => $category['percentage']
            ];
        }
    }
    
    echo json_encode([
        'overview' => [
            'total_budget' => $total_budget,
            'total_spent' => $total_spent,
            'total_remaining' => $overall_remaining,
            'overall_percentage' => $overall_percentage,
            'year' => $currentYear,
            'formatted' => [
                'total_budget' => '$' . number_format($total_budget, 2),
                'total_spent' => '$' . number_format($total_spent, 2),
                'total_remaining' => '$' . number_format($overall_remaining, 2)
            ]
        ],
        'categories' => $budget_data,
        'monthly_trends' => $monthly_trends,
        'alerts' => $alerts
    ]);
    
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>
