<?php
namespace Domain\Attribute;

class AttributeRepository
{
    private const MAX_PER_PAGE = 100;

    private $db;
    private ?string $defaultLangId = null;

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

    /**
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function listForAdmin(
        array $filters,
        string $sortColumn = 'o',
        string $sortDir = 'ASC',
        int $page = 1,
        int $perPage = 15
    ): array {
        $allowedSortColumns = [
            'id' => 'sa.id',
            'o' => 'sa.o',
            'name' => 'name_for_sort',
            'type' => 'sa.type',
            'status' => 'sa.status',
            'values_count' => 'values_count',
        ];

        $sortSql = $allowedSortColumns[$sortColumn] ?? 'sa.o';
        $sortDir = strtoupper(trim($sortDir)) === 'DESC' ? 'DESC' : 'ASC';
        $page = max(1, $page);
        $perPage = min(self::MAX_PER_PAGE, max(1, $perPage));
        $offset = ($page - 1) * $perPage;

        $whereData = $this->buildAdminWhere($filters);
        $whereSql = $whereData['sql'];
        $params = $whereData['params'];
        $params[':default_lang_id'] = $this->defaultLanguageId();

        $sqlCount = "
            SELECT COUNT(0)
            FROM pp_shop_attributes AS sa
            WHERE {$whereSql}
        ";
        $stmtCount = $this->db->query($sqlCount, $whereData['params']);
        $countRows = $stmtCount ? $stmtCount->fetchAll() : [];
        $total = isset($countRows[0][0]) ? (int)$countRows[0][0] : 0;

        $sql = "
            SELECT
                sa.id,
                sa.status,
                sa.type,
                sa.o,
                (
                    SELECT sal.name
                    FROM pp_shop_attributes_langs AS sal
                    WHERE sal.attribute_id = sa.id
                    AND sal.lang_id = :default_lang_id
                    LIMIT 1
                ) AS name_default,
                (
                    SELECT sal2.name
                    FROM pp_shop_attributes_langs AS sal2
                    INNER JOIN pp_langs AS pl2 ON pl2.id = sal2.lang_id
                    WHERE sal2.attribute_id = sa.id
                    AND sal2.name <> ''
                    ORDER BY pl2.o ASC
                    LIMIT 1
                ) AS name_any,
                (
                    SELECT COUNT(0)
                    FROM pp_shop_attributes_values AS sav
                    WHERE sav.attribute_id = sa.id
                ) AS values_count,
                COALESCE(
                    (
                        SELECT sal3.name
                        FROM pp_shop_attributes_langs AS sal3
                        WHERE sal3.attribute_id = sa.id
                        AND sal3.lang_id = :default_lang_id
                        LIMIT 1
                    ),
                    (
                        SELECT sal4.name
                        FROM pp_shop_attributes_langs AS sal4
                        INNER JOIN pp_langs AS pl4 ON pl4.id = sal4.lang_id
                        WHERE sal4.attribute_id = sa.id
                        AND sal4.name <> ''
                        ORDER BY pl4.o ASC
                        LIMIT 1
                    ),
                    ''
                ) AS name_for_sort
            FROM pp_shop_attributes AS sa
            WHERE {$whereSql}
            ORDER BY {$sortSql} {$sortDir}, sa.id ASC
            LIMIT {$perPage} OFFSET {$offset}
        ";

        $stmt = $this->db->query($sql, $params);
        $items = $stmt ? $stmt->fetchAll() : [];
        if (!is_array($items)) {
            $items = [];
        }

        foreach ($items as &$item) {
            $nameDefault = trim((string)($item['name_default'] ?? ''));
            $nameAny = trim((string)($item['name_any'] ?? ''));

            $item['id'] = (int)($item['id'] ?? 0);
            $item['status'] = $this->toSwitchValue($item['status'] ?? 0);
            $item['type'] = (int)($item['type'] ?? 0);
            $item['o'] = (int)($item['o'] ?? 0);
            $item['name'] = $nameDefault !== '' ? $nameDefault : $nameAny;
            $item['values_count'] = (int)($item['values_count'] ?? 0);

            unset($item['name_default'], $item['name_any'], $item['name_for_sort']);
        }
        unset($item);

        return [
            'items' => $items,
            'total' => $total,
        ];
    }

    public function findAttribute(int $attributeId): array
    {
        if ($attributeId <= 0) {
            return $this->defaultAttribute();
        }

        $attribute = $this->db->get('pp_shop_attributes', '*', ['id' => $attributeId]);
        if (!is_array($attribute)) {
            return $this->defaultAttribute();
        }

        $attribute['id'] = (int)($attribute['id'] ?? 0);
        $attribute['status'] = $this->toSwitchValue($attribute['status'] ?? 0);
        $attribute['type'] = (int)($attribute['type'] ?? 0);
        $attribute['o'] = (int)($attribute['o'] ?? 0);
        $attribute['languages'] = [];

        $translations = $this->db->select(
            'pp_shop_attributes_langs',
            ['lang_id', 'name'],
            ['attribute_id' => $attribute['id']]
        );
        if (is_array($translations)) {
            foreach ($translations as $translation) {
                $langId = (string)($translation['lang_id'] ?? '');
                if ($langId !== '') {
                    $attribute['languages'][$langId] = $translation;
                }
            }
        }

        return $attribute;
    }

    public function saveAttribute(array $data): ?int
    {
        $attributeId = (int)($data['id'] ?? 0);
        $row = [
            'status' => $this->toSwitchValue($data['status'] ?? 0),
            'type' => $this->toTypeValue($data['type'] ?? 0),
            'o' => max(0, (int)($data['o'] ?? 0)),
        ];

        if ($attributeId <= 0) {
            $this->db->insert('pp_shop_attributes', $row);
            $attributeId = (int)$this->db->id();
            if ($attributeId <= 0) {
                return null;
            }
        } else {
            $this->db->update('pp_shop_attributes', $row, ['id' => $attributeId]);
        }

        $names = [];
        if (isset($data['name']) && is_array($data['name'])) {
            $names = $data['name'];
        }
        $this->saveAttributeTranslations($attributeId, $names);

        $this->clearTempAndCache();
        $this->clearFrontCache($attributeId, 'frontAttributeDetails');
        return $attributeId;
    }

    public function deleteAttribute(int $attributeId): bool
    {
        if ($attributeId <= 0) {
            return false;
        }

        $deleted = (bool)$this->db->delete('pp_shop_attributes', ['id' => $attributeId]);
        if ($deleted) {
            $this->clearTempAndCache();
            $this->clearFrontCache($attributeId, 'frontAttributeDetails');
        }

        return $deleted;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function findValues(int $attributeId): array
    {
        if ($attributeId <= 0) {
            return [];
        }

        $rows = $this->db->select(
            'pp_shop_attributes_values',
            ['id', 'is_default', 'impact_on_the_price'],
            [
                'attribute_id' => $attributeId,
                'ORDER' => ['id' => 'ASC'],
            ]
        );
        if (!is_array($rows)) {
            return [];
        }

        $values = [];
        foreach ($rows as $row) {
            $valueId = (int)($row['id'] ?? 0);
            if ($valueId <= 0) {
                continue;
            }

            $value = [
                'id' => $valueId,
                'is_default' => $this->toSwitchValue($row['is_default'] ?? 0),
                'impact_on_the_price' => $this->toNullableNumeric($row['impact_on_the_price'] ?? null),
                'languages' => [],
            ];

            $translations = $this->db->select(
                'pp_shop_attributes_values_langs',
                ['lang_id', 'name', 'value'],
                ['value_id' => $valueId]
            );
            if (is_array($translations)) {
                foreach ($translations as $translation) {
                    $langId = (string)($translation['lang_id'] ?? '');
                    if ($langId !== '') {
                        $value['languages'][$langId] = $translation;
                    }
                }
            }

            $values[] = $value;
        }

        return $values;
    }

    /**
     * @param array<string, mixed> $payload
     */
    public function saveValues(int $attributeId, array $payload): bool
    {
        if ($attributeId <= 0) {
            return false;
        }

        $rowsRaw = [];
        if (isset($payload['rows']) && is_array($payload['rows'])) {
            $rowsRaw = $payload['rows'];
        } elseif (array_key_exists(0, $payload)) {
            $rowsRaw = $payload;
        }

        $rows = $this->normalizeValueRows($rowsRaw);

        $currentIds = $this->db->select(
            'pp_shop_attributes_values',
            'id',
            ['attribute_id' => $attributeId]
        );
        if (!is_array($currentIds)) {
            $currentIds = [];
        }
        $currentIds = array_values(array_unique(array_map('intval', $currentIds)));

        $incomingIds = [];
        foreach ($rows as $row) {
            $rowId = (int)($row['id'] ?? 0);
            if ($rowId > 0) {
                $incomingIds[$rowId] = $rowId;
            }
        }
        $incomingIds = array_values($incomingIds);

        $deleteIds = array_diff($currentIds, $incomingIds);
        foreach ($deleteIds as $deleteId) {
            $this->clearFrontCache((int)$deleteId, 'frontValueDetails');
            $this->db->delete('pp_shop_attributes_values_langs', ['value_id' => (int)$deleteId]);
            $this->db->delete('pp_shop_attributes_values', ['id' => (int)$deleteId]);
        }

        $defaultValueId = 0;
        foreach ($rows as $row) {
            $rowId = (int)($row['id'] ?? 0);
            if ($rowId > 0 && !$this->valueBelongsToAttribute($rowId, $attributeId)) {
                $rowId = 0;
            }

            $impactOnPrice = $this->toNullableNumeric($row['impact_on_the_price'] ?? null);

            if ($rowId <= 0) {
                $this->db->insert('pp_shop_attributes_values', [
                    'attribute_id' => $attributeId,
                    'impact_on_the_price' => $impactOnPrice,
                    'is_default' => 0,
                ]);
                $rowId = (int)$this->db->id();
                if ($rowId <= 0) {
                    return false;
                }
            } else {
                $this->db->update('pp_shop_attributes_values', [
                    'impact_on_the_price' => $impactOnPrice,
                ], [
                    'id' => $rowId,
                ]);
            }

            $translations = is_array($row['translations'] ?? null) ? $row['translations'] : [];
            $this->saveValueTranslations($rowId, $translations);
            $this->refreshCombinationPricesForValue($rowId, $impactOnPrice);
            $this->clearFrontCache($rowId, 'frontValueDetails');

            if (!empty($row['is_default'])) {
                $defaultValueId = $rowId;
            }
        }

        $this->db->update(
            'pp_shop_attributes_values',
            ['is_default' => 0],
            ['attribute_id' => $attributeId]
        );
        if ($defaultValueId > 0) {
            $this->db->update(
                'pp_shop_attributes_values',
                ['is_default' => 1],
                ['id' => $defaultValueId]
            );
        }

        $this->clearTempAndCache();
        return true;
    }

    /**
     * Legacy compatibility for old payload shape.
     *
     * @param array<string, array<int, string>> $names
     * @param array<string, array<int, string>> $values
     * @param array<string, array<int, string|int>> $ids
     * @param mixed $defaultValue
     * @param array<int, string|float|int|null> $impactOnThePrice
     */
    public function saveLegacyValues(
        int $attributeId,
        array $names,
        array $values,
        array $ids,
        $defaultValue = '',
        array $impactOnThePrice = []
    ): ?int {
        if ($attributeId <= 0) {
            return null;
        }

        $mainLanguage = $this->defaultLanguageId();
        $mainLanguageNames = $names[$mainLanguage] ?? [];
        if (!is_array($mainLanguageNames)) {
            $mainLanguageNames = [];
        }

        $rows = [];
        $count = count($mainLanguageNames);
        for ($i = 0; $i < $count; ++$i) {
            $rowId = 0;
            if (isset($ids[$mainLanguage]) && is_array($ids[$mainLanguage])) {
                $rowId = (int)($ids[$mainLanguage][$i] ?? 0);
            }

            $translations = [];
            foreach ($names as $langId => $langNames) {
                if (!is_string($langId) || !is_array($langNames)) {
                    continue;
                }

                $translations[$langId] = [
                    'name' => (string)($langNames[$i] ?? ''),
                    'value' => isset($values[$langId]) && is_array($values[$langId])
                        ? (string)($values[$langId][$i] ?? '')
                        : '',
                ];
            }

            $rows[] = [
                'id' => $rowId,
                'impact_on_the_price' => $impactOnThePrice[$i] ?? null,
                'is_default' => ((string)$defaultValue === (string)$i),
                'translations' => $translations,
            ];
        }

        $saved = $this->saveValues($attributeId, ['rows' => $rows]);
        return $saved ? $attributeId : null;
    }

    public function valueDetails(int $valueId): array
    {
        if ($valueId <= 0) {
            return [];
        }

        $value = $this->db->get('pp_shop_attributes_values', '*', ['id' => $valueId]);
        if (!is_array($value)) {
            return [];
        }

        $value['id'] = (int)($value['id'] ?? 0);
        $value['attribute_id'] = (int)($value['attribute_id'] ?? 0);
        $value['is_default'] = $this->toSwitchValue($value['is_default'] ?? 0);
        $value['impact_on_the_price'] = $this->toNullableNumeric($value['impact_on_the_price'] ?? null);
        $value['languages'] = [];

        $translations = $this->db->select(
            'pp_shop_attributes_values_langs',
            ['lang_id', 'name', 'value'],
            ['value_id' => $valueId]
        );
        if (is_array($translations)) {
            foreach ($translations as $translation) {
                $langId = (string)($translation['lang_id'] ?? '');
                if ($langId !== '') {
                    $value['languages'][$langId] = $translation;
                }
            }
        }

        return $value;
    }

    public function getAttributeNameById(int $attributeId, ?string $langId = null): string
    {
        if ($attributeId <= 0) {
            return '';
        }

        $languageId = $langId !== null && trim($langId) !== '' ? trim($langId) : $this->defaultLanguageId();
        return (string)$this->db->get(
            'pp_shop_attributes_langs',
            'name',
            [
                'AND' => [
                    'attribute_id' => $attributeId,
                    'lang_id' => $languageId,
                ],
            ]
        );
    }

    public function getAttributeValueById(int $valueId, ?string $langId = null): string
    {
        if ($valueId <= 0) {
            return '';
        }

        $languageId = $langId !== null && trim($langId) !== '' ? trim($langId) : $this->defaultLanguageId();

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "AttributeRepository::getAttributeValueById:{$valueId}:{$languageId}";
        $cached = $cacheHandler->get($cacheKey);

        if ($cached !== false && $cached !== null) {
            return (string)unserialize($cached);
        }

        $name = (string)$this->db->get(
            'pp_shop_attributes_values_langs',
            'name',
            [
                'AND' => [
                    'value_id' => $valueId,
                    'lang_id' => $languageId,
                ],
            ]
        );

        $cacheHandler->set($cacheKey, $name);

        return $name;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function getAttributesListForCombinations(): array
    {
        $rows = $this->db->select('pp_shop_attributes', '*', ['ORDER' => ['o' => 'ASC']]);
        if (!is_array($rows)) {
            return [];
        }

        $attributes = [];
        foreach ($rows as $row) {
            $attributeId = (int)($row['id'] ?? 0);
            if ($attributeId <= 0) {
                continue;
            }

            $attribute = $this->findAttribute($attributeId);
            $attribute['values'] = $this->findValues($attributeId);
            $attributes[] = $attribute;
        }

        return $attributes;
    }

    /**
     * Zwraca aktywne atrybuty z wartościami i wielojęzycznymi nazwami dla REST API.
     *
     * @return array<int, array<string, mixed>>
     */
    public function listForApi(): array
    {
        // 1. Get all active attribute IDs (1 query)
        $rows = $this->db->select('pp_shop_attributes', ['id', 'type', 'status'], [
            'status' => 1,
            'ORDER' => ['o' => 'ASC'],
        ]);
        if (!is_array($rows) || empty($rows)) {
            return [];
        }

        $attrIds = [];
        foreach ($rows as $row) {
            $id = (int)($row['id'] ?? 0);
            if ($id > 0) {
                $attrIds[] = $id;
            }
        }
        if (empty($attrIds)) {
            return [];
        }

        // 2. Batch load ALL attribute translations (1 query)
        $allAttrTranslations = $this->db->select(
            'pp_shop_attributes_langs',
            ['attribute_id', 'lang_id', 'name'],
            ['attribute_id' => $attrIds]
        );
        $attrNamesMap = [];
        if (is_array($allAttrTranslations)) {
            foreach ($allAttrTranslations as $t) {
                $aId = (int)($t['attribute_id'] ?? 0);
                $langId = (string)($t['lang_id'] ?? '');
                if ($aId > 0 && $langId !== '') {
                    $attrNamesMap[$aId][$langId] = (string)($t['name'] ?? '');
                }
            }
        }

        // 3. Batch load ALL values for those attribute IDs (1 query)
        $allValueRows = $this->db->select(
            'pp_shop_attributes_values',
            ['id', 'attribute_id', 'is_default', 'impact_on_the_price'],
            [
                'attribute_id' => $attrIds,
                'ORDER' => ['id' => 'ASC'],
            ]
        );
        $valuesByAttr = [];
        $allValueIds = [];
        if (is_array($allValueRows)) {
            foreach ($allValueRows as $vRow) {
                $valueId = (int)($vRow['id'] ?? 0);
                $attrId = (int)($vRow['attribute_id'] ?? 0);
                if ($valueId > 0 && $attrId > 0) {
                    $valuesByAttr[$attrId][] = $vRow;
                    $allValueIds[] = $valueId;
                }
            }
        }

        // 4. Batch load ALL value translations (1 query)
        $valueNamesMap = [];
        if (!empty($allValueIds)) {
            $allValueTranslations = $this->db->select(
                'pp_shop_attributes_values_langs',
                ['value_id', 'lang_id', 'name'],
                ['value_id' => $allValueIds]
            );
            if (is_array($allValueTranslations)) {
                foreach ($allValueTranslations as $vt) {
                    $vId = (int)($vt['value_id'] ?? 0);
                    $langId = (string)($vt['lang_id'] ?? '');
                    if ($vId > 0 && $langId !== '') {
                        $valueNamesMap[$vId][$langId] = (string)($vt['name'] ?? '');
                    }
                }
            }
        }

        // 5. Assemble result in-memory
        $result = [];
        foreach ($rows as $row) {
            $attributeId = (int)($row['id'] ?? 0);
            if ($attributeId <= 0) {
                continue;
            }

            $names = isset($attrNamesMap[$attributeId]) ? $attrNamesMap[$attributeId] : [];

            $values = [];
            if (isset($valuesByAttr[$attributeId])) {
                foreach ($valuesByAttr[$attributeId] as $vRow) {
                    $valueId = (int)$vRow['id'];
                    $impact = $vRow['impact_on_the_price'];
                    $values[] = [
                        'id' => $valueId,
                        'names' => isset($valueNamesMap[$valueId]) ? $valueNamesMap[$valueId] : [],
                        'is_default' => (int)($vRow['is_default'] ?? 0),
                        'impact_on_the_price' => ($impact !== null && $impact !== '') ? (float)$impact : null,
                    ];
                }
            }

            $result[] = [
                'id' => $attributeId,
                'type' => (int)($row['type'] ?? 0),
                'status' => (int)($row['status'] ?? 0),
                'names' => $names,
                'values' => $values,
            ];
        }

        return $result;
    }

    /**
     * Find existing attribute by name/type or create a new one for API integration.
     *
     * @return array{id:int,created:bool}|null
     */
    public function ensureAttributeForApi(string $name, int $type = 0, string $langId = 'pl'): ?array
    {
        $normalizedName = trim($name);
        $normalizedLangId = trim($langId) !== '' ? trim($langId) : 'pl';
        $normalizedType = $this->toTypeValue($type);
        if ($normalizedName === '') {
            return null;
        }

        $existingId = $this->findAttributeIdByNameAndType($normalizedName, $normalizedType);
        if ($existingId > 0) {
            return ['id' => $existingId, 'created' => false];
        }

        $this->db->insert('pp_shop_attributes', [
            'status' => 1,
            'type' => $normalizedType,
            'o' => $this->nextOrder(),
        ]);
        $attributeId = (int) $this->db->id();
        if ($attributeId <= 0) {
            return null;
        }

        $this->db->insert('pp_shop_attributes_langs', [
            'attribute_id' => $attributeId,
            'lang_id' => $normalizedLangId,
            'name' => $normalizedName,
        ]);

        $this->clearTempAndCache();
        $this->clearFrontCache($attributeId, 'frontAttributeDetails');

        return ['id' => $attributeId, 'created' => true];
    }

    /**
     * Find existing value by name within attribute or create a new one for API integration.
     *
     * @return array{id:int,created:bool}|null
     */
    public function ensureAttributeValueForApi(int $attributeId, string $name, string $langId = 'pl'): ?array
    {
        $normalizedName = trim($name);
        $normalizedLangId = trim($langId) !== '' ? trim($langId) : 'pl';
        $attributeId = max(0, $attributeId);

        if ($attributeId <= 0 || $normalizedName === '') {
            return null;
        }

        $attributeExists = (int) $this->db->count('pp_shop_attributes', ['id' => $attributeId]) > 0;
        if (!$attributeExists) {
            return null;
        }

        $existingId = $this->findAttributeValueIdByName($attributeId, $normalizedName);
        if ($existingId > 0) {
            return ['id' => $existingId, 'created' => false];
        }

        $this->db->insert('pp_shop_attributes_values', [
            'attribute_id' => $attributeId,
            'impact_on_the_price' => null,
            'is_default' => 0,
        ]);
        $valueId = (int) $this->db->id();
        if ($valueId <= 0) {
            return null;
        }

        $this->db->insert('pp_shop_attributes_values_langs', [
            'value_id' => $valueId,
            'lang_id' => $normalizedLangId,
            'name' => $normalizedName,
            'value' => null,
        ]);

        $this->clearTempAndCache();
        $this->clearFrontCache($valueId, 'frontValueDetails');

        return ['id' => $valueId, 'created' => true];
    }

    /**
     * @return array{sql: string, params: array<string, mixed>}
     */
    private function buildAdminWhere(array $filters): array
    {
        $where = ['1 = 1'];
        $params = [];

        $name = trim((string)($filters['name'] ?? ''));
        if ($name !== '') {
            if (strlen($name) > 255) {
                $name = substr($name, 0, 255);
            }
            $where[] = 'EXISTS (
                SELECT 1
                FROM pp_shop_attributes_langs AS sal_filter
                WHERE sal_filter.attribute_id = sa.id
                AND sal_filter.name LIKE :name
            )';
            $params[':name'] = '%' . $name . '%';
        }

        $status = trim((string)($filters['status'] ?? ''));
        if ($status === '0' || $status === '1') {
            $where[] = 'sa.status = :status';
            $params[':status'] = (int)$status;
        }

        return [
            'sql' => implode(' AND ', $where),
            'params' => $params,
        ];
    }

    /**
     * @param array<string, string> $names
     */
    private function saveAttributeTranslations(int $attributeId, array $names): void
    {
        foreach ($names as $langId => $name) {
            if (!is_string($langId) || trim($langId) === '') {
                continue;
            }

            $translationName = trim((string)$name);
            $where = [
                'AND' => [
                    'attribute_id' => $attributeId,
                    'lang_id' => $langId,
                ],
            ];

            $translationId = $this->db->get('pp_shop_attributes_langs', 'id', $where);
            if ($translationId) {
                $this->db->update('pp_shop_attributes_langs', [
                    'name' => $translationName,
                ], [
                    'id' => (int)$translationId,
                ]);
            } else {
                $this->db->insert('pp_shop_attributes_langs', [
                    'attribute_id' => $attributeId,
                    'lang_id' => $langId,
                    'name' => $translationName,
                ]);
            }
        }
    }

    /**
     * @param array<string, mixed> $translations
     */
    private function saveValueTranslations(int $valueId, array $translations): void
    {
        foreach ($translations as $langId => $translationData) {
            if (!is_string($langId) || trim($langId) === '') {
                continue;
            }

            $name = '';
            $value = null;
            if (is_array($translationData)) {
                $name = trim((string)($translationData['name'] ?? ''));
                $rawValue = trim((string)($translationData['value'] ?? ''));
                $value = $rawValue !== '' ? $rawValue : null;
            } else {
                $name = trim((string)$translationData);
            }

            $where = [
                'AND' => [
                    'value_id' => $valueId,
                    'lang_id' => $langId,
                ],
            ];

            $translationId = $this->db->get('pp_shop_attributes_values_langs', 'id', $where);
            if ($name === '') {
                if ($translationId) {
                    $this->db->delete('pp_shop_attributes_values_langs', ['id' => (int)$translationId]);
                }
                continue;
            }

            if ($translationId) {
                $this->db->update('pp_shop_attributes_values_langs', [
                    'name' => $name,
                    'value' => $value,
                ], [
                    'id' => (int)$translationId,
                ]);
            } else {
                $this->db->insert('pp_shop_attributes_values_langs', [
                    'value_id' => $valueId,
                    'lang_id' => $langId,
                    'name' => $name,
                    'value' => $value,
                ]);
            }
        }
    }

    private function valueBelongsToAttribute(int $valueId, int $attributeId): bool
    {
        if ($valueId <= 0 || $attributeId <= 0) {
            return false;
        }

        return (bool)$this->db->count('pp_shop_attributes_values', [
            'AND' => [
                'id' => $valueId,
                'attribute_id' => $attributeId,
            ],
        ]);
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<int, array<string, mixed>>
     */
    private function normalizeValueRows(array $rows): array
    {
        $normalizedRows = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }

            $translations = [];
            if (isset($row['translations']) && is_array($row['translations'])) {
                $translations = $row['translations'];
            }

            $normalizedRows[] = [
                'id' => (int)($row['id'] ?? 0),
                'impact_on_the_price' => $row['impact_on_the_price'] ?? null,
                'is_default' => !empty($row['is_default']),
                'translations' => $translations,
            ];
        }

        return $normalizedRows;
    }

    private function refreshCombinationPricesForValue(int $valueId, ?string $impactOnThePrice): void
    {
        if ($valueId <= 0 || $impactOnThePrice === null) {
            return;
        }

        $impact = (float)$impactOnThePrice;

        $products = $this->db->select('pp_shop_products_attributes', ['product_id'], ['value_id' => $valueId]);
        if (!is_array($products)) {
            return;
        }

        foreach ($products as $row) {
            $productId = (int)($row['product_id'] ?? 0);
            if ($productId <= 0) {
                continue;
            }

            $parentId = (int)$this->db->get('pp_shop_products', 'parent_id', ['id' => $productId]);
            if ($parentId <= 0) {
                continue;
            }

            $parentProduct = $this->db->get('pp_shop_products', '*', ['id' => $parentId]);
            if (!is_array($parentProduct)) {
                continue;
            }

            $parentPriceBrutto = (float)($parentProduct['price_brutto'] ?? 0);
            $parentVat = (float)($parentProduct['vat'] ?? 0);
            $parentPriceBruttoPromo = $parentProduct['price_brutto_promo'];
            $parentPriceNettoPromo = $parentProduct['price_netto_promo'];

            if ($impact > 0) {
                $priceBrutto = $parentPriceBrutto + $impact;
                $priceNetto = $this->normalizeDecimal($priceBrutto / (1 + ($parentVat / 100)), 2);

                if ($parentPriceNettoPromo !== null && $parentPriceBruttoPromo !== null) {
                    $priceBruttoPromo = (float)$parentPriceBruttoPromo + $impact;
                    $priceNettoPromo = $this->normalizeDecimal($priceBruttoPromo / (1 + ($parentVat / 100)), 2);
                } else {
                    $priceBruttoPromo = null;
                    $priceNettoPromo = null;
                }

                $this->db->update('pp_shop_products', [
                    'price_netto' => $priceNetto,
                    'price_brutto' => $priceBrutto,
                    'price_netto_promo' => $priceNettoPromo,
                    'price_brutto_promo' => $priceBruttoPromo,
                    'date_modify' => date('Y-m-d H:i:s'),
                ], [
                    'id' => $productId,
                ]);
                continue;
            }

            if (abs($impact) < 0.000001) {
                $this->db->update('pp_shop_products', [
                    'price_netto' => null,
                    'price_brutto' => null,
                    'price_netto_promo' => null,
                    'price_brutto_promo' => null,
                    'quantity' => null,
                    'stock_0_buy' => null,
                    'date_modify' => date('Y-m-d H:i:s'),
                ], [
                    'id' => $productId,
                ]);
            }
        }
    }

    private function toSwitchValue($value): int
    {
        if (is_bool($value)) {
            return $value ? 1 : 0;
        }

        if (is_numeric($value)) {
            return ((int)$value) === 1 ? 1 : 0;
        }

        if (is_string($value)) {
            $normalized = strtolower(trim($value));
            return in_array($normalized, ['1', 'on', 'true', 'yes'], true) ? 1 : 0;
        }

        return 0;
    }

    private function toTypeValue($value): int
    {
        $type = (int)$value;
        if ($type < 0 || $type > 2) {
            return 0;
        }

        return $type;
    }

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

        $stringValue = trim((string)$value);
        if ($stringValue === '') {
            return null;
        }

        return str_replace(',', '.', $stringValue);
    }

    private function defaultAttribute(): array
    {
        return [
            'id' => 0,
            'status' => 1,
            'type' => 0,
            'o' => $this->nextOrder(),
            'languages' => [],
        ];
    }

    private function nextOrder(): int
    {
        $maxOrder = $this->db->max('pp_shop_attributes', 'o');
        return max(0, (int)$maxOrder + 1);
    }

    private function defaultLanguageId(): string
    {
        if ($this->defaultLangId !== null) {
            return $this->defaultLangId;
        }

        $rows = $this->db->select('pp_langs', ['id', 'start', 'o'], [
            'status' => 1,
            'ORDER' => ['start' => 'DESC', 'o' => 'ASC'],
        ]);

        if (is_array($rows) && !empty($rows)) {
            $this->defaultLangId = (string)($rows[0]['id'] ?? '');
        } else {
            $this->defaultLangId = '';
        }

        return $this->defaultLangId;
    }

    private function findAttributeIdByNameAndType(string $name, int $type): int
    {
        $statement = $this->db->query(
            'SELECT sa.id
             FROM pp_shop_attributes sa
             INNER JOIN pp_shop_attributes_langs sal ON sal.attribute_id = sa.id
             WHERE sa.type = :type
               AND LOWER(TRIM(sal.name)) = LOWER(TRIM(:name))
             ORDER BY sa.id ASC
             LIMIT 1',
            [
                ':type' => $type,
                ':name' => $name,
            ]
        );
        if (!$statement) {
            return 0;
        }

        $id = $statement->fetchColumn();
        return $id === false ? 0 : (int) $id;
    }

    private function findAttributeValueIdByName(int $attributeId, string $name): int
    {
        $statement = $this->db->query(
            'SELECT sav.id
             FROM pp_shop_attributes_values sav
             INNER JOIN pp_shop_attributes_values_langs savl ON savl.value_id = sav.id
             WHERE sav.attribute_id = :attribute_id
               AND LOWER(TRIM(savl.name)) = LOWER(TRIM(:name))
             ORDER BY sav.id ASC
             LIMIT 1',
            [
                ':attribute_id' => $attributeId,
                ':name' => $name,
            ]
        );
        if (!$statement) {
            return 0;
        }

        $id = $statement->fetchColumn();
        return $id === false ? 0 : (int) $id;
    }

    // ── Frontend methods ──────────────────────────────────────────

    public function frontAttributeDetails(int $attributeId, string $langId): array
    {
        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "AttributeRepository::frontAttributeDetails:$attributeId:$langId";

        $objectData = $cacheHandler->get($cacheKey);
        if ($objectData) {
            $cached = @unserialize($objectData);
            if (is_array($cached)) {
                return $cached;
            }
            $cacheHandler->delete($cacheKey);
        }

        $attribute = $this->db->get('pp_shop_attributes', '*', ['id' => (int)$attributeId]);
        if (!is_array($attribute)) {
            $attribute = ['id' => $attributeId, 'status' => 0, 'type' => 0];
        }

        $attribute['language'] = $this->db->get('pp_shop_attributes_langs', ['lang_id', 'name'], [
            'AND' => [
                'attribute_id' => (int)$attributeId,
                'lang_id' => $langId,
            ],
        ]);
        if (!is_array($attribute['language'])) {
            $attribute['language'] = ['lang_id' => $langId, 'name' => ''];
        }

        $cacheHandler->set($cacheKey, $attribute);
        return $attribute;
    }

    public function frontValueDetails(int $valueId, string $langId): array
    {
        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "AttributeRepository::frontValueDetails:$valueId:$langId";

        $objectData = $cacheHandler->get($cacheKey);
        if ($objectData) {
            $cached = @unserialize($objectData);
            if (is_array($cached)) {
                return $cached;
            }
            $cacheHandler->delete($cacheKey);
        }

        $value = $this->db->get('pp_shop_attributes_values', '*', ['id' => (int)$valueId]);
        if (!is_array($value)) {
            $value = ['id' => $valueId];
        }

        $value['language'] = $this->db->get('pp_shop_attributes_values_langs', ['lang_id', 'name'], [
            'AND' => [
                'value_id' => (int)$valueId,
                'lang_id' => $langId,
            ],
        ]);
        if (!is_array($value['language'])) {
            $value['language'] = ['lang_id' => $langId, 'name' => ''];
        }

        $cacheHandler->set($cacheKey, $value);
        return $value;
    }

    private function clearFrontCache(int $id, string $type): void
    {
        if ($id <= 0 || !class_exists('\Shared\Cache\CacheHandler')) {
            return;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $langs = $this->db->select('pp_langs', 'id', ['status' => 1]);
        if (!is_array($langs)) {
            return;
        }

        foreach ($langs as $langId) {
            $cacheHandler->delete("AttributeRepository::$type:$id:$langId");
        }
    }

    private function clearTempAndCache(): void
    {
        \Shared\Helpers\Helpers::delete_dir('../temp/');
        \Shared\Helpers\Helpers::delete_cache();
    }

    private function normalizeDecimal(float $value, int $precision = 2): float
    {
        return round($value, $precision);
    }

    public function isValueDefault(int $valueId)
    {
        if ($valueId <= 0) {
            return null;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "attr_value_default:{$valueId}";
        $cached = $cacheHandler->get($cacheKey);

        if ($cached) {
            return unserialize($cached);
        }

        $isDefault = $this->db->get('pp_shop_attributes_values', 'is_default', ['id' => $valueId]);
        $cacheHandler->set($cacheKey, $isDefault);

        return $isDefault;
    }

    public function getAttributeOrder(int $attributeId)
    {
        if ($attributeId <= 0) {
            return null;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "attr_order:{$attributeId}";
        $cached = $cacheHandler->get($cacheKey);

        if ($cached) {
            return unserialize($cached);
        }

        $order = $this->db->get('pp_shop_attributes', 'o', ['id' => $attributeId]);
        $cacheHandler->set($cacheKey, $order);

        return $order;
    }

    public function getAttributeNameByValue(int $valueId, string $langId)
    {
        if ($valueId <= 0) {
            return null;
        }

        $stmt = $this->db->query(
            'SELECT name FROM pp_shop_attributes_langs AS psal '
            . 'INNER JOIN pp_shop_attributes_values AS psav ON psal.attribute_id = psav.attribute_id '
            . 'WHERE psav.id = :value_id AND lang_id = :lang_id',
            [':value_id' => $valueId, ':lang_id' => $langId]
        );

        if (!$stmt) {
            return null;
        }

        $row = $stmt->fetch(\PDO::FETCH_ASSOC);

        return is_array($row) ? ($row['name'] ?? null) : null;
    }
}