<?php

declare(strict_types=1);

namespace App\Repository;

use App\UI\DTO\FilterDTO;
use App\UI\DTO\OrderDirection;
use App\UI\DTO\OrderDTO;
use Nette\Database\Explorer;
use Nette\Database\Row;
use Nette\Database\SqlLiteral;

class SurveyRepository
{
    public function __construct(
        private readonly Explorer $explorer
    ) {
    }

    /**
     * @return array<int, Row>
     */
    public function findSurveys(int $limit, int $offset, ?OrderDTO $order, FilterDTO $filter): array
    {
        if ($order === null) {
            $order = new OrderDTO(
                'survey_id',
                OrderDirection::DESC
            );
        }

        $data = $this->explorer->query(
            "SELECT 
                    survey.id AS survey_id,
                    survey.name AS survey_name,
                    survey.comments,
                    survey.agreement,
                    (
                        SELECT
                            GROUP_CONCAT(i2.interest SEPARATOR ',')
                        FROM interests i2
                        WHERE survey.id = i2.survey_id
                        GROUP BY i2.survey_id
                    ) AS interests
                FROM 
                    survey
                LEFT JOIN 
                    interests
                ON 
                    survey.id = interests.survey_id
                WHERE ?
                GROUP BY 
                    survey.id
                ORDER BY ? ?
                LIMIT ?
                OFFSET ?",
            $filter->filters,
            new SqlLiteral($order->column),
            new SqlLiteral($order->direction->value),
            $limit,
            $offset
        );

        return $data->fetchAll();
    }

    public function getSuveysCount(FilterDTO $filter): int
    {
        $countQuery = $this->explorer->query(
            "SELECT
                        COUNT(DISTINCT survey.id) AS cnt
                    FROM survey
                    LEFT JOIN 
                        interests
                    ON 
                        survey.id = interests.survey_id
                    WHERE ?",
            $filter->filters
        );
        return $countQuery->fetchField();
    }
}