#!/bin/bash

echo "=== Checking ALL User Roles for HVAC-related roles ==="
echo "Date: $(date)"
echo

# SSH connection details
SERVER="146.190.76.204"
USER="roodev"

echo "🔍 Searching for ALL users with any HVAC-related roles..."

# Execute debugging commands via SSH
ssh ${USER}@${SERVER} << 'EOF'
cd /home/974670.cloudwaysapps.com/uberrxmprk/public_html

echo "=== Comprehensive User Role Analysis ==="

echo "🔍 Step 1: Find all users with ANY role containing 'hvac' (case insensitive)..."
wp db query "SELECT u.ID, u.user_login, u.user_email, um.meta_value as capabilities 
            FROM wp_users u 
            JOIN wp_usermeta um ON u.ID = um.user_id 
            WHERE um.meta_key = 'wp_capabilities' 
            AND LOWER(um.meta_value) LIKE '%hvac%'"

echo
echo "🔍 Step 2: Find all users with ANY role containing 'trainer' (case insensitive)..."
wp db query "SELECT u.ID, u.user_login, u.user_email, um.meta_value as capabilities 
            FROM wp_users u 
            JOIN wp_usermeta um ON u.ID = um.user_id 
            WHERE um.meta_key = 'wp_capabilities' 
            AND LOWER(um.meta_value) LIKE '%trainer%'"

echo
echo "🔍 Step 3: Check for exact role names that might be causing issues..."
wp db query "SELECT DISTINCT um.meta_value as role_data
            FROM wp_usermeta um 
            WHERE um.meta_key = 'wp_capabilities' 
            AND (um.meta_value LIKE '%trainer%' OR um.meta_value LIKE '%hvac%')"

echo
echo "🔍 Step 4: List ALL users and their roles (first 20)..."
wp user list --fields=ID,user_login,user_email,roles --number=20 --format=table

echo
echo "🔍 Step 5: Check if there are users with events but no hvac_trainer role..."
wp db query "SELECT DISTINCT p.post_author, u.user_login, u.user_email, um.meta_value as roles
            FROM wp_posts p 
            JOIN wp_users u ON p.post_author = u.ID 
            JOIN wp_usermeta um ON u.ID = um.user_id
            WHERE p.post_type = 'tribe_events' 
            AND um.meta_key = 'wp_capabilities'
            AND p.post_author NOT IN (
                SELECT u2.ID FROM wp_users u2 
                JOIN wp_usermeta um2 ON u2.ID = um2.user_id 
                WHERE um2.meta_key = 'wp_capabilities' 
                AND um2.meta_value LIKE '%hvac_trainer%'
            )"

echo
echo "🔍 Step 6: Search for variations like 'HVAC Trainer' with spaces or capitals..."
wp eval "
global \$wpdb;
\$results = \$wpdb->get_results(\"
    SELECT u.ID, u.user_login, u.user_email, um.meta_value 
    FROM {$wpdb->users} u 
    JOIN {$wpdb->usermeta} um ON u.ID = um.user_id 
    WHERE um.meta_key = 'wp_capabilities' 
    AND (um.meta_value LIKE '%HVAC%' OR um.meta_value LIKE '%Trainer%' OR um.meta_value LIKE '% trainer%')
\");
foreach(\$results as \$result) {
    echo 'User: ' . \$result->user_login . ' (' . \$result->user_email . ') - Capabilities: ' . \$result->meta_value . PHP_EOL;
}
"

EOF

echo "✅ Comprehensive user role analysis completed!"