<?php
namespace Domain\Product;

/**
 * Repository odpowiedzialny za dostęp do danych produktów
 *
 * Zgodnie z wzorcem Repository Pattern, ta klasa enkapsuluje
 * logikę dostępu do bazy danych dla produktów.
 */
class ProductRepository
{
    private const MAX_PER_PAGE = 100;

    /**
     * @var \medoo Instancja Medoo ORM
     */
    private $db;

    /**
     * Konstruktor - przyjmuje instancję bazy danych
     *
     * @param \medoo $db Instancja Medoo ORM
     */
    public function __construct($db)
    {
        $this->db = $db;
    }

    /**
     * Pobiera stan magazynowy produktu
     *
     * @param int $productId ID produktu
     * @return int|null Ilość produktu lub null jeśli nie znaleziono
     */
    public function getQuantity(int $productId): ?int
    {
        $quantity = $this->db->get('pp_shop_products', 'quantity', ['id' => $productId]);

        // Medoo zwraca false jeśli nie znaleziono
        return $quantity !== false ? (int)$quantity : null;
    }

    /**
     * Pobiera produkt po ID
     *
     * @param int $productId ID produktu
     * @return array|null Dane produktu lub null
     */
    public function find(int $productId): ?array
    {
        $product = $this->db->get('pp_shop_products', '*', ['id' => $productId]);
        return $product ?: null;
    }

    /**
     * Zwraca liste produktow z archiwum do panelu admin.
     *
     * @return array{items: array<int, array<string, mixed>>, total: int}
     */
    public function listArchivedForAdmin(
        array $filters,
        string $sortColumn = 'id',
        string $sortDir = 'DESC',
        int $page = 1,
        int $perPage = 10
    ): array {
        $allowedSortColumns = [
            'id' => 'psp.id',
            'name' => 'name',
            'price_brutto' => 'psp.price_brutto',
            'price_brutto_promo' => 'psp.price_brutto_promo',
            'quantity' => 'psp.quantity',
            'combinations' => 'combinations',
        ];

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

        $where = ['psp.archive = 1', 'psp.parent_id IS NULL'];
        $params = [];

        $phrase = trim((string)($filters['phrase'] ?? ''));
        if (strlen($phrase) > 255) {
            $phrase = substr($phrase, 0, 255);
        }

        if ($phrase !== '') {
            $where[] = '(
                psp.ean LIKE :phrase
                OR psp.sku LIKE :phrase
                OR EXISTS (
                    SELECT 1
                    FROM pp_shop_products_langs AS pspl2
                    WHERE pspl2.product_id = psp.id
                      AND pspl2.name LIKE :phrase
                )
            )';
            $params[':phrase'] = '%' . $phrase . '%';
        }

        $whereSql = implode(' AND ', $where);

        $sqlCount = "
            SELECT COUNT(0)
            FROM pp_shop_products AS psp
            WHERE {$whereSql}
        ";

        $stmtCount = $this->db->query($sqlCount, $params);
        $countRows = $stmtCount ? $stmtCount->fetchAll() : [];
        $total = isset($countRows[0][0]) ? (int)$countRows[0][0] : 0;

        $sql = "
            SELECT
                psp.id,
                psp.price_brutto,
                psp.price_brutto_promo,
                psp.quantity,
                psp.sku,
                psp.ean,
                (
                    SELECT pspl.name
                    FROM pp_shop_products_langs AS pspl
                    INNER JOIN pp_langs AS pl ON pl.id = pspl.lang_id
                    WHERE pspl.product_id = psp.id
                      AND pspl.name <> ''
                    ORDER BY pl.o ASC
                    LIMIT 1
                ) AS name,
                (
                    SELECT pspi.src
                    FROM pp_shop_products_images AS pspi
                    WHERE pspi.product_id = psp.id
                    ORDER BY pspi.o ASC, pspi.id ASC
                    LIMIT 1
                ) AS image_src,
                (
                    SELECT pspi.alt
                    FROM pp_shop_products_images AS pspi
                    WHERE pspi.product_id = psp.id
                    ORDER BY pspi.o ASC, pspi.id ASC
                    LIMIT 1
                ) AS image_alt,
                (
                    SELECT COUNT(0)
                    FROM pp_shop_products AS pspc
                    WHERE pspc.parent_id = psp.id
                ) AS combinations
            FROM pp_shop_products AS psp
            WHERE {$whereSql}
            ORDER BY {$sortSql} {$sortDir}, psp.id {$sortDir}
            LIMIT {$perPage} OFFSET {$offset}
        ";

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

        return [
            'items' => is_array($items) ? $items : [],
            'total' => $total,
        ];
    }

    /**
     * Pobiera cenę produktu (promocyjną jeśli jest niższa, w przeciwnym razie regularną)
     *
     * @param int $productId ID produktu
     * @return float|null Cena brutto lub null jeśli nie znaleziono
     */
    public function getPrice(int $productId): ?float
    {
        $prices = $this->db->get('pp_shop_products', ['price_brutto', 'price_brutto_promo'], ['id' => $productId]);

        if (!$prices) {
            return null;
        }

        if ($prices['price_brutto_promo'] != '' && $prices['price_brutto_promo'] < $prices['price_brutto']) {
            return (float)$prices['price_brutto_promo'];
        }

        return (float)$prices['price_brutto'];
    }

    /**
     * Pobiera nazwę produktu w danym języku
     *
     * @param int $productId ID produktu
     * @param string $langId ID języka
     * @return string|null Nazwa produktu lub null jeśli nie znaleziono
     */
    public function getName(int $productId, string $langId): ?string
    {
        $name = $this->db->get('pp_shop_products_langs', 'name', ['AND' => ['product_id' => $productId, 'lang_id' => $langId]]);

        return $name ?: null;
    }

    /**
     * Aktualizuje ilość produktu
     *
     * @param int $productId ID produktu
     * @param int $quantity Nowa ilość
     * @return bool Czy aktualizacja się powiodła
     */
    public function updateQuantity(int $productId, int $quantity): bool
    {
        $result = $this->db->update(
            'pp_shop_products',
            ['quantity' => $quantity],
            ['id' => $productId]
        );

        return $result !== false;
    }

    /**
     * Przywraca produkt z archiwum (wraz z kombinacjami)
     *
     * @param int $productId ID produktu
     * @return bool Czy operacja się powiodła
     */
    public function unarchive(int $productId): bool
    {
        $this->db->update( 'pp_shop_products', [ 'status' => 1, 'archive' => 0 ], [ 'id' => $productId ] );
        $this->db->update( 'pp_shop_products', [ 'status' => 1, 'archive' => 0 ], [ 'parent_id' => $productId ] );

        return true;
    }

    /**
     * Przenosi produkt do archiwum (wraz z kombinacjami)
     *
     * @param int $productId ID produktu
     * @return bool Czy operacja się powiodła
     */
    public function archive(int $productId): bool
    {
        $this->db->update( 'pp_shop_products', [ 'status' => 0, 'archive' => 1 ], [ 'id' => $productId ] );
        $this->db->update( 'pp_shop_products', [ 'status' => 0, 'archive' => 1 ], [ 'parent_id' => $productId ] );

        return true;
    }

    /**
     * Pobiera listę wszystkich produktów głównych (id => name) do masowej edycji.
     * Zwraca tylko produkty bez parent_id (bez kombinacji).
     *
     * @return array<int, string> Mapa id => nazwa produktu
     */
    public function allProductsForMassEdit(): array
    {
        $defaultLang = $this->db->get( 'pp_langs', 'id', [ 'start' => 1 ] );
        if ( !$defaultLang ) {
            $defaultLang = 'pl';
        }

        $results = $this->db->select( 'pp_shop_products', 'id', [ 'parent_id' => null ] );
        $products = [];

        if ( is_array( $results ) ) {
            foreach ( $results as $id ) {
                $name = $this->db->get( 'pp_shop_products_langs', 'name', [
                    'AND' => [ 'product_id' => $id, 'lang_id' => $defaultLang ]
                ] );
                $products[ (int) $id ] = $name ?: '';
            }
        }

        return $products;
    }

    /**
     * Pobiera listę ID produktów przypisanych do danej kategorii.
     *
     * @param int $categoryId ID kategorii
     * @return int[] Lista ID produktów
     */
    public function getProductsByCategory(int $categoryId): array
    {
        $results = $this->db->select(
            'pp_shop_products_categories',
            'product_id',
            [ 'category_id' => $categoryId ]
        );

        return is_array( $results ) ? $results : [];
    }

    /**
     * Aplikuje rabat procentowy na produkt (cena promocyjna = cena - X%).
     * Aktualizuje również ceny kombinacji produktu.
     *
     * @param int $productId ID produktu
     * @param float $discountPercent Procent rabatu
     * @return array|null Tablica z price_brutto i price_brutto_promo lub null przy błędzie
     */
    public function applyDiscountPercent(int $productId, float $discountPercent): ?array
    {
        $product = $this->db->get( 'pp_shop_products', [
            'vat', 'price_brutto', 'price_netto'
        ], [ 'id' => $productId ] );

        if ( !$product ) {
            return null;
        }

        $vat = $product['vat'];
        $priceBrutto = (float) $product['price_brutto'];
        $priceNetto = (float) $product['price_netto'];

        $priceBruttoPromo = $priceBrutto - ( $priceBrutto * ( $discountPercent / 100 ) );
        $priceNettoPromo = $priceNetto - ( $priceNetto * ( $discountPercent / 100 ) );

        if ( $priceBrutto == $priceBruttoPromo ) {
            $priceBruttoPromo = null;
        }
        if ( $priceNetto == $priceNettoPromo ) {
            $priceNettoPromo = null;
        }

        $this->db->update( 'pp_shop_products', [
            'price_brutto_promo' => $priceBruttoPromo,
            'price_netto_promo' => $priceNettoPromo
        ], [ 'id' => $productId ] );

        $this->updateCombinationPrices( $productId, $priceNetto, $vat, $priceNettoPromo );

        return [
            'price_brutto' => $priceBrutto,
            'price_brutto_promo' => $priceBruttoPromo
        ];
    }

    // ─── Krok 1: metody CRUD admin ───────────────────────────────────

    /**
     * Liczba produktów (nie-archiwum, parent_id IS NULL).
     */
    public function countProducts(?array $where = null): int
    {
        if ( $where ) {
            return (int) $this->db->count( 'pp_shop_products', $where );
        }

        return (int) $this->db->count( 'pp_shop_products', [ 'archive' => 0 ] );
    }

    /**
     * Lista produktów do panelu admin (paginacja, sortowanie, filtrowanie).
     *
     * @return array{items: array, total: int}
     */
    public function listForAdmin(
        array $filters = [],
        string $sortColumn = 'id',
        string $sortDir = 'DESC',
        int $page = 1,
        int $perPage = 15
    ): array {
        $page = max( 1, $page );
        $perPage = min( max( 1, $perPage ), self::MAX_PER_PAGE );
        $offset = ( $page - 1 ) * $perPage;

        $allowedSort = [ 'id', 'name', 'price_brutto', 'status', 'promoted', 'quantity' ];
        if ( !in_array( $sortColumn, $allowedSort, true ) ) {
            $sortColumn = 'id';
        }
        $sortDir = strtoupper( $sortDir ) === 'ASC' ? 'ASC' : 'DESC';

        $conditions = [ 'psp.archive = 0', 'psp.parent_id IS NULL' ];
        $params = [];

        $search = trim( (string) ( $filters['search'] ?? '' ) );
        if ( $search !== '' ) {
            $conditions[] = '( pspl.name LIKE :search OR psp.ean LIKE :search_ean OR psp.sku LIKE :search_sku )';
            $params[':search'] = '%' . $search . '%';
            $params[':search_ean'] = '%' . $search . '%';
            $params[':search_sku'] = '%' . $search . '%';
        }

        $statusFilter = (string) ( $filters['status'] ?? '' );
        if ( $statusFilter === '1' || $statusFilter === '0' ) {
            $conditions[] = 'psp.status = :status';
            $params[':status'] = (int) $statusFilter;
        }

        $promotedFilter = (string) ( $filters['promoted'] ?? '' );
        if ( $promotedFilter === '1' || $promotedFilter === '0' ) {
            $conditions[] = 'psp.promoted = :promoted';
            $params[':promoted'] = (int) $promotedFilter;
        }

        $whereSql = implode( ' AND ', $conditions );
        $needsJoin = $search !== '' || $sortColumn === 'name';

        $joinSql = $needsJoin
            ? 'INNER JOIN pp_shop_products_langs AS pspl ON pspl.product_id = psp.id AND pspl.lang_id = ' . $this->db->quote( $this->defaultLangId() )
            : '';

        $orderColumn = $sortColumn === 'name' ? 'pspl.name' : 'psp.' . $sortColumn;

        $countStmt = $this->db->query(
            'SELECT COUNT( DISTINCT psp.id ) AS total '
            . 'FROM pp_shop_products AS psp '
            . $joinSql . ' '
            . 'WHERE ' . $whereSql,
            $params
        );
        $countRow = $countStmt ? $countStmt->fetchAll( \PDO::FETCH_ASSOC ) : [];
        $total = isset( $countRow[0]['total'] ) ? (int) $countRow[0]['total'] : 0;

        $stmt = $this->db->query(
            'SELECT DISTINCT psp.id '
            . 'FROM pp_shop_products AS psp '
            . $joinSql . ' '
            . 'WHERE ' . $whereSql
            . ' ORDER BY ' . $orderColumn . ' ' . $sortDir . ', psp.id DESC'
            . ' LIMIT ' . $offset . ', ' . $perPage,
            $params
        );

        $rows = $stmt ? $stmt->fetchAll( \PDO::FETCH_ASSOC ) : [];
        $items = [];

        foreach ( $rows as $row ) {
            $id = (int) ( $row['id'] ?? 0 );
            if ( $id > 0 ) {
                $product = $this->findForAdmin( $id );
                if ( $product ) {
                    $items[] = $product;
                }
            }
        }

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

    /**
     * Lista produktów dla REST API z filtrowaniem, sortowaniem i paginacją.
     *
     * @param array $filters Filtry: search, status, promoted
     * @param string $sortColumn Kolumna sortowania
     * @param string $sortDir Kierunek: ASC|DESC
     * @param int $page Numer strony
     * @param int $perPage Wyników na stronę (max 100)
     * @return array{items: array, total: int, page: int, per_page: int}
     */
    public function listForApi(
        array $filters = [],
        string $sortColumn = 'id',
        string $sortDir = 'DESC',
        int $page = 1,
        int $perPage = 50
    ): array {
        $allowedSortColumns = [
            'id' => 'psp.id',
            'name' => 'name',
            'price_brutto' => 'psp.price_brutto',
            'status' => 'psp.status',
            'promoted' => 'psp.promoted',
            'quantity' => 'psp.quantity',
        ];

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

        $where = ['psp.archive = 0', 'psp.parent_id IS NULL'];
        $params = [];

        $search = trim((string)($filters['search'] ?? ''));
        if (strlen($search) > 255) {
            $search = substr($search, 0, 255);
        }

        if ($search !== '') {
            $where[] = '(
                psp.ean LIKE :search
                OR psp.sku LIKE :search
                OR EXISTS (
                    SELECT 1
                    FROM pp_shop_products_langs AS pspl2
                    WHERE pspl2.product_id = psp.id
                      AND pspl2.name LIKE :search
                )
            )';
            $params[':search'] = '%' . $search . '%';
        }

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

        $promotedFilter = (string)($filters['promoted'] ?? '');
        if ($promotedFilter === '1' || $promotedFilter === '0') {
            $where[] = 'psp.promoted = :promoted';
            $params[':promoted'] = (int)$promotedFilter;
        }

        // Attribute filters: attribute_{id} = {value_id}
        $attrFilters = isset($filters['attributes']) && is_array($filters['attributes']) ? $filters['attributes'] : [];
        $attrIdx = 0;
        foreach ($attrFilters as $attrId => $valueId) {
            $attrId = (int)$attrId;
            $valueId = (int)$valueId;
            if ($attrId <= 0 || $valueId <= 0) {
                continue;
            }
            $paramAttr = ':attr_id_' . $attrIdx;
            $paramVal = ':attr_val_' . $attrIdx;
            $where[] = "EXISTS (
                SELECT 1
                FROM pp_shop_products AS psp_var{$attrIdx}
                INNER JOIN pp_shop_products_attributes AS pspa{$attrIdx}
                    ON pspa{$attrIdx}.product_id = psp_var{$attrIdx}.id
                WHERE psp_var{$attrIdx}.parent_id = psp.id
                  AND pspa{$attrIdx}.attribute_id = {$paramAttr}
                  AND pspa{$attrIdx}.value_id = {$paramVal}
            )";
            $params[$paramAttr] = $attrId;
            $params[$paramVal] = $valueId;
            $attrIdx++;
        }

        $whereSql = implode(' AND ', $where);

        $sqlCount = "
            SELECT COUNT(0)
            FROM pp_shop_products AS psp
            WHERE {$whereSql}
        ";

        $stmtCount = $this->db->query($sqlCount, $params);
        $countRows = $stmtCount ? $stmtCount->fetchAll() : [];
        $total = isset($countRows[0][0]) ? (int)$countRows[0][0] : 0;

        $sql = "
            SELECT
                psp.id,
                psp.sku,
                psp.ean,
                psp.price_brutto,
                psp.price_brutto_promo,
                psp.price_netto,
                psp.price_netto_promo,
                psp.quantity,
                psp.status,
                psp.promoted,
                psp.vat,
                psp.weight,
                psp.date_add,
                psp.date_modify,
                (
                    SELECT pspl.name
                    FROM pp_shop_products_langs AS pspl
                    INNER JOIN pp_langs AS pl ON pl.id = pspl.lang_id
                    WHERE pspl.product_id = psp.id
                      AND pspl.name <> ''
                    ORDER BY pl.o ASC
                    LIMIT 1
                ) AS name,
                (
                    SELECT pspi.src
                    FROM pp_shop_products_images AS pspi
                    WHERE pspi.product_id = psp.id
                    ORDER BY pspi.o ASC, pspi.id ASC
                    LIMIT 1
                ) AS main_image
            FROM pp_shop_products AS psp
            WHERE {$whereSql}
            ORDER BY {$sortSql} {$sortDir}, psp.id {$sortDir}
            LIMIT {$perPage} OFFSET {$offset}
        ";

        $stmt = $this->db->query($sql, $params);
        $rows = $stmt ? $stmt->fetchAll(\PDO::FETCH_ASSOC) : [];

        $items = [];
        if (is_array($rows)) {
            foreach ($rows as $row) {
                $items[] = [
                    'id' => (int)$row['id'],
                    'sku' => $row['sku'],
                    'ean' => $row['ean'],
                    'name' => $row['name'],
                    'price_brutto' => $row['price_brutto'] !== null ? (float)$row['price_brutto'] : null,
                    'price_brutto_promo' => $row['price_brutto_promo'] !== null ? (float)$row['price_brutto_promo'] : null,
                    'price_netto' => $row['price_netto'] !== null ? (float)$row['price_netto'] : null,
                    'price_netto_promo' => $row['price_netto_promo'] !== null ? (float)$row['price_netto_promo'] : null,
                    'quantity' => (int)$row['quantity'],
                    'status' => (int)$row['status'],
                    'promoted' => (int)$row['promoted'],
                    'vat' => (int)$row['vat'],
                    'weight' => $row['weight'] !== null ? (float)$row['weight'] : null,
                    'main_image' => $row['main_image'],
                    'date_add' => $row['date_add'],
                    'date_modify' => $row['date_modify'],
                ];
            }
        }

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

    /**
     * Szczegóły produktu dla REST API.
     *
     * @param int $id ID produktu
     * @return array|null Dane produktu lub null
     */
    public function findForApi(int $id): ?array
    {
        $product = $this->db->get('pp_shop_products', '*', ['id' => $id]);
        if (!$product) {
            return null;
        }

        if (!empty($product['archive'])) {
            return null;
        }

        $result = [
            'id' => (int)$product['id'],
            'sku' => $product['sku'],
            'ean' => $product['ean'],
            'price_brutto' => $product['price_brutto'] !== null ? (float)$product['price_brutto'] : null,
            'price_brutto_promo' => $product['price_brutto_promo'] !== null ? (float)$product['price_brutto_promo'] : null,
            'price_netto' => $product['price_netto'] !== null ? (float)$product['price_netto'] : null,
            'price_netto_promo' => $product['price_netto_promo'] !== null ? (float)$product['price_netto_promo'] : null,
            'quantity' => (int)$product['quantity'],
            'status' => (int)$product['status'],
            'promoted' => (int)$product['promoted'],
            'vat' => (int)$product['vat'],
            'weight' => $product['weight'] !== null ? (float)$product['weight'] : null,
            'stock_0_buy' => (int)($product['stock_0_buy'] ?? 0),
            'custom_label_0' => $product['custom_label_0'],
            'custom_label_1' => $product['custom_label_1'],
            'custom_label_2' => $product['custom_label_2'],
            'custom_label_3' => $product['custom_label_3'],
            'custom_label_4' => $product['custom_label_4'],
            'new_to_date' => $product['new_to_date'],
            'additional_message' => (int)($product['additional_message'] ?? 0),
            'additional_message_required' => (int)($product['additional_message_required'] ?? 0),
            'additional_message_text' => $product['additional_message_text'],
            'set_id' => $product['set_id'] !== null ? (int)$product['set_id'] : null,
            'product_unit_id' => $product['product_unit_id'] !== null ? (int)$product['product_unit_id'] : null,
            'producer_id' => $product['producer_id'] !== null ? (int)$product['producer_id'] : null,
            'producer_name' => $this->resolveProducerName($product['producer_id']),
            'date_add' => $product['date_add'],
            'date_modify' => $product['date_modify'],
        ];

        // Languages
        $langs = $this->db->select('pp_shop_products_langs', '*', ['product_id' => $id]);
        $result['languages'] = [];
        if (is_array($langs)) {
            foreach ($langs as $lang) {
                $result['languages'][$lang['lang_id']] = [
                    'name' => $lang['name'],
                    'short_description' => $lang['short_description'],
                    'description' => $lang['description'],
                    'meta_description' => $lang['meta_description'],
                    'meta_keywords' => $lang['meta_keywords'],
                    'meta_title' => $lang['meta_title'],
                    'seo_link' => $lang['seo_link'],
                    'copy_from' => $lang['copy_from'],
                    'warehouse_message_zero' => $lang['warehouse_message_zero'],
                    'warehouse_message_nonzero' => $lang['warehouse_message_nonzero'],
                    'tab_name_1' => $lang['tab_name_1'],
                    'tab_description_1' => $lang['tab_description_1'],
                    'tab_name_2' => $lang['tab_name_2'],
                    'tab_description_2' => $lang['tab_description_2'],
                    'canonical' => $lang['canonical'],
                    'security_information' => $lang['security_information'] ?? null,
                ];
            }
        }

        // Images
        $images = $this->db->select('pp_shop_products_images', ['id', 'src', 'alt'], [
            'product_id' => $id,
            'ORDER' => ['o' => 'ASC', 'id' => 'ASC'],
        ]);
        $result['images'] = is_array($images) ? $images : [];
        foreach ($result['images'] as &$img) {
            $img['id'] = (int)$img['id'];
        }
        unset($img);

        // Categories
        $categories = $this->db->select('pp_shop_products_categories', 'category_id', ['product_id' => $id]);
        $result['categories'] = [];
        if (is_array($categories)) {
            foreach ($categories as $catId) {
                $result['categories'][] = (int)$catId;
            }
        }

        // Attributes (enriched with names) — batch-loaded
        $attributes = $this->db->select('pp_shop_products_attributes', ['attribute_id', 'value_id'], ['product_id' => $id]);
        $result['attributes'] = [];
        if (is_array($attributes) && !empty($attributes)) {
            $attrIds = [];
            $valueIds = [];
            foreach ($attributes as $attr) {
                $attrIds[] = (int)$attr['attribute_id'];
                $valueIds[] = (int)$attr['value_id'];
            }
            $attrNamesMap = $this->batchLoadAttributeNames($attrIds);
            $valueNamesMap = $this->batchLoadValueNames($valueIds);
            $attrTypesMap = $this->batchLoadAttributeTypes($attrIds);

            foreach ($attributes as $attr) {
                $attrId = (int)$attr['attribute_id'];
                $valId = (int)$attr['value_id'];
                $result['attributes'][] = [
                    'attribute_id' => $attrId,
                    'attribute_type' => isset($attrTypesMap[$attrId]) ? $attrTypesMap[$attrId] : 0,
                    'attribute_names' => isset($attrNamesMap[$attrId]) ? $attrNamesMap[$attrId] : [],
                    'value_id' => $valId,
                    'value_names' => isset($valueNamesMap[$valId]) ? $valueNamesMap[$valId] : [],
                ];
            }
        }

        // Custom fields (Dodatkowe pola)
        $customFields = $this->db->select('pp_shop_products_custom_fields', ['name', 'type', 'is_required'], ['id_product' => $id]);
        $result['custom_fields'] = [];
        if (is_array($customFields)) {
            foreach ($customFields as $cf) {
                $result['custom_fields'][] = [
                    'name'        => $cf['name'],
                    'type'        => !empty($cf['type']) ? $cf['type'] : 'text',
                    'is_required' => $cf['is_required'],
                ];
            }
        }

        // Variants (only for parent products)
        if (empty($product['parent_id'])) {
            $result['variants'] = $this->findVariantsForApi($id);
        }

        return $result;
    }

    /**
     * Pobiera warianty produktu z atrybutami i tłumaczeniami dla REST API.
     *
     * @param int $productId ID produktu nadrzędnego
     * @return array Lista wariantów
     */
    public function findVariantsForApi(int $productId): array
    {
        $stmt = $this->db->query(
            'SELECT id, permutation_hash, sku, ean, price_brutto, price_brutto_promo,
                    price_netto, price_netto_promo, quantity, stock_0_buy, weight, status
             FROM pp_shop_products
             WHERE parent_id = :pid
             ORDER BY id ASC',
            [':pid' => $productId]
        );

        $rows = $stmt ? $stmt->fetchAll(\PDO::FETCH_ASSOC) : [];
        if (!is_array($rows)) {
            return [];
        }

        // Collect all variant IDs, then load attributes in batch
        $variantIds = [];
        foreach ($rows as $row) {
            $variantIds[] = (int)$row['id'];
        }

        // Load all attributes for all variants at once
        $allAttrsRaw = [];
        if (!empty($variantIds)) {
            $allAttrsRaw = $this->db->select('pp_shop_products_attributes', ['product_id', 'attribute_id', 'value_id'], ['product_id' => $variantIds]);
            if (!is_array($allAttrsRaw)) {
                $allAttrsRaw = [];
            }
        }

        // Group by variant and collect unique IDs for batch loading
        $attrsByVariant = [];
        $allAttrIds = [];
        $allValueIds = [];
        foreach ($allAttrsRaw as $a) {
            $pid = (int)$a['product_id'];
            $aId = (int)$a['attribute_id'];
            $vId = (int)$a['value_id'];
            $attrsByVariant[$pid][] = ['attribute_id' => $aId, 'value_id' => $vId];
            $allAttrIds[] = $aId;
            $allValueIds[] = $vId;
        }

        $attrNamesMap = $this->batchLoadAttributeNames($allAttrIds);
        $valueNamesMap = $this->batchLoadValueNames($allValueIds);

        $variants = [];
        foreach ($rows as $row) {
            $variantId = (int)$row['id'];
            $variantAttrs = [];
            if (isset($attrsByVariant[$variantId])) {
                foreach ($attrsByVariant[$variantId] as $a) {
                    $aId = $a['attribute_id'];
                    $vId = $a['value_id'];
                    $variantAttrs[] = [
                        'attribute_id' => $aId,
                        'attribute_names' => isset($attrNamesMap[$aId]) ? $attrNamesMap[$aId] : [],
                        'value_id' => $vId,
                        'value_names' => isset($valueNamesMap[$vId]) ? $valueNamesMap[$vId] : [],
                    ];
                }
            }

            $variants[] = [
                'id' => $variantId,
                'permutation_hash' => $row['permutation_hash'],
                'sku' => $row['sku'],
                'ean' => $row['ean'],
                'price_brutto' => $row['price_brutto'] !== null ? (float)$row['price_brutto'] : null,
                'price_brutto_promo' => $row['price_brutto_promo'] !== null ? (float)$row['price_brutto_promo'] : null,
                'price_netto' => $row['price_netto'] !== null ? (float)$row['price_netto'] : null,
                'price_netto_promo' => $row['price_netto_promo'] !== null ? (float)$row['price_netto_promo'] : null,
                'quantity' => (int)$row['quantity'],
                'stock_0_buy' => (int)($row['stock_0_buy'] ?? 0),
                'weight' => $row['weight'] !== null ? (float)$row['weight'] : null,
                'status' => (int)$row['status'],
                'attributes' => $variantAttrs,
            ];
        }

        return $variants;
    }

    /**
     * Pobiera pojedynczy wariant po ID dla REST API.
     *
     * @param int $variantId ID wariantu
     * @return array|null Dane wariantu lub null
     */
    public function findVariantForApi(int $variantId): ?array
    {
        $row = $this->db->get('pp_shop_products', '*', ['id' => $variantId]);
        if (!$row || empty($row['parent_id'])) {
            return null;
        }

        $attrs = $this->db->select('pp_shop_products_attributes', ['attribute_id', 'value_id'], ['product_id' => $variantId]);
        $variantAttrs = [];
        if (is_array($attrs) && !empty($attrs)) {
            $attrIds = [];
            $valueIds = [];
            foreach ($attrs as $a) {
                $attrIds[] = (int)$a['attribute_id'];
                $valueIds[] = (int)$a['value_id'];
            }
            $attrNamesMap = $this->batchLoadAttributeNames($attrIds);
            $valueNamesMap = $this->batchLoadValueNames($valueIds);

            foreach ($attrs as $a) {
                $aId = (int)$a['attribute_id'];
                $vId = (int)$a['value_id'];
                $variantAttrs[] = [
                    'attribute_id' => $aId,
                    'attribute_names' => isset($attrNamesMap[$aId]) ? $attrNamesMap[$aId] : [],
                    'value_id' => $vId,
                    'value_names' => isset($valueNamesMap[$vId]) ? $valueNamesMap[$vId] : [],
                ];
            }
        }

        return [
            'id' => (int)$row['id'],
            'parent_id' => (int)$row['parent_id'],
            'permutation_hash' => $row['permutation_hash'],
            'sku' => $row['sku'],
            'ean' => $row['ean'],
            'price_brutto' => $row['price_brutto'] !== null ? (float)$row['price_brutto'] : null,
            'price_brutto_promo' => $row['price_brutto_promo'] !== null ? (float)$row['price_brutto_promo'] : null,
            'price_netto' => $row['price_netto'] !== null ? (float)$row['price_netto'] : null,
            'price_netto_promo' => $row['price_netto_promo'] !== null ? (float)$row['price_netto_promo'] : null,
            'quantity' => (int)$row['quantity'],
            'stock_0_buy' => (int)($row['stock_0_buy'] ?? 0),
            'weight' => $row['weight'] !== null ? (float)$row['weight'] : null,
            'status' => (int)$row['status'],
            'attributes' => $variantAttrs,
        ];
    }

    /**
     * Tworzy nowy wariant (kombinację) produktu przez API.
     *
     * @param int $parentId ID produktu nadrzędnego
     * @param array $data Dane wariantu (attributes, sku, ean, price_brutto, etc.)
     * @return array|null ['id' => int, 'permutation_hash' => string] lub null przy błędzie
     */
    public function createVariantForApi(int $parentId, array $data): ?array
    {
        $parent = $this->db->get('pp_shop_products', ['id', 'archive', 'parent_id', 'vat'], ['id' => $parentId]);
        if (!$parent) {
            return null;
        }
        if (!empty($parent['archive'])) {
            return null;
        }
        if (!empty($parent['parent_id'])) {
            return null;
        }

        $attributes = isset($data['attributes']) && is_array($data['attributes']) ? $data['attributes'] : [];
        if (empty($attributes)) {
            return null;
        }

        // Build permutation hash
        ksort($attributes);
        $hashParts = [];
        foreach ($attributes as $attrId => $valueId) {
            $hashParts[] = (int)$attrId . '-' . (int)$valueId;
        }
        $permutationHash = implode('|', $hashParts);

        // Check duplicate
        $existing = $this->db->count('pp_shop_products', [
            'AND' => [
                'parent_id' => $parentId,
                'permutation_hash' => $permutationHash,
            ],
        ]);
        if ($existing > 0) {
            return null;
        }

        $insertData = [
            'parent_id' => $parentId,
            'permutation_hash' => $permutationHash,
            'vat' => $parent['vat'] ?? 0,
            'sku' => isset($data['sku']) ? (string)$data['sku'] : null,
            'ean' => isset($data['ean']) ? (string)$data['ean'] : null,
            'price_brutto' => isset($data['price_brutto']) ? (float)$data['price_brutto'] : null,
            'price_netto' => isset($data['price_netto']) ? (float)$data['price_netto'] : null,
            'quantity' => isset($data['quantity']) ? (int)$data['quantity'] : 0,
            'stock_0_buy' => isset($data['stock_0_buy']) ? (int)$data['stock_0_buy'] : 0,
            'weight' => isset($data['weight']) ? (float)$data['weight'] : null,
            'status' => 1,
        ];

        $this->db->insert('pp_shop_products', $insertData);
        $variantId = (int)$this->db->id();
        if ($variantId <= 0) {
            return null;
        }

        // Insert attribute rows
        foreach ($attributes as $attrId => $valueId) {
            $this->db->insert('pp_shop_products_attributes', [
                'product_id' => $variantId,
                'attribute_id' => (int)$attrId,
                'value_id' => (int)$valueId,
            ]);
        }

        return [
            'id' => $variantId,
            'permutation_hash' => $permutationHash,
        ];
    }

    /**
     * Aktualizuje wariant produktu przez API.
     *
     * @param int $variantId ID wariantu
     * @param array $data Pola do aktualizacji
     * @return bool true jeśli sukces
     */
    public function updateVariantForApi(int $variantId, array $data): bool
    {
        $variant = $this->db->get('pp_shop_products', ['id', 'parent_id'], ['id' => $variantId]);
        if (!$variant || empty($variant['parent_id'])) {
            return false;
        }

        $casts = [
            'sku' => 'string',
            'ean' => 'string',
            'price_brutto' => 'float_or_null',
            'price_netto' => 'float_or_null',
            'price_brutto_promo' => 'float_or_null',
            'price_netto_promo' => 'float_or_null',
            'quantity' => 'int',
            'stock_0_buy' => 'int',
            'weight' => 'float_or_null',
            'status' => 'int',
        ];

        $updateData = [];
        foreach ($casts as $field => $type) {
            if (array_key_exists($field, $data)) {
                $value = $data[$field];
                if ($type === 'string') {
                    $updateData[$field] = ($value !== null) ? (string)$value : '';
                } elseif ($type === 'int') {
                    $updateData[$field] = (int)$value;
                } elseif ($type === 'float_or_null') {
                    $updateData[$field] = ($value !== null && $value !== '') ? (float)$value : null;
                }
            }
        }

        if (empty($updateData)) {
            return true;
        }

        $this->db->update('pp_shop_products', $updateData, ['id' => $variantId]);
        return true;
    }

    /**
     * Usuwa wariant produktu przez API.
     *
     * @param int $variantId ID wariantu
     * @return bool true jeśli sukces
     */
    public function deleteVariantForApi(int $variantId): bool
    {
        $variant = $this->db->get('pp_shop_products', ['id', 'parent_id'], ['id' => $variantId]);
        if (!$variant || empty($variant['parent_id'])) {
            return false;
        }

        $this->db->delete('pp_shop_products_langs', ['product_id' => $variantId]);
        $this->db->delete('pp_shop_products_attributes', ['product_id' => $variantId]);
        $this->db->delete('pp_shop_products', ['id' => $variantId]);
        return true;
    }

    /**
     * Batch-loads attribute names for multiple attribute IDs.
     *
     * @param int[] $attrIds
     * @return array<int, array<string, string>> [attrId => [langId => name]]
     */
    private function batchLoadAttributeNames(array $attrIds): array
    {
        if (empty($attrIds)) {
            return [];
        }
        $translations = $this->db->select(
            'pp_shop_attributes_langs',
            ['attribute_id', 'lang_id', 'name'],
            ['attribute_id' => array_values(array_unique($attrIds))]
        );
        $result = [];
        if (is_array($translations)) {
            foreach ($translations as $t) {
                $aId = (int)($t['attribute_id'] ?? 0);
                $langId = (string)($t['lang_id'] ?? '');
                if ($aId > 0 && $langId !== '') {
                    $result[$aId][$langId] = (string)($t['name'] ?? '');
                }
            }
        }
        return $result;
    }

    /**
     * Batch-loads value names for multiple value IDs.
     *
     * @param int[] $valueIds
     * @return array<int, array<string, string>> [valueId => [langId => name]]
     */
    private function batchLoadValueNames(array $valueIds): array
    {
        if (empty($valueIds)) {
            return [];
        }
        $translations = $this->db->select(
            'pp_shop_attributes_values_langs',
            ['value_id', 'lang_id', 'name'],
            ['value_id' => array_values(array_unique($valueIds))]
        );
        $result = [];
        if (is_array($translations)) {
            foreach ($translations as $t) {
                $vId = (int)($t['value_id'] ?? 0);
                $langId = (string)($t['lang_id'] ?? '');
                if ($vId > 0 && $langId !== '') {
                    $result[$vId][$langId] = (string)($t['name'] ?? '');
                }
            }
        }
        return $result;
    }

    /**
     * Batch-loads attribute types for multiple attribute IDs.
     *
     * @param int[] $attrIds
     * @return array<int, int> [attrId => type]
     */
    private function batchLoadAttributeTypes(array $attrIds): array
    {
        if (empty($attrIds)) {
            return [];
        }
        $rows = $this->db->select(
            'pp_shop_attributes',
            ['id', 'type'],
            ['id' => array_values(array_unique($attrIds))]
        );
        $result = [];
        if (is_array($rows)) {
            foreach ($rows as $row) {
                $result[(int)$row['id']] = (int)($row['type'] ?? 0);
            }
        }
        return $result;
    }

    /**
     * Zwraca nazwę producenta po ID (null jeśli brak).
     *
     * @param mixed $producerId
     * @return string|null
     */
    private function resolveProducerName($producerId): ?string
    {
        if (empty($producerId)) {
            return null;
        }
        $name = $this->db->get('pp_shop_producer', 'name', ['id' => (int)$producerId]);
        return ($name !== false && $name !== null) ? (string)$name : null;
    }

    /**
     * Szczegóły produktu (admin) — zastępuje factory product_details().
     */
    public function findForAdmin(int $productId): ?array
    {
        $product = $this->db->get( 'pp_shop_products', '*', [ 'id' => $productId ] );
        if ( !$product ) {
            return null;
        }

        $results = $this->db->select( 'pp_shop_products_langs', '*', [ 'product_id' => $productId ] );
        if ( is_array( $results ) ) {
            foreach ( $results as $row ) {
                $product['languages'][ $row['lang_id'] ] = $row;
            }
        }

        $product['images'] = $this->db->select( 'pp_shop_products_images', '*', [ 'product_id' => $productId, 'ORDER' => [ 'o' => 'ASC', 'id' => 'ASC' ] ] );
        $product['files'] = $this->db->select( 'pp_shop_products_files', '*', [ 'product_id' => $productId ] );
        $product['categories'] = $this->db->select( 'pp_shop_products_categories', 'category_id', [ 'product_id' => $productId ] );
        $product['attributes'] = $this->db->select( 'pp_shop_products_attributes', [ 'attribute_id', 'value_id' ], [ 'product_id' => $productId ] );
        $product['products_related'] = $this->db->select( 'pp_shop_products_related', 'product_related_id', [ 'product_id' => $productId ] );
        $product['custom_fields'] = $this->db->select( 'pp_shop_products_custom_fields', '*', [ 'id_product' => $productId ] );

        return $product;
    }

    /**
     * Prosta lista produktów (id => name) — do selectów w formularzu.
     */
    public function allProductsList(): array
    {
        $results = $this->db->select( 'pp_shop_products', 'id', [ 'parent_id' => null ] );
        $products = [];

        if ( is_array( $results ) ) {
            foreach ( $results as $id ) {
                $products[ $id ] = $this->db->get( 'pp_shop_products_langs', 'name', [
                    'AND' => [ 'product_id' => $id, 'lang_id' => 'pl' ]
                ] );
            }
        }

        return $products;
    }

    /**
     * Tekst z nazwami kategorii produktu (oddzielone " / ").
     */
    public function productCategoriesText(int $productId): string
    {
        $results = $this->db->query(
            'SELECT category_id FROM pp_shop_products_categories WHERE product_id = :pid',
            [ ':pid' => $productId ]
        );
        $rows = $results ? $results->fetchAll( \PDO::FETCH_ASSOC ) : [];

        if ( !is_array( $rows ) || empty( $rows ) ) {
            return '';
        }

        $out = ' - ';
        foreach ( $rows as $i => $row ) {
            $title = $this->db->get( 'pp_shop_categories_langs', 'title', [
                'AND' => [
                    'category_id' => (int) $row['category_id'],
                    'lang_id' => $this->defaultLangId(),
                ]
            ] );
            $out .= $title ?: '';
            if ( $i < count( $rows ) - 1 ) {
                $out .= ' / ';
            }
        }

        return $out;
    }

    /**
     * ID produktu nadrzędnego (parent_id).
     */
    public function getParentId(int $productId): ?int
    {
        $parentId = $this->db->get( 'pp_shop_products', 'parent_id', [ 'id' => $productId ] );
        return $parentId ? (int) $parentId : null;
    }

    /**
     * Domyślna nazwa produktu (w domyślnym języku).
     */
    public function productDefaultName(int $productId): ?string
    {
        $name = $this->db->get( 'pp_shop_products_langs', 'name', [
            'AND' => [ 'product_id' => $productId, 'lang_id' => $this->defaultLangId() ]
        ] );
        return $name ?: null;
    }

    /**
     * ID domyślnego języka.
     */
    private function defaultLangId(): string
    {
        $langId = $this->db->get( 'pp_langs', 'id', [ 'start' => 1 ] );
        return $langId ?: 'pl';
    }

    // ─── Krok 2: zapis produktu ──────────────────────────────────────

    /**
     * Zapis produktu (insert lub update). Zastępuje factory save().
     *
     * @param array $d Dane produktu (z formularza)
     * @param int|null $userId ID aktualnego użytkownika admin
     * @return int|null ID produktu lub null przy błędzie
     */
    public function saveProduct(array $d, ?int $userId = null): ?int
    {
        $productId = (int) ( $d['id'] ?? 0 );
        $isNew = !$productId;

        $productData = [
            'date_modify' => date( 'Y-m-d H:i:s' ),
            'modify_by' => $userId !== null ? (int) $userId : 0,
            'status' => ( $d['status'] ?? '' ) === 'on' ? 1 : 0,
            'price_netto' => $this->nullIfEmpty( $d['price_netto'] ?? null ),
            'price_brutto' => $this->nullIfEmpty( $d['price_brutto'] ?? null ),
            'vat' => $d['vat'] ?? 0,
            'promoted' => ( $d['promoted'] ?? '' ) === 'on' ? 1 : 0,
            'layout_id' => $this->nullIfEmpty( $d['layout_id'] ?? null ),
            'price_netto_promo' => $this->nullIfEmpty( $d['price_netto_promo'] ?? null ),
            'price_brutto_promo' => $this->nullIfEmpty( $d['price_brutto_promo'] ?? null ),
            'new_to_date' => $this->nullIfEmpty( $d['new_to_date'] ?? null ),
            'stock_0_buy' => ( $d['stock_0_buy'] ?? '' ) === 'on' ? 1 : 0,
            'wp' => $this->nullIfEmpty( $d['wp'] ?? null ),
            'sku' => $this->nullIfEmpty( $d['sku'] ?? null ),
            'ean' => $this->nullIfEmpty( $d['ean'] ?? null ),
            'custom_label_0' => $this->nullIfEmpty( $d['custom_label_0'] ?? null ),
            'custom_label_1' => $this->nullIfEmpty( $d['custom_label_1'] ?? null ),
            'custom_label_2' => $this->nullIfEmpty( $d['custom_label_2'] ?? null ),
            'custom_label_3' => $this->nullIfEmpty( $d['custom_label_3'] ?? null ),
            'custom_label_4' => $this->nullIfEmpty( $d['custom_label_4'] ?? null ),
            'additional_message' => ( $d['additional_message'] ?? '' ) == 'on' ? 1 : 0,
            'set_id' => !empty( $d['set'] ) ? (int) $d['set'] : null,
            'quantity' => (int) ( $d['quantity'] ?? 0 ),
            'additional_message_text' => $this->nullIfEmpty( $d['additional_message_text'] ?? null ),
            'additional_message_required' => ( $d['additional_message_required'] ?? '' ) == 'on' ? 1 : 0,
            'producer_id' => !empty( $d['producer_id'] ) ? $d['producer_id'] : null,
            'product_unit_id' => !empty( $d['product_unit'] ) ? $d['product_unit'] : null,
            'weight' => !empty( $d['weight'] ) ? $d['weight'] : null,
        ];

        if ( $isNew ) {
            $productData['date_add'] = date( 'Y-m-d H:i:s' );
            $this->db->insert( 'pp_shop_products', $productData );
            $productId = (int) $this->db->id();

            if ( !$productId ) {
                return null;
            }
        } else {
            $this->db->update( 'pp_shop_products', $productData, [ 'id' => $productId ] );

            $this->db->update( 'pp_shop_products', [
                'additional_message' => $productData['additional_message'],
            ], [ 'parent_id' => $productId ] );

            $this->updateCombinationPricesFromBase(
                $productId,
                $d['price_brutto'] ?? 0,
                $d['vat'] ?? 0,
                $d['price_brutto_promo'] ?? null
            );
        }

        $this->saveLanguages( $productId, $d, $isNew );
        $this->saveCategories( $productId, $d['categories'] ?? null );
        $this->saveRelatedProducts( $productId, $d['products_related'] ?? null );
        $this->moveTemporaryFiles( $productId );
        $this->moveTemporaryImages( $productId );

        if ( !empty( $d['gallery_order'] ) ) {
            $this->saveImagesOrder( $productId, $d['gallery_order'] );
        }

        // Zapisz custom fields tylko gdy jawnie podane (partial update przez API może nie zawierać tego klucza)
        if ( array_key_exists( 'custom_field_name', $d ) ) {
            $this->saveCustomFields( $productId, $d['custom_field_name'] ?? [], $d['custom_field_type'] ?? [], $d['custom_field_required'] ?? [] );
        }

        if ( !$isNew ) {
            $this->cleanupDeletedFiles( $productId );
            $this->cleanupDeletedImages( $productId );
        }

        \Shared\Helpers\Helpers::htacces();
        \Shared\Helpers\Helpers::delete_dir( '../temp/' );
        \Shared\Helpers\Helpers::delete_dir( '../thumbs/' );

        if ( !$isNew ) {
            $redis = \Shared\Cache\RedisConnection::getInstance()->getConnection();
            if ( $redis ) {
                $redis->flushAll();
            }
        }

        return $productId;
    }

    private function saveLanguages(int $productId, array $d, bool $isNew): void
    {
        $langs = ( new \Domain\Languages\LanguagesRepository( $this->db ) )->languagesList( true );

        foreach ( $langs as $lg ) {
            $lid = $lg['id'];
            $langData = [
                'name' => $this->nullIfEmpty( $d['name'][$lid] ?? null ),
                'short_description' => $this->nullIfEmpty( $d['short_description'][$lid] ?? null ),
                'description' => $this->nullIfEmpty( $d['description'][$lid] ?? null ),
                'meta_description' => $this->nullIfEmpty( $d['meta_description'][$lid] ?? null ),
                'meta_keywords' => $this->nullIfEmpty( $d['meta_keywords'][$lid] ?? null ),
                'seo_link' => !empty( $d['seo_link'][$lid] ) ? \Shared\Helpers\Helpers::seo( $d['seo_link'][$lid] ) : null,
                'copy_from' => $this->nullIfEmpty( $d['copy_from'][$lid] ?? null ),
                'warehouse_message_zero' => $this->nullIfEmpty( $d['warehouse_message_zero'][$lid] ?? null ),
                'warehouse_message_nonzero' => $this->nullIfEmpty( $d['warehouse_message_nonzero'][$lid] ?? null ),
                'tab_name_1' => $this->nullIfEmpty( $d['tab_name_1'][$lid] ?? null ),
                'tab_description_1' => $this->nullIfEmpty( $d['tab_description_1'][$lid] ?? null ),
                'tab_name_2' => $this->nullIfEmpty( $d['tab_name_2'][$lid] ?? null ),
                'tab_description_2' => $this->nullIfEmpty( $d['tab_description_2'][$lid] ?? null ),
                'canonical' => $this->nullIfEmpty( $d['canonical'][$lid] ?? null ),
                'meta_title' => $this->nullIfEmpty( $d['meta_title'][$lid] ?? null ),
                'security_information' => $this->nullIfEmpty( $d['security_information'][$lid] ?? null ),
            ];

            if ( $isNew ) {
                $langData['product_id'] = $productId;
                $langData['lang_id'] = $lid;
                $this->db->insert( 'pp_shop_products_langs', $langData );
            } else {
                $translationId = $this->db->get( 'pp_shop_products_langs', 'id', [
                    'AND' => [ 'product_id' => $productId, 'lang_id' => $lid ]
                ] );

                if ( $translationId ) {
                    $currentSeoLink = $this->db->get( 'pp_shop_products_langs', 'seo_link', [ 'id' => $translationId ] );
                    $newSeoLink = $langData['seo_link'] ?: \Shared\Helpers\Helpers::seo( 'p-' . $productId . '-' . ( $d['name'][$lid] ?? '' ) );

                    if ( $newSeoLink !== $currentSeoLink && $currentSeoLink != '' ) {
                        $this->handleSeoRedirects( $productId, $lid, $newSeoLink, $currentSeoLink );
                    }

                    $this->db->update( 'pp_shop_products_langs', $langData, [ 'id' => $translationId ] );
                } else {
                    $langData['product_id'] = $productId;
                    $langData['lang_id'] = $lid;
                    $this->db->insert( 'pp_shop_products_langs', $langData );
                }
            }
        }
    }

    private function handleSeoRedirects(int $productId, string $langId, string $newSeoLink, string $currentSeoLink): void
    {
        if ( $this->db->count( 'pp_redirects', [ 'from' => $newSeoLink, 'to' => $currentSeoLink, 'lang_id' => $langId, 'product_id' => $productId ] ) ) {
            $this->db->delete( 'pp_redirects', [ 'from' => $newSeoLink, 'to' => $currentSeoLink, 'lang_id' => $langId, 'product_id' => $productId ] );
        }

        $this->db->delete( 'pp_redirects', [
            'AND' => [
                'product_id' => $productId,
                'lang_id' => $langId,
                'from' => $currentSeoLink,
                'to[!]' => $newSeoLink,
            ],
        ] );

        $seoUsed = (bool) $this->db->count( 'pp_shop_products_langs', [
            'AND' => [
                'lang_id' => $langId,
                'seo_link' => $currentSeoLink,
                'product_id[!]' => $productId,
            ],
        ] );

        if ( !$seoUsed ) {
            $this->db->delete( 'pp_redirects', [
                'AND' => [
                    'from' => $currentSeoLink,
                    'lang_id' => $langId,
                    'product_id[!]' => $productId,
                ],
            ] );

            if ( !$this->db->count( 'pp_redirects', [ 'from' => $currentSeoLink, 'to' => $newSeoLink, 'lang_id' => $langId, 'product_id' => $productId ] ) ) {
                if ( \Shared\Helpers\Helpers::canAddRedirect( $currentSeoLink, $newSeoLink, $langId ) ) {
                    $this->db->insert( 'pp_redirects', [ 'from' => $currentSeoLink, 'to' => $newSeoLink, 'lang_id' => $langId, 'product_id' => $productId ] );
                }
            }
        } else {
            $this->db->delete( 'pp_redirects', [ 'AND' => [ 'product_id' => $productId, 'lang_id' => $langId, 'from' => $currentSeoLink ] ] );
        }
    }

    private function saveCategories(int $productId, $categories): void
    {
        if ( !is_array( $categories ) ) {
            $categories = $categories ? [ $categories ] : [];
        }

        $notIn = array_merge( [0], $categories );
        $this->db->delete( 'pp_shop_products_categories', [ 'AND' => [ 'product_id' => $productId, 'category_id[!]' => $notIn ] ] );

        $existing = $this->db->select( 'pp_shop_products_categories', 'category_id', [ 'product_id' => $productId ] );
        $existing = is_array( $existing ) ? $existing : [];
        $toAdd = array_diff( $categories, $existing );

        foreach ( $toAdd as $categoryId ) {
            if ( $productId && $categoryId ) {
                $order = (int) $this->db->max( 'pp_shop_products_categories', 'o' ) + 1;
                $this->db->insert( 'pp_shop_products_categories', [
                    'product_id' => $productId,
                    'category_id' => (int) $categoryId,
                    'o' => $order,
                ] );
            }
        }
    }

    private function saveRelatedProducts(int $productId, $products): void
    {
        if ( !is_array( $products ) ) {
            $products = $products ? [ $products ] : [];
        }

        $notIn = array_merge( [0], $products );
        $this->db->delete( 'pp_shop_products_related', [ 'AND' => [ 'product_id' => $productId, 'product_related_id[!]' => $notIn ] ] );

        $existing = $this->db->select( 'pp_shop_products_related', 'product_related_id', [ 'product_id' => $productId ] );
        $existing = is_array( $existing ) ? $existing : [];
        $toAdd = array_diff( $products, $existing );

        foreach ( $toAdd as $relatedId ) {
            if ( $productId && $relatedId ) {
                $this->db->insert( 'pp_shop_products_related', [
                    'product_id' => $productId,
                    'product_related_id' => (int) $relatedId,
                ] );
            }
        }
    }

    private function moveTemporaryFiles(int $productId): void
    {
        $results = $this->db->select( 'pp_shop_products_files', '*', [ 'product_id' => null ] );
        if ( !is_array( $results ) ) return;

        $created = false;
        foreach ( $results as $row ) {
            $dir = '/upload/product_files/product_' . $productId;
            $newName = str_replace( '/upload/product_files/tmp', $dir, $row['src'] );

            if ( file_exists( '..' . $row['src'] ) ) {
                if ( !is_dir( '../' . $dir ) && !$created ) {
                    if ( mkdir( '../' . $dir, 0755, true ) ) $created = true;
                }
                rename( '..' . $row['src'], '..' . $newName );
            }

            $this->db->update( 'pp_shop_products_files', [ 'src' => $newName, 'product_id' => $productId ], [ 'id' => $row['id'] ] );
        }
    }

    private function moveTemporaryImages(int $productId): void
    {
        $results = $this->db->select( 'pp_shop_products_images', '*', [ 'product_id' => null ] );
        if ( !is_array( $results ) ) return;

        $created = false;
        foreach ( $results as $row ) {
            $dir = '/upload/product_images/product_' . $productId;
            $newName = str_replace( '/upload/product_images/tmp', $dir, $row['src'] );

            if ( file_exists( '..' . $newName ) ) {
                $ext = strrpos( $newName, '.' );
                $base = substr( $newName, 0, $ext );
                $extension = substr( $newName, $ext );
                $count = 1;
                while ( file_exists( '..' . $base . '_' . $count . $extension ) ) {
                    ++$count;
                }
                $newName = $base . '_' . $count . $extension;
            }

            if ( file_exists( '..' . $row['src'] ) ) {
                if ( !is_dir( '../' . $dir ) && !$created ) {
                    if ( mkdir( '../' . $dir, 0755, true ) ) $created = true;
                }
                rename( '..' . $row['src'], '..' . $newName );
            }

            $this->db->update( 'pp_shop_products_images', [ 'src' => $newName, 'product_id' => $productId ], [ 'id' => $row['id'] ] );
        }
    }

    private function saveCustomFields(int $productId, array $names, array $types, array $required): void
    {
        $existingIds = [];
        foreach ( $names as $i => $name ) {
            if ( !empty( $name ) ) {
                $id = (int) $this->db->get( 'pp_shop_products_custom_fields', 'id_additional_field', [
                    'AND' => [ 'id_product' => $productId, 'name' => $name ]
                ] );
                if ( $id ) $existingIds[] = $id;
            }
        }

        if ( !empty( $existingIds ) ) {
            $this->db->delete( 'pp_shop_products_custom_fields', [
                'AND' => [ 'id_product' => $productId, 'id_additional_field[!]' => $existingIds ]
            ] );
        } else {
            $this->db->delete( 'pp_shop_products_custom_fields', [ 'id_product' => $productId ] );
        }

        foreach ( $names as $i => $name ) {
            if ( empty( $name ) ) continue;

            $typeVal = $types[$i] ?? 'text';
            $isRequired = !empty( $required[$i] ) ? 1 : 0;

            if ( !$this->db->count( 'pp_shop_products_custom_fields', [ 'AND' => [ 'id_product' => $productId, 'name' => $name ] ] ) ) {
                $this->db->insert( 'pp_shop_products_custom_fields', [
                    'id_product' => $productId,
                    'name' => $name,
                    'type' => $typeVal,
                    'is_required' => $isRequired,
                ] );
            } else {
                $this->db->update( 'pp_shop_products_custom_fields', [
                    'type' => $typeVal,
                    'is_required' => $isRequired,
                ], [ 'AND' => [ 'id_product' => $productId, 'name' => $name ] ] );
            }
        }
    }

    private function cleanupDeletedFiles(int $productId): void
    {
        $results = $this->db->select( 'pp_shop_products_files', '*', [ 'AND' => [ 'product_id' => $productId, 'to_delete' => 1 ] ] );
        if ( is_array( $results ) ) {
            foreach ( $results as $row ) {
                $this->safeUnlink( $row['src'] );
            }
        }
        $this->db->delete( 'pp_shop_products_files', [ 'AND' => [ 'product_id' => $productId, 'to_delete' => 1 ] ] );
    }

    private function cleanupDeletedImages(int $productId): void
    {
        $results = $this->db->select( 'pp_shop_products_images', '*', [ 'AND' => [ 'product_id' => $productId, 'to_delete' => 1 ] ] );
        if ( is_array( $results ) ) {
            foreach ( $results as $row ) {
                $this->safeUnlink( $row['src'] );
            }
        }
        $this->db->delete( 'pp_shop_products_images', [ 'AND' => [ 'product_id' => $productId, 'to_delete' => 1 ] ] );
    }

    private function nullIfEmpty($value)
    {
        return ( $value !== null && $value !== '' && $value !== 0.00 ) ? $value : null;
    }

    // ─── Koniec kroku 2 ──────────────────────────────────────────────

    // ─── Krok 3: Operacje na produktach ──────────────────────────────

    /**
     * Usuwa produkt i wszystkie powiązane dane.
     *
     * @param int $productId ID produktu
     * @return bool
     */
    public function delete(int $productId): bool
    {
        // Usuń kombinacje (produkty z parent_id = productId)
        $combinations = $this->db->select( 'pp_shop_products', 'id', [ 'parent_id' => $productId ] );
        if ( $combinations ) {
            foreach ( $combinations as $combId ) {
                $this->db->delete( 'pp_shop_products_attributes', [ 'product_id' => $combId ] );
                $this->db->delete( 'pp_shop_products', [ 'id' => $combId ] );
            }
        }

        $this->db->delete( 'pp_shop_products_categories', [ 'product_id' => $productId ] );
        $this->db->delete( 'pp_shop_products_langs', [ 'product_id' => $productId ] );
        $this->db->delete( 'pp_shop_products_images', [ 'product_id' => $productId ] );
        $this->db->delete( 'pp_shop_products_files', [ 'product_id' => $productId ] );
        $this->db->delete( 'pp_shop_products_custom_fields', [ 'id_product' => $productId ] );
        $this->db->delete( 'pp_shop_products_attributes', [ 'product_id' => $productId ] );
        $this->db->delete( 'pp_shop_products', [ 'id' => $productId ] );
        $this->db->delete( 'pp_shop_product_sets_products', [ 'product_id' => $productId ] );
        $this->db->delete( 'pp_routes', [ 'product_id' => $productId ] );
        $this->db->delete( 'pp_redirects', [ 'product_id' => $productId ] );

        \Shared\Helpers\Helpers::delete_dir( '../upload/product_images/product_' . $productId . '/' );
        \Shared\Helpers\Helpers::delete_dir( '../upload/product_files/product_' . $productId . '/' );

        return true;
    }

    /**
     * Duplikuje produkt z opcjonalnym kopiowaniem kombinacji.
     *
     * @param int $productId ID produktu do zduplikowania
     * @param bool $withCombinations Czy kopiować kombinacje
     * @return bool
     */
    public function duplicate(int $productId, bool $withCombinations = false): bool
    {
        $product = $this->db->get( 'pp_shop_products', '*', [ 'id' => $productId ] );
        if ( !$product ) {
            return false;
        }

        $this->db->insert( 'pp_shop_products', [
            'price_netto'          => $product['price_netto'],
            'price_brutto'         => $product['price_brutto'],
            'price_netto_promo'    => $product['price_netto_promo'],
            'price_brutto_promo'   => $product['price_brutto_promo'],
            'vat'                  => $product['vat'],
            'promoted'             => $product['promoted'],
            'layout_id'            => $product['layout_id'],
            'new_to_date'          => $product['new_to_date'],
            'stock_0_buy'          => $product['stock_0_buy'],
            'wp'                   => $product['wp'],
            'custom_label_0'       => $product['custom_label_0'],
            'custom_label_1'       => $product['custom_label_1'],
            'custom_label_2'       => $product['custom_label_2'],
            'custom_label_3'       => $product['custom_label_3'],
            'custom_label_4'       => $product['custom_label_4'],
            'additional_message'   => $product['additional_message'],
        ] );

        $newProductId = $this->db->id();
        if ( !$newProductId ) {
            return false;
        }

        // Atrybuty
        $attributes = $this->db->select( 'pp_shop_products_attributes', '*', [ 'product_id' => $productId ] );
        if ( \Shared\Helpers\Helpers::is_array_fix( $attributes ) ) {
            foreach ( $attributes as $row ) {
                $this->db->insert( 'pp_shop_products_attributes', [
                    'product_id'   => $newProductId,
                    'attribute_id' => $row['attribute_id'],
                    'value_id'     => $row['value_id'],
                ] );
            }
        }

        // Kategorie
        $categories = $this->db->select( 'pp_shop_products_categories', '*', [ 'product_id' => $productId ] );
        if ( \Shared\Helpers\Helpers::is_array_fix( $categories ) ) {
            foreach ( $categories as $row ) {
                $this->db->insert( 'pp_shop_products_categories', [
                    'product_id'  => $newProductId,
                    'category_id' => $row['category_id'],
                    'o'           => $row['o'],
                ] );
            }
        }

        // Języki
        $langs = $this->db->select( 'pp_shop_products_langs', '*', [ 'product_id' => $productId ] );
        if ( \Shared\Helpers\Helpers::is_array_fix( $langs ) ) {
            foreach ( $langs as $row ) {
                $this->db->insert( 'pp_shop_products_langs', [
                    'product_id'               => $newProductId,
                    'lang_id'                  => $row['lang_id'],
                    'name'                     => $row['name'] . ' - kopia',
                    'short_description'        => $row['short_description'],
                    'description'              => $row['description'],
                    'tab_name_1'               => $row['tab_name_1'],
                    'tab_description_1'        => $row['tab_description_1'],
                    'tab_name_2'               => $row['tab_name_2'],
                    'tab_description_2'        => $row['tab_description_2'],
                    'meta_description'         => $row['meta_description'],
                    'meta_keywords'            => $row['meta_keywords'],
                    'copy_from'                => $row['copy_from'],
                    'warehouse_message_zero'   => $row['warehouse_message_zero'],
                    'warehouse_message_nonzero' => $row['warehouse_message_nonzero'],
                ] );
            }
        }

        // Custom fields
        $customFields = $this->db->select( 'pp_shop_products_custom_fields', '*', [ 'id_product' => $productId ] );
        if ( \Shared\Helpers\Helpers::is_array_fix( $customFields ) ) {
            foreach ( $customFields as $row ) {
                $this->db->insert( 'pp_shop_products_custom_fields', [
                    'id_product'  => $newProductId,
                    'name'        => $row['name'],
                    'type'        => $row['type'] ?? 'text',
                    'is_required' => $row['is_required'] ?? 0,
                ] );
            }
        }

        // Duplikowanie kombinacji
        if ( $withCombinations ) {
            $productCombinations = $this->db->select( 'pp_shop_products', '*', [ 'parent_id' => $productId ] );
            if ( \Shared\Helpers\Helpers::is_array_fix( $productCombinations ) ) {
                foreach ( $productCombinations as $comb ) {
                    $this->db->insert( 'pp_shop_products', [
                        'parent_id'                    => $newProductId,
                        'permutation_hash'             => $comb['permutation_hash'],
                        'price_netto'                  => $comb['price_netto'],
                        'price_brutto'                 => $comb['price_brutto'],
                        'price_netto_promo'            => $comb['price_netto_promo'],
                        'price_brutto_promo'           => $comb['price_brutto_promo'],
                        'vat'                          => $comb['vat'],
                        'stock_0_buy'                  => $comb['stock_0_buy'],
                        'quantity'                     => $comb['quantity'],
                        'wp'                           => $comb['wp'],
                        'additional_message'           => $comb['additional_message'],
                        'additional_message_text'      => $comb['additional_message_text'],
                        'additional_message_required'  => $comb['additional_message_required'],
                    ] );

                    $combNewId = $this->db->id();
                    if ( $combNewId ) {
                        $combAttrs = $this->db->select( 'pp_shop_products_attributes', '*', [ 'product_id' => $comb['id'] ] );
                        if ( \Shared\Helpers\Helpers::is_array_fix( $combAttrs ) ) {
                            foreach ( $combAttrs as $attr ) {
                                $this->db->insert( 'pp_shop_products_attributes', [
                                    'product_id'   => $combNewId,
                                    'attribute_id' => $attr['attribute_id'],
                                    'value_id'     => $attr['value_id'],
                                ] );
                            }
                        }
                    }
                }
            }
        }

        return true;
    }

    /**
     * Przełącza status produktu (aktywny/nieaktywny).
     *
     * @param int $productId ID produktu
     * @return bool
     */
    public function toggleStatus(int $productId): bool
    {
        $status = $this->db->get( 'pp_shop_products', 'status', [ 'id' => $productId ] );
        $newStatus = $status == 1 ? 0 : 1;
        $this->db->update( 'pp_shop_products', [ 'status' => $newStatus ], [ 'id' => $productId ] );
        return true;
    }

    /**
     * Szybka zmiana ceny brutto produktu.
     *
     * @param int $productId ID produktu
     * @param mixed $price Nowa cena brutto
     * @return bool
     */
    public function updatePriceBrutto(int $productId, $price): bool
    {
        $vat = (float) $this->db->get( 'pp_shop_products', 'vat', [ 'id' => $productId ] );
        $priceNetto = \Shared\Helpers\Helpers::normalize_decimal( (float) $price / ( 100 + $vat ) * 100, 2 );

        $this->db->update( 'pp_shop_products', [
            'price_brutto' => $price != 0.00 ? $price : null,
            'price_netto'  => $priceNetto != 0.00 ? $priceNetto : null,
        ], [ 'id' => $productId ] );

        return true;
    }

    /**
     * Szybka zmiana ceny promocyjnej brutto produktu.
     *
     * @param int $productId ID produktu
     * @param mixed $price Nowa cena promo brutto
     * @return bool
     */
    public function updatePriceBruttoPromo(int $productId, $price): bool
    {
        $vat = (float) $this->db->get( 'pp_shop_products', 'vat', [ 'id' => $productId ] );
        $priceNetto = \Shared\Helpers\Helpers::normalize_decimal( (float) $price / ( 100 + $vat ) * 100, 2 );

        $this->db->update( 'pp_shop_products', [
            'price_brutto_promo' => $price != 0.00 ? $price : null,
            'price_netto_promo'  => $priceNetto != 0.00 ? $priceNetto : null,
        ], [ 'id' => $productId ] );

        return true;
    }

    /**
     * Szybka zmiana custom label produktu.
     *
     * @param int $productId ID produktu
     * @param string $label Numer labela (0-4)
     * @param mixed $value Wartość
     * @return bool
     */
    public function updateCustomLabel(int $productId, string $label, $value): bool
    {
        $allowed = ['0', '1', '2', '3', '4'];
        if (!in_array($label, $allowed, true)) {
            return false;
        }

        $this->db->update( 'pp_shop_products', [
            'custom_label_' . $label => $value ? $value : null,
        ], [ 'id' => $productId ] );

        return true;
    }

    // ─── Koniec kroku 3 ──────────────────────────────────────────────

    // ─── Krok 4: Kombinacje ──────────────────────────────────────────

    /**
     * Pobiera kombinacje produktu — lekka wersja do tabeli admin.
     * Jedno zapytanie SQL zamiast N×7 (findForAdmin per wiersz).
     *
     * @param int $productId ID produktu nadrzędnego
     * @return array<int, array{id: int, permutation_hash: string, sku: ?string, quantity: ?int, price_netto: ?float, stock_0_buy: int, parent_id: int}>
     */
    public function getCombinationsForTable(int $productId): array
    {
        $stmt = $this->db->query(
            'SELECT id, permutation_hash, sku, quantity, price_netto, stock_0_buy, parent_id
             FROM pp_shop_products
             WHERE parent_id = :pid
             ORDER BY id ASC',
            [ ':pid' => $productId ]
        );

        return $stmt ? $stmt->fetchAll( \PDO::FETCH_ASSOC ) : [];
    }

    /**
     * Pobiera permutacje (kombinacje) produktu z pełnymi danymi.
     *
     * @param int $productId ID produktu nadrzędnego
     * @return array
     */
    public function getPermutations(int $productId): array
    {
        $products = [];
        $results = $this->db->select( 'pp_shop_products', 'id', [ 'parent_id' => $productId ] );
        if ( \Shared\Helpers\Helpers::is_array_fix( $results ) ) {
            foreach ( $results as $row ) {
                $detail = $this->findForAdmin( (int) $row );
                if ( $detail ) {
                    $products[] = $detail;
                }
            }
        }
        return $products;
    }

    /**
     * Generuje kombinacje produktu na podstawie atrybutów.
     *
     * @param int $productId ID produktu nadrzędnego
     * @param array $attributes Tablica atrybutów [attribute_id => [value_id, ...], ...]
     * @return bool
     */
    public function generateCombinations(int $productId, array $attributes): bool
    {
        $vat = $this->db->get( 'pp_shop_products', 'vat', [ 'id' => $productId ] );
        $attributeRepository = new \Domain\Attribute\AttributeRepository( $this->db );

        $permutations = self::arrayCartesian( $attributes );
        if ( !\Shared\Helpers\Helpers::is_array_fix( $permutations ) ) {
            return true;
        }

        foreach ( $permutations as $permutation ) {
            $product = null;
            ksort( $permutation );

            $permutationHash = '';

            if ( \Shared\Helpers\Helpers::is_array_fix( $permutation ) ) {
                foreach ( $permutation as $key => $val ) {
                    if ( $permutationHash ) {
                        $permutationHash .= '|';
                    }
                    $permutationHash .= $key . '-' . $val;

                    $valueDetails = $attributeRepository->valueDetails( (int) $val );
                    $impactOnPrice = $valueDetails['impact_on_the_price'];

                    if ( $impactOnPrice > 0 ) {
                        if ( !$product ) {
                            $product = $this->findForAdmin( $productId );
                        }

                        $productPriceBrutto = $product['price_brutto'] + $impactOnPrice;
                        $productPriceNetto = $productPriceBrutto / ( 1 + ( $product['vat'] / 100 ) );

                        if ( $product['price_brutto_promo'] ) {
                            $productPriceBruttoPromo = $product['price_brutto_promo'] + $impactOnPrice;
                            $productPriceNettoPromo = $productPriceBruttoPromo / ( 1 + ( $product['vat'] / 100 ) );
                        } else {
                            $productPriceBruttoPromo = null;
                            $productPriceNettoPromo = null;
                        }
                    }

                    if ( $permutationHash && !$this->db->count( 'pp_shop_products', [ 'AND' => [ 'parent_id' => $productId, 'permutation_hash' => $permutationHash ] ] ) ) {
                        if ( $this->db->insert( 'pp_shop_products', [ 'parent_id' => $productId, 'permutation_hash' => $permutationHash, 'vat' => $vat ] ) ) {
                            $combinationId = $this->db->id();
                            if ( $product ) {
                                $this->db->update( 'pp_shop_products', [
                                    'price_netto'        => $productPriceNetto,
                                    'vat'                => $product['vat'],
                                    'price_brutto'       => $productPriceBrutto,
                                    'price_netto_promo'  => $productPriceNettoPromo ?? null,
                                    'price_brutto_promo' => $productPriceBruttoPromo ?? null,
                                ], [ 'id' => $combinationId ] );
                            }

                            $hashRows = explode( '|', $permutationHash );
                            foreach ( $hashRows as $hashRow ) {
                                $attrRev = explode( '-', $hashRow );
                                $this->db->insert( 'pp_shop_products_attributes', [
                                    'product_id'   => $combinationId,
                                    'attribute_id' => $attrRev[0],
                                    'value_id'     => $attrRev[1],
                                ] );
                            }
                        }
                    }
                }
            }
        }
        return true;
    }

    /**
     * Usuwa kombinację produktu.
     *
     * @param int $combinationId ID kombinacji
     * @return bool
     */
    public function deleteCombination(int $combinationId): bool
    {
        $this->db->delete( 'pp_shop_products', [ 'id' => $combinationId ] );
        $this->db->delete( 'pp_shop_products_attributes', [ 'product_id' => $combinationId ] );
        return true;
    }

    /**
     * Liczy kombinacje produktu.
     *
     * @param int $productId ID produktu nadrzędnego
     * @return int
     */
    public function countCombinations(int $productId): int
    {
        return (int) $this->db->count( 'pp_shop_products', [ 'parent_id' => $productId ] );
    }

    /**
     * Zapisuje stock_0_buy kombinacji.
     */
    public function saveCombinationStock0Buy(int $productId, $value): bool
    {
        $this->db->update( 'pp_shop_products', [ 'stock_0_buy' => $value == 'true' ? 1 : 0 ], [ 'id' => $productId ] );
        return true;
    }

    /**
     * Zapisuje SKU kombinacji.
     */
    public function saveCombinationSku(int $productId, $sku): bool
    {
        $this->db->update( 'pp_shop_products', [ 'sku' => $sku ], [ 'id' => $productId ] );
        return true;
    }

    /**
     * Zapisuje ilość kombinacji.
     */
    public function saveCombinationQuantity(int $productId, $quantity): bool
    {
        $this->db->update( 'pp_shop_products', [
            'quantity' => $quantity == '' ? null : (int) $quantity,
        ], [ 'id' => $productId ] );
        return true;
    }

    /**
     * Zapisuje cenę kombinacji (z przeliczeniem netto -> brutto).
     */
    public function saveCombinationPrice(int $productId, $priceNetto): bool
    {
        $vat = $this->db->get( 'pp_shop_products', 'vat', [ 'id' => $productId ] );
        $priceBrutto = (float) $priceNetto * ( 1 + ( (float) $vat / 100 ) );

        $this->db->update( 'pp_shop_products', [
            'price_netto'  => $priceNetto == '' ? null : (float) $priceNetto,
            'price_brutto' => $priceBrutto == '' ? null : (float) $priceBrutto,
        ], [ 'id' => $productId ] );

        return true;
    }

    // ─── Koniec kroku 4 ──────────────────────────────────────────────

    // ─── Krok 5: Zdjęcia / Pliki / XML ──────────────────────────────

    /**
     * Oznacza zdjęcie do usunięcia.
     */
    public function deleteImage(int $imageId): bool
    {
        $this->db->update( 'pp_shop_products_images', [ 'to_delete' => 1 ], [ 'id' => $imageId ] );
        return true;
    }

    /**
     * Zmienia atrybut alt zdjęcia.
     */
    public function updateImageAlt(int $imageId, string $alt): bool
    {
        $result = $this->db->update( 'pp_shop_products_images', [ 'alt' => $alt ], [ 'id' => $imageId ] );
        \Shared\Helpers\Helpers::delete_cache();
        return (bool) $result;
    }

    /**
     * Zapisuje kolejność zdjęć.
     */
    public function saveImagesOrder(int $productId, string $order): bool
    {
        $orderArr = explode( ';', $order );
        if ( is_array( $orderArr ) && !empty( $orderArr ) ) {
            $i = 0;
            foreach ( $orderArr as $imageId ) {
                $this->db->update( 'pp_shop_products_images', [
                    'o' => $i++,
                ], [
                    'AND' => [
                        'product_id' => $productId,
                        'id'         => $imageId,
                    ],
                ] );
            }
        }
        return true;
    }

    /**
     * Usuwa nieprzypisane zdjęcia (product_id = null).
     */
    public function deleteNonassignedImages(): void
    {
        $results = $this->db->select( 'pp_shop_products_images', '*', [ 'product_id' => null ] );
        if ( is_array( $results ) ) {
            foreach ( $results as $row ) {
                $this->safeUnlink( $row['src'] );
            }
        }
        $this->db->delete( 'pp_shop_products_images', [ 'product_id' => null ] );
    }

    /**
     * Usuwa plik z dysku tylko jeśli ścieżka pozostaje wewnątrz katalogu upload/.
     * Zapobiega path traversal przy danych z bazy.
     */
    private function safeUnlink(string $src): void
    {
        $base = realpath('../upload');
        if (!$base) {
            return;
        }
        $full = realpath('../' . ltrim($src, '/'));
        if ($full && strpos($full, $base . DIRECTORY_SEPARATOR) === 0 && is_file($full)) {
            unlink($full);
        } elseif ($full) {
            error_log( '[shopPRO] safeUnlink: ścieżka poza upload/: ' . $src );
        }
    }

    /**
     * Oznacza plik do usunięcia.
     */
    public function deleteFile(int $fileId): bool
    {
        $this->db->update( 'pp_shop_products_files', [ 'to_delete' => 1 ], [ 'id' => $fileId ] );
        return true;
    }

    /**
     * Zmienia nazwę pliku.
     */
    public function updateFileName(int $fileId, string $name): bool
    {
        $this->db->update( 'pp_shop_products_files', [ 'name' => $name ], [ 'id' => $fileId ] );
        return true;
    }

    /**
     * Usuwa nieprzypisane pliki (product_id = null).
     */
    public function deleteNonassignedFiles(): void
    {
        $results = $this->db->select( 'pp_shop_products_files', '*', [ 'product_id' => null ] );
        if ( is_array( $results ) ) {
            foreach ( $results as $row ) {
                if ( file_exists( '../' . $row['src'] ) ) {
                    unlink( '../' . $row['src'] );
                }
            }
        }
        $this->db->delete( 'pp_shop_products_files', [ 'product_id' => null ] );
    }

    /**
     * Pobiera zdjęcia produktu.
     */
    public function getProductImages(int $productId): array
    {
        return $this->db->select( 'pp_shop_products_images', 'src', [
            'product_id' => $productId,
            'ORDER'      => [ 'o' => 'ASC', 'id' => 'ASC' ],
        ] ) ?: [];
    }

    /**
     * Zapisuje nazwę XML produktu.
     */
    public function saveXmlName(int $productId, string $xmlName, string $langId): bool
    {
        return (bool) $this->db->update( 'pp_shop_products_langs', [
            'xml_name' => $xmlName,
        ], [
            'AND' => [ 'product_id' => $productId, 'lang_id' => $langId ],
        ] );
    }

    /**
     * Pobiera sugestie custom label.
     */
    public function customLabelSuggestions(string $customLabel, string $labelType): array
    {
        $allowed = ['custom_label_0', 'custom_label_1', 'custom_label_2', 'custom_label_3', 'custom_label_4'];
        if (!in_array($labelType, $allowed, true)) {
            return [];
        }

        $output = [];
        $results = $this->db->query(
            'SELECT DISTINCT ' . $labelType . ' AS label FROM pp_shop_products WHERE ' . $labelType . ' LIKE :custom_label LIMIT 10',
            [ ':custom_label' => '%' . $customLabel . '%' ]
        );
        if ( $results ) {
            foreach ( $results->fetchAll( \PDO::FETCH_ASSOC ) as $row ) {
                $output[] = $row;
            }
        }
        return $output;
    }

    /**
     * Zapisuje custom label produktu.
     */
    public function saveCustomLabel(int $productId, string $customLabel, string $labelType): bool
    {
        $allowed = ['custom_label_0', 'custom_label_1', 'custom_label_2', 'custom_label_3', 'custom_label_4'];
        if (!in_array($labelType, $allowed, true)) {
            return false;
        }

        return (bool) $this->db->update( 'pp_shop_products', [ $labelType => $customLabel ], [ 'id' => $productId ] );
    }

    /**
     * Generuje kod EAN.
     */
    public function generateEAN(string $number): string
    {
        $code = '200' . str_pad( $number, 9, '0' );
        $weightflag = true;
        $sum = 0;

        for ( $i = strlen( $code ) - 1; $i >= 0; $i-- ) {
            $sum += (int) $code[$i] * ( $weightflag ? 3 : 1 );
            $weightflag = !$weightflag;
        }

        $code .= ( 10 - ( $sum % 10 ) ) % 10;
        return $code;
    }

    /**
     * Generuje Google Feed XML.
     */
    public function generateGoogleFeedXml(): void
    {
        global $lang_id;

        $settings = ( new \Domain\Settings\SettingsRepository( $this->db ) )->allSettings( true );
        $this->transportRepoForXml = new \Domain\Transport\TransportRepository( $this->db );

        $domainPrefix = 'https';
        $url = preg_replace( '#^(http(s)?://)?w{3}\.#', '$1', $_SERVER['SERVER_NAME'] );

        $doc = new \DOMDocument( '1.0', 'UTF-8' );
        $xmlRoot = $doc->createElement( 'rss' );
        $doc->appendChild( $xmlRoot );
        $xmlRoot->setAttribute( 'version', '2.0' );
        $xmlRoot->setAttributeNS( 'http://www.w3.org/2000/xmlns/', 'xmlns:g', 'http://base.google.com/ns/1.0' );
        $channelNode = $xmlRoot->appendChild( $doc->createElement( 'channel' ) );
        $channelNode->appendChild( $doc->createElement( 'title', $settings['firm_name'] ) );
        $channelNode->appendChild( $doc->createElement( 'link', $domainPrefix . '://' . $url ) );

        $rows = $this->db->select( 'pp_shop_products', 'id', [
            'AND' => [ 'status' => '1', 'archive' => 0, 'parent_id' => null ],
        ] );

        if ( \Shared\Helpers\Helpers::is_array_fix( $rows ) ) {
            foreach ( $rows as $productId ) {
                $product = $this->findCached( $productId, $lang_id );
                if ( !$product ) continue;

                if ( is_array( $product['product_combinations'] ) && count( $product['product_combinations'] ) ) {
                    foreach ( $product['product_combinations'] as $productCombination ) {
                        if ( $productCombination['quantity'] !== null || $productCombination['stock_0_buy'] ) {
                            $this->appendCombinationToXml( $doc, $channelNode, $product, $productCombination, $domainPrefix, $url );
                        }
                    }
                } else {
                    $this->appendProductToXml( $doc, $channelNode, $product, $domainPrefix, $url );
                }
            }
        }

        file_put_contents( '../google-feed.xml', $doc->saveXML() );
    }

    /**
     * Dodaje kombinację produktu do XML feed.
     */
    private function appendCombinationToXml(\DOMDocument $doc, \DOMElement $channelNode, $product, $combination, string $domainPrefix, string $url): void
    {
        $itemNode = $channelNode->appendChild( $doc->createElement( 'item' ) );
        $itemNode->appendChild( $doc->createElement( 'g:id', $combination['id'] ) );
        $itemNode->appendChild( $doc->createElement( 'g:item_group_id', $product['id'] ) );

        for ( $l = 0; $l <= 4; $l++ ) {
            $label = 'custom_label_' . $l;
            if ( isset( $product[$label] ) && $product[$label] ) {
                $itemNode->appendChild( $doc->createElement( 'g:' . $label, $product[$label] ) );
            }
        }

        $title = $product['language']['xml_name'] ?: $product['language']['name'];
        $itemNode->appendChild( $doc->createElement( 'title', str_replace( '&', '&amp;', $title ) . ' - ' . $this->generateSubtitleFromAttributes( $combination['permutation_hash'] ) ) );

        $gtin = $product['ean'] ?: $this->generateEAN( $product['id'] );
        $itemNode->appendChild( $doc->createElement( 'g:gtin', $gtin ) );

        $desc = $product['language']['short_description'] ?: $product['language']['name'];
        $itemNode->appendChild( $doc->createElement( 'g:description', html_entity_decode( strip_tags( $desc ) ) ) );

        if ( $product['language']['seo_link'] ) {
            $link = $domainPrefix . '://' . $url . '/' . \Shared\Helpers\Helpers::seo( $product['language']['seo_link'] ) . '/' . str_replace( '|', '/', $combination['permutation_hash'] );
        } else {
            $link = $domainPrefix . '://' . $url . '/p-' . $product['id'] . '-' . \Shared\Helpers\Helpers::seo( $product['language']['name'] ) . '/' . str_replace( '|', '/', $combination['permutation_hash'] );
        }
        $itemNode->appendChild( $doc->createElement( 'link', $link ) );

        $this->appendImagesToXml( $doc, $itemNode, $product, $domainPrefix, $url );

        $itemNode->appendChild( $doc->createElement( 'g:condition', 'new' ) );

        if ( $combination['quantity'] !== null ) {
            if ( $combination['quantity'] > 0 ) {
                $itemNode->appendChild( $doc->createElement( 'g:availability', 'in stock' ) );
                $itemNode->appendChild( $doc->createElement( 'g:quantity', $combination['quantity'] ) );
            } else {
                $itemNode->appendChild( $doc->createElement( 'g:availability', $combination['stock_0_buy'] ? 'in stock' : 'out of stock' ) );
            }
        } else {
            if ( $product['quantity'] > 0 ) {
                $itemNode->appendChild( $doc->createElement( 'g:availability', 'in stock' ) );
                $itemNode->appendChild( $doc->createElement( 'g:quantity', $product['quantity'] ) );
            } else {
                $itemNode->appendChild( $doc->createElement( 'g:availability', $product['stock_0_buy'] ? 'in stock' : 'out of stock' ) );
                $itemNode->appendChild( $doc->createElement( 'g:quantity', $product['stock_0_buy'] ? 999 : 0 ) );
            }
        }

        if ( $combination['price_brutto'] ) {
            $itemNode->appendChild( $doc->createElement( 'g:price', $combination['price_brutto'] . ' PLN' ) );
            if ( $combination['price_brutto_promo'] ) {
                $itemNode->appendChild( $doc->createElement( 'g:sale_price', $combination['price_brutto_promo'] . ' PLN' ) );
            }
        } else {
            $itemNode->appendChild( $doc->createElement( 'g:price', $product['price_brutto'] . ' PLN' ) );
            if ( $product['price_brutto_promo'] ) {
                $itemNode->appendChild( $doc->createElement( 'g:sale_price', $product['price_brutto_promo'] . ' PLN' ) );
            }
        }

        $this->appendShippingToXml( $doc, $itemNode, $product );
    }

    /**
     * Dodaje produkt (bez kombinacji) do XML feed.
     */
    private function appendProductToXml(\DOMDocument $doc, \DOMElement $channelNode, $product, string $domainPrefix, string $url): void
    {
        $itemNode = $channelNode->appendChild( $doc->createElement( 'item' ) );
        $itemNode->appendChild( $doc->createElement( 'g:id', $product['id'] ) );
        $itemNode->appendChild( $doc->createElement( 'g:item_group_id', $product['id'] ) );

        if ( isset( $product['google_xml_label'] ) && $product['google_xml_label'] ) {
            $itemNode->appendChild( $doc->createElement( 'g:custom_label_0', $product['google_xml_label'] ) );
        }

        $title = $product['language']['xml_name'] ?: $product['language']['name'];
        $itemNode->appendChild( $doc->createElement( 'title', str_replace( '&', '&amp;', $title ) ) );

        $gtin = $product['ean'] ?: $this->generateEAN( $product['id'] );
        $itemNode->appendChild( $doc->createElement( 'g:gtin', $gtin ) );

        $desc = $product['language']['short_description'] ?: $product['language']['name'];
        $itemNode->appendChild( $doc->createElement( 'g:description', html_entity_decode( strip_tags( $desc ) ) ) );

        if ( $product['language']['seo_link'] ) {
            $link = $domainPrefix . '://' . $url . '/' . \Shared\Helpers\Helpers::seo( $product['language']['seo_link'] );
        } else {
            $link = $domainPrefix . '://' . $url . '/p-' . $product['id'] . '-' . \Shared\Helpers\Helpers::seo( $product['language']['name'] );
        }
        $itemNode->appendChild( $doc->createElement( 'link', $link ) );

        for ( $l = 0; $l <= 4; $l++ ) {
            $label = 'custom_label_' . $l;
            if ( isset( $product[$label] ) && $product[$label] ) {
                $itemNode->appendChild( $doc->createElement( 'g:' . $label, $product[$label] ) );
            }
        }

        $this->appendImagesToXml( $doc, $itemNode, $product, $domainPrefix, $url );

        $itemNode->appendChild( $doc->createElement( 'g:condition', 'new' ) );

        if ( $product['quantity'] ) {
            $itemNode->appendChild( $doc->createElement( 'g:availability', 'in stock' ) );
            $itemNode->appendChild( $doc->createElement( 'g:quantity', $product['quantity'] ) );
        } else {
            if ( $product['stock_0_buy'] ) {
                $itemNode->appendChild( $doc->createElement( 'g:availability', 'in stock' ) );
                $itemNode->appendChild( $doc->createElement( 'g:quantity', 999 ) );
            } else {
                $itemNode->appendChild( $doc->createElement( 'g:availability', 'out of stock' ) );
                $itemNode->appendChild( $doc->createElement( 'g:quantity', 0 ) );
            }
        }

        $itemNode->appendChild( $doc->createElement( 'g:price', $product['price_brutto'] . ' PLN' ) );
        if ( $product['price_brutto_promo'] ) {
            $itemNode->appendChild( $doc->createElement( 'g:sale_price', $product['price_brutto_promo'] . ' PLN' ) );
        }

        $this->appendShippingToXml( $doc, $itemNode, $product );
    }

    /**
     * Dodaje zdjęcia do node XML.
     */
    private function appendImagesToXml(\DOMDocument $doc, \DOMElement $itemNode, $product, string $domainPrefix, string $url): void
    {
        if ( isset( $product['images'][0] ) ) {
            $itemNode->appendChild( $doc->createElement( 'g:image_link', $domainPrefix . '://' . $url . $product['images'][0]['src'] ) );
        }
        if ( is_array( $product['images'] ) && count( $product['images'] ) > 1 ) {
            for ( $i = 1; $i < count( $product['images'] ); ++$i ) {
                $itemNode->appendChild( $doc->createElement( 'g:additional_image_link', $domainPrefix . '://' . $url . $product['images'][$i]['src'] ) );
            }
        }
    }

    /**
     * Dodaje sekcję shipping do node XML.
     */
    private function appendShippingToXml(\DOMDocument $doc, \DOMElement $itemNode, $product): void
    {
        $shippingNode = $itemNode->appendChild( $doc->createElement( 'g:shipping' ) );
        $shippingNode->appendChild( $doc->createElement( 'g:country', 'PL' ) );
        $shippingNode->appendChild( $doc->createElement( 'g:service', '1 dzień roboczy' ) );
        $shippingNode->appendChild( $doc->createElement( 'g:price',
            $this->transportRepoForXml->lowestTransportPrice( (int) $product['wp'] ) . ' PLN'
        ) );
    }

    // ─── Koniec kroku 5 ──────────────────────────────────────────────

    /**
     * Aktualizuje ceny kombinacji produktu na podstawie cen brutto bazowych.
     * Wywoływane z saveProduct() i z cron.php.
     *
     * @param int $productId ID produktu nadrzędnego
     * @param float $priceBrutto Cena brutto bazowa
     * @param float $vat Stawka VAT
     * @param float|null $priceBruttoPromo Cena promo brutto bazowa (null = brak)
     */
    public function updateCombinationPricesFromBase(int $productId, $priceBrutto, $vat, $priceBruttoPromo): void
    {
        $priceBrutto = (float) $priceBrutto;
        $vat = (float) $vat;
        $priceBruttoPromo = $priceBruttoPromo ? (float) $priceBruttoPromo : null;

        $combinations = $this->db->query(
            'SELECT psp.id '
            . 'FROM pp_shop_products AS psp '
            . 'INNER JOIN pp_shop_products_attributes AS pspa ON psp.id = pspa.product_id '
            . 'INNER JOIN pp_shop_attributes_values AS psav ON pspa.value_id = psav.id '
            . 'WHERE psav.impact_on_the_price > 0 AND psp.parent_id = :product_id',
            [ ':product_id' => $productId ]
        );

        if ( !$combinations ) {
            return;
        }

        $rows = $combinations->fetchAll( \PDO::FETCH_ASSOC );
        foreach ( $rows as $row ) {
            $combBrutto = $priceBrutto;
            $combBruttoPromo = $priceBruttoPromo;

            $values = $this->db->query(
                'SELECT impact_on_the_price FROM pp_shop_attributes_values AS psav '
                . 'INNER JOIN pp_shop_products_attributes AS pspa ON pspa.value_id = psav.id '
                . 'WHERE impact_on_the_price IS NOT NULL AND product_id = :product_id',
                [ ':product_id' => $row['id'] ]
            );

            if ( $values ) {
                foreach ( $values->fetchAll( \PDO::FETCH_ASSOC ) as $value ) {
                    $combBrutto += $value['impact_on_the_price'];
                    if ( $combBruttoPromo !== null ) {
                        $combBruttoPromo += $value['impact_on_the_price'];
                    } else {
                        $combBruttoPromo = null;
                    }
                }
            }

            $combNetto = \Shared\Helpers\Helpers::normalize_decimal( $combBrutto / ( 100 + $vat ) * 100, 2 );
            $combNettoPromo = $combBruttoPromo !== null
                ? \Shared\Helpers\Helpers::normalize_decimal( $combBruttoPromo / ( 100 + $vat ) * 100, 2 )
                : null;

            $this->db->update( 'pp_shop_products', [
                'price_netto' => $combNetto,
                'price_brutto' => $combBrutto,
                'price_netto_promo' => $combNettoPromo,
                'price_brutto_promo' => $combBruttoPromo
            ], [ 'id' => $row['id'] ] );
        }
    }

    /**
     * Aktualizuje ceny kombinacji produktu uwzględniając wpływ na cenę (impact_on_the_price).
     * Wersja prywatna — przyjmuje ceny netto (dla mass_edit / applyDiscountPercent).
     *
     * @param int $productId ID produktu nadrzędnego
     * @param float $priceNetto Cena netto bazowa
     * @param float $vat Stawka VAT
     * @param float|null $priceNettoPromo Cena promo netto bazowa (null = brak)
     */
    private function updateCombinationPrices(int $productId, float $priceNetto, float $vat, ?float $priceNettoPromo): void
    {
        $priceBrutto = \Shared\Helpers\Helpers::normalize_decimal( $priceNetto * ( 100 + $vat ) / 100, 2 );
        $priceBruttoPromo = $priceNettoPromo !== null
            ? \Shared\Helpers\Helpers::normalize_decimal( $priceNettoPromo * ( 100 + $vat ) / 100, 2 )
            : null;

        $combinations = $this->db->query(
            'SELECT psp.id '
            . 'FROM pp_shop_products AS psp '
            . 'INNER JOIN pp_shop_products_attributes AS pspa ON psp.id = pspa.product_id '
            . 'INNER JOIN pp_shop_attributes_values AS psav ON pspa.value_id = psav.id '
            . 'WHERE psav.impact_on_the_price > 0 AND psp.parent_id = :product_id',
            [ ':product_id' => $productId ]
        );

        if ( !$combinations ) {
            return;
        }

        $rows = $combinations->fetchAll( \PDO::FETCH_ASSOC );
        foreach ( $rows as $row ) {
            $combBrutto = $priceBrutto;
            $combBruttoPromo = $priceBruttoPromo;

            $values = $this->db->query(
                'SELECT impact_on_the_price FROM pp_shop_attributes_values AS psav '
                . 'INNER JOIN pp_shop_products_attributes AS pspa ON pspa.value_id = psav.id '
                . 'WHERE impact_on_the_price IS NOT NULL AND product_id = :product_id',
                [ ':product_id' => $row['id'] ]
            );

            if ( $values ) {
                foreach ( $values->fetchAll( \PDO::FETCH_ASSOC ) as $value ) {
                    $combBrutto += $value['impact_on_the_price'];
                    if ( $combBruttoPromo !== null ) {
                        $combBruttoPromo += $value['impact_on_the_price'];
                    }
                }
            }

            $combNetto = \Shared\Helpers\Helpers::normalize_decimal( $combBrutto / ( 100 + $vat ) * 100, 2 );
            $combNettoPromo = $combBruttoPromo !== null
                ? \Shared\Helpers\Helpers::normalize_decimal( $combBruttoPromo / ( 100 + $vat ) * 100, 2 )
                : null;

            $this->db->update( 'pp_shop_products', [
                'price_netto' => $combNetto,
                'price_brutto' => $combBrutto,
                'price_netto_promo' => $combNettoPromo,
                'price_brutto_promo' => $combBruttoPromo
            ], [ 'id' => $row['id'] ] );
        }
    }

    // =========================================================================
    // Frontend methods (migrated from front\factory\ShopProduct)
    // =========================================================================

    /**
     * @return string|null
     */
    public function getSkuWithFallback(int $productId, bool $withParentFallback = false)
    {
        if ($productId <= 0) {
            return null;
        }

        $sku = $this->db->get('pp_shop_products', 'sku', ['id' => $productId]);

        if (!$sku && $withParentFallback) {
            $parentId = $this->db->get('pp_shop_products', 'parent_id', ['id' => $productId]);
            if ($parentId) {
                return $this->getSkuWithFallback((int)$parentId, true);
            }
            return null;
        }

        return $sku ? (string)$sku : null;
    }

    /**
     * @return string|null
     */
    public function getEanWithFallback(int $productId, bool $withParentFallback = false)
    {
        if ($productId <= 0) {
            return null;
        }

        $ean = $this->db->get('pp_shop_products', 'ean', ['id' => $productId]);

        if (!$ean && $withParentFallback) {
            $parentId = $this->db->get('pp_shop_products', 'parent_id', ['id' => $productId]);
            if ($parentId) {
                return $this->getEanWithFallback((int)$parentId, true);
            }
            return null;
        }

        return $ean ? (string)$ean : null;
    }

    public function isProductActiveCached(int $productId): int
    {
        if ($productId <= 0) {
            return 0;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'is_product_active:' . $productId;
        $cached = $cacheHandler->get($cacheKey);

        if ($cached) {
            return (int)unserialize($cached) === 1 ? 1 : 0;
        }

        $status = $this->db->get('pp_shop_products', 'status', ['id' => $productId]);
        $cacheHandler->set($cacheKey, $status);

        return (int)$status === 1 ? 1 : 0;
    }

    /**
     * @return string|null
     */
    public function getMinimalPriceCached(int $productId, $priceBruttoPromo = null)
    {
        if ($productId <= 0) {
            return null;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'get_minimal_price:' . $productId;
        $cached = $cacheHandler->get($cacheKey);

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

        $price = $this->db->min('pp_shop_product_price_history', 'price', [
            'AND' => [
                'id_product' => $productId,
                'price[!]' => str_replace(',', '.', $priceBruttoPromo),
            ],
        ]);

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

        return $price;
    }

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

        $parentId = $this->db->get('pp_shop_products', 'parent_id', ['id' => $productId]);
        $targetId = $parentId ? (int)$parentId : $productId;

        $stmt = $this->db->query(
            'SELECT category_id FROM pp_shop_products_categories WHERE product_id = :pid',
            [':pid' => $targetId]
        );

        return $stmt ? $stmt->fetchAll(\PDO::FETCH_ASSOC) : [];
    }

    /**
     * @return string|null
     */
    public function getProductNameCached(int $productId, string $langId)
    {
        if ($productId <= 0) {
            return null;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'product_name' . $langId . '_' . $productId;
        $cached = $cacheHandler->get($cacheKey);

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

        $name = $this->db->get('pp_shop_products_langs', 'name', [
            'AND' => ['product_id' => $productId, 'lang_id' => $langId],
        ]);

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

        return $name;
    }

    /**
     * @return string|null
     */
    public function getFirstImageCached(int $productId)
    {
        if ($productId <= 0) {
            return null;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'product_image:' . $productId;
        $cached = $cacheHandler->get($cacheKey);

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

        $stmt = $this->db->query(
            'SELECT src FROM pp_shop_products_images WHERE product_id = :pid ORDER BY o ASC LIMIT 1',
            [':pid' => $productId]
        );
        $rows = $stmt ? $stmt->fetchAll(\PDO::FETCH_ASSOC) : [];
        $image = isset($rows[0]['src']) ? $rows[0]['src'] : null;

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

        return $image;
    }

    public function getWeightCached(int $productId)
    {
        if ($productId <= 0) {
            return null;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'product_wp:' . $productId;
        $cached = $cacheHandler->get($cacheKey);

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

        $wp = $this->db->get('pp_shop_products', 'wp', ['id' => $productId]);
        $cacheHandler->set($cacheKey, $wp);

        return $wp;
    }

    /**
     * @return array<int, int>
     */
    public function promotedProductIdsCached(int $limit = 6): array
    {
        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'promoted_products-' . $limit;
        $cached = $cacheHandler->get($cacheKey);

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

        $stmt = $this->db->query(
            'SELECT id FROM pp_shop_products WHERE status = 1 AND promoted = 1 ORDER BY RAND() LIMIT ' . (int)$limit
        );
        $rows = $stmt ? $stmt->fetchAll() : [];
        $products = [];
        if (is_array($rows)) {
            foreach ($rows as $row) {
                $products[] = (int)$row['id'];
            }
        }

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

        return $products;
    }

    /**
     * @return array<int, int>
     */
    public function topProductIds(int $limit = 6): array
    {
        $date30 = date('Y-m-d', strtotime('-30 days'));

        $stmt = $this->db->query(
            "SELECT COUNT(0) AS sell_count, psop.parent_product_id
             FROM pp_shop_order_products AS psop
             INNER JOIN pp_shop_orders AS pso ON pso.id = psop.order_id
             WHERE pso.date_order >= :d
             GROUP BY parent_product_id
             ORDER BY sell_count DESC",
            [':d' => $date30]
        );
        $rows = $stmt ? $stmt->fetchAll(\PDO::FETCH_ASSOC) : [];

        $ids = [];
        foreach ($rows as $row) {
            if ($this->isProductActiveCached((int)$row['parent_product_id'])) {
                $ids[] = (int)$row['parent_product_id'];
            }
        }

        return $ids;
    }

    /**
     * @return array<int, int>
     */
    public function newProductIds(int $limit = 10): array
    {
        $stmt = $this->db->query(
            'SELECT id FROM pp_shop_products WHERE status = 1 ORDER BY date_add DESC LIMIT ' . (int)$limit
        );
        $rows = $stmt ? $stmt->fetchAll(\PDO::FETCH_ASSOC) : [];

        return array_column($rows, 'id');
    }

    /**
     * @return array|null
     */
    public function productDetailsFrontCached(int $productId, string $langId)
    {
        if ($productId <= 0) {
            return null;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'product_details_front:' . $productId . ':' . $langId;
        $cached = $cacheHandler->get($cacheKey);

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

        $product = $this->db->get('pp_shop_products', '*', ['id' => $productId]);
        if (!is_array($product)) {
            return null;
        }

        // language
        $langRows = $this->db->select('pp_shop_products_langs', '*', [
            'AND' => ['product_id' => $productId, 'lang_id' => $langId],
        ]);
        if (is_array($langRows)) {
            foreach ($langRows as $row) {
                if ($row['copy_from']) {
                    $copyRows = $this->db->select('pp_shop_products_langs', '*', [
                        'AND' => ['product_id' => $productId, 'lang_id' => $row['copy_from']],
                    ]);
                    if (is_array($copyRows)) {
                        foreach ($copyRows as $row2) {
                            $product['language'] = $row2;
                        }
                    }
                } else {
                    $product['language'] = $row;
                }
            }
        }

        // attributes
        $attrStmt = $this->db->query(
            'SELECT DISTINCT(attribute_id) FROM pp_shop_products_attributes AS pspa '
            . 'INNER JOIN pp_shop_attributes AS psa ON psa.id = pspa.attribute_id '
            . 'WHERE product_id = ' . $productId . ' ORDER BY o ASC'
        );
        $attrRows = $attrStmt ? $attrStmt->fetchAll() : [];
        if (is_array($attrRows)) {
            foreach ($attrRows as $row) {
                $row['type'] = $this->db->get('pp_shop_attributes', 'type', ['id' => $row['attribute_id']]);
                $row['language'] = $this->db->get('pp_shop_attributes_langs', ['name'], [
                    'AND' => ['attribute_id' => $row['attribute_id'], 'lang_id' => $langId],
                ]);

                $valStmt = $this->db->query(
                    'SELECT value_id, is_default FROM pp_shop_products_attributes AS pspa '
                    . 'INNER JOIN pp_shop_attributes_values AS psav ON psav.id = pspa.value_id '
                    . 'WHERE product_id = :pid AND pspa.attribute_id = :aid',
                    [':pid' => $productId, ':aid' => $row['attribute_id']]
                );
                $valRows = $valStmt ? $valStmt->fetchAll(\PDO::FETCH_ASSOC) : [];
                if (is_array($valRows)) {
                    foreach ($valRows as $row2) {
                        $row2['language'] = $this->db->get('pp_shop_attributes_values_langs', ['name', 'value'], [
                            'AND' => ['value_id' => $row2['value_id'], 'lang_id' => $langId],
                        ]);
                        $row['values'][] = $row2;
                    }
                }

                $product['attributes'][] = $row;
            }
        }

        $product['images'] = $this->db->select('pp_shop_products_images', '*', [
            'product_id' => $productId,
            'ORDER' => ['o' => 'ASC', 'id' => 'ASC'],
        ]);
        $product['files'] = $this->db->select('pp_shop_products_files', '*', ['product_id' => $productId]);
        $product['categories'] = $this->db->select('pp_shop_products_categories', 'category_id', ['product_id' => $productId]);
        $product['products_related'] = $this->db->select('pp_shop_products_related', 'product_related_id', ['product_id' => $productId]);

        $setId = (int)($product['set_id'] ?? 0);
        $productsSets = $this->db->select('pp_shop_product_sets_products', 'product_id', ['set_id' => $setId]);
        $product['products_sets'] = is_array($productsSets) ? array_unique($productsSets) : [];

        $attributes = $this->db->select('pp_shop_products_attributes', ['attribute_id', 'value_id'], ['product_id' => $productId]);
        $attributesTmp = [];
        if (is_array($attributes)) {
            foreach ($attributes as $attr) {
                $attributesTmp[$attr['attribute_id']][] = $attr['value_id'];
            }
        }
        if (!empty($attributesTmp)) {
            $product['permutations'] = \Shared\Helpers\Helpers::array_cartesian_product($attributesTmp);
        }

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

        return $product;
    }

    /**
     * @return string|null
     */
    public function getWarehouseMessageZero(int $productId, string $langId)
    {
        if ($productId <= 0) {
            return null;
        }

        return $this->db->get('pp_shop_products_langs', 'warehouse_message_zero', [
            'AND' => ['product_id' => $productId, 'lang_id' => $langId],
        ]);
    }

    /**
     * @return string|null
     */
    public function getWarehouseMessageNonzero(int $productId, string $langId)
    {
        if ($productId <= 0) {
            return null;
        }

        return $this->db->get('pp_shop_products_langs', 'warehouse_message_nonzero', [
            'AND' => ['product_id' => $productId, 'lang_id' => $langId],
        ]);
    }

    public function findCustomFieldCached(int $customFieldId)
    {
        if ($customFieldId <= 0) {
            return null;
        }

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = 'custom_field:' . $customFieldId;
        $cached = $cacheHandler->get($cacheKey);

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

        $result = $this->db->get('pp_shop_products_custom_fields', '*', ['id_additional_field' => $customFieldId]);
        if (!is_array($result)) {
            return null;
        }

        $cacheHandler->set($cacheKey, $result, 60 * 60 * 24);

        return $result;
    }

    // =========================================================================
    // Migrated from \shop\Product
    // =========================================================================

    /**
     * Replacement for $this->findCached() — returns array instead of Product object.
     * Uses same Redis cache key pattern: shop\product:{id}:{lang}:{hash}
     */
    public function findCached(int $productId, string $langId = null, string $permutationHash = null)
    {
        if ($productId <= 0) {
            return null;
        }

        if (!$langId) {
            $langId = (new \Domain\Languages\LanguagesRepository($this->db))->defaultLanguage();
        }

        $cacheKey = "shop\\product:$productId:$langId:$permutationHash";

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cached = $cacheHandler->get($cacheKey);

        if ($cached) {
            $data = unserialize($cached);
            // Legacy cache may contain old \shop\Product objects — invalidate and re-fetch
            if (is_object($data)) {
                $cacheHandler->delete($cacheKey);
                // Fall through to re-fetch from DB
            } elseif (is_array($data)) {
                return $data;
            }
        }

        $product = $this->db->get('pp_shop_products', '*', ['id' => $productId]);
        if (!is_array($product)) {
            return null;
        }

        $effectiveId = $productId;

        // Combination — load data from parent
        if ($product['parent_id']) {
            $effectiveId = (int) $product['parent_id'];

            if (!$product['price_netto'] || !$product['price_brutto']) {
                $parentPrices = $this->db->get('pp_shop_products', ['price_netto', 'price_brutto', 'price_netto_promo', 'price_brutto_promo', 'vat', 'wp'], ['id' => $effectiveId]);
                if (is_array($parentPrices)) {
                    foreach ($parentPrices as $k => $v) {
                        $product[$k] = $v;
                    }
                }
            }
        }

        // Language
        $langRows = $this->db->select('pp_shop_products_langs', '*', ['AND' => ['product_id' => $effectiveId, 'lang_id' => $langId]]);
        if (\Shared\Helpers\Helpers::is_array_fix($langRows)) {
            foreach ($langRows as $row) {
                if ($row['copy_from']) {
                    $copyRows = $this->db->select('pp_shop_products_langs', '*', ['AND' => ['product_id' => $effectiveId, 'lang_id' => $row['copy_from']]]);
                    if (is_array($copyRows)) {
                        foreach ($copyRows as $row2) {
                            $product['language'] = $row2;
                        }
                    }
                } else {
                    $product['language'] = $row;
                }
            }
        }

        // Images, files, categories, related, sets
        $product['images'] = $this->db->select('pp_shop_products_images', '*', ['product_id' => $effectiveId, 'ORDER' => ['o' => 'ASC', 'id' => 'ASC']]);
        $product['files'] = $this->db->select('pp_shop_products_files', '*', ['product_id' => $effectiveId]);
        $product['categories'] = $this->db->select('pp_shop_products_categories', 'category_id', ['product_id' => $effectiveId]);
        $product['products_related'] = $this->db->select('pp_shop_products_related', 'product_related_id', ['product_id' => $effectiveId]);
        $product['products_sets'] = $this->db->select('pp_shop_product_sets_products', 'product_id', ['AND' => ['set_id' => (int) ($product['set_id'] ?? 0), 'product_id[!]' => $effectiveId]]);

        // Product combinations (only for main products)
        if (!$product['parent_id']) {
            $combIds = $this->db->select('pp_shop_products', 'id', ['parent_id' => $productId]);
            if (\Shared\Helpers\Helpers::is_array_fix($combIds)) {
                $combos = [];
                foreach ($combIds as $combId) {
                    $combos[] = $this->findCached((int) $combId, $langId);
                }
                $product['product_combinations'] = $combos;
            }
        }

        // Producer
        $producer = $this->db->get('pp_shop_producer', '*', ['id' => (int) ($product['producer_id'] ?? 0)]);
        $producerLang = $this->db->get('pp_shop_producer_lang', '*', ['AND' => ['producer_id' => (int) ($product['producer_id'] ?? 0), 'lang_id' => $langId]]);
        if (is_array($producer)) {
            $producer['description'] = is_array($producerLang) ? ($producerLang['description'] ?? null) : null;
            $producer['data'] = is_array($producerLang) ? ($producerLang['data'] ?? null) : null;
            $producer['meta_title'] = is_array($producerLang) ? ($producerLang['meta_title'] ?? null) : null;
        }
        $product['producer'] = $producer;

        // Permutation price overrides
        if ($permutationHash) {
            $permPrices = $this->db->get('pp_shop_products', ['price_netto', 'price_brutto', 'price_netto_promo', 'price_brutto_promo'], ['AND' => ['permutation_hash' => $permutationHash, 'parent_id' => $productId]]);
            if (is_array($permPrices)) {
                if ($permPrices['price_netto'] !== null) $product['price_netto'] = $permPrices['price_netto'];
                if ($permPrices['price_brutto'] !== null) $product['price_brutto'] = $permPrices['price_brutto'];
                if ($permPrices['price_netto_promo'] !== null) $product['price_netto_promo'] = $permPrices['price_netto_promo'];
                if ($permPrices['price_brutto_promo'] !== null) $product['price_brutto_promo'] = $permPrices['price_brutto_promo'];
            }
        }

        // Custom fields
        $product['custom_fields'] = $this->db->select('pp_shop_products_custom_fields', '*', ['id_product' => $productId]);

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

        return $product;
    }

    public function isProductOnPromotion(int $productId): bool
    {
        return $this->db->get('pp_shop_products', 'price_netto_promo', ['id' => $productId]) !== null;
    }

    public function productSetsWhenAddToBasket(int $productId): string
    {
        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "ProductRepository::productSetsWhenAddToBasket:$productId";

        $objectData = $cacheHandler->get($cacheKey);

        if (!$objectData) {
            $parentId = $this->db->get('pp_shop_products', 'parent_id', ['id' => $productId]);
            if ($parentId) {
                $setId = $this->db->get('pp_shop_products', 'set_id', ['id' => $parentId]);
            } else {
                $setId = $this->db->get('pp_shop_products', 'set_id', ['id' => $productId]);
            }

            $products = $this->db->select('pp_shop_product_sets_products', 'product_id', ['set_id' => $setId]);
            if (!$products) {
                $products_intersection = $this->db->select('pp_shop_orders_products_intersection', ['product_1_id', 'product_2_id'], ['OR' => ['product_1_id' => $productId, 'product_2_id' => $productId], 'ORDER' => ['count' => 'DESC'], 'LIMIT' => 5]);
                if (!count($products_intersection)) {
                    $parentId2 = $this->db->get('pp_shop_products', 'parent_id', ['id' => $productId]);
                    $products_intersection = $this->db->select('pp_shop_orders_products_intersection', ['product_1_id', 'product_2_id'], ['OR' => ['product_1_id' => $parentId2, 'product_2_id' => $parentId2], 'ORDER' => ['count' => 'DESC'], 'LIMIT' => 5]);
                }

                $products = [];
                foreach ($products_intersection as $pi) {
                    if ($pi['product_1_id'] != $productId) {
                        $products[] = $pi['product_1_id'];
                    } else {
                        $products[] = $pi['product_2_id'];
                    }
                }
                $products = array_unique($products);
            }

            $cacheHandler->set($cacheKey, $products);
        } else {
            $products = unserialize($objectData);
        }

        if (is_array($products)) {
            foreach ($products as $k => $pid) {
                if (!$this->isProductActiveCached((int) $pid)) {
                    unset($products[$k]);
                }
            }
        }

        return \Shared\Tpl\Tpl::view('shop-basket/alert-product-sets', [
            'products' => $products,
        ]);
    }

    public function addVisit(int $productId): void
    {
        $this->db->update('pp_shop_products', ['visits[+]' => 1], ['id' => $productId]);
    }

    public function getProductImg(int $productId)
    {
        $rows = $this->db->select('pp_shop_products_images', 'src', ['product_id' => $productId, 'ORDER' => ['o' => 'ASC']]);
        if (\Shared\Helpers\Helpers::is_array_fix($rows)) {
            foreach ($rows as $row) {
                return $row;
            }
        }
        return null;
    }

    public function getProductUrl(int $productId): string
    {
        $langId = (new \Domain\Languages\LanguagesRepository($this->db))->defaultLanguage();

        $langRows = $this->db->select('pp_shop_products_langs', '*', ['AND' => ['product_id' => $productId, 'lang_id' => $langId]]);
        $language = null;
        if (\Shared\Helpers\Helpers::is_array_fix($langRows)) {
            foreach ($langRows as $row) {
                if ($row['copy_from']) {
                    $copyRows = $this->db->select('pp_shop_products_langs', '*', ['AND' => ['product_id' => $productId, 'lang_id' => $row['copy_from']]]);
                    if (is_array($copyRows)) {
                        foreach ($copyRows as $row2) {
                            $language = $row2;
                        }
                    }
                } else {
                    $language = $row;
                }
            }
        }

        if ($language && $language['seo_link']) {
            return '/' . $language['seo_link'];
        }
        return '/p-' . $productId . '-' . \Shared\Helpers\Helpers::seo($language ? $language['name'] : '');
    }

    public function searchProductsByNameCount(string $query, string $langId): int
    {
        $stmt = $this->db->query('SELECT COUNT(0) AS c FROM ( '
            . 'SELECT psp.id, '
              . '( CASE '
                . 'WHEN copy_from IS NULL THEN name '
                . 'WHEN copy_from IS NOT NULL THEN ( '
                  . 'SELECT name FROM pp_shop_products_langs WHERE lang_id = pspl.copy_from AND product_id = psp.id '
                  . ') '
                . 'END ) AS name '
              . 'FROM pp_shop_products AS psp '
              . 'INNER JOIN pp_shop_products_langs AS pspl ON pspl.product_id = psp.id '
              . 'WHERE status = 1 AND name LIKE :query AND lang_id = :lang_id '
            . ') AS q1', [
            ':query' => '%' . $query . '%',
            ':lang_id' => $langId,
        ]);
        $results = $stmt ? $stmt->fetchAll(\PDO::FETCH_ASSOC) : [];

        return (int) ($results[0]['c'] ?? 0);
    }

    public function getProductsIdByName(string $query, string $langId, int $limit, int $from): array
    {
        $stmt = $this->db->query('SELECT psp.id, '
            . '( CASE '
              . 'WHEN copy_from IS NULL THEN name '
              . 'WHEN copy_from IS NOT NULL THEN ( '
                . 'SELECT name FROM pp_shop_products_langs WHERE lang_id = pspl.copy_from AND product_id = psp.id '
                . ') '
              . 'END ) AS name '
            . 'FROM pp_shop_products AS psp '
            . 'INNER JOIN pp_shop_products_langs AS pspl ON pspl.product_id = psp.id '
            . 'WHERE status = 1 AND name LIKE :query AND lang_id = :lang_id '
            . 'ORDER BY name ASC '
            . 'LIMIT ' . (int) $from . ',' . (int) $limit, [
            ':query' => '%' . $query . '%',
            ':lang_id' => $langId,
        ]);
        $results = $stmt ? $stmt->fetchAll(\PDO::FETCH_ASSOC) : [];

        $output = [];
        if (is_array($results)) {
            foreach ($results as $row) {
                $output[] = $row['id'];
            }
        }
        return $output;
    }

    public function searchProductsByName(string $query, string $langId, int $page = 0): array
    {
        $count = $this->searchProductsByNameCount($query, $langId);
        $ls = ceil($count / 12);

        if ($page < 1) {
            $page = 1;
        } elseif ($page > $ls) {
            $page = (int) $ls;
        }

        $from = 12 * ($page - 1);
        if ($from < 0) {
            $from = 0;
        }

        return [
            'products' => $this->getProductsIdByName($query, $langId, 12, $from),
            'count' => $count,
            'ls' => $ls,
        ];
    }

    public function searchProductByNameAjax(string $query, string $langId): array
    {
        $stmt = $this->db->query(
            'SELECT product_id FROM pp_shop_products_langs AS pspl '
            . 'INNER JOIN pp_shop_products AS psp ON psp.id = pspl.product_id '
            . 'WHERE status = 1 AND lang_id = :lang_id AND LOWER(name) LIKE :query '
            . 'ORDER BY visits DESC LIMIT 12',
            [':query' => '%' . $query . '%', ':lang_id' => $langId]
        );
        $results = $stmt ? $stmt->fetchAll(\PDO::FETCH_ASSOC) : [];

        return is_array($results) ? $results : [];
    }

    public function isStock0Buy(int $productId)
    {
        $parentId = $this->db->get('pp_shop_products', 'parent_id', ['id' => $productId]);
        if ($parentId) {
            return $this->db->get('pp_shop_products', 'stock_0_buy', ['id' => $parentId]);
        }
        return $this->db->get('pp_shop_products', 'stock_0_buy', ['id' => $productId]);
    }

    public function getProductPermutationQuantityOptions(int $productId, $permutation)
    {
        global $settings;

        $cacheHandler = new \Shared\Cache\CacheHandler();
        $cacheKey = "ProductRepository::getProductPermutationQuantityOptions:v2:$productId:$permutation";

        $objectData = $cacheHandler->get($cacheKey);

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

        $result = [];

        if ($this->db->count('pp_shop_products', ['AND' => ['parent_id' => $productId, 'permutation_hash' => $permutation]])) {
            $result['quantity'] = $this->db->get('pp_shop_products', 'quantity', ['AND' => ['parent_id' => $productId, 'permutation_hash' => $permutation]]);
            $result['stock_0_buy'] = $this->db->get('pp_shop_products', 'stock_0_buy', ['AND' => ['parent_id' => $productId, 'permutation_hash' => $permutation]]);

            if ($result['quantity'] === null) {
                $result['quantity'] = $this->db->get('pp_shop_products', 'quantity', ['id' => $productId]);
                if ($result['stock_0_buy'] === null) {
                    $result['stock_0_buy'] = $this->db->get('pp_shop_products', 'stock_0_buy', ['id' => $productId]);
                }
            }
        } else {
            $result['quantity'] = $this->db->get('pp_shop_products', 'quantity', ['id' => $productId]);
            $result['stock_0_buy'] = $this->db->get('pp_shop_products', 'stock_0_buy', ['id' => $productId]);
        }

        $result['messages'] = $this->db->get('pp_shop_products_langs', ['warehouse_message_zero', 'warehouse_message_nonzero'], ['AND' => ['product_id' => $productId, 'lang_id' => 'pl']]);
        if (!isset($result['messages']['warehouse_message_zero']) || !$result['messages']['warehouse_message_zero']) {
            $result['messages']['warehouse_message_zero'] = isset($settings['warehouse_message_zero_pl']) ? $settings['warehouse_message_zero_pl'] : '';
        }
        if (!isset($result['messages']['warehouse_message_nonzero']) || !$result['messages']['warehouse_message_nonzero']) {
            $result['messages']['warehouse_message_nonzero'] = isset($settings['warehouse_message_nonzero_pl']) ? $settings['warehouse_message_nonzero_pl'] : '';
        }

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

        return $result;
    }

    public function getProductIdByAttributes(int $parentId, array $attributes)
    {
        return $this->db->get('pp_shop_products', 'id', ['AND' => ['parent_id' => $parentId, 'permutation_hash' => implode('|', $attributes)]]);
    }

    public function getProductPermutationHash(int $productId)
    {
        return $this->db->get('pp_shop_products', 'permutation_hash', ['id' => $productId]);
    }

    /**
     * Build attribute list from product combinations (array of arrays with permutation_hash).
     */
    public function getProductAttributes($products)
    {
        if (!is_array($products) || !count($products)) {
            return false;
        }

        $attrRepo = new \Domain\Attribute\AttributeRepository($this->db);
        $attributes = [];

        foreach ($products as $product) {
            $hash = is_array($product) ? ($product['permutation_hash'] ?? '') : (is_object($product) ? $product->permutation_hash : '');
            $permutations = explode('|', $hash);
            foreach ($permutations as $permutation) {
                $parts = explode('-', $permutation);
                if (count($parts) < 2) continue;

                $value = [];
                $value['id'] = $parts[1];
                $value['is_default'] = $attrRepo->isValueDefault((int) $parts[1]);

                if (array_search($parts[1], array_column($attributes, 'id')) === false) {
                    $attributes[$parts[0]][] = $value;
                }
            }
        }

        $attributes = \Shared\Helpers\Helpers::removeDuplicates($attributes, 'id');

        $toSort = [];
        foreach ($attributes as $key => $val) {
            $row = [];
            $row['id'] = $key;
            $row['values'] = $val;
            $toSort[] = ['order' => (int) $attrRepo->getAttributeOrder((int) $key), 'data' => $row];
        }
        usort($toSort, function ($a, $b) { return $a['order'] - $b['order']; });

        $sorted = [];
        foreach ($toSort as $i => $item) {
            $sorted[$i + 1] = $item['data'];
        }

        return $sorted;
    }

    public function generateSkuCode(): string
    {
        $skus = $this->db->select('pp_shop_products', 'sku', ['sku[~]' => 'PP-']);
        $codes = [];
        if (is_array($skus)) {
            foreach ($skus as $sku) {
                $codes[] = (int) substr($sku, 3);
            }
        }

        if (!empty($codes)) {
            return 'PP-' . str_pad(max($codes) + 1, 6, '0', STR_PAD_LEFT);
        }
        return 'PP-000001';
    }

    public function productMeta(int $productId): array
    {
        $result = $this->db->select(
            'pp_shop_products_categories (ppc)',
            ['[>]pp_shop_categories_langs (pcl)' => ['ppc.category_id' => 'category_id']],
            ['pcl.title', 'pcl.seo_link'],
            ['ppc.product_id' => $productId]
        );
        return is_array($result) ? $result : [];
    }

    public function generateSubtitleFromAttributes(string $permutationHash, string $langId = null): string
    {
        if (!$langId) {
            global $lang_id;
            $langId = $lang_id;
        }

        $subtitle = '';
        $attrRepo = new \Domain\Attribute\AttributeRepository($this->db);
        $parts = explode('|', $permutationHash);

        foreach ($parts as $part) {
            $attr = explode('-', $part);
            if (count($attr) < 2) continue;

            if ($subtitle) {
                $subtitle .= ', ';
            }
            $subtitle .= $attrRepo->getAttributeNameById((int) $attr[0], $langId) . ': ' . $attrRepo->getAttributeValueById((int) $attr[1], $langId);
        }

        return $subtitle;
    }

    public function getDefaultCombinationPrices(array $product): array
    {
        $prices = [];
        if (!isset($product['product_combinations']) || !is_array($product['product_combinations'])) {
            return $prices;
        }

        $permutationHash = '';
        $attributes = $this->getProductAttributes($product['product_combinations']);
        if (is_array($attributes)) {
            foreach ($attributes as $attribute) {
                foreach ($attribute['values'] as $value) {
                    if ($value['is_default']) {
                        if ($permutationHash) {
                            $permutationHash .= '|';
                        }
                        $permutationHash .= $attribute['id'] . '-' . $value['id'];
                    }
                }
            }
        }

        foreach ($product['product_combinations'] as $combo) {
            $comboHash = is_array($combo) ? ($combo['permutation_hash'] ?? '') : (is_object($combo) ? $combo->permutation_hash : '');
            if ($comboHash == $permutationHash) {
                $prices['price_netto'] = is_array($combo) ? ($combo['price_netto'] ?? null) : $combo->price_netto;
                $prices['price_brutto'] = is_array($combo) ? ($combo['price_brutto'] ?? null) : $combo->price_brutto;
                $prices['price_netto_promo'] = is_array($combo) ? ($combo['price_netto_promo'] ?? null) : $combo->price_netto_promo;
                $prices['price_brutto_promo'] = is_array($combo) ? ($combo['price_brutto_promo'] ?? null) : $combo->price_brutto_promo;
            }
        }

        return $prices;
    }

    public function getProductDataBySelectedAttributes(array $product, string $selectedAttribute): array
    {
        global $settings;

        $result = [];

        if (isset($product['product_combinations']) && is_array($product['product_combinations'])) {
            foreach ($product['product_combinations'] as $combo) {
                $comboHash = is_array($combo) ? ($combo['permutation_hash'] ?? '') : (is_object($combo) ? $combo->permutation_hash : '');
                if ($comboHash == $selectedAttribute) {
                    $comboQty = is_array($combo) ? ($combo['quantity'] ?? null) : $combo->quantity;
                    $comboS0B = is_array($combo) ? ($combo['stock_0_buy'] ?? null) : $combo->stock_0_buy;

                    if ($comboQty !== null || $comboS0B) {
                        $result['quantity'] = $comboQty;
                        $result['stock_0_buy'] = $comboS0B;
                        $result['price_netto'] = \Shared\Helpers\Helpers::shortPrice(is_array($combo) ? $combo['price_netto'] : $combo->price_netto);
                        $result['price_brutto'] = \Shared\Helpers\Helpers::shortPrice(is_array($combo) ? $combo['price_brutto'] : $combo->price_brutto);
                        $pnp = is_array($combo) ? ($combo['price_netto_promo'] ?? null) : $combo->price_netto_promo;
                        $pbp = is_array($combo) ? ($combo['price_brutto_promo'] ?? null) : $combo->price_brutto_promo;
                        $result['price_netto_promo'] = $pnp ? \Shared\Helpers\Helpers::shortPrice($pnp) : null;
                        $result['price_brutto_promo'] = $pbp ? \Shared\Helpers\Helpers::shortPrice($pbp) : null;
                    } else {
                        $result['quantity'] = $product['quantity'] ?? null;
                        $result['stock_0_buy'] = $product['stock_0_buy'] ?? null;
                        $result['price_netto'] = \Shared\Helpers\Helpers::shortPrice($product['price_netto'] ?? 0);
                        $result['price_brutto'] = \Shared\Helpers\Helpers::shortPrice($product['price_brutto'] ?? 0);
                        $result['price_netto_promo'] = ($product['price_netto_promo'] ?? null) ? \Shared\Helpers\Helpers::shortPrice($product['price_netto_promo']) : null;
                        $result['price_brutto_promo'] = ($product['price_brutto_promo'] ?? null) ? \Shared\Helpers\Helpers::shortPrice($product['price_brutto_promo']) : null;
                    }
                }
            }
        }

        $lang = isset($product['language']) ? $product['language'] : [];
        $result['messages']['warehouse_message_zero'] = !empty($lang['warehouse_message_zero']) ? $lang['warehouse_message_zero'] : (isset($settings['warehouse_message_zero_pl']) ? $settings['warehouse_message_zero_pl'] : '');
        $result['messages']['warehouse_message_nonzero'] = !empty($lang['warehouse_message_nonzero']) ? $lang['warehouse_message_nonzero'] : (isset($settings['warehouse_message_nonzero_pl']) ? $settings['warehouse_message_nonzero_pl'] : '');
        $result['permutation_hash'] = $selectedAttribute;

        return $result;
    }

    public function productCategories(int $productId): array
    {
        $result = $this->db->select('pp_shop_products_categories', 'category_id', ['product_id' => $productId]);
        return is_array($result) ? $result : [];
    }

    public static function arrayCartesian(array $input): array
    {
        $result = [];

        foreach ($input as $key => $values) {
            if (empty($values)) {
                continue;
            }

            if (empty($result)) {
                foreach ($values as $value) {
                    $result[] = [$key => $value];
                }
            } else {
                $append = [];
                foreach ($result as &$product) {
                    $product[$key] = array_shift($values);
                    $copy = $product;

                    foreach ($values as $item) {
                        $copy[$key] = $item;
                        $append[] = $copy;
                    }

                    array_unshift($values, $product[$key]);
                }
                $result = array_merge($result, $append);
            }
        }

        return $result;
    }
}