KSA-ORACLE-ALLACCESSKEYS/app/Support/CardCsvImporter.php

<?php

namespace App\Support;

use App\Models\Card;

class CardCsvImporter
{
    /**
     * Import cards from a CSV or XLSX file (upsert by name/title).
     * Returns array [inserted, updated, skipped].
     */
    public function import(string $file, string $delimiter = ',', bool $dryRun = false): array
    {
        if (!is_file($file)) {
            throw new \InvalidArgumentException("File not found: {$file}");
        }

        [$headers, $rows] = $this->readRows($file, $delimiter);
        if (empty($headers)) {
            return [0, 0, 0];
        }

        // Normalize headers (lowercase, trimmed, remove BOM)
        $headers = array_map(function ($h) {
            $h = (string) ($h ?? '');
            $h = preg_replace('/^\xEF\xBB\xBF/', '', $h); // strip UTF-8 BOM
            return strtolower(trim($h));
        }, $headers);

        $idx = array_flip($headers);

        $nameKey = array_key_exists('name', $idx) ? 'name' : (array_key_exists('title', $idx) ? 'title' : null);
        if (!$nameKey) {
            throw new \RuntimeException('CSV must contain a "name" or "title" header.');
        }

        $get = function (array $row, string $key) use ($idx): ?string {
            if (!array_key_exists($key, $idx)) return null;
            $value = $row[$idx[$key]] ?? null;
            return is_string($value) ? trim($value) : (is_null($value) ? null : trim((string) $value));
        };

        $inserted = 0;
        $updated = 0;
        $skipped = 0;

        foreach ($rows as $row) {
            if (!is_array($row)) continue;
            if (count(array_filter($row, fn($v) => $v !== null && $v !== '')) === 0) continue;

            $name = $get($row, $nameKey);
            if (!$name) { $skipped++; continue; }

            $payload = [
                'description' => (string) ($get($row, 'description') ?? ''),
                'description_upright' => (string) ($get($row, 'description_upright') ?? ''),
                'description_reversed' => (string) ($get($row, 'description_reversed') ?? ''),
                'image_url' => $get($row, 'image_url') ?: null,
                'symbolism' => $this->parseSymbolism($get($row, 'symbolism')),
            ];

            if ($dryRun) {
                continue;
            }

            $existing = Card::where('name', $name)->first();
            if ($existing) {
                $existing->fill($payload)->save();
                $updated++;
            } else {
                Card::create(array_merge(['name' => $name], $payload));
                $inserted++;
            }
        }

        return [$inserted, $updated, $skipped];
    }

    /**
     * Read headers and rows from CSV or XLSX.
     * @return array{0: array<int,string>, 1: array<int,array<int,string|null>>>}
     */
    private function readRows(string $file, string $delimiter): array
    {
        $ext = strtolower(pathinfo($file, PATHINFO_EXTENSION));
        if (in_array($ext, ['xlsx', 'xls', 'ods'])) {
            if (!class_exists('PhpOffice\\PhpSpreadsheet\\IOFactory')) {
                throw new \RuntimeException('XLSX import requires phpoffice/phpspreadsheet. Run: composer require phpoffice/phpspreadsheet');
            }
            /** @var \PhpOffice\PhpSpreadsheet\Spreadsheet $spreadsheet */
            $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);
            $sheet = $spreadsheet->getActiveSheet();
            $data = $sheet->toArray(null, true, true, false); // rows of arrays
            if (empty($data)) return [[], []];
            $headers = array_map(fn($v) => is_string($v) ? $v : (is_null($v) ? '' : (string) $v), array_shift($data));
            $rows = array_map(function ($row) use ($headers) {
                // Normalize row length to headers length
                $row = array_map(fn($v) => is_string($v) ? $v : (is_null($v) ? null : (string) $v), $row);
                if (count($row) < count($headers)) {
                    $row = array_pad($row, count($headers), null);
                } elseif (count($row) > count($headers)) {
                    $row = array_slice($row, 0, count($headers));
                }
                return $row;
            }, $data);
            return [$headers, $rows];
        }

        // Default: treat as CSV/TSV/plain text
        $csv = new \SplFileObject($file, 'r');
        $csv->setFlags(\SplFileObject::READ_CSV | \SplFileObject::SKIP_EMPTY | \SplFileObject::DROP_NEW_LINE);
        $csv->setCsvControl($delimiter);

        if ($csv->eof()) return [[], []];
        $headers = $csv->fgetcsv();
        if (!$headers || !is_array($headers)) return [[], []];
        $headers = array_map(fn($h) => is_string($h) ? $h : (is_null($h) ? '' : (string) $h), $headers);

        $rows = [];
        foreach ($csv as $row) {
            if (!is_array($row)) continue;
            $rows[] = $row;
        }
        return [$headers, $rows];
    }

    /**
     * Parse symbolism string into an array suitable for the Card::casts ['symbolism' => 'array'].
     * Accepts JSON arrays or delimited strings (separated by ;, |, •, or ,).
     */
    public function parseSymbolism(?string $raw): array
    {
        $raw = trim((string) $raw);
        if ($raw === '') return [];

        // Try JSON first
        if (str_starts_with($raw, '[') || str_starts_with($raw, '{')) {
            $decoded = json_decode($raw, true);
            if (json_last_error() === JSON_ERROR_NONE) {
                if (is_array($decoded)) return array_values($decoded);
                return [];
            }
        }

        $parts = preg_split('/[;|•,]+/u', $raw);
        $parts = array_map(fn($s) => trim($s), $parts);
        $parts = array_filter($parts, fn($s) => $s !== '');
        return array_values($parts);
    }
}