<?php

namespace Ignite\Core\Services\Reports;

use Generator;
use Ignite\Core\Entities\ReportDownload;
use Ignite\Core\Entities\User;
use Ignite\Core\Jobs\ProcessReportDownload;
use Ignite\Core\Models\Grid\AbstractTable;
use Ignite\Core\Models\Grid\Scopes\FilterFactory;
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use InvalidArgumentException;
use Maatwebsite\Excel\Cache\MemoryCache;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Settings;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use RuntimeException;
use Throwable;
use Yajra\DataTables\Utilities\Request as DataTablesRequest;

/**
 * Create, queue, process, cancel, and retry durable report downloads.
 *
 * This intentionally consolidated service keeps the complete download flow visible in
 * one place. ReportDownloadRequest owns serializable input and ProcessReportDownload is
 * only the Horizon adapter; additional factories and writer services are avoided.
 */
class ReportDownloadService
{
    /** @var FilterFactory */
    protected $filterFactory;

    public function __construct(FilterFactory $filterFactory)
    {
        $this->filterFactory = $filterFactory;
    }

    /**
     * Persist and dispatch a new report download request.
     *
     * @throws Throwable When persistence or queue dispatch fails.
     */
    public function requestReportDownload(ReportDownloadRequest $request): ReportDownload
    {
        if (! config('core.report_download.enabled', true)) {
            throw new RuntimeException('Report downloads are disabled.');
        }

        $data = $request->toArray();
        // Store only plain data. The worker rebuilds the table or query later.
        $download = ReportDownload::create([
            'user_id' => $data['user_id'],
            'report_type' => $data['source']['type'] === ReportDownloadRequest::SOURCE_TABLE
                ? $data['source']['class']
                : $data['source']['type'],
            'parameters' => $data,
            'format' => $data['format'],
            'status' => ReportDownload::STATUS_PENDING,
        ]);

        try {
            $download->markQueued();
            ProcessReportDownload::dispatch($download->getKey());
        } catch (Throwable $exception) {
            $download->refresh()->markFailed($exception->getMessage());
            throw $exception;
        }

        return $download->refresh();
    }

    /**
     * Determine whether the request meets the configured queued-download threshold.
     */
    public function shouldQueue(ReportDownloadRequest $request): bool
    {
        // Count the filtered rows, not the whole table. A narrow search can still
        // be small enough to download immediately.
        return $this->expectedRowCount($request) >= $this->recordThreshold();
    }

    /**
     * Count the exact filtered rows represented by a serializable download request.
     */
    public function expectedRowCount(ReportDownloadRequest $request): int
    {
        return $this->runAsRequester($request, function () use ($request) {
            $data = $request->toArray();
            [$query] = $data['source']['type'] === ReportDownloadRequest::SOURCE_TABLE
                ? $this->resolveTableSource($request)
                : $this->resolveAdHocSource($request);

            return $this->countRows($query);
        });
    }

    /**
     * Cooperatively cancel queued or in-progress work.
     *
     * A row lock orders cancellation against final publication so a download cannot
     * become COMPLETE after a successful cancellation.
     */
    public function cancel(int $reportDownloadId): ReportDownload
    {
        return DB::transaction(function () use ($reportDownloadId) {
            /** @var ReportDownload $download */
            $download = ReportDownload::query()->lockForUpdate()->findOrFail($reportDownloadId);
            $download->cancel();

            return $download->refresh();
        });
    }

    /**
     * Requeue the same failed record while retaining its previous public file until
     * a complete replacement can be atomically published.
     */
    public function retry(int $reportDownloadId): ReportDownload
    {
        /** @var ReportDownload $download */
        $download = DB::transaction(function () use ($reportDownloadId) {
            /** @var ReportDownload $locked */
            $locked = ReportDownload::query()->lockForUpdate()->findOrFail($reportDownloadId);
            $locked->prepareRetry();
            $locked->markQueued();

            return $locked->refresh();
        });

        try {
            ProcessReportDownload::dispatch($download->getKey());
        } catch (Throwable $exception) {
            $download->refresh()->markFailed($exception->getMessage());
            throw $exception;
        }

        return $download->refresh();
    }

    /**
     * Generate and atomically publish one queued download.
     *
     * @throws Throwable The job rethrows failures so Horizon controls retries.
     */
    public function process(int $reportDownloadId): ReportDownload
    {
        // Claim the work in one update so a duplicate queue delivery cannot start
        // the same download twice.
        $claimed = ReportDownload::query()
            ->whereKey($reportDownloadId)
            ->where('status', ReportDownload::STATUS_QUEUED)
            ->update([
                'status' => ReportDownload::STATUS_IN_PROGRESS,
                'started_at' => now(),
                'progress' => 0,
            ]);

        /** @var ReportDownload $download */
        $download = ReportDownload::findOrFail($reportDownloadId);
        if ($claimed !== 1) {
            // Duplicate queue delivery or cancellation is a harmless no-op.
            return $download;
        }

        $request = ReportDownloadRequest::fromArray($download->parameters);
        $data = $request->toArray();
        $diskName = config('core.report_download.disk', 'local');
        $directory = trim(config('core.report_download.directory', 'report-downloads'), '/');
        $filename = sprintf(
            '%s-%s.%s',
            $download->getKey(),
            Str::slug($data['name']) ?: 'report-download',
            $data['format']
        );
        $finalPath = $directory.'/'.$filename;
        $temporaryPath = $directory.'/.'.$filename.'.'.Str::random(16).'.tmp';

        return $this->runAsRequester($request, function () use (
            $download,
            $request,
            $data,
            $diskName,
            $directory,
            $finalPath,
            $temporaryPath
        ) {
            $localTemporaryPath = null;

            try {
                $localTemporaryPath = $this->createLocalTemporaryFile();
                [$query, $columns, $formatter] = $data['source']['type'] === ReportDownloadRequest::SOURCE_TABLE
                    ? $this->resolveTableSource($request)
                    : $this->resolveAdHocSource($request);

                // Count after rebuilding the source so progress uses the same rows
                // that will be written to the file.
                $expectedRows = $this->countRows($query);
                if ($data['format'] === ReportDownloadRequest::FORMAT_XLSX
                    && $expectedRows > (int) config('core.report_download.xlsx.max_rows', 1000000)) {
                    throw new RuntimeException('The report exceeds the configured XLSX row limit.');
                }

                $download->updateProgress(0, $expectedRows);
                $filesystem = Storage::disk($diskName);
                $filesystem->makeDirectory($directory);
                $rows = $this->rows($download, $query, $columns, $formatter, $expectedRows);

                if ($data['format'] === ReportDownloadRequest::FORMAT_CSV) {
                    $this->writeCsv($localTemporaryPath, $columns, $rows);
                } else {
                    $this->writeXlsx($localTemporaryPath, $columns, $rows);
                }

                $this->putLocalFile($filesystem, $temporaryPath, $localTemporaryPath);

                $download->refresh();
                if ($download->status === ReportDownload::STATUS_CANCELLED) {
                    // A cancelled job may have already written a temp file. Remove
                    // it before leaving the durable record cancelled.
                    $filesystem->delete($temporaryPath);
                    return $download;
                }

                DB::transaction(function () use (
                    $download,
                    $filesystem,
                    $temporaryPath,
                    $finalPath
                ) {
                    /** @var ReportDownload $locked */
                    $locked = ReportDownload::query()->lockForUpdate()->findOrFail($download->getKey());
                    if ($locked->status === ReportDownload::STATUS_CANCELLED) {
                        $filesystem->delete($temporaryPath);
                        return;
                    }

                    // Use Laravel's filesystem API so configured disks own their
                    // path handling. Local disks rename; remote disks may copy/delete.
                    if (! $filesystem->move($temporaryPath, $finalPath)) {
                        throw new RuntimeException('Unable to publish the completed report download.');
                    }

                    $locked->markComplete($finalPath, $locked->row_count);
                });

                return $download->refresh();
            } catch (Throwable $exception) {
                Storage::disk($diskName)->delete($temporaryPath);
                throw $exception;
            } finally {
                if ($localTemporaryPath && file_exists($localTemporaryPath)) {
                    unlink($localTemporaryPath);
                }
            }
        });
    }

    /**
     * Resolve a scoped table query and its existing download formatting callback.
     *
     * @return array{0:EloquentBuilder|QueryBuilder,1:array<int,array{key:string,title:string}>,2:callable}
     */
    protected function resolveTableSource(ReportDownloadRequest $request): array
    {
        $data = $request->toArray();
        $source = $data['source'];
        $app = app();
        $originalRequest = $app->make('request');
        $hadDataTablesRequest = $app->resolved('datatables.request');
        $originalDataTablesRequest = $hadDataTablesRequest ? $app->make('datatables.request') : null;

        try {
            /** @var AbstractTable $table */
            $table = $app->make($source['class'], ['params' => $source['params']]);
            $tableColumns = $table->columnsForDownload();
            $definitions = collect($tableColumns)->filter(function ($column) {
                return ($column['exportable'] ?? true) !== false;
            });
            $visible = $data['visible_columns'];
            if (! empty($visible)) {
                // Do not trust column names from the browser unless the table says
                // they are valid export columns.
                $unknown = array_diff($visible, $definitions->keys()->all());
                if (! empty($unknown)) {
                    throw new InvalidArgumentException('Unknown visible report columns: '.implode(', ', $unknown));
                }
                $definitions = $definitions->only($visible);
            }

            $input = $this->dataTablesInput($tableColumns, $request);
            // Yajra reads search and sorting from the current request. Give it a
            // small fake request that contains only the export state we saved.
            $syntheticRequest = Request::create('/', 'POST', $input);
            $app->instance('request', $syntheticRequest);
            $app->forgetInstance('datatables.request');
            $app->instance('datatables.request', new DataTablesRequest());

            $table = $this->filterFactory->apply($table, $data['filters']);
            $dataTable = $table->dataTable($table->queryForDownload());
            $dataTable->skipPaging();
            $query = $dataTable->getFilteredQuery();
            $callback = $table->fastExcelCallback();
            $columns = $definitions->map(function ($column, $key) {
                return ['key' => $key, 'title' => $column['title'] ?? $key];
            })->values()->all();

            return [$query, $columns, function ($row) use ($callback, $columns) {
                $formatted = $callback($row);

                return array_map(function ($column) use ($formatted) {
                    return $formatted[$column['title']] ?? '';
                }, $columns);
            }];
        } finally {
            $app->instance('request', $originalRequest);
            $app->forgetInstance('datatables.request');
            if ($hadDataTablesRequest) {
                $app->instance('datatables.request', $originalDataTablesRequest);
            }
        }
    }

    /**
     * Rebuild a trusted ad-hoc query as a raw-value subquery.
     *
     * @return array{0:QueryBuilder,1:array<int,array{key:string,title:string}>,2:callable}
     */
    protected function resolveAdHocSource(ReportDownloadRequest $request): array
    {
        $data = $request->toArray();
        $source = $data['source'];
        $columns = $data['columns'];
        $query = DB::connection($source['connection'] ?: null)
            ->query()
            ->fromRaw('('.$source['sql'].') as report_download_source', $source['bindings'])
            ->select(collect($columns)->pluck('key')->all());

        $search = $data['search'];
        if ($search['value'] !== '') {
            // Ad-hoc queries do not go through Yajra, so apply global search here.
            $query->where(function (QueryBuilder $query) use ($columns, $search) {
                foreach ($columns as $column) {
                    $query->orWhere($column['key'], 'like', '%'.$search['value'].'%');
                }
            });
        }
        foreach ($search['columns'] as $column => $value) {
            // Ignore unknown column names from the request.
            if (in_array($column, collect($columns)->pluck('key')->all(), true) && $value !== '') {
                $query->where($column, 'like', '%'.$value.'%');
            }
        }
        foreach ($data['sort'] as $order) {
            $query->orderBy($order['column'], $order['direction']);
        }

        return [$query, $columns, function ($row) use ($columns) {
            return array_map(function ($column) use ($row) {
                return $row->{$column['key']} ?? '';
            }, $columns);
        }];
    }

    /**
     * Build the minimal DataTables input needed to reproduce search and sorting.
     *
     * @param array<string, array<string, mixed>> $definitions
     * @return array<string, mixed>
     */
    protected function dataTablesInput(array $definitions, ReportDownloadRequest $request): array
    {
        $data = $request->toArray();
        $columns = [];
        foreach ($definitions as $key => $definition) {
            $columns[] = [
                'data' => $key,
                'name' => $definition['name'] ?? $key,
                'searchable' => ($definition['searchable'] ?? true) ? 'true' : 'false',
                'orderable' => ($definition['orderable'] ?? true) ? 'true' : 'false',
                'search' => [
                    'value' => $data['search']['columns'][$key] ?? '',
                    'regex' => 'false',
                ],
            ];
        }

        $keys = array_keys($definitions);
        $order = [];
        foreach ($data['sort'] as $sort) {
            // Yajra expects sort columns by index, so convert our saved key back.
            $index = array_search($sort['column'], $keys, true);
            if ($index === false || ($definitions[$sort['column']]['orderable'] ?? true) === false) {
                throw new InvalidArgumentException('Unknown or non-orderable report sort column: '.$sort['column']);
            }
            $order[] = ['column' => $index, 'dir' => $sort['direction']];
        }

        return [
            'columns' => $columns,
            'order' => $order,
            'search' => ['value' => $data['search']['value'], 'regex' => 'false'],
            'start' => 0,
            'length' => -1,
        ];
    }

    /**
     * Count the exact filtered result, including grouped and distinct queries.
     */
    protected function countRows($query): int
    {
        $base = $query instanceof EloquentBuilder ? $query->toBase() : $query;
        $copy = clone $base;

        return (int) $copy->getConnection()
            ->query()
            ->fromRaw('(' . $copy->toSql()  .') as report_download_count', $copy->getBindings())
            ->count();
    }

    /**
     * Run request reconstruction under the original requester for scoped reports.
     *
     * @template TReturn
     * @param callable():TReturn $callback
     * @return TReturn
     */
    protected function runAsRequester(ReportDownloadRequest $request, callable $callback)
    {
        $previousUser = auth()->user();
        $data = $request->toArray();

        try {
            auth()->setUser(User::findOrFail($data['user_id']));

            return $callback();
        } finally {
            if ($previousUser) {
                auth()->setUser($previousUser);
            } else {
                // Core's guard provides a quiet logout so a long-running Horizon
                // process does not leak this download's requester into its next job.
                auth()->quietLogout();
            }
        }
    }

    /**
     * Get the configured threshold for queued report downloads.
     *
     * @return int
     */
    protected function recordThreshold(): int
    {
        return max(0, (int) config('core.report_download.record_threshold', 2500));
    }

    /**
     * Lazily format rows while persisting measured progress and checking cancellation.
     *
     * @param EloquentBuilder|QueryBuilder $query
     * @param array<int, array{key:string,title:string}> $columns
     * @return Generator<int, array<int, mixed>>
     */
    protected function rows(
        ReportDownload $download,
        $query,
        array $columns,
        callable $formatter,
        int $expectedRows
    ): Generator {
        $rowCount = 0;
        $batchSize = max(1, (int) config('core.report_download.progress_batch_size', 500));

        foreach ($query->cursor() as $row) {
            yield $formatter($row);
            $rowCount++;

            if ($rowCount % $batchSize === 0) {
                $download->refresh();
                if ($download->status === ReportDownload::STATUS_CANCELLED) {
                    return;
                }
                $download->updateProgress($rowCount, $expectedRows);
            }
        }

        $download->refresh();
        if ($download->status !== ReportDownload::STATUS_CANCELLED) {
            $download->updateProgress($rowCount, $expectedRows);
        }
    }

    /**
     * Write a CSV file with a header even when the query returns no data rows.
     *
     * @param array<int, array{key:string,title:string}> $columns
     * @param iterable<array<int, mixed>> $rows
     */
    protected function writeCsv(string $path, array $columns, iterable $rows): void
    {
        $handle = fopen($path, 'w');
        if (! is_resource($handle)) {
            throw new RuntimeException('Unable to create the temporary CSV download.');
        }

        try {
            if (fputcsv($handle, $this->safeCsvRow(collect($columns)->pluck('title')->all())) === false) {
                throw new RuntimeException('Unable to write report download headers.');
            }
            foreach ($rows as $row) {
                if (fputcsv($handle, $this->safeCsvRow($row)) === false) {
                    throw new RuntimeException('Unable to write a report download row.');
                }
            }
        } finally {
            fclose($handle);
        }
    }

    /**
     * Write XLSX values with a per-download PhpSpreadsheet cell cache.
     *
     * PhpSpreadsheet still keeps the workbook in memory until save, so this is not
     * true streaming. TODO: evaluate a bounded-memory streaming writer after
     * representative production measurements.
     *
     * @param array<int, array{key:string,title:string}> $columns
     * @param iterable<array<int, mixed>> $rows
     */
    protected function writeXlsx(string $path, array $columns, iterable $rows): void
    {
        $previousCache = Settings::getCache();
        $cache = new MemoryCache();
        $spreadsheet = null;

        try {
            Settings::setCache($cache);
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $this->writeXlsxRow($sheet, 1, collect($columns)->pluck('title')->all());
            $rowNumber = 2;
            $maxRows = (int) config('core.report_download.xlsx.max_rows', 1000000);
            foreach ($rows as $row) {
                if ($rowNumber - 1 > $maxRows) {
                    throw new RuntimeException('The report exceeds the configured XLSX row limit.');
                }
                $this->writeXlsxRow($sheet, $rowNumber, $row);
                $rowNumber++;
            }

            $writer = new Xlsx($spreadsheet);
            $writer->setPreCalculateFormulas(false);
            $writer->save($path);
        } finally {
            if ($spreadsheet) {
                $spreadsheet->disconnectWorksheets();
            }
            $cache->clear();
            Settings::setCache($previousCache);
        }
    }

    /**
     * Create the local file used by CSV/XLSX writers before storing to Laravel disk.
     */
    protected function createLocalTemporaryFile(): string
    {
        $path = tempnam(sys_get_temp_dir(), 'ignite-report-download-');
        if ($path === false) {
            throw new RuntimeException('Unable to create a local temporary report download file.');
        }

        return $path;
    }

    /**
     * Stream a completed local temp file into the configured Laravel disk.
     *
     * @param \Illuminate\Contracts\Filesystem\Filesystem|\Illuminate\Filesystem\FilesystemAdapter $filesystem
     */
    protected function putLocalFile($filesystem, string $path, string $localPath): void
    {
        $stream = fopen($localPath, 'rb');
        if (! is_resource($stream)) {
            throw new RuntimeException('Unable to read the temporary report download file.');
        }

        try {
            if (! $filesystem->put($path, $stream)) {
                throw new RuntimeException('Unable to store the temporary report download file.');
            }
        } finally {
            fclose($stream);
        }
    }

    /**
     * Neutralize strings that spreadsheet programs may interpret as formulas.
     *
     * A leading apostrophe is the conventional CSV escape used to make spreadsheet
     * applications treat the following value as text. Non-string values are left
     * untouched so legitimate numeric and date-like values retain their type.
     *
     * @param array<int, mixed> $row
     * @return array<int, mixed>
     */
    protected function safeCsvRow(array $row): array
    {
        return array_map(function ($value) {
            return $this->isUnsafeSpreadsheetString($value) ? "'".$value : $value;
        }, array_values($row));
    }

    /**
     * Write one XLSX row while forcing dangerous formula-like strings to text.
     *
     * @param \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet
     * @param int $rowNumber
     * @param array<int, mixed> $row
     */
    protected function writeXlsxRow($sheet, int $rowNumber, array $row): void
    {
        foreach (array_values($row) as $columnIndex => $value) {
            $cell = $sheet->getCellByColumnAndRow($columnIndex + 1, $rowNumber);
            if ($this->isUnsafeSpreadsheetString($value)) {
                $cell->setValueExplicit($value, DataType::TYPE_STRING);
            } else {
                $cell->setValue($value);
            }
        }
    }

    /**
     * Detect formula prefixes after ASCII whitespace or control characters.
     *
     * @param mixed $value
     */
    protected function isUnsafeSpreadsheetString($value): bool
    {
        return is_string($value) && preg_match('/^[\x00-\x20]*[=+\-@]/', $value) === 1;
    }
}
