<?php
/**
 * Qixit Product Class
 * Handles product management with REST API integration
 * 
 * @package Qixit
 * @version 2.0.0
 */

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

class Qixit_Product {
    // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.NoCaching
    
    /**
     * Singleton instance
     */
    private static $instance = null;
    
    /**
     * API instance
     */
    private $api;
    
    /**
     * Logger instance
     */
    private $logger;
    
    /**
     * Post ID for instance-based usage
     */
    private $post_id = 0;
    
    /**
     * Product data for instance-based usage
     */
    private $product_data = null;

    /**
     * Decode cookie-backed purchased/session post lists safely.
     *
     * @param string $cookie_name Cookie key.
     * @return array<int> Sanitized post IDs.
     */
    private function get_cookie_post_ids($cookie_name) {
        $cookie_name = sanitize_key($cookie_name);
        if ($cookie_name === '') {
            return array();
        }

        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- JSON cookie payload requires raw read before decode.
        if (!isset($_COOKIE[$cookie_name])) {
            return array();
        }

        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- JSON cookie payload requires raw read before decode.
        $raw_cookie = sanitize_text_field(wp_unslash($_COOKIE[$cookie_name]));
        if ($raw_cookie === '') {
            return array();
        }
        $decoded = json_decode($raw_cookie, true);
        if (!is_array($decoded)) {
            return array();
        }

        $post_ids = array_map('absint', $decoded);
        $post_ids = array_filter($post_ids, function ($id) {
            return $id > 0;
        });

        return array_values(array_unique($post_ids));
    }
    
    /**
     * Get singleton instance (for static method calls)
     */
    public static function get_instance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    /**
     * Constructor - can be called with post_id for instance-based usage
     * 
     * @param int $post_id Optional post ID for instance methods
     */
    public function __construct($post_id = 0) {
        $this->api = Qixit_API::get_instance();
        $this->logger = Qixit_Logger::get_instance();
        
        if ($post_id > 0) {
            $this->post_id = $post_id;
            $this->load_product_data();
        }
    }
    
    /**
     * Load product data for the current post
     */
    private function load_product_data() {
        global $wpdb;

        if ($this->post_id <= 0) {
            return;
        }

        // Use WP object cache to avoid a DB hit on every page load.
        // Cache group '2waymicropay' is invalidated on product save/update.
        $cache_key = 'product_post_' . $this->post_id;
        $cached = wp_cache_get($cache_key, '2waymicropay');
        if ($cached !== false) {
            // wp_cache_get returns false on miss; null means "we cached a miss"
            $this->product_data = ($cached === 'null') ? null : $cached;
            return;
        }

        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted plugin table name from $wpdb->prefix.
        $this->product_data = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table_name} WHERE post_id = %d",
            $this->post_id
        ));

        // Cache the result. Store 'null' string for cache misses (non-premium posts)
        // so we can distinguish a cache miss from a null product.
        wp_cache_set($cache_key, $this->product_data === null ? 'null' : $this->product_data, '2waymicropay', 300);
    }
    
    /**
     * Check if this is a premium product (instance method)
     * 
     * @return bool True if premium
     */
    public function is_premium() {
        if (!$this->product_data) {
            return false;
        }
        
        return in_array($this->product_data->qixit_post_type, array(
            QIXIT_PREMIUM_PAY_ONCE_VIEW_ANYTIME,
            QIXIT_PREMIUM_PAY_PER_VIEW
        ));
    }
    
    /**
     * Get product cost (instance method)
     * 
     * @return float Product cost
     */
    public function get_cost() {
        if (!$this->product_data) {
            return 0.0;
        }
        
        return floatval($this->product_data->premium_post_cost);
    }
    
    /**
     * Get product type (instance method)
     * 
     * @return string Product type
     */
    public function get_type() {
        if (!$this->product_data) {
            return QIXIT_REG;
        }
        
        return $this->product_data->qixit_post_type;
    }
    
    /**
     * Get product GUID (instance method)
     * 
     * @return string Product GUID
     */
    public function get_guid() {
        if (!$this->product_data) {
            return '';
        }
        
        return $this->product_data->post_qixit_PID_guid ?: '';
    }
    
    /**
     * Get product ID (instance method)
     * 
     * @return int Product ID
     */
    public function get_product_id() {
        if (!$this->product_data) {
            return 0;
        }
        
        return intval($this->product_data->product_id);
    }
    
    /**
     * Get all product data (instance method)
     * 
     * @return object|null Product data
     */
    public function get_data() {
        return $this->product_data;
    }
    
    /**
     * Check if user has purchased this product (instance method)
     * 
     * Access duration is controlled by the access_hours field:
     * - access_hours = -1: Permanent access (forever, pay once)
     * - access_hours = -2: Pay per play (each view requires payment)
     * - access_hours = 0: Session only (cookie-based, until browser/tab closes)
     * - access_hours > 0: Time-limited access (N hours from purchase)
     * 
     * PRODUCTS PORTAL IS SOURCE OF TRUTH: If product has a GUID, we fetch
     * accessHours from Products Portal to ensure correct access control.
     * 
     * @param int $user_id User ID (0 for current user)
     * @return bool True if purchased (and still valid based on access_hours)
     */
    public function has_user_purchased($user_id = 0) {
        global $wpdb;
        
        $logger = Qixit_Logger::get_instance();
        
        if (!$this->product_data) {
            $logger->info("has_user_purchased: NO product_data for post_id={$this->post_id}");
            return false;
        }
        
        $product_id = $this->product_data->product_id;
        $logger->info("has_user_purchased: post_id={$this->post_id}, product_id={$product_id}");
        
        // Get access_hours from local database first
        $access_hours = isset($this->product_data->access_hours) ? 
                        intval($this->product_data->access_hours) : -1;
        
        // PRODUCTS PORTAL IS SOURCE OF TRUTH
        // If we have a GUID, fetch authoritative accessHours from Products Portal
        $guid = $this->get_guid();
        if (!empty($guid)) {
            $api = Qixit_API::get_instance();
            $api_product = $api->get_public_product($guid);
            
            if ($api_product['success'] && isset($api_product['data']['accessHours'])) {
                $portal_access_hours = intval($api_product['data']['accessHours']);
                
                // Log if there's a mismatch
                if ($portal_access_hours !== $access_hours) {
                    $logger->info("has_user_purchased: Products Portal access_hours={$portal_access_hours} differs from local={$access_hours}, using Portal value");
                    
                    // Update local database to sync with portal
                    $product_manager = Qixit_Product::get_instance();
                    $product_manager->update_local_product($product_id, array('access_hours' => $portal_access_hours));
                }
                
                $access_hours = $portal_access_hours;
            }
        }
        
        $logger->info("has_user_purchased: access_hours={$access_hours}");
        
        // Pay Per Play (access_hours = -2): NEVER owned - each view requires new payment
        if ($access_hours === -2) {
            $logger->info("has_user_purchased: Pay Per Play - returning FALSE");
            return false;
        }

        // Require active Qixit authentication.  The qixit_logged_in cookie is
        // set by the frontend JS on login/purchase and cleared on logout.
        // ALL users (including WordPress admins) must have this cookie for
        // purchased content to be revealed on the frontend.  WP admins manage
        // content from the admin panel; the public-facing page should reflect
        // the real Qixit auth state so publishers can test the visitor experience.
        if (empty($_COOKIE['qixit_logged_in']) || !qixit_visitor_session_is_valid()) {
            $logger->info("has_user_purchased: No valid qixit visitor session - returning FALSE");
            return false;
        }
        
        // Session-only access (access_hours = 0): Only check session cookie
        if ($access_hours === 0) {
            // Check for session cookie (set without expiration = session cookie)
            if (isset($_COOKIE['qixit_session_posts'])) {
                $session_posts = $this->get_cookie_post_ids('qixit_session_posts');
                if (is_array($session_posts) && in_array($this->post_id, $session_posts)) {
                    $logger->info("has_user_purchased: Found in session cookie - returning TRUE");
                    return true;
                }
            }
            $logger->info("has_user_purchased: Session-only, not in cookie - returning FALSE");
            return false; // Session-only: no database check, just cookie
        }
        
        // Check for post_id in cookie first (works for both logged in and guest)
        if (isset($_COOKIE['qixit_premium_posts'])) {
            $purchased_posts = $this->get_cookie_post_ids('qixit_premium_posts');
            $logger->info("has_user_purchased: premium_posts cookie=" . json_encode($purchased_posts));
            if (is_array($purchased_posts) && in_array($this->post_id, $purchased_posts)) {
                // Cookie is sufficient for all non-session access
                // The cookie itself has the correct expiration time
                // (access_hours * 3600 seconds for time-limited, or QIXIT_DEFAULT_COOKIE_DAYS for forever)
                $logger->info("has_user_purchased: Found in premium cookie - returning TRUE");
                return true;
            }
        } else {
            $logger->info("has_user_purchased: No premium_posts cookie set");
        }
        
        // Get user ID
        if ($user_id === 0) {
            $user_id = get_current_user_id();
        }
        
        $logger->info("has_user_purchased: wp_user_id={$user_id}");
        
        // Also check for guest purchaser cookie
        // phpcs:ignore WordPress.Security.ValidatedSanitizedInput.InputNotSanitized -- cookie value is sanitized before DB use.
        $guest_id = isset($_COOKIE['qixit_purchaser']) ? sanitize_text_field(wp_unslash($_COOKIE['qixit_purchaser'])) : '';
        $logger->info("has_user_purchased: guest_id={$guest_id}");
        
        $table_name = esc_sql($wpdb->prefix . QIXIT_PAYMENT_DETAILS);
        
        // Check database for WordPress user
        if ($user_id > 0) {
            if ($access_hours > 0) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is derived from $wpdb->prefix and escaped with esc_sql.
                $sql = $wpdb->prepare(
                    "SELECT COUNT(*) FROM `{$table_name}` 
                     WHERE product_id = %d AND wp_user_id = %d AND payment_for = 'view_post'
                     AND date_purchased >= DATE_SUB(NOW(), INTERVAL %d HOUR)",
                    $product_id,
                    $user_id,
                    $access_hours
                );
            } else {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is derived from $wpdb->prefix and escaped with esc_sql.
                $sql = $wpdb->prepare(
                    "SELECT COUNT(*) FROM `{$table_name}` 
                     WHERE product_id = %d AND wp_user_id = %d AND payment_for = 'view_post'",
                    $product_id,
                    $user_id
                );
            }
            $logger->info("has_user_purchased: SQL=" . $sql);
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery -- $sql is generated via $wpdb->prepare() branches above.
            $count = $wpdb->get_var($sql);
            $logger->info("has_user_purchased: DB count={$count}");
            
            if ($count > 0) {
                $logger->info("has_user_purchased: Found in DB - returning TRUE");
                return true;
            }
        }
        
        // Check database for guest purchaser
        if (!empty($guest_id)) {
            if ($access_hours > 0) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is derived from $wpdb->prefix and escaped with esc_sql.
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted plugin table name from $wpdb->prefix.
                $count = $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(*) FROM `{$table_name}` 
                     WHERE product_id = %d AND qixit_user_id = %s AND payment_for = 'view_post'
                     AND date_purchased >= DATE_SUB(NOW(), INTERVAL %d HOUR)",
                    $product_id,
                    $guest_id,
                    $access_hours
                ));
            } else {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name is derived from $wpdb->prefix and escaped with esc_sql.
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted plugin table name from $wpdb->prefix.
                $count = $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(*) FROM `{$table_name}` 
                     WHERE product_id = %d AND qixit_user_id = %s AND payment_for = 'view_post'",
                    $product_id,
                    $guest_id
                ));
            }
            
            if ($count > 0) {
                $logger->info("has_user_purchased: Found guest in DB - returning TRUE");
                return true;
            }
        }
        
        // For permanent access products, also check the Products Portal API
        // This handles cases where the purchase was made but the redirect back failed
        if ($access_hours === -1 && !empty($guest_id)) {
            $api_owned = $this->check_ownership_via_api($guest_id);
            if ($api_owned) {
                // Record the purchase locally so we don't have to check API every time
                $this->record_purchase($guest_id, $this->get_cost(), 'api_verified');
                $logger->info("has_user_purchased: API verified - returning TRUE");
                return true;
            }
        }
        
        $logger->info("has_user_purchased: Not found anywhere - returning FALSE");
        return false;
    }
    
    /**
     * Check product ownership via Products Portal API
     * 
     * @param string $qixit_user_id The Qixit user ID (GUID)
     * @return bool True if user owns the product according to the API
     */
    private function check_ownership_via_api($qixit_user_id) {
        if (!$this->product_data || empty($this->product_data->qixit_PID_guid)) {
            return false;
        }
        
        try {
            $api = Qixit_API::get_instance();
            $product_guid = $this->product_data->qixit_PID_guid;
            
            // Call the check-ownership endpoint
            $response = $api->check_ownership($product_guid, $qixit_user_id);
            
            if ($response && isset($response['success']) && $response['success']) {
                if (isset($response['data']['owned']) && $response['data']['owned']) {
                    $logger = Qixit_Logger::get_instance();
                    $logger->info("API verified ownership for product {$product_guid}, user {$qixit_user_id}");
                    return true;
                }
            }
        } catch (Exception $e) {
            // Log but don't fail - API might be unavailable
            $logger = Qixit_Logger::get_instance();
            $logger->error("API ownership check failed: " . $e->getMessage());
        }
        
        return false;
    }
    
    /**
     * Record a purchase (instance method)
     * 
     * @param int $user_id User ID
     * @param float $amount Amount paid
     * @return bool Success
     */
    public function record_purchase($user_id, $amount, $txn_id = '') {
        global $wpdb;
        
        if (!$this->product_data) {
            return false;
        }
        
        // Determine if user_id is WordPress user or Qixit user
        $wp_user_id = 0;
        $qixit_user_id = '';
        
        // Prefer stable per-vendor buyer key when the platform sends qixit_buyer_key (survives rename).
        // Fall back to legacy qixit_user (account GUID today) for older platform builds.
        // The return URL is an external payment callback and cannot carry a WordPress nonce;
        // callers accept it only after portal transaction verification.
        $qixit_user_id = '';
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- external payment callback URL cannot carry a WP nonce.
        if (!empty($_GET['qixit_buyer_key'])) {
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- external payment callback URL cannot carry a WP nonce.
            $qixit_user_id = sanitize_text_field(wp_unslash($_GET['qixit_buyer_key']));
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- external payment callback URL cannot carry a WP nonce.
        } elseif (!empty($_REQUEST['qixit_buyer_key'])) {
            // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- external payment callback URL cannot carry a WP nonce.
            $qixit_user_id = sanitize_text_field(wp_unslash($_REQUEST['qixit_buyer_key']));
        }

        if (is_numeric($user_id) && $user_id > 0) {
            $wp_user_id = intval($user_id);
        } elseif ($qixit_user_id === '') {
            $qixit_user_id = sanitize_text_field($user_id);
        }
        
        $data = array(
            'product_id' => $this->product_data->product_id,
            'qixit_PID_guid' => $this->get_guid(),
            'wp_user_id' => $wp_user_id,
            'qixit_user_id' => $qixit_user_id,
            'total' => $amount,
            'payment_for' => 'view_post',
            'type' => 'P',
            'txn_id' => $txn_id,
            'date_purchased' => current_time('mysql')
        );
        
        $result = Qixit_Payment::get_instance()->record_payment($data);
        
        // Get access_hours from product (-1 = forever, 0 = session, N = hours)
        $access_hours = isset($this->product_data->access_hours) ? 
                        intval($this->product_data->access_hours) : -1;
        
        // Session-only access (access_hours = 0): Use session cookie
        if ($access_hours === 0) {
            $session_posts = $this->get_cookie_post_ids('qixit_session_posts');
            $session_posts[] = $this->post_id;
            $session_posts = array_unique($session_posts);
            // Session cookie (no expiration = expires when browser closes)
            setcookie('qixit_session_posts', json_encode($session_posts), 0, '/');
        } else {
            // Permanent or time-limited: Use expiring cookie
            $purchased_posts = $this->get_cookie_post_ids('qixit_premium_posts');
            $purchased_posts[] = $this->post_id;
            $purchased_posts = array_unique($purchased_posts);
            
            // Cookie duration based on access_hours
            if ($access_hours < 0) {
                // Forever: use default cookie days
                $cookie_seconds = intval(QIXIT_DEFAULT_COOKIE_DAYS) * 86400;
            } else {
                // Time-limited: access_hours in seconds
                $cookie_seconds = $access_hours * 3600;
            }
            
            setcookie('qixit_premium_posts', json_encode($purchased_posts), time() + $cookie_seconds, '/');
        }
        
        return $result !== false;
    }
    
    /**
     * Get product by post ID
     * 
     * @param int $post_id WordPress post ID
     * @return object|null Product object or null
     */
    public function get_product_by_post_id($post_id) {
        global $wpdb;
        
        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted plugin table name from $wpdb->prefix.
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table_name} WHERE post_id = %d",
            $post_id
        ));
    }
    
    /**
     * Get product by GUID
     * 
     * @param string $guid Product GUID
     * @return object|null Product object or null
     */
    public function get_product_by_guid($guid) {
        global $wpdb;
        
        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted plugin table name from $wpdb->prefix.
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table_name} WHERE post_qixit_PID_guid = %s",
            $guid
        ));
    }
    
    /**
     * Get product by legacy PID
     * 
     * @param string $pid Legacy product ID
     * @return object|null Product object or null
     */
    public function get_product_by_legacy_pid($pid) {
        global $wpdb;
        
        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted plugin table name from $wpdb->prefix.
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table_name} WHERE post_qixit_PID = %s",
            $pid
        ));
    }
    
    /**
     * Update product type
     * 
     * @param int $post_id Post ID
     * @param string $type New type
     * @return bool Success
     */
    public function update_product_type($post_id, $type) {
        global $wpdb;
        
        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
        return $wpdb->update(
            $table_name,
            array('qixit_post_type' => $type, 'date_updated' => current_time('mysql')),
            array('post_id' => $post_id)
        ) !== false;
    }
    
    /**
     * Update local product
     * 
     * @param int $product_id Product ID
     * @param array $data Product data
     * @return bool Success
     */
    public function update_local_product($product_id, $data) {
        global $wpdb;
        
        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        $data['date_updated'] = current_time('mysql');
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
        $result = $wpdb->update($table_name, $data, array('product_id' => $product_id));
        
        // Log any database errors
        if ($result === false) {
            $this->logger->error("Failed to update product {$product_id}: " . $wpdb->last_error);
            $this->logger->error("Data attempted: " . wp_json_encode($data));
        } else {
            $this->logger->info("Successfully updated product {$product_id}, rows affected: {$result}");
        }

        // Invalidate object cache for the post that owns this product
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- table name is derived from $wpdb->prefix and controlled plugin constant.
        $product_row = $wpdb->get_row($wpdb->prepare("SELECT post_id FROM {$table_name} WHERE product_id = %d", $product_id));
        if ($product_row && !empty($product_row->post_id)) {
            wp_cache_delete('product_post_' . $product_row->post_id, '2waymicropay');
        }
        
        return $result !== false;
    }
    
    /**
     * Create product for post
     * 
     * @param int $post_id Post ID
     * @param array $data Product data
     * @return int|false Product ID or false
     */
    public function create_product_for_post($post_id, $data) {
        global $wpdb;
        
        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        
        $data['post_id'] = $post_id;
        $data['date_created'] = current_time('mysql');
        $data['date_updated'] = current_time('mysql');
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
        $result = $wpdb->insert($table_name, $data);

        if ($result) {
            $this->logger->log("Created local product for post {$post_id}");
            // Bust the object cache so the next request reads the new row
            wp_cache_delete('product_post_' . $post_id, '2waymicropay');
            return $wpdb->insert_id;
        }

        return false;
    }
    
    /**
     * Sync product to API
     * 
     * @param int $post_id Post ID
     * @return array Result with success status
     */
    public function sync_product_to_api($post_id = null) {
        try {
            return $this->sync_product_to_api_internal($post_id);
        } catch (\Throwable $e) {
            if ($this->logger) {
                $this->logger->error('sync_product_to_api exception: ' . $e->getMessage());
            }
            return array(
                'success' => false,
                'error' => __('Sync failed unexpectedly. Try Save & Sync again.', '2waymicropay'),
            );
        }
    }

    /**
     * Internal sync implementation (throws are caught by sync_product_to_api).
     *
     * @param int|null $post_id Post ID
     * @return array Result with success status
     */
    private function sync_product_to_api_internal($post_id = null) {
        // Allow calling without post_id if using instance method
        if ($post_id === null && $this->post_id) {
            $post_id = $this->post_id;
        }
        
        $product = $this->get_product_by_post_id($post_id);
        
        if (!$product) {
            return array('success' => false, 'error' => __('Product not found', '2waymicropay'));
        }
        
        $post = get_post($post_id);
        if (!$post) {
            return array('success' => false, 'error' => __('Post not found', '2waymicropay'));
        }

        $share_comments_with_authors = boolval(get_option('qixit_share_comment_revenue_with_authors', false));
        $comment_share_percent = intval(get_option('qixit_comment_author_share_percent', 0));
        $comment_share_percent = max(0, min(100, $comment_share_percent));
        $share_author_id = intval($post->post_author);
        $share_guid_meta_key = '';
        $use_author_comment_share = false;
        $author_comment_split = array();
        $author_label = '';
        if ($share_comments_with_authors && $comment_share_percent > 0 && class_exists('Qixit_Author_Commerce') && $share_author_id > 0) {
            $author_comment_split = Qixit_Author_Commerce::get_instance()->get_split_payload_for_post($post_id, $comment_share_percent);
            if (!empty($author_comment_split)) {
                $use_author_comment_share = true;
                $share_guid_meta_key = '_qixit_comments_share_guid_author_' . $share_author_id;
                $author_obj = get_user_by('id', $share_author_id);
                $author_label = $author_obj ? $author_obj->display_name : ('Author ' . $share_author_id);
            }
        }
        
        // Check if this is a free post (QIXIT_REG) - only sync comments product if enabled
        $is_free_post = (!isset($product->qixit_post_type) || $product->qixit_post_type === QIXIT_REG);
        $has_paid_comments = isset($product->enable_paid_comments) && $product->enable_paid_comments;
        
        // For free posts with paid comments, only sync the comments product
        if ($is_free_post && $has_paid_comments) {
            $this->logger->info("Free post {$post_id} with paid comments - syncing comments product only");
            $result = $this->sync_comments_product_to_api($post_id, $product);
            return $result;
        }
        
        // For free posts without paid comments, nothing to sync
        if ($is_free_post) {
            return array('success' => true, 'message' => __('Free post - no sync needed', '2waymicropay'));
        }
        
        // Prepare API data for premium content
        // access_hours: -1 = forever, 0 = session only, N = hours
        $access_hours = isset($product->access_hours) ? intval($product->access_hours) : -1;
        
        // Build delivery URL based on access type
        // For session-only (access_hours = 0), use a time-limited callback instead of permanent qixit_verified
        // This prevents bookmarking the unlock URL from bypassing session-only restrictions
        if ($access_hours === 0) {
            $callback_token = function_exists('qixit_issue_callback_token')
                ? qixit_issue_callback_token('session_unlock', array('post_id' => intval($post_id)))
                : '';
            // Session-only: use qixit_session_callback with timestamp
            // The WordPress plugin will verify the timestamp is recent (within 60 seconds)
            $delivery_url = add_query_arg(array(
                'qixit_session_callback' => '1',
                'post_id' => $post_id,
                'qixit_cb' => $callback_token,
                'qixit_ts' => '(timestamp)',  // API placeholder for purchase timestamp
                'qixit_user' => '(userid)'    // API placeholder for user ID
            ), get_permalink($post_id));
        } else {
            $callback_token = function_exists('qixit_issue_callback_token')
                ? qixit_issue_callback_token('post_unlock', array('post_id' => intval($post_id)))
                : '';
            // Permanent or time-limited: use standard qixit_verified flow
            $delivery_url = add_query_arg(array(
                'qixit_unlock' => '1',
                'qixit_verified' => '1',
                'post_id' => $post_id,
                'qixit_cb' => $callback_token,
                'qixit_user' => '(userid)'  // API placeholder for user ID
            ), get_permalink($post_id));
        }
        
        // Get commercial category from product or default from settings
        $commercial_category = isset($product->commercial_category) ? $product->commercial_category : '';
        if (empty($commercial_category)) {
            $commercial_category = get_option('qixit_default_commercial_category', 'CONTENT');
        }
        
        // Build ReportUrl for server-to-server purchase notifications
        // This is called by Products Portal after each successful purchase
        $report_url = admin_url('admin-ajax.php?action=qixit_purchase_report');
        
        $this->logger->info("sync_product_to_api: Building API data with reportUrl = {$report_url}");

        // Resolve author revenue split payload (affiliate) when configured.
        $author_split_percent = isset($product->percent_to_author) ? intval($product->percent_to_author) : 0;
        if ($author_split_percent <= 0) {
            $author_split_percent = intval(get_option('qixit_author_default_split_percent', 0));
        }
        $author_split = array();
        if (class_exists('Qixit_Author_Commerce')) {
            $author_split = Qixit_Author_Commerce::get_instance()
                ->get_split_payload_for_post($post_id, $author_split_percent);
        }
        if ($author_split_percent > 0 && empty($author_split)) {
            $this->logger->warning("sync_product_to_api: split configured ({$author_split_percent}%) but no author Qixit mapping found for post {$post_id}");
        }
        
        $api_data = array(
            'description' => $post->post_title,
            'cost' => floatval($product->premium_post_cost),
            'pitchUrl' => get_permalink($post_id),
            'deliveryUrl' => $delivery_url,
            'commercialCode' => $commercial_category,
            'available' => 1,
            'newBrowser' => 0,  // Don't open in new browser - redirect in same window
            'accessHours' => $access_hours,  // Sync access duration to API
            'reportUrl' => $report_url  // Server-to-server callback for purchase tracking
        );
        if (function_exists('qixit_price_opt_section_for_post') && function_exists('qixit_apply_price_opt_section_to_api_data')) {
            $api_data = qixit_apply_price_opt_section_to_api_data($api_data, qixit_price_opt_section_for_post($post_id));
        }
        if (!empty($author_split)) {
            $api_data = array_merge($api_data, $author_split);
        }
        if (function_exists('qixit_apply_forwarder_to_api_data')) {
            $api_data = qixit_apply_forwarder_to_api_data($api_data);
        }
        $this->logger->info(
            'sync_product_to_api: affiliate payload post_id=' . $post_id .
            ', affiliateUserId=' . (isset($author_split['affiliateUserId']) ? $author_split['affiliateUserId'] : '') .
            ', affiliatePercentage=' . (isset($author_split['affiliatePercentage']) ? intval($author_split['affiliatePercentage']) : 0) .
            ', forwardPercentage=' . (isset($api_data['forwardPercentage']) ? intval($api_data['forwardPercentage']) : 0)
        );
        
        $this->logger->info("sync_product_to_api: Full API data = " . json_encode($api_data));
        
        $need_create = true;
        $result = array('success' => false);
        
        if (!empty($product->post_qixit_PID_guid)) {
            // Try to update existing product
            $this->logger->info("Attempting to update existing product: {$product->post_qixit_PID_guid}");
            $result = $this->api->update_product($product->post_qixit_PID_guid, $api_data);
            
            if ($result['success']) {
                $need_create = false;
                $this->logger->info("Successfully updated product: {$product->post_qixit_PID_guid}");
            } else {
                // Check if product not found (404) - GUID is stale
                $status_code = isset($result['status_code']) ? $result['status_code'] : 0;
                $error_text = strtolower((string)($result['error'] ?? ''));
                $is_not_found = ($status_code === 404 || strpos($error_text, 'not found') !== false);
                $is_auth_rebind_case = (
                    in_array($status_code, array(401, 403), true) ||
                    strpos($error_text, 'authorization failed') !== false ||
                    strpos($error_text, 'invalid_api_key') !== false ||
                    strpos($error_text, 'invalid api key') !== false
                );
                if ($is_not_found || $is_auth_rebind_case) {
                    $this->logger->info("Product not found on API (stale GUID), clearing and will create new");
                    // Clear the stale GUID from local database
                    $this->update_local_product($product->product_id, array(
                        'post_qixit_PID_guid' => ''
                    ));
                    // Set admin notice about stale GUID being purged
                    set_transient('qixit_stale_guid_purged_' . $post_id, true, 60);
                } else {
                    $this->logger->info("Update failed for other reason: " . json_encode($result));
                    // Don't create new, return the error
                    $need_create = false;
                }
            }
        }
        
        if ($need_create) {
            // Create new product
            $this->logger->info("Creating new product for post {$post_id}");
            $result = $this->api->create_product($api_data);
            
            if ($result['success'] && isset($result['data']['productKeyId'])) {
                // Save the new GUID
                $this->update_local_product($product->product_id, array(
                    'post_qixit_PID_guid' => $result['data']['productKeyId'],
                    'migration_status' => 'migrated'
                ));
                $this->logger->info("Created new product: {$result['data']['productKeyId']}");
            } else {
                $this->logger->info("Failed to create product: " . json_encode($result));
            }
        }
        
        // Also sync comments product if paid comments are enabled
        if (isset($product->enable_paid_comments) && $product->enable_paid_comments) {
            $this->sync_comments_product_to_api($post_id, $product);
        }
        
        return $result;
    }
    
    /**
     * Sync comments product to API
     * Creates a separate product for paid comments with the correct delivery URL
     * 
     * @param int $post_id Post ID
     * @param object $product Product data (optional, will be fetched if not provided)
     * @return array Result with success status
     */
    public function sync_comments_product_to_api($post_id, $product = null) {
        // Fetch product if not provided
        if ($product === null) {
            $product = $this->get_product_by_post_id($post_id);
            if (!$product) {
                return array('success' => false, 'error' => __('Product not found', '2waymicropay'));
            }
        }
        global $wpdb;
        
        $post = get_post($post_id);
        if (!$post) {
            return array('success' => false, 'error' => __('Post not found', '2waymicropay'));
        }

        // Recompute author comment-share context in this function scope so
        // affiliate payload is always available during comments product sync.
        $share_comments_with_authors = boolval(get_option('qixit_share_comment_revenue_with_authors', false));
        $comment_share_percent = intval(get_option('qixit_comment_author_share_percent', 0));
        $comment_share_percent = max(0, min(100, $comment_share_percent));
        $share_author_id = intval($post->post_author);
        $share_guid_meta_key = '';
        $use_author_comment_share = false;
        $author_comment_split = array();
        $author_label = '';
        if ($share_comments_with_authors && $comment_share_percent > 0 && class_exists('Qixit_Author_Commerce') && $share_author_id > 0) {
            $author_comment_split = Qixit_Author_Commerce::get_instance()->get_split_payload_for_post($post_id, $comment_share_percent);
            if (!empty($author_comment_split)) {
                $use_author_comment_share = true;
                $author_obj = get_user_by('id', $share_author_id);
                $author_label = $author_obj ? $author_obj->display_name : ('Author ' . $share_author_id);
            } else {
                $this->logger->warning("sync_comments_product_to_api: comment share configured ({$comment_share_percent}%) but no author Qixit mapping found for post {$post_id}");
            }
        }
        
        // Build comment-specific delivery URL with payment confirmation parameters
        // The qixit_user parameter will be appended by the Products Portal when redirecting
        $delivery_url = add_query_arg(array(
            'qixit_comment_paid' => '1',
            'post_id' => $post_id
        ), get_permalink($post_id) . '#respond');
        
        // Get comments cost
        $comments_cost = isset($product->premium_comments_cost) ? floatval($product->premium_comments_cost) : 0.01;
        if ($comments_cost < 0.01) {
            $comments_cost = 0.01;  // Minimum 1 cent
        }
        
        // Get commercial category from product or default from settings
        $commercial_category = isset($product->commercial_category) ? $product->commercial_category : '';
        if (empty($commercial_category)) {
            $commercial_category = get_option('qixit_default_commercial_category', 'CONTENT');
        }
        
        // Build ReportUrl for server-to-server purchase notifications
        $report_url = admin_url('admin-ajax.php?action=qixit_purchase_report');
        
        $description = $use_author_comment_share
            /* translators: %s: author display label */
            ? sprintf(__('Paid Comments Share for %s', '2waymicropay'), $author_label)
            : __('Paid Comments (Site-wide)', '2waymicropay');

        $api_data = array(
            'description' => $description,
            'cost' => $comments_cost,
            'pitchUrl' => get_permalink($post_id) . '#respond',
            'deliveryUrl' => $delivery_url,
            'commercialCode' => $commercial_category,
            'available' => 1,
            'newBrowser' => 0,  // Redirect in same window to return to comment form
            'accessHours' => -2,  // Pay-per-play: each comment requires payment
            'reportUrl' => $report_url  // Server-to-server callback for purchase tracking
        );
        if (function_exists('qixit_price_opt_section_fixed') && function_exists('qixit_apply_price_opt_section_to_api_data')) {
            $api_data = qixit_apply_price_opt_section_to_api_data($api_data, qixit_price_opt_section_fixed('paid_comments'));
        }
        if ($use_author_comment_share && !empty($author_comment_split)) {
            $api_data = array_merge($api_data, $author_comment_split);
        }
        if (function_exists('qixit_apply_forwarder_to_api_data')) {
            $api_data = qixit_apply_forwarder_to_api_data($api_data);
        }
        $this->logger->info(
            'sync_comments_product_to_api: affiliate payload post_id=' . $post_id .
            ', enabled=' . ($use_author_comment_share ? '1' : '0') .
            ', affiliateUserId=' . (isset($author_comment_split['affiliateUserId']) ? $author_comment_split['affiliateUserId'] : '') .
            ', affiliatePercentage=' . (isset($author_comment_split['affiliatePercentage']) ? intval($author_comment_split['affiliatePercentage']) : 0)
        );
        
        $existing_guid = $this->get_comments_shared_guid($use_author_comment_share, $share_author_id);
        
        $this->logger->info("sync_comments_product_to_api: post_id={$post_id}, product_id=" . ($product->product_id ?? 'NULL') . ", existing_guid=" . ($existing_guid ?: 'NONE'));
        
        $need_create = true;
        $result = array('success' => false);
        
        // If we have an existing GUID, try to update it first
        if (!empty($existing_guid)) {
            $this->logger->info("Attempting to update existing comments product: {$existing_guid}");
            $result = $this->api->update_product($existing_guid, $api_data);
            
            if ($result['success']) {
                $this->logger->info("Successfully updated comments product: {$existing_guid}");
                $this->set_comments_shared_guid($use_author_comment_share, $share_author_id, $existing_guid);
                $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
                $wpdb->update(
                    $table_name,
                    array('comments_qixit_PID_guid' => $existing_guid),
                    array('post_id' => $post_id)
                );
                $need_create = false;
            } else {
                // Check if product not found (404) - GUID is stale
                $status_code = isset($result['status_code']) ? $result['status_code'] : 0;
                $is_not_found = ($status_code === 404 || strpos(strtolower($result['error'] ?? ''), 'not found') !== false);
                
                if ($is_not_found) {
                    // Product was deleted from API - clear stale GUID and create new
                    $this->logger->info("Comments product not found (404/stale GUID), clearing and creating new");
                    $this->set_comments_shared_guid($use_author_comment_share, $share_author_id, '');
                    
                    $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
                    $wpdb->update(
                        $table_name,
                        array('comments_qixit_PID_guid' => ''),
                        array('post_id' => $post_id)
                    );
                    $this->logger->info("Purged stale comments GUID for post {$post_id}");
                    
                    // Set admin notice about stale GUID being purged
                    set_transient('qixit_stale_comments_guid_purged_' . $post_id, true, 60);
                } else {
                    // Keep canonical GUID on transient/non-404 failures to avoid duplicate products.
                    $this->logger->info("Update failed (error: " . json_encode($result) . "), retaining existing comments GUID");
                    $need_create = false;
                }
            }
        }
        
        // Create new comments product if needed
        if ($need_create) {
            $this->logger->info("Creating new comments product for post {$post_id}");
            $result = $this->api->create_product($api_data);
            
            $this->logger->info("API create_product result: " . json_encode($result));
            
            if ($result['success'] && isset($result['data']['productKeyId'])) {
                $new_guid = $result['data']['productKeyId'];
                
                // Save the new comments GUID
                $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
                
                // Try update by product_id first
                $update_result = false;
                if (!empty($product->product_id)) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
                    $update_result = $wpdb->update(
                        $table_name,
                        array('comments_qixit_PID_guid' => $new_guid),
                        array('product_id' => $product->product_id)
                    );
                    $this->logger->info("Updated by product_id {$product->product_id}: result=" . wp_json_encode($update_result));
                }
                
                // If that didn't work, try update by post_id
                if ($update_result === false || $update_result === 0) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
                    $update_result = $wpdb->update(
                        $table_name,
                        array('comments_qixit_PID_guid' => $new_guid),
                        array('post_id' => $post_id)
                    );
                    $this->logger->info("Updated by post_id {$post_id}: result=" . wp_json_encode($update_result));
                }
                
                if ($update_result === false) {
                    $this->logger->info("DB update error: " . $wpdb->last_error);
                }

                $this->set_comments_shared_guid($use_author_comment_share, $share_author_id, $new_guid);
                
                $this->logger->info("Created comments product for post {$post_id}: {$new_guid}");
            } else {
                $this->logger->info("API create_product failed: " . json_encode($result));
            }
        }
        
        return $result;
    }

    /**
     * Resolve canonical comments product GUID for a post.
     * - Author-share mode: one GUID per author
     * - No-share mode: one site-wide GUID
     *
     * @param int $post_id
     * @param object|null $product
     * @return string
     */
    public function get_comments_purchase_guid_for_post($post_id, $product = null) {
        if ($product === null) {
            $product = $this->get_product_by_post_id($post_id);
        }
        $post = get_post($post_id);
        if (!$post) {
            return '';
        }

        $share_comments_with_authors = boolval(get_option('qixit_share_comment_revenue_with_authors', false));
        $comment_share_percent = intval(get_option('qixit_comment_author_share_percent', 0));
        $comment_share_percent = max(0, min(100, $comment_share_percent));
        $share_author_id = intval($post->post_author);
        $use_author_comment_share = false;

        if ($share_comments_with_authors && $comment_share_percent > 0 && class_exists('Qixit_Author_Commerce') && $share_author_id > 0) {
            $author_comment_split = Qixit_Author_Commerce::get_instance()->get_split_payload_for_post($post_id, $comment_share_percent);
            $use_author_comment_share = !empty($author_comment_split);
        }

        $guid = $this->get_comments_shared_guid($use_author_comment_share, $share_author_id);
        if (!empty($guid)) {
            return $guid;
        }

        return $product && !empty($product->comments_qixit_PID_guid) ? $product->comments_qixit_PID_guid : '';
    }

    /**
     * Canonical storage key for comments GUID.
     *
     * @param bool $use_author_comment_share
     * @param int $author_id
     * @return string
     */
    private function get_comments_shared_guid_option_key($use_author_comment_share, $author_id = 0) {
        if ($use_author_comment_share && $author_id > 0) {
            return 'qixit_comments_product_guid_author_' . intval($author_id);
        }
        return 'qixit_comments_product_guid_global';
    }

    /**
     * Get canonical comments GUID.
     *
     * @param bool $use_author_comment_share
     * @param int $author_id
     * @return string
     */
    private function get_comments_shared_guid($use_author_comment_share, $author_id = 0) {
        $key = $this->get_comments_shared_guid_option_key($use_author_comment_share, $author_id);
        return trim((string) get_option($key, ''));
    }

    /**
     * Set canonical comments GUID.
     *
     * @param bool $use_author_comment_share
     * @param int $author_id
     * @param string $guid
     * @return void
     */
    private function set_comments_shared_guid($use_author_comment_share, $author_id = 0, $guid = '') {
        $key = $this->get_comments_shared_guid_option_key($use_author_comment_share, $author_id);
        $guid = trim((string) $guid);
        if ($guid === '') {
            delete_option($key);
            return;
        }
        update_option($key, $guid, false);
    }
    
    /**
     * Create or update product via API
     * 
     * @param int $post_id Post ID
     * @param array $data Product data
     * @return array Result with success status
     */
    public function save($post_id, $data) {
        global $wpdb;
        
        $post = get_post($post_id);
        if (!$post) {
            return array('success' => false, 'error' => __('Post not found', '2waymicropay'));
        }
        
        // Get existing product
        $existing = $this->get_product_by_post_id($post_id);
        
        // Build default delivery URL with verification parameters
        $default_delivery_url = add_query_arg(array(
            'qixit_unlock' => '1',
            'qixit_verified' => '1',
            'post_id' => $post_id,
            'qixit_cb' => function_exists('qixit_issue_callback_token')
                ? qixit_issue_callback_token('post_unlock', array('post_id' => intval($post_id)))
                : '',
            'qixit_user' => '(userid)'  // API placeholder for user ID
        ), get_permalink($post_id));
        
        // Prepare API data
        $api_data = array(
            'description' => isset($data['description']) ? $data['description'] : $post->post_title,
            'cost' => isset($data['cost']) ? floatval($data['cost']) : floatval(QIXIT_DEFAULT_COST),
            'pitchUrl' => isset($data['pitchUrl']) ? $data['pitchUrl'] : get_permalink($post_id),
            'deliveryUrl' => isset($data['deliveryUrl']) ? $data['deliveryUrl'] : $default_delivery_url,
            'commercialCode' => isset($data['commercialCode']) ? $data['commercialCode'] : 'CONTENT',
            'affiliateUserId' => isset($data['affiliateUserId']) ? $data['affiliateUserId'] : '',
            'affiliatePercentage' => isset($data['affiliatePercentage']) ? intval($data['affiliatePercentage']) : 0,
            'refundMessage' => isset($data['refundMessage']) ? $data['refundMessage'] : __('Contact site admin for refund', '2waymicropay'),
            'available' => isset($data['available']) ? intval($data['available']) : 1,
            'newBrowser' => 0  // Redirect in same window to unlock content
        );
        if (function_exists('qixit_price_opt_section_for_post') && function_exists('qixit_apply_price_opt_section_to_api_data')) {
            $api_data = qixit_apply_price_opt_section_to_api_data($api_data, qixit_price_opt_section_for_post($post_id));
        }

        // Keep author split payload consistent on save/update operations.
        $author_split_percent = isset($data['percent_to_author'])
            ? intval($data['percent_to_author'])
            : intval(get_option('qixit_author_default_split_percent', 0));
        if ($author_split_percent > 0 && class_exists('Qixit_Author_Commerce')) {
            $author_split = Qixit_Author_Commerce::get_instance()
                ->get_split_payload_for_post($post_id, $author_split_percent);
            if (!empty($author_split)) {
                $api_data = array_merge($api_data, $author_split);
            }
        }
        if (function_exists('qixit_apply_forwarder_to_api_data')) {
            $api_data = qixit_apply_forwarder_to_api_data($api_data);
        }
        
        // Get existing GUID if any
        $existing_guid = $existing ? $existing->post_qixit_PID_guid : '';
        
        if (!empty($existing_guid)) {
            // Update existing product
            $result = $this->api->update_product($existing_guid, $api_data);
        } else {
            // Create new product
            $result = $this->api->create_product($api_data);
        }
        
        if ($result['success']) {
            // Save to local database
            $product_guid = isset($result['data']['productKeyId']) ? $result['data']['productKeyId'] : $existing_guid;
            
            $db_data = array(
                'post_id' => $post_id,
                'post_qixit_PID_guid' => $product_guid,
                'qixit_post_type' => isset($data['qixit_post_type']) ? $data['qixit_post_type'] : QIXIT_PREMIUM_PAY_ONCE_VIEW_ANYTIME,
                'premium_post_cost' => $api_data['cost'],
                'premium_post_url' => $api_data['pitchUrl'],
                'percent_to_author' => isset($data['percent_to_author']) ? floatval($data['percent_to_author']) : floatval(QIXIT_DEFAULT_PERCENT_TO_AUTHOR),
                'date_updated' => current_time('mysql'),
                'migration_status' => 'migrated'
            );
            
            $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
            
            if (!$existing) {
                // Insert new
                $db_data['date_created'] = current_time('mysql');
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
                $wpdb->insert($table_name, $db_data);
            } else {
                // Update existing
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
                $wpdb->update($table_name, $db_data, array('post_id' => $post_id));
            }
            
            $this->logger->log("Product saved successfully for post {$post_id}, GUID: {$product_guid}");
            
            return array(
                'success' => true,
                'product_guid' => $product_guid,
                'data' => $result['data']
            );
        }
        
        return $result;
    }
    
    /**
     * Delete product
     * 
     * @param int $post_id Post ID
     * @return array Result with success status
     */
    public function delete($post_id) {
        global $wpdb;
        
        $product = $this->get_product_by_post_id($post_id);
        
        if ($product && !empty($product->post_qixit_PID_guid)) {
            // Delete from API
            $result = $this->api->delete_product($product->post_qixit_PID_guid);
            
            if (!$result['success'] && (!isset($result['status_code']) || $result['status_code'] !== 404)) {
                // Only fail if it's not a "not found" error
                return $result;
            }
        }
        
        // Delete from local database
        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned products table.
        $wpdb->delete($table_name, array('post_id' => $post_id));
        
        // Delete payment history
        if ($product) {
            $table_name = esc_sql($wpdb->prefix . QIXIT_PAYMENT_DETAILS);
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- direct write to plugin-owned payments table.
            $wpdb->delete($table_name, array('product_id' => $product->product_id));
        }
        
        $this->logger->log("Product deleted for post {$post_id}");
        
        return array('success' => true);
    }
    
    /**
     * Get all products for migration
     * 
     * @param string $status Migration status (pending, migrated, failed)
     * @return array Products
     */
    public function get_products_for_migration($status = 'pending') {
        global $wpdb;
        
        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted plugin table name from $wpdb->prefix.
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM {$table_name} WHERE migration_status = %s ORDER BY product_id",
            $status
        ));
    }
    
    /**
     * Get migration statistics
     * 
     * @return array Statistics
     */
    public function get_migration_stats() {
        global $wpdb;
        
        $table_name = esc_sql($wpdb->prefix . QIXIT_PRODUCTS);
        
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- table name is derived from $wpdb->prefix and escaped with esc_sql.
        $total = $wpdb->get_var("SELECT COUNT(*) FROM `{$table_name}`");
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- table name is derived from $wpdb->prefix and escaped with esc_sql.
        $migrated = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `{$table_name}` WHERE migration_status = %s", 'migrated'));
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- table name is derived from $wpdb->prefix and escaped with esc_sql.
        $pending = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `{$table_name}` WHERE migration_status = %s", 'pending'));
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- table name is derived from $wpdb->prefix and escaped with esc_sql.
        $failed = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM `{$table_name}` WHERE migration_status = %s", 'failed'));
        
        return array(
            'total' => intval($total),
            'migrated' => intval($migrated),
            'pending' => intval($pending),
            'failed' => intval($failed)
        );
    }
    
    /**
     * Get purchase URL for a post (static usage with post_id parameter)
     * or for instance usage (no parameter, uses $this->post_id)
     * 
     * @param int|null $post_id Post ID (optional if using instance method)
     * @return string Purchase URL or empty string
     */
    public function get_purchase_url($post_id = null) {
        // Instance method usage - use loaded product data
        if ($post_id === null && $this->product_data) {
            $guid = $this->get_guid();
            
            // If no GUID, try to sync to get one
            if (empty($guid) && $this->is_premium()) {
                $this->logger->info("No GUID for post {$this->post_id}, attempting auto-sync");
                $result = $this->sync_product_to_api();
                if ($result['success'] && !empty($result['guid'])) {
                    $guid = $result['guid'];
                    $this->product_data->post_qixit_PID_guid = $guid;
                }
            }
            
            if (empty($guid)) {
                $this->logger->warning("Cannot generate purchase URL - no GUID for post {$this->post_id}");
                return '';
            }
            return $this->api->get_purchase_url($guid);
        }
        
        // Static usage - look up by post_id
        if ($post_id !== null) {
            $product = $this->get_product_by_post_id($post_id);
            
            // If no GUID, try to sync
            if ($product && empty($product->post_qixit_PID_guid)) {
                $temp_product = new Qixit_Product($post_id);
                if ($temp_product->is_premium()) {
                    $result = $temp_product->sync_product_to_api();
                    if ($result['success'] && !empty($result['guid'])) {
                        $product->post_qixit_PID_guid = $result['guid'];
                    }
                }
            }
            
            if (!$product || empty($product->post_qixit_PID_guid)) {
                return '';
            }
            
            return $this->api->get_purchase_url($product->post_qixit_PID_guid);
        }
        
        return '';
    }
    // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.NoCaching
}

// Initialize singleton
Qixit_Product::get_instance();