strprfcrm/app/Http/Controllers/FilterController.php

<?php

namespace App\Http\Controllers;

use App\Http\Requests\FilterRequest;
use App\Models\CommonCatalogItem;
use App\Models\SparePartsView;
use App\Models\StockOrder;
use Illuminate\Http\JsonResponse;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class FilterController extends Controller
{
    const DB_TABLES = [
        'orders' => 'orders_view',
        'product_sku' => 'mafs_view',
        'products' => 'products',
        'reclamations' => 'reclamations_view',
        'maf_order' => 'maf_orders_view',
        'import' => 'imports',
        'responsibles' => 'responsibles_view',
        'users' => 'users',
        'contracts' => 'contracts',
        'spare_parts' => 'spare_parts_view',
        'spare_part_orders' => 'spare_part_orders_view',
        'notifications' => 'user_notifications',
        'notification_logs' => 'notification_delivery_logs',
        'common_catalog_items' => 'common_catalog_items',
        'stock_availability' => 'common_catalog_items',
        'stock_orders' => 'stock_orders',
        'schedule_orders' => 'production_orders',
    ];

    const SKIP_YEAR_FILTER = [
        'reclamations',
        'notifications',
        'notification_logs',
    ];

    /**
     * Маппинг виртуальных столбцов (аксессоров Eloquent) на реальные столбцы БД.
     * Ключ — имя таблицы из DB_TABLES, значение — массив 'виртуальный_столбец' => 'реальный_столбец'.
     */
    const COLUMN_MAP = [
        'spare_part_orders' => [
            'status_name' => 'status',
            'with_documents_text' => 'with_documents',
        ],
        'spare_parts' => [
            'customer_price_txt' => 'customer_price',
            'expertise_price_txt' => 'expertise_price',
            'purchase_price_txt' => 'purchase_price',
        ],
        'products' => [
            'product_price_txt' => 'product_price',
            'installation_price_txt' => 'installation_price',
            'total_price_txt' => 'total_price',
        ],
        'responsibles' => [
            'area-name' => 'area_name',
        ],
        'common_catalog_items' => [
            'calculator_enabled_txt' => 'calculator_enabled',
            'builders_price_txt' => 'builders_price',
            'wholesale_price_txt' => 'wholesale_price',
            'recommended_price_txt' => 'recommended_price',
            'retail_price_txt' => 'retail_price',
            'project_price_txt' => 'project_price',
            'project_with_installation_price_txt' => 'project_with_installation_price',
            'pik_price_txt' => 'pik_price',
            'recommended_plus_10_price_txt' => 'recommended_plus_10_price',
        ],
        'stock_orders' => [
            'status_name' => 'status',
        ],
        'schedule_orders' => [
            'status_name' => 'status',
            'execution_type_name' => 'execution_type',
            'delivery_dates' => 'delivery_date',
            'installation_dates' => 'installation_date',
        ],
    ];

    /**
     * Маппинг значений: реальное значение БД => отображаемое значение.
     * Ключ — имя таблицы, затем реальный столбец.
     */
    const VALUE_MAP = [
        'users' => [
            'role' => \App\Models\Role::NAMES,
        ],
        'notifications' => [
            'type' => [
                'platform' => 'Площадки',
                'reclamation' => 'Рекламации',
                'schedule' => 'График монтажей',
                'production_order' => 'График заказов',
            ],
            'event' => [
                'created' => 'Создание',
                'status_changed' => 'Смена статуса',
                'schedule_added' => 'Добавлено в график',
                'delivery_added' => 'Добавлена доставка',
                'installation_added' => 'Добавлен монтаж',
                'reclamation_added' => 'Добавлена рекламация',
            ],
        ],
        'spare_part_orders' => [
            'with_documents' => [
                0 => 'Нет',
                1 => 'Да',
            ],
            'status' => [
                'ordered' => 'Заказано',
                'in_stock' => 'На складе',
                'shipped' => 'Отгружено',
            ],
        ],
        'notification_logs' => [
            'channel' => [
                'in_app' => 'Браузер',
                'browser' => 'Браузер',
                'push' => 'Android/iOS',
                'email' => 'Email',
            ],
            'status' => [
                'sent' => 'Отправлено',
                'failed' => 'Ошибка',
                'skipped' => 'Пропущено',
                'dead_letter' => 'Dead letter',
            ],
        ],
        'common_catalog_items' => [
            'calculator_enabled' => [
                0 => 'нет',
                1 => 'да',
            ],
        ],
        'stock_orders' => [
            'status' => StockOrder::STATUS_NAMES,
        ],
        'schedule_orders' => [
            'status' => \App\Enums\ProductionOrderStatus::LABELS,
            'execution_type' => \App\Enums\ProductionOrderExecutionType::LABELS,
        ],
    ];

    private const FIELD_ACCESS_MODULES = [
        'common_catalog_items' => 'common-catalog',
        'schedule_orders' => 'schedule-orders',
    ];

    private const SESSION_KEYS = [
        'common_catalog_items' => 'gp_common_catalog',
    ];

    public function getFilters(FilterRequest $request): JsonResponse
    {
        $table = $request->validated('table');
        $column = $request->validated('column');
        if (! array_key_exists($table, self::DB_TABLES)) {
            abort(400, 'Table not found');
        }

        $this->assertCanViewColumn($request, $table, $column);

        if ($table === 'stock_availability') {
            return $this->stockAvailabilityFilters($column);
        }
        if ($table === 'stock_orders') {
            return $this->stockOrderFilters($column);
        }
        if ($table === 'schedule_orders' && in_array($column, ['delivery_dates', 'installation_dates'], true)) {
            return $this->productionOrderPlanningFilters($column);
        }

        $gp = session(self::SESSION_KEYS[$table] ?? 'gp_'.$table);

        if ($table === 'spare_parts' && $column === 'pricing_codes_list') {
            $result = DB::table('pricing_codes as pc')
                ->join('spare_part_pricing_code as sppc', 'sppc.pricing_code_id', '=', 'pc.id')
                ->select('pc.code')
                ->distinct()
                ->orderBy('pc.code')
                ->pluck('pc.code')
                ->toArray();

            $hasEmptyValue = SparePartsView::query()
                ->doesntHave('pricingCodes')
                ->exists();

            if ($hasEmptyValue) {
                array_unshift($result, '-пусто-');
            }

            return response()->json($result, 200, [], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
        }

        $dbTable = self::DB_TABLES[$table];

        // Определяем реальный столбец БД
        $dbColumn = self::resolveDbColumn($table, $dbTable, $column);

        if ($dbColumn && Schema::hasColumn($dbTable, $dbColumn)) {
            $normalizedColumn = self::normalizedSelectExpression($dbColumn);
            $q = DB::table($dbTable)->selectRaw($normalizedColumn.' as filter_value')->distinct();
            if (! in_array($table, self::SKIP_YEAR_FILTER) && Schema::hasColumn($dbTable, 'year')) {
                $q->where('year', year());
            }
            if (Schema::hasColumn($dbTable, 'deleted_at')) {
                $q->whereNull('deleted_at');
            }

            if (isset($gp['filters']) && is_array($gp['filters']) && count($gp['filters'])) {
                foreach ($gp['filters'] as $colName => $vals) {
                    if ($colName === $column) {
                        continue;
                    }
                    $filterDbColumn = self::resolveDbColumn($table, $dbTable, $colName);
                    if (! $filterDbColumn || ! Schema::hasColumn($dbTable, $filterDbColumn)) {
                        continue;
                    }
                    $q->where(function ($query) use ($filterDbColumn, $vals) {
                        foreach (explode('||', $vals) as $val) {
                            if ($val == '-пусто-') {
                                self::applyEmptyFilterConditionForFilterQuery($query, $filterDbColumn);
                            } else {
                                $query->orWhere($filterDbColumn, '=', $val);
                            }
                        }
                    });
                }
            }

            $result = $q->orderBy('filter_value')->get()->pluck('filter_value')->toArray();

            // Конвертация цен из копеек в рубли для отображения
            if (str_ends_with($dbColumn, '_price')) {
                $result = array_map(function ($val) {
                    if ($val === null || $val === '-пусто-') {
                        return $val;
                    }

                    return $val / 100;
                }, $result);
            }

            // Применяем маппинг значений, если есть
            if (isset(self::VALUE_MAP[$table][$dbColumn])) {
                $map = self::VALUE_MAP[$table][$dbColumn];
                $result = array_map(fn ($val) => $map[$val] ?? $val, $result);
            }
        } else {
            $result = [];
        }

        return response()->json($result, 200, [], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
    }

    private static function normalizedSelectExpression(string $column): string
    {
        return "CASE WHEN {$column} IS NULL OR TRIM(CAST({$column} AS CHAR)) = '' THEN '-пусто-' ELSE CAST({$column} AS CHAR) END";
    }

    private static function applyEmptyFilterConditionForFilterQuery($query, string $column): void
    {
        $query->orWhereNull($column)
            ->orWhereRaw("TRIM(CAST({$column} AS CHAR)) = ''");
    }

    private function assertCanViewColumn(FilterRequest $request, string $table, string $column): void
    {
        if ($table === 'stock_availability') {
            abort_unless($request->user()->hasPermission('stock.view'), 403);

            return;
        }
        if ($table === 'stock_orders') {
            abort_unless($request->user()->hasPermission('stock.orders.view'), 403);

            return;
        }

        $module = self::FIELD_ACCESS_MODULES[$table] ?? null;
        if ($module === null) {
            return;
        }

        $field = self::COLUMN_MAP[$table][$column] ?? $column;
        abort_unless($request->user()->canViewField($module, $field), 403);
    }

    private function stockAvailabilityFilters(string $column): JsonResponse
    {
        $values = match ($column) {
            'article', 'calculator_name', 'kind', 'unit' => CommonCatalogItem::query()
                ->whereHas('stockOrders')
                ->pluck($column),
            'latest_order_note' => CommonCatalogItem::query()
                ->whereHas('stockOrders')
                ->with('latestStockOrder:id,common_catalog_item_id,note')
                ->get()
                ->pluck('latestStockOrder.note'),
            default => [],
        };

        return $this->filterValuesResponse($values);
    }

    private function stockOrderFilters(string $column): JsonResponse
    {
        $values = match ($column) {
            'order_number', 'note' => StockOrder::query()->pluck($column),
            'created_at' => StockOrder::query()
                ->selectRaw('DATE(created_at) as filter_date')
                ->pluck('filter_date'),
            'item_article' => StockOrder::query()
                ->join('common_catalog_items', 'common_catalog_items.id', '=', 'stock_orders.common_catalog_item_id')
                ->pluck('common_catalog_items.article'),
            'status_name' => StockOrder::query()
                ->pluck('status')
                ->map(fn (string $status): string => StockOrder::STATUS_NAMES[$status] ?? $status),
            default => [],
        };

        return $this->filterValuesResponse($values);
    }

    private function productionOrderPlanningFilters(string $column): JsonResponse
    {
        $values = match ($column) {
            'delivery_dates' => DB::table('production_order_deliveries')
                ->whereNull('deleted_at')
                ->pluck('delivery_date'),
            'installation_dates' => DB::table('production_order_installations')
                ->whereNull('deleted_at')
                ->pluck('installation_date'),
            default => [],
        };

        return $this->filterValuesResponse($values);
    }

    private function filterValuesResponse(iterable $values): JsonResponse
    {
        $normalized = collect($values)
            ->map(static fn (mixed $value): string => $value === null || trim((string) $value) === ''
                ? '-пусто-'
                : (string) $value)
            ->unique()
            ->sort(static fn (string $left, string $right): int => strnatcasecmp($left, $right))
            ->values()
            ->all();

        return response()->json($normalized, 200, [], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
    }

    /**
     * Определяет реальный столбец БД по имени столбца из заголовка.
     * Приоритет: прямое совпадение в БД → COLUMN_MAP для конкретной таблицы.
     */
    public static function resolveDbColumn(string $table, string $dbTable, string $column): ?string
    {
        // 1. Прямое совпадение — столбец существует в БД
        if (Schema::hasColumn($dbTable, $column)) {
            return $column;
        }

        // 2. Явный маппинг для конкретной таблицы
        if (isset(self::COLUMN_MAP[$table][$column])) {
            return self::COLUMN_MAP[$table][$column];
        }

        return null;
    }
}