<?php

namespace Maestro\Core\DataTables;

use Illuminate\Database\Eloquent\Builder;

/**
 * B3: Applies validated payload to a DataTable query (filters, global search, sort, pagination).
 * All column references come from the resource whitelist (columns()); user input is never used as SQL.
 */
class DataTableQueryApplier
{
    /**
     * Supported filter operators per column type.
     *
     * @var array<string, array<int, string>>
     */
    private const SUPPORTED_OPERATORS_BY_TYPE = [
        // text-like
        'text' => ['contains', 'starts_with', 'ends_with', 'equals', 'not_equals'],
        'enum' => ['contains', 'starts_with', 'ends_with', 'equals', 'not_equals'],
        'badge' => ['contains', 'starts_with', 'ends_with', 'equals', 'not_equals'],
        // numeric
        'number' => ['equals', 'not_equals', 'lt', 'lte', 'gt', 'gte'],
        // dates
        'date' => ['date_equals', 'before', 'after', 'between'],
        // booleans
        'boolean' => ['equals', 'not_equals'],
    ];

    /**
     * Default operator per column type when no operator is provided.
     *
     * @var array<string, string>
     */
    private const DEFAULT_OPERATOR_BY_TYPE = [
        'text' => 'contains',
        'enum' => 'contains',
        'badge' => 'contains',
        'number' => 'equals',
        'date' => 'date_equals',
        'boolean' => 'equals',
    ];

    /**
     * @param  Builder  $query  Base Eloquent query from resource
     * @param  array<int, array<string, mixed>>  $columns  Resource column definitions (key, expr, type, sortable, searchable, filterable, filterMode)
     * @param  array<string, mixed>  $payload  Validated request (pagination, sort, visibleColumns, filters, globalSearch)
     * @param  callable(object): array  $transformRow
     * @param  DataTableResource|null  $resource  Optional resource for hooks like expandGlobalSearch
     * @return array{data: array<int, array>, meta: array{page: int, limit: int, total: int, sort: array<int, array{field: string, order: int}}}
     */
    public function apply(Builder $query, array $columns, array $payload, callable $transformRow, ?object $resource = null): array
    {
        $colMap = $this->buildColumnMap($columns);
        $visibleColumns = $this->sanitizeVisibleColumns(
            $payload['visibleColumns'] ?? [],
            $colMap
        );

        $workingQuery = clone $query;
        $this->applyFilters($workingQuery, $payload['filters'] ?? [], $colMap);
        $this->applyGlobalSearch($workingQuery, (string) ($payload['globalSearch'] ?? ''), $visibleColumns, $colMap, $resource);
        if ($resource !== null && method_exists($resource, 'expandMatchedGroups')) {
            $resource->expandMatchedGroups($query, $workingQuery);
        } else {
            $query = $workingQuery;
        }
        $appliedSort = $this->applySort($query, $payload['sort'] ?? [], $colMap);

        $page = (int) (($payload['pagination']['page'] ?? 1) ?: 1);
        $limit = (int) (($payload['pagination']['limit'] ?? 25) ?: 25);
        $page = max(1, $page);
        $limit = max(1, min(200, $limit));

        // Count distinct primary ids while ignoring any GROUP BY that resources may add
        // for display purposes (e.g. joins that would otherwise duplicate rows).
        $countQuery = clone $query;
        $base = $countQuery->getQuery();
        $base->groups = null;
        $idCol = $this->qualifiedIdColumn($countQuery);
        $total = $countQuery->distinct($idCol)->count($idCol);
        $data = $query->forPage($page, $limit)->get();
        $transformed = $data->map(fn (object $model) => $transformRow($model))->values()->all();

        return [
            'data' => $transformed,
            'meta' => [
                'page' => $page,
                'limit' => $limit,
                'total' => $total,
                'sort' => $appliedSort,
            ],
        ];
    }

    /**
     * @return array<string, array<string, mixed>>
     */
    private function buildColumnMap(array $columns): array
    {
        $map = [];
        foreach ($columns as $def) {
            if (isset($def['key']) && is_string($def['key'])) {
                $map[$def['key']] = $def;
            }
        }

        return $map;
    }

    /**
     * @param  array<int, string>  $visibleColumns
     * @return array<int, string>
     */
    private function sanitizeVisibleColumns(array $visibleColumns, array $colMap): array
    {
        $out = [];
        foreach ($visibleColumns as $key) {
            if (is_string($key) && isset($colMap[$key])) {
                $out[] = $key;
            }
        }

        return $out;
    }

    private function applyFilters(Builder $query, array $filters, array $colMap): void
    {
        foreach ($filters as $key => $filter) {
            if (! is_string($key) || ! isset($colMap[$key])) {
                continue;
            }
            $def = $colMap[$key];
            if (empty($def['filterable'])) {
                continue;
            }
            if (! is_array($filter) || ! array_key_exists('value', $filter)) {
                continue;
            }
            $value = $filter['value'];
            if (is_object($value)) {
                continue;
            }
            $expr = $def['expr'] ?? null;
            if (! is_string($expr) && empty($def['filterHandler'])) {
                continue;
            }
            $type = $def['type'] ?? 'text';

            $requestedOp = null;
            if (isset($filter['op']) && is_string($filter['op'])) {
                $requestedOp = $filter['op'];
            }

            $allowedOperators = $this->resolveAllowedOperators($def);
            $defaultOp = $this->resolveDefaultOperator($def);

            $op = $requestedOp !== null && in_array($requestedOp, $allowedOperators, true)
                ? $requestedOp
                : $defaultOp;

            if (! in_array($op, $allowedOperators, true)) {
                // Safety guard: if the resolved operator is not allowed, skip this filter.
                continue;
            }

            // Optional per-column handler for complex filters (e.g. many-to-many relation filters).
            if (isset($def['filterHandler']) && is_callable($def['filterHandler'])) {
                ($def['filterHandler'])($query, $op, $value, $def);

                continue;
            }

            if ($value === null || $value === '' || (is_array($value) && $value === [])) {
                continue;
            }

            $this->applyFilterOperator($query, $expr, $type, $op, $value);
        }
    }

    /**
     * @param  array<string, mixed>  $def
     * @return array<int, string>
     */
    private function resolveAllowedOperators(array $def): array
    {
        $type = $def['type'] ?? 'text';
        $base = self::SUPPORTED_OPERATORS_BY_TYPE[$type] ?? self::SUPPORTED_OPERATORS_BY_TYPE['text'];

        if (! empty($def['allowedOperators']) && is_array($def['allowedOperators'])) {
            $whitelist = array_values(
                array_filter(
                    $def['allowedOperators'],
                    static fn ($op): bool => is_string($op)
                )
            );
            if ($whitelist !== []) {
                return array_values(array_intersect($base, $whitelist));
            }
        }

        return $base;
    }

    /**
     * @param  array<string, mixed>  $def
     */
    private function resolveDefaultOperator(array $def): string
    {
        if (isset($def['filterMode']) && is_string($def['filterMode'])) {
            return $def['filterMode'];
        }
        $type = $def['type'] ?? 'text';

        return self::DEFAULT_OPERATOR_BY_TYPE[$type] ?? 'contains';
    }

    /**
     * Apply a single filter condition based on (type, operator).
     */
    private function applyFilterOperator(Builder $query, string $expr, string $type, string $op, mixed $value): void
    {
        // Normalize type aliases (badge behaves like text for filtering).
        if ($type === 'badge') {
            $type = 'text';
        }

        if ($type === 'text' || $type === 'enum') {
            $this->applyTextFilter($query, $expr, $op, $value);

            return;
        }

        if ($type === 'number') {
            $this->applyNumberFilter($query, $expr, $op, $value);

            return;
        }

        if ($type === 'date') {
            $this->applyDateFilter($query, $expr, $op, $value);

            return;
        }

        if ($type === 'boolean') {
            $this->applyBooleanFilter($query, $expr, $op, $value);

            return;
        }

        // Fallback: treat as text.
        $this->applyTextFilter($query, $expr, $op, $value);
    }

    /**
     * @return array<int, string>
     */
    private function normalizeStringList(mixed $value): array
    {
        if (! is_array($value)) {
            $v = trim((string) $value);

            return $v === '' ? [] : [$v];
        }

        $out = [];
        foreach ($value as $item) {
            if (is_array($item) || is_object($item)) {
                continue;
            }
            $s = trim((string) $item);
            if ($s !== '') {
                $out[] = $s;
            }
        }

        // De-duplicate while preserving order.
        $seen = [];
        $unique = [];
        foreach ($out as $s) {
            $k = mb_strtolower($s);
            if (isset($seen[$k])) {
                continue;
            }
            $seen[$k] = true;
            $unique[] = $s;
        }

        return $unique;
    }

    private function applyTextFilter(Builder $query, string $expr, string $op, mixed $value): void
    {
        $values = $this->normalizeStringList($value);
        if ($values === []) {
            return;
        }

        // Multi-value support for equals / not_equals: LOWER(expr) IN (...) semantics.
        if (($op === 'equals' || $op === 'not_equals') && count($values) > 1) {
            $lowered = array_map(static fn (string $v): string => mb_strtolower($v), $values);
            $placeholders = implode(', ', array_fill(0, count($lowered), '?'));
            $sql = 'LOWER('.$expr.') '.($op === 'equals' ? 'IN' : 'NOT IN').' ('.$placeholders.')';
            $query->whereRaw($sql, $lowered);

            return;
        }

        $term = mb_strtolower((string) $values[0]);
        $likeContains = '%'.$term.'%';
        $likeStartsWith = $term.'%';
        $likeEndsWith = '%'.$term;

        if ($op === 'contains') {
            $query->whereRaw('LOWER('.$expr.') LIKE ?', [$likeContains]);

            return;
        }

        if ($op === 'starts_with') {
            $query->whereRaw('LOWER('.$expr.') LIKE ?', [$likeStartsWith]);

            return;
        }

        if ($op === 'ends_with') {
            $query->whereRaw('LOWER('.$expr.') LIKE ?', [$likeEndsWith]);

            return;
        }

        if ($op === 'equals') {
            $query->whereRaw('LOWER('.$expr.') = ?', [$term]);

            return;
        }

        if ($op === 'not_equals') {
            $query->whereRaw('LOWER('.$expr.') != ?', [$term]);

            return;
        }
    }

    private function applyNumberFilter(Builder $query, string $expr, string $op, mixed $value): void
    {
        if (! is_numeric($value)) {
            return;
        }

        $number = $value + 0;

        match ($op) {
            'equals' => $query->where($expr, '=', $number),
            'not_equals' => $query->where($expr, '!=', $number),
            'lt' => $query->where($expr, '<', $number),
            'lte' => $query->where($expr, '<=', $number),
            'gt' => $query->where($expr, '>', $number),
            'gte' => $query->where($expr, '>=', $number),
            default => null,
        };
    }

    private function applyDateFilter(Builder $query, string $expr, string $op, mixed $value): void
    {
        $stringValue = trim((string) $value);
        if ($stringValue === '') {
            return;
        }

        if ($op === 'date_equals') {
            $query->whereDate($expr, '=', $stringValue);

            return;
        }

        if ($op === 'before') {
            $query->whereDate($expr, '<', $stringValue);

            return;
        }

        if ($op === 'after') {
            $query->whereDate($expr, '>', $stringValue);

            return;
        }

        if ($op === 'between') {
            // Expect "start|end" (kept scalar for request validation).
            [$start, $end] = array_pad(explode('|', $stringValue, 2), 2, null);
            $start = $start !== null ? trim($start) : '';
            $end = $end !== null ? trim($end) : '';

            if ($start === '' || $end === '') {
                return;
            }

            $query
                ->whereDate($expr, '>=', $start)
                ->whereDate($expr, '<=', $end);
        }
    }

    private function applyBooleanFilter(Builder $query, string $expr, string $op, mixed $value): void
    {
        $bool = filter_var($value, FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE);
        if ($bool === null) {
            return;
        }

        if ($op === 'equals') {
            $query->where($expr, '=', $bool);

            return;
        }

        if ($op === 'not_equals') {
            $query->where($expr, '!=', $bool);
        }
    }

    /**
     * @param  array<int, string>  $visibleColumns
     */
    private function applyGlobalSearch(Builder $query, string $term, array $visibleColumns, array $colMap, ?object $resource = null): void
    {
        $term = trim($term);
        if ($term === '') {
            return;
        }
        $searchTerm = '%'.mb_strtolower($term).'%';
        $keys = [];
        foreach ($visibleColumns as $key) {
            if (! isset($colMap[$key])) {
                continue;
            }
            $def = $colMap[$key];
            if (empty($def['searchable'])) {
                continue;
            }
            $type = $def['type'] ?? 'text';
            if ($type === 'text' || $type === 'enum') {
                $keys[] = $key;
            }
        }
        if ($keys === []) {
            return;
        }
        $query->where(function (Builder $q) use ($keys, $searchTerm, $colMap, $resource, $term): void {
            foreach ($keys as $key) {
                $expr = $colMap[$key]['expr'] ?? null;
                if (is_string($expr)) {
                    $q->orWhereRaw('LOWER('.$expr.') LIKE ?', [$searchTerm]);
                }
            }
            if ($resource !== null && method_exists($resource, 'expandGlobalSearch')) {
                $resource->expandGlobalSearch($q, $term);
            }
        });
    }

    /**
     * Qualified id column for order (avoids ambiguity when query has joins).
     */
    private function qualifiedIdColumn(Builder $query): string
    {
        $model = $query->getModel();

        return $model ? $model->getTable().'.id' : 'id';
    }

    /**
     * Apply first valid sort; add stable tie-breaker. Returns applied sort or default.
     *
     * @return array<int, array{field: string, order: int}>
     */
    private function applySort(Builder $query, array $sort, array $colMap): array
    {
        $first = null;
        foreach ($sort as $item) {
            if (! is_array($item) || ! isset($item['field'], $item['order'])) {
                continue;
            }
            $field = $item['field'];
            $order = (int) $item['order'];
            if (! isset($colMap[$field]) || empty($colMap[$field]['sortable'])) {
                continue;
            }
            $direction = $order === 1 ? 'asc' : 'desc';

            $sortExpressions = $colMap[$field]['sortExpressions'] ?? null;
            if (is_array($sortExpressions) && $sortExpressions !== []) {
                foreach ($sortExpressions as $sortExpr) {
                    if (preg_match('/\s+(ASC|DESC)$/i', $sortExpr, $m)) {
                        $query->orderByRaw($sortExpr);
                    } else {
                        $query->orderByRaw($sortExpr.' '.$direction);
                    }
                }
            } else {
                $expr = $colMap[$field]['expr'] ?? null;
                if (! is_string($expr)) {
                    continue;
                }
                $query->orderBy($expr, $direction);
            }

            $idCol = $this->qualifiedIdColumn($query);
            $query->orderBy($idCol, 'desc');

            return [['field' => $field, 'order' => $order]];
        }
        $query->orderBy($this->qualifiedIdColumn($query), 'desc');

        return [];
    }

    /**
     * Apply filters, global search and sort for export (no pagination).
     * Returns the same query instance for chaining.
     *
     * @param  array<int, array<string, mixed>>  $columns
     * @param  array<string, mixed>  $payload  Validated export payload (visibleColumns, filters, globalSearch, sort)
     */
    public function applyExport(Builder $query, array $columns, array $payload, ?object $resource = null): Builder
    {
        $colMap = $this->buildColumnMap($columns);
        $visibleColumns = $this->sanitizeVisibleColumns(
            $payload['visibleColumns'] ?? [],
            $colMap
        );

        $workingQuery = clone $query;
        $this->applyFilters($workingQuery, $payload['filters'] ?? [], $colMap);
        $this->applyGlobalSearch($workingQuery, (string) ($payload['globalSearch'] ?? ''), $visibleColumns, $colMap, $resource);
        if ($resource !== null && method_exists($resource, 'expandMatchedGroups')) {
            $resource->expandMatchedGroups($query, $workingQuery);
        } else {
            $query = $workingQuery;
        }
        $this->applySort($query, $payload['sort'] ?? [], $colMap);

        return $query;
    }

    /**
     * Return ordered list of column keys to export: visibleColumns ordered by columnOrder, whitelisted by resource columns.
     *
     * @param  array<int, array<string, mixed>>  $columns
     * @param  array<string, mixed>  $payload
     * @return array<int, string>
     */
    public function orderedExportColumns(array $columns, array $payload): array
    {
        $colMap = $this->buildColumnMap($columns);
        $visible = $this->sanitizeVisibleColumns($payload['visibleColumns'] ?? [], $colMap);
        if ($visible === []) {
            return [];
        }
        $visibleSet = array_flip($visible);
        $order = $payload['columnOrder'] ?? [];
        $ordered = [];
        foreach ($order as $key) {
            if (is_string($key) && isset($visibleSet[$key])) {
                $ordered[] = $key;
                unset($visibleSet[$key]);
            }
        }
        foreach (array_keys($visibleSet) as $key) {
            $ordered[] = $key;
        }

        return $ordered;
    }
}
