<?php

namespace KupShop\IndexedFilterBundle\Inspection;

use KupShop\SystemInspectionBundle\Inspections\Inspection;
use KupShop\SystemInspectionBundle\Inspections\User\UserInspectionInterface;
use KupShop\SystemInspectionBundle\InspectionWriters\MessageTypes\SimpleMessage;
use Query\Operator;

class FilterUrlDuplicateInspection extends Inspection implements UserInspectionInterface
{
    public function runInspection(): ?array
    {
        $tableMappings = [];
        if (findModule(\Modules::PRODUCTS_PARAMETERS)) {
            $tableMappings['parameters_list'] = 'Hodnota parametru';
        }
        if (findModule(\Modules::PRODUCTS_SECTIONS)) {
            $tableMappings['sections'] = 'Sekce';
        }
        if (findModule(\Modules::PRODUCTS_VARIATIONS)) {
            $tableMappings['products_variations_choices_values'] = 'Hodnoda jmenovky variant';
        }
        if (findModule(\Modules::PRODUCERS)) {
            $tableMappings['producers'] = 'Výrobce';
        }

        $urls = [];

        foreach ($tableMappings as $table => $humanReadableName) {
            $qb = sqlQueryBuilder()
               ->select('filter_url')
               ->from($table);

            switch ($table) {
                case 'producers':
                case 'sections':
                    $qb->addSelect('name');
                    break;
                case 'parameters_list':
                    $qb->join($table, 'parameters', 'par', "par.id = {$table}.id_parameter")
                        ->addSelect('par.name');
                    break;
                case 'products_variations_choices_values':
                    $qb->join($table, 'products_variations_choices_labels', 'pvcl', "pvcl.id = {$table}.id_label")
                        ->addSelect('pvcl.label as name');
            }

            if ($table == 'parameters_list') {
                $parameterIds = sqlQueryBuilder()
                   ->select('id_parameter')
                   ->from('parameters_sections')
                   ->where(Operator::equals(['to_title' => 'Y']))
                   ->groupBy('id_parameter')
                   ->execute()->fetchFirstColumn();

                $qb->where(Operator::inIntArray($parameterIds, 'id_parameter'));
            }

            if ($table == 'sections') {
                $idsFromParametersSections = sqlQueryBuilder()
                   ->select('id_section')
                   ->from('parameters_sections')
                   ->where(Operator::equals(['to_title' => 'Y']))
                   ->groupBy('id_section')
                   ->execute()->fetchFirstColumn();

                $idsFromProductsVariationsSections = sqlQueryBuilder()
                   ->select('id_section')
                   ->from('products_variations_sections')
                   ->where(Operator::equals(['to_title' => 'Y']))
                   ->groupBy('id_section')
                   ->execute()->fetchFirstColumn();

                $section_ids = array_merge($idsFromParametersSections, $idsFromProductsVariationsSections);

                $qb->where(Operator::inIntArray($section_ids, 'id'));
            }

            $qb->groupBy('filter_url');

            $results = $qb->execute()->fetchAll();

            foreach ($results as $result) {
                $urls[$result['filter_url']][] = "{$humanReadableName}: {$result['name']} = {$result['filter_url']}";
            }
        }

        $duplicated = [];

        foreach ($urls as $url => $tables) {
            if (count($tables) >= 2) {
                $duplicated[$url] = implode(', ', $tables);
            }
        }

        if ($duplicated) {
            return [
                new SimpleMessage('Detekováno '.count($duplicated)." duplicitních url v indexovaném filtru. Hrozí kolize ve filtru, upravte hodnoty 'url ve filtru'.",
                    $duplicated),
            ];
        }

        return null;
    }
}
