<?php
// Test token management database queries
require_once '../includes/db.php';

echo "Testing Token Management Database Queries...\n\n";

try {
    echo "1. Testing user_groups query...\n";
    $groups = $pdo->query("
        SELECT 
            g.*,
            COUNT(ugm.user_id) as member_count,
            GROUP_CONCAT(CONCAT(COALESCE(p.full_name, u.username), ' (', ugm.role_in_group, ')') SEPARATOR ', ') as members_list,
            b.balance as group_budget,
            b.id as budget_id,
            cb.username as created_by_name
        FROM user_groups g
        LEFT JOIN user_group_memberships ugm ON g.id = ugm.group_id
        LEFT JOIN users u ON ugm.user_id = u.id
        LEFT JOIN user_profiles p ON u.id = p.user_id
        LEFT JOIN budgets b ON g.id = b.group_id AND b.is_active = 1
        LEFT JOIN users cb ON g.created_by = cb.id
        WHERE g.is_active = 1
        GROUP BY g.id
        ORDER BY g.created_at DESC
        LIMIT 5
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    echo "✓ Groups query successful - found " . count($groups) . " groups\n";
    
    echo "\n2. Testing group_memberships query...\n";
    $memberships = $pdo->query("
        SELECT 
            g.id as group_id,
            g.name as group_name,
            g.color as group_color,
            u.id as user_id,
            u.username,
            p.full_name,
            ugm.role_in_group,
            ugm.joined_at,
            ab.username as added_by_name
        FROM user_groups g
        INNER JOIN user_group_memberships ugm ON g.id = ugm.group_id
        INNER JOIN users u ON ugm.user_id = u.id
        LEFT JOIN user_profiles p ON u.id = p.user_id
        LEFT JOIN users ab ON ugm.added_by = ab.id
        WHERE g.is_active = 1
        ORDER BY g.name, ugm.role_in_group DESC, p.full_name, u.username
        LIMIT 10
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    echo "✓ Group memberships query successful - found " . count($memberships) . " memberships\n";
    
    echo "\n3. Testing token_transactions query...\n";
    $transactions = $pdo->query("
        SELECT 
            t.*,
            u.username,
            p.full_name,
            a.action_name,
            CASE 
                WHEN t.budget_id IS NOT NULL THEN 'Budget'
                ELSE 'Manual'
            END as transaction_source
        FROM token_transactions t
        LEFT JOIN users u ON t.user_id = u.id
        LEFT JOIN user_profiles p ON u.id = p.user_id
        LEFT JOIN token_actions a ON t.action_id = a.id
        ORDER BY t.created_at DESC
        LIMIT 10
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    echo "✓ Token transactions query successful - found " . count($transactions) . " transactions\n";
    
    echo "\n4. Testing budgets query...\n";
    $budgets = $pdo->query("
        SELECT 
            b.*,
            ug.name as group_name,
            ug.color as group_color,
            u.username as created_by_username,
            p.full_name as created_by_name
        FROM budgets b
        LEFT JOIN user_groups ug ON b.group_id = ug.id
        LEFT JOIN users u ON b.created_by = u.id
        LEFT JOIN user_profiles p ON u.id = p.user_id
        ORDER BY b.created_at DESC
        LIMIT 10
    ")->fetchAll(PDO::FETCH_ASSOC);
    
    echo "✓ Budgets query successful - found " . count($budgets) . " budgets\n";
    
    echo "\n✅ All database queries are working correctly!\n";
    echo "\nToken Management system is ready to use.\n";
    
} catch (Exception $e) {
    echo "❌ Error: " . $e->getMessage() . "\n";
    echo "Error details: " . $e->getTraceAsString() . "\n";
}
?>
