<?php

namespace App\Models;

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\SoftDeletes;

class Academy extends Model
{
    use HasFactory, SoftDeletes;

    protected $fillable = [
        'name',
        'duration',
        'url'
    ];

    protected $appends = ['map'];

    public function question_types()
    {
        return $this->belongsToMany(QuestionType::class, 'academy_questions_map')->withPivot('amount');
    }

    // foreach academy it appends a map property, where all question_types and amount of questions are set, like:
    // map: [{question_type_id: 1, question_type_name: 'Numerical Reasonin', amount: 5}, {...}, {...}]
    public function getMapAttribute()
    {
        return DB::select("SELECT 
                question_types.id as question_type_id, 
                question_types.name as question_type_name, 
                IFNULL(academy_questions_map.amount, 0) as amount
            FROM academies 
                LEFT OUTER JOIN question_types ON 1 = 1 
                LEFT JOIN academy_questions_map 
                    ON academies.id = academy_questions_map.academy_id and question_types.id = academy_questions_map.question_type_id 
            WHERE academies.id = {$this->attributes['id']} 
            ORDER BY question_type_id;");
    }

    public function getQuestionNumberAttribute()
    {
        $number = 0;
        foreach ($this->map as $type) {
            $number += $type->amount;
        }

        return $number;
    }

    public function getRandomQuestionsAttribute()
    {
        // Initial state to start creating the UNION query
        $query = DB::table('questions')->where('id', 0);

        /**
         * foeach questionType from the map, append a UNION query to the above initial state
         * the resulting query would be like:
         * SELECT * FROM questions WHERE id = 0
         * UNION
         * SELECT * FROM questions WHERE question_type_id = X ORDER BY RAND() LIMIT Q
         * UNION 
         * SELECT * FROM questions WHERE question_type_id = Y ORDER BY RAND() LIMIT Z
         * ...
         */
        foreach ($this->question_types as $questionType) {
            if ($questionType->pivot->amount > 0) {
                $query = Question::with('answers')
                    ->where('question_type_id', '=', $questionType->id)
                    ->inRandomOrder()
                    ->limit($questionType->pivot->amount)
                    ->union($query);
            }
        }

        return $query->orderBy('question_type_id')->get();
    }
}