<?php

namespace App\Services\Client;

use Illuminate\Database\Eloquent\Builder;

/**
 * Shared client-filter applier.
 *
 * The Client list page, the Bulk Email Station preview, and the bulk fan-out job
 * all build the identical filtered query through this class, so filter semantics
 * stay in one place.  Supported keys (all optional):
 *   search, status_id, days_range, tranche_id, lead_source_id, url,
 *   tag_id, priority, date_from, date_to, handler, care_setting_id
 *
 * Categorical filters accept EITHER a scalar (legacy single-select, e.g. the
 * Client list page) OR an array (multi-select, e.g. the Bulk Email Station).
 * Scalars keep their exact original semantics; arrays use `whereIn`.
 */
class ClientFilter
{
    public function apply(Builder $query, array $f): Builder
    {
        // --- search (scalar) ---
        $rawSearch = trim((string) ($f['search'] ?? ''));
        if ($rawSearch !== '') {
            $search = $rawSearch;
            if (str_starts_with($search, '07')) {
                $search = '+44' . substr($search, 1);
            }

            // Keep the numeric-id lookup INSIDE the search closure — a top-level
            // orWhere here would otherwise escape any surrounding (tenant/chunk) scope.
            $query->where(function ($q) use ($search, $rawSearch) {
                $q->where('name_first', 'like', '%' . $search . '%')
                    ->orWhere('email_address', 'like', '%' . $search . '%')
                    ->orWhereRaw("CONCAT(name_first, ' ', name_last) LIKE ?", ["%{$search}%"])
                    ->orWhere('phone_number', 'like', '%' . $search . '%')
                    ->orWhere('name_last', 'like', '%' . $search . '%');

                if (is_numeric($rawSearch)) {
                    $q->orWhere('id', $search - 100000);
                }
            });
        }

        // --- status (scalar keeps the days-in-status sub-filter; array → whereIn) ---
        // --- status ---
        $statusList = $this->asList($f['status_id'] ?? null);

        if ($statusList !== null) {
            if ($statusList) {
                $query->whereHas('claim', fn ($c) => $c->whereIn('status_id', $statusList));
            }
        } elseif (!empty($f['status_id'])) {
            // --- status ---
        $statusList = $this->asList($f['status_id'] ?? null);
        $daysRange = (string) ($f['days_range'] ?? '');
        $statusFilterType = $f['status_filter_type'] ?? 'current';

        if ($statusList !== null) {
            if ($statusList) {
                $query->whereHas('claim', fn ($c) => $c->whereIn('status_id', $statusList));
            }
        } elseif (!empty($f['status_id'])) {
            $statusId = $f['status_id'];

            if ($statusFilterType === 'history') {
               
                $query->whereHas('claim', function ($claims) use ($statusId, $daysRange) {
                    $solicitor = app(\App\Models\Solicitor\Solicitor::class);
                    $claims->where('status_id', $statusId)
                        ->whereHas('statusHistory', function ($sh) use ($statusId, $daysRange, $solicitor) {
                            $sh->where('status_id', $statusId)
                                ->where('solicitor_id', $solicitor->id)
                                ->whereNull('ended_at');

                            match ($daysRange) {
                                'today' => $sh->whereDate('started_at', today()),
                                'week'  => $sh->whereRaw("DATEDIFF(NOW(), started_at) BETWEEN 2 AND 7"),
                                'month' => $sh->whereRaw("DATEDIFF(NOW(), started_at) BETWEEN 8 AND 30"),
                                '60'    => $sh->whereRaw("DATEDIFF(NOW(), started_at) BETWEEN 31 AND 60"),
                                '90'    => $sh->whereRaw("DATEDIFF(NOW(), started_at) BETWEEN 61 AND 90"),
                                'old'   => $sh->whereRaw("DATEDIFF(NOW(), started_at) > 90"),
                                default => null,
                            };
                        });
                });
            } else {
                $query->whereHas('claim', fn ($claims) => $claims->where('status_id', $statusId));
            }
        }
        }

        // --- tranche (0 = "None Assigned" → NULL) ---
        $trancheList = $this->asList($f['tranche_id'] ?? null);
        if ($trancheList !== null) {
            if ($trancheList) {
                $this->whereInOrNull($query, 'tranche_id', $trancheList);
            }
        } elseif (is_numeric($f['tranche_id'] ?? null)) {
            $tranche = $f['tranche_id'] ?: null;
            $query->where('tranche_id', $tranche);
        }

        // --- lead source ---
        $leadList = $this->asList($f['lead_source_id'] ?? null);
        if ($leadList !== null) {
            if ($leadList) {
                $this->whereInOrNull($query, 'lead_source_id', $leadList);
            }
        } elseif (($f['lead_source_id'] ?? null) !== null) {
            if ($f['lead_source_id']) {
                $query->where('lead_source_id', $f['lead_source_id']);
            } else {
                $query->whereNull('lead_source_id');
            }
        }

        // --- url / endpoint (0 = "None Assigned" → NULL) ---
        $urlList = $this->asList($f['url'] ?? null);
        if ($urlList !== null) {
            if ($urlList) {
                $this->whereInOrNull($query, 'lead_source_endpoint_id', $urlList);
            }
        } elseif (($f['url'] ?? null) !== null) {
            if ($f['url']) {
                $query->where('lead_source_endpoint_id', $f['url']);
            } else {
                $query->whereNull('lead_source_endpoint_id');
            }
        }

        // --- tags ---
        $tagList = $this->asList($f['tag_id'] ?? null);
        if ($tagList !== null) {
            if ($tagList) {
                $query->whereHas('claim.tags', fn ($tags) => $tags->whereIn('tags.id', $tagList));
            }
        } elseif (!empty($f['tag_id'])) {
            $tagId = $f['tag_id'];
            $query->whereHas('claim.tags', fn ($tags) => $tags->where('tags.id', $tagId));
        }

        // --- priority (1 = high, 2 = low; both/neither selected = no constraint) ---
        $priorityList = $this->asList($f['priority'] ?? null);
        if ($priorityList !== null) {
            $wantsHigh = $this->listHas($priorityList, 1);
            $wantsLow = $this->listHas($priorityList, 2);
            if ($wantsHigh !== $wantsLow) {
                $query->where(fn ($pq) => $wantsHigh
                    ? $this->highPriority($pq)
                    : $this->lowPriority($pq));
            }
        } elseif (!empty($f['priority'])) {
            $priority = $f['priority'];
            $query->where(function ($priorityQuery) use ($priority) {
                if ($priority == 1) {
                    $this->highPriority($priorityQuery);
                } elseif ($priority == 2) {
                    $this->lowPriority($priorityQuery);
                }
            });
        }

        // --- created date range (scalar) ---
        if (!empty($f['date_from'])) {
            $query->whereDate('created_at', '>=', $f['date_from']);
        }
        if (!empty($f['date_to'])) {
            $query->whereDate('created_at', '<=', $f['date_to']);
        }

        // --- handler (0 = unassigned cases) ---
        $handlerList = $this->asList($f['handler'] ?? null);

        if ($handlerList !== null) {
            if ($handlerList) {
                $wantsUnassigned = $this->listHas($handlerList, 0);

                $handlerIds = array_values(array_filter(
                    $handlerList,
                    fn ($handlerId) => (string) $handlerId !== '0'
                ));

                $query->where(function (Builder $handlerQuery) use ($handlerIds, $wantsUnassigned) {
                    if ($handlerIds) {
                        $handlerQuery->whereHas(
                            'handlers',
                            fn ($handlers) => $handlers->whereIn('user_id', $handlerIds)
                        );
                    }

                    if ($wantsUnassigned) {
                        $handlerIds
                            ? $handlerQuery->orWhereDoesntHave('handlers')
                            : $handlerQuery->whereDoesntHave('handlers');
                    }
                });
            }
        } elseif (($f['handler'] ?? null) !== null && $f['handler'] !== '') {
            $handler = $f['handler'];

            if ((string) $handler === '0') {
                $query->whereDoesntHave('handlers');
            } else {
                $query->whereHas(
                    'handlers',
                    fn ($handlers) => $handlers->where('user_id', $handler)
                );
            }
        }

        // --- care setting ---
        $careList = $this->asList($f['care_setting_id'] ?? null);
        if ($careList !== null) {
            if ($careList) {
                $query->whereHas('claim', fn ($claim) => $claim->whereHas(
                    'careSettings', fn ($cs) => $cs->whereIn('institution_id', $careList)
                ));
            }
        } elseif (!empty($f['care_setting_id'])) {
            $careSettingId = $f['care_setting_id'];
            $query->whereHas('claim', fn ($claim) => $claim->whereHas(
                'careSettings', fn ($cs) => $cs->where('institution_id', $careSettingId)
            ));
        }

        return $query;
    }

    /**
     * Constrain a client query to a single tenant explicitly.
     *
     * Required in console/queue contexts where the `solicitor` global scope is
     * skipped (see App\Trait\BelongsToSolicitor::bootBelongsToSolicitor).
     */
    public function applyTenant(Builder $query, int $solicitorId): Builder
    {
        return $query->withoutGlobalScope('solicitor')
            ->where('clients.solicitor_id', $solicitorId);
    }

    /** A client is high priority if terminally ill or aged 68+. */
    private function highPriority(Builder $q): Builder
    {
        return $q->whereHas('claim.terminalIllness')
            ->orWhereDate('clients.date_of_birth', '<', now()->subYears(68));
    }

    private function lowPriority(Builder $q): Builder
    {
        return $q->whereHas('claim', fn ($claim) => $claim->whereDoesntHave('terminalIllness'))
            ->whereDate('clients.date_of_birth', '>', now()->subYears(68));
    }

    /** Returns a cleaned list when $v is an array (multi-select), or null when it's a scalar (legacy single-select). */
    private function asList($v): ?array
    {
        if (!is_array($v)) {
            return null;
        }

        return array_values(array_filter($v, fn ($x) => $x !== '' && $x !== null));
    }

    private function listHas(array $list, int $needle): bool
    {
        foreach ($list as $v) {
            if ((int) $v === $needle) {
                return true;
            }
        }

        return false;
    }

    /** whereIn, treating a "0" sentinel as "NULL" (the "None Assigned" option). */
    private function whereInOrNull(Builder $query, string $column, array $vals): void
    {
        $wantsNull = false;
        $ids = [];
        foreach ($vals as $v) {
            if ((string) $v === '0') {
                $wantsNull = true;
            } else {
                $ids[] = $v;
            }
        }

        $query->where(function ($q) use ($column, $ids, $wantsNull) {
            if ($ids) {
                $q->whereIn($column, $ids);
            }
            if ($wantsNull) {
                $ids ? $q->orWhereNull($column) : $q->whereNull($column);
            }
        });
    }
}
