<?php

namespace Pixeldev\SquareWooSync\REST;

use Pixeldev\SquareWooSync\Abstracts\RESTController;
use Pixeldev\SquareWooSync\Logger\Logger;
use Pixeldev\SquareWooSync\Square\SquareInventory;
use Pixeldev\SquareWooSync\Square\SquareHelper;
use Pixeldev\SquareWooSync\Square\SquareImport;
use Pixeldev\SquareWooSync\Woo\CreateOrder;
use WP_REST_Server;
use WP_REST_Response;
use WP_REST_Request;
use WP_Error;

if (!defined('ABSPATH')) {
  exit; // Exit if accessed directly
}

class SquareController extends RESTController
{
  /**
   * Endpoint namespace.
   *
   * @var string
   */
  protected $namespace = 'sws/v1';

  /**
   * Route base.
   *
   * @var string
   */
  protected $base = 'square-inventory';

  /**
   * Registers the routes for handling inventory.
   *
   * @return void
   */

  public function register_routes()
  {
    $routes = [
      ['', WP_REST_Server::READABLE, 'get_square_inventory', 'check_permission'],
      ['/import', WP_REST_Server::EDITABLE, 'import_to_woocommerce', 'check_permission'],
      ['/saved-inventory', WP_REST_Server::READABLE, 'get_saved_inventory', 'check_permission'],
      ['/saved-inventory/search', WP_REST_Server::READABLE, 'search_inventory', 'check_permission'],
      ['/categories', WP_REST_Server::READABLE, 'get_square_categories', 'check_permission'],
      ['/import/status', WP_REST_Server::READABLE, 'get_import_status', 'check_permission'],
      ['/import/stop', WP_REST_Server::EDITABLE, 'stop_import', 'check_permission'],
    ];

    foreach ($routes as $route) {
      $args = [
        'methods' => $route[1],
        'callback' => [$this, $route[2]],
        'permission_callback' => [$this, $route[3]],
      ];

      if ($route[0] === '/saved-inventory/search') {
        $args[] = array(
          'search' => array(
            'description' => __('Search query for products (name, sku, or square product id).', 'squarewoosync'),
            'type'        => 'string',
            'required'    => false,
          ),
          'category' => array(
            'description' => __('Category filter.', 'squarewoosync'),
            'type'        => 'string',
            'required'    => false,
          ),
          'status' => array(
            'description' => __('Status filter.', 'squarewoosync'),
            'type'        => 'string',
            'required'    => false,
          ),
          'page' => array(
            'description' => __('Page number.', 'squarewoosync'),
            'type'        => 'integer',
            'required'    => false,
            'default'     => 1,
          ),
          'per_page' => array(
            'description' => __('Number of items per page.', 'squarewoosync'),
            'type'        => 'integer',
            'required'    => false,
            'default'     => 20,
          ),
        );
      }
      register_rest_route($this->namespace, '/' . $this->base . $route[0], $args);
    }
  }

  /**
   * Retrieves Square categories, with transient caching and optional force refresh.
   *
   * @param WP_REST_Request $request
   * @return WP_REST_Response|WP_Error
   */
  public function get_square_categories(WP_REST_Request $request)
  {
    $cache_key = 'sws_square_categories';
    $force_refresh = $request->get_param('force');

    if (!$force_refresh && ($cached = get_transient($cache_key)) !== false) {
      return new WP_REST_Response([
        'categories' => $cached,
        'cached' => true,
      ], 200);
    }

    try {
      $squareInventory = new \Pixeldev\SquareWooSync\Square\SquareInventory();
      $categories = $squareInventory->get_all_square_categories();

      if (!is_wp_error($categories)) {
        set_transient($cache_key, $categories, 600); // Cache for 10 minutes
      }

      return new WP_REST_Response([
        'categories' => $categories,
        'cached' => false,
      ], 200);
    } catch (\Exception $e) {
      return new WP_Error(
        'categories_error',
        'Failed to get categories: ' . $e->getMessage(),
        ['status' => 500]
      );
    }
  }


  public function reenable_webhook()
  {
    $square = new SquareHelper();
    // Make a request to fetch webhooks
    $response = $square->square_api_request('/webhooks/subscriptions?include_disabled=true', 'GET');

    if ($response['success'] && isset($response['data']['subscriptions'])) {
      foreach ($response['data']['subscriptions'] as $subscription) {
        if (strpos($subscription['notification_url'], '/wp-json/sws/v1/square-inventory/update') !== false) {
          $webhookId = $subscription['id'];

          // Re-enable the matching webhook
          $square->square_api_request("/webhooks/subscriptions/" . $webhookId, 'PUT', [
            'subscription' => [
              'enabled' => true,
            ],
          ]);

          Logger::log('info', 'Webhook re-enabled after processing previously queued actions.', [
            'webhook_id' => $webhookId,
          ]);

          // Reset the tracking flag
          delete_option('square_webhook_disabled');
          // Reset the request count transient
          delete_transient('square_webhook_request_count');

          break;
        }
      }
    }
  }


  /**
   * Retrieve WooCommerce products by their SKU.
   *
   * @param string $sku The SKU to look up.
   * @return array Array of products with matching SKU, includes square_product_id and square_variation_id if set.
   */
  public function get_woocommerce_products_by_sku($sku)
  {
    global $wpdb;

    $sku = sanitize_text_field($sku);

    // Execute the query and return results
    $results = $wpdb->get_results(
      $wpdb->prepare("
            SELECT 
                p.ID, 
                p.post_title AS name, 
                meta1.meta_value AS sku, 
                meta2.meta_value AS square_product_id, 
                meta3.meta_value AS square_variation_id
            FROM {$wpdb->prefix}posts AS p
            LEFT JOIN {$wpdb->prefix}postmeta AS meta1 
                ON (p.ID = meta1.post_id AND meta1.meta_key = '_sku')
            LEFT JOIN {$wpdb->prefix}postmeta AS meta2 
                ON (p.ID = meta2.post_id AND meta2.meta_key = 'square_product_id')
            LEFT JOIN {$wpdb->prefix}postmeta AS meta3 
                ON (p.ID = meta3.post_id AND meta3.meta_key = 'square_variation_id')
            WHERE p.post_type IN ('product', 'product_variation')
            AND meta1.meta_value = %s
            ORDER BY p.ID
        ", $sku),
      ARRAY_A
    );

    return $results;
  }


  /**
   * Retrieve WooCommerce product data with minimal memory usage, only for products with a specified square_product_id.
   *
   * @param string $squareProductId1 First Square product ID to filter by.
   * @param string $squareProductId2 Second Square product ID to filter by.
   * @return array Array of products with matching Square product IDs, including square_variation_id.
   */
  public function get_woocommerce_products_square($squareProductId1, $squareProductId2)
  {
    global $wpdb;

    $squareProductId1 = sanitize_text_field($squareProductId1);
    $squareProductId2 = sanitize_text_field($squareProductId2);

    // Execute the query directly and return results
    $results = $wpdb->get_results($wpdb->prepare("
        SELECT 
            p.ID, 
            p.post_title AS name, 
            meta1.meta_value AS sku, 
            meta2.meta_value AS square_product_id, 
            meta3.meta_value AS square_variation_id
        FROM {$wpdb->prefix}posts AS p
        LEFT JOIN {$wpdb->prefix}postmeta AS meta1 ON (p.ID = meta1.post_id AND meta1.meta_key = '_sku')
        LEFT JOIN {$wpdb->prefix}postmeta AS meta2 ON (p.ID = meta2.post_id AND meta2.meta_key = 'square_product_id')
        LEFT JOIN {$wpdb->prefix}postmeta AS meta3 ON (p.ID = meta3.post_id AND meta3.meta_key = 'square_variation_id')
        WHERE p.post_type IN ('product', 'product_variation')
        AND meta2.meta_value IN (%s, %s)
        ORDER BY p.ID", $squareProductId1, $squareProductId2), ARRAY_A);

    return $results;
  }



  /**
   * Retrieve WooCommerce product data with minimal memory usage.
   *
   * @return array
   */
  public function get_woocommerce_products()
  {
    global $wpdb;

    // Execute the query directly and return results
    $results = $wpdb->get_results("
    SELECT p.ID, p.post_title AS name, meta1.meta_value AS sku, meta2.meta_value AS square_product_id, meta3.meta_value AS square_variation_id
    FROM {$wpdb->prefix}posts AS p
    LEFT JOIN {$wpdb->prefix}postmeta AS meta1 ON (p.ID = meta1.post_id AND meta1.meta_key = '_sku')
    LEFT JOIN {$wpdb->prefix}postmeta AS meta2 ON (p.ID = meta2.post_id AND meta2.meta_key = 'square_product_id')
    LEFT JOIN {$wpdb->prefix}postmeta AS meta3 ON (p.ID = meta3.post_id AND meta3.meta_key = 'square_variation_id')
    WHERE p.post_type IN ('product', 'product_variation')
    ORDER BY p.ID", ARRAY_A);

    return $results;
  }

  /**
   * Compares Square SKU with WooCommerce SKU for matching purposes and updates the import status.
   *
   * @param array  $squareInventory   Inventory data from Square.
   * @param array  $woocommerceProducts WooCommerce products data.
   * @param object $square            Square helper object.
   * @return array                    Updated inventory data.
   */
  public function compare_skus($squareInventory, $woocommerceProducts, $square)
  {
    $categories = $square->get_all_square_categories();
    $result = [];

    // Create a mapping of WooCommerce square_product_id to WooCommerce product IDs.
    $squareProductIdMapping = [];
    foreach ($woocommerceProducts as $wcProduct) {
      $wcSquareProductId = $wcProduct['square_product_id'] ?? null;
      $wcSquareVariationId = $wcProduct['square_variation_id'] ?? null;
      $wcProductId = $wcProduct['ID'] ?? null;

      if ($wcSquareProductId && $wcProductId) {
        $squareProductIdMapping[$wcSquareProductId] = $wcProductId;
      }

      if ($wcSquareVariationId && $wcProductId) {
        $squareProductIdMapping[$wcSquareVariationId] = $wcProductId;
      }
    }

    foreach ($squareInventory as $squareItem) {
      $itemData = $squareItem;

      // Process categories if present.
      if (isset($itemData['item_data']['categories'])) {
        // Create a lookup array.
        $lookup = [];
        foreach ($categories as $item) {
          $lookup[$item['id']] = $item;
        }

        // Merge category details.
        foreach ($itemData['item_data']['categories'] as &$item) {
          if (isset($lookup[$item['id']])) {
            $item['name'] = $lookup[$item['id']]['name'];
            $item['parent_id'] = $lookup[$item['id']]['parent_id'];
          }
        }
        unset($item); // Break the reference.
      }

      $squareProductId = $itemData['id'] ?? null;
      // Default imported status is false.
      $itemData['imported'] = false;
      if ($squareProductId && isset($squareProductIdMapping[$squareProductId])) {
        $itemData['imported'] = true;
        $itemData['woocommerce_product_id'] = $squareProductIdMapping[$squareProductId];
      }

      // Process variations for SKU matching.
      if (isset($itemData['item_data']['variations'])) {
        foreach ($itemData['item_data']['variations'] as &$variation) {
          $variationId = $variation['id'] ?? null;
          $variation['imported'] = false;
          if ($variationId && isset($squareProductIdMapping[$variationId])) {
            $variation['imported'] = true;
            $variation['woocommerce_product_id'] = $squareProductIdMapping[$variationId];
          }
        }
        unset($variation); // Break the reference.
      }

      // Additional check for simple products with square_variation_id in WooCommerce products.
      foreach ($woocommerceProducts as $wcProduct) {
        if (isset($wcProduct['square_variation_id'])) {
          $squareVariationId = $wcProduct['square_variation_id'];
          if (
            $squareVariationId &&
            in_array($squareVariationId, array_column($itemData['item_data']['variations'], 'id'))
          ) {
            $itemData['imported'] = true;
            $itemData['woocommerce_product_id'] = $wcProduct['ID'];
          }
        }
      }


      // --- New logic: Determine product type and validate variable products ---
      if (isset($itemData['item_data']['variations']) && is_array($itemData['item_data']['variations'])) {
        if (count($itemData['item_data']['variations']) > 1) {
          $itemData['type'] = 'variable';

          // Check ALL variations instead of just the first
          $allHaveOptions = true;
          foreach ($itemData['item_data']['variations'] as $variation) {
            // If this variation's item_option_values is empty/null, mark as invalid
            if (empty($variation['item_variation_data']['item_option_values'])) {
              $allHaveOptions = false;
              break;
            }
          }

          if (!$allHaveOptions) {
            $itemData['invalid_variable_product'] = true;
          }
        } else {
          $itemData['type'] = 'simple';
        }
      } else {
        $itemData['type'] = 'simple';
      }

      // ------------------------------------------------------------------------

      $result[] = $itemData;
    }

    return $result;
  }

  public function get_square_inventory(WP_REST_Request $request)
  {
    global $wpdb;
    try {

      $woocommerceInstalled = $this->check_woocommerce();
      if (!$woocommerceInstalled) {
        return rest_ensure_response(new WP_Error(424, 'Woocommerce not installed or activated'));
      }
      $squareHelper = new SquareHelper();

      if ($squareHelper->get_active_token() === null) {
        return rest_ensure_response(new WP_Error(401, 'Invalid access token'));
      }

      $force = $request->get_param('force') === 'true';
      $table_name = $wpdb->prefix . 'square_inventory';
      $cron_option = 'update_square_inventory_cron';

      if ($force) {
        self::clear_inventory_table();

        wp_schedule_single_event(time(), $cron_option);

        // Ensure settings exist
        $settings = get_option('square-woo-sync_settings', []);
        if (!isset($settings['inventory'])) {
          $settings['inventory'] = [];
        }
        $settings['inventory']['isFetching'] = 1;

        update_option('square-woo-sync_settings', $settings);

        return new WP_REST_Response(['message' => 'Fetching data, please wait...', 'loading' => true, 'data' => ['finished' => false]], 200);
      } else {
        // Ensure settings exist
        $settings = get_option('square-woo-sync_settings', []);
        if (!isset($settings['inventory'])) {
          $settings['inventory'] = [];
        }
        $isRunning = isset($settings['inventory']['isFetching']) ? $settings['inventory']['isFetching'] : 0;

        if (wp_next_scheduled($cron_option) || $isRunning === 1) {
          return new WP_REST_Response(['message' => 'Data is being fetched, please wait...', 'loading' => true, 'data' => []], 200);
        }

        $per_page = $request->get_param('page_size') ?? 10;

        // 1. Get total count of records (no LIMIT)
        $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}");

        // 2. Query the first $per_page rows
        $results = $wpdb->get_results(
          $wpdb->prepare(
            "SELECT * FROM {$table_name} LIMIT %d",
            $per_page
          ),
          ARRAY_A
        );

        // 3. Unserialize the data
        $inventory = array();
        foreach ($results as $row) {
          $inventory[] = json_decode($row['product_data'], true);
        }

        $filteredTotal = $total; // The total count from the database (assumes LIKE filtering is sufficient)
        $response = rest_ensure_response(['message' => 'Data has been fetched', 'loading' => false, 'data' => $inventory]);
        $response->header('X-WP-Total', $filteredTotal);
        $response->header('X-WP-TotalPages', ceil($filteredTotal / $per_page));


        return $response;
      }
    } catch (\Exception $e) {
      error_log('Error in get_square_inventory: ' . $e->getMessage());
      return new WP_REST_Response(['message' => 'An error occurred: ' . $e->getMessage(), 'loading' => false, 'data' => []], 500);
    }
  }

  /**
   * Updates the Square inventory and saves it to the database.
   *
   * @return void
   */
  public static function update_square_inventory_function()
  {
    try {
      if (!wp_next_scheduled('sws_reset_is_fetching')) {
        // Schedule it at (now + 600 seconds)
        wp_schedule_single_event(time() + 900, 'sws_reset_is_fetching');
      }

      // 1) Mark as fetching
      $settings = get_option('square-woo-sync_settings', []);
      if (!isset($settings['inventory'])) {
        $settings['inventory'] = [];
      }
      $settings['inventory']['isFetching'] = 1;
      update_option('square-woo-sync_settings', $settings);

      $squareInv = new SquareInventory();
      $instance  = new self();

      // 2) Validate token
      $squareHelper = new SquareHelper();

      if ($squareHelper->get_active_token() === null) {
        $settings['inventory']['isFetching'] = 0;
        update_option('square-woo-sync_settings', $settings);
        throw new \Exception('Access token not set or invalid.');
      }

      // 3) Clear table + set transient
      self::clear_inventory_table();
      set_transient('update_square_inventory_cron', true, 0);

      // 4) Get all WC products once in memory (usually smaller than the entire Square catalog).
      $woocommerceProducts = $instance->get_woocommerce_products();

      // 5) Now fetch items chunk-by-chunk, compare, then immediately save each chunk
      $squareInv->retrieve_inventory_in_batches(function ($squareProductsChunk) use ($instance, $squareInv, $woocommerceProducts) {
        // Compare SKUs
        $matched = $instance->compare_skus($squareProductsChunk, $woocommerceProducts, $squareInv);

        // Save chunk to DB
        $instance->save_inventory_to_db($matched);

        // Release memory
        unset($matched, $squareProductsChunk);
      });

      // 6) Done. Clean up
      delete_transient('update_square_inventory_cron');

      // Mark fetching done
      $settings['inventory']['isFetching'] = 0;
      update_option('square-woo-sync_settings', $settings);

      // Attempt to remove the single scheduled event, if it exists
      $timestamp = wp_next_scheduled('sws_reset_is_fetching');
      if ($timestamp) {
        wp_unschedule_event($timestamp, 'sws_reset_is_fetching');
      }
    } catch (\Exception $e) {
      // If something fails, reset the “isFetching” status
      $settings['inventory']['isFetching'] = 0;
      update_option('square-woo-sync_settings', $settings);

      error_log('[SWS DEBUG] Exception: ' . $e->getMessage());
    }
  }

  public static function reset_is_fetching_failsafe()
  {
    // We only do this if isFetching is still 1
    $settings = get_option('square-woo-sync_settings', []);
    if (!isset($settings['inventory'])) {
      $settings['inventory'] = [];
    }

    if (!empty($settings['inventory']['isFetching'])) {
      $settings['inventory']['isFetching'] = 0;
      update_option('square-woo-sync_settings', $settings);
    }
  }



  /**
   * Retrieves saved inventory items based on a search query.
   *
   * Searches for the term in:
   *   - The top–level product id (from the JSON)
   *   - The product name (item_data['name'])
   *   - Any SKU in the product’s variations (item_data['variations'][…]['item_variation_data']['sku'])
   *
   * Also supports pagination and server‐side sorting.
   *
   * Expected GET parameters:
   *   - search: the search term
   *   - page: current page (1-based)
   *   - per_page: items per page
   *   - orderby: (optional) the field to sort on (allowed: id, name, sku, price)
   *   - order: (optional) "asc" or "desc" (default asc)
   *
   * @param WP_REST_Request $request Full details about the request.
   * @return WP_REST_Response|WP_Error Response object on success, or WP_Error object on failure.
   */
  public function search_inventory(WP_REST_Request $request)
  {
    try {
      global $wpdb;
      $table_name = $wpdb->prefix . 'square_inventory';

      // 1) Get parameters
      $search   = trim($request->get_param('search') ?? '');
      $page     = max(1, (int) $request->get_param('page', 1));
      $per_page = max(1, min(100, (int) $request->get_param('per_page', 10)));
      $offset   = ($page - 1) * $per_page;

      // Sorting parameters
      $sortBy         = sanitize_text_field($request->get_param('orderby'));
      $sortOrderInput = strtoupper(sanitize_text_field($request->get_param('order')));
      $sortOrder      = ($sortOrderInput === 'DESC') ? 'DESC' : 'ASC';

      // Filter parameters
      $categoryFilter = trim($request->get_param('category') ?? '');
      $statusFilter   = trim($request->get_param('status') ?? '');
      $ecomFilter   = trim($request->get_param('ecom') ?? '');
      $archiveFilter   = trim($request->get_param('archive') ?? '');
      $locationParam  = $request->get_param('location') ?? '';

      // Retrieve settings for default location if needed
      $pluginSettings = get_option('square-woo-sync_settings', []);
      $defaultLocation = '';
      if (isset($pluginSettings['location'])) {
        $defaultLocation = strtolower($pluginSettings['location']);
      }

      // Allowed sort keys mapping (using JSON extraction)
      $allowedSort = array(
        'id'     => "JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.id'))",
        'name'   => "JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.item_data.name'))",
        'sku'    => "JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.item_data.variations[0].item_variation_data.sku'))",
        'price'  => "CAST(JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.item_data.variations[0].item_variation_data.price_money.amount')) AS UNSIGNED)",
        'status' => "JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.imported'))",
        'stock'  => "CAST(JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.item_data.variations[0].item_variation_data.inventory_count')) AS UNSIGNED)",
        'type'   => "CASE WHEN JSON_LENGTH(JSON_EXTRACT(product_data, '$.item_data.variations')) > 1 THEN 'variable' ELSE 'simple' END"
      );

      $orderByClause = '';
      if ($sortBy && array_key_exists($sortBy, $allowedSort)) {
        $orderByClause = " ORDER BY " . $allowedSort[$sortBy] . " " . $sortOrder;
      }

      // 2) Build the WHERE clause and collect parameters.
      // We'll use LOWER() for case-insensitive matching.
      $clauses = array();
      $where_params = array();

      // Search filter: match against product_id or product_data (converted to lowercase)
      if ($search !== '') {
        $search_lower = strtolower($wpdb->esc_like($search));
        $like = '%' . $search_lower . '%';
        $clauses[] = "(LOWER(product_id) LIKE %s OR LOWER(product_data) LIKE %s)";
        $where_params[] = $like;
        $where_params[] = $like;
      }

      // Category filter
      if ($categoryFilter !== '') {
        $cat_lower = strtolower($categoryFilter);
        $clauses[] = "JSON_SEARCH(LOWER(JSON_EXTRACT(product_data, '$.item_data.categories')), 'one', %s, NULL, '$[*].name') IS NOT NULL";
        $where_params[] = $cat_lower;
      }

      // Status filter
      if ($statusFilter === 'imported') {
        $clauses[] = "LOWER(JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.imported'))) = 'true'";
      } elseif ($statusFilter === 'not_imported') {
        $clauses[] = "(JSON_EXTRACT(product_data, '$.imported') IS NULL OR LOWER(JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.imported'))) = 'false')";
      }

      // Ecom filter: if set to 'ecom', include only products whose visibility is not PRIVATE.
      if ($ecomFilter === 'ecom') {
        $clauses[] = "(JSON_EXTRACT(product_data, '$.item_data.visibility') IS NULL OR LOWER(JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.item_data.visibility'))) != 'private')";
      }

      // Archive filter: if set to 'hide', exclude products with is_archived=true.
      if ($archiveFilter === 'hide') {
        $clauses[] = "(JSON_EXTRACT(product_data, '$.is_archived') IS NULL OR LOWER(JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.is_archived'))) != 'true')";
      }

      // Location filter: only add if the parameter is provided (even as empty string, we treat that as "reset")
      if (!empty($locationParam)) {
        // only if $locationParam is non-empty, then apply the filter
        if (!empty($defaultLocation)) {
          $loc = strtolower($defaultLocation);
          $clauses[] = "JSON_CONTAINS(LOWER(JSON_EXTRACT(product_data, '$.present_at_location_ids')), %s) = 1";
          $where_params[] = json_encode($defaultLocation);
        }
      }

      // Combine the clauses into one WHERE clause if any conditions exist.
      $where_sql = '';
      if (!empty($clauses)) {
        $where_sql = 'WHERE ' . implode(' AND ', $clauses);
      }

      // Debug: Log the prepared COUNT SQL query.
      $preparedCountSql = $wpdb->prepare("SELECT COUNT(*) FROM {$table_name} {$where_sql}", $where_params);

      // 3) Count matching rows.
      $count_sql = "SELECT COUNT(*) FROM {$table_name} {$where_sql}";
      $total = (int) $wpdb->get_var($wpdb->prepare($count_sql, $where_params));

      // Debug: Log the prepared data query.
      $data_sql = "SELECT * FROM {$table_name} {$where_sql} {$orderByClause} LIMIT %d OFFSET %d";
      $all_params = array_merge($where_params, array($per_page, $offset));
      $preparedDataSql = $wpdb->prepare($data_sql, $all_params);

      // 4) Execute the data query.
      $results = $wpdb->get_results($preparedDataSql, ARRAY_A);

      $inventory = array();
      foreach ($results as $row) {
        $decoded = json_decode($row['product_data'], true);
        if ($decoded) {
          $inventory[] = $decoded;
        }
      }

      // 5) Return the response with pagination headers.
      $response = rest_ensure_response($inventory);
      $response->header('X-WP-Total', $total);
      $response->header('X-WP-TotalPages', ceil($total / $per_page));


      return $response;
    } catch (\Exception $e) {
      return new WP_Error(
        'search_inventory_error',
        'An error occurred while searching inventory: ' . $e->getMessage(),
        array('status' => 500)
      );
    }
  }



  /**
   * Fetches saved inventory data from the database (limited to 10).
   *
   * @return WP_REST_Response|WP_Error
   */
  public function get_saved_inventory(WP_REST_Request $request)
  {
    $settings = get_option('square-woo-sync_settings', []);

    // Check if the cron job is already running
    if (isset($settings['inventory']['isFetching']) && $settings['inventory']['isFetching'] === 1) {
      return new WP_REST_Response(['message' => 'Inventory loading', 'finished' => false], 200);
    }

    global $wpdb;
    $table_name = $wpdb->prefix . 'square_inventory';

    // We'll define per_page = 10 for this "first 10" scenario.
    $per_page = $request->get_param('page_size') ?? 10;

    // 1. Get total count of records (no LIMIT)
    $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table_name}");

    // 2. Query the first $per_page rows
    $results = $wpdb->get_results(
      $wpdb->prepare(
        "SELECT * FROM {$table_name} LIMIT %d",
        $per_page
      ),
      ARRAY_A
    );

    // 3. Unserialize the data
    $inventory = array();
    foreach ($results as $row) {
      $inventory[] = json_decode($row['product_data'], true);
    }

    // 4. Prepare REST response
    $response = rest_ensure_response(['inventory' => $inventory, 'finished' => true]);

    // 5. Calculate total pages (in this fixed scenario, there's only one page shown in the response, 
    //    but let's still provide the headers for consistency)
    $total_pages = ceil($total / $per_page);

    $response->header('X-WP-Total', $total);
    $response->header('X-WP-TotalPages', $total_pages);

    return $response;
  }

  /**
   * Saves the inventory data to the database.
   *
   * @param array $inventory Inventory data.
   * @return void
   */
  private function save_inventory_to_db($inventory)
  {
    global $wpdb;
    $table_name = $wpdb->prefix . 'square_inventory';
    $batchSize = 30; // Define the size of each batch
    $batches = array_chunk($inventory, $batchSize);

    foreach ($batches as $batch) {
      $wpdb->query('START TRANSACTION');
      try {
        foreach ($batch as $product) {
          // Convert the product array to a JSON string.
          $jsonProduct = json_encode($product);
          if ($jsonProduct === false) {
            throw new \Exception('Failed to encode product to JSON: ' . print_r($product, true));
          }

          $wpdb->replace(
            $table_name,
            array(
              'product_id'   => $product['id'],
              'product_data' => $jsonProduct,
            ),
            array(
              '%s',
              '%s',
            )
          );
        }
        $wpdb->query('COMMIT');
      } catch (\Exception $e) {
        $wpdb->query('ROLLBACK');
        error_log('Error saving batch to database: ' . $e->getMessage());
      }
    }
  }

  /**
   * Clears the inventory table.
   */
  private static function clear_inventory_table()
  {
    global $wpdb;
    $table_name = $wpdb->prefix . 'square_inventory';

    $wpdb->query("DROP TABLE IF EXISTS $table_name");

    // Check if the table exists
    if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {
      // If the table doesn't exist, create it
      $charset_collate = $wpdb->get_charset_collate();
      $sql = "CREATE TABLE $table_name (
            id INT NOT NULL AUTO_INCREMENT,
            product_id VARCHAR(255) NOT NULL,
            product_data JSON NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY (product_id)
        ) $charset_collate;";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);
    }
  }


  public function import_to_woocommerce(\WP_REST_Request $request): \WP_REST_Response
  {
    // 1) Validate license (unchanged).

    // 2) Validate Square token
    $squareHelper = new SquareHelper();

    if ($squareHelper->get_active_token() === null) {
      return new \WP_REST_Response(['error' => 'Invalid access token'], 401);
    }

    // 3) Parse request
    $productParams = $request->get_param('product');     // array or null
    $dataToImport  = $request->get_param('datatoimport'); // e.g. {title,sku,price,etc.}
    $batchSize     = (int) $request->get_param('batchSize') ?: 10; // if not provided, default 10

    global $wpdb;
    $table_name = $wpdb->prefix . 'square_inventory';
    $productItems = [];

    // 4) If no products => "Import All" but possibly *filtered* by search, category, status
    if (empty($productParams)) {
      // We read optional filter params from the request:
      $searchFilter    = trim($request->get_param('searchFilter'));
      $categoryFilter  = trim($request->get_param('categoryFilter'));
      $statusFilter    = trim($request->get_param('statusFilter'));

      // Build a WHERE clause similar to search_inventory
      $clauses      = [];
      $where_params = [];

      // Lowercase search filter
      if (!empty($searchFilter)) {
        $search_lower = strtolower($wpdb->esc_like($searchFilter));
        $like = '%' . $search_lower . '%';
        $clauses[] = "(LOWER(product_id) LIKE %s OR LOWER(product_data) LIKE %s)";
        $where_params[] = $like;
        $where_params[] = $like;
      }

      if (!empty($categoryFilter)) {
        $cat_lower = strtolower($categoryFilter);
        $clauses[] = "JSON_SEARCH(LOWER(JSON_EXTRACT(product_data, '$.item_data.categories')), 'one', %s, NULL, '$[*].name') IS NOT NULL";
        $where_params[] = $cat_lower;
      }

      if ($statusFilter === 'imported') {
        $clauses[] = "LOWER(JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.imported'))) = 'true'";
      } elseif ($statusFilter === 'not_imported') {
        $clauses[] = "(JSON_EXTRACT(product_data, '$.imported') IS NULL OR LOWER(JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.imported'))) = 'false')";
      }

      $clauses[] = "(
        JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.invalid_variable_product')) IS NULL
        OR JSON_UNQUOTE(JSON_EXTRACT(product_data, '$.invalid_variable_product')) != 'true'
      )";



      $where_sql = '';
      if (!empty($clauses)) {
        $where_sql = 'WHERE ' . implode(' AND ', $clauses);
      }

      // Now query only the matching product_ids:
      // Use DISTINCT to avoid duplicates in case the table has multiple entries per product
      $query = "SELECT DISTINCT product_id FROM {$table_name} {$where_sql}";
      // Prepare the statement with the $where_params
      $prepared_sql = $wpdb->prepare($query, $where_params);
      $rows = $wpdb->get_results($prepared_sql, ARRAY_A);

      if (!$rows) {
        return new \WP_REST_Response(['error' => 'No inventory rows found matching your filters.'], 404);
      }

      foreach ($rows as $row) {
        $productItems[] = [
          'parent_id'           => $row['product_id'],
          'selected_variations' => [],
        ];
      }
    } else {
      // 5) If specific products were sent => build a minimal structure
      $productItems = $this->prepareProductsMinimal($productParams, $table_name);
      if (empty($productItems)) {
        return new \WP_REST_Response(['error' => 'No matching products found in DB.'], 404);
      }
    }

    // 6) Mark import as "in progress"
    $settings = get_option('square-woo-sync_settings', []);
    if (! isset($settings['inventory'])) {
      $settings['inventory'] = [];
    }

    $settings['inventory']['isImporting'] = true;
    $settings['inventory']['importProgress'] = [
      'total'      => count($productItems),
      'processed'  => 0,
      'results'    => [],
      'started_at' => current_time('mysql'),
      'finished'   => false,
      'error'      => '',
    ];
    update_option('square-woo-sync_settings', $settings);

    // 7) Break into chunks, schedule an Action for each chunk
    $chunks = array_chunk($productItems, $batchSize);

    if (! function_exists('as_enqueue_async_action')) {
      return $this->abortImport('Action Scheduler function missing.', $settings);
    }

    $dataToImport['modifiers'] = true;

    foreach ($chunks as $index => $chunk) {
      $args = [
        'productItems' => $chunk,
        'dataToImport' => $dataToImport,
        'chunk_index'  => $index,
      ];

      $scheduled = as_enqueue_async_action('sws_import_chunk', $args, 'square_imports');
      if (false === $scheduled) {
        return $this->abortImport("Failed to schedule chunk #{$index}. Import aborted.", $settings);
      }
    }

    return new \WP_REST_Response([
      'status'  => 'import scheduled',
      'total'   => count($productItems),
      'message' => 'Import is now running in the background',
    ], 200);
  }

  /**
   * A helper that returns minimal parent ID + variation IDs
   * rather than full product data. If subRows exist, we only
   * keep the selected variation IDs. We'll fetch full data in the chunk action.
   */
  private function prepareProductsMinimal(array $productParams, string $table_name): array
  {
    global $wpdb;
    $importList = [];

    foreach ($productParams as $parent) {
      if (empty($parent['id'])) {
        continue; // skip
      }
      $parentId = sanitize_text_field($parent['id']);

      // Check if row actually exists
      $rowExists = $wpdb->get_var(
        $wpdb->prepare(
          "SELECT COUNT(*) FROM {$table_name} WHERE product_id = %s",
          $parentId
        )
      );
      if (! $rowExists) {
        continue; // no DB match
      }

      // Gather subRows for partial variations if subRows is an array
      $subRows = (! empty($parent['subRows']) && is_array($parent['subRows']))
        ? $parent['subRows']
        : [];

      // If subRows => user selected specific variation IDs
      $variationIds = [];
      if (! empty($subRows)) {
        $variationIds = array_map('sanitize_text_field', array_column($subRows, 'id'));
      }

      $importList[] = [
        'parent_id'           => $parentId,
        'selected_variations' => $variationIds,
      ];
    }

    return $importList;
  }

  /**
   * If scheduling fails or an error occurs, update plugin settings to
   * indicate the import was aborted with an error. Then return a WP_REST_Response.
   */
  private function abortImport(string $errorMessage, array $originalSettings): \WP_REST_Response
  {
    $originalSettings['inventory']['isImporting'] = false;
    $originalSettings['inventory']['importProgress']['finished'] = true;
    $originalSettings['inventory']['importProgress']['finished_at'] = current_time('mysql');
    $originalSettings['inventory']['importProgress']['error'] = $errorMessage;

    update_option('square-woo-sync_settings', $originalSettings);

    return new \WP_REST_Response([
      'error'   => $errorMessage,
      'message' => 'Import aborted due to error.',
    ], 500);
  }


  /**
   * Check import status so the front end can poll.
   */
  public function get_import_status(WP_REST_Request $request): WP_REST_Response
  {
    $settings = get_option('square-woo-sync_settings', []);
    if (!isset($settings['inventory'])) {
      $settings['inventory'] = [];
    }
    $inv = $settings['inventory'];

    $isImporting = !empty($inv['isImporting']) ? true : false;

    // gather progress info
    $progress = $inv['importProgress'] ?? [];


    // you could limit ‘results’ if it's large
    return new WP_REST_Response([
      'isImporting' => $isImporting,
      'progress'    => $progress,
    ], 200);
  }

  /**
   * POST /sws/v1/square-inventory/import/stop
   *
   * Cancels any ongoing import by marking isImporting=false and unscheduling
   * any pending chunk actions. The front-end can call this to forcibly stop.
   */
  public function stop_import(\WP_REST_Request $request): \WP_REST_Response
  {
    $settings = get_option('square-woo-sync_settings', []);
    if (!isset($settings['inventory'])) {
      $settings['inventory'] = [];
    }
    $inv = $settings['inventory'];

    // If not importing, just return a message
    if (empty($inv['isImporting'])) {
      return new \WP_REST_Response([
        'message' => 'No import in progress to stop.',
        'success' => false,
      ], 200);
    }

    // Mark isImporting = false and finished
    $inv['isImporting'] = false;
    if (!isset($inv['importProgress'])) {
      $inv['importProgress'] = [];
    }
    $inv['importProgress']['finished'] = true;
    $inv['importProgress']['finished_at'] = current_time('mysql');
    $inv['importProgress']['error'] = 'Import stopped by user';

    // Save back to DB
    $settings['inventory'] = $inv;
    update_option('square-woo-sync_settings', $settings);

    // Unschedule all future chunk actions
    if (function_exists('as_unschedule_all_actions')) {
      // This removes all actions for the hook 'sws_import_chunk' in the group 'square_imports'
      as_unschedule_all_actions('sws_import_chunk', [], 'square_imports');
    }

    return new \WP_REST_Response([
      'message' => 'Import stopped successfully.',
      'success' => true,
    ], 200);
  }
}
