<?php
declare(strict_types=1);

namespace Esun\Catalog\Model\MixStepPricing;

use Magento\Framework\Exception\LocalizedException;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;

class SpreadsheetReader
{
    public const IMPORT_SHEET = '批量导入模板';
    public const DELETE_SHEET = '批量删除模板';
    private const MAX_ROWS = 5000;

    private const IMPORT_HEADERS = [
        'sku',
        'website',
        'Combined Qty From',
        'Pricing Method',
        'Discount % / Unit Price',
    ];

    private const DELETE_HEADERS = ['sku', 'website'];

    /**
     * @throws LocalizedException
     */
    public function readImport(string $path): array
    {
        return $this->read($path, self::IMPORT_SHEET, self::IMPORT_HEADERS, static function (array $values, int $row): array {
            return [
                'row' => $row,
                'sku' => trim((string)$values[0]),
                'website' => trim((string)$values[1]),
                'combined_qty_from' => $values[2],
                'pricing_method' => trim((string)$values[3]),
                'value' => $values[4],
            ];
        });
    }

    /**
     * @throws LocalizedException
     */
    public function readDelete(string $path): array
    {
        return $this->read($path, self::DELETE_SHEET, self::DELETE_HEADERS, static function (array $values, int $row): array {
            return [
                'row' => $row,
                'sku' => trim((string)$values[0]),
                'website' => trim((string)$values[1]),
            ];
        });
    }

    /**
     * @throws LocalizedException
     */
    private function read(string $path, string $sheetName, array $headers, callable $mapper): array
    {
        try {
            $spreadsheet = IOFactory::load($path);
        } catch (\Throwable $exception) {
            throw new LocalizedException(__('The Excel file could not be read: %1', $exception->getMessage()));
        }

        try {
            $sheet = $spreadsheet->getSheetByName($sheetName);
            if ($sheet === null) {
                throw new LocalizedException(__('The workbook must contain a "%1" worksheet.', $sheetName));
            }

            foreach ($headers as $index => $expected) {
                $actual = trim((string)$sheet->getCell(Coordinate::stringFromColumnIndex($index + 1) . '1')->getValue());
                if ($actual !== $expected) {
                    throw new LocalizedException(
                        __('Invalid header in column %1. Expected "%2", found "%3".', $index + 1, $expected, $actual)
                    );
                }
            }

            $highestRow = $sheet->getHighestDataRow();
            if ($highestRow - 1 > self::MAX_ROWS) {
                throw new LocalizedException(__('The file contains more than %1 data rows.', self::MAX_ROWS));
            }

            $rows = [];
            for ($row = 2; $row <= $highestRow; $row++) {
                $values = [];
                foreach (array_keys($headers) as $index) {
                    $cell = $sheet->getCell(Coordinate::stringFromColumnIndex($index + 1) . $row);
                    if ($cell->isFormula()) {
                        throw new LocalizedException(__('Row %1 contains a formula. Use plain values only.', $row));
                    }
                    $values[] = $cell->getValue();
                }

                if (count(array_filter($values, static fn(mixed $value): bool => $value !== null && trim((string)$value) !== '')) === 0) {
                    continue;
                }
                $rows[] = $mapper($values, $row);
            }

            if ($rows === []) {
                throw new LocalizedException(__('The worksheet contains no data rows.'));
            }

            return $rows;
        } finally {
            $spreadsheet->disconnectWorksheets();
        }
    }
}