<?php

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

/**
 * Sales Tab for FlowDino WooCommerce
 *
 * Synchronises orders from external platforms (Vinted, Leboncoin, Etsy, eBay, …)
 * into WooCommerce via the FlowDino API.
 */
class FlowDino_WC_Sales_Tab {

    /** @var FlowDino_WC_API */
    private $api;

    /** Default date used when no sync has occurred yet */
    const DEFAULT_SYNC_DATE = '2025-11-01 00:00:00';

    /** FlowDino status → WooCommerce order-status mapping */
    private $status_map = array(
        'completed' => 'wc-completed',
        'pending'   => 'wc-processing',
        'cancelled' => 'wc-cancelled',
        'refunded'  => 'wc-refunded',
    );

    /**
     * Constructor
     */
    public function __construct() {
        require_once FLOWDINO_PLUGIN_PATH . 'includes/class-flowdino-wc-api.php';
        $this->api = new FlowDino_WC_API();
    }

    // =========================================================================
    // RENDER
    // =========================================================================

    /**
     * Render the Ventes tab
     */
    public function render() {
        $stats = $this->get_sales_stats();
        ?>
        <div class="flowdino-sales-tab">

            <!-- Sync section -->
            <div class="flowdino-card" style="margin-top:20px;">
                <h2 style="margin-top:0;">
                    <span class="dashicons dashicons-cart" style="vertical-align:middle;margin-right:6px;"></span>
                    <?php esc_html_e('Ventes depuis les plateformes FlowDino', 'flowdino'); ?>
                </h2>

                <div class="notice notice-info inline" style="background:#e0f7fa;border-left-color:#00acc1;padding:10px 14px;margin:0 0 16px;">
                    <span class="dashicons dashicons-info" style="color:#00acc1;vertical-align:middle;margin-right:6px;"></span>
                    <?php esc_html_e('Synchronisez vos ventes depuis diverses plateformes (Vinted, Leboncoin, Etsy, eBay, etc.) pour créer des commandes dans WooCommerce.', 'flowdino'); ?>
                </div>

                <button id="flowdino-sync-sales" class="button button-primary" style="background:#00acc1;border-color:#00acc1;">
                    <span class="dashicons dashicons-update" style="vertical-align:middle;margin-right:4px;"></span>
                    <?php esc_html_e('SYNCHRONISER LES VENTES', 'flowdino'); ?>
                </button>

                <div id="flowdino-sales-sync-result" style="margin-top:12px;"></div>
            </div>

            <!-- Stats section -->
            <div class="flowdino-card" style="margin-top:20px;">
                <h2 style="margin-top:0;">
                    <span class="dashicons dashicons-chart-bar" style="vertical-align:middle;margin-right:6px;"></span>
                    <?php esc_html_e('Statistiques de ventes par plateforme', 'flowdino'); ?>
                </h2>

                <?php if (empty($stats)) : ?>
                    <p><?php esc_html_e('Aucune donnée disponible. Lancez une synchronisation pour commencer.', 'flowdino'); ?></p>
                <?php else : ?>
                    <table class="wp-list-table widefat fixed striped" style="margin-top:10px;">
                        <thead>
                            <tr>
                                <th><?php esc_html_e('Plateforme', 'flowdino'); ?></th>
                                <th><?php esc_html_e('Total commandes', 'flowdino'); ?></th>
                                <th><?php esc_html_e('Montant total', 'flowdino'); ?></th>
                                <th><?php esc_html_e('Total frais', 'flowdino'); ?></th>
                                <th><?php esc_html_e('Montant net', 'flowdino'); ?></th>
                                <th><?php esc_html_e('Annonces', 'flowdino'); ?></th>
                                <th><?php esc_html_e('Vues', 'flowdino'); ?></th>
                                <th><?php esc_html_e('Dernière synchro', 'flowdino'); ?></th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $total_orders   = 0;
                            $total_amount   = 0.0;
                            $total_fees     = 0.0;
                            $total_listings = 0;
                            $total_views    = 0;

                            foreach ($stats as $row) :
                                $total_orders   += (int) $row['order_count'];
                                $total_amount   += (float) $row['total_amount'];
                                $total_fees     += (float) $row['total_fees'];
                                $total_listings += (int) $row['nb_annonces'];
                                $total_views    += (int) $row['nb_vues'];
                                $net = (float) $row['total_amount'] - (float) $row['total_fees'];
                            ?>
                            <tr>
                                <td>
                                    <?php if (!empty($row['logo_url'])) : ?>
                                        <img src="<?php echo esc_url($row['logo_url']); ?>"
                                             alt="<?php echo esc_attr($row['site_name']); ?>"
                                             style="height:22px;vertical-align:middle;margin-right:6px;">
                                    <?php else : ?>
                                        <strong><?php echo esc_html($row['site_name']); ?></strong>
                                    <?php endif; ?>
                                </td>
                                <td>
                                    <span style="background:#00acc1;color:#fff;border-radius:50%;display:inline-block;min-width:22px;text-align:center;padding:0 4px;">
                                        <?php echo esc_html($row['order_count']); ?>
                                    </span>
                                </td>
                                <td><?php echo esc_html(number_format((float) $row['total_amount'], 2, '.', '') . ' ' . get_woocommerce_currency_symbol()); ?></td>
                                <td><?php echo esc_html(number_format((float) $row['total_fees'], 2, '.', '') . ' ' . get_woocommerce_currency_symbol()); ?></td>
                                <td><strong><?php echo esc_html(number_format($net, 2, '.', '') . ' ' . get_woocommerce_currency_symbol()); ?></strong></td>
                                <td><?php echo esc_html($row['nb_annonces']); ?></td>
                                <td><?php echo esc_html($row['nb_vues']); ?></td>
                                <td><?php echo esc_html($row['last_sync'] !== self::DEFAULT_SYNC_DATE ? $row['last_sync'] : '—'); ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                        <tfoot>
                            <tr style="font-weight:bold;">
                                <td><?php esc_html_e('TOTAL', 'flowdino'); ?></td>
                                <td>
                                    <span style="background:#00acc1;color:#fff;border-radius:50%;display:inline-block;min-width:22px;text-align:center;padding:0 4px;">
                                        <?php echo esc_html($total_orders); ?>
                                    </span>
                                </td>
                                <td><?php echo esc_html(number_format($total_amount, 2, '.', '') . ' ' . get_woocommerce_currency_symbol()); ?></td>
                                <td style="color:#d32f2f;"><?php echo esc_html(number_format($total_fees, 2, '.', '') . ' ' . get_woocommerce_currency_symbol()); ?></td>
                                <td style="color:#388e3c;"><strong><?php echo esc_html(number_format($total_amount - $total_fees, 2, '.', '') . ' ' . get_woocommerce_currency_symbol()); ?></strong></td>
                                <td><?php echo esc_html($total_listings); ?></td>
                                <td><?php echo esc_html($total_views); ?></td>
                                <td>—</td>
                            </tr>
                        </tfoot>
                    </table>
                <?php endif; ?>
            </div>

        </div>
        <?php
    }

    // =========================================================================
    // STATS
    // =========================================================================

    /**
     * Build the stats array used by render().
     * Combines API site info with local DB aggregates.
     *
     * @return array
     */
    public function get_sales_stats() {
        $sites_response = $this->api->get_user_sites();
        if (empty($sites_response) || empty($sites_response['sites'])) {
            return array();
        }

        global $wpdb;
        $orders_table   = $wpdb->prefix . 'flowdino_orders';
        $sync_table     = $wpdb->prefix . 'flowdino_sales_sync';

        $stats = array();

        foreach ($sites_response['sites'] as $site) {
            $site_id = (int) $site['id'];

            // DB aggregates
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-specific aggregation on custom table
            $agg = $wpdb->get_row($wpdb->prepare(
                "SELECT COUNT(*) AS order_count,
                        COALESCE(SUM(amount), 0) AS total_amount,
                        COALESCE(SUM(fees), 0)   AS total_fees
                 FROM " . esc_sql($orders_table) . "
                 WHERE site_id = %d",
                $site_id
            ), ARRAY_A);

            // Last sync date
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-specific query on custom sync table
            $last_sync = $wpdb->get_var($wpdb->prepare(
                "SELECT last_sync_date FROM " . esc_sql($sync_table) . " WHERE site_id = %d",
                $site_id
            ));

            $stats[] = array(
                'site_id'      => $site_id,
                'site_name'    => $site['siteName'],
                'logo_url'     => $site['logoSite'] ?? '',
                'order_count'  => $agg ? (int) $agg['order_count']   : 0,
                'total_amount' => $agg ? (float) $agg['total_amount'] : 0.0,
                'total_fees'   => $agg ? (float) $agg['total_fees']   : 0.0,
                'nb_annonces'  => (int) ($site['nbAnnonces'] ?? 0),
                'nb_vues'      => (int) ($site['nbVues']     ?? 0),
                'last_sync'    => $last_sync ?: self::DEFAULT_SYNC_DATE,
            );
        }

        return $stats;
    }

    // =========================================================================
    // MAIN SYNC
    // =========================================================================

    /**
     * Synchronise orders for all active platforms.
     *
     * @return array { success, total, message, sites }
     */
    public function process_sync_sales() {
        $sites_response = $this->api->get_user_sites();
        if (empty($sites_response) || empty($sites_response['sites'])) {
            return array(
                'success' => false,
                'total'   => 0,
                'message' => __('Impossible de récupérer les plateformes FlowDino.', 'flowdino'),
                'sites'   => array(),
            );
        }

        $grand_total  = 0;
        $site_results = array();

        foreach ($sites_response['sites'] as $site) {
            if (empty($site['status'])) {
                continue; // Skip inactive platforms
            }

            $site_id   = (int) $site['id'];
            $site_name = sanitize_text_field($site['siteName']);

            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("FlowDino Sales: Starting sync for site {$site_name} (id={$site_id})");

            $last_sync_date = $this->get_last_sync_date($site_id);
            $page           = 1;
            $count          = 0;
            $latest_date    = $last_sync_date;

            do {
                $orders_response = $this->api->get_orders($site_id, $last_sync_date, $page, 100);

                if (empty($orders_response) || empty($orders_response['success'])) {
                    // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                    error_log("FlowDino Sales: API error on page {$page} for site {$site_id}");
                    break;
                }

                $items      = $orders_response['items']      ?? array();
                $pagination = $orders_response['pagination'] ?? array();

                foreach ($items as $order_data) {
                    $flowdino_order_id = (int) $order_data['id'];

                    try {
                        $existing_row = $this->get_order_by_flowdino_id($flowdino_order_id);

                        if ($existing_row) {
                            $this->update_wc_order((int) $existing_row->id_order, $order_data, $site);
                        } else {
                            // Fallback: check WC order meta in case the flowdino_orders table
                            // was cleared but WC orders still exist (prevents duplicates on resync).
                            $existing_wc_order_id = $this->find_wc_order_id_by_meta($flowdino_order_id);

                            if ($existing_wc_order_id) {
                                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                                error_log("FlowDino Sales: Duplicate prevented — WC order #{$existing_wc_order_id} already exists for FlowDino #{$flowdino_order_id}");
                                // Restore the cross-reference row so future syncs use the fast path.
                                $this->save_flowdino_order(array(
                                    'id_order'                => $existing_wc_order_id,
                                    'flowdino_order_id'       => $flowdino_order_id,
                                    'flowdino_transaction_id' => $order_data['transactionId'] ?? '',
                                    'site_id'                 => $site_id,
                                    'site_name'               => $site_name,
                                    'order_status'            => $order_data['status'] ?? 'pending',
                                    'amount'                  => (float) ($order_data['amountOrder'] ?? 0),
                                    'fees'                    => (float) ($order_data['feesOrder']   ?? 0),
                                    'currency'                => $order_data['currency'] ?? get_woocommerce_currency(),
                                    'date_order'              => $order_data['dateOrder'] ?? current_time('mysql'),
                                ));
                                $this->update_wc_order($existing_wc_order_id, $order_data, $site);
                            } else {
                                $this->create_wc_order($order_data, $site);
                                $count++;
                            }
                        }

                        // Track latest order date for next sync
                        if (!empty($order_data['updatedAt']) && $order_data['updatedAt'] > $latest_date) {
                            $latest_date = $order_data['updatedAt'];
                        }
                    } catch (Exception $e) {
                        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                        error_log("FlowDino Sales: Exception processing order {$flowdino_order_id} — " . $e->getMessage());
                    }
                }

                $current_page = (int) ($pagination['currentPage'] ?? $page);
                $total_pages  = (int) ($pagination['totalPages']  ?? 1);
                $page++;

            } while ($current_page < $total_pages);

            // Update last sync date for this site
            if ($latest_date !== $last_sync_date) {
                $this->update_last_sync_date($site_id, $latest_date);
            }

            $grand_total             += $count;
            $site_results[$site_name] = array(
                'success' => true,
                'count'   => $count,
                'message' => sprintf(
                    /* translators: 1: number of orders, 2: platform name */
                    __('%1$d commandes synchronisées depuis %2$s', 'flowdino'),
                    $count,
                    $site_name
                ),
            );
        }

        return array(
            'success' => true,
            'total'   => $grand_total,
            'message' => sprintf(
                /* translators: %d: total number of orders */
                __('%d commande(s) synchronisée(s) au total.', 'flowdino'),
                $grand_total
            ),
            'sites'   => $site_results,
        );
    }

    // =========================================================================
    // WC ORDER CREATION / UPDATE
    // =========================================================================

    /**
     * Create a new WooCommerce order from FlowDino order data.
     *
     * @param array $order_data FlowDino order item.
     * @param array $site       FlowDino site object.
     * @return int WooCommerce order ID, or 0 on failure.
     */
    private function create_wc_order(array $order_data, array $site) {
        $site_id   = (int) $site['id'];
        $site_name = sanitize_text_field($site['siteName']);

        $flowdino_order_id = (int) $order_data['id'];
        $order_ref         = 'FD-' . $site_id . '-' . ($order_data['orderId'] ?? $flowdino_order_id);

        // --- Customer info ---
        list($first_name, $last_name) = $this->split_full_name($order_data['fullName'] ?? ($order_data['pseudo'] ?? ''));
        $email = $this->resolve_email($order_data, $flowdino_order_id);

        // --- Create WC order ---
        $order = wc_create_order(array(
            'customer_id' => $this->find_customer_id_by_email($email),
            'created_via' => 'flowdino',
        ));

        if (is_wp_error($order)) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("FlowDino Sales: Failed to create WC order for FlowDino ID {$flowdino_order_id} — " . $order->get_error_message());
            return 0;
        }

        // Billing address
        $order->set_billing_first_name($first_name);
        $order->set_billing_last_name($last_name);
        $order->set_billing_email($email);
        $order->set_billing_phone($order_data['phone'] ?? '');
        $order->set_billing_address_1($this->clean_field($order_data['street'] ?? ''));
        $order->set_billing_city($this->clean_field($order_data['city'] ?? ''));
        $order->set_billing_postcode($order_data['zipcode'] ?? '');
        $order->set_billing_country($this->resolve_country($order_data['countryIsoCode'] ?? 'FR'));

        // Shipping = same as billing
        $order->set_shipping_first_name($first_name);
        $order->set_shipping_last_name($last_name);
        $order->set_shipping_address_1($order->get_billing_address_1());
        $order->set_shipping_city($order->get_billing_city());
        $order->set_shipping_postcode($order->get_billing_postcode());
        $order->set_shipping_country($order->get_billing_country());

        // Payment
        $order->set_payment_method($site_name);
        $order->set_payment_method_title($site_name);

        // --- Order lines ---
        $order_lines     = $order_data['orderLines'] ?? array();
        $total_lines_ht  = 0.0;
        $billing_country = $order->get_billing_country();

        if (!empty($order_lines)) {
            // --- Classify lines: product, shipping, discount ---
            $product_lines  = array();
            $shipping_lines = array();
            $discount_lines = array();

            foreach ($order_lines as $line) {
                $amount   = (float) ($line['amountLines'] ?? 0);
                $title_lc = strtolower($line['title'] ?? '');
                $has_product = !empty($line['productId']) || !empty($line['product']['id'])
                              || !empty($line['externalKey']) || !empty($line['product']['externalKey']);

                if ($has_product) {
                    $product_lines[] = $line;
                } elseif (
                    strpos($title_lc, 'frais de port') !== false ||
                    strpos($title_lc, 'frais de livraison') !== false ||
                    strpos($title_lc, 'livraison') !== false ||
                    strpos($title_lc, 'expédition') !== false ||
                    strpos($title_lc, 'expedition') !== false ||
                    strpos($title_lc, 'shipping') !== false
                ) {
                    $shipping_lines[] = $line;
                } elseif ($amount < 0) {
                    $discount_lines[] = $line;
                } else {
                    $product_lines[] = $line;
                }
            }

            // --- Total discount TTC (sum of negative discount lines) ---
            $total_discount_ttc = 0.0;
            foreach ($discount_lines as $dl) {
                $total_discount_ttc += (float) ($dl['amountLines'] ?? 0);
            }

            // --- Sum of product amounts for proportional discount distribution ---
            $products_total_ttc = 0.0;
            foreach ($product_lines as $pl) {
                $products_total_ttc += max(0.0, (float) ($pl['amountLines'] ?? 0));
            }

            // --- Product lines (discount absorbed as subtotal/total difference) ---
            foreach ($product_lines as $line) {
                $product_id = $this->get_wc_product_id_from_line($line);
                $line_ttc   = (float) ($line['amountLines'] ?? 0);

                // Proportional share of the global discount for this line
                $discount_share_ttc = 0.0;
                if ($products_total_ttc > 0 && $total_discount_ttc < 0) {
                    $discount_share_ttc = $total_discount_ttc * ($line_ttc / $products_total_ttc);
                }

                $product   = $product_id ? wc_get_product($product_id) : null;
                $tax_class = $product ? $product->get_tax_class() : '';

                // subtotal = original gross price (before discount)
                $sub_amounts = $this->split_ttc_amount($line_ttc, $billing_country, $tax_class);
                // total = net price after discount applied
                $net_amounts = $this->split_ttc_amount(max(0.0, $line_ttc + $discount_share_ttc), $billing_country, $tax_class);

                $total_lines_ht += $net_amounts['ht'];

                $item = new WC_Order_Item_Product();
                $item->set_name(sanitize_text_field($line['title'] ?? __('Produit FlowDino', 'flowdino')));
                $item->set_quantity(1);
                $item->set_subtotal($sub_amounts['ht']); // original price → shows discount in WC
                $item->set_total($net_amounts['ht']);    // price after discount

                if ($product_id) {
                    if ($product && $product->is_type('variation')) {
                        $item->set_product_id($product->get_parent_id());
                        $item->set_variation_id($product->get_id());
                    } else {
                        $item->set_product_id($product_id);
                    }
                }

                if (!empty($sub_amounts['rates'])) {
                    $subtotal_taxes = WC_Tax::calc_tax($sub_amounts['ht'], $sub_amounts['rates'], false);
                    $total_taxes    = WC_Tax::calc_tax($net_amounts['ht'], $sub_amounts['rates'], false);
                    $item->set_taxes(array('total' => $total_taxes, 'subtotal' => $subtotal_taxes));
                }

                $order->add_item($item);
            }

            // --- Shipping lines ---
            $shipping_total_ht  = 0.0;
            $shipping_total_tax = 0.0;
            foreach ($shipping_lines as $line) {
                $shipping_ttc = (float) ($line['amountLines'] ?? 0);
                $amounts      = $this->split_ttc_amount($shipping_ttc, $billing_country, '');

                $shipping_total_ht  += $amounts['ht'];
                $shipping_total_tax += $amounts['tax'];
                $total_lines_ht     += $amounts['ht'];

                $shipping_item = new WC_Order_Item_Shipping();
                $shipping_item->set_name(sanitize_text_field(
                    $order_data['shippingMethod'] ?? $line['title'] ?? __('Expédition', 'flowdino')
                ));
                $shipping_item->set_total($amounts['ht']);

                if (!empty($amounts['rates']) && $amounts['tax'] > 0.0) {
                    $taxes = WC_Tax::calc_tax($amounts['ht'], $amounts['rates'], false);
                    $shipping_item->set_taxes(array('total' => $taxes));
                }

                $order->add_item($shipping_item);
            }

            // Set shipping total so WooCommerce order summary reflects the correct amount
            if ($shipping_total_ht > 0.0) {
                $order->set_shipping_total($shipping_total_ht);
                if (wc_tax_enabled() && $shipping_total_tax > 0.0) {
                    $order->set_shipping_tax($shipping_total_tax);
                }
            }

        } else {
            // No lines: compute from amounts
            $amount_order = (float) ($order_data['amountOrder'] ?? 0);
            $fees_order   = (float) ($order_data['feesOrder']   ?? 0);
            $line_ttc     = $amount_order - $fees_order;

            // Try to resolve the product from the order-level externalKey
            $fallback_product_id = 0;
            $order_external_key  = $order_data['externalKey'] ?? '';
            if (!empty($order_external_key)) {
                $prefix = get_site_url() . '-';
                if (strpos($order_external_key, $prefix) === 0) {
                    $pid = (int) substr($order_external_key, strlen($prefix));
                    if ($pid > 0 && get_post_type($pid) === 'product') {
                        $fallback_product_id = $pid;
                    }
                }
                if (!$fallback_product_id) {
                    $fallback_product_id = (int) wc_get_product_id_by_sku($order_external_key);
                }
            }

            $fallback_product = $fallback_product_id ? wc_get_product($fallback_product_id) : null;
            $tax_class        = $fallback_product ? $fallback_product->get_tax_class() : '';

            $amounts        = $this->split_ttc_amount($line_ttc, $billing_country, $tax_class);
            $total_lines_ht = $amounts['ht'];

            $item = new WC_Order_Item_Product();
            $item->set_name($fallback_product
                ? $fallback_product->get_name()
                : sprintf(
                    /* translators: %s: platform name */
                    __('Vente %s', 'flowdino'),
                    $site_name
                )
            );
            $item->set_quantity(1);
            $item->set_subtotal($amounts['ht']);
            $item->set_total($amounts['ht']);

            if ($fallback_product_id) {
                if ($fallback_product && $fallback_product->is_type('variation')) {
                    $item->set_product_id($fallback_product->get_parent_id());
                    $item->set_variation_id($fallback_product->get_id());
                } else {
                    $item->set_product_id($fallback_product_id);
                }
            }

            if (!empty($amounts['rates']) && $amounts['tax'] > 0.0) {
                $taxes = WC_Tax::calc_tax($amounts['ht'], $amounts['rates'], false);
                $item->set_taxes(array('total' => $taxes, 'subtotal' => $taxes));
            }

            $order->add_item($item);
        }

        // Aggregate tax line items from per-item taxes
        if (wc_tax_enabled()) {
            $order->calculate_taxes();
        }

        // Grand total = TTC amount from FlowDino
        $grand_total_ttc = (float) ($order_data['amountOrder'] ?? 0);
        $order->set_total($grand_total_ttc > 0 ? $grand_total_ttc : $total_lines_ht);

        // FlowDino order reference as meta
        $order->update_meta_data('_flowdino_order_ref', sanitize_text_field($order_ref));
        $order->update_meta_data('_flowdino_site_name', $site_name);
        $order->update_meta_data('_flowdino_order_id', $flowdino_order_id);

        // Shipping label URL
        $label_url = esc_url_raw($order_data['urlEtiquette'] ?? '');
        if (!empty($label_url)) {
            $order->update_meta_data('_flowdino_shipping_label_url', $label_url);
        }

        // Date
        if (!empty($order_data['dateOrder'])) {
            $order->set_date_created($order_data['dateOrder']);
        }

        // Status
        $wc_status = $this->map_status($order_data['status'] ?? 'pending');
        $order->set_status($wc_status, __('Importé depuis FlowDino', 'flowdino'), true);

        $wc_order_id = $order->save();

        // Decrement stock for all matched products (safe: WC checks _order_stock_reduced meta)
        wc_reduce_stock_levels($wc_order_id);

        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
        error_log("FlowDino Sales: Created WC order #{$wc_order_id} from FlowDino order #{$flowdino_order_id}");

        // Save cross-reference to our table
        $this->save_flowdino_order(array(
            'id_order'               => $wc_order_id,
            'flowdino_order_id'      => $flowdino_order_id,
            'flowdino_transaction_id'=> $order_data['transactionId'] ?? '',
            'site_id'                => $site_id,
            'site_name'              => $site_name,
            'order_status'           => $order_data['status'] ?? 'pending',
            'amount'                 => (float) ($order_data['amountOrder'] ?? 0),
            'fees'                   => (float) ($order_data['feesOrder']   ?? 0),
            'currency'               => $order_data['currency'] ?? get_woocommerce_currency(),
            'date_order'             => $order_data['dateOrder'] ?? current_time('mysql'),
        ));

        return $wc_order_id;
    }

    /**
     * Update an existing WooCommerce order status from FlowDino data.
     *
     * @param int   $wc_order_id
     * @param array $order_data
     * @param array $site
     */
    private function update_wc_order(int $wc_order_id, array $order_data, array $site) {
        $order = wc_get_order($wc_order_id);
        if (!$order) {
            return;
        }

        // Update shipping label URL if provided
        $label_url = esc_url_raw($order_data['urlEtiquette'] ?? '');
        if (!empty($label_url)) {
            $order->update_meta_data('_flowdino_shipping_label_url', $label_url);
        }

        $order->save();

        // Update our table
        $this->update_flowdino_order((int) $order_data['id'], array(
            'order_status' => $order_data['status'] ?? 'pending',
            'amount'       => (float) ($order_data['amountOrder'] ?? 0),
            'fees'         => (float) ($order_data['feesOrder']   ?? 0),
        ));

        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
        error_log("FlowDino Sales: Updated WC order #{$wc_order_id}");
    }

    // =========================================================================
    // HELPERS — BUSINESS LOGIC
    // =========================================================================

    /**
     * Map a FlowDino status string to a WooCommerce order status.
     *
     * @param string $flowdino_status
     * @return string  WC order status (without 'wc-' prefix).
     */
    private function map_status($flowdino_status) {
        $wc_status = $this->status_map[$flowdino_status] ?? 'wc-processing';
        // wc_get_order_statuses() uses 'wc-' prefixes but WC set_status does not
        return ltrim($wc_status, 'wc-');
    }

    /**
     * Split a full name into first / last name.
     *
     * @param string $full_name
     * @return array [first_name, last_name]
     */
    private function split_full_name($full_name) {
        $full_name = trim($full_name);
        if (empty($full_name)) {
            return array('FlowDino', 'Client');
        }
        $parts = explode(' ', $full_name, 2);
        return array($parts[0], $parts[1] ?? '');
    }

    /**
     * Build a fallback email when none is provided.
     *
     * @param array $order_data
     * @param int   $flowdino_order_id
     * @return string
     */
    private function resolve_email(array $order_data, $flowdino_order_id) {
        $email = $order_data['email'] ?? '';
        if (empty($email) || !is_email($email)) {
            $email = 'flowdino_' . $flowdino_order_id . '_' . time() . '@noemail.local';
        }
        return sanitize_email($email);
    }

    /**
     * Find a WC customer ID by email (returns 0 if not found).
     *
     * @param string $email
     * @return int
     */
    private function find_customer_id_by_email($email) {
        $user = get_user_by('email', $email);
        return $user ? (int) $user->ID : 0;
    }

    /**
     * Validate/normalise a country ISO code; falls back to 'FR'.
     *
     * @param string $iso
     * @return string
     */
    private function resolve_country($iso) {
        $iso = strtoupper(trim($iso));
        $wc_countries = new WC_Countries();
        $countries     = $wc_countries->get_countries();
        return isset($countries[$iso]) ? $iso : 'FR';
    }

    /**
     * Remove characters that could break address fields.
     *
     * @param string $value
     * @return string
     */
    private function clean_field($value) {
        return sanitize_text_field(wp_strip_all_tags($value));
    }

    /**
     * Split a TTC (tax-included) amount into HT (ex-tax) and tax portions.
     *
     * If WooCommerce taxes are disabled, returns the full amount as HT with no tax.
     * Uses WC_Tax::find_rates() with the billing country and product tax class.
     *
     * @param float  $ttc           Amount including tax.
     * @param string $country       Billing country ISO code (e.g. 'FR').
     * @param string $tax_class     WooCommerce tax class ('' = standard).
     * @return array { ht: float, tax: float, rates: array }
     */
    private function split_ttc_amount(float $ttc, string $country, string $tax_class = ''): array {
        $empty = array('ht' => $ttc, 'tax' => 0.0, 'rates' => array());

        if (!wc_tax_enabled() || $ttc <= 0) {
            return $empty;
        }

        $rates = WC_Tax::find_rates(array(
            'country'   => $country,
            'tax_class' => $tax_class,
        ));

        if (empty($rates)) {
            $rates = WC_Tax::get_rates($tax_class);
        }

        if (empty($rates)) {
            return $empty;
        }

        $total_rate = array_sum(array_column($rates, 'rate'));
        if ($total_rate <= 0) {
            return array('ht' => $ttc, 'tax' => 0.0, 'rates' => $rates);
        }

        $ht  = (float) wc_format_decimal($ttc / (1.0 + $total_rate / 100.0), wc_get_price_decimals());
        $tax = (float) wc_format_decimal($ttc - $ht, wc_get_price_decimals());

        return array('ht' => $ht, 'tax' => $tax, 'rates' => $rates);
    }

    /**
     * Attempt to find the WooCommerce product ID from a FlowDino order line.
     *
     * Resolution order:
     * 1. FlowDino product ID → flowdino_product_sync table
     * 2. externalKey → WC product ID (last segment after '-')
     * 3. externalKey → SKU lookup
     * 4. productId field directly
     * 5. Line title → SKU lookup
     *
     * @param array $line
     * @return int Product ID or 0 if not found.
     */
    private function get_wc_product_id_from_line(array $line) {
        global $wpdb;

        // 1. FlowDino product ID → sync table lookup
        $flowdino_product_id = (int) ($line['product']['id'] ?? 0);
        if ($flowdino_product_id > 0) {
            $sync_table = $wpdb->prefix . 'flowdino_product_sync';
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $product_id = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT product_id FROM " . esc_sql($sync_table) . " WHERE flowdino_id = %d LIMIT 1",
                $flowdino_product_id
            ));
            if ($product_id > 0 && in_array(get_post_type($product_id), array('product', 'product_variation'), true)) {
                return $product_id;
            }
        }

        // 2 & 3. External key: format "{shop_url}-{productId}"
        // Accept key from both $line['externalKey'] and $line['product']['externalKey']
        $external_key = $line['externalKey'] ?? ($line['product']['externalKey'] ?? '');
        if (!empty($external_key)) {
            $prefix = get_site_url() . '-';
            if (strpos($external_key, $prefix) === 0) {
                $product_id = (int) substr($external_key, strlen($prefix));
                if ($product_id > 0 && in_array(get_post_type($product_id), array('product', 'product_variation'), true)) {
                    return $product_id;
                }
            }
            $by_sku = wc_get_product_id_by_sku($external_key);
            if ($by_sku) {
                return $by_sku;
            }
        }

        // 4. productId field directly
        $product_id = (int) ($line['productId'] ?? 0);
        if ($product_id > 0 && in_array(get_post_type($product_id), array('product', 'product_variation'), true)) {
            return $product_id;
        }

        // 5. Title → SKU lookup
        $title = sanitize_text_field($line['title'] ?? '');
        if (!empty($title)) {
            $by_sku = wc_get_product_id_by_sku($title);
            if ($by_sku) {
                return $by_sku;
            }
        }

        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
        error_log('FlowDino Sales: Product not found — flowdino_id=' . $flowdino_product_id . ', externalKey="' . esc_html($external_key) . '"');
        return 0;
    }

    // =========================================================================
    // DATABASE HELPERS
    // =========================================================================

    /**
     * Retrieve last sync date for a site (defaults to DEFAULT_SYNC_DATE).
     *
     * @param int $site_id
     * @return string
     */
    private function get_last_sync_date($site_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'flowdino_sales_sync';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-specific query
        $date = $wpdb->get_var($wpdb->prepare(
            "SELECT last_sync_date FROM " . esc_sql($table) . " WHERE site_id = %d",
            $site_id
        ));
        return $date ?: self::DEFAULT_SYNC_DATE;
    }

    /**
     * Update the last sync date for a site.
     *
     * @param int    $site_id
     * @param string $date
     */
    private function update_last_sync_date($site_id, $date) {
        global $wpdb;
        $table = $wpdb->prefix . 'flowdino_sales_sync';
        $now   = current_time('mysql');

        $exists = $wpdb->get_var($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            "SELECT site_id FROM " . esc_sql($table) . " WHERE site_id = %d",
            $site_id
        ));

        if ($exists) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-specific update
            $wpdb->update(
                $table,
                array('last_sync_date' => $date, 'date_upd' => $now),
                array('site_id' => $site_id),
                array('%s', '%s'),
                array('%d')
            );
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-specific insert
            $wpdb->insert($table, array(
                'site_id'        => $site_id,
                'last_sync_date' => $date,
                'date_add'       => $now,
                'date_upd'       => $now,
            ));
        }
    }

    /**
     * Find an existing WooCommerce order ID by the _flowdino_order_id meta.
     * Used as a fallback when the flowdino_orders table record is missing.
     *
     * @param int $flowdino_order_id
     * @return int|null WC order ID or null.
     */
    private function find_wc_order_id_by_meta($flowdino_order_id) {
        $orders = wc_get_orders(array(
            'limit'      => 1,
            'meta_key'   => '_flowdino_order_id',
            'meta_value' => (int) $flowdino_order_id,
            'return'     => 'ids',
        ));

        return !empty($orders) ? (int) $orders[0] : null;
    }

    /**
     * Get a FlowDino order row by its FlowDino ID.
     *
     * @param int $flowdino_order_id
     * @return object|null
     */
    private function get_order_by_flowdino_id($flowdino_order_id) {
        global $wpdb;
        $table = $wpdb->prefix . 'flowdino_orders';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-specific query
        return $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM " . esc_sql($table) . " WHERE flowdino_order_id = %d",
            $flowdino_order_id
        ));
    }

    /**
     * Insert a new row in flowdino_orders.
     *
     * @param array $data
     */
    private function save_flowdino_order(array $data) {
        global $wpdb;
        $table = $wpdb->prefix . 'flowdino_orders';
        $now   = current_time('mysql');

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-specific insert
        $wpdb->insert($table, array(
            'id_order'                => $data['id_order'],
            'flowdino_order_id'       => $data['flowdino_order_id'],
            'flowdino_transaction_id' => $data['flowdino_transaction_id'] ?? '',
            'site_id'                 => $data['site_id'],
            'site_name'               => $data['site_name'],
            'order_status'            => $data['order_status'],
            'amount'                  => $data['amount'],
            'fees'                    => $data['fees'],
            'currency'                => $data['currency'],
            'date_order'              => $data['date_order'],
            'date_add'                => $now,
            'date_upd'                => $now,
        ));
    }

    /**
     * Update status/amounts in flowdino_orders.
     *
     * @param int   $flowdino_order_id
     * @param array $data
     */
    private function update_flowdino_order($flowdino_order_id, array $data) {
        global $wpdb;
        $table = $wpdb->prefix . 'flowdino_orders';

        $data['date_upd'] = current_time('mysql');

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-specific update
        $wpdb->update(
            $table,
            $data,
            array('flowdino_order_id' => $flowdino_order_id)
        );
    }
}
