<?php
/**
 * Financial sales report grid — one row per sold unit (SQL, HPOS + legacy).
 */

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

/**
 * @return list<string>
 */
function zerti_sales_report_scope_slugs(): array
{
    return ['period'];
}

function zerti_is_valid_sales_report_scope(string $scope): bool
{
    return in_array($scope, zerti_sales_report_scope_slugs(), true);
}

/**
 * GMT bounds for a sales report scope (site timezone).
 *
 * @return array{start_gmt:string,end_gmt:string}|null
 */
function zerti_sales_report_scope_gmt_bounds(string $scope): ?array
{
    if ($scope === '') {
        return null;
    }
    if (!zerti_is_valid_sales_report_scope($scope)) {
        return null;
    }

    if (!function_exists('zerti_wc_home_ts_to_gmt_datetime')) {
        require_once dirname(__DIR__) . '/zerti-wc-home-stats.php';
    }

    $tz = zerti_get_timezone();
    $now = new DateTimeImmutable('now', $tz);
    $start = $now->modify('first day of this month')->setTime(0, 0, 0);
    $end = $now->setTime(23, 59, 59);

    return [
        'start_gmt' => zerti_wc_home_ts_to_gmt_datetime($start->getTimestamp()),
        'end_gmt' => zerti_wc_home_ts_to_gmt_datetime($end->getTimestamp()),
    ];
}

/**
 * @return list<string>
 */
function zerti_sales_report_paid_statuses_for_query(): array
{
    if (!function_exists('zerti_wc_home_get_paid_statuses_for_query')) {
        require_once dirname(__DIR__) . '/zerti-wc-home-stats.php';
    }

    return zerti_wc_home_get_paid_statuses_for_query();
}

/**
 * @param array<string, mixed> $input
 * @return array{start:int,limit:int,search:string,scope:string,orderby:string,order:string}
 */
function zerti_sales_report_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));

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

    $scope = isset($input['sales_scope']) ? sanitize_key((string) $input['sales_scope']) : '';
    if ($scope !== '' && !zerti_is_valid_sales_report_scope($scope)) {
        $scope = '';
    }

    $order = 'DESC';
    $orderby = 'sold_date';
    $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 = 'row_num';
                    break;
                case 'c1':
                    $orderby = 'product_id';
                    break;
                case 'c2':
                    $orderby = 'product_name';
                    break;
                case 'c3':
                    $orderby = 'sku';
                    break;
                case 'c4':
                    $orderby = 'sold_date';
                    break;
                case 'c5':
                    $orderby = 'price';
                    break;
                case 'c6':
                    $orderby = 'order_id';
                    break;
                default:
                    break;
            }
        }
    }

    return [
        'start' => $start,
        'limit' => $limit,
        'search' => $search,
        'scope' => $scope,
        'orderby' => $orderby,
        'order' => $order,
    ];
}

/**
 * Whether scope / status filters rule out all rows.
 */
function zerti_sales_report_scope_is_empty(string $scope): bool
{
    if (zerti_sales_report_scope_gmt_bounds($scope) === null && $scope !== '') {
        return true;
    }

    return zerti_sales_report_paid_statuses_for_query() === [];
}

/**
 * ORDER BY for expanded unit rows.
 *
 * @return array{order_sql:string}
 */
function zerti_sales_report_order_sql_parts(string $orderby, string $order): array
{
    $order_col = 'sold_date_gmt';
    switch ($orderby) {
        case 'row_num':
            $order_col = 'sold_date_gmt';
            break;
        case 'product_id':
            $order_col = 'product_id';
            break;
        case 'product_name':
            $order_col = 'product_name';
            break;
        case 'sku':
            $order_col = 'sku';
            break;
        case 'sold_date':
            $order_col = 'sold_date_gmt';
            break;
        case 'price':
            $order_col = 'unit_amount';
            break;
        case 'order_id':
            $order_col = 'order_id';
            break;
        default:
            break;
    }
    $order_dir = $order === 'ASC' ? 'ASC' : 'DESC';
    $order_sql = sanitize_sql_orderby("{$order_col} {$order_dir}, line_id DESC, unit_seq ASC");
    if ($order_sql === false) {
        $order_sql = 'sold_date_gmt DESC, line_id DESC, unit_seq ASC';
    }

    return ['order_sql' => $order_sql];
}

/**
 * ORDER BY for line-level queries (all-time grid walker; avoids expanding every sold unit in SQL).
 *
 * @return array{order_sql:string}
 */
function zerti_sales_report_line_order_sql_parts(string $orderby, string $order): array
{
    $order_dir = $order === 'ASC' ? 'ASC' : 'DESC';

    switch ($orderby) {
        case 'product_id':
            $order_sql = "product_id {$order_dir}, sold_date_gmt DESC, line_id DESC";
            break;
        case 'product_name':
            $order_sql = "product_name {$order_dir}, sold_date_gmt DESC, line_id DESC";
            break;
        case 'sku':
            $order_sql = "sku {$order_dir}, sold_date_gmt DESC, line_id DESC";
            break;
        case 'price':
            $order_sql = "(line_amount / GREATEST(1, line_qty)) {$order_dir}, sold_date_gmt DESC, line_id DESC";
            break;
        case 'order_id':
            $order_sql = "order_id {$order_dir}, sold_date_gmt DESC, line_id DESC";
            break;
        case 'row_num':
        case 'sold_date':
        default:
            $order_sql = "sold_date_gmt {$order_dir}, line_id DESC";
            break;
    }

    return ['order_sql' => $order_sql];
}

/**
 * Whether the sales report grid should load units via batched line rows (all-time / unbounded).
 */
function zerti_sales_report_grid_uses_line_walker(string $scope): bool
{
    if ($scope !== '') {
        return false;
    }

    /**
     * @param bool $use_walker Default true for all-time lists (large catalogs).
     */
    return (bool) apply_filters('zerti_sales_report_grid_use_line_walker_all_time', true);
}

/**
 * @param array<string, mixed> $line_row
 * @return array<string, mixed>
 */
function zerti_sales_report_unit_sql_shape_from_line(array $line_row, int $unit_seq): array
{
    $line_qty = max(1, min(1000, (int) ($line_row['line_qty'] ?? 1)));
    $unit_seq = max(1, min($line_qty, $unit_seq));
    $line_amount = (float) ($line_row['line_amount'] ?? 0);

    return [
        'line_id' => (int) ($line_row['line_id'] ?? 0),
        'unit_seq' => $unit_seq,
        'order_id' => (int) ($line_row['order_id'] ?? 0),
        'product_id' => (int) ($line_row['product_id'] ?? 0),
        'product_name' => (string) ($line_row['product_name'] ?? ''),
        'sku' => (string) ($line_row['sku'] ?? ''),
        'sold_date_gmt' => (string) ($line_row['sold_date_gmt'] ?? ''),
        'unit_amount' => $line_qty > 0 ? $line_amount / $line_qty : 0.0,
    ];
}

/**
 * Fetch one grid page for all-time sales by walking line items and expanding qty in PHP.
 *
 * @param array{start:int,limit:int,search:string,scope:string,orderby:string,order:string} $parsed
 * @return list<array<string, mixed>>
 */
function zerti_sales_report_fetch_grid_rows_line_walker(array $parsed, array $lines): array
{
    global $wpdb;

    $start = $parsed['start'];
    $limit = $parsed['limit'];
    $target_end = $start + $limit;
    $unit_index = 0;
    $rows = [];

    /**
     * @param int $line_batch Line rows per SQL batch.
     */
    $line_batch = (int) apply_filters('zerti_sales_report_grid_line_batch_size', 300);
    if ($line_batch < 50) {
        $line_batch = 300;
    }

    $line_order = zerti_sales_report_line_order_sql_parts($parsed['orderby'], $parsed['order']);
    $line_offset = 0;

    $select_sql = "
        SELECT line_id, order_id, product_id, product_name, sku, sold_date_gmt, line_qty, line_amount
        FROM ({$lines['sql']}) AS sales_lines
        ORDER BY {$line_order['order_sql']}
        LIMIT %d OFFSET %d
    ";

    do {
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- placeholders in lines subquery + limit.
        $prepared = $wpdb->prepare($select_sql, array_merge($lines['params'], [$line_batch, $line_offset]));
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $prepared from $wpdb->prepare() above.
    $line_rows = $wpdb->get_results($prepared, ARRAY_A);

        if ($wpdb->last_error !== '') {
            if (defined('WP_DEBUG') && WP_DEBUG) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log('zerti_sales_report_fetch_grid_rows_line_walker: ' . $wpdb->last_error);
            }

            return [];
        }

        if (!is_array($line_rows) || $line_rows === []) {
            break;
        }

        foreach ($line_rows as $line_row) {
            if (!is_array($line_row)) {
                continue;
            }

            $line_qty = max(1, min(1000, (int) ($line_row['line_qty'] ?? 1)));
            for ($unit_seq = 1; $unit_seq <= $line_qty; $unit_seq++) {
                if ($unit_index >= $start && $unit_index < $target_end) {
                    $row_num = $unit_index + 1;
                    $rows[] = zerti_sales_report_grid_row_from_sql(
                        zerti_sales_report_unit_sql_shape_from_line($line_row, $unit_seq),
                        $row_num
                    );
                }
                $unit_index++;
                if ($unit_index >= $target_end) {
                    return $rows;
                }
            }
        }

        $line_offset += count($line_rows);
    } while (count($line_rows) >= $line_batch);

    return $rows;
}

/**
 * Filtered line items subquery (paid statuses + optional GMT range + search on lines).
 *
 * @param array{start_gmt:string,end_gmt:string}|null $bounds Null = no date filter (all time).
 * @return array{sql:string,params:list<mixed>,empty:bool}
 */
function zerti_sales_report_lines_subquery_bundle_with_bounds(?array $bounds, string $search = ''): array
{
    global $wpdb;

    if (!function_exists('zerti_wc_home_uses_hpos')) {
        require_once dirname(__DIR__) . '/zerti-wc-home-stats.php';
    }

    $statuses = zerti_sales_report_paid_statuses_for_query();
    if ($statuses === []) {
        return ['sql' => 'SELECT 1 AS line_id WHERE 1 = 0', 'params' => [], 'empty' => true];
    }

    $status_sql = implode(', ', array_fill(0, count($statuses), '%s'));
    $params = $statuses;

    $uses_hpos = zerti_wc_home_uses_hpos();
    $lines_from = zerti_sales_report_lines_subquery_sql($uses_hpos);

    if ($uses_hpos) {
        $where = "o.type = 'shop_order' AND o.status IN ({$status_sql})";
        if ($bounds !== null) {
            $where .= ' AND o.date_created_gmt >= %s AND o.date_created_gmt <= %s';
            $params[] = $bounds['start_gmt'];
            $params[] = $bounds['end_gmt'];
        }
    } else {
        $where = "o.post_type = 'shop_order' AND o.post_status IN ({$status_sql})";
        if ($bounds !== null) {
            $where .= ' AND o.post_date_gmt >= %s AND o.post_date_gmt <= %s';
            $params[] = $bounds['start_gmt'];
            $params[] = $bounds['end_gmt'];
        }
    }

    if ($search !== '') {
        $like = '%' . $wpdb->esc_like($search) . '%';
        $order_id_col = $uses_hpos ? 'o.id' : 'o.ID';
        $where .= " AND (oi.order_item_name LIKE %s OR COALESCE(NULLIF(sku.meta_value, ''), psku.meta_value, '') LIKE %s OR CAST(COALESCE(pid.meta_value, '0') AS CHAR) LIKE %s OR CAST({$order_id_col} AS CHAR) LIKE %s OR CAST(oi.order_item_id AS CHAR) LIKE %s)";
        $params[] = $like;
        $params[] = $like;
        $params[] = $like;
        $params[] = $like;
        $params[] = $like;
    }

    return [
        'sql' => $lines_from . ' WHERE ' . $where,
        'params' => $params,
        'empty' => false,
    ];
}

/**
 * Filtered line items subquery for the sales report grid/export (scope + search).
 *
 * @return array{sql:string,params:list<mixed>,empty:bool}
 */
function zerti_sales_report_lines_subquery_bundle(string $scope, string $search): array
{
    if (zerti_sales_report_scope_is_empty($scope)) {
        return ['sql' => 'SELECT 1 AS line_id WHERE 1 = 0', 'params' => [], 'empty' => true];
    }

    $bounds = zerti_sales_report_scope_gmt_bounds($scope);
    $bounds_arg = $bounds !== null
        ? ['start_gmt' => $bounds['start_gmt'], 'end_gmt' => $bounds['end_gmt']]
        : null;

    return zerti_sales_report_lines_subquery_bundle_with_bounds($bounds_arg, $search);
}

/**
 * Fast sold-unit count from filtered lines (no per-unit expansion).
 */
function zerti_sales_report_count_sold_units(string $scope, string $search): int
{
    global $wpdb;

    $lines = zerti_sales_report_lines_subquery_bundle($scope, $search);
    if ($lines['empty']) {
        return 0;
    }

    $count_sql = "
        SELECT COALESCE(SUM(LEAST(GREATEST(1, sales_lines.line_qty), 1000)), 0)
        FROM ({$lines['sql']}) AS sales_lines
    ";
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- placeholders in lines subquery.
    $prepared = $wpdb->prepare($count_sql, $lines['params']);
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $prepared from $wpdb->prepare() above.
    $total = $wpdb->get_var($prepared);

    if ($wpdb->last_error !== '') {
        if (defined('WP_DEBUG') && WP_DEBUG) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log('zerti_sales_report_count_sold_units: ' . $wpdb->last_error);
        }

        return 0;
    }

    return max(0, (int) $total);
}

/**
 * Inline 1..1000 sequence for expanding line qty into unit rows.
 *
 * @return string
 */
function zerti_sales_report_unit_numbers_sql(): string
{
    static $cache = null;
    if ($cache !== null) {
        return $cache;
    }

    $digits = [];
    for ($i = 0; $i <= 9; $i++) {
        $digits[] = "SELECT {$i} AS n";
    }
    $digit_union = implode(' UNION ALL ', $digits);

    $cache = "
        SELECT (a.n + b.n * 10 + c.n * 100 + 1) AS unit_seq
        FROM ({$digit_union}) a
        CROSS JOIN ({$digit_union}) b
        CROSS JOIN ({$digit_union}) c
        HAVING unit_seq <= 1000
    ";

    return $cache;
}

/**
 * Base line-item SELECT (order filters are injected by zerti_sales_report_lines_subquery_bundle()).
 *
 * @return string
 */
function zerti_sales_report_lines_subquery_sql(bool $uses_hpos): string
{
    global $wpdb;

    $oi_table = "{$wpdb->prefix}woocommerce_order_items";
    $oim_table = "{$wpdb->prefix}woocommerce_order_itemmeta";

    if ($uses_hpos) {
        $orders_table = "{$wpdb->prefix}wc_orders";

        return "
            SELECT
                oi.order_item_id AS line_id,
                o.id AS order_id,
                o.status AS o_status,
                o.date_created_gmt AS sold_date_gmt,
                CAST(COALESCE(pid.meta_value, '0') AS UNSIGNED) AS product_id,
                oi.order_item_name AS product_name,
                COALESCE(NULLIF(sku.meta_value, ''), psku.meta_value, '') AS sku,
                GREATEST(1, CAST(COALESCE(qty.meta_value, '1') AS UNSIGNED)) AS line_qty,
                (
                    CAST(COALESCE(lt.meta_value, '0') AS DECIMAL(20, 4))
                    + CAST(COALESCE(ltx.meta_value, '0') AS DECIMAL(20, 4))
                ) AS line_amount
            FROM {$orders_table} o
            INNER JOIN {$oi_table} oi
                ON oi.order_id = o.id AND oi.order_item_type = 'line_item'
            LEFT JOIN {$oim_table} pid
                ON pid.order_item_id = oi.order_item_id AND pid.meta_key = '_product_id'
            LEFT JOIN {$oim_table} sku
                ON sku.order_item_id = oi.order_item_id AND sku.meta_key = '_sku'
            LEFT JOIN {$oim_table} qty
                ON qty.order_item_id = oi.order_item_id AND qty.meta_key = '_qty'
            LEFT JOIN {$oim_table} lt
                ON lt.order_item_id = oi.order_item_id AND lt.meta_key = '_line_total'
            LEFT JOIN {$oim_table} ltx
                ON ltx.order_item_id = oi.order_item_id AND ltx.meta_key = '_line_tax'
            LEFT JOIN {$wpdb->postmeta} psku
                ON psku.post_id = CAST(COALESCE(pid.meta_value, '0') AS UNSIGNED)
                AND psku.meta_key = '_sku'
                AND CAST(COALESCE(pid.meta_value, '0') AS UNSIGNED) > 0
        ";
    }

    return "
        SELECT
            oi.order_item_id AS line_id,
            o.ID AS order_id,
            o.post_status AS o_status,
            o.post_date_gmt AS sold_date_gmt,
            CAST(COALESCE(pid.meta_value, '0') AS UNSIGNED) AS product_id,
            oi.order_item_name AS product_name,
            COALESCE(NULLIF(sku.meta_value, ''), psku.meta_value, '') AS sku,
            GREATEST(1, CAST(COALESCE(qty.meta_value, '1') AS UNSIGNED)) AS line_qty,
            (
                CAST(COALESCE(lt.meta_value, '0') AS DECIMAL(20, 4))
                + CAST(COALESCE(ltx.meta_value, '0') AS DECIMAL(20, 4))
            ) AS line_amount
        FROM {$wpdb->posts} o
        INNER JOIN {$oi_table} oi
            ON oi.order_id = o.ID AND oi.order_item_type = 'line_item'
        LEFT JOIN {$oim_table} pid
            ON pid.order_item_id = oi.order_item_id AND pid.meta_key = '_product_id'
        LEFT JOIN {$oim_table} sku
            ON sku.order_item_id = oi.order_item_id AND sku.meta_key = '_sku'
        LEFT JOIN {$oim_table} qty
            ON qty.order_item_id = oi.order_item_id AND qty.meta_key = '_qty'
        LEFT JOIN {$oim_table} lt
            ON lt.order_item_id = oi.order_item_id AND lt.meta_key = '_line_total'
        LEFT JOIN {$oim_table} ltx
            ON ltx.order_item_id = oi.order_item_id AND ltx.meta_key = '_line_tax'
        LEFT JOIN {$wpdb->postmeta} psku
            ON psku.post_id = CAST(COALESCE(pid.meta_value, '0') AS UNSIGNED)
            AND psku.meta_key = '_sku'
            AND CAST(COALESCE(pid.meta_value, '0') AS UNSIGNED) > 0
    ";
}

/**
 * Expand filtered line rows into one row per sold unit (qty).
 *
 * @return string
 */
function zerti_sales_report_expanded_lines_sql_from_lines(string $lines_sql): string
{
    $units = zerti_sales_report_unit_numbers_sql();

    return "
        SELECT
            sales_lines.line_id,
            sales_lines.order_id,
            sales_lines.o_status,
            sales_lines.sold_date_gmt,
            sales_lines.product_id,
            sales_lines.product_name,
            sales_lines.sku,
            sales_lines.line_qty,
            unit_nums.unit_seq,
            (
                sales_lines.line_amount / CAST(sales_lines.line_qty AS DECIMAL(20, 4))
            ) AS unit_amount
        FROM ({$lines_sql}) AS sales_lines
        INNER JOIN ({$units}) AS unit_nums
            ON unit_nums.unit_seq <= LEAST(sales_lines.line_qty, 1000)
    ";
}

/**
 * @param array<string, mixed> $sql_row
 * @param int $row_num
 * @return array<string, mixed>
 */
function zerti_sales_report_grid_row_from_sql(array $sql_row, int $row_num): array
{
    $line_id = (int) ($sql_row['line_id'] ?? 0);
    $unit_seq = max(1, (int) ($sql_row['unit_seq'] ?? 1));
    $order_id = (int) ($sql_row['order_id'] ?? 0);
    $product_id = (int) ($sql_row['product_id'] ?? 0);
    $product_name = trim((string) ($sql_row['product_name'] ?? ''));
    if ($product_name === '') {
        $product_name = __('Untitled', 'zerti');
    }
    $sku = trim((string) ($sql_row['sku'] ?? ''));
    $sku_display = $sku !== '' ? zerti_to_persian_digits($sku) : '—';

    $sold_gmt = (string) ($sql_row['sold_date_gmt'] ?? '');
    $sold_ts = $sold_gmt !== '' ? strtotime($sold_gmt . ' UTC') : 0;
    if ($sold_ts > 0) {
        $sold_dt = new DateTime('@' . $sold_ts);
        $sold_dt->setTimezone(zerti_get_timezone());
        $sold_label = zerti_format_date($sold_dt);
    } else {
        $sold_label = '—';
    }

    $amount = function_exists('zerti_wc_parse_amount')
        ? zerti_wc_parse_amount($sql_row['unit_amount'] ?? 0)
        : (float) ($sql_row['unit_amount'] ?? 0);
    $price_html = function_exists('zerti_wc_price_html_persian')
        ? zerti_wc_price_html_persian($amount)
        : esc_html((string) $amount);

    $name_display = zerti_to_persian_digits($product_name);
    $order_label = zerti_to_persian_digits((string) $order_id);
    $product_label = $product_id > 0 ? zerti_to_persian_digits((string) $product_id) : '—';
    $row_label = zerti_to_persian_digits((string) $row_num);

    $search_text = trim(implode(' ', [
        (string) $row_num,
        $row_label,
        (string) $product_id,
        $product_label,
        $product_name,
        $name_display,
        $sku,
        $sku_display,
        $sold_label,
        zerti_to_latin_digits($sold_label),
        (string) $order_id,
        $order_label,
        (string) $amount,
        trim(wp_strip_all_tags($price_html)),
    ]));

    return [
        'rowKeyRaw' => $line_id . ':' . $unit_seq,
        'lineIdRaw' => $line_id,
        'unitSeqRaw' => $unit_seq,
        'rowNumRaw' => $row_num,
        'rowNum' => $row_label,
        'productIdRaw' => $product_id,
        'productId' => $product_label,
        'name' => $name_display,
        'nameSort' => $product_name,
        'sku' => $sku_display,
        'skuSort' => $sku,
        'soldDate' => $sold_label,
        'soldDateSort' => $sold_ts,
        'priceHtml' => $price_html,
        'priceSort' => $amount,
        'orderIdRaw' => $order_id,
        'orderId' => $order_label,
        'searchText' => $search_text,
    ];
}

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

    global $wpdb;

    $parsed = zerti_sales_report_grid_input_from_array($input);
    $start = $parsed['start'];
    $limit = $parsed['limit'];
    $search = $parsed['search'];
    $orderby = $parsed['orderby'];
    $order = $parsed['order'];

    $lines = zerti_sales_report_lines_subquery_bundle($parsed['scope'], $search);
    if ($lines['empty']) {
        return ['rows' => [], 'total' => 0];
    }

    $total = zerti_sales_report_count_sold_units($parsed['scope'], $search);

    if (zerti_sales_report_grid_uses_line_walker($parsed['scope'])) {
        $rows = zerti_sales_report_fetch_grid_rows_line_walker($parsed, $lines);
        if ($rows === [] && $total > 0 && $start === 0) {
            return new WP_Error('zerti_sales_report_db', 'db_error', ['status' => 500]);
        }
    } else {

    $order_parts = zerti_sales_report_order_sql_parts($orderby, $order);
    $expanded = zerti_sales_report_expanded_lines_sql_from_lines($lines['sql']);
    $from_sql = "FROM ({$expanded}) AS sales_units";

    $select_sql = "
        SELECT line_id, unit_seq, order_id, product_id, product_name, sku, sold_date_gmt, unit_amount
        {$from_sql}
        ORDER BY {$order_parts['order_sql']}
        LIMIT %d OFFSET %d
    ";
    $select_params = array_merge($lines['params'], [$limit, $start]);
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- placeholders in lines subquery + limit.
    $select_prepared = $wpdb->prepare($select_sql, $select_params);
    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $select_prepared from $wpdb->prepare() above.
    $sql_rows = $wpdb->get_results($select_prepared, ARRAY_A);

    if ($wpdb->last_error !== '') {
        if (defined('WP_DEBUG') && WP_DEBUG) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log('zerti_fetch_sales_report_grid: ' . $wpdb->last_error);
        }

        return new WP_Error('zerti_sales_report_db', 'db_error', ['status' => 500]);
    }

        $rows = [];
        if (is_array($sql_rows)) {
            $row_num = $start + 1;
            foreach ($sql_rows as $sql_row) {
                if (!is_array($sql_row)) {
                    continue;
                }
                $rows[] = zerti_sales_report_grid_row_from_sql($sql_row, $row_num);
                $row_num++;
            }
        }
    }

    // If the aggregate count failed but rows loaded, keep pagination usable.
    if ($total < 1 && $rows !== []) {
        $total = $start + count($rows);
        if (count($rows) >= $limit) {
            $total++;
        }
    }

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

