Game Functions

get_games($filters = [], $limit = null, $offset = 0)

$sql = "SELECT 
    p.ID as id,
    p.post_title as name,
    p.post_name as slug,
    p.post_date as created_at,
    (SELECT meta_value FROM de_postmeta WHERE post_id = p.ID AND meta_key = '_dilema_fecha_activacion' LIMIT 1) as activation_date,
    p.post_status as status,
    p.guid as guid,
    MAX(c.post_title) as client,
    (SELECT gp.guid FROM de_posts gp WHERE gp.ID = (SELECT CAST(gpm.meta_value AS UNSIGNED) FROM de_postmeta gpm WHERE gpm.post_id = MAX(c.ID) AND gpm.meta_key = '_thumbnail_id' LIMIT 1)) as client_logo,
    (SELECT meta_value FROM de_postmeta WHERE post_id = p.ID AND (meta_key = 'dilemma_type' OR meta_key = '_tipo_dilema' OR meta_key = 'tipo') LIMIT 1) as type,
    COUNT(s.id) as sessions_count,
    SUM(s.score) as total_score,
    SUM(s.duration) as total_duration,
    COUNT(DISTINCT s.userId) as users_count
FROM de_posts p
LEFT JOIN de_postmeta pm ON p.ID = pm.post_id AND pm.meta_key = '_cliente_asociado'
LEFT JOIN de_posts c ON pm.meta_value = c.ID
LEFT JOIN de_app_sessions s ON p.ID = s.gameId
WHERE p.post_type = 'dilemas' AND p.post_status = 'publish'
GROUP BY p.ID
ORDER BY p.post_date DESC";

get_games_count($filters = [])

$totalGames = get_games_count(['client' => 'Acme Corporation']);
echo "Total games: " . $totalGames;

get_game_details($gameId)

$game = get_game_details(42);
if ($game) {
    echo "Game: " . $game['name'];
    echo "Started: " . $game['started'];
    echo "Finished: " . $game['finished'];
    echo "Completion Rate: " . round(($game['finished'] / $game['started']) * 100, 1) . "%";
}

get_game_users($gameId, $limit = 10, $offset = 0, $search = '', $status = '')

// Get finished sessions for a game
$users = get_game_users(42, 20, 0, '', 'Finished');

// Search for specific user
$users = get_game_users(42, 10, 0, '[email protected]', '');