<?php
// Final verification test for Token Management system
require_once '../includes/db.php';

echo "🔍 Final Token Management System Verification...\n\n";

$all_tests_passed = true;

try {
    // Test 1: Check all required tables exist
    $required_tables = [
        'users', 'user_profiles', 'user_groups', 'user_group_memberships',
        'budgets', 'token_actions', 'token_transactions'
    ];
    
    echo "📋 Checking required tables...\n";
    $existing_tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
    
    foreach ($required_tables as $table) {
        if (in_array($table, $existing_tables)) {
            echo "  ✓ $table\n";
        } else {
            echo "  ❌ $table (MISSING)\n";
            $all_tests_passed = false;
        }
    }
    
    // Test 2: Run key queries from token_management.php
    echo "\n🔄 Testing key database queries...\n";
    
    // Test System Overview queries
    $actions = $pdo->query("SELECT * FROM token_actions WHERE is_active = 1 ORDER BY category, action_name LIMIT 5")->fetchAll();
    echo "  ✓ Token actions query: " . count($actions) . " actions found\n";
    
    $packages = $pdo->query("
        SELECT 
            ta.id,
            ta.action_name,
            ta.action_description,
            ta.token_cost,
            ta.category,
            COUNT(tt.id) as usage_count,
            SUM(CASE WHEN tt.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as recent_usage
        FROM token_actions ta
        LEFT JOIN token_transactions tt ON ta.id = tt.action_id
        WHERE ta.is_active = 1
        GROUP BY ta.id
        ORDER BY usage_count DESC, ta.category, ta.action_name
        LIMIT 5
    ")->fetchAll();
    echo "  ✓ Token packages query: " . count($packages) . " packages found\n";
    
    $budget_stats = $pdo->query("
        SELECT 
            COUNT(*) as total_budgets,
            COALESCE(SUM(balance), 0) as total_balance,
            COALESCE(SUM(total_spent), 0) as total_spent,
            COALESCE(AVG(balance), 0) as avg_balance
        FROM budgets 
        WHERE is_active = 1
    ")->fetch(PDO::FETCH_ASSOC);
    echo "  ✓ Budget statistics query: {$budget_stats['total_budgets']} budgets found\n";
    
    // Test Group Members queries
    $all_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();
    echo "  ✓ Groups overview query: " . count($all_groups) . " groups found\n";
    
    $group_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();
    echo "  ✓ Group memberships query: " . count($group_memberships) . " memberships found\n";
    
    // Test 3: Check file syntax
    echo "\n📝 Checking PHP syntax...\n";
    $output = shell_exec('php -l ../admin/token_management.php 2>&1');
    if (strpos($output, 'No syntax errors') !== false) {
        echo "  ✓ token_management.php syntax is valid\n";
    } else {
        echo "  ❌ token_management.php has syntax errors:\n";
        echo "     " . trim($output) . "\n";
        $all_tests_passed = false;
    }
    
    echo "\n" . str_repeat("=", 50) . "\n";
    
    if ($all_tests_passed) {
        echo "🎉 SUCCESS! Token Management System is fully operational!\n\n";
        echo "✅ All database tables exist and are properly structured\n";
        echo "✅ All database queries execute without errors\n";
        echo "✅ PHP syntax is valid\n";
        echo "✅ Group Members feature is ready\n";
        echo "✅ System Overview feature is ready\n\n";
        echo "🚀 You can now access the Token Management interface at:\n";
        echo "   📱 /admin/token_management.php\n\n";
        echo "📋 Available features:\n";
        echo "   • System Overview tab with comprehensive data display\n";
        echo "   • Group Members tab with full management capabilities\n";
        echo "   • Token Actions and Packages management\n";
        echo "   • Budget tracking and statistics\n";
        echo "   • Transaction history and analytics\n";
    } else {
        echo "❌ ERRORS DETECTED! Please review the issues above.\n";
    }
    
} catch (Exception $e) {
    echo "\n❌ CRITICAL ERROR: " . $e->getMessage() . "\n";
    echo "Error trace: " . $e->getTraceAsString() . "\n";
    $all_tests_passed = false;
}

exit($all_tests_passed ? 0 : 1);
?>
