<?php

namespace App\Support;

use Illuminate\Support\Facades\DB;
use Illuminate\Pagination\LengthAwarePaginator;

class MultiTablePaginator
{
    protected array $tables = [];
    protected ?string $ingestedAtFrom = null;
    protected ?string $ingestedAtTo = null;
    protected ?string $createdAtFrom = null;
    protected ?string $createdAtTo = null;
    protected string $orderBy = 'created_at';
    protected string $direction = 'desc';

    public function addTable(string $name, string $model): static
    {
        $this->tables[] = compact('name', 'model');
        return $this;
    }

    public function filterByIngestedAt(?string $from, ?string $to): static
    {
        $this->ingestedAtFrom = $from;
        $this->ingestedAtTo = $to;
        return $this;
    }

    public function filterByCreatedAt(?string $from, ?string $to): static
    {
        $this->createdAtFrom = $from;
        $this->createdAtTo = $to;
        return $this;
    }

    public function orderBy(string $column, string $direction = 'desc'): static
    {
        $this->orderBy = $column;
        $this->direction = $direction;
        return $this;
    }

    public function paginate(int $perPage = 15, string $pageParam = 'page'): LengthAwarePaginator
    {
        $page = request()->input($pageParam, 1);

        $union = null;

        foreach ($this->tables as $table) {
            $query = DB::table($table['name'])
                ->selectRaw("id, '{$table['name']}' as table_name, {$this->orderBy}")
                ->when(
                    $this->ingestedAtFrom,
                    fn ($q) => $q->where('ingested_at', '>=', $this->ingestedAtFrom)
                )
                ->when(
                    $this->ingestedAtTo,
                    fn ($q) => $q->where('ingested_at', '<=', $this->ingestedAtTo)
                )
                ->when(
                    $this->createdAtFrom,
                    fn ($q) => $q->where('created_at', '>=', $this->createdAtFrom)
                )
                ->when(
                    $this->createdAtTo,
                    fn ($q) => $q->where('created_at', '<=', $this->createdAtTo)
                );

            $union ? $union->unionAll($query) : $union = $query;
        }

        $paginator = DB::query()
            ->fromSub($union, 'merged')
            ->orderBy($this->orderBy, $this->direction)
            ->paginate($perPage, ['*'], $pageParam, $page);

        $rows = $paginator->getCollection();

        $grouped = $rows->groupBy('table_name')
            ->map(fn ($items) => $items->pluck('id')->toArray());

        $loaded = collect();

        foreach ($this->tables as $table) {
            if (!isset($grouped[$table['name']])) {
                continue;
            }

            $models = $table['model']::whereIn('id', $grouped[$table['name']])->get();
            $loaded[$table['name']] = $models->keyBy('id');
        }

        $final = $rows->map(fn ($row) => $loaded[$row->table_name][$row->id] ?? null)->filter()->values();
        $paginator->setCollection($final);

        return $paginator;
    }
}
