<?php

/**
 * Get a list of all options/modifier field names across all BigCommerce products
 * 
 * Although this takes only a single query, that query could potentially return a LOT of data
 * causing a considerable server strain for large shops.
 *
 * It would be awesome if something like this was a V3 endpoint instead :)
 */
function get_all_bigcommerce_options() {
    global $wpdb;

    $option_names = [];

    $sql = "
    SELECT meta_key, meta_value
    FROM {$wpdb->prefix}postmeta
    WHERE meta_key IN ('bigcommerce_options_data', 'bigcommerce_modifier_data')";
    $results = (array) $wpdb->get_results( $sql );

    foreach ( $results as $result ) {
        $which = ( 'bigcommerce_options_data' == $result->meta_key ) ? 'option' : 'modifier';
        $data = json_decode( $result->meta_value );
        foreach ( $data as $row ) {

            // using array keys to prevent duplicates
            $option_names[ $which . '-' . $row->name ] = true; // e.g. option-Color
        }
    }

    return array_keys( $option_names );
}