<?php
/**
 * Product Mapper Class
 *
 * Handles mapping between WooCommerce products and Dropshipzone SKUs
 *
 * @package Dropshipzone
 */

namespace Dropshipzone;

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Product Mapper
 */
class Product_Mapper {

    /**
     * Table name
     */
    private $table_name;

    /**
     * Logger instance
     */
    private $logger;

    /**
     * Constructor
     */
    public function __construct(Logger $logger) {
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'dsz_product_mapping';
        $this->logger = $logger;
    }

    /**
     * Create mapping table
     */
    public static function create_table() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'dsz_product_mapping';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            wc_product_id bigint(20) NOT NULL,
            dsz_sku varchar(100) NOT NULL,
            dsz_product_name varchar(255) DEFAULT '',
            last_synced datetime DEFAULT NULL,
            last_resynced datetime DEFAULT NULL,
            sync_enabled tinyint(1) DEFAULT 1,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY wc_product_id (wc_product_id),
            UNIQUE KEY dsz_sku (dsz_sku),
            KEY sync_enabled (sync_enabled),
            KEY sync_enabled_last_synced (sync_enabled, last_synced)
        ) $charset_collate;";

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

        // Add last_resynced column if it doesn't exist (for upgrades)
        self::maybe_add_last_resynced_column();
        self::maybe_add_indexes();
    }

    /**
     * Add newer indexes on existing installations (schema v3)
     */
    public static function maybe_add_indexes() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'dsz_product_mapping';

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        $index_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(1) FROM information_schema.statistics WHERE table_schema = %s AND table_name = %s AND index_name = %s",
            DB_NAME,
            $table_name,
            'sync_enabled_last_synced'
        ));

        if (!$index_exists) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
            $wpdb->query("ALTER TABLE `{$table_name}` ADD KEY `sync_enabled_last_synced` (`sync_enabled`, `last_synced`)");
        }
    }
    
    /**
     * Add last_resynced column if it doesn't exist (for database upgrades)
     */
    public static function maybe_add_last_resynced_column() {
        global $wpdb;
        $table_name = $wpdb->prefix . 'dsz_product_mapping';
        
        // First check if table exists
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        $table_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(1) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_name
        ));
        
        if (!$table_exists) {
            return; // Table doesn't exist yet, will be created with column
        }
        
        // Check if column exists using information_schema (more reliable)
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        $column_exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(1) FROM information_schema.columns WHERE table_schema = %s AND table_name = %s AND column_name = %s",
            DB_NAME,
            $table_name,
            'last_resynced'
        ));
        
        if (!$column_exists) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
            $wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN `last_resynced` datetime DEFAULT NULL AFTER `last_synced`");
        }
    }

    /**
     * Get mapping by WooCommerce product ID
     *
     * @param int $wc_product_id WooCommerce product ID
     * @return array|null Mapping data or null
     */
    public function get_by_wc_product($wc_product_id) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM %i WHERE wc_product_id = %d",
            $this->table_name,
            $wc_product_id
        ), ARRAY_A);
    }

    /**
     * Get Dropshipzone SKU by WooCommerce product ID
     *
     * @param int $wc_product_id WooCommerce product ID
     * @return string|null DSZ SKU or null
     */
    public function get_dsz_sku($wc_product_id) {
        $mapping = $this->get_by_wc_product($wc_product_id);
        return $mapping ? $mapping['dsz_sku'] : null;
    }

    /**
     * Get mapping by Dropshipzone SKU
     *
     * @param string $dsz_sku Dropshipzone SKU
     * @return array|null Mapping data or null
     */
    public function get_by_dsz_sku($dsz_sku) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM %i WHERE dsz_sku = %s",
            $this->table_name,
            $dsz_sku
        ), ARRAY_A);
    }

    /**
     * Create or update mapping
     *
     * @param int    $wc_product_id     WooCommerce product ID
     * @param string $dsz_sku           Dropshipzone SKU
     * @param string $dsz_product_name  Dropshipzone product name
     * @return int|false Mapping ID or false
     */
    public function map($wc_product_id, $dsz_sku, $dsz_product_name = '') {
        global $wpdb;

        // Check if mapping already exists for this WC product
        $existing_by_wc = $this->get_by_wc_product($wc_product_id);
        
        // Check if mapping already exists for this SKU
        $existing_by_sku = $this->get_by_dsz_sku($dsz_sku);

        if ($existing_by_wc) {
            // If the SKU is already mapped to a DIFFERENT product, we might need to clean that up
            if ($existing_by_sku && (int)$existing_by_sku['wc_product_id'] !== (int)$wc_product_id) {
                $this->unmap((int)$existing_by_sku['wc_product_id']);
            }

            // Update existing mapping for this product
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
            $result = $wpdb->update(
                $this->table_name,
                [
                    'dsz_sku' => $dsz_sku,
                    'dsz_product_name' => $dsz_product_name,
                    'updated_at' => current_time('mysql'),
                ],
                ['wc_product_id' => $wc_product_id],
                ['%s', '%s', '%s'],
                ['%d']
            );
            
            if ($result !== false) {
                $this->logger->info('Product mapping updated', [
                    'wc_product_id' => $wc_product_id,
                    'dsz_sku' => $dsz_sku,
                ]);
                return $existing_by_wc['id'];
            }
            return false;
        }

        // If we reach here, no mapping exists for this WC product ID.
        // However, the SKU might still be mapped to a different (likely deleted) product.
        if ($existing_by_sku) {
            // Clean up the orphan SKU mapping
            $this->unmap((int)$existing_by_sku['wc_product_id']);
        }

        // Create new mapping
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        $result = $wpdb->insert(
            $this->table_name,
            [
                'wc_product_id' => $wc_product_id,
                'dsz_sku' => $dsz_sku,
                'dsz_product_name' => $dsz_product_name,
                'sync_enabled' => 1,
                'created_at' => current_time('mysql'),
            ],
            ['%d', '%s', '%s', '%d', '%s']
        );

        if ($result) {
            $this->logger->info('Product mapping created', [
                'wc_product_id' => $wc_product_id,
                'dsz_sku' => $dsz_sku,
            ]);
            return $wpdb->insert_id;
        }

        $this->logger->error('Failed to create mapping', [
            'wc_product_id' => $wc_product_id,
            'dsz_sku' => $dsz_sku,
            'error' => $wpdb->last_error,
        ]);
        return false;
    }

    /**
     * Remove mapping
     *
     * @param int $wc_product_id WooCommerce product ID
     * @return bool Success
     */
    public function unmap($wc_product_id) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        $result = $wpdb->delete(
            $this->table_name,
            ['wc_product_id' => $wc_product_id],
            ['%d']
        );
        
        if ($result) {
            $this->logger->info('Product mapping removed', ['wc_product_id' => $wc_product_id]);
        }
        return $result !== false;
    }

    /**
     * Toggle sync enabled
     *
     * @param int  $wc_product_id WooCommerce product ID
     * @param bool $enabled       Enabled status
     * @return bool Success
     */
    public function set_sync_enabled($wc_product_id, $enabled) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return $wpdb->update(
            $this->table_name,
            ['sync_enabled' => $enabled ? 1 : 0],
            ['wc_product_id' => $wc_product_id],
            ['%d'],
            ['%d']
        ) !== false;
    }

    /**
     * Get all mappings with product details
     *
     * @param array $args Query arguments
     * @return array Mappings with product info
     */
    public function get_mappings($args = []) {
        global $wpdb;

        $defaults = [
            'limit' => 50,
            'offset' => 0,
            'search' => '',
            'sync_enabled' => null,
            'resync_filter' => '', // Filter by last_resynced status: never, today, week, month, older
            'orderby' => 'created_at',
            'order' => 'DESC',
        ];
        $args = wp_parse_args($args, $defaults);

        $where = "1=1";
        $values = [];

        if ($args['sync_enabled'] !== null) {
            $where .= " AND m.sync_enabled = %d";
            $values[] = $args['sync_enabled'] ? 1 : 0;
        }

        // Filter by last_resynced status (full data resync)
        if (!empty($args['resync_filter'])) {
            switch ($args['resync_filter']) {
                case 'never':
                    $where .= " AND m.last_resynced IS NULL";
                    break;
                case 'today':
                    $where .= " AND m.last_resynced >= %s";
                    $values[] = gmdate('Y-m-d 00:00:00');
                    break;
                case 'week':
                    $where .= " AND m.last_resynced >= %s";
                    $values[] = gmdate('Y-m-d 00:00:00', strtotime('-7 days'));
                    break;
                case 'month':
                    $where .= " AND m.last_resynced >= %s";
                    $values[] = gmdate('Y-m-d 00:00:00', strtotime('-30 days'));
                    break;
                case 'older':
                    $where .= " AND m.last_resynced < %s";
                    $values[] = gmdate('Y-m-d 00:00:00', strtotime('-30 days'));
                    break;
            }
        }

        if (!empty($args['search'])) {
            $where .= " AND (m.dsz_sku LIKE %s OR m.dsz_product_name LIKE %s OR p.post_title LIKE %s)";
            $search = '%' . $wpdb->esc_like($args['search']) . '%';
            $values[] = $search;
            $values[] = $search;
            $values[] = $search;
        }

        $orderby = in_array($args['orderby'], ['created_at', 'dsz_sku', 'last_synced']) ? $args['orderby'] : 'created_at';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';

        // Escaping
        $orderby = esc_sql($orderby);
        $order = esc_sql($order);

        $sql = "SELECT m.*, p.post_title as wc_product_name
                FROM " . $this->table_name . " m
                LEFT JOIN " . $wpdb->posts . " p ON m.wc_product_id = p.ID
                WHERE " . $where . "
                ORDER BY m.{$orderby} {$order}, m.id ASC
                LIMIT %d OFFSET %d";

        $values[] = intval($args['limit']);
        $values[] = intval($args['offset']);

        if (!empty($values)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
            $sql = $wpdb->prepare($sql, $values);
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return $wpdb->get_results($sql, ARRAY_A);
    }

    /**
     * Get total count of mappings
     *
     * @param array $args Query arguments
     * @return int Count
     */
    public function get_count($args = []) {
        global $wpdb;

        $where = "1=1";
        $values = [];

        if (isset($args['sync_enabled'])) {
            $where .= " AND sync_enabled = %d";
            $values[] = $args['sync_enabled'] ? 1 : 0;
        }

        // Filter by last_resynced status (full data resync)
        if (!empty($args['resync_filter'])) {
            switch ($args['resync_filter']) {
                case 'never':
                    $where .= " AND last_resynced IS NULL";
                    break;
                case 'today':
                    $where .= " AND last_resynced >= %s";
                    $values[] = gmdate('Y-m-d 00:00:00');
                    break;
                case 'week':
                    $where .= " AND last_resynced >= %s";
                    $values[] = gmdate('Y-m-d 00:00:00', strtotime('-7 days'));
                    break;
                case 'month':
                    $where .= " AND last_resynced >= %s";
                    $values[] = gmdate('Y-m-d 00:00:00', strtotime('-30 days'));
                    break;
                case 'older':
                    $where .= " AND last_resynced < %s";
                    $values[] = gmdate('Y-m-d 00:00:00', strtotime('-30 days'));
                    break;
            }
        }

        if (!empty($args['search'])) {
            $where .= " AND (dsz_sku LIKE %s OR dsz_product_name LIKE %s)";
            $search = '%' . $wpdb->esc_like($args['search']) . '%';
            $values[] = $search;
            $values[] = $search;
        }

        $sql = "SELECT COUNT(*) FROM " . $this->table_name . " WHERE " . $where;

        if (!empty($values)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
            $sql = $wpdb->prepare($sql, $values);
        }

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return (int) $wpdb->get_var($sql);
    }

    /**
     * Get all mapped DSZ SKUs for sync
     *
     * @param int $limit  Limit
     * @param int $offset Offset
     * @return array Array of ['wc_product_id' => X, 'dsz_sku' => Y]
     */
    public function get_mapped_skus_for_sync($limit = 100, $offset = 0) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return $wpdb->get_results($wpdb->prepare(
            "SELECT wc_product_id, dsz_sku 
             FROM %i 
             WHERE sync_enabled = 1 
             ORDER BY id ASC 
             LIMIT %d OFFSET %d",
            $this->table_name,
            $limit, $offset
        ), ARRAY_A);
    }

    /**
     * Get syncable mappings for a set of Dropshipzone SKUs.
     *
     * Used by the incremental pass to intersect the supplier's change log
     * with the SKUs this store actually sells.
     *
     * @param array $skus Dropshipzone SKUs
     * @return array Rows of ['wc_product_id' => X, 'dsz_sku' => Y]
     */
    public function get_syncable_by_skus($skus) {
        global $wpdb;

        $skus = array_values(array_unique(array_filter(array_map('strval', (array) $skus))));

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

        $results = [];

        // Chunked so a large change window cannot build a query longer than
        // max_allowed_packet or exhaust the placeholder limit.
        foreach (array_chunk($skus, 500) as $chunk) {
            // One %s per SKU. Built from a count, never from input, and every
            // value is still passed through prepare() below.
            $placeholders = implode(',', array_fill(0, count($chunk), '%s'));

            $sql = "SELECT wc_product_id, dsz_sku FROM %i WHERE sync_enabled = 1 AND dsz_sku IN ({$placeholders})"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $placeholders is a generated list of %s, not data.

            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name comes from $wpdb->prefix, not user input; the replacement count is dynamic by design and matches $placeholders. These are plugin-owned tables, so no core caching API applies.
            $rows = $wpdb->get_results($wpdb->prepare($sql, array_merge([$this->table_name], $chunk)), ARRAY_A);

            if (!empty($rows)) {
                $results = array_merge($results, $rows);
            }
        }

        return $results;
    }

    /**
     * Get total count of syncable mappings
     *
     * @return int Count
     */
    public function get_syncable_count() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i WHERE sync_enabled = 1", $this->table_name));
    }

    /**
     * Auto-map products by matching SKUs
     *
     * @return array Results with 'mapped' and 'skipped' counts
     */
    public function auto_map_by_sku() {
        global $wpdb;

        $results = [
            'mapped' => 0,
            'skipped' => 0,
            'remaining' => 0,
            'details' => [],
        ];

        $unmapped_query = "
            SELECT p.ID, pm.meta_value as sku
            FROM " . $wpdb->posts . " p
            INNER JOIN " . $wpdb->postmeta . " pm ON p.ID = pm.post_id AND pm.meta_key = '_sku'
            LEFT JOIN " . $this->table_name . " m ON p.ID = m.wc_product_id
            WHERE p.post_type IN ('product', 'product_variation')
            AND p.post_status = 'publish'
            AND pm.meta_value != ''
            AND pm.meta_value IS NOT NULL
            AND m.id IS NULL";

        // Process in bounded batches with a time guard so large catalogs
        // cannot run into PHP max_execution_time; re-running continues the job.
        $start_time = time();
        $batch_size = 500;

        do {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
            $wc_products = $wpdb->get_results($unmapped_query . $wpdb->prepare(' LIMIT %d', $batch_size), ARRAY_A);

            if (empty($wc_products)) {
                break;
            }

            $batch_mapped = 0;
            foreach ($wc_products as $product) {
                // Create mapping using WC SKU as DSZ SKU (assumes they match)
                $mapping_id = $this->map($product['ID'], $product['sku'], '');

                if ($mapping_id) {
                    $results['mapped']++;
                    $batch_mapped++;
                    if (count($results['details']) < 100) {
                        $results['details'][] = [
                            'wc_product_id' => $product['ID'],
                            'sku' => $product['sku'],
                            'status' => 'mapped',
                        ];
                    }
                } else {
                    $results['skipped']++;
                }
            }

            // Nothing mapped this batch: the same rows would be selected again
            if ($batch_mapped === 0) {
                break;
            }
        } while (count($wc_products) === $batch_size && (time() - $start_time) < 20);

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        $results['remaining'] = intval($wpdb->get_var("SELECT COUNT(*) FROM (" . $unmapped_query . ") t"));

        $this->logger->info('Auto-mapping completed', [
            'mapped' => $results['mapped'],
            'skipped' => $results['skipped'],
            'remaining' => $results['remaining'],
        ]);
        return $results;
    }

    /**
     * Remove mappings whose WooCommerce product no longer exists
     *
     * @return int Rows deleted
     */
    public function cleanup_orphaned() {
        global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        $deleted = $wpdb->query($wpdb->prepare(
            'DELETE m FROM %i m LEFT JOIN %i p ON m.wc_product_id = p.ID WHERE p.ID IS NULL',
            $this->table_name,
            $wpdb->posts
        ));

        if ($deleted) {
            $this->logger->info('Maintenance: removed orphaned mappings', ['deleted' => intval($deleted)]);
        }

        return intval($deleted);
    }

    /**
     * Update last synced timestamp (for price/stock sync)
     *
     * @param int $wc_product_id WooCommerce product ID
     * @return bool Success
     */
    public function update_last_synced($wc_product_id) {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return $wpdb->update(
            $this->table_name,
            ['last_synced' => current_time('mysql')],
            ['wc_product_id' => $wc_product_id],
            ['%s'],
            ['%d']
        ) !== false;
    }
    
    /**
     * Update last resynced timestamp (for full data resync)
     *
     * @param int $wc_product_id WooCommerce product ID
     * @return bool Success
     */
    public function update_last_resynced($wc_product_id) {
        global $wpdb;
        
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        $result = $wpdb->update(
            $this->table_name,
            ['last_resynced' => current_time('mysql')],
            ['wc_product_id' => $wc_product_id],
            ['%s'],
            ['%d']
        );
        
        $this->logger->debug('update_last_resynced called', [
            'wc_product_id' => $wc_product_id,
            'rows_updated' => $result,
            'last_error' => $wpdb->last_error,
        ]);
        
        return $result !== false && $result > 0;
    }

    /**
     * Search WooCommerce products for mapping
     *
     * @param string $search Search term
     * @param int    $limit  Limit
     * @return array Products
     */
    public function search_wc_products($search, $limit = 20) {
        global $wpdb;

        $like = '%' . $wpdb->esc_like($search) . '%';

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return $wpdb->get_results($wpdb->prepare(
            "SELECT p.ID, p.post_title, pm.meta_value as sku,
                    CASE WHEN m.id IS NOT NULL THEN 1 ELSE 0 END as is_mapped,
                    m.dsz_sku as mapped_to
             FROM %i p
             LEFT JOIN %i pm ON p.ID = pm.post_id AND pm.meta_key = '_sku'
             LEFT JOIN %i m ON p.ID = m.wc_product_id
             WHERE p.post_type IN ('product', 'product_variation')
             AND p.post_status = 'publish'
             AND (p.post_title LIKE %s OR pm.meta_value LIKE %s OR p.ID = %d)
             ORDER BY p.post_title ASC
             LIMIT %d",
            $wpdb->posts,
            $wpdb->postmeta,
            $this->table_name,
            $like,
            $like,
            intval($search),
            $limit
        ), ARRAY_A);
    }

    /**
     * Get unmapped WooCommerce products count
     *
     * @return int Count
     */
    public function get_unmapped_count() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        return (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT p.ID)
             FROM %i p
             INNER JOIN %i pm ON p.ID = pm.post_id AND pm.meta_key = '_sku'
             LEFT JOIN %i m ON p.ID = m.wc_product_id
             WHERE p.post_type IN ('product', 'product_variation')
             AND p.post_status = 'publish'
             AND pm.meta_value != ''
             AND pm.meta_value IS NOT NULL
             AND m.id IS NULL",
            $wpdb->posts,
            $wpdb->postmeta,
            $this->table_name
        ));
    }

    /**
     * Clear all mappings
     *
     * @return bool Success
     */
    public function clear_all() {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.SlowDBQuery.slow_db_query_meta_key, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is built from $wpdb->prefix and is not user input; all values are passed through prepare(). These are plugin-owned tables, so no core caching API applies.
        $result = $wpdb->query($wpdb->prepare("TRUNCATE TABLE %i", $this->table_name));
        if ($result !== false) {
            $this->logger->info('All mappings cleared');
        }
        return $result !== false;
    }
}