<?php
/**
 * Product Repository.
 *
 * Single source of truth for all product CRUD operations.
 * Handles the full product data graph:
 *   - Core product row
 *   - Translations
 *   - Prices (quantity breaks, usergroup tiers)
 *   - Category assignments
 *   - Images
 *   - Options + variants
 *   - Features + values
 *
 * Provides product create / read / update / query operations
 * but in a clean OOP service with repository pattern.
 *
 * @package TitanCart\Modules\Products
 */

namespace TitanCart\Modules\Products;

use TitanCart\DB\Database;
use TitanCart\Services\SettingsService;
use TitanCart\Core\LogService;

defined( 'ABSPATH' ) || exit;

class ProductRepository {

    private Database $db;

    // Table references (fully prefixed).
    private string $t_products;
    private string $t_trans;
    private string $t_prices;
    private string $t_cats;
    private string $t_images;
    private string $t_options;
    private string $t_option_trans;
    private string $t_option_variants;
    private string $t_variant_trans;
    private string $t_option_deps;
    private string $t_features;
    private string $t_feature_trans;
    private string $t_feature_vals;
    private string $t_variation_links;

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

        $this->t_products        = $this->db->table( 'products' );
        $this->t_trans           = $this->db->table( 'product_translations' );
        $this->t_prices          = $this->db->table( 'product_prices' );
        $this->t_cats            = $this->db->table( 'product_categories' );
        $this->t_images          = $this->db->table( 'product_images' );
        $this->t_options         = $this->db->table( 'product_options' );
        $this->t_option_trans    = $this->db->table( 'product_option_translations' );
        $this->t_option_variants = $this->db->table( 'product_option_variants' );
        $this->t_variant_trans   = $this->db->table( 'product_option_variant_translations' );
        $this->t_option_deps     = $this->db->table( 'product_option_dependencies' );
        $this->t_features        = $this->db->table( 'product_features' );
        $this->t_feature_trans   = $this->db->table( 'product_feature_translations' );
        $this->t_feature_vals    = $this->db->table( 'product_feature_values' );
        $this->t_variation_links = $this->db->table( 'product_variation_links' );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // READ
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Retrieve a single product with all associated data.
     *
     * @param  int    $product_id
     * @param  string $lang
     * @return array<string,mixed>|null
     */
    public function get( int $product_id, string $lang = 'en' ): ?array {
        $product = $this->db->getRow(
            "SELECT p.*,
                    pt.name, pt.description, pt.short_desc,
                    pt.meta_title, pt.meta_desc
             FROM {$this->t_products} p
             LEFT JOIN {$this->t_trans} pt
                ON pt.product_id = p.id AND pt.language_code = ?s
             WHERE p.id = ?i AND p.deleted_at IS NULL",
            $lang,
            $product_id
        );

        if ( ! $product ) {
            return null;
        }

        $product['prices']     = $this->get_prices( $product_id );
        $product['categories'] = $this->get_category_ids( $product_id );
        $product['images']     = $this->get_images( $product_id );
        $product['options']    = $this->get_options( $product_id, $lang );
        try {
            $product['features'] = $this->get_feature_values( $product_id, $lang );
        } catch ( \Throwable $e ) {
            $product['features'] = [];
            titancart_log( '[TitanCart] get_feature_values failed: ' . $e->getMessage() );
        }

        // Decode JSON columns.
        $product['videos'] = ! empty( $product['videos'] ) ? ( json_decode( $product['videos'], true ) ?: [] ) : [];

        do_action_ref_array( 'titancart_get_product', [ &$product, $product_id, $lang ] );

        return $product;
    }

    /**
     * Retrieve a paginated, filtered list of products.
     *
     * Supported $params keys:
     *   status          string            'active'|'disabled'|'hidden'|''
     *   type            string            'simple'|'variable'|...
     *   category_id     int               Filter to products in a category
     *   search          string            Name LIKE search
     *   sku             string            Exact SKU match
     *   brand_id        int
     *   featured        bool
     *   stock_status    string            'in_stock'|'out_of_stock'
     *   price_min       float
     *   price_max       float
     *   min_rating      float             0–5; products with rating_avg >= N
     *   updated_within_days int           Products with updated_at within last N days
     *   ids             int[]             Specific product IDs
     *   parent_id       int               0 = no parent (top-level only)
     *   sort_by         string            'name'|'price'|'position'|'date'|'sales'|'rating'|'discount'
     *   sort_order      string            'asc'|'desc'
     *   items_per_page  int               0 = no limit
     *   page            int               1-based
     *
     * @param  array<string,mixed> $params
     * @param  string              $lang
     * @return array{ products: array[], params: array }
     */
    public function get_list( array $params = [], string $lang = 'en' ): array {
        $params = array_merge( [
            'status'         => 'active',
            'type'           => '',
            'category_id'    => 0,
            'category_ids'   => [],
            'search'         => '',
            'sku'            => '',
            'brand_id'       => 0,
            'featured'       => null,
            'stock_status'   => '',
            'price_min'      => null,
            'price_max'      => null,
            'min_rating'     => null,
            'updated_within_days' => null,
            'ids'            => [],
            'parent_id'      => null,
            'sort_by'        => 'date',
            'sort_order'     => 'desc',
            'items_per_page' => 24,
            'page'           => 1,
            // roadmap #15: when true, exclude non-representative members of
            // "display as a single product" groups from this listing. Storefront
            // callers pass true; admin listings leave it false so every member
            // SKU stays editable/visible.
            'hide_single_group_members' => false,
        ], $params );

        $joins      = [];
        $conditions = [ "p.deleted_at IS NULL" ];
        $args       = [ $lang ]; // First arg is always lang for LEFT JOIN.

        // Category join (single ID).
        if ( ! empty( $params['category_ids'] ) ) {
            $joins[]      = "INNER JOIN {$this->t_cats} pc ON pc.product_id = p.id AND pc.category_id IN (?a)";
            $args[]       = array_map( 'intval', $params['category_ids'] );
        } elseif ( $params['category_id'] > 0 ) {
            $joins[]      = "INNER JOIN {$this->t_cats} pc ON pc.product_id = p.id AND pc.category_id = ?i";
            $args[]       = (int) $params['category_id'];
        }

        // Status filter.
        if ( ! empty( $params['status'] ) ) {
            $conditions[] = "p.status = ?s";
            $args[]       = $params['status'];
        }

        // Type filter.
        if ( ! empty( $params['type'] ) ) {
            $conditions[] = "p.type = ?s";
            $args[]       = $params['type'];
        }

        // SKU exact match.
        if ( ! empty( $params['sku'] ) ) {
            $conditions[] = "p.sku = ?s";
            $args[]       = $params['sku'];
        }

        // Brand.
        if ( $params['brand_id'] > 0 ) {
            $conditions[] = "p.brand_id = ?i";
            $args[]       = (int) $params['brand_id'];
        }

        // Featured.
        if ( $params['featured'] !== null ) {
            $conditions[] = "p.featured = ?i";
            $args[]       = $params['featured'] ? 1 : 0;
        }

        // Stock status.
        if ( ! empty( $params['stock_status'] ) ) {
            $conditions[] = "p.stock_status = ?s";
            $args[]       = $params['stock_status'];
        }

        // Price range.
        if ( $params['price_min'] !== null ) {
            $conditions[] = "p.price >= ?s";
            $args[]       = number_format( (float) $params['price_min'], 4, '.', '' );
        }

        if ( $params['price_max'] !== null ) {
            $conditions[] = "p.price <= ?s";
            $args[]       = number_format( (float) $params['price_max'], 4, '.', '' );
        }

        // Minimum average rating (sidebar "Rating" filter).
        if ( $params['min_rating'] !== null && (float) $params['min_rating'] > 0 ) {
            $conditions[] = "p.rating_avg >= ?s";
            $args[]       = number_format( (float) $params['min_rating'], 2, '.', '' );
        }

        // Products updated within the last N days (sidebar "Date Updated" filter).
        if ( $params['updated_within_days'] !== null && (int) $params['updated_within_days'] > 0 ) {
            $conditions[] = "p.updated_at >= (?s - INTERVAL ?i DAY)";
            $args[]       = titancart_now_utc(); // KI-24: UTC base, not MySQL-tz NOW()
            $args[]       = (int) $params['updated_within_days'];
        }

        // Specific IDs.
        if ( ! empty( $params['ids'] ) ) {
            $conditions[] = "p.id IN (?a)";
            $args[]       = array_map( 'intval', $params['ids'] );
        }

        // On Sale: list_price > 0 AND list_price > price.
        if ( ! empty( $params['on_sale'] ) ) {
            $conditions[] = "(p.list_price > 0 AND p.list_price > p.price)";
        }

        // Free shipping.
        if ( ! empty( $params['free_shipping'] ) ) {
            $conditions[] = "p.free_shipping = 1";
        }

        // Parent ID.
        if ( $params['parent_id'] !== null ) {
            $conditions[] = "p.parent_id = ?i";
            $args[]       = (int) $params['parent_id'];
        }

        // Name search — joins translation table + search_words.
        if ( ! empty( $params['search'] ) ) {
            $conditions[] = "(pt.name LIKE ?s OR p.sku LIKE ?s OR p.search_words LIKE ?s)";
            $args[]       = '%' . $params['search'] . '%';
            $args[]       = '%' . $params['search'] . '%';
            $args[]       = '%' . $params['search'] . '%';
        }

        // roadmap #15: collapse "display as a single product" groups to their
        // representative on storefront listings (grid / archive / search /
        // related / homepage strips). The fragment is a correlated NOT IN with
        // NO bound args, so it applies to both the COUNT and the SELECT (which
        // share $where + $args) without touching $args. Admin listings leave
        // hide_single_group_members false, so every member SKU stays listed.
        if ( ! empty( $params['hide_single_group_members'] ) ) {
            $conditions[] = self::single_group_exclusion_sql( 'p' );
        }

        /**
         * Filter the joins / conditions / args bound to the products list query.
         *
         * Lets extensions extend the products read path without forking the
         * repository. The marketplace plugin subscribes from
         * VendorProductListFilter (S197 — Track B Module B8) to inject an
         * INNER JOIN against tc_vendor_products + condition `v.id = ?i` when
         * $params['vendor_id'] is set — enabling vendor-scoped catalog reads
         * (vendor storefront pages at /vendors/{slug}/ + shop archive
         * ?vendor={slug} facet) via delegation to this method instead of a
         * parallel VendorProductRepository::get_list() with duplicated column
         * logic.
         *
         * Pattern #158 SIXTH instance — query-layer enrichment on products.
         * Twins:
         *   - S185 `titancart_products_list_response` (response-layer)
         *   - S187 `titancart_orders_list_where`      (orders query-layer)
         *   - S190 `titancart_order_item_data`        (order-item write-layer)
         *   - S192 `titancart_stripe_payment_intent_params` (gateway-params layer)
         *   - S193 `titancart_stripe_settings_fields` (settings-fields layer)
         *
         * State-array shape:
         *   - joins:      string[]  SQL fragments (e.g. "INNER JOIN ... ON ...").
         *                           Args inside join ON clauses go in args[].
         *   - conditions: string[]  WHERE clauses (joined with AND).
         *   - args:       array     Positional bound params for ?s / ?i / ?a
         *                           placeholders. Order matches placeholder
         *                           order across the assembled join + where SQL.
         *   - params:     array     Read-only resolved query params (context).
         *   - lang:       string    Read-only language code.
         *
         * Listener contract:
         *   - Return the state array with same shape; joins + conditions + args
         *     must be returned together so they stay in lockstep.
         *   - May only ADD to joins / conditions / args (never remove existing).
         *   - Newly appended args MUST be in the same positional order as
         *     newly appended placeholders across joins + conditions.
         *   - params and lang are read-only context — listeners must not mutate.
         *
         * @since  S197
         * @param  array{joins:array,conditions:array,args:array,params:array,lang:string} $state
         * @return array{joins:array,conditions:array,args:array,params:array,lang:string}
         */
        $state = apply_filters( 'titancart_products_list_query', [
            'joins'      => $joins,
            'conditions' => $conditions,
            'args'       => $args,
            'params'     => $params,
            'lang'       => $lang,
        ] );
        $joins      = ( isset( $state['joins'] )      && is_array( $state['joins'] )      ) ? $state['joins']      : $joins;
        $conditions = ( isset( $state['conditions'] ) && is_array( $state['conditions'] ) ) ? $state['conditions'] : $conditions;
        $args       = ( isset( $state['args'] )       && is_array( $state['args'] )       ) ? $state['args']       : $args;

        $join_sql = implode( ' ', $joins );
        $where    = implode( ' AND ', $conditions );

        // Sort.
        $sort_map  = [
            'name'     => 'pt.name',
            'price'    => 'p.price',
            'position' => 'p.position',
            'date'     => 'p.created_at',
            'sales'    => 'p.sales_count',
            'rating'   => 'p.rating_avg',
            'discount' => '((p.list_price - p.price) / GREATEST(p.list_price, 0.01))',
        ];
        $sort_col  = $sort_map[ $params['sort_by'] ] ?? 'p.created_at';
        $sort_dir  = strtoupper( $params['sort_order'] ) === 'ASC' ? 'ASC' : 'DESC';

        // Count total matching rows.
        $count = (int) $this->db->getField(
            "SELECT COUNT(DISTINCT p.id)
             FROM {$this->t_products} p
             LEFT JOIN {$this->t_trans} pt ON pt.product_id = p.id AND pt.language_code = ?s
             {$join_sql}
             WHERE {$where}",
            ...$args
        );

        // Pagination.
        $limit_sql = '';
        if ( (int) $params['items_per_page'] > 0 ) {
            $offset    = ( max( 1, (int) $params['page'] ) - 1 ) * (int) $params['items_per_page'];
            $limit_sql = "LIMIT {$params['items_per_page']} OFFSET {$offset}";
        }

        $products = $this->db->getArray(
            "SELECT DISTINCT p.id, p.sku, p.slug, p.price, p.list_price, p.stock_quantity,
                    p.stock_status, p.type, p.status, p.featured, p.rating_avg,
                    p.rating_count, p.sales_count, p.created_at, p.updated_at,
                    p.weight, p.weight_unit, p.free_shipping, p.brand_id,
                    pt.name, pt.short_desc
             FROM {$this->t_products} p
             LEFT JOIN {$this->t_trans} pt ON pt.product_id = p.id AND pt.language_code = ?s
             {$join_sql}
             WHERE {$where}
             ORDER BY {$sort_col} {$sort_dir}
             {$limit_sql}",
            ...$args
        );

        // Attach main image to each product.
        if ( ! empty( $products ) ) {
            $ids    = array_column( $products, 'id' );
            $images = $this->db->getArray(
                "SELECT product_id, wp_attachment_id, alt_text
                 FROM {$this->t_images}
                 WHERE product_id IN (?a) AND is_main = 1",
                $ids
            );

            $image_map = [];
            foreach ( $images as $img ) {
                $image_map[ $img['product_id'] ] = $img;
            }

            foreach ( $products as &$prod ) {
                $pid         = $prod['id'];
                $img         = $image_map[ $pid ] ?? null;
                $prod['image'] = $img
                    ? [
                        'url'      => wp_get_attachment_image_url( $img['wp_attachment_id'], 'medium_large' ) ?: wp_get_attachment_image_url( $img['wp_attachment_id'], 'medium' ),
                        'thumb'    => wp_get_attachment_image_url( $img['wp_attachment_id'], 'medium' ),
                        'alt'      => $img['alt_text'] ?? '',
                    ]
                    : null;
            }
            unset( $prod );
        }

        $params['total_items'] = $count;
        $params['total_pages'] = $params['items_per_page'] > 0
            ? (int) ceil( $count / $params['items_per_page'] )
            : 1;

        // Brand line: resolve each product's brand from a `purpose='brand'`
        // attribute (the way brands are set up in this store) into a display
        // string, batched in one query. Independent of the attribute's
        // filter_type, so a Brand attribute that is NOT a filter chip still
        // surfaces on the card. Stores with no brand attribute get '' (no line).
        // Single source of truth for every card surface — REST + storefront
        // archive both flow through get_list(); the theme homepage strips call
        // the same static helper directly.
        $products = self::attach_brand_to_rows( $products, $lang );

        // roadmap #15: for storefront listings that collapse single-display
        // groups, override the representative row's name with the group's clean
        // display name (or a best-effort de-suffixed member name). Price needs
        // no enrichment — the representative row IS the returned row, so its own
        // price (the lowest-position member, e.g. size S) already shows.
        if ( ! empty( $params['hide_single_group_members'] ) ) {
            $products = self::attach_group_single_display( $products );
        }

        /**
         * Filter the products array on the list read path.
         *
         * Single fire site for ALL list consumers — REST endpoint
         * (ProductsController::get_items), storefront archive
         * (StorefrontModule::route shop / category / tag / search /
         * homepage strips), and any future internal caller of get_list().
         * Lets extensions enrich the rows before they reach consumer
         * surfaces — used by the marketplace extension (S197 — Track B
         * Module B8) to attach a `vendor` sub-array per product when an
         * active vendor↔product junction exists. Extensions must preserve
         * the existing keys and the row order; they may only add new keys.
         *
         * Pattern #158 first-instance migration: this filter originated at
         * S185 inside ProductsController::get_items as a REST-layer hook.
         * S197 moves the fire site INTO get_list itself so the storefront
         * archive (which bypasses REST and calls get_list directly) also
         * gets the enrichment without scattering apply_filters across every
         * StorefrontModule::route case. Single source of truth; idempotency
         * preserved (listeners SHOULD short-circuit if their enrichment key
         * is already present, but the migration eliminates the only known
         * double-fire risk by removing the duplicate REST-layer call).
         *
         * @param array  $products Array of product rows (each is an assoc array).
         * @param array  $params   The resolved query params used for this list.
         * @param string $lang     Language code resolved from the request.
         * @since S185 (originally at ProductsController::get_items)
         * @since S197 (migrated to single fire site inside get_list)
         */
        $products = apply_filters( 'titancart_products_list_response', $products, $params, $lang );

        return [ 'products' => $products, 'params' => $params ];
    }

    /**
     * Attach a resolved brand string to each product row.
     *
     * Brands in this store are modelled as an attribute (a `tc_product_features`
     * row with purpose='brand'); the chosen value lives in
     * `tc_product_feature_values` — as a variant (Select-type attribute, name in
     * the variant translations) or as free text (value_str / value_text). This
     * resolves that to a single display string per product and writes it to
     * $row['brand'] ('' when the product has no brand attribute value).
     *
     * Batched (one query for the whole row set; no N+1). filter_type is NOT
     * considered, so a Brand attribute that is not also a filter chip still
     * surfaces. Static so the theme's homepage strips (which build rows without
     * going through get_list) can reuse the exact same resolution.
     *
     * @param array  $products Product rows (each an assoc array with an 'id').
     * @param string $lang     Language code for the variant-name translation.
     * @return array The same rows, each with a 'brand' string key added.
     */
    public static function attach_brand_to_rows( array $products, string $lang = 'en' ): array {
        if ( empty( $products ) ) {
            return $products;
        }

        $ids = array_values( array_filter( array_map(
            static function ( $p ) { return (int) ( $p['id'] ?? 0 ); },
            $products
        ) ) );
        if ( empty( $ids ) ) {
            return $products;
        }

        $db      = titancart_db();
        $t_vals  = $db->table( 'product_feature_values' );
        $t_feat  = $db->table( 'product_features' );
        $t_vtrn  = $db->table( 'product_feature_variant_translations' );

        // First active brand-purpose attribute value per product wins
        // (ordered by feature position, then id, for deterministic output).
        $rows = $db->getArray(
            "SELECT fv.product_id AS product_id,
                    COALESCE(vt.name, fv.value_str, fv.value_text) AS brand_value
             FROM {$t_vals} fv
             INNER JOIN {$t_feat} f
                     ON f.id = fv.feature_id
                    AND f.purpose = 'brand'
                    AND f.status = 'active'
             LEFT JOIN {$t_vtrn} vt
                    ON vt.variant_id = fv.variant_id
                   AND vt.language_code = ?s
             WHERE fv.product_id IN (?a)
             ORDER BY f.position ASC, f.id ASC",
            $lang,
            $ids
        );

        $brand_map = [];
        foreach ( $rows as $r ) {
            $pid = (int) $r['product_id'];
            // First non-empty value for a product wins; later rows are ignored.
            if ( ! isset( $brand_map[ $pid ] ) ) {
                $val = trim( (string) ( $r['brand_value'] ?? '' ) );
                if ( '' !== $val ) {
                    $brand_map[ $pid ] = $val;
                }
            }
        }

        foreach ( $products as &$p ) {
            $pid         = (int) ( $p['id'] ?? 0 );
            $p['brand']  = $brand_map[ $pid ] ?? '';
        }
        unset( $p );

        return $products;
    }

    /**
     * WHERE fragment that excludes non-representative members of
     * "display as a single product" groups (roadmap #15) from a LISTING query.
     *
     * The outer product table MUST be aliased $alias (default 'p'). The fragment
     * is a correlated NOT IN with NO bound parameters, so it can be dropped into
     * any assembled WHERE (get_list, the typeahead query, the sitemap query)
     * without disturbing positional args.
     *
     * Representative = the group member with the lowest `position`, ties broken
     * by the lowest `product_id` (ORDER BY position ASC, product_id ASC LIMIT 1).
     * No window functions, so it runs identically on MySQL 5.7 and 8.0. Members
     * of groups with display_as_single = 0, and all non-grouped products, are
     * never excluded.
     *
     * @param  string $alias Outer product-table alias.
     * @return string        A ready-to-AND WHERE fragment.
     */
    public static function single_group_exclusion_sql( string $alias = 'p' ): string {
        $db = titancart_db();
        $gp = $db->table( 'product_group_products' );
        $g  = $db->table( 'product_groups' );

        return "{$alias}.id NOT IN (
            SELECT sgp.product_id
            FROM {$gp} sgp
            INNER JOIN {$g} sg
                    ON sg.id = sgp.group_id
                   AND sg.display_as_single = 1
            WHERE sgp.product_id <> (
                SELECT rgp.product_id
                FROM {$gp} rgp
                WHERE rgp.group_id = sgp.group_id
                ORDER BY rgp.position ASC, rgp.product_id ASC
                LIMIT 1
            )
        )";
    }

    /**
     * Override the representative row's display name for single-display groups
     * (roadmap #15). Only the representative of each display_as_single group can
     * reach this method's input (non-representatives were excluded upstream by
     * single_group_exclusion_sql), so any returned id found in a single-display
     * group is that group's representative.
     *
     * Name resolution: the group's `display_name` if set; otherwise a
     * best-effort de-suffix of the member's own name. Price is not changed — the
     * representative row already carries the lowest-position member price (e.g.
     * size S). Also tags the row with is_group_single = true for any surface
     * that wants to badge it. Batched: one query for the whole page (no N+1).
     *
     * @param  array $products Product rows (each an assoc array with 'id','name').
     * @return array           The same rows with representative names overridden.
     */
    public static function attach_group_single_display( array $products ): array {
        if ( empty( $products ) ) {
            return $products;
        }

        $ids = array_values( array_filter( array_map(
            static function ( $p ) { return (int) ( $p['id'] ?? 0 ); },
            $products
        ) ) );
        if ( empty( $ids ) ) {
            return $products;
        }

        $db = titancart_db();
        $gp = $db->table( 'product_group_products' );
        $g  = $db->table( 'product_groups' );

        // product_id => display_name for the single-display representatives on
        // this page. (Only representatives survive the listing exclusion, so a
        // returned id present here is a representative.)
        $rows = $db->getArray(
            "SELECT sgp.product_id AS product_id, sg.display_name AS display_name
             FROM {$gp} sgp
             INNER JOIN {$g} sg
                     ON sg.id = sgp.group_id
                    AND sg.display_as_single = 1
             WHERE sgp.product_id IN (?a)",
            $ids
        );
        if ( empty( $rows ) ) {
            return $products;
        }

        $name_map = [];
        foreach ( $rows as $r ) {
            $name_map[ (int) $r['product_id'] ] = (string) ( $r['display_name'] ?? '' );
        }

        foreach ( $products as &$p ) {
            $pid = (int) ( $p['id'] ?? 0 );
            if ( ! array_key_exists( $pid, $name_map ) ) {
                continue;
            }
            $p['is_group_single'] = true;
            $custom = trim( $name_map[ $pid ] );
            if ( '' !== $custom ) {
                $p['name'] = $custom;
                continue;
            }
            // Fallback: de-suffix the representative's own name.
            $p['name'] = self::strip_variant_suffix( (string) ( $p['name'] ?? '' ) );
        }
        unset( $p );

        return $products;
    }

    /**
     * Best-effort removal of a trailing variant tag from a product name, used as
     * the fallback group card name when no display_name is set (roadmap #15).
     * Strips a single trailing " - <tag>", " \u2013 <tag>", " \u2014 <tag>", or
     * " (<tag>)" where <tag> is a short token (<= 8 non-space chars) — e.g.
     * "Tee - 3XL" -> "Tee", "Mug (11oz)" -> "Mug". Conservative: a hyphen with
     * no surrounding spaces ("T-Shirt") and longer / multi-word trailing phrases
     * are left intact. Returns the original string when nothing matches or the
     * result would be empty.
     *
     * @param  string $name
     * @return string
     */
    private static function strip_variant_suffix( string $name ): string {
        $stripped = preg_replace( '/\s+[\-\x{2013}\x{2014}]\s*\S{1,8}$/u', '', $name );
        if ( ! is_string( $stripped ) || $stripped === $name ) {
            $paren = preg_replace( '/\s+\(\s*\S{1,8}\s*\)$/u', '', $name );
            if ( is_string( $paren ) ) {
                $stripped = $paren;
            }
        }
        if ( is_string( $stripped ) ) {
            $stripped = trim( $stripped );
            if ( '' !== $stripped ) {
                return $stripped;
            }
        }
        return $name;
    }

    // ══════════════════════════════════════════════════════════════════════════
    // WRITE
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Create or update a product and its full data graph.
     *
     * Accepts a $data array with optional sub-keys:
     *   'translation'  array   Name, description, meta fields
     *   'prices'       array[] Quantity/usergroup price rows
     *   'categories'   int[]   Category IDs (first = main)
     *   'images'       array[] [ wp_attachment_id, position, is_main, alt_text ]
     *   'options'      array[] Option definitions
     *   'features'     array[] Feature value assignments
     *
     * @param  array<string,mixed> $data
     * @param  int                 $product_id  0 = create new.
     * @param  string              $lang
     * @return int Product ID.
     */
    public function save( array $data, int $product_id = 0, string $lang = 'en' ): int {
        // Extract sub-data before saving main row.
        $translation = $data['translation'] ?? [];
        $prices      = $data['prices']      ?? null;
        $categories  = $data['categories']  ?? null;
        $images      = $data['images']      ?? null;
        $options     = $data['options']     ?? null;
        $features    = $data['features']    ?? null;

        unset(
            $data['translation'], $data['prices'],
            $data['categories'],  $data['images'],
            $data['options'],     $data['features']
        );

        // Generate or de-duplicate the slug. A slug supplied by the caller (the admin
        // pre-fills it from the name) is still run through unique_slug() so two products
        // — e.g. same-named variations — can never share one /product/{slug}/ URL, which
        // would make all but one of them unreachable on the storefront.
        //
        // [KI-55] On UPDATE, distinguish an ABSENT slug from an explicitly-empty one.
        // sanitize_product_input() (ProductsController L2120) only sets 'slug' in $data
        // when the caller actually sent the param, so array_key_exists() tells "omitted"
        // from "sent empty". A partial PUT that changes the name but omits slug must NOT
        // silently regenerate — and therefore move — the product's public URL; an
        // omitted slug on update is left untouched (the column falls out of $row below,
        // so the UPDATE never writes it and no 301 is created). An explicitly-empty
        // slug ('') still means "regenerate from the name". On CREATE (product_id 0) an
        // omitted slug regenerates from the name exactly as before.
        if ( $product_id > 0 && ! array_key_exists( 'slug', $data ) ) {
            unset( $data['slug'] );
        } elseif ( empty( $data['slug'] ) ) {
            if ( ! empty( $translation['name'] ) ) {
                $data['slug'] = $this->unique_slug(
                    sanitize_title( $translation['name'] ),
                    $product_id
                );
            } elseif ( $product_id > 0 ) {
                // [KI-76] An explicitly-empty slug ('') means "regenerate from the
                // name", but this payload carries no translation.name. Never write
                // an empty slug — that would give the product a broken/empty public
                // URL. Fall back to the product's STORED name (the current
                // authoritative name), exactly as the with-name branch above would
                // have done had the name been resent; run it through unique_slug()
                // with $product_id excluded so a no-op re-save keeps the same slug.
                // If there is no stored name to regenerate from, keep the existing
                // slug untouched by unsetting the key so the column falls out of
                // $row below and the UPDATE never writes it (never empties a live URL).
                $stored_name = (string) $this->db->getField(
                    "SELECT name FROM {$this->t_trans}
                     WHERE product_id = ?i AND language_code = ?s",
                    $product_id,
                    $lang
                );
                $fallback = $stored_name !== '' ? sanitize_title( $stored_name ) : '';
                if ( $fallback !== '' ) {
                    $data['slug'] = $this->unique_slug( $fallback, $product_id );
                } else {
                    unset( $data['slug'] );
                }
            }
            // On CREATE (product_id === 0) with an empty slug and no name there is
            // nothing to regenerate from; leave $data['slug'] as '' (pre-existing
            // behavior — a create always carries a name in practice).
        } else {
            $data['slug'] = $this->unique_slug(
                sanitize_title( $data['slug'] ),
                $product_id
            );
        }

        // Core product row fields.
        $allowed_fields = [
            'parent_id', 'brand_id', 'sku', 'slug', 'price', 'list_price', 'cost_price',
            'weight', 'weight_unit', 'length', 'width', 'height', 'dimension_unit',
            'stock_quantity', 'stock_status', 'track_inventory', 'allow_backorders',
            'min_quantity', 'max_quantity', 'quantity_step',
            'tax_class_id', 'shipping_freight', 'free_shipping',
            'age_restricted', 'age_limit',
            'is_downloadable', 'requires_shipping', 'type',
            // S129 Phase 1 — Downloadable Products per-product overrides.
            // NULL = inherit global setting (catalog_download_key_ttl) /
            // unlimited downloads; <=0 ttl = never expires.
            'download_ttl_hours', 'download_max_count',
            'subscriptions_only',
            'status', 'visibility', 'featured', 'position',
            'details_view', 'search_words', 'promo_text', 'videos',
            // S120 — schema-gap fixes (matrix Tier-3): explicit columns now exist.
            'zero_price_action', 'out_of_stock_actions',
            'options_type', 'exceptions_type', 'avail_since',
            'unit_name', 'units_in_product', 'show_price_per',
            // S124: rp_* per-product reward overrides removed from the write
            // allow-list — group-keyed earning rates (Loyalty Rewards admin →
            // Earning Rates tab) replace them. Schema columns still exist on
            // tc_products and are returned by SELECT * for back-compat, but
            // nothing reads or writes them at runtime.
        ];
        $row = array_intersect_key( $data, array_flip( $allowed_fields ) );

        // Auto-sync stock_status from stock_quantity when not explicitly provided.
        if ( ! isset( $row['stock_status'] ) && isset( $row['stock_quantity'] ) ) {
            $row['stock_status'] = ( (int) $row['stock_quantity'] > 0 ) ? 'in_stock' : 'out_of_stock';
        }

        $is_new = ( $product_id === 0 );

        // Capture old slug before update (for auto SEO redirect).
        $old_slug = '';
        if ( ! $is_new && isset( $row['slug'] ) ) {
            $old_slug = (string) $this->db->getField(
                "SELECT slug FROM {$this->t_products} WHERE id = ?i",
                $product_id
            );
        }

        // S120: Capture old base price so we can detect a base-price change and refresh
        // the percent-discount cache on tc_product_prices afterwards. Only relevant when
        // updating (not new) and the payload contains a `price` change.
        $old_price = null;
        if ( ! $is_new && isset( $row['price'] ) ) {
            $old_price = (float) $this->db->getField(
                "SELECT price FROM {$this->t_products} WHERE id = ?i",
                $product_id
            );
        }

        if ( $is_new ) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                titancart_log( '[TitanCart] repo->save() inserting row: ' . wp_json_encode( $row ) );
            }
            $product_id = $this->db->insert( 'products', $row );
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                titancart_log( '[TitanCart] repo->save() insert result product_id=' . $product_id . ' db_error=' . $this->db->lastError() );
            }
        } else {
            // S183: short-circuit when $row is empty. Every allow-listed column
            // can legitimately be filtered out — for instance the marketplace's
            // VendorSelfController PUT path nulls `status` (S183 defense-in-depth)
            // AND drops admin-only fields via the controller/service/repo whitelist
            // chain, leaving $data potentially empty when a vendor submits only
            // admin-restricted or non-row fields. Without this guard, $wpdb->update
            // with empty $data emits `UPDATE wp_tc_products SET WHERE id = N` —
            // a MySQL syntax error that surfaced in D4 smoke at S183.
            //
            // Sub-data (translation / prices / categories / images / options /
            // features) still processes below — those are independent writes
            // against their own tables and the vendor PUT may have legitimately
            // changed e.g. only `name` (translation table, not tc_products).
            if ( ! empty( $row ) ) {
                $this->db->update( 'products', $row, [ 'id' => $product_id ] );

                // Auto-create 301 redirect if slug changed.
                if ( $old_slug !== '' && isset( $row['slug'] ) && $row['slug'] !== $old_slug ) {
                    $pfx = \TitanCart\Services\SeoService::get_url_patterns()['product_prefix'];
                    $base = $pfx !== '' ? '/' . $pfx : '';
                    $this->create_seo_redirect(
                        $base . '/' . $old_slug . '/',
                        $base . '/' . $row['slug'] . '/'
                    );
                }
            }
        }

        if ( ! $product_id ) {
            return 0;
        }

        // ── Auto-generate Product Code (SKU) when blank ──────────────────────
        // When the admin enables auto-generation (Settings → General → Product
        // Code Generation) and a product is saved with no code, derive one from
        // the configured source and guarantee uniqueness across tc_products.sku.
        // Runs after the row is persisted so the "Product ID" source has a real
        // id to use. Products that already carry a code never reach this branch.
        $incoming_sku = isset( $data['sku'] ) ? trim( (string) $data['sku'] ) : '';
        if ( $incoming_sku === ''
            && \TitanCart\Services\SettingsService::bool( 'general', 'product_code_autogenerate', false ) ) {
            $generated = $this->generate_product_code( $product_id, $translation, $categories, $lang );
            if ( $generated !== '' ) {
                $this->db->update( 'products', [ 'sku' => $generated ], [ 'id' => $product_id ] );
            }
        }

        // Translation.
        if ( ! empty( $translation ) ) {
            $this->save_translation( $product_id, $translation, $lang );
        }

        // Prices.
        if ( $prices !== null ) {
            $this->save_prices( $product_id, $prices );
        } elseif ( $is_new && isset( $row['price'] ) ) {
            // Auto-create a default base price row.
            $this->save_prices( $product_id, [[
                'usergroup_id'   => 0,
                'min_quantity'   => 1,
                'discount_type'  => 'absolute',
                'discount_value' => $row['price'],
            ]] );
        } elseif ( ! $is_new && $old_price !== null && isset( $row['price'] ) && (float) $row['price'] !== $old_price ) {
            // S120: Base price changed AND no fresh prices array was sent —
            // refresh the cached `price` on any percent-discount rows so they
            // reflect the new base. Absolute rows are intentionally untouched.
            $this->recompute_percent_price_cache( $product_id, (float) $row['price'] );
        }

        // Categories.
        if ( $categories !== null ) {
            $this->save_categories( $product_id, $categories );
        }

        // Images.
        if ( $images !== null ) {
            $this->save_images( $product_id, $images );
        }

        // Options.
        if ( $options !== null ) {
            $this->save_options( $product_id, $options, $lang );
        }

        // Features.
        if ( $features !== null ) {
            $this->save_feature_values( $product_id, $features );
        }

        do_action( 'titancart_product_saved', $product_id, $data, $is_new );

        return $product_id;
    }

    /**
     * Soft-delete a product (and its variations if it is a parent).
     *
     * @param  int $product_id
     * @return bool
     */
    public function delete( int $product_id ): bool {
        // Soft-delete any child variations.
        $child_ids = $this->db->getColumn(
            "SELECT child_id FROM {$this->t_variation_links} WHERE parent_id = ?i",
            $product_id
        );

        if ( ! empty( $child_ids ) ) {
            $this->db->query(
                "UPDATE {$this->t_products}
                 SET deleted_at = ?s
                 WHERE id IN (?a)",
                titancart_now_utc(), // KI-24: UTC, not MySQL-tz NOW()
                array_map( 'intval', $child_ids )
            );
        }

        $this->db->query(
            "UPDATE {$this->t_products}
             SET deleted_at = ?s
             WHERE id = ?i",
            titancart_now_utc(), // KI-24: UTC, not MySQL-tz NOW()
            $product_id
        );

        do_action( 'titancart_product_deleted', $product_id );
        return true;
    }

    /**
     * Update a product's stock quantity (atomic delta).
     *
     * @param int $product_id
     * @param int $delta       Positive = add, negative = reduce.
     * @return int New quantity.
     */
    public function update_stock( int $product_id, int $delta ): int {
        $this->db->query(
            "UPDATE {$this->t_products}
             SET stock_quantity = GREATEST(0, stock_quantity + ?i),
                 stock_status   = CASE
                     WHEN GREATEST(0, stock_quantity + ?i) > 0 THEN 'in_stock'
                     ELSE 'out_of_stock'
                 END
             WHERE id = ?i AND track_inventory = 1",
            $delta,
            $delta,
            $product_id
        );

        $new_qty = (int) $this->db->getField(
            "SELECT stock_quantity FROM {$this->t_products} WHERE id = ?i",
            $product_id
        );

        // Low stock threshold warning.
        $threshold = SettingsService::int( 'general', 'catalog_low_stock_threshold', 0 );
        if ( $threshold > 0 && $new_qty > 0 && $new_qty <= $threshold ) {
            LogService::warning( 'products', 'low_stock', sprintf(
                'Product #%d stock is low: %d remaining (threshold: %d)',
                $product_id, $new_qty, $threshold
            ), [
                'object_type' => 'product',
                'object_id'   => $product_id,
            ] );

            // KI-27: fire the webhook source event ONLY on the transition INTO
            // low stock (previous qty was above threshold), not on every further
            // decrement while already low. prev = $new_qty - $delta is exact here
            // because $new_qty > 0 guarantees GREATEST(0, …) did not clamp.
            $prev_qty = $new_qty - $delta;
            if ( $prev_qty > $threshold ) {
                do_action( 'titancart_product_low_stock', $product_id, $new_qty );
            }
        }

        return $new_qty;
    }

    /**
     * Increment views counter for a product.
     *
     * @param int $product_id
     */
    public function increment_views( int $product_id ): void {
        $this->db->query(
            "UPDATE {$this->t_products}
             SET views_count = views_count + 1
             WHERE id = ?i",
            $product_id
        );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // PRICES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get all price rows for a product.
     *
     * @param  int $product_id
     * @return array<int, array<string,mixed>>
     */
    public function get_prices( int $product_id ): array {
        return $this->db->getArray(
            "SELECT id, usergroup_id, min_quantity, price, discount_type, discount_value
             FROM {$this->t_prices}
             WHERE product_id = ?i
             ORDER BY usergroup_id ASC, min_quantity ASC",
            $product_id
        );
    }

    /**
     * Get the applicable price for a customer/quantity combination.
     *
     * @param  int      $product_id
     * @param  int      $quantity
     * @param  int[]    $usergroup_ids  Customer's usergroup IDs.
     * @return float
     */
    public function get_price( int $product_id, int $quantity = 1, array $usergroup_ids = [] ): float {
        // Collect applicable usergroup IDs (always include 0 = all customers).
        $groups   = array_unique( array_merge( [0], array_map( 'intval', $usergroup_ids ) ) );

        $price = $this->db->getField(
            "SELECT price FROM {$this->t_prices}
             WHERE product_id = ?i
               AND usergroup_id IN (?a)
               AND min_quantity <= ?i
             ORDER BY price ASC
             LIMIT 1",
            $product_id,
            $groups,
            $quantity
        );

        if ( $price !== null ) {
            return (float) $price;
        }

        // Fall back to base product price.
        return (float) $this->db->getField(
            "SELECT price FROM {$this->t_products} WHERE id = ?i",
            $product_id
        );
    }

    /**
     * Replace all price rows for a product.
     *
     * Each row carries:
     *   - usergroup_id, min_quantity (filter keys)
     *   - discount_type ('absolute' | 'percent') + discount_value (the input value)
     *   - price (cached effective unit price, derived from the above + base price)
     *
     * The `price` column exists for two reasons:
     *   1. Backward compatibility — pre-S120 callers (e.g. `get_price()`) read it directly.
     *   2. Performance — the cart consumer can pick the lowest matching unit price without
     *      JOINing back to tc_products to get the base price for percent calculations.
     *
     * For the absolute case: price === discount_value.
     * For the percent case:  price === base_price * (1 - discount_value / 100).
     *
     * If the product's base price changes, the percent rows go stale until save_prices()
     * runs again — Plugin's product save() detects base-price changes and re-saves prices
     * to keep the cache fresh (see save() flow).
     *
     * @param int     $product_id
     * @param array[] $prices  Each: { usergroup_id, min_quantity, discount_type, discount_value }
     *                         OR legacy { usergroup_id, min_quantity, price } from a stale caller.
     */
    private function save_prices( int $product_id, array $prices ): void {
        // Resolve the product's base price once — needed for percent → absolute conversion.
        $base_price = (float) $this->db->getField(
            "SELECT price FROM {$this->t_products} WHERE id = ?i",
            $product_id
        );

        $this->db->delete( 'product_prices', [ 'product_id' => $product_id ] );

        foreach ( $prices as $p ) {
            $type = $p['discount_type'] ?? 'absolute';
            if ( ! in_array( $type, [ 'absolute', 'percent' ], true ) ) {
                $type = 'absolute';
            }

            // Pull the input value: prefer discount_value, fall back to legacy `price`.
            $value = isset( $p['discount_value'] )
                ? (float) $p['discount_value']
                : (float) ( $p['price'] ?? 0 );

            // Compute the cached effective unit price.
            if ( $type === 'percent' ) {
                $pct = max( 0.0, min( 100.0, $value ) );
                $effective = max( 0.0, $base_price * ( 1.0 - ( $pct / 100.0 ) ) );
            } else {
                $effective = max( 0.0, $value );
            }

            $this->db->insert( 'product_prices', [
                'product_id'     => $product_id,
                'usergroup_id'   => (int) ( $p['usergroup_id'] ?? 0 ),
                'min_quantity'   => max( 1, (int) ( $p['min_quantity'] ?? 1 ) ),
                'price'          => number_format( $effective, 4, '.', '' ),
                'discount_type'  => $type,
                'discount_value' => number_format( max( 0.0, $value ), 4, '.', '' ),
            ] );
        }
    }

    /**
     * Recompute cached `price` on existing percent-discount rows after the product's
     * base price changes. Called from save() when `data['price']` is in the payload
     * but no fresh `prices` array was provided (so save_prices() didn't already run).
     *
     * Only touches rows where discount_type='percent' — absolute rows hold a fixed
     * customer-facing price that's intentionally independent of the base.
     *
     * @param int   $product_id
     * @param float $new_base_price
     */
    private function recompute_percent_price_cache( int $product_id, float $new_base_price ): void {
        $rows = $this->db->getArray(
            "SELECT id, discount_value
             FROM {$this->t_prices}
             WHERE product_id = ?i AND discount_type = 'percent'",
            $product_id
        );
        foreach ( $rows as $r ) {
            $pct       = max( 0.0, min( 100.0, (float) $r['discount_value'] ) );
            $effective = max( 0.0, $new_base_price * ( 1.0 - ( $pct / 100.0 ) ) );
            $this->db->update( 'product_prices',
                [ 'price' => number_format( $effective, 4, '.', '' ) ],
                [ 'id' => (int) $r['id'] ]
            );
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    // CATEGORIES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get category IDs for a product.
     *
     * @param  int $product_id
     * @return array{ id:int, is_main:int, position:int }[]
     */
    public function get_category_ids( int $product_id ): array {
        // Return a flat array of integer IDs (what the REST API and JS expect).
        return $this->db->getColumn(
            "SELECT category_id
             FROM {$this->t_cats}
             WHERE product_id = ?i
             ORDER BY is_main DESC, position ASC",
            $product_id
        );
    }

    /**
     * Replace all category assignments for a product.
     * First entry in $category_ids is treated as the main category.
     *
     * @param int   $product_id
     * @param int[] $category_ids
     */
    private function save_categories( int $product_id, array $category_ids ): void {
        $this->db->delete( 'product_categories', [ 'product_id' => $product_id ] );

        foreach ( $category_ids as $pos => $cat_id ) {
            $this->db->insert( 'product_categories', [
                'product_id'  => $product_id,
                'category_id' => (int) $cat_id,
                'is_main'     => $pos === 0 ? 1 : 0,
                'position'    => $pos,
            ] );
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    // IMAGES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get all images for a product, formatted with WP attachment URLs.
     *
     * @param  int $product_id
     * @return array<int, array<string,mixed>>
     */
    public function get_images( int $product_id ): array {
        $rows = $this->db->getArray(
            "SELECT id, wp_attachment_id, position, is_main, alt_text
             FROM {$this->t_images}
             WHERE product_id = ?i
             ORDER BY is_main DESC, position ASC",
            $product_id
        );

        foreach ( $rows as &$row ) {
            $attachment_id    = (int) $row['wp_attachment_id'];
            $row['url']       = wp_get_attachment_image_url( $attachment_id, 'full' ) ?: '';
            $row['thumb']     = wp_get_attachment_image_url( $attachment_id, 'medium' ) ?: '';
            $row['thumbnail'] = wp_get_attachment_image_url( $attachment_id, ImageService::cart_thumb_size() ) ?: '';
            $row['srcset']    = wp_get_attachment_image_srcset( $attachment_id ) ?: '';
        }
        unset( $row );

        return $rows;
    }

    /**
     * Replace all image records for a product.
     *
     * @param int    $product_id
     * @param array[] $images  Each: { wp_attachment_id, position, is_main, alt_text }
     */
    private function save_images( int $product_id, array $images ): void {
        $this->db->delete( 'product_images', [ 'product_id' => $product_id ] );

        foreach ( $images as $pos => $image ) {
            $this->db->insert( 'product_images', [
                'product_id'       => $product_id,
                'wp_attachment_id' => (int) ( $image['wp_attachment_id'] ?? 0 ),
                'position'         => (int) ( $image['position'] ?? $pos ),
                'is_main'          => $pos === 0 ? 1 : (int) ( $image['is_main'] ?? 0 ),
                'alt_text'         => sanitize_text_field( $image['alt_text'] ?? '' ),
            ] );
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    // OPTIONS
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get all options with their variants for a product.
     *
     * S140 — also hydrates each option with `compound_pricing` (TINYINT) and
     * `dependencies` (array of `{parent_option_id, parent_variant_id}` rows).
     * An empty `dependencies` array means the option is a flat option (always
     * visible), which is the default for every option not yet edited under the
     * sequential-options UI. Frontend gates "unlock" by grouping deps by
     * parent_option_id (OR within same parent, AND across parents).
     *
     * @param  int    $product_id
     * @param  string $lang
     * @return array<int, array<string,mixed>>
     */
    public function get_options( int $product_id, string $lang = 'en' ): array {
        $options = $this->db->getArray(
            "SELECT o.id, o.type, o.position, o.required, o.compound_pricing, o.status,
                    ot.name, ot.comment
             FROM {$this->t_options} o
             LEFT JOIN {$this->t_option_trans} ot
                ON ot.option_id = o.id AND ot.language_code = ?s
             WHERE o.product_id = ?i
             ORDER BY o.position ASC",
            $lang,
            $product_id
        );

        if ( empty( $options ) ) {
            return [];
        }

        // Build per-option variants list (existing behavior).
        foreach ( $options as &$option ) {
            $option['variants'] = $this->db->getArray(
                "SELECT v.id, v.position, v.modifier, v.modifier_type,
                        v.weight_modifier, v.image_id, v.status,
                        vt.name
                 FROM {$this->t_option_variants} v
                 LEFT JOIN {$this->t_variant_trans} vt
                    ON vt.variant_id = v.id AND vt.language_code = ?s
                 WHERE v.option_id = ?i
                 ORDER BY v.position ASC",
                $lang,
                $option['id']
            );
            // Default dependencies to empty so callers can rely on the key.
            $option['dependencies']     = [];
            $option['compound_pricing'] = (int) ( $option['compound_pricing'] ?? 0 );
        }
        unset( $option );

        // S140 — batch-fetch all dependency edges for these options in one
        // query, then attach to each option. Cheap because edge count is
        // bounded by the option count and most products have zero deps.
        if ( empty( $options ) ) {
            return $options;
        }
        $option_ids = array_map( 'intval', array_column( $options, 'id' ) );
        $dep_rows   = $this->db->getArray(
            "SELECT option_id, parent_option_id, parent_variant_id
             FROM {$this->t_option_deps}
             WHERE option_id IN (?a)",
            $option_ids
        );
        if ( $dep_rows ) {
            // Group by option_id.
            $grouped = [];
            foreach ( $dep_rows as $row ) {
                $oid = (int) $row['option_id'];
                if ( ! isset( $grouped[ $oid ] ) ) {
                    $grouped[ $oid ] = [];
                }
                $grouped[ $oid ][] = [
                    'parent_option_id'  => (int) $row['parent_option_id'],
                    'parent_variant_id' => (int) $row['parent_variant_id'],
                ];
            }
            foreach ( $options as &$option ) {
                $oid = (int) $option['id'];
                if ( isset( $grouped[ $oid ] ) ) {
                    $option['dependencies'] = $grouped[ $oid ];
                }
            }
            unset( $option );
        }

        return $options;
    }

    /**
     * Sort options for storefront rendering: linked options stay grouped, with
     * parents above their dependents inside each group.
     *
     * S141 — companion to the Sequential Options PDP rendering. Two UX
     * properties matter for the customer-facing flow:
     *
     *   1. PARENT-BEFORE-DEPENDENT — the lock hint *"Choose 'X' first..."*
     *      must point at an option the customer can already see and interact
     *      with above the locked one.
     *   2. KEEP-LINKED-OPTIONS-TOGETHER — an unrelated option must not appear
     *      between two linked options. Sean's case: `[Gift wrapping → Engraving
     *      → Wrapping Paper Design]` (positions 0/1/2, WPD depends on Gift)
     *      breaks the chain visually — customer reads "pick gift wrap, fill in
     *      engraving (wait, why?), pick wrapping design (linked to what?)".
     *      Solution: `[Gift wrapping → Wrapping Paper Design → Engraving]` —
     *      Engraving moves to its own slot AFTER the linked group.
     *
     * Algorithm:
     *   1. Build undirected adjacency from the dep graph (parent ↔ child) and
     *      find connected components. A standalone option becomes a component
     *      of size 1.
     *   2. For each component, compute the lowest (`position`, `id`) among its
     *      members — that's the component's sort key.
     *   3. Emit components in component-key order. Within each component,
     *      topo-sort so every parent precedes its dependents, with
     *      (`position`, `id`) as the tiebreaker among equally-eligible
     *      siblings.
     *
     * O(n²) where n = option count per product (typically <20). Could be
     * O(n + e) with proper structures but the constant factor doesn't matter
     * at this scale and the explicit form is auditable.
     *
     * Cycle defense: REST cycle detection
     * (ProductsController::dep_save_creates_cycle) prevents cycles from
     * reaching storage. If one somehow does (race, direct DB edit, migration
     * corruption), the inner topo-sort falls back to "append remaining
     * in-order" rather than infinite-looping. PDP's per-option lock evaluation
     * will then perma-lock the cyclic options, which is the safest customer-
     * facing outcome.
     *
     * Trade-off: an admin who *wants* an unrelated option interleaved between
     * linked ones (e.g., for visual rhythm) loses that. For sequential UX the
     * grouping win is decisively bigger than the loss.
     *
     * Storefront-only — admin SPA + REST API continue to receive
     * position-ordered options so admins see exactly the order they
     * configured.
     *
     * @param  array<int, array<string,mixed>> $options Output of get_options().
     * @return array<int, array<string,mixed>> Storefront-ordered copy.
     */
    public function topo_sort_options_for_storefront( array $options ): array {
        if ( count( $options ) <= 1 ) {
            return $options;
        }

        // ── Setup: id → option map and per-option in-product parent set ──
        $local_ids = [];
        foreach ( $options as $opt ) {
            $local_ids[ (int) $opt['id'] ] = true;
        }

        $by_id   = [];
        $parents = [];
        foreach ( $options as $opt ) {
            $oid = (int) $opt['id'];
            $by_id[ $oid ]   = $opt;
            $parents[ $oid ] = [];
            foreach ( ( $opt['dependencies'] ?? [] ) as $dep ) {
                $pid = (int) ( $dep['parent_option_id'] ?? 0 );
                if ( $pid > 0 && isset( $local_ids[ $pid ] ) ) {
                    $parents[ $oid ][ $pid ] = true;
                }
            }
        }

        // ── Phase 1: connected components via undirected BFS ──
        // Each parent edge contributes both directions to the undirected
        // adjacency; that way an option and its dependents land in the same
        // component regardless of which side of the edge you start from.
        $adj = [];
        foreach ( $local_ids as $oid => $_unused ) {
            $adj[ $oid ] = [];
        }
        foreach ( $parents as $oid => $parent_set ) {
            foreach ( array_keys( $parent_set ) as $pid ) {
                $adj[ $oid ][ $pid ] = true;
                $adj[ $pid ][ $oid ] = true;
            }
        }

        $component_of = [];
        $components   = [];
        foreach ( $local_ids as $oid => $_unused ) {
            if ( isset( $component_of[ $oid ] ) ) {
                continue;
            }
            $idx = count( $components );
            $components[ $idx ] = [];
            $stack = [ $oid ];
            while ( ! empty( $stack ) ) {
                $cur = array_pop( $stack );
                if ( isset( $component_of[ $cur ] ) ) {
                    continue;
                }
                $component_of[ $cur ] = $idx;
                $components[ $idx ][] = $cur;
                foreach ( array_keys( $adj[ $cur ] ) as $nbr ) {
                    if ( ! isset( $component_of[ $nbr ] ) ) {
                        $stack[] = $nbr;
                    }
                }
            }
        }

        // ── Phase 2: sort components by their lowest (position, id) member ──
        $component_keys = [];
        foreach ( $components as $idx => $comp_ids ) {
            $min_pos = PHP_INT_MAX;
            $min_id  = PHP_INT_MAX;
            foreach ( $comp_ids as $oid ) {
                $pos = (int) ( $by_id[ $oid ]['position'] ?? 0 );
                if ( $pos < $min_pos || ( $pos === $min_pos && $oid < $min_id ) ) {
                    $min_pos = $pos;
                    $min_id  = $oid;
                }
            }
            $component_keys[ $idx ] = [ $min_pos, $min_id ];
        }

        $sorted_indices = array_keys( $components );
        usort( $sorted_indices, function ( $a, $b ) use ( $component_keys ) {
            $ka = $component_keys[ $a ];
            $kb = $component_keys[ $b ];
            if ( $ka[0] !== $kb[0] ) {
                return $ka[0] - $kb[0];
            }
            return $ka[1] - $kb[1];
        } );

        // ── Phase 3: emit components in order, topo-sorted within ──
        $result = [];
        foreach ( $sorted_indices as $idx ) {
            $comp_ids = $components[ $idx ];

            if ( count( $comp_ids ) === 1 ) {
                $result[] = $by_id[ $comp_ids[0] ];
                continue;
            }

            // Topo-sort within this component: pick the option with the lowest
            // (position, id) whose every parent is already placed; repeat.
            $placed     = [];
            $remaining  = array_values( $comp_ids );
            $iter_guard = count( $remaining ) + 1;

            while ( ! empty( $remaining ) && $iter_guard-- > 0 ) {
                $best_idx = -1;
                $best_pos = PHP_INT_MAX;
                $best_id  = PHP_INT_MAX;

                foreach ( $remaining as $i => $oid ) {
                    $eligible = true;
                    foreach ( array_keys( $parents[ $oid ] ) as $pid ) {
                        if ( ! isset( $placed[ $pid ] ) ) {
                            $eligible = false;
                            break;
                        }
                    }
                    if ( ! $eligible ) {
                        continue;
                    }
                    $pos = (int) ( $by_id[ $oid ]['position'] ?? 0 );
                    if ( $pos < $best_pos || ( $pos === $best_pos && $oid < $best_id ) ) {
                        $best_pos = $pos;
                        $best_id  = $oid;
                        $best_idx = $i;
                    }
                }

                if ( $best_idx === -1 ) {
                    // Cycle within component (defensive — REST cycle detection
                    // should make this unreachable). Append remaining in input
                    // order rather than infinite-loop.
                    foreach ( $remaining as $oid ) {
                        $result[] = $by_id[ $oid ];
                    }
                    break;
                }

                $winner_id = $remaining[ $best_idx ];
                $result[] = $by_id[ $winner_id ];
                $placed[ $winner_id ] = true;
                array_splice( $remaining, $best_idx, 1 );
            }
        }

        return $result;
    }

    /**
     * Save a single option and its variants.
     *
     * S140 — accepts `compound_pricing` (TINYINT 0|1) and an optional
     * `dependencies` sub-key. When `dependencies` is present (even an empty
     * array), the option's dependency edges are replaced wholesale via
     * save_option_dependencies(). When the key is absent, existing edges are
     * left alone — protects legacy callers that don't know about the new
     * graph from accidentally wiping it.
     *
     * @param int    $product_id
     * @param array  $option       Option data including 'variants', 'translation',
     *                             optional 'dependencies' sub-keys.
     * @param string $lang
     * @param int    $option_id    0 = create new.
     * @return int Option ID.
     */
    public function save_option( int $product_id, array $option, string $lang = 'en', int $option_id = 0 ): int {
        $translation     = $option['translation']     ?? [];
        $variants        = $option['variants']        ?? [];
        $deps_provided   = array_key_exists( 'dependencies', $option );
        $dependencies    = $deps_provided ? (array) $option['dependencies'] : [];

        // S140 — `compound_pricing` joins the writable allow-list. ENUM coercion
        // handled by REST sanitizer; repo-level guard is the array_intersect_key.
        $allowed = [ 'type', 'position', 'required', 'compound_pricing', 'status' ];
        $row     = array_intersect_key( $option, array_flip( $allowed ) );
        if ( isset( $row['compound_pricing'] ) ) {
            $row['compound_pricing'] = (int) ( $row['compound_pricing'] ? 1 : 0 );
        }
        $row['product_id'] = $product_id;

        if ( $option_id === 0 ) {
            $option_id = $this->db->insert( 'product_options', $row );
        } else {
            $this->db->update( 'product_options', $row, [ 'id' => $option_id ] );
        }

        if ( ! $option_id ) {
            return 0;
        }

        // Translation.
        if ( ! empty( $translation ) ) {
            $this->upsert_option_translation( $option_id, $translation, $lang );
        }

        // Variants.
        foreach ( $variants as $variant ) {
            $this->save_variant( $option_id, $variant, $lang );
        }

        // S140 — dependencies replace-all (only if key was explicitly provided).
        if ( $deps_provided ) {
            $this->save_option_dependencies( $product_id, (int) $option_id, $dependencies );
        }

        return $option_id;
    }

    /**
     * Delete a product option and all its variants + translations.
     *
     * S140 — also cascades dependency cleanup. The option may be referenced
     * BOTH as the dependent (option_id) and as a parent (parent_option_id) on
     * sibling options' edges; both directions get cleared so no edge points
     * at a now-missing option.
     *
     * @param int $option_id
     */
    public function delete_option( int $option_id ): void {
        // Delete variant translations first.
        $variant_ids = $this->db->getColumn(
            "SELECT id FROM {$this->t_option_variants} WHERE option_id = ?i",
            $option_id
        );
        if ( $variant_ids ) {
            foreach ( $variant_ids as $vid ) {
                $this->db->delete( 'product_option_variant_translations', [ 'variant_id' => (int) $vid ] );
            }
        }
        $this->db->delete( 'product_option_variants', [ 'option_id' => $option_id ] );
        $this->db->delete( 'product_option_translations', [ 'option_id' => $option_id ] );

        // S140 — cascade dependency cleanup before the parent row is dropped.
        // Both directions: edges where this option is the dependent, and
        // edges where this option is a parent on some sibling option's row.
        $this->db->query(
            "DELETE FROM {$this->t_option_deps}
             WHERE option_id = ?i OR parent_option_id = ?i",
            $option_id,
            $option_id
        );

        $this->db->delete( 'product_options', [ 'id' => $option_id ] );
    }

    /**
     * Delete a single option variant + its translations.
     *
     * S140 — also cascades dependency cleanup for any edge that named this
     * variant as a parent_variant_id. Without this, deleting a parent
     * variant would orphan downstream gates that can never unlock.
     *
     * @param int $variant_id
     */
    public function delete_variant( int $variant_id ): void {
        $this->db->delete( 'product_option_variant_translations', [ 'variant_id' => $variant_id ] );
        $this->db->delete( 'product_option_variants', [ 'id' => $variant_id ] );

        // S140 — cascade dep cleanup.
        $this->db->delete( 'product_option_dependencies', [ 'parent_variant_id' => $variant_id ] );
    }

    // ══════════════════════════════════════════════════════════════════════════
    // OPTION DEPENDENCIES (S140)
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Replace-all-rows save semantics for an option's dependency edges.
     *
     * Defends in depth at the persistence layer: parent_option_id and
     * parent_variant_id must each belong to the same product as option_id,
     * and parent_variant_id must belong to parent_option_id. Bad rows are
     * silently skipped — REST layer should have rejected them upstream, this
     * is the failsafe.
     *
     * Self-referential edges (option_id === parent_option_id) are also
     * silently skipped. Cycle prevention across longer chains is enforced
     * at the REST save handler before this method is called.
     *
     * @param int                                                          $product_id
     * @param int                                                          $option_id
     * @param array<int, array{parent_option_id:int,parent_variant_id:int}> $dependencies
     */
    public function save_option_dependencies( int $product_id, int $option_id, array $dependencies ): void {
        if ( $option_id <= 0 ) {
            return;
        }

        // Wipe existing edges for this dependent option first.
        $this->db->delete( 'product_option_dependencies', [ 'option_id' => $option_id ] );

        if ( empty( $dependencies ) ) {
            return;
        }

        // Build the set of valid (option_id => [variant_ids]) for this product
        // so we can validate each incoming edge against actual data.
        $valid_options  = $this->db->getColumn(
            "SELECT id FROM {$this->t_options} WHERE product_id = ?i",
            $product_id
        );
        $valid_options  = array_map( 'intval', $valid_options );
        $valid_options  = array_flip( $valid_options ); // O(1) lookup

        $valid_variants = []; // option_id => [variant_id => true]
        if ( ! empty( $valid_options ) ) {
            $variant_rows = $this->db->getArray(
                "SELECT id, option_id FROM {$this->t_option_variants}
                 WHERE option_id IN (?a)",
                array_keys( $valid_options )
            );
            foreach ( $variant_rows as $vr ) {
                $oid = (int) $vr['option_id'];
                $vid = (int) $vr['id'];
                if ( ! isset( $valid_variants[ $oid ] ) ) {
                    $valid_variants[ $oid ] = [];
                }
                $valid_variants[ $oid ][ $vid ] = true;
            }
        }

        // De-dupe via the destination set; the unique key would also catch
        // duplicates but inserting first and letting MySQL reject is noisier.
        $seen = [];
        foreach ( $dependencies as $dep ) {
            $parent_oid = (int) ( $dep['parent_option_id']  ?? 0 );
            $parent_vid = (int) ( $dep['parent_variant_id'] ?? 0 );

            if ( $parent_oid <= 0 || $parent_vid <= 0 ) {
                continue;
            }
            // No self-reference.
            if ( $parent_oid === $option_id ) {
                continue;
            }
            // Parent option must belong to this product.
            if ( ! isset( $valid_options[ $parent_oid ] ) ) {
                continue;
            }
            // Parent variant must belong to parent option.
            if ( empty( $valid_variants[ $parent_oid ][ $parent_vid ] ) ) {
                continue;
            }
            // Dedupe identical edges within the same save call.
            $key = $parent_oid . ':' . $parent_vid;
            if ( isset( $seen[ $key ] ) ) {
                continue;
            }
            $seen[ $key ] = true;

            $this->db->insert( 'product_option_dependencies', [
                'option_id'         => $option_id,
                'parent_option_id'  => $parent_oid,
                'parent_variant_id' => $parent_vid,
            ] );
        }
    }

    /**
     * Get all dependency edges for a product as a flat list.
     *
     * Useful for admin REST endpoints that surface the dep graph separately
     * from the per-option edit form. Storefront/cart consumers should use
     * get_options() instead — it already embeds deps per option.
     *
     * @param  int $product_id
     * @return array<int, array{id:int,option_id:int,parent_option_id:int,parent_variant_id:int}>
     */
    public function get_option_dependencies( int $product_id ): array {
        return $this->db->getArray(
            "SELECT d.id, d.option_id, d.parent_option_id, d.parent_variant_id
             FROM {$this->t_option_deps} d
             INNER JOIN {$this->t_options} o ON o.id = d.option_id
             WHERE o.product_id = ?i
             ORDER BY d.option_id ASC, d.parent_option_id ASC",
            $product_id
        );
    }

    /**
     * Delete every dependency edge for a single dependent option.
     *
     * Public helper for callers that want to clear deps without rewriting
     * the option itself (e.g. an admin "make this option unconditional"
     * action). save_option_dependencies() with an empty array does the same
     * thing; either is fine.
     *
     * @param int $option_id
     */
    public function delete_option_dependencies( int $option_id ): void {
        if ( $option_id <= 0 ) {
            return;
        }
        $this->db->delete( 'product_option_dependencies', [ 'option_id' => $option_id ] );
    }

    /**
     * Clone a global option (product_id=0) onto a specific product,
     * including all its variants and translations.
     *
     * @param  int    $source_option_id  The global option to clone.
     * @param  int    $product_id        Target product.
     * @param  string $lang
     * @return int    New option ID on the product, or 0 on failure.
     */
    public function clone_option_to_product( int $source_option_id, int $product_id, string $lang = 'en' ): int {
        // Read source option.
        $src = $this->db->getRow(
            "SELECT * FROM {$this->t_options} WHERE id = ?i",
            $source_option_id
        );
        if ( ! $src ) {
            return 0;
        }

        // Insert cloned option with the target product_id.
        $new_option_id = $this->db->insert( 'product_options', [
            'product_id' => $product_id,
            'type'       => $src['type'],
            'position'   => (int) $src['position'],
            'required'   => (int) $src['required'],
            'status'     => $src['status'],
        ] );

        if ( ! $new_option_id ) {
            return 0;
        }

        // Clone translation(s).
        $trans = $this->db->getArray(
            "SELECT * FROM {$this->t_option_trans} WHERE option_id = ?i",
            $source_option_id
        );
        foreach ( $trans as $t ) {
            $this->db->insert( 'product_option_translations', [
                'option_id'     => $new_option_id,
                'language_code' => $t['language_code'],
                'name'          => $t['name'],
                'comment'       => $t['comment'] ?? null,
            ] );
        }

        // Clone variants + their translations.
        $variants = $this->db->getArray(
            "SELECT * FROM {$this->t_option_variants} WHERE option_id = ?i ORDER BY position ASC",
            $source_option_id
        );
        foreach ( $variants as $v ) {
            $new_variant_id = $this->db->insert( 'product_option_variants', [
                'option_id'       => $new_option_id,
                'position'        => (int) $v['position'],
                'modifier'        => $v['modifier'],
                'modifier_type'   => $v['modifier_type'],
                'weight_modifier' => $v['weight_modifier'],
                'image_id'        => $v['image_id'] ?? null,
                'status'          => $v['status'],
            ] );

            if ( $new_variant_id ) {
                $vtrans = $this->db->getArray(
                    "SELECT * FROM {$this->db->table('product_option_variant_translations')} WHERE variant_id = ?i",
                    $v['id']
                );
                foreach ( $vtrans as $vt ) {
                    $this->db->insert( 'product_option_variant_translations', [
                        'variant_id'    => $new_variant_id,
                        'language_code' => $vt['language_code'],
                        'name'          => $vt['name'],
                    ] );
                }
            }
        }

        return $new_option_id;
    }

    // ══════════════════════════════════════════════════════════════════════════
    // FEATURES
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get feature values assigned to a product.
     *
     * @param  int    $product_id
     * @param  string $lang
     * @return array<int, array<string,mixed>>
     */
    public function get_feature_values( int $product_id, string $lang = 'en' ): array {
        $t_fvt  = $this->db->table( 'product_feature_variant_translations' );
        $t_fvar = $this->db->table( 'product_feature_variants' );

        $rows = $this->db->getArray(
            "SELECT fv.id, fv.feature_id, fv.value_int, fv.value_float,
                    fv.value_str, fv.value_text, fv.value_date, fv.variant_id,
                    fv.image_id,
                    ft.name AS feature_name, f.type AS feature_type, f.purpose AS feature_purpose,
                    f.show_on_pdp AS show_on_pdp, f.filter_type AS filter_type,
                    vt.name AS variant_name,
                    fvar.color_hex AS color_hex
             FROM {$this->t_feature_vals} fv
             INNER JOIN {$this->t_features} f ON f.id = fv.feature_id
             LEFT JOIN {$this->t_feature_trans} ft
                ON ft.feature_id = fv.feature_id AND ft.language_code = ?s
             LEFT JOIN {$t_fvt} vt
                ON vt.variant_id = fv.variant_id AND vt.language_code = ?s
             LEFT JOIN {$t_fvar} fvar
                ON fvar.id = fv.variant_id
             WHERE fv.product_id = ?i
             ORDER BY f.position ASC, f.id ASC, " . FeatureValueOrder::clause( 'f', 'fvar', 'vt' ),
            $lang,
            $lang,
            $product_id
        );

        // Resolve variant image URLs: image_id → product_images.wp_attachment_id → WP URL.
        $t_pimg = $this->db->table( 'product_images' );
        foreach ( $rows as &$row ) {
            $row['variant_image_url'] = null;
            if ( ! empty( $row['image_id'] ) ) {
                $att_id = $this->db->getField(
                    "SELECT wp_attachment_id FROM {$t_pimg} WHERE id = ?i",
                    (int) $row['image_id']
                );
                if ( $att_id ) {
                    $row['variant_image_url'] = \wp_get_attachment_image_url( (int) $att_id, 'thumbnail' );
                }
            }
        }
        unset( $row );

        return $rows;
    }

    /**
     * Replace all feature values for a product.
     *
     * @param int   $product_id
     * @param array $features   Each: { feature_id, value_* fields }
     */
    public function save_feature_values( int $product_id, array $features ): void {
        $this->db->delete( 'product_feature_values', [ 'product_id' => $product_id ] );

        foreach ( $features as $feat ) {
            $this->db->insert( 'product_feature_values', [
                'product_id'  => $product_id,
                'feature_id'  => (int) $feat['feature_id'],
                'value_int'   => isset( $feat['value_int'] )   ? (int) $feat['value_int']     : null,
                'value_float' => isset( $feat['value_float'] ) ? (float) $feat['value_float'] : null,
                'value_str'   => $feat['value_str']  ?? null,
                'value_text'  => $feat['value_text'] ?? null,
                'value_date'  => $feat['value_date'] ?? null,
                'variant_id'  => isset( $feat['variant_id'] ) ? (int) $feat['variant_id'] : null,
                'image_id'    => ! empty( $feat['image_id'] ) ? (int) $feat['image_id'] : null,
            ] );
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    // VARIATIONS
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Get all variation products linked to a parent.
     *
     * @param  int    $parent_id
     * @param  string $lang
     * @return array<int, array<string,mixed>>
     */
    public function get_variations( int $parent_id, string $lang = 'en' ): array {
        $links = $this->db->getArray(
            "SELECT vl.child_id, vl.combination, vl.position
             FROM {$this->t_variation_links} vl
             WHERE vl.parent_id = ?i
             ORDER BY vl.position ASC",
            $parent_id
        );

        $result = [];
        foreach ( $links as $link ) {
            $child = $this->get( (int) $link['child_id'], $lang );
            if ( $child ) {
                $child['combination'] = json_decode( $link['combination'], true );
                $child['position']    = $link['position'];
                $result[]             = $child;
            }
        }

        return $result;
    }

    /**
     * Link a variation product to a parent with its option combination.
     *
     * @param int   $parent_id
     * @param int   $child_id
     * @param array $combination  [ option_id => variant_id, ... ]
     * @param int   $position
     */
    public function link_variation( int $parent_id, int $child_id, array $combination, int $position = 0 ): void {
        $existing = $this->db->getField(
            "SELECT id FROM {$this->t_variation_links}
             WHERE parent_id = ?i AND child_id = ?i",
            $parent_id,
            $child_id
        );

        $data = [
            'parent_id'   => $parent_id,
            'child_id'    => $child_id,
            'combination' => wp_json_encode( $combination ),
            'position'    => $position,
        ];

        if ( $existing ) {
            $this->db->update( 'product_variation_links', $data, [ 'id' => (int) $existing ] );
        } else {
            $this->db->insert( 'product_variation_links', $data );
        }
    }

    // ══════════════════════════════════════════════════════════════════════════
    // PRIVATE HELPERS
    // ══════════════════════════════════════════════════════════════════════════

    /**
     * Upsert a product translation row.
     */
    private function save_translation( int $product_id, array $data, string $lang ): void {
        $existing = $this->db->getField(
            "SELECT id FROM {$this->t_trans}
             WHERE product_id = ?i AND language_code = ?s",
            $product_id,
            $lang
        );

        $allowed = [ 'name', 'description', 'short_desc', 'meta_title', 'meta_desc' ];
        $row     = array_intersect_key( $data, array_flip( $allowed ) );
        $row['product_id']    = $product_id;
        $row['language_code'] = $lang;

        if ( $existing ) {
            unset( $row['product_id'], $row['language_code'] );
            $this->db->update( 'product_translations', $row, [
                'product_id'    => $product_id,
                'language_code' => $lang,
            ] );
        } else {
            $this->db->insert( 'product_translations', $row );
        }
    }

    /**
     * Upsert an option translation.
     */
    private function upsert_option_translation( int $option_id, array $data, string $lang ): void {
        $existing = $this->db->getField(
            "SELECT id FROM {$this->t_option_trans}
             WHERE option_id = ?i AND language_code = ?s",
            $option_id,
            $lang
        );

        $row = [
            'option_id'     => $option_id,
            'language_code' => $lang,
            'name'          => sanitize_text_field( $data['name'] ?? '' ),
            'comment'       => $data['comment'] ?? null,
        ];

        if ( $existing ) {
            unset( $row['option_id'], $row['language_code'] );
            $this->db->update( 'product_option_translations', $row, [
                'option_id'     => $option_id,
                'language_code' => $lang,
            ] );
        } else {
            $this->db->insert( 'product_option_translations', $row );
        }
    }

    /**
     * Save or update a single option variant.
     */
    private function save_variant( int $option_id, array $variant, string $lang ): int {
        $translation = $variant['translation'] ?? [];
        $variant_id  = (int) ( $variant['id'] ?? 0 );

        $allowed  = [ 'position', 'modifier', 'modifier_type', 'weight_modifier', 'image_id', 'status' ];
        $row      = array_intersect_key( $variant, array_flip( $allowed ) );
        $row['option_id'] = $option_id;

        if ( $variant_id === 0 ) {
            $variant_id = $this->db->insert( 'product_option_variants', $row );
        } else {
            $this->db->update( 'product_option_variants', $row, [ 'id' => $variant_id ] );
        }

        if ( $variant_id && ! empty( $translation ) ) {
            $this->upsert_variant_translation( $variant_id, $translation, $lang );
        }

        return $variant_id;
    }

    /**
     * Upsert a variant translation.
     */
    private function upsert_variant_translation( int $variant_id, array $data, string $lang ): void {
        $existing = $this->db->getField(
            "SELECT id FROM {$this->t_variant_trans}
             WHERE variant_id = ?i AND language_code = ?s",
            $variant_id,
            $lang
        );

        $row = [
            'variant_id'    => $variant_id,
            'language_code' => $lang,
            'name'          => sanitize_text_field( $data['name'] ?? '' ),
        ];

        if ( $existing ) {
            $this->db->update( 'product_option_variant_translations',
                [ 'name' => $row['name'] ],
                [ 'variant_id' => $variant_id, 'language_code' => $lang ]
            );
        } else {
            $this->db->insert( 'product_option_variant_translations', $row );
        }
    }

    /**
     * Save options and all their variants (replaces existing set).
     */
    private function save_options( int $product_id, array $options, string $lang ): void {
        // Remove options not in the incoming set.
        $incoming_ids = array_filter( array_column( $options, 'id' ) );

        $existing_ids = $this->db->getColumn(
            "SELECT id FROM {$this->t_options} WHERE product_id = ?i",
            $product_id
        );

        foreach ( $existing_ids as $eid ) {
            if ( ! in_array( (int) $eid, array_map( 'intval', $incoming_ids ), true ) ) {
                $this->delete_option( (int) $eid );
            }
        }

        foreach ( $options as $option ) {
            $this->save_option( $product_id, $option, $lang, (int) ( $option['id'] ?? 0 ) );
        }
    }

    /**
     * Generate a unique product slug.
     *
     * @param  string $base
     * @param  int    $exclude_id
     * @return string
     */
    private function unique_slug( string $base, int $exclude_id = 0 ): string {
        $slug    = $base;
        $suffix  = 1;
        $exclude = $exclude_id > 0 ? " AND id != {$exclude_id}" : '';

        while ( true ) {
            // S362 / KI-52 — exclude soft-deleted rows. Products are soft-deleted
            // (`deleted_at`), and both `get()` L87 and `list()` L171 filter on it,
            // so a deleted product's slug is unreachable and must be reusable.
            // Counting it silently suffixed live products (`titancart-essentials-5`).
            $count = (int) $this->db->getField(
                "SELECT COUNT(*) FROM {$this->t_products} WHERE slug = ?s AND deleted_at IS NULL{$exclude}",
                $slug
            );

            if ( $count === 0 ) {
                break;
            }

            $slug = "{$base}-{$suffix}";
            $suffix++;
        }

        return $slug;
    }

    /**
     * Build a unique product code (SKU) from the configured source.
     *
     * Source (Settings → General → Product Code Generation → "Generate from"):
     *   name     — a code-ified slug of the product name
     *   category — a code-ified slug of the main (first) category's name
     *   id       — the product's numeric id
     *   prefix   — the configured prefix only, with the id as the unique tail
     *
     * The optional prefix is prepended to every source. Uniqueness is guaranteed
     * by appending -2, -3 … when the candidate already exists on tc_products.sku.
     *
     * @param  int                 $product_id  Persisted product id (never 0 here).
     * @param  array<string,mixed> $translation In-memory translation row (name).
     * @param  array<int>|null     $categories  Category ids; first = main.
     * @param  string              $lang        Language code for the category name.
     * @return string
     */
    private function generate_product_code( int $product_id, array $translation, $categories, string $lang ): string {
        $source = \TitanCart\Services\SettingsService::get( 'general', 'product_code_source', 'id' );
        $prefix = $this->codeify( \TitanCart\Services\SettingsService::get( 'general', 'product_code_prefix', '' ) );

        $base = '';
        switch ( $source ) {
            case 'name':
                $base = $this->codeify( (string) ( $translation['name'] ?? '' ) );
                break;

            case 'category':
                if ( is_array( $categories ) && ! empty( $categories ) ) {
                    $cat_id = (int) reset( $categories );
                    if ( $cat_id > 0 ) {
                        $cat_name = (string) $this->db->getField(
                            "SELECT name FROM " . $this->db->table( 'category_translations' ) .
                            " WHERE category_id = ?i AND language_code = ?s LIMIT 1",
                            $cat_id, $lang
                        );
                        $base = $this->codeify( $cat_name );
                    }
                }
                break;

            case 'id':
            case 'prefix':
            default:
                // 'id'     → the bare product id (optionally prefixed below).
                // 'prefix' → prefix + id; the id is the per-product unique tail.
                $base = (string) $product_id;
                break;
        }

        // Name / category can resolve empty (an unnamed product, or no category
        // assigned) — fall back to the id so we always emit a usable, unique code.
        if ( $base === '' ) {
            $base = (string) $product_id;
        }

        $code = ( $prefix !== '' ) ? $prefix . '-' . $base : $base;

        return $this->unique_product_code( $code, $product_id );
    }

    /**
     * Normalise an arbitrary string into a clean, uppercase, hyphen-separated
     * SKU fragment: transliterate accents, keep A–Z and 0–9, collapse every other
     * run to a single hyphen, and cap the length so codes stay manageable.
     */
    private function codeify( string $str ): string {
        $str = trim( $str );
        if ( $str === '' ) {
            return '';
        }
        if ( function_exists( 'remove_accents' ) ) {
            $str = remove_accents( $str );
        }
        $str = strtoupper( $str );
        $str = preg_replace( '/[^A-Z0-9]+/', '-', $str );
        $str = trim( (string) $str, '-' );
        if ( strlen( $str ) > 48 ) {
            $str = rtrim( substr( $str, 0, 48 ), '-' );
        }
        return $str;
    }

    /**
     * Guarantee a product code is unique on tc_products.sku, excluding the row
     * being saved. Appends -2, -3 … to the first colliding candidate.
     */
    private function unique_product_code( string $code, int $exclude_id ): string {
        if ( $code === '' ) {
            $code = (string) $exclude_id;
        }
        $candidate = $code;
        $n         = 1;
        while ( true ) {
            $clash = (int) $this->db->getField(
                "SELECT COUNT(*) FROM {$this->t_products} WHERE sku = ?s AND id <> ?i",
                $candidate, $exclude_id
            );
            if ( $clash === 0 ) {
                return $candidate;
            }
            $n++;
            $candidate = $code . '-' . $n;
        }
    }

    /**
     * Auto-create a 301 SEO redirect when a slug changes.
     * Skips if an identical redirect already exists.
     */
    private function create_seo_redirect( string $from_url, string $to_url ): void {
        $table = $this->db->table( 'seo_redirects' );

        // Check table exists (may not on fresh installs before migration 16).
        try {
            // Skip if identical redirect already exists.
            $exists = $this->db->getField(
                "SELECT id FROM {$table} WHERE from_url = ?s AND to_url = ?s LIMIT 1",
                $from_url, $to_url
            );
            if ( $exists ) {
                return;
            }

            // Also update any existing redirect that pointed TO the old URL,
            // so redirect chains are collapsed: A→B becomes A→C when B→C is created.
            $this->db->query(
                "UPDATE {$table} SET to_url = ?s WHERE to_url = ?s AND status = 'active'",
                $to_url, $from_url
            );

            $this->db->insert( 'seo_redirects', [
                'from_url' => $from_url,
                'to_url'   => $to_url,
                'type'     => 301,
                'status'   => 'active',
            ] );
        } catch ( \Throwable $e ) {
            // Silently fail if table doesn't exist yet.
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                titancart_log( '[TitanCart] SEO redirect create failed: ' . $e->getMessage() );
            }
        }
    }
}
