<?php

namespace App\Cursometr\Facts\CourseStructure;

use App\Cursometr\Facts\UsingQuery;
use App\Helpers\Structure;
use Tinderbox\ClickhouseBuilder\Integrations\Laravel\Builder;
use Tinderbox\ClickhouseBuilder\Query\Identifier;

class CourseStructureFastAnswersFact extends AbstractCourseStructureFact implements UsingQuery
{
    protected $format = 'integer';
    protected $defaultValue = 0;

    public static function getName(): string
    {
        return 'fast-answers';
    }

    public function getQuery(\Closure $queryCallback = null): Builder
    {
        /*
         * Получим список листьев из раздела practice
         */
        $entities = Structure::getPracticeEntities($this->getCourseId());

        /*
         * Считаем среднее и отклонение для каждой сущности, что бы потом приджоинить это к основному запросу при
         * подсчете z-score.
         */
        $durationsQuery = $this->getCleanQuery()
                               ->table('learners')
                               ->select('entitiesAttempts.entityIri as entityIri', 'entitiesAttempts.durationTotal as duration')
                               ->final()
                               ->arrayJoin('entitiesAttempts')
                               ->whereIn('entityIri', $entities->getIris())
                               ->preWhere('courseId', '=', $this->getCourseId())
                               ->where('duration', '>', 0)
                               ->where('entitiesAttempts.passed', '=', 1);

        $durationsQuery = $this->getCleanQuery()
                               ->table($durationsQuery)
                               ->select('entityIri', raw('groupArray(duration) as durations'))
                               ->groupBy('entityIri');

        /*
         * arrayReduce('avg', values) AS median,
         * arrayMap(x -> (pow(x - median, 2)), values) AS squared_deltas,
         * sqrt(arraySum(squared_deltas) / (length(values) - 1)) AS deviation
         */

        $medianAndDeviationQuery = $this->getCleanQuery()
                                        ->table($durationsQuery)
                                        ->select(
                                            'entityIri',
                                            raw('arrayReduce(\'median\', durations) as median'),
                                            raw('sqrt(arraySum(arrayMap(x -> (pow(x - median, 2)), durations)) / (length(durations) - 1)) as deviation')
                                        );

        /*
         * Выбираем все попытки
         */
        $query = $this->getCleanQuery()
                      ->table('learners')
                      ->arrayJoin('entitiesAttempts')
                      ->select('learnerId', 'entitiesAttempts.entityIri as entityIri', 'entitiesAttempts.durationTotal as duration')
                      ->where('entitiesAttempts.passed', '=', 1)
                      ->where('entitiesAttempts.durationTotal', '>', 0)
                      ->applyWherePeriod('entitiesAttempts.startedAt', $this->getPeriod())
                      ->final();

        /*
         * Тут применяется фильтр по лернерам и сущностям
         */
        if (!is_null($queryCallback)) {
            $query = $queryCallback($query, $this);
        }

        $attemptsQuery = $this->getCleanQuery()
                              ->table($query)
                              ->select('entityIri', 'learnerId', raw('(duration - median) / deviation as z'))
                              ->anyLeftJoin($medianAndDeviationQuery, ['entityIri'])
                              ->where('z', '<=', -2);

        /*
         * Тут мы оборачиваем запрос и подменяем entityIri на childEntityIri
         */
        $query = $this->getCleanQuery()
                      ->from($attemptsQuery)
                      ->select('learnerId', 'entityIri as childEntityIri');

        /*
         * Берем из временной таблицы информацию о сущности и разворачиваем все строки с дочками
         */
        $longTableQuery = $this->getCleanQuery()
                               ->table(new Identifier($this->getTempTableName()))
                               ->select('entityIri', 'childEntityIri')
                               ->arrayJoin('childs as childEntityIri');

        /*
         * Далее на каждую развернутую строчку джоиним попытки по сущностям по дочернему iri
         */
        $finalQuery = $this->getCleanQuery()
                           ->table($longTableQuery)
                           ->select('entityIri', 'learnerId')
                           ->allLeftJoin($query, ['childEntityIri'])
                           ->where('learnerId', '>', 0);

        return $this->getCleanQuery()
            ->table($finalQuery)
            ->select(raw('uniq(learnerId) as '.static::getIdentifier()), 'entityIri')
            ->groupBy('entityIri');
    }

    public function getJoinColumns(): array
    {
        return ['entityIri'];
    }
}