<?php

declare(strict_types=1);

namespace KupShop\CatalogBundle\Admin\Controller;

use KupShop\CatalogBundle\Query\Search;
use KupShop\KupShopBundle\Routing\AdminRoute;
use Query\Operator as Op;
use Query\QueryBuilder;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;

class SectionsAutocompleteController extends AbstractController
{
    #[AdminRoute(path: '/autocomplete/sections/{id}', methods: ['GET'])]
    public function getSubsections(Request $request, ?int $id = null): Response
    {
        if ($request->query->has('term')) {
            return $this->json($this->autocomplete($request->query->get('term')));
        }

        if ($request->query->get('all_subsections') == 1) {
            $qb = $this->getRecursiveQuery($id, 's');
        } else {
            $qb = sqlQueryBuilder()
                ->from('sections_relation', 'ss')
                ->leftJoin('ss', 'sections', 's', 's.id = ss.id_section');

            if (!isset($id)) {
                $qb->andWhere('s.id IN (SELECT DISTINCT id_section FROM sections_relation WHERE id_topsection IS NULL OR id_topsection = 0)');
            } else {
                $qb->andWhere(Op::equals(['id_topsection' => $id]));
            }
        }

        $qb->addSelect('s.id', 's.name', $this->hasSubsections('s.id'))
            ->andWhere("s.figure = 'Y'")
            ->addOrderBy('position');

        return $this->json($qb->execute()->fetchAllAssociative());
    }

    public function autocomplete(string $term): array
    {
        $qb = $this->getRecursiveQuery()
            ->select(
                'cte.id',
                'cte.name',
                'cte.path as full_path',
                $this->hasSubsections('cte.id'),
            )
            ->andWhere(Search::searchFields($term, [['field' => 'cte.path', 'match' => 'both']], 'OR'))
            ->setMaxResults(30);

        return $qb->execute()->fetchAllAssociative();
    }

    protected function getRecursiveQuery(?int $idTopSection = null, string $alias = 'cte'): QueryBuilder
    {
        $whereSection = $idTopSection ? 'id_topsection = :idTopSection' : 'id_topsection IS NULL';

        $recursiveQuery = "
            WITH RECURSIVE cte (id, id_topsection, path, name, figure, position, depth, full_position) as (
                SELECT     id_section,
                           id_topsection,
                           (SELECT name FROM sections WHERE id = id_section LIMIT 1) as path,
                           (SELECT name FROM sections WHERE id = id_section LIMIT 1) as name,
                           (SELECT figure FROM sections WHERE id = id_section LIMIT 1) as figure,
                           position,
                           1 AS depth,
                           CAST(LPAD(position, 5, 0) AS CHAR(500)) AS full_position
                FROM       sections_relation
                WHERE      {$whereSection}
                UNION ALL
                SELECT     sr.id_section,
                           sr.id_topsection,
                           CONCAT(cte.path,' > ',(SELECT name FROM sections WHERE id = sr.id_section LIMIT 1)) as path,
                           (SELECT name FROM sections WHERE id = sr.id_section LIMIT 1) as name,
                           (SELECT figure FROM sections WHERE id = sr.id_section LIMIT 1) as figure,
                           sr.position,
                           depth + 1,
                           CONCAT(full_position, '/', LPAD(sr.position, 5, 0))
                FROM       sections_relation sr
                INNER JOIN cte
                        on sr.id_topsection = cte.id
            ) SELECT * FROM cte";

        $qb = sqlQueryBuilder()->from('('.$recursiveQuery.')', $alias);

        if ($idTopSection !== null) {
            $qb->setParameter('idTopSection', $idTopSection);
        }

        return $qb;
    }

    private function hasSubsections(string $idField = 'id', string $as = 'has_subsections'): string
    {
        return "EXISTS (
            SELECT * 
                FROM sections_relation _ss 
                LEFT JOIN sections _s ON _s.id = _ss.id_section
                WHERE {$idField} = _ss.id_topsection 
                    AND _s.figure = 'Y'
        ) AS {$as}";
    }
}
