Warner-Vistex-Automater/app/Import/SpreadsheetRowProcessor.php

<?php

declare(strict_types=1);

namespace App\Import;

final class SpreadsheetRowProcessor
{
    private const CALCULATED_HEADERS = [
        'Actual/Accrual',
        'US/ex-US sale',
        'Contracting Party Filtered',
        'String',
        '2110 Applicable X201',
        'Class',
    ];

    private const X201_ELIGIBLE_STRINGS = [
        '1756YYFDI',
        '1756YYFLI',
        '1756YYFPA',
        '1756YYFWO',
        '1756YYDDI',
        '1756YYDWO',
        '1756YYDLI',
        '1756YYDPA',
        '1100NYDDI',
        '1100NYDWO',
        '1100YYDWO',
        '1100YYFDI',
        '1100YYFLI',
        '1100YYFPA',
        '1100YYFWO',
        '1100NYDLI',
        '1100NYDPA',
        '1100YYDLI',
        '1100YYDDI',
        '1100YYDPA',
        '1902NYDDI',
        '1902NYDLI',
        '1902NYDWO',
        '1902YYFDI',
        '1902YYFLI',
        '1902YYFPA',
        '1902YYFWO',
        '1902YYDDI',
        '1902YYDWO',
        '1902NYDPA',
        '1902YYDLI',
        '1902YYDPA',
        'LOCALYYDWO',
        'LOCALYYDDI',
        'LOCALYYDLI',
    ];

    public function calculatedHeaders(): array
    {
        return self::CALCULATED_HEADERS;
    }

    public function process(array $row): array
    {
        $normalized = $this->normalize($row);
        $calculated = $this->calculateDerivedFields($normalized);

        return [
            'normalized' => $normalized,
            'calculated' => $calculated,
            'merged' => array_merge($normalized, $calculated),
        ];
    }

    private function normalize(array $row): array
    {
        $normalized = [];
        foreach ($row as $header => $value) {
            $normalized[$header] = $this->normalizeValue($value);
        }

        return $normalized;
    }

    private function normalizeValue(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }

        $value = trim((string) $value);
        return $value === '' ? null : $value;
    }

    private function calculateDerivedFields(array $row): array
    {
        $contractingParty = $this->normalizeCode($row['Contracting Party'] ?? null);
        $localSellingCompany = $this->normalizeCode($row['Local Selling Company (S/T)'] ?? null);
        $rightsHolderLiableFlag = $this->normalizeFlag($row['Rights Holder Liable Flag'] ?? null);
        $globalTransferPricingFlag = $this->normalizeFlag($row['Global Transfer Pricing Flag'] ?? null);
        $domesticForeignFlag = $this->normalizeFlag($row['Domestic/ Foreign Flag'] ?? null);
        $ownershipType = $this->normalizeValue($row['Ownership Type'] ?? null) ?? '';

        // Business rule: Actual vs accrual is driven by the raw Accrual Category value.
        $actualAccrual = (($row['Accrual Category'] ?? null) === '##') ? 'Actual' : 'Accrual';

        // Business rule: US/ex-US depends on the local selling company range.
        $usExUs = $this->inRange($localSellingCompany, 1700, 1809) ? 'US' : 'ex-US';

        // Business rule: Contracting Party is narrowed to the special codes; all others become LOCAL.
        $contractingPartyFiltered = in_array($contractingParty, ['1756', '1100', '1902'], true)
            ? $contractingParty
            : 'LOCAL';

        // Business rule: String is a direct concatenation of the filtered contracting party plus the flags.
        $string = $contractingPartyFiltered
            . $rightsHolderLiableFlag
            . $globalTransferPricingFlag
            . $domesticForeignFlag
            . $ownershipType;

        // Business rule: 2110 applicability is a fixed whitelist lookup against the derived String.
        $applicableX201 = in_array($string, self::X201_ELIGIBLE_STRINGS, true) ? 'X' : '';

        // Business rule: Class uses ordered precedence. The first matching rule wins.
        $class = $this->calculateClass($contractingParty, $localSellingCompany, $rightsHolderLiableFlag, $globalTransferPricingFlag, $applicableX201, $contractingPartyFiltered);

        return [
            'Actual/Accrual' => $actualAccrual,
            'US/ex-US sale' => $usExUs,
            'Contracting Party Filtered' => $contractingPartyFiltered,
            'String' => $string,
            '2110 Applicable X201' => $applicableX201,
            'Class' => $class,
        ];
    }

    private function calculateClass(
        ?string $contractingParty,
        ?string $localSellingCompany,
        string $rightsHolderLiableFlag,
        string $globalTransferPricingFlag,
        string $applicableX201,
        string $contractingPartyFiltered
    ): string {
        if ($contractingParty === '1902') {
            return 'A';
        }

        if (
            $contractingParty === '1756'
            && $rightsHolderLiableFlag === 'Y'
            && !$this->inRange($localSellingCompany, 1700, 1808)
        ) {
            return 'B';
        }

        if ($contractingParty === '1756' && $this->inRange($localSellingCompany, 1700, 1808) && $applicableX201 === 'X') {
            return 'C';
        }

        if ($contractingParty === '1756' && $this->inRange($localSellingCompany, 1700, 1808) && $applicableX201 !== 'X') {
            return 'D';
        }

        if ($contractingParty === '1100' && !$this->inRange($localSellingCompany, 1700, 1808)) {
            return 'E';
        }

        if ($contractingParty === '1100' && $this->inRange($localSellingCompany, 1700, 1808)) {
            return 'F';
        }

        if ($contractingPartyFiltered === 'LOCAL') {
            return 'G';
        }

        if (
            $contractingParty === '1756'
            && $rightsHolderLiableFlag === 'N'
            && $globalTransferPricingFlag === 'Y'
            && !$this->inRange($localSellingCompany, 1700, 1808)
        ) {
            return 'H';
        }

        return '';
    }

    private function normalizeCode(mixed $value): ?string
    {
        $value = $this->normalizeValue($value);
        if ($value === null) {
            return null;
        }

        if (is_numeric($value) && (float) $value == (int) $value) {
            return (string) (int) $value;
        }

        return $value;
    }

    private function normalizeFlag(mixed $value): string
    {
        $normalized = $this->normalizeValue($value);
        return $normalized ?? '';
    }

    private function inRange(?string $value, int $min, int $max): bool
    {
        if ($value === null || $value === '') {
            return false;
        }

        if (!is_numeric($value)) {
            return false;
        }

        $number = (int) $value;
        return $number >= $min && $number <= $max;
    }
}