<?php

namespace Trainingssystem\Module\App\Query;

class GetAudioForTrainingIdAndUserIdQuery
{
    public function execute(int $trainingId, int $userId): array
    {
        global $wpdb;

        $trainingsmgr_tranings_user_table = $wpdb->prefix . 'md_trainingsmgr_tranings_user';

        $queryString = $wpdb->prepare(
            "SELECT DISTINCT lektion.ID lektionId, audio.guid audioUrl, audio.post_title audioName
FROM $trainingsmgr_tranings_user_table
    LEFT JOIN $wpdb->posts lektion ON lektion.ID = lektion_id OR lektion.ID = training_id
    LEFT JOIN $wpdb->postmeta meta ON lektion.ID = meta.post_id AND meta.meta_key = 'training_audio_mapping'
    LEFT JOIN $wpdb->posts audio ON audio.ID = meta.meta_value
WHERE training_id = %d AND user_id = %d AND audio.guid IS NOT NULL",
            $trainingId,
            $userId
        );
        return $wpdb->get_results($queryString);
    }
}
