<?php

namespace App\Exports;

use App\Models\Octoparse\OctoparseImportLog;
use App\Models\Octoparse\OctoparseRecord;
use Carbon\Carbon;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;

class OctoparseRecordsExport implements WithMultipleSheets
{
    public const STATUS_NEW = 'New';
    public const STATUS_INITIAL = 'Initial import';
    public const STATUS_NOT_LISTED = 'No longer listed';

    /** @var array<string,array<string,mixed>> */
    protected $tasks;

    /**
     * @param array<string,array<string,mixed>> $tasks Tasks from config('octoparse.tasks')
     */
    public function __construct(array $tasks)
    {
        $this->tasks = $tasks;
    }

    public function sheets(): array
    {
        $byTask = OctoparseRecord::whereIn('task_id', array_keys($this->tasks))
            ->orderBy('id')
            ->get()
            ->groupBy('task_id');

        $extraKeys = $this->extraKeys($byTask->flatten(1));

        $trackListing = !config('octoparse.import.use_not_exported_endpoint');

        $sevenDaysAgo = Carbon::now()->subDays(7);
        $thirtyDaysAgo = Carbon::now()->subDays(30);

        $entries = [];
        $summary = [];

        foreach ($this->tasks as $taskId => $taskConfig) {
            $records = $byTask->get($taskId, collect());
            $label = $taskConfig['label'] ?? $taskId;
            $lastLog = OctoparseImportLog::forTask($taskId)->orderByDesc('started_at')->first();

            $firstCreated = $records->min('created_at');
            $initialUntil = $firstCreated ? $firstCreated->copy()->addHour() : null;

            $lastSeen = $records->max('imported_at');
            $listedSince = $lastSeen ? $lastSeen->copy()->subHour() : null;

            $newLastImport = $new7 = $new30 = $notListed = 0;

            foreach ($records as $record) {
                $isNew = $record->created_at && $record->created_at->gt($initialUntil);
                $isNotListed = $trackListing && $record->imported_at && $record->imported_at->lt($listedSince);

                if ($isNew) {
                    $newLastImport += $lastLog && $record->created_at->gte($lastLog->started_at) ? 1 : 0;
                    $new7 += $record->created_at->gte($sevenDaysAgo) ? 1 : 0;
                    $new30 += $record->created_at->gte($thirtyDaysAgo) ? 1 : 0;
                }
                $notListed += $isNotListed ? 1 : 0;

                $payload = is_array($record->payload) ? $record->payload : [];
                $nameKey = $this->nameKey($payload, $taskConfig);

                $row = [
                    $label,
                    $nameKey !== null ? $this->formatValue($payload[$nameKey]) : '',
                    $this->excelDate($record->created_at),
                    $this->excelDate($record->imported_at),
                    $isNotListed ? self::STATUS_NOT_LISTED : ($isNew ? self::STATUS_NEW : self::STATUS_INITIAL),
                ];

                foreach ($extraKeys as $key) {
                    $row[] = $key !== $nameKey && array_key_exists($key, $payload)
                        ? $this->formatValue($payload[$key])
                        : '';
                }

                $entries[] = $row;
            }

            $summary[] = [
                $label,
                $taskConfig['link'] ?? '',
                $records->count(),
                $newLastImport,
                $new7,
                $new30,
                $notListed,
                $lastLog ? $this->excelDate($lastLog->started_at) : null,
                $lastLog->status ?? 'never',
            ];
        }

        return [
            new OctoparseSheet(
                'Entries',
                array_merge(['Platform', 'Name', 'Added on', 'Last seen', 'Status'], $extraKeys),
                $entries,
                ['C', 'D']
            ),
            new OctoparseSheet(
                'Summary',
                [
                    'Platform',
                    'Link',
                    'Total entries',
                    'New (last import)',
                    'New (last 7 days)',
                    'New (last 30 days)',
                    'No longer listed',
                    'Last import',
                    'Last import status',
                ],
                $summary,
                ['H']
            ),
        ];
    }

    /**
     * Payload keys other than the one exported as "Name".
     *
     * @return string[]
     */
    protected function extraKeys(Collection $records): array
    {
        $extraKeys = [];
        foreach ($records as $record) {
            $payload = is_array($record->payload) ? $record->payload : [];
            $nameKey = $this->nameKey($payload, $this->tasks[$record->task_id] ?? []);
            foreach (array_keys($payload) as $key) {
                if ((string) $key !== $nameKey) {
                    $extraKeys[(string) $key] = true;
                }
            }
        }

        return array_keys($extraKeys);
    }

    /**
     * Payload key holding the record's main value: the task's unique key
     * field when present, otherwise the first payload key.
     */
    protected function nameKey(array $payload, array $taskConfig): ?string
    {
        $field = $taskConfig['unique_key_field'] ?? null;
        if ($field !== null && array_key_exists($field, $payload)) {
            return (string) $field;
        }

        $first = array_key_first($payload);

        return $first === null ? null : (string) $first;
    }

    protected function formatValue($value): string
    {
        if (is_array($value)) {
            $value = json_encode($value, JSON_UNESCAPED_UNICODE);
        }

        return trim(preg_replace('/\s+/u', ' ', (string) $value));
    }

    protected function excelDate(?Carbon $date): ?float
    {
        return $date ? (float) ExcelDate::dateTimeToExcel($date) : null;
    }
}