<?php
/**
 * WooCommerce products grid — row builders and data fetch for ZERTI inventory.
 */

if (!defined('ABSPATH')) {
    exit;
}

/**
 * Whether the current user may load the products grid.
 */
function zerti_user_can_view_products_grid(): bool
{
    return zerti_user_can_access_tab('inventory')
        && (current_user_can('edit_products') || current_user_can('manage_woocommerce'));
}

/**
 * Persian label for WooCommerce product post status.
 */
function zerti_product_status_label_fa(string $status): string
{
    $labels = [
        'publish' => __('Published', 'zerti'),
        'draft' => __('Draft', 'zerti'),
        'pending' => __('Awaiting re-review', 'zerti'),
        'private' => __('Private', 'zerti'),
        'future' => __('Scheduled', 'zerti'),
    ];

    return $labels[$status] ?? $status;
}

/**
 * Persian label for WooCommerce stock status slug.
 */
function zerti_product_stock_status_label_fa(string $status): string
{
    $labels = [
        'instock' => __('In stock', 'zerti'),
        'outofstock' => __('Out of stock', 'zerti'),
        'onbackorder' => __('Pre-order', 'zerti'),
    ];

    return $labels[$status] ?? $status;
}

/**
 * Persian label for WooCommerce product type slug.
 */
function zerti_product_type_label_fa(string $type): string
{
    $labels = [
        'simple' => __('Simple', 'zerti'),
        'variable' => __('Variable', 'zerti'),
        'grouped' => __('Grouped', 'zerti'),
        'external' => __('External', 'zerti'),
    ];

    return $labels[$type] ?? $type;
}

/**
 * Breadcrumb separator between parent and child product categories.
 */
function zerti_product_category_breadcrumb_separator(): string
{
    return ' › ';
}

/**
 * Root-to-leaf breadcrumb for one product_cat term (e.g. مادر › فرزند).
 */
function zerti_product_category_breadcrumb_path(WP_Term $term): string
{
    $taxonomy = zerti_product_cat_taxonomy();
    if ($term->taxonomy !== $taxonomy) {
        return $term->name;
    }

    $names = [];
    $ancestor_ids = get_ancestors((int) $term->term_id, $taxonomy, 'taxonomy');
    if (is_array($ancestor_ids) && $ancestor_ids !== []) {
        $ancestor_ids = array_reverse(array_map('intval', $ancestor_ids));
        foreach ($ancestor_ids as $ancestor_id) {
            $ancestor = get_term($ancestor_id, $taxonomy);
            if ($ancestor instanceof WP_Term && !is_wp_error($ancestor)) {
                $names[] = $ancestor->name;
            }
        }
    }
    $names[] = $term->name;

    return implode(zerti_product_category_breadcrumb_separator(), $names);
}

/**
 * Assigned categories as breadcrumb paths; skips parent terms when a child is also assigned.
 */
function zerti_product_category_breadcrumbs_display(int $product_id): string
{
    $taxonomy = zerti_product_cat_taxonomy();
    $terms = get_the_terms($product_id, $taxonomy);
    if (!is_array($terms) || $terms === []) {
        return '—';
    }

    $term_ids = [];
    foreach ($terms as $term) {
        if ($term instanceof WP_Term) {
            $term_ids[] = (int) $term->term_id;
        }
    }
    if ($term_ids === []) {
        return '—';
    }

    $leaf_terms = [];
    foreach ($terms as $term) {
        if (!($term instanceof WP_Term)) {
            continue;
        }
        $term_id = (int) $term->term_id;
        $is_ancestor_of_another = false;
        foreach ($term_ids as $other_id) {
            if ($other_id === $term_id) {
                continue;
            }
            if (term_is_ancestor_of($term_id, $other_id, $taxonomy)) {
                $is_ancestor_of_another = true;
                break;
            }
        }
        if (!$is_ancestor_of_another) {
            $leaf_terms[] = $term;
        }
    }

    if ($leaf_terms === []) {
        $leaf_terms = array_values(array_filter($terms, static function ($t): bool {
            return $t instanceof WP_Term;
        }));
    }

    $paths = [];
    foreach ($leaf_terms as $term) {
        $paths[] = zerti_product_category_breadcrumb_path($term);
    }
    $paths = array_values(array_unique(array_filter($paths, static function (string $p): bool {
        return $p !== '';
    })));

    if ($paths === []) {
        return '—';
    }

    return zerti_to_persian_digits(implode('، ', $paths));
}

/**
 * Product thumbnail or empty placeholder for the inventory grid.
 */
function zerti_product_grid_image_html(WC_Product $product): string
{
    $image_id = (int) $product->get_image_id();
    if ($image_id > 0) {
        $url = wp_get_attachment_image_url($image_id, 'thumbnail');
        if (is_string($url) && $url !== '') {
            $alt = esc_attr((string) $product->get_name());

            return '<div class="zerti-products-grid-image-wrap">' .
                '<img src="' . esc_url($url) . '" alt="' . $alt . '" class="zerti-products-grid-image" loading="lazy" decoding="async" />' .
                '</div>';
        }
    }

    return '<div class="zerti-products-grid-image-wrap zerti-products-grid-image-wrap--empty" role="img" aria-label="' . esc_attr__('No image', 'zerti') . '">' .
        '<i class="fa-solid fa-image zerti-products-grid-image-placeholder" aria-hidden="true"></i>' .
        '</div>';
}

/**
 * Main price used for grid display and sale-price validation (regular, or active price for simple products).
 */
function zerti_product_grid_main_price_amount(WC_Product $product): float
{
    $regular = (float) $product->get_regular_price();
    if ($regular > 0) {
        return $regular;
    }

    if ($product->is_type('simple')) {
        $price = (float) $product->get_price();
        if ($price > 0) {
            return $price;
        }
    }

    return 0.0;
}

/**
 * Convert one WooCommerce product to the AG Grid row payload.
 *
 * @return array<string, mixed>
 */
function zerti_product_grid_row(WC_Product $product): array
{
    $product_id_raw = (int) $product->get_id();
    $product_id_label = zerti_to_persian_digits((string) $product_id_raw);

    $name = trim((string) $product->get_name());
    if ($name === '') {
        $name = __('Untitled', 'zerti');
    }
    $name_display = zerti_to_persian_digits($name);

    $sku = trim((string) $product->get_sku());
    $sku_display = $sku !== '' ? zerti_to_persian_digits($sku) : '—';

    $type_slug = (string) $product->get_type();
    $is_simple = $type_slug === 'simple';
    $price_editable = $is_simple;
    $sale_price_editable = $is_simple;
    $stock_editable = $is_simple;

    $decimals = function_exists('wc_get_price_decimals') ? wc_get_price_decimals() : 2;
    $regular_amount = (float) $product->get_regular_price();
    $sale_amount = (float) $product->get_sale_price();
    $main_price_amount = zerti_product_grid_main_price_amount($product);

    if ($product->is_type('variable')) {
        $price_html_raw = $product->get_price_html();
        $price_html = is_string($price_html_raw) && $price_html_raw !== ''
            ? zerti_html_persian_digits($price_html_raw)
            : '—';
        $price_sort = $regular_amount > 0 ? $regular_amount : (float) $product->get_price();
        $sale_price_html = '—';
        $sale_price_sort = 0.0;
    } else {
        $price_html = $main_price_amount > 0
            ? zerti_wc_price_html_persian($main_price_amount)
            : '—';
        $price_sort = $main_price_amount;
        $sale_price_html = $sale_amount > 0
            ? zerti_wc_price_html_persian($sale_amount)
            : '—';
        $sale_price_sort = $sale_amount;
    }

    $price_input_value = '';
    $sale_price_input_value = '';
    if ($price_editable) {
        $price_input_value = $main_price_amount > 0
            ? wc_format_decimal((string) $main_price_amount, $decimals)
            : '';
    }
    if ($sale_price_editable) {
        $sale_price_input_value = $sale_amount > 0
            ? wc_format_decimal((string) $sale_amount, $decimals)
            : '';
    }

    $managing = $product->get_manage_stock();
    $qty = $product->get_stock_quantity();
    $stock_is_numeric = false;
    $stock_input_value = '';
    if ($managing && $qty !== null) {
        $stock_display = zerti_to_persian_digits(wc_stock_amount($qty));
        $stock_sort = (float) $qty;
        $stock_is_numeric = true;
        $stock_input_value = (string) wc_stock_amount($qty);
    } else {
        $stock_status = (string) $product->get_stock_status();
        $stock_display = zerti_product_stock_status_label_fa($stock_status);
        $stock_sort = $stock_status === 'instock' ? 1 : 0;
        if ($stock_editable) {
            $stock_is_numeric = true;
            $stock_input_value = '';
        }
    }

    $status_slug = (string) $product->get_status();
    $status_display = zerti_product_status_label_fa($status_slug);

    $type_display = zerti_product_type_label_fa($type_slug);

    $cat_search_blob = zerti_product_category_breadcrumbs_display($product_id_raw);

    $search_text = trim(implode(' ', array_filter([
        (string) $product_id_raw,
        $product_id_label,
        zerti_to_latin_digits($product_id_label),
        $name,
        $name_display,
        zerti_to_latin_digits($name_display),
        $sku,
        zerti_to_latin_digits($sku_display),
        (string) $regular_amount,
        (string) $sale_amount,
        zerti_to_latin_digits((string) $regular_amount),
        zerti_to_latin_digits((string) $sale_amount),
        $stock_display,
        zerti_to_latin_digits($stock_display),
        $status_slug,
        $status_display,
        $type_slug,
        $type_display,
        $cat_search_blob,
        zerti_to_latin_digits($cat_search_blob),
    ])));

    return [
        'idRaw' => $product_id_raw,
        'id' => $product_id_label,
        'image' => zerti_product_grid_image_html($product),
        'name' => $name_display,
        'sku' => $sku_display,
        'price' => $price_html,
        'priceSort' => $price_sort,
        'priceEditable' => $price_editable,
        'priceValue' => $price_input_value,
        'hasRegularPrice' => $is_simple && $main_price_amount > 0,
        'salePrice' => $sale_price_html,
        'salePriceSort' => $sale_price_sort,
        'salePriceEditable' => $sale_price_editable,
        'salePriceValue' => $sale_price_input_value,
        'stock' => $stock_display,
        'stockSort' => $stock_sort,
        'stockEditable' => $stock_editable,
        'stockValue' => $stock_input_value,
        'stockIsNumeric' => $stock_is_numeric,
        'status' => $status_display,
        'statusSlug' => $status_slug,
        'type' => $type_display,
        'typeSlug' => $type_slug,
        'searchText' => $search_text,
    ];
}

/**
 * @return list<string>
 */
function zerti_products_stock_filter_slugs(): array
{
    return ['zero', 'one', 'available'];
}

function zerti_is_valid_products_stock_filter(string $filter): bool
{
    return in_array($filter, zerti_products_stock_filter_slugs(), true);
}

/**
 * Whether a product matches the home-dashboard «zero stock» rules.
 */
function zerti_product_matches_zero_stock_filter(WC_Product $product): bool
{
    if ($product->is_type('variation')) {
        return zerti_product_is_zero_stock_line($product);
    }

    if ($product->is_type('variable')) {
        foreach ($product->get_children() as $child_id) {
            $child = wc_get_product((int) $child_id);
            if ($child instanceof WC_Product && $child->get_status() === 'publish') {
                if (zerti_product_is_zero_stock_line($child)) {
                    return true;
                }
            }
        }

        return false;
    }

    return zerti_product_is_zero_stock_line($product);
}

/**
 * Whether a product matches the home-dashboard «stock = 1» rules (managed stock only).
 */
function zerti_product_matches_one_stock_filter(WC_Product $product): bool
{
    if ($product->is_type('variation')) {
        return zerti_product_is_one_stock_line($product);
    }

    if ($product->is_type('variable')) {
        foreach ($product->get_children() as $child_id) {
            $child = wc_get_product((int) $child_id);
            if ($child instanceof WC_Product && $child->get_status() === 'publish') {
                if (zerti_product_is_one_stock_line($child)) {
                    return true;
                }
            }
        }

        return false;
    }

    return zerti_product_is_one_stock_line($product);
}

/**
 * Single SKU line: managed stock quantity exactly 1.
 */
function zerti_product_is_one_stock_line(WC_Product $product): bool
{
    if (!$product->get_manage_stock()) {
        return false;
    }

    $qty = $product->get_stock_quantity();

    return $qty !== null && (int) $qty === 1;
}

/**
 * Single SKU line: managed qty ≤ 0, or not managed and out of stock.
 */
function zerti_product_is_zero_stock_line(WC_Product $product): bool
{
    if ($product->get_manage_stock()) {
        $qty = $product->get_stock_quantity();

        return $qty === null || (int) $qty <= 0;
    }

    return $product->get_stock_status() === 'outofstock';
}

/**
 * Single SKU line counted in home «available in warehouse» (positive inventory).
 */
function zerti_product_has_positive_inventory_line(WC_Product $product): bool
{
    if ($product->get_manage_stock()) {
        $qty = $product->get_stock_quantity();

        return $qty !== null && (int) $qty > 0;
    }

    return $product->get_stock_status() !== 'outofstock';
}

/**
 * Whether a product matches the home-dashboard «available in warehouse» rules.
 */
function zerti_product_matches_available_stock_filter(WC_Product $product): bool
{
    if ($product->is_type('variation')) {
        return zerti_product_has_positive_inventory_line($product);
    }

    if ($product->is_type('variable')) {
        foreach ($product->get_children() as $child_id) {
            $child = wc_get_product((int) $child_id);
            if ($child instanceof WC_Product && $child->get_status() === 'publish') {
                if (zerti_product_has_positive_inventory_line($child)) {
                    return true;
                }
            }
        }

        return false;
    }

    return zerti_product_has_positive_inventory_line($product);
}

/**
 * Apply home-dashboard stock filter slug to a product.
 */
function zerti_product_matches_stock_filter(WC_Product $product, string $filter): bool
{
    if ($filter === 'zero') {
        return zerti_product_matches_zero_stock_filter($product);
    }
    if ($filter === 'one') {
        return zerti_product_matches_one_stock_filter($product);
    }
    if ($filter === 'available') {
        return zerti_product_matches_available_stock_filter($product);
    }

    return true;
}

/**
 * Post statuses used by the inventory products grid (parent products only).
 *
 * @return list<string>
 */
function zerti_products_grid_post_statuses(): array
{
    return ['publish', 'draft', 'private', 'pending'];
}

/**
 * Stock on hand value for one SKU line (simple product or variation).
 */
function zerti_product_stock_value_line(WC_Product $product): float
{
    if (!zerti_product_has_positive_inventory_line($product)) {
        return 0.0;
    }

    $price = (float) $product->get_price();
    if ($price < 0) {
        $price = 0.0;
    }

    if ($product->get_manage_stock()) {
        $qty = $product->get_stock_quantity();
        if ($qty === null || (int) $qty <= 0) {
            return 0.0;
        }

        return $price * (int) $qty;
    }

    return $price;
}

/**
 * Stock on hand value for a parent product row (sums variable variations).
 */
function zerti_product_stock_value(WC_Product $product): float
{
    if ($product->is_type('variable')) {
        $total = 0.0;
        foreach ($product->get_children() as $child_id) {
            $child = wc_get_product((int) $child_id);
            if ($child instanceof WC_Product && $child->get_status() === 'publish') {
                $total += zerti_product_stock_value_line($child);
            }
        }

        return $total;
    }

    return zerti_product_stock_value_line($product);
}

/**
 * @param array{manage_stock:int,stock_quantity:?string,stock_status:string,price:float} $line
 */
function zerti_wc_home_inventory_line_is_zero(array $line): bool
{
    if (!empty($line['manage_stock'])) {
        $qty = $line['stock_quantity'];

        return $qty === null || $qty === '' || (int) $qty <= 0;
    }

    return ($line['stock_status'] ?? '') === 'outofstock';
}

/**
 * @param array{manage_stock:int,stock_quantity:?string,stock_status:string,price:float} $line
 */
function zerti_wc_home_inventory_line_is_one(array $line): bool
{
    if (empty($line['manage_stock'])) {
        return false;
    }
    $qty = $line['stock_quantity'];

    return $qty !== null && $qty !== '' && (int) $qty === 1;
}

/**
 * @param array{manage_stock:int,stock_quantity:?string,stock_status:string,price:float} $line
 */
function zerti_wc_home_inventory_line_is_available(array $line): bool
{
    if (!empty($line['manage_stock'])) {
        $qty = $line['stock_quantity'];

        return $qty !== null && $qty !== '' && (int) $qty > 0;
    }

    return ($line['stock_status'] ?? '') !== 'outofstock';
}

/**
 * @param array{manage_stock:int,stock_quantity:?string,stock_status:string,price:float} $line
 */
function zerti_wc_home_inventory_line_value(array $line): float
{
    if (!zerti_wc_home_inventory_line_is_available($line)) {
        return 0.0;
    }

    $price = (float) ($line['price'] ?? 0);
    if ($price < 0) {
        $price = 0.0;
    }

    if (!empty($line['manage_stock'])) {
        $qty = (int) ($line['stock_quantity'] ?? 0);
        if ($qty <= 0) {
            return 0.0;
        }

        return $price * $qty;
    }

    return $price;
}

/**
 * @param list<array{manage_stock:int,stock_quantity:?string,stock_status:string,price:float}> $lines
 * @return array{zero:bool,one:bool,available:bool,value:float}
 */
function zerti_wc_home_inventory_parent_flags(array $lines): array
{
    $zero = false;
    $one = false;
    $available = false;
    $value = 0.0;

    foreach ($lines as $line) {
        if (zerti_wc_home_inventory_line_is_zero($line)) {
            $zero = true;
        }
        if (zerti_wc_home_inventory_line_is_one($line)) {
            $one = true;
        }
        if (zerti_wc_home_inventory_line_is_available($line)) {
            $available = true;
        }
        $value += zerti_wc_home_inventory_line_value($line);
    }

    return [
        'zero' => $zero,
        'one' => $one,
        'available' => $available,
        'value' => $value,
    ];
}

/**
 * Legacy home inventory stats (loads every product object).
 *
 * @return array{available_count:int,available_value:float,near_zero_count:int,zero_count:int}
 */
function zerti_wc_home_compute_inventory_stats_legacy(): array
{
    $empty = [
        'available_count' => 0,
        'available_value' => 0.0,
        'near_zero_count' => 0,
        'zero_count' => 0,
    ];

    if (!function_exists('wc_get_products')) {
        return $empty;
    }

    $available_count = 0;
    $available_value = 0.0;
    $near_zero_count = 0;
    $zero_count = 0;

    $page = 1;
    $batch_size = 200;

    while (true) {
        $result = wc_get_products([
            'limit' => $batch_size,
            'page' => $page,
            'paginate' => true,
            'status' => zerti_products_grid_post_statuses(),
            'return' => 'objects',
            'orderby' => 'ID',
            'order' => 'ASC',
        ]);

        $products = is_object($result) && isset($result->products) ? $result->products : [];
        if ($products === []) {
            break;
        }

        foreach ($products as $product_obj) {
            if (!is_a($product_obj, 'WC_Product')) {
                continue;
            }

            if (zerti_product_matches_zero_stock_filter($product_obj)) {
                $zero_count++;
            }
            if (zerti_product_matches_one_stock_filter($product_obj)) {
                $near_zero_count++;
            }
            if (zerti_product_matches_available_stock_filter($product_obj)) {
                $available_count++;
            }

            $available_value += zerti_product_stock_value($product_obj);
        }

        if (count($products) < $batch_size) {
            break;
        }
        $page++;
    }

    return [
        'available_count' => $available_count,
        'available_value' => $available_value,
        'near_zero_count' => $near_zero_count,
        'zero_count' => $zero_count,
    ];
}

/**
 * Home-dashboard inventory stats aligned with the products grid filters.
 *
 * @return array{available_count:int,available_value:float,near_zero_count:int,zero_count:int}
 */
function zerti_wc_home_compute_inventory_stats(): array
{
    $empty = [
        'available_count' => 0,
        'available_value' => 0.0,
        'near_zero_count' => 0,
        'zero_count' => 0,
    ];

    if (!function_exists('wc_get_products')) {
        return $empty;
    }

    global $wpdb;

    $lookup_table = $wpdb->prefix . 'wc_product_meta_lookup';
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $lookup_table)) !== $lookup_table) {
        return zerti_wc_home_compute_inventory_stats_legacy();
    }

    $statuses = zerti_products_grid_post_statuses();
    if ($statuses === []) {
        return $empty;
    }

    $status_sql = implode(', ', array_fill(0, count($statuses), '%s'));
    $posts_table = $wpdb->posts;
    $meta_table = $wpdb->postmeta;

    $variation_sql = "
        SELECT v.post_parent AS parent_id,
            CASE WHEN ms.meta_value = 'yes' THEN 1 ELSE 0 END AS manage_stock,
            l.stock_quantity,
            l.stock_status,
            COALESCE(l.min_price, 0) AS price
        FROM {$posts_table} v
        INNER JOIN {$lookup_table} l ON l.product_id = v.ID
        LEFT JOIN {$meta_table} ms ON ms.post_id = v.ID AND ms.meta_key = '_manage_stock'
        WHERE v.post_type = 'product_variation'
            AND v.post_status = 'publish'
            AND v.post_parent > 0
    ";
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table names from $wpdb; no user input in SQL.
    $variation_rows = $wpdb->get_results($variation_sql, ARRAY_A);
    if (!is_array($variation_rows)) {
        return zerti_wc_home_compute_inventory_stats_legacy();
    }

    $published_variation_exists_sql = "
        EXISTS (
            SELECT 1 FROM {$posts_table} v
            WHERE v.post_type = 'product_variation'
                AND v.post_parent = p.ID
                AND v.post_status = 'publish'
        )
    ";

    $simple_sql = "
        SELECT p.ID AS parent_id,
            CASE WHEN ms.meta_value = 'yes' THEN 1 ELSE 0 END AS manage_stock,
            l.stock_quantity,
            l.stock_status,
            COALESCE(l.min_price, 0) AS price
        FROM {$posts_table} p
        INNER JOIN {$lookup_table} l ON l.product_id = p.ID
        LEFT JOIN {$meta_table} pt ON pt.post_id = p.ID AND pt.meta_key = '_product_type'
        LEFT JOIN {$meta_table} ms ON ms.post_id = p.ID AND ms.meta_key = '_manage_stock'
        WHERE p.post_type = 'product'
            AND p.post_status IN ({$status_sql})
            AND (pt.meta_value IS NULL OR pt.meta_value = '' OR pt.meta_value = 'simple')
            AND NOT {$published_variation_exists_sql}
    ";
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $simple_rows = $wpdb->get_results($wpdb->prepare($simple_sql, $statuses), ARRAY_A);
    if (!is_array($simple_rows)) {
        return zerti_wc_home_compute_inventory_stats_legacy();
    }

    $variable_parent_sql = "
        SELECT p.ID AS parent_id
        FROM {$posts_table} p
        LEFT JOIN {$meta_table} pt ON pt.post_id = p.ID AND pt.meta_key = '_product_type'
        WHERE p.post_type = 'product'
            AND p.post_status IN ({$status_sql})
            AND (
                pt.meta_value = 'variable'
                OR (
                    (pt.meta_value IS NULL OR pt.meta_value = '')
                    AND {$published_variation_exists_sql}
                )
            )
    ";
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $variable_parent_rows = $wpdb->get_results($wpdb->prepare($variable_parent_sql, $statuses), ARRAY_A);
    if (!is_array($variable_parent_rows)) {
        return zerti_wc_home_compute_inventory_stats_legacy();
    }

    $variations_by_parent = [];
    foreach ($variation_rows as $row) {
        $parent_id = (int) ($row['parent_id'] ?? 0);
        if ($parent_id < 1) {
            continue;
        }
        $line = [
            'manage_stock' => (int) ($row['manage_stock'] ?? 0),
            'stock_quantity' => isset($row['stock_quantity']) ? (string) $row['stock_quantity'] : null,
            'stock_status' => (string) ($row['stock_status'] ?? ''),
            'price' => (float) ($row['price'] ?? 0),
        ];
        if (!isset($variations_by_parent[$parent_id])) {
            $variations_by_parent[$parent_id] = [];
        }
        $variations_by_parent[$parent_id][] = $line;
    }

    $available_count = 0;
    $available_value = 0.0;
    $near_zero_count = 0;
    $zero_count = 0;

    foreach ($simple_rows as $row) {
        $line = [
            'manage_stock' => (int) ($row['manage_stock'] ?? 0),
            'stock_quantity' => isset($row['stock_quantity']) ? (string) $row['stock_quantity'] : null,
            'stock_status' => (string) ($row['stock_status'] ?? ''),
            'price' => (float) ($row['price'] ?? 0),
        ];
        $flags = zerti_wc_home_inventory_parent_flags([$line]);
        if ($flags['zero']) {
            $zero_count++;
        }
        if ($flags['one']) {
            $near_zero_count++;
        }
        if ($flags['available']) {
            $available_count++;
        }
        $available_value += $flags['value'];
    }

    foreach ($variable_parent_rows as $row) {
        $parent_id = (int) ($row['parent_id'] ?? 0);
        if ($parent_id < 1) {
            continue;
        }
        $lines = $variations_by_parent[$parent_id] ?? [];
        if ($lines === []) {
            continue;
        }
        $flags = zerti_wc_home_inventory_parent_flags($lines);
        if ($flags['zero']) {
            $zero_count++;
        }
        if ($flags['one']) {
            $near_zero_count++;
        }
        if ($flags['available']) {
            $available_count++;
        }
        $available_value += $flags['value'];
    }

    if ($simple_rows === [] && $variable_parent_rows === []) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:disable WordPress.DB.PreparedSQL -- posts table + status list built from constants.
        $parent_sql = $wpdb->prepare(
            "SELECT COUNT(*) FROM {$posts_table} WHERE post_type = %s AND post_status IN ({$status_sql})",
            array_merge(['product'], $statuses)
        );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $parent_product_count = (int) $wpdb->get_var($parent_sql);
        // phpcs:enable WordPress.DB.PreparedSQL
        if ($parent_product_count > 0) {
            return zerti_wc_home_compute_inventory_stats_legacy();
        }
    }

    return [
        'available_count' => $available_count,
        'available_value' => $available_value,
        'near_zero_count' => $near_zero_count,
        'zero_count' => $zero_count,
    ];
}

/**
 * Test a formatted product grid row against the toolbar search.
 *
 * @param array<string, mixed> $row
 */
function zerti_product_grid_row_matches(array $row, string $search): bool
{
    if ($search === '') {
        return true;
    }

    $needle = zerti_shop_orders_grid_search_normalize($search);
    if ($needle === '' || zerti_shop_orders_grid_search_length($needle) < 3) {
        return true;
    }

    $haystack = zerti_shop_orders_grid_search_normalize(isset($row['searchText']) ? (string) $row['searchText'] : '');

    return function_exists('mb_stripos')
        ? mb_stripos($haystack, $needle, 0, 'UTF-8') !== false
        : stripos($haystack, $needle) !== false;
}

/**
 * @param array<string, mixed> $input
 * @return array{start:int,limit:int,search:string,stock_filter:string,orderby:string,order:string,meta_key:string}
 */
function zerti_products_grid_input_from_array(array $input): array
{
    $start = isset($input['start']) ? max(0, absint((string) $input['start'])) : 0;
    $limit = isset($input['limit']) ? absint((string) $input['limit']) : 50;
    $limit = min(100, max(1, $limit));

    $stock_filter = isset($input['stock_filter']) ? sanitize_key((string) $input['stock_filter']) : '';
    if (!zerti_is_valid_products_stock_filter($stock_filter)) {
        $stock_filter = '';
    }

    $search = isset($input['search']) ? sanitize_text_field((string) $input['search']) : '';
    if (zerti_shop_orders_grid_search_length(zerti_shop_orders_grid_search_normalize($search)) < 3) {
        $search = '';
    }

    $order = 'DESC';
    $orderby = 'ID';
    $meta_key = '';
    $sort_model_raw = isset($input['sort_model']) ? (string) $input['sort_model'] : '';
    if ($sort_model_raw === '' && isset($input['sortModel'])) {
        $sort_model_raw = is_string($input['sortModel'])
            ? (string) $input['sortModel']
            : wp_json_encode($input['sortModel']);
    }
    if ($sort_model_raw !== '') {
        $sort_model = json_decode($sort_model_raw, true);
        if (is_array($sort_model) && isset($sort_model[0]) && is_array($sort_model[0])) {
            $sort_col = isset($sort_model[0]['colId']) ? sanitize_key((string) $sort_model[0]['colId']) : '';
            $sort_dir = isset($sort_model[0]['sort']) ? strtolower(sanitize_key((string) $sort_model[0]['sort'])) : '';
            $order = $sort_dir === 'asc' ? 'ASC' : 'DESC';
            switch ($sort_col) {
                case 'c0':
                    $orderby = 'ID';
                    break;
                case 'c2':
                    $orderby = 'title';
                    break;
                case 'c8':
                    $orderby = 'status';
                    break;
                case 'c3':
                    $orderby = 'meta_value';
                    $meta_key = '_sku';
                    break;
                case 'c4':
                    $orderby = 'meta_value_num';
                    $meta_key = '_regular_price';
                    break;
                case 'c5':
                    $orderby = 'meta_value_num';
                    $meta_key = '_sale_price';
                    break;
                case 'c6':
                    $orderby = 'meta_value_num';
                    $meta_key = '_stock';
                    break;
                default:
                    break;
            }
        }
    }

    return [
        'start' => $start,
        'limit' => $limit,
        'search' => $search,
        'stock_filter' => $stock_filter,
        'orderby' => $orderby,
        'order' => $order,
        // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- Admin grid sort column mapping.
        'meta_key' => $meta_key,
    ];
}

/**
 * Sort grid rows in PHP (used when WooCommerce meta_key order would drop products).
 *
 * @param list<array<string, mixed>> $rows
 * @param array{start:int,limit:int,search:string,stock_filter:string,orderby:string,order:string,meta_key:string} $parsed
 */
function zerti_products_grid_sort_rows(array &$rows, array $parsed): void
{
    if ($rows === []) {
        return;
    }

    $asc = strtoupper((string) ($parsed['order'] ?? 'DESC')) === 'ASC';
    $meta_key = (string) ($parsed['meta_key'] ?? '');
    $orderby = (string) ($parsed['orderby'] ?? 'ID');

    usort(
        $rows,
        static function (array $a, array $b) use ($asc, $meta_key, $orderby): int {
            if ($meta_key === '_sku') {
                $left = zerti_shop_orders_grid_search_normalize(zerti_to_latin_digits((string) ($a['sku'] ?? '')));
                $right = zerti_shop_orders_grid_search_normalize(zerti_to_latin_digits((string) ($b['sku'] ?? '')));
                $cmp = strcmp($left, $right);
            } elseif ($meta_key === '_regular_price') {
                $cmp = ((float) ($a['priceSort'] ?? 0)) <=> ((float) ($b['priceSort'] ?? 0));
            } elseif ($meta_key === '_sale_price') {
                $cmp = ((float) ($a['salePriceSort'] ?? 0)) <=> ((float) ($b['salePriceSort'] ?? 0));
            } elseif ($meta_key === '_stock') {
                $cmp = ((float) ($a['stockSort'] ?? 0)) <=> ((float) ($b['stockSort'] ?? 0));
            } elseif ($orderby === 'title') {
                $cmp = strcmp((string) ($a['name'] ?? ''), (string) ($b['name'] ?? ''));
            } elseif ($orderby === 'status') {
                $cmp = strcmp((string) ($a['statusSlug'] ?? ''), (string) ($b['statusSlug'] ?? ''));
            } else {
                $cmp = ((int) ($a['idRaw'] ?? 0)) <=> ((int) ($b['idRaw'] ?? 0));
            }

            if ($cmp === 0) {
                $cmp = ((int) ($a['idRaw'] ?? 0)) <=> ((int) ($b['idRaw'] ?? 0));
            }

            return $asc ? $cmp : -$cmp;
        }
    );
}

/**
 * Fetch products by scanning the full catalog, then sort and paginate in PHP.
 *
 * @param array{start:int,limit:int,search:string,stock_filter:string,orderby:string,order:string,meta_key:string} $parsed
 * @return array{rows:list<array<string,mixed>>,total:int}
 */
function zerti_products_grid_fetch_scanned(array $parsed): array
{
    $start = $parsed['start'];
    $limit = $parsed['limit'];
    $search = $parsed['search'];
    $stock_filter = $parsed['stock_filter'];

    $rows_all = [];
    $scan_page = 1;
    $scan_limit = 200;

    while (true) {
        $scan_args = zerti_products_grid_wc_query_args(
            [
                'limit' => $scan_limit,
                'orderby' => 'ID',
                'order' => 'ASC',
                // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- Full scan uses ID order; meta_key unused.
                'meta_key' => '',
            ],
            $scan_page
        );
        $scan_args['paginate'] = true;

        $batch = wc_get_products($scan_args);
        $products = is_object($batch) && isset($batch->products) ? $batch->products : [];
        if ($products === []) {
            break;
        }

        foreach ($products as $product_obj) {
            if (!is_a($product_obj, 'WC_Product')) {
                continue;
            }
            if ($stock_filter !== '' && !zerti_product_matches_stock_filter($product_obj, $stock_filter)) {
                continue;
            }
            $row = zerti_product_grid_row($product_obj);
            if (!zerti_product_grid_row_matches($row, $search)) {
                continue;
            }
            $rows_all[] = $row;
        }

        if (count($products) < $scan_limit) {
            break;
        }
        $scan_page++;
    }

    zerti_products_grid_sort_rows($rows_all, $parsed);

    return [
        'rows' => array_slice($rows_all, $start, $limit),
        'total' => count($rows_all),
    ];
}

/**
 * Build wc_get_products() args for one grid page.
 *
 * @return array<string, mixed>
 */
function zerti_products_grid_wc_query_args(array $parsed, int $page): array
{
    $args = [
        'limit' => $parsed['limit'],
        'page' => max(1, $page),
        'paginate' => true,
        'orderby' => $parsed['orderby'],
        'order' => $parsed['order'],
        'status' => zerti_products_grid_post_statuses(),
        'return' => 'objects',
    ];

    if ($parsed['meta_key'] !== '') {
        // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- Admin product grid sort; paginated.
        $args['meta_key'] = $parsed['meta_key'];
    }

    return $args;
}

/**
 * @param array<string, mixed> $input
 * @return array{rows:list<array<string,mixed>>,total:int}|WP_Error
 */
function zerti_fetch_products_grid(array $input)
{
    if (!zerti_user_can_view_products_grid()) {
        return new WP_Error('zerti_forbidden', 'forbidden', ['status' => 403]);
    }
    if (!class_exists('WooCommerce') || !function_exists('wc_get_products')) {
        return new WP_Error('zerti_wc_missing', 'woocommerce_missing', ['status' => 400]);
    }

    $parsed = zerti_products_grid_input_from_array($input);
    $start = $parsed['start'];
    $limit = $parsed['limit'];
    $search = $parsed['search'];
    $stock_filter = $parsed['stock_filter'];

    if ($search !== '' || $stock_filter !== '' || $parsed['meta_key'] !== '') {
        return zerti_products_grid_fetch_scanned($parsed);
    }

    $page = (int) floor($start / $limit) + 1;
    $query_args = zerti_products_grid_wc_query_args($parsed, $page);
    $result = wc_get_products($query_args);

    $products = is_object($result) && isset($result->products) ? $result->products : [];
    $total = is_object($result) && isset($result->total) ? (int) $result->total : count($products);

    $rows = [];
    foreach ($products as $product_obj) {
        if (is_a($product_obj, 'WC_Product')) {
            $rows[] = zerti_product_grid_row($product_obj);
        }
    }

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

/**
 * AJAX: return one page of WooCommerce products for AG Grid.
 */
function zerti_ajax_products_grid(): void
{
    check_ajax_referer('zerti_products_grid', 'nonce');

    $post = zerti_unslash_post_array();
    // phpcs:ignore WordPress.Security.NonceVerification.Missing -- nonce verified above.
    $input = zerti_products_grid_input_from_array($post);

    $result = zerti_fetch_products_grid($input);
    if (is_wp_error($result)) {
        $status = (int) ($result->get_error_data()['status'] ?? 400);
        wp_send_json_error(['message' => $result->get_error_code()], $status > 0 ? $status : 400);
    }

    wp_send_json_success($result);
}

/**
 * AJAX: quick-update simple product price or stock from the inventory grid.
 */
function zerti_ajax_product_quick_update(): void
{
    check_ajax_referer('zerti_products_grid', 'nonce');

    if (!zerti_user_can_view_products_grid() || !current_user_can('edit_products')) {
        wp_send_json_error(['message' => __('You do not have permission to edit products.', 'zerti')], 403);
    }

    if (!function_exists('wc_get_product')) {
        wp_send_json_error(['message' => __('WooCommerce is not available.', 'zerti')], 400);
    }

    $post = zerti_unslash_post_array();
    $product_id = isset($post['product_id']) ? absint((string) $post['product_id']) : 0;
    $field = isset($post['field']) ? sanitize_key((string) $post['field']) : '';
    $value_raw = isset($post['value']) ? sanitize_text_field((string) $post['value']) : '';

    if ($product_id < 1 || !in_array($field, ['price', 'sale_price', 'stock'], true)) {
        wp_send_json_error(['message' => __('Invalid request.', 'zerti')], 400);
    }

    $product = wc_get_product($product_id);
    if (!is_a($product, 'WC_Product')) {
        wp_send_json_error(['message' => __('Product not found.', 'zerti')], 404);
    }

    if (!current_user_can('edit_post', $product_id)) {
        wp_send_json_error(['message' => __('You do not have permission to edit this product.', 'zerti')], 403);
    }

    if ($product->get_type() !== 'simple') {
        wp_send_json_error(['message' => __('Only simple products can be edited from this table.', 'zerti')], 400);
    }

    $value_latin = zerti_to_latin_digits(trim($value_raw));
    $value_latin = str_replace([',', '٬', ' '], ['', '', ''], $value_latin);

    $decimals = function_exists('wc_get_price_decimals') ? wc_get_price_decimals() : 2;

    if ($field === 'price') {
        if ($value_latin === '' || !is_numeric($value_latin)) {
            wp_send_json_error(['message' => __('Entered price is not valid.', 'zerti')], 400);
        }
        $price = wc_format_decimal($value_latin, $decimals);
        $product->set_regular_price($price);
        $sale = (string) $product->get_sale_price();
        if ($sale !== '' && is_numeric($sale) && (float) $sale > 0 && (float) $sale < (float) $price) {
            $product->set_price($sale);
        } else {
            $product->set_price($price);
        }
    } elseif ($field === 'sale_price') {
        if ($value_latin === '') {
            $product->set_sale_price('');
            $regular = (string) $product->get_regular_price();
            $product->set_price($regular !== '' ? $regular : '');
        } elseif (!is_numeric($value_latin)) {
            wp_send_json_error(['message' => __('Entered sale price is not valid.', 'zerti')], 400);
        } else {
            $main_price_amount = zerti_product_grid_main_price_amount($product);
            if ($main_price_amount <= 0) {
                wp_send_json_error(
                    ['message' => __('To set a sale price, enter the regular product price first.', 'zerti')],
                    400
                );
            }
            $regular_str = (string) $product->get_regular_price();
            if ($regular_str === '' || !is_numeric($regular_str) || (float) $regular_str <= 0) {
                $product->set_regular_price(wc_format_decimal($main_price_amount, $decimals));
            }
            $sale = wc_format_decimal($value_latin, $decimals);
            $product->set_sale_price($sale);
            $product->set_price($sale);
        }
    } else {
        if ($value_latin === '' || !is_numeric($value_latin)) {
            wp_send_json_error(['message' => __('Invalid stock quantity.', 'zerti')], 400);
        }
        $qty = wc_stock_amount((float) $value_latin);
        $product->set_manage_stock(true);
        $product->set_stock_quantity($qty);
        $product->set_stock_status($qty > 0 ? 'instock' : 'outofstock');
    }

    $product->save();

    if (function_exists('zerti_invalidate_sidebar_badge_counts')) {
        zerti_invalidate_sidebar_badge_counts();
    }

    wp_send_json_success([
        'message' => __('Product changes saved.', 'zerti'),
        'row' => zerti_product_grid_row($product),
    ]);
}


