<?php

namespace App\Http\Controllers;

use App\Models\Report;
use App\Services\ReportQueryBuilder;
use Illuminate\Http\Request;
use Inertia\Inertia;

// facades referenced with fully-qualified names below to satisfy static analysis

class ReportController extends Controller
{
    public function __construct(protected ReportQueryBuilder $queryBuilder) {}

    public function index(Request $request)
    {
        $reports = Report::where('enabled', true)
            ->orderBy('order')
            ->orderBy('name')
            ->get()
            ->map(fn ($r) => [
                'slug' => $r->slug,
                'name' => $r->name,
                'description' => $r->description,
                'category' => $r->category,
            ])
            ->values();

        return Inertia::render('Reports/Index', [
            'reports' => $reports,
        ]);
    }

    public function show(string $slug, Request $request)
    {
        $report = Report::with(['filters', 'columns'])
            ->where('slug', $slug)
            ->where('enabled', true)
            ->firstOrFail();

        // Accept filters & pagination from query and return initial data for server-driven table
        $inputs = $this->buildInputsArray($report);
        $filters = $this->validateFilters($inputs, $request);
        \Log::info('Report filters', ['filters' => $filters, 'request' => $request->all()]);

        $perPage = (int) ($request->integer('per_page') ?: 25);
        $query = $this->queryBuilder->build($report, $filters);
        $paginator = $query->paginate($perPage);

        $rows = collect($paginator->items())
            ->map(fn ($row) => $this->normalizeRow($row))
            ->values();

        return Inertia::render('Reports/Show', [
            'slug' => $report->slug,
            'name' => $report->name,
            'description' => $report->description,
            'inputs' => $inputs,
            'columns' => $this->buildColumnsArray($report),
            'rows' => $rows,
            'meta' => [
                'total' => $paginator->total(),
                'current_page' => $paginator->currentPage(),
                'per_page' => $paginator->perPage(),
                'last_page' => $paginator->lastPage(),
            ],
            'query' => array_filter($filters, fn ($v) => $v !== null && $v !== ''),
        ]);
    }

    public function data(string $slug, Request $request)
    {
        $report = Report::with(['filters', 'columns'])
            ->where('slug', $slug)
            ->where('enabled', true)
            ->firstOrFail();

        $inputs = $this->buildInputsArray($report);
        $filters = $this->validateFilters($inputs, $request);
        $perPage = (int) ($request->integer('per_page') ?: 25);

        $query = $this->queryBuilder->build($report, $filters);
        $paginator = $query->paginate($perPage);

        $rows = collect($paginator->items())
            ->map(fn ($row) => $this->normalizeRow($row))
            ->values();

        return response()->json([
            'data' => $rows,
            'total' => $paginator->total(),
            'current_page' => $paginator->currentPage(),
            'last_page' => $paginator->lastPage(),
        ]);
    }

    public function export(string $slug, Request $request)
    {
        $report = Report::with(['filters', 'columns'])
            ->where('slug', $slug)
            ->where('enabled', true)
            ->firstOrFail();

        $inputs = $this->buildInputsArray($report);
        $filters = $this->validateFilters($inputs, $request);
        $format = strtolower((string) $request->get('format', 'csv'));

        $query = $this->queryBuilder->build($report, $filters);
        $rows = $query->get()->map(fn ($row) => $this->normalizeRow($row));
        $columns = $this->buildColumnsArray($report);
        $filename = $report->slug.'-'.now()->format('Ymd_His');

        if ($format === 'pdf') {
            $pdf = \Barryvdh\DomPDF\Facade\Pdf::loadView('reports.pdf.table', [
                'name' => $report->name,
                'columns' => $columns,
                'rows' => $rows,
            ]);

            return $pdf->download($filename.'.pdf');
        }

        if ($format === 'xlsx') {
            $keys = array_map(fn ($c) => $c['key'], $columns);
            $headings = array_map(fn ($c) => $c['label'] ?? $c['key'], $columns);

            // Convert values for correct Excel rendering (dates, numbers, text)
            $array = $this->prepareXlsxArray($rows, $keys);

            // Build base column formats: text for contracts, EU datetime for *_at; numbers are formatted per-cell in AfterSheet
            $columnFormats = [];
            $textColumns = [];
            $dateColumns = [];
            foreach ($keys as $i => $key) {
                $letter = $this->excelColumnLetter($i + 1);
                if ($key === 'contract_reference') {
                    $columnFormats[$letter] = '@';
                    $textColumns[] = $letter;

                    continue;
                }
                if (str_ends_with($key, '_at')) {
                    $columnFormats[$letter] = 'dd.mm.yyyy hh:mm';
                    $dateColumns[] = $letter;

                    continue;
                }
            }

            // Anonymous export with custom value binder to force text where needed
            $export = new class($array, $headings, $columnFormats, $textColumns, $dateColumns) extends \Maatwebsite\Excel\DefaultValueBinder implements \Maatwebsite\Excel\Concerns\FromArray, \Maatwebsite\Excel\Concerns\ShouldAutoSize, \Maatwebsite\Excel\Concerns\WithColumnFormatting, \Maatwebsite\Excel\Concerns\WithCustomValueBinder, \Maatwebsite\Excel\Concerns\WithEvents, \Maatwebsite\Excel\Concerns\WithHeadings
            {
                public function __construct(private array $array, private array $headings, private array $formats, private array $textColumns, private array $dateColumns) {}

                public function array(): array
                {
                    return $this->array;
                }

                public function headings(): array
                {
                    return $this->headings;
                }

                public function columnFormats(): array
                {
                    return $this->formats;
                }

                public function bindValue(\PhpOffice\PhpSpreadsheet\Cell\Cell $cell, $value): bool
                {
                    $col = preg_replace('/\d+/', '', $cell->getCoordinate()); // e.g., B from B2
                    // Force text for configured columns or very long digit-only strings (>15)
                    if (in_array($col, $this->textColumns, true) || (is_string($value) && ctype_digit($value) && strlen($value) > 15)) {
                        $cell->setValueExplicit((string) $value, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);

                        return true;
                    }

                    return parent::bindValue($cell, $value);
                }

                public function registerEvents(): array
                {
                    return [
                        \Maatwebsite\Excel\Events\AfterSheet::class => function (\Maatwebsite\Excel\Events\AfterSheet $event) {
                            $sheet = $event->sheet->getDelegate();
                            // Data starts at row 2 (row 1 is headings)
                            $rowIndex = 2;
                            foreach ($this->array as $row) {
                                foreach (array_values($row) as $i => $val) {
                                    $colLetter = $this->colLetter($i + 1);
                                    if (in_array($colLetter, $this->textColumns, true) || in_array($colLetter, $this->dateColumns, true)) {
                                        continue; // already handled via columnFormats or binder
                                    }
                                    $coord = $colLetter.$rowIndex;
                                    $fmt = null;
                                    if (is_int($val)) {
                                        // Integer: thousands separator, no decimals
                                        $fmt = '#,##0';
                                    } elseif (is_float($val)) {
                                        // Float: show decimals only if fractional part exists
                                        $fmt = (floor($val) != $val) ? '#,##0.00' : '#,##0';
                                    }
                                    if ($fmt) {
                                        $sheet->getStyle($coord)->getNumberFormat()->setFormatCode($fmt);
                                    }
                                }
                                $rowIndex++;
                            }
                        },
                    ];
                }

                private function colLetter(int $index): string
                {
                    $letter = '';
                    while ($index > 0) {
                        $mod = ($index - 1) % 26;
                        $letter = chr(65 + $mod).$letter;
                        $index = intdiv($index - $mod, 26) - 1;
                    }

                    return $letter;
                }
            };

            return \Maatwebsite\Excel\Facades\Excel::download($export, $filename.'.xlsx');
        }

        // Default CSV export
        $keys = array_map(fn ($c) => $c['key'], $columns);
        $headings = array_map(fn ($c) => $c['label'] ?? $c['key'], $columns);

        $csv = fopen('php://temp', 'r+');
        fputcsv($csv, $headings);
        foreach ($rows as $r) {
            $line = collect($keys)->map(fn ($k) => data_get($r, $k))->toArray();
            fputcsv($csv, $line);
        }
        rewind($csv);
        $content = stream_get_contents($csv) ?: '';
        fclose($csv);

        return response($content, 200, [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="'.$filename.'.csv"',
        ]);
    }

    /**
     * Lightweight users lookup for filters: id + name, optional search and limit.
     */
    public function users(Request $request)
    {
        $search = trim((string) $request->get('search', ''));
        $limit = (int) ($request->integer('limit') ?: 10);

        $q = \App\Models\User::query()->orderBy('name');
        if ($search !== '') {
            $like = '%'.mb_strtolower($search).'%';
            $q->where(function ($qq) use ($like) {
                $qq->whereRaw('LOWER(name) LIKE ?', [$like])
                    ->orWhereRaw('LOWER(email) LIKE ?', [$like]);
            });
        }

        $users = $q->limit(max(1, min(50, $limit)))->get(['id', 'name']);

        return response()->json($users);
    }

    /**
     * Lightweight clients lookup for filters: uuid + name (person full_name), optional search and limit.
     */
    public function clients(Request $request)
    {
        $clients = \App\Models\Client::query()
            ->with('person:id,full_name')
            ->get()
            ->map(fn ($c) => [
                'id' => $c->uuid,
                'name' => $c->person->full_name ?? 'Unknown',
            ])
            ->sortBy('name')
            ->values();

        return response()->json($clients);
    }

    /**
     * Lightweight actions lookup for select:action filters.
     */
    public function actions(Request $request)
    {
        $actions = \App\Models\Action::query()
            ->orderBy('name')
            ->get(['id', 'name'])
            ->map(fn ($a) => ['id' => $a->id, 'name' => $a->name])
            ->values();

        return response()->json($actions);
    }

    /**
     * Lightweight decisions lookup for select:decision filters.
     * Optionally filtered by action_id (for dependent filter UI).
     */
    public function decisions(Request $request)
    {
        $actionId = $request->integer('action_id', 0) ?: null;

        $q = \App\Models\Decision::query()->orderBy('name');

        if ($actionId !== null) {
            $q->whereHas('actions', fn ($qq) => $qq->where('actions.id', $actionId));
        }

        $decisions = $q->get(['id', 'name'])
            ->map(fn ($d) => ['id' => $d->id, 'name' => $d->name])
            ->values();

        return response()->json($decisions);
    }

    /**
     * Build validation rules based on inputs descriptor and validate.
     *
     * @param  array<int, array<string, mixed>>  $inputs
     * @return array<string, mixed>
     */
    protected function validateFilters(array $inputs, Request $request): array
    {
        $rules = [];
        foreach ($inputs as $inp) {
            $key = $inp['key'];
            $type = $inp['type'] ?? 'string';
            $nullable = ($inp['nullable'] ?? true) ? 'nullable' : 'required';
            $rules[$key] = match ($type) {
                'date' => [$nullable, 'date'],
                'integer' => [$nullable, 'integer'],
                'select:user' => [$nullable, 'integer', 'exists:users,id'],
                'select:client' => [$nullable, 'string', 'exists:clients,uuid'],
                'select:action' => [$nullable, 'integer', 'exists:actions,id'],
                'select:decision' => [$nullable, 'integer', 'exists:decisions,id'],
                default => [$nullable, 'string'],
            };
        }

        return $request->validate($rules);
    }

    /**
     * Build inputs array from report filters.
     */
    protected function buildInputsArray(Report $report): array
    {
        return $report->filters->map(fn ($filter) => [
            'key' => $filter->key,
            'type' => $filter->type,
            'label' => $filter->label,
            'nullable' => $filter->nullable,
            'default' => $filter->default_value,
            'options' => $filter->options,
        ])->toArray();
    }

    /**
     * Build columns array from report columns.
     */
    protected function buildColumnsArray(Report $report): array
    {
        return $report->columns
            ->where('visible', true)
            ->map(fn ($col) => [
                'key' => $col->key,
                'label' => $col->label,
            ])
            ->toArray();
    }

    /**
     * Ensure derived export/display fields exist on row objects.
     */
    protected function normalizeRow(object $row): object
    {
        if (isset($row->contract) && ! isset($row->contract_reference)) {
            $row->contract_reference = $row->contract->reference ?? null;
        }
        if (isset($row->assignedUser) && ! isset($row->assigned_user_name)) {
            $row->assigned_user_name = $row->assignedUser->name ?? null;
        }

        return $row;
    }

    /**
     * Convert rows for XLSX export: dates to Excel serial numbers, numbers to numeric, contract refs to text.
     *
     * @param  iterable<int, object|array>  $rows
     * @param  array<int, string>  $keys
     * @return array<int, array<int, mixed>>
     */
    protected function prepareXlsxArray(iterable $rows, array $keys): array
    {
        $out = [];
        foreach ($rows as $r) {
            $line = [];
            foreach ($keys as $k) {
                $v = data_get($r, $k);
                if ($k === 'contract_reference') {
                    $line[] = (string) $v;

                    continue;
                }
                if (str_ends_with($k, '_at')) {
                    if (empty($v)) {
                        $line[] = null;
                    } else {
                        try {
                            $dt = \Carbon\Carbon::parse($v);
                            $line[] = \PhpOffice\PhpSpreadsheet\Shared\Date::dateTimeToExcel($dt);
                        } catch (\Throwable $e) {
                            $line[] = (string) $v;
                        }
                    }

                    continue;
                }
                if (is_int($v) || is_float($v)) {
                    $line[] = $v;
                } elseif (is_numeric($v) && is_string($v)) {
                    // cast numeric-like strings unless they are identifiers that we want as text
                    $line[] = (strpos($k, 'id') !== false) ? (int) $v : ($v + 0);
                } else {
                    $line[] = $v;
                }
            }
            $out[] = $line;
        }

        return $out;
    }

    /**
     * Convert 1-based index to Excel column letter.
     */
    protected function excelColumnLetter(int $index): string
    {
        $letter = '';
        while ($index > 0) {
            $mod = ($index - 1) % 26;
            $letter = chr(65 + $mod).$letter;
            $index = intdiv($index - $mod, 26) - 1;
        }

        return $letter;
    }
}
