<?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);
    
    // Category usage statistics
    $stmt = $pdo->prepare("
        SELECT 
            ec.id,
            ec.name,
            ec.budget_limit,
            COUNT(t.id) as transaction_count,
            COALESCE(SUM(t.amount), 0) as total_spent,
            COALESCE(SUM(CASE WHEN YEAR(t.created_at) = YEAR(NOW()) THEN t.amount ELSE 0 END), 0) as yearly_spent,
            COALESCE(SUM(CASE WHEN MONTH(t.created_at) = MONTH(NOW()) AND YEAR(t.created_at) = YEAR(NOW()) THEN t.amount ELSE 0 END), 0) as monthly_spent
        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
        ORDER BY total_spent DESC
    ");
    $stmt->execute();
    $categories = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Calculate budget usage and status
    foreach ($categories as &$category) {
        // Budget usage percentage
        if ($category['budget_limit'] > 0) {
            $category['budget_usage_percent'] = round(($category['yearly_spent'] / $category['budget_limit']) * 100, 2);
            $category['remaining_budget'] = $category['budget_limit'] - $category['yearly_spent'];
        } else {
            $category['budget_usage_percent'] = 0;
            $category['remaining_budget'] = 0;
        }
        
        // Budget status
        if ($category['budget_usage_percent'] >= 100) {
            $category['budget_status'] = 'exceeded';
        } elseif ($category['budget_usage_percent'] >= 80) {
            $category['budget_status'] = 'warning';
        } elseif ($category['budget_usage_percent'] >= 60) {
            $category['budget_status'] = 'caution';
        } else {
            $category['budget_status'] = 'safe';
        }
        
        // Format amounts
        $category['budget_limit'] = number_format($category['budget_limit'], 2);
        $category['total_spent'] = number_format($category['total_spent'], 2);
        $category['yearly_spent'] = number_format($category['yearly_spent'], 2);
        $category['monthly_spent'] = number_format($category['monthly_spent'], 2);
        $category['remaining_budget'] = number_format($category['remaining_budget'], 2);
    }
    
    // Summary statistics
    $stmt = $pdo->prepare("
        SELECT 
            COUNT(DISTINCT ec.id) as total_categories,
            COUNT(DISTINCT t.id) as total_transactions,
            COALESCE(SUM(ec.budget_limit), 0) as total_budget,
            COALESCE(SUM(CASE WHEN YEAR(t.created_at) = YEAR(NOW()) THEN t.amount ELSE 0 END), 0) as total_yearly_spent
        FROM expense_categories ec
        LEFT JOIN transactions t ON ec.id = t.category_id AND t.type = 'expense'
    ");
    $stmt->execute();
    $summary = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Calculate overall budget usage
    $overall_budget_usage = 0;
    if ($summary['total_budget'] > 0) {
        $overall_budget_usage = round(($summary['total_yearly_spent'] / $summary['total_budget']) * 100, 2);
    }
    
    echo json_encode([
        'categories' => $categories,
        'summary' => [
            'total_categories' => $summary['total_categories'],
            'total_transactions' => $summary['total_transactions'],
            'total_budget' => number_format($summary['total_budget'], 2),
            'total_yearly_spent' => number_format($summary['total_yearly_spent'], 2),
            'overall_budget_usage' => $overall_budget_usage,
            'remaining_total_budget' => number_format($summary['total_budget'] - $summary['total_yearly_spent'], 2)
        ]
    ]);
    
} catch (PDOException $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}
?>
