<?php
/**
 * phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
 * phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
 * phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
 * phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
 */

declare(strict_types=1);

namespace Agent001\Data;

\defined( 'ABSPATH' ) || exit;

use wpdb;

final class ProductReader
{
    private const META_KEYS = [
        '_price',
        '_regular_price',
        '_stock',
        '_stock_status',
        '_sku',
    ];

    private const PARENT_TYPE    = 'product';
    private const VARIATION_TYPE = 'product_variation';

    private const TAXONOMY = 'product_cat';

    private const BRAND_TAXONOMIES = [ 'product_brand', 'pwb-brand', 'yith_product_brand', 'berocket_brand', 'brand' ];

    private wpdb $wpdb;

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

    /**
     * @return array<int, array{type: string, id: string, title: string, content: string, url: string}>
     */
    public function readStoreContext(): array
    {
        $context = [];
        $categories = \get_terms( [ 'taxonomy' => self::TAXONOMY, 'hide_empty' => false ] );
        if ( ! \is_wp_error( $categories ) && is_array( $categories ) ) {
            foreach ( $categories as $term ) {
                if ( is_object( $term ) ) {
                    $this->appendTermContext( $context, 'category', self::TAXONOMY, $term );
                }
            }
        }

        if ( \function_exists( 'wc_get_attribute_taxonomies' ) ) {
            foreach ( (array) \wc_get_attribute_taxonomies() as $attribute ) {
                if ( ! is_object( $attribute ) || empty( $attribute->attribute_name ) ) {
                    continue;
                }
                $taxonomy = \wc_attribute_taxonomy_name( (string) $attribute->attribute_name );
                $label = \function_exists( 'wc_attribute_label' ) ? (string) \wc_attribute_label( $taxonomy ) : $taxonomy;
                $context[] = [
                    'type' => 'attribute',
                    'id' => $taxonomy,
                    'title' => $label,
                    'content' => 'Global WooCommerce product attribute.',
                    'url' => '',
                ];
                $terms = \get_terms( [ 'taxonomy' => $taxonomy, 'hide_empty' => false ] );
                if ( \is_wp_error( $terms ) || ! is_array( $terms ) ) {
                    continue;
                }
                foreach ( $terms as $term ) {
                    if ( is_object( $term ) ) {
                        $this->appendTermContext( $context, 'attribute', $taxonomy, $term );
                    }
                }
            }
        }

        $taxonomies = \get_object_taxonomies( self::PARENT_TYPE, 'objects' );
        if ( is_array( $taxonomies ) ) {
            foreach ( $taxonomies as $taxonomy ) {
                if ( ! is_object( $taxonomy ) || empty( $taxonomy->public ) || empty( $taxonomy->name )
                    || $taxonomy->name === self::TAXONOMY || strpos( (string) $taxonomy->name, 'pa_' ) === 0 ) {
                    continue;
                }
                $label = (string) ( $taxonomy->label ?? $taxonomy->name );
                $context[] = [
                    'type' => 'taxonomy',
                    'id' => (string) $taxonomy->name,
                    'title' => \wp_html_excerpt( $label, 500, '…' ),
                    'content' => sprintf( 'Public product taxonomy: %s. Hierarchical: %s.', $taxonomy->name, ! empty( $taxonomy->hierarchical ) ? 'yes' : 'no' ),
                    'url' => '',
                ];
                $terms = \get_terms( [ 'taxonomy' => (string) $taxonomy->name, 'hide_empty' => false ] );
                if ( \is_wp_error( $terms ) || ! is_array( $terms ) ) {
                    continue;
                }
                foreach ( $terms as $term ) {
                    if ( is_object( $term ) ) {
                        $this->appendTermContext( $context, 'taxonomy', (string) $taxonomy->name, $term, $label );
                    }
                }
            }
        }

        $this->appendPolicyContext( $context, \function_exists( 'wc_get_page_id' ) ? (int) \wc_get_page_id( 'terms' ) : 0, 'terms', 'Terms and conditions' );
        $this->appendPolicyContext( $context, (int) \get_option( 'wp_page_for_privacy_policy', 0 ), 'privacy-policy', 'Privacy policy' );

        return $context;
    }

    private function appendTermContext( array &$context, string $type, string $taxonomy, object $term, ?string $label = null ): void
    {
        if ( empty( $term->term_id ) || ! isset( $term->name ) ) {
            return;
        }
        $content = sprintf( 'Taxonomy: %s. Slug: %s.', $label ?? $taxonomy, (string) ( $term->slug ?? '' ) );
        if ( ! empty( $term->description ) ) {
            $content .= ' Description: ' . \wp_html_excerpt( \wp_strip_all_tags( (string) $term->description ), 1000, '…' );
        }
        if ( ! empty( $term->parent ) ) {
            $parent = \get_term( (int) $term->parent, $taxonomy );
            if ( ! \is_wp_error( $parent ) && is_object( $parent ) && isset( $parent->name ) ) {
                $content .= ' Parent: ' . (string) $parent->name . '.';
            }
        }
        $url = \get_term_link( $term );
        $context[] = [
            'type' => $type,
            'id' => $taxonomy . ':' . (int) $term->term_id,
            'title' => \wp_html_excerpt( (string) $term->name, 500, '…' ),
            'content' => $content,
            'url' => \is_wp_error( $url ) ? '' : (string) $url,
        ];
    }

    private function appendPolicyContext( array &$context, int $pageId, string $id, string $title ): void
    {
        $page = $pageId > 0 ? \get_post( $pageId ) : null;
        if ( ! is_object( $page ) || (string) ( $page->post_status ?? '' ) !== 'publish' ) {
            return;
        }
        $content = \strip_shortcodes( \wp_strip_all_tags( (string) ( $page->post_content ?? '' ) ) );
        $content = trim( (string) preg_replace( '/\s+/u', ' ', html_entity_decode( $content, ENT_QUOTES | ENT_HTML5, 'UTF-8' ) ) );
        if ( $content === '' ) {
            return;
        }
        preg_match_all( '/.{1,6000}/us', $content, $matches );
        foreach ( $matches[0] as $index => $part ) {
            $context[] = [
                'type' => 'document',
                'id' => $id . ':' . $index,
                'title' => $title,
                'content' => $part,
                'url' => (string) \get_permalink( $pageId ),
            ];
        }
    }

    private function checkQueryError(): void
    {
        if ( $this->wpdb->last_error !== '' ) {
            throw new \RuntimeException( \esc_html__( 'Could not read the product catalog. Please try again.', 'agent001-support-assistant-for-woocommerce' ) );
        }
    }

    private function availableStockCondition(): string
    {
        return "NOT EXISTS (
            SELECT 1 FROM {$this->wpdb->postmeta} stock_quantity
            WHERE stock_quantity.post_id = p.ID
              AND stock_quantity.meta_key = '_stock'
              AND stock_quantity.meta_value <> ''
              AND CAST(stock_quantity.meta_value AS DECIMAL(20,6)) <= 0
        ) AND NOT EXISTS (
            SELECT 1 FROM {$this->wpdb->postmeta} stock_status
            WHERE stock_status.post_id = p.ID
              AND stock_status.meta_key = '_stock_status'
              AND stock_status.meta_value = 'outofstock'
        )";
    }

    private function parentCondition(): string
    {
        return $this->availableStockCondition() . " AND p.ID NOT IN (
            SELECT visibility.object_id
            FROM {$this->wpdb->term_relationships} visibility
            INNER JOIN {$this->wpdb->term_taxonomy} visibility_tt
                ON visibility_tt.term_taxonomy_id = visibility.term_taxonomy_id
                AND visibility_tt.taxonomy = 'product_visibility'
            INNER JOIN {$this->wpdb->terms} visibility_t
                ON visibility_t.term_id = visibility_tt.term_id
                AND visibility_t.slug IN ('exclude-from-catalog', 'exclude-from-search')
            GROUP BY visibility.object_id
            HAVING COUNT(DISTINCT visibility_t.slug) = 2
        )";
    }

    /**
     * @param int[] $categoryIds
     */
    public function count( array $categoryIds = [] ): int
    {
        if ( $categoryIds === [] ) {
            $count = $this->wpdb->get_var(
                $this->wpdb->prepare(
                    "SELECT COUNT(*) FROM {$this->wpdb->posts} p WHERE p.post_type = %s AND p.post_status = 'publish' AND " . $this->parentCondition(),
                    self::PARENT_TYPE
                )
            );
            $this->checkQueryError();
            return (int) $count;
        }

        $idPlaceholder = implode( ', ', array_fill( 0, count( $categoryIds ), '%d' ) );
        $args          = array_merge( [ self::TAXONOMY ], $categoryIds, [ self::PARENT_TYPE ] );

        $count = $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT COUNT(DISTINCT p.ID)
                FROM {$this->wpdb->posts} p
                INNER JOIN {$this->wpdb->term_relationships} tr ON tr.object_id = p.ID
                INNER JOIN {$this->wpdb->term_taxonomy} tt
                    ON tt.term_taxonomy_id = tr.term_taxonomy_id
                    AND tt.taxonomy = %s
                    AND tt.term_id IN ({$idPlaceholder})
                WHERE p.post_type = %s
                  AND p.post_status = 'publish'
                  AND " . $this->parentCondition(),
                $args
            )
        );
        $this->checkQueryError();
        return (int) $count;
    }

    /**
     * @param int[] $categoryIds
     */
    public function readBatchAfterId( int $afterId, int $limit, array $categoryIds = [] ): array
    {
        $rows = $this->fetchParentRowsAfterId( $afterId, $limit, $categoryIds );

        if ( $rows === [] ) {
            return [];
        }

        $parentIds   = array_map( static fn( array $row ): int => (int) $row['id'], $rows );
        $categories  = $this->loadCategories( $parentIds );
        $taxonomies  = $this->loadProductTaxonomies( $parentIds );
        $wooProducts = $this->loadWooProducts( $parentIds );
        $variations  = $this->loadVariationsForParents( $parentIds );

        $products = [];
        foreach ( $rows as $row ) {
            $id         = (int) $row['id'];
            $products[] = $this->buildProduct(
                $row,
                $categories[ $id ] ?? [],
                $taxonomies[ $id ] ?? [],
                $wooProducts[ $id ] ?? null,
                $variations[ $id ] ?? []
            );
        }

        return $products;
    }

    public function readOne( int $id, array $categoryIds = [] ): ?array
    {
        $parentId = $this->resolveParentId( $id );
        if ( $parentId === null ) {
            return null;
        }

        if ( $categoryIds !== [] ) {
            $termIds = \wp_get_post_terms( $parentId, 'product_cat', [ 'fields' => 'ids' ] );
            if ( \is_wp_error( $termIds ) ) {
                throw new \RuntimeException( \esc_html__( 'Could not read the product categories. Please try again.', 'agent001-support-assistant-for-woocommerce' ) );
            }
            if ( array_intersect( $categoryIds, $termIds ) === [] ) {
                return null;
            }
        }

        $rows = $this->fetchParentRowsByIds( [ $parentId ] );
        if ( $rows === [] ) {
            return null;
        }

        $row         = $rows[0];
        $rowId       = (int) $row['id'];
        $categories  = $this->loadCategories( [ $rowId ] );
        $taxonomies  = $this->loadProductTaxonomies( [ $rowId ] );
        $wooProducts = $this->loadWooProducts( [ $rowId ] );
        $variations  = $this->loadVariationsForParents( [ $rowId ] );

        return $this->buildProduct(
            $row,
            $categories[ $rowId ] ?? [],
            $taxonomies[ $rowId ] ?? [],
            $wooProducts[ $rowId ] ?? null,
            $variations[ $rowId ] ?? []
        );
    }

    /**
     * @param array<string, mixed>                       $row
     * @param array{names?: string[], paths?: array}     $categories
     * @param array<string, array<int, string>>          $taxonomies
     * @param mixed                                      $wooProduct
     * @param array<int, array<string, mixed>>           $variations
     * @return array<string, mixed>
     */
    private function buildProduct( array $row, array $categories, array $taxonomies, $wooProduct, array $variations ): array
    {
        $id       = (int) $row['id'];
        $imageUrl = \get_the_post_thumbnail_url( $id, 'full' );
        $price    = $row['price'] !== null ? (string) $row['price'] : null;
        $regular  = $row['regular_price'] !== null ? (string) $row['regular_price'] : null;

        if ( is_object( $wooProduct ) && method_exists( $wooProduct, 'is_type' ) && $wooProduct->is_type( 'variable' ) ) {
            $minPrice   = (string) $wooProduct->get_variation_price( 'min', false );
            $minRegular = (string) $wooProduct->get_variation_regular_price( 'min', false );
            $price      = $minPrice !== '' ? $minPrice : $price;
            $regular    = $minRegular !== '' ? $minRegular : $regular;
        }

        $product = [
            'id'            => $id,
            'type'          => self::PARENT_TYPE,
            'title'         => (string) $row['title'],
            'description'   => \strip_shortcodes( (string) $row['description'] ),
            'parent_id'     => 0,
            'sku'           => $row['sku'] !== null ? (string) $row['sku'] : null,
            'price'         => $price,
            'regular_price' => $regular,
            'stock'         => $row['stock'] !== null ? (string) $row['stock'] : null,
            'stock_status'  => $row['stock_status'] !== null ? (string) $row['stock_status'] : null,
            'currency'      => \function_exists( 'get_woocommerce_currency' ) ? (string) \get_woocommerce_currency() : null,
            'categories'    => $categories['names'] ?? [],
            'category_paths' => $categories['paths'] ?? [],
            'taxonomies'    => $taxonomies,
            'brand'         => $this->brandFrom( $taxonomies ),
            'url'           => (string) \get_permalink( $id ),
            'image_url'     => is_string( $imageUrl ) ? $imageUrl : null,
            'attributes'    => $this->productAttributes( $wooProduct ),
            'variations'    => $variations,
        ];

        return array_merge( $product, $this->productExtras( $wooProduct ) );
    }

    /**
     * @param array<string, array<int, string>> $taxonomies
     */
    private function brandFrom( array $taxonomies ): ?string
    {
        foreach ( self::BRAND_TAXONOMIES as $taxonomy ) {
            if ( isset( $taxonomies[ $taxonomy ][0] ) && $taxonomies[ $taxonomy ][0] !== '' ) {
                return $taxonomies[ $taxonomy ][0];
            }
        }

        return null;
    }

    /**
     * @param mixed $product
     * @return array<string, mixed>
     */
    private function productExtras( $product ): array
    {
        if ( ! is_object( $product ) || ! method_exists( $product, 'get_type' ) ) {
            return [];
        }

        $extras = [
            'product_type'       => (string) $product->get_type(),
            'on_sale'            => (bool) $product->is_on_sale(),
            'catalog_visibility' => (string) $product->get_catalog_visibility(),
        ];

        $shortDescription = trim( \strip_shortcodes( (string) $product->get_short_description() ) );
        if ( $shortDescription !== '' ) {
            $extras['short_description'] = $shortDescription;
        }

        $salePrice = (string) $product->get_sale_price();
        if ( $salePrice !== '' ) {
            $extras['sale_price'] = $salePrice;
        }

        $weight = (string) $product->get_weight();
        if ( $weight !== '' ) {
            $extras['weight'] = $weight;
        }

        if ( $product->has_dimensions() ) {
            $extras['dimensions'] = \wc_format_dimensions( $product->get_dimensions( false ) );
        }

        if ( (int) $product->get_review_count() > 0 ) {
            $extras['average_rating'] = (string) $product->get_average_rating();
            $extras['review_count']   = (int) $product->get_review_count();
        }

        return $extras;
    }

    /**
     * @param int[] $postIds
     * @return array<int, array<string, array<int, string>>>
     */
    private function loadProductTaxonomies( array $postIds ): array
    {
        if ( $postIds === [] ) {
            return [];
        }

        $registered = \get_object_taxonomies( self::PARENT_TYPE, 'objects' );
        if ( ! is_array( $registered ) ) {
            return [];
        }

        $taxonomyNames = [];
        foreach ( $registered as $taxonomy ) {
            if ( ! is_object( $taxonomy ) || empty( $taxonomy->public ) || empty( $taxonomy->name ) ) {
                continue;
            }

            $name = (string) $taxonomy->name;
            if ( $name === self::TAXONOMY || strpos( $name, 'pa_' ) === 0 ) {
                continue;
            }

            $taxonomyNames[] = $name;
        }

        if ( $taxonomyNames === [] ) {
            return [];
        }

        $terms = \wp_get_object_terms( $postIds, $taxonomyNames, [ 'fields' => 'all_with_object_id' ] );
        if ( \is_wp_error( $terms ) ) {
            throw new \RuntimeException( \esc_html__( 'Could not read the product taxonomies. Please try again.', 'agent001-support-assistant-for-woocommerce' ) );
        }
        if ( ! is_array( $terms ) ) {
            return [];
        }

        $grouped = [];
        foreach ( $terms as $term ) {
            if ( ! is_object( $term ) || empty( $term->object_id ) || empty( $term->taxonomy ) || ! isset( $term->name ) ) {
                continue;
            }

            $postId = (int) $term->object_id;
            $name   = (string) $term->taxonomy;
            $value  = (string) $term->name;
            if ( ! in_array( $name, $taxonomyNames, true ) || $value === '' ) {
                continue;
            }

            $grouped[ $postId ][ $name ][] = $value;
        }

        foreach ( $grouped as $postId => $productTaxonomies ) {
            foreach ( $productTaxonomies as $name => $values ) {
                $grouped[ $postId ][ $name ] = array_values( array_unique( $values ) );
            }
        }

        return $grouped;
    }

    /**
     * @param int[] $productIds
     * @return array<int, object>
     */
    private function loadWooProducts( array $productIds, bool $variations = false ): array
    {
        if ( $productIds === [] ) {
            return [];
        }

        $args = [
            'include' => $productIds,
            'limit'   => count( $productIds ),
            'return'  => 'objects',
        ];
        if ( $variations ) {
            $args['type'] = [ 'variation' ];
        }

        $products = \wc_get_products( $args );
        if ( ! is_array( $products ) ) {
            return [];
        }

        $indexed = [];
        foreach ( $products as $product ) {
            if ( is_object( $product ) && method_exists( $product, 'get_id' ) ) {
                $indexed[ (int) $product->get_id() ] = $product;
            }
        }

        return $indexed;
    }

    /**
     * @param mixed $product
     * @return array<int, array{name: string, options: array<int, string>}>
     */
    private function productAttributes( $product ): array
    {
        if ( ! is_object( $product ) || ! method_exists( $product, 'get_attributes' ) ) {
            return [];
        }

        $attributes = [];
        foreach ( $product->get_attributes() as $attribute ) {
            if ( ! is_object( $attribute ) || ! method_exists( $attribute, 'get_name' ) ) {
                continue;
            }

            $name = (string) $attribute->get_name();
            if ( $name === '' ) {
                continue;
            }

            if ( method_exists( $attribute, 'is_taxonomy' ) && $attribute->is_taxonomy() ) {
                $options = \wc_get_product_terms(
                    (int) $product->get_id(),
                    $name,
                    [ 'fields' => 'names' ]
                );
            } elseif ( method_exists( $attribute, 'get_options' ) ) {
                $options = $attribute->get_options();
            } else {
                continue;
            }

            if ( ! is_array( $options ) || \is_wp_error( $options ) ) {
                continue;
            }

            $options = array_values( array_filter( array_map( 'strval', $options ), static function ( string $option ): bool {
                return trim( $option ) !== '';
            } ) );
            if ( $options === [] ) {
                continue;
            }

            $attributes[] = [
                'name'    => (string) \wc_attribute_label( $name, $product ),
                'options' => $options,
            ];
        }

        return $attributes;
    }

    private function resolveParentId( int $id ): ?int
    {
        $row = $this->wpdb->get_row(
            $this->wpdb->prepare(
                "SELECT
                    CASE WHEN p.post_type = %s THEN p.post_parent ELSE p.ID END AS parent_id,
                    p.post_type,
                    p.post_status
                FROM {$this->wpdb->posts} p
                WHERE p.ID = %d",
                self::VARIATION_TYPE,
                $id
            ),
            ARRAY_A
        );

        $this->checkQueryError();
        if ( ! is_array( $row ) ) {
            return null;
        }

        $parentId = (int) ( $row['parent_id'] ?? 0 );

        return $parentId > 0 ? $parentId : null;
    }

    /**
     * @param int[] $parentIds
     */
    private function fetchParentRowsByIds( array $parentIds ): array
    {
        if ( $parentIds === [] ) {
            return [];
        }

        $idPlaceholder   = implode( ', ', array_fill( 0, count( $parentIds ), '%d' ) );
        $metaPlaceholder = implode( ', ', array_fill( 0, count( self::META_KEYS ), '%s' ) );
        $args            = array_merge( self::META_KEYS, [ self::PARENT_TYPE ], $parentIds );

        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT
                    p.ID AS id,
                    p.post_title AS title,
                    p.post_content AS description,
                    MAX(CASE WHEN pm.meta_key = '_price' THEN pm.meta_value END) AS price,
                    MAX(CASE WHEN pm.meta_key = '_regular_price' THEN pm.meta_value END) AS regular_price,
                    MAX(CASE WHEN pm.meta_key = '_stock' THEN pm.meta_value END) AS stock,
                    MAX(CASE WHEN pm.meta_key = '_stock_status' THEN pm.meta_value END) AS stock_status,
                    MAX(CASE WHEN pm.meta_key = '_sku' THEN pm.meta_value END) AS sku
                FROM {$this->wpdb->posts} p
                LEFT JOIN {$this->wpdb->postmeta} pm
                    ON pm.post_id = p.ID AND pm.meta_key IN ({$metaPlaceholder})
                WHERE p.post_type = %s
                  AND p.post_status = 'publish'
                  AND p.ID IN ({$idPlaceholder})
                  AND " . $this->parentCondition() . "
                GROUP BY p.ID",
                $args
            ),
            ARRAY_A
        );

        $this->checkQueryError();
        return is_array( $rows ) ? $rows : [];
    }

    /**
     * @param int[] $categoryIds
     */
    private function fetchParentRowsAfterId( int $afterId, int $limit, array $categoryIds = [] ): array
    {
        $metaPlaceholder = implode( ', ', array_fill( 0, count( self::META_KEYS ), '%s' ) );

        if ( $categoryIds === [] ) {
            $args = array_merge( self::META_KEYS, [ self::PARENT_TYPE, $afterId, $limit ] );

            $rows = $this->wpdb->get_results(
                $this->wpdb->prepare(
                    "SELECT
                        p.ID AS id,
                        p.post_title AS title,
                        p.post_content AS description,
                        MAX(CASE WHEN pm.meta_key = '_price' THEN pm.meta_value END) AS price,
                        MAX(CASE WHEN pm.meta_key = '_regular_price' THEN pm.meta_value END) AS regular_price,
                        MAX(CASE WHEN pm.meta_key = '_stock' THEN pm.meta_value END) AS stock,
                        MAX(CASE WHEN pm.meta_key = '_stock_status' THEN pm.meta_value END) AS stock_status,
                        MAX(CASE WHEN pm.meta_key = '_sku' THEN pm.meta_value END) AS sku
                    FROM {$this->wpdb->posts} p
                    LEFT JOIN {$this->wpdb->postmeta} pm
                        ON pm.post_id = p.ID AND pm.meta_key IN ({$metaPlaceholder})
                    WHERE p.post_type = %s
                      AND p.post_status = 'publish'
                      AND p.ID > %d
                      AND " . $this->parentCondition() . "
                    GROUP BY p.ID
                    ORDER BY p.ID ASC
                    LIMIT %d",
                    $args
                ),
                ARRAY_A
            );

            $this->checkQueryError();
            return is_array( $rows ) ? $rows : [];
        }

        $idPlaceholder = implode( ', ', array_fill( 0, count( $categoryIds ), '%d' ) );
        $args          = array_merge(
            [ self::TAXONOMY ],
            $categoryIds,
            self::META_KEYS,
            [ self::PARENT_TYPE, $afterId, $limit ]
        );

        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT
                    p.ID AS id,
                    p.post_title AS title,
                    p.post_content AS description,
                    MAX(CASE WHEN pm.meta_key = '_price' THEN pm.meta_value END) AS price,
                    MAX(CASE WHEN pm.meta_key = '_regular_price' THEN pm.meta_value END) AS regular_price,
                    MAX(CASE WHEN pm.meta_key = '_stock' THEN pm.meta_value END) AS stock,
                    MAX(CASE WHEN pm.meta_key = '_stock_status' THEN pm.meta_value END) AS stock_status,
                    MAX(CASE WHEN pm.meta_key = '_sku' THEN pm.meta_value END) AS sku
                FROM {$this->wpdb->posts} p
                INNER JOIN {$this->wpdb->term_relationships} tr ON tr.object_id = p.ID
                INNER JOIN {$this->wpdb->term_taxonomy} tt
                    ON tt.term_taxonomy_id = tr.term_taxonomy_id
                    AND tt.taxonomy = %s
                    AND tt.term_id IN ({$idPlaceholder})
                LEFT JOIN {$this->wpdb->postmeta} pm
                    ON pm.post_id = p.ID AND pm.meta_key IN ({$metaPlaceholder})
                WHERE p.post_type = %s
                  AND p.post_status = 'publish'
                  AND p.ID > %d
                  AND " . $this->parentCondition() . "
                GROUP BY p.ID
                ORDER BY p.ID ASC
                LIMIT %d",
                $args
            ),
            ARRAY_A
        );

        $this->checkQueryError();
        return is_array( $rows ) ? $rows : [];
    }

    /**
     * @param int[] $parentIds
     */
    private function loadVariationsForParents( array $parentIds ): array
    {
        if ( $parentIds === [] ) {
            return [];
        }

        $idPlaceholder   = implode( ', ', array_fill( 0, count( $parentIds ), '%d' ) );
        $metaPlaceholder = implode( ', ', array_fill( 0, count( self::META_KEYS ), '%s' ) );
        $args            = array_merge( self::META_KEYS, [ self::VARIATION_TYPE ], $parentIds );

        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT
                    p.ID AS id,
                    p.post_parent AS parent_id,
                    p.post_title AS title,
                    MAX(CASE WHEN pm.meta_key = '_price' THEN pm.meta_value END) AS price,
                    MAX(CASE WHEN pm.meta_key = '_regular_price' THEN pm.meta_value END) AS regular_price,
                    MAX(CASE WHEN pm.meta_key = '_stock' THEN pm.meta_value END) AS stock,
                    MAX(CASE WHEN pm.meta_key = '_stock_status' THEN pm.meta_value END) AS stock_status,
                    MAX(CASE WHEN pm.meta_key = '_sku' THEN pm.meta_value END) AS sku
                FROM {$this->wpdb->posts} p
                LEFT JOIN {$this->wpdb->postmeta} pm
                    ON pm.post_id = p.ID AND pm.meta_key IN ({$metaPlaceholder})
                WHERE p.post_type = %s
                  AND p.post_status = 'publish'
                  AND p.post_parent IN ({$idPlaceholder})
                  AND " . $this->availableStockCondition() . "
                GROUP BY p.ID
                ORDER BY p.post_parent ASC, p.ID ASC",
                $args
            ),
            ARRAY_A
        );

        $this->checkQueryError();
        if ( ! is_array( $rows ) ) {
            return [];
        }

        $variationIds = array_map( static fn( array $row ): int => (int) $row['id'], $rows );
        $variationProducts = $this->loadWooProducts( $variationIds, true );
        $grouped = [];
        foreach ( $rows as $row ) {
            $parentId = (int) $row['parent_id'];
            $id       = (int) $row['id'];
            $variation = $variationProducts[ $id ] ?? null;

            if ( ! isset( $grouped[ $parentId ] ) ) {
                $grouped[ $parentId ] = [];
            }

            $grouped[ $parentId ][] = [
                'id'            => $id,
                'title'         => (string) $row['title'],
                'sku'           => $row['sku'] !== null ? (string) $row['sku'] : null,
                'price'         => $row['price'] !== null ? (string) $row['price'] : null,
                'regular_price' => $row['regular_price'] !== null ? (string) $row['regular_price'] : null,
                'stock'         => $row['stock'] !== null ? (string) $row['stock'] : null,
                'stock_status'  => $row['stock_status'] !== null ? (string) $row['stock_status'] : null,
                'url'           => (string) \get_permalink( $id ),
                'attributes'    => $this->variationAttributes( $variation ),
            ];

            $variationImage = \get_the_post_thumbnail_url( $id, 'full' );
            if ( is_string( $variationImage ) && $variationImage !== '' ) {
                $grouped[ $parentId ][ count( $grouped[ $parentId ] ) - 1 ]['image_url'] = $variationImage;
            }
        }

        return $grouped;
    }

    /**
     * @param mixed $variation
     * @return array<int, array{name: string, option: string}>
     */
    private function variationAttributes( $variation ): array
    {
        if ( ! is_object( $variation ) || ! method_exists( $variation, 'get_attributes' ) ) {
            return [];
        }

        $attributes = [];
        foreach ( $variation->get_attributes() as $name => $option ) {
            $name = (string) $name;
            $option = (string) $option;
            if ( $name === '' || $option === '' ) {
                continue;
            }

            $value = method_exists( $variation, 'get_attribute' )
                ? (string) $variation->get_attribute( $name )
                : $option;

            $attributes[] = [
                'name'   => (string) \wc_attribute_label( $name, $variation ),
                'option' => $value,
            ];
        }

        return $attributes;
    }

    /**
     * @param int[] $postIds
     * @return array<int, array{names: string[], paths: array<int, string[]>}>
     */
    private function loadCategories( array $postIds ): array
    {
        if ( $postIds === [] ) {
            return [];
        }

        $idPlaceholder = implode( ', ', array_fill( 0, count( $postIds ), '%d' ) );
        $args          = array_merge( [ self::TAXONOMY ], $postIds );

        $rows = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT tr.object_id AS post_id, t.term_id AS term_id, t.name AS name
                FROM {$this->wpdb->term_relationships} tr
                INNER JOIN {$this->wpdb->term_taxonomy} tt
                    ON tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = %s
                INNER JOIN {$this->wpdb->terms} t
                    ON t.term_id = tt.term_id
                WHERE tr.object_id IN ({$idPlaceholder})",
                $args
            ),
            ARRAY_A
        );

        $this->checkQueryError();
        if ( ! is_array( $rows ) ) {
            return [];
        }

        $terms = [];
        foreach ( $rows as $row ) {
            $terms[ (int) $row['post_id'] ][ (int) $row['term_id'] ] = (string) $row['name'];
        }

        $grouped = [];
        foreach ( $terms as $postId => $postTerms ) {
            $grouped[ $postId ] = $this->categoryTree( $postTerms );
        }

        return $grouped;
    }

    /**
     * @param array<int, string> $postTerms term_id => name
     * @return array{names: string[], paths: array<int, string[]>}
     */
    private function categoryTree( array $postTerms ): array
    {
        $ancestors = [];
        $parents   = [];
        foreach ( array_keys( $postTerms ) as $termId ) {
            $ancestors[ $termId ] = array_map( 'intval', (array) \get_ancestors( $termId, self::TAXONOMY, 'taxonomy' ) );
            foreach ( $ancestors[ $termId ] as $ancestorId ) {
                $parents[ $ancestorId ] = true;
            }
        }

        $ordered = array_keys( $postTerms );
        usort(
            $ordered,
            static function ( int $a, int $b ) use ( $ancestors, $postTerms ): int {
                $byDepth = count( $ancestors[ $b ] ) <=> count( $ancestors[ $a ] );
                return $byDepth !== 0 ? $byDepth : strcmp( $postTerms[ $a ], $postTerms[ $b ] );
            }
        );

        $names = [];
        $paths = [];
        foreach ( $ordered as $termId ) {
            $names[] = $postTerms[ $termId ];
            if ( isset( $parents[ $termId ] ) ) {
                continue;
            }

            $path = [];
            foreach ( array_reverse( $ancestors[ $termId ] ) as $ancestorId ) {
                $ancestor = \get_term( $ancestorId, self::TAXONOMY );
                if ( is_object( $ancestor ) && isset( $ancestor->name ) ) {
                    $path[] = (string) $ancestor->name;
                }
            }
            $path[]  = $postTerms[ $termId ];
            $paths[] = $path;
        }

        return [
            'names' => array_values( array_unique( $names ) ),
            'paths' => $paths,
        ];
    }
}