-- Sincronizacion nativa entre WebFace y OrderFlowPro en el mismo servidor MySQL.
--
-- Objetivo:
-- - Reemplazar la sincronizacion PHP/Laravel por triggers de base de datos.
-- - Mantener tbl_orders y tbl_order_details actualizados en tiempo real.
-- - Registrar el estado inicial de la orden y la transicion automatica a Preparando u On Hold.
-- - Resolver store_id desde la primera sucursal consolidada en el detalle.
--
-- Alcance:
-- - Fuente: universal4shop_webfaceb2c.orders y universal4shop_webfaceb2c.order_detail.
-- - Destino: universal4shop_oms.tbl_orders, tbl_order_details y tbl_order_status_logs.
--
-- Diseno:
-- - Se usan procedimientos auxiliares para concentrar la logica y reutilizarla desde INSERT y UPDATE.
-- - El script es idempotente: elimina triggers/procedimientos previos y los vuelve a crear.
-- - El update de cabecera no pisa status_id si la orden local ya avanzo en el flujo operativo.

USE universal4shop_webfaceb2c;

-- Limpieza previa para permitir re-ejecucion segura del script.
DROP TRIGGER IF EXISTS trg_webface_orders_ai_sync_orderflowpro;
DROP TRIGGER IF EXISTS trg_webface_orders_au_sync_orderflowpro;
DROP TRIGGER IF EXISTS trg_webface_order_detail_ai_sync_orderflowpro;
DROP TRIGGER IF EXISTS trg_webface_order_detail_au_sync_orderflowpro;

DROP PROCEDURE IF EXISTS sp_sync_webface_order_header;
DROP PROCEDURE IF EXISTS sp_sync_webface_order_detail;

DELIMITER //

CREATE PROCEDURE sp_sync_webface_order_header(
    IN p_order_id BIGINT UNSIGNED,
    IN p_force_initial_flow TINYINT(1)
)
proc: BEGIN
    -- Variables del snapshot fuente y de catalogos/relaciones del destino.
    DECLARE v_order_exists INT DEFAULT 0;
    DECLARE v_remote_order_id BIGINT UNSIGNED;
    DECLARE v_order_reference VARCHAR(9);
    DECLARE v_created_at DATETIME;
    DECLARE v_updated_at DATETIME;
    DECLARE v_date_order DATETIME;
    DECLARE v_cod_client VARCHAR(50);
    DECLARE v_customer_name VARCHAR(255);
    DECLARE v_customer_identification VARCHAR(255);
    DECLARE v_customer_email VARCHAR(255);
    DECLARE v_customer_phone VARCHAR(255);
    DECLARE v_customer_legal_activity VARCHAR(255);
    DECLARE v_payment VARCHAR(255);
    DECLARE v_order_notes TEXT;
    DECLARE v_total_products_ti DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_total_products_te DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_total_discounts DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_total_shipping DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_total_paid_ti DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_total_paid_te DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_completo TINYINT(1) DEFAULT 0;
    DECLARE v_store_id BIGINT UNSIGNED;
    DECLARE v_store_name VARCHAR(255);
    DECLARE v_channel_id INT UNSIGNED;
    DECLARE v_order_type_id INT UNSIGNED;
    DECLARE v_status_ingresado_id INT UNSIGNED;
    DECLARE v_status_preparando_id INT UNSIGNED;
    DECLARE v_status_on_hold_id INT UNSIGNED;
    DECLARE v_has_special_product TINYINT(1) DEFAULT 0;
    DECLARE v_target_status_id INT UNSIGNED;

    -- Carga el snapshot de la orden remota y, cuando existe, completa los datos del cliente.
    SELECT
        o.id_order,
        NULLIF(TRIM(o.order_reference), ''),
        COALESCE(o.date_order, CURRENT_TIMESTAMP),
        CURRENT_TIMESTAMP,
        COALESCE(NULLIF(TRIM(c.cedula), ''), CAST(o.idClient AS CHAR(50)), 'N/A'),
        NULLIF(TRIM(COALESCE(c.name, c.commercial_name)), ''),
        NULLIF(TRIM(c.cedula), ''),
        NULLIF(TRIM(c.email), ''),
        NULLIF(TRIM(c.phone), ''),
        NULLIF(TRIM(c.actividad), ''),
        NULLIF(TRIM(o.payment), ''),
        NULLIF(TRIM(o.order_notes), ''),
        ROUND(COALESCE(o.total_productsTI, 0), 6),
        ROUND(COALESCE(o.total_productsTE, 0), 6),
        ROUND(COALESCE(o.total_discounts, 0), 6),
        ROUND(COALESCE(o.total_shipping, 0), 6),
        ROUND(COALESCE(o.total_paidTI, o.total_productsTI, 0), 6),
        ROUND(COALESCE(o.total_paidTE, o.total_productsTE, 0), 6),
        COALESCE(o.completo, 0)
    INTO
        v_remote_order_id,
        v_order_reference,
        v_created_at,
        v_updated_at,
        v_cod_client,
        v_customer_name,
        v_customer_identification,
        v_customer_email,
        v_customer_phone,
        v_customer_legal_activity,
        v_payment,
        v_order_notes,
        v_total_products_ti,
        v_total_products_te,
        v_total_discounts,
        v_total_shipping,
        v_total_paid_ti,
        v_total_paid_te,
        v_completo
    FROM orders o
    LEFT JOIN clients c
        ON c.id = o.idClient
    WHERE o.id_order = p_order_id
    LIMIT 1;

    -- Sin referencia operativa no se puede mantener integridad en tbl_orders.
    IF v_order_reference IS NULL THEN
        LEAVE proc;
    END IF;

    -- La fecha de negocio en OrderFlow se conserva desde la fecha original del pedido remoto.
    SET v_date_order = v_created_at;

    -- store_id se resuelve dinamicamente desde el primer detalle fuente.
    -- La regla de negocio toma la primera sucursal del pedido segun id_order_detail.
    SELECT s.id, s.name
    INTO v_store_id, v_store_name
    FROM order_detail d
    JOIN universal4shop_oms.tbl_stores s
        ON s.universal_sucursal_id = CAST(COALESCE(NULLIF(CAST(d.idSucursal AS CHAR(255)), ''), '0') AS UNSIGNED)
    WHERE d.order_reference = v_order_reference
    ORDER BY d.id_order_detail
    LIMIT 1;

    -- Se intenta primero el catalogo moderno y luego el legacy si coexistieran ambos codigos.
    SELECT toc.id_order_channel
    INTO v_channel_id
    FROM universal4shop_oms.tbl_order_channels toc
    WHERE toc.code IN ('shopify', 'Web')
    ORDER BY FIELD(toc.code, 'shopify', 'Web'), toc.id_order_channel
    LIMIT 1;

    -- El tipo por defecto para este flujo remoto es despacho/normal.
    SELECT tot.id_order_type
    INTO v_order_type_id
    FROM universal4shop_oms.tbl_order_types tot
    WHERE tot.code IN ('dispatch', 'Normal')
    ORDER BY FIELD(tot.code, 'dispatch', 'Normal'), tot.id_order_type
    LIMIT 1;

    -- Estados usados para el flujo automatico inicial.
    SELECT tos.id_order_status
    INTO v_status_ingresado_id
    FROM universal4shop_oms.tbl_order_statuses tos
    WHERE tos.code = 'Ingresado'
    LIMIT 1;

    SELECT tos.id_order_status
    INTO v_status_preparando_id
    FROM universal4shop_oms.tbl_order_statuses tos
    WHERE tos.code = 'Preparando'
    LIMIT 1;

     SELECT tos.id_order_status
     INTO v_status_on_hold_id
     FROM universal4shop_oms.tbl_order_statuses tos
     WHERE tos.code IN ('OnHold', 'on_hold')
         OR tos.name = 'On Hold'
     ORDER BY FIELD(tos.code, 'OnHold', 'on_hold'), tos.id_order_status
     LIMIT 1;

    -- Detecta si la orden ya existe para decidir entre alta inicial o simple refresco.
    SELECT COUNT(*)
    INTO v_order_exists
    FROM universal4shop_oms.tbl_orders o
    WHERE o.remote_order_id = v_remote_order_id
       OR o.order_reference = v_order_reference;

    -- Upsert de cabecera:
    -- - En insert crea la orden con estado Ingresado.
    -- - En update refresca datos comerciales/financieros sin retroceder status_id.
    INSERT INTO universal4shop_oms.tbl_orders (
        remote_order_id,
        order_reference,
        original_order_reference,
        store_id,
        store_name,
        channel_id,
        order_type_id,
        status_id,
        picking_aprobado,
        Completo_OFP,
        guide,
        carrier_id,
        codClient,
        customer_name,
        customer_identification,
        customer_email,
        customer_phone,
        customer_legal_activity,
        customer_total_orders,
        payment,
        order_notes,
        total_productsTI,
        total_productsTE,
        total_discounts,
        total_shipping,
        total_paidTI,
        total_paidTE,
        date_order,
        postal_code,
        province_id,
        canton_id,
        district_id,
        address_detail,
        created_at,
        updated_at,
        parent_remote_order_id
    ) VALUES (
        v_remote_order_id,
        v_order_reference,
        NULL,
        v_store_id,
        v_store_name,
        v_channel_id,
        v_order_type_id,
        COALESCE(v_status_ingresado_id, v_status_preparando_id),
        0,
        COALESCE(v_completo, 0),
        NULL,
        NULL,
        v_cod_client,
        v_customer_name,
        v_customer_identification,
        v_customer_email,
        v_customer_phone,
        v_customer_legal_activity,
        1,
        v_payment,
        v_order_notes,
        v_total_products_ti,
        v_total_products_te,
        v_total_discounts,
        v_total_shipping,
        v_total_paid_ti,
        v_total_paid_te,
        v_date_order,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        v_created_at,
        IF(v_order_exists = 0 AND p_force_initial_flow = 1, v_created_at, v_updated_at),
        NULL
    )
    ON DUPLICATE KEY UPDATE
        order_reference = VALUES(order_reference),
        store_id = COALESCE(VALUES(store_id), store_id),
        store_name = COALESCE(VALUES(store_name), store_name),
        channel_id = COALESCE(VALUES(channel_id), channel_id),
        order_type_id = COALESCE(VALUES(order_type_id), order_type_id),
        status_id = CASE
            WHEN status_id IS NULL THEN VALUES(status_id)
            ELSE status_id
        END,
        Completo_OFP = VALUES(Completo_OFP),
        codClient = VALUES(codClient),
        customer_name = COALESCE(VALUES(customer_name), customer_name),
        customer_identification = COALESCE(VALUES(customer_identification), customer_identification),
        customer_email = COALESCE(VALUES(customer_email), customer_email),
        customer_phone = COALESCE(VALUES(customer_phone), customer_phone),
        customer_legal_activity = COALESCE(VALUES(customer_legal_activity), customer_legal_activity),
        payment = COALESCE(VALUES(payment), payment),
        order_notes = COALESCE(VALUES(order_notes), order_notes),
        total_productsTI = VALUES(total_productsTI),
        total_productsTE = VALUES(total_productsTE),
        total_discounts = VALUES(total_discounts),
        total_shipping = VALUES(total_shipping),
        total_paidTI = VALUES(total_paidTI),
        total_paidTE = VALUES(total_paidTE),
        date_order = VALUES(date_order),
        updated_at = IF(p_force_initial_flow = 1 AND v_order_exists = 0, VALUES(created_at), VALUES(updated_at));

    -- Solo en el alta inicial se registra la bitacora de nacimiento y la transicion automatica.
    IF v_order_exists = 0 AND p_force_initial_flow = 1 THEN
        -- Bitacora: la orden nace de cara al cliente como Ingresado.
        INSERT INTO universal4shop_oms.tbl_order_status_logs (
            order_reference,
            order_detail_id,
            status,
            note,
            user_id,
            user_name,
            status_id_old,
            status_id_new,
            happened_at,
            created_at,
            updated_at
        ) VALUES (
            v_order_reference,
            NULL,
            'Ingresado',
            'Alta automatica generada por el sistema.',
            NULL,
            'system-trigger',
            NULL,
            v_status_ingresado_id,
            v_created_at,
            v_created_at,
            v_created_at
        );

                SELECT EXISTS(
                        SELECT 1
                        FROM order_detail d
                        WHERE d.order_reference = v_order_reference
                            AND UPPER(TRIM(COALESCE(d.special_product, ''))) = 'NOSTOCK'
                )
                INTO v_has_special_product;

        SET v_target_status_id = CASE
            WHEN v_has_special_product = 1 AND v_status_on_hold_id IS NOT NULL THEN v_status_on_hold_id
            ELSE COALESCE(v_status_preparando_id, v_status_on_hold_id, v_status_ingresado_id)
        END;

        -- Transicion operativa inmediata: luego del ingreso, el flujo local pasa a Preparando
        -- o a On Hold si la orden ya trae productos Marketplace.
        -- Se conserva created_at/updated_at con la misma marca temporal del alta original.
        UPDATE universal4shop_oms.tbl_orders
        SET status_id = COALESCE(v_target_status_id, status_id),
            updated_at = v_created_at
        WHERE remote_order_id = v_remote_order_id
           OR order_reference = v_order_reference;

        -- Bitacora: se registra tambien la transicion automatica al estado operativo inicial.
        INSERT INTO universal4shop_oms.tbl_order_status_logs (
            order_reference,
            order_detail_id,
            status,
            note,
            user_id,
            user_name,
            status_id_old,
            status_id_new,
            happened_at,
            created_at,
            updated_at
        ) VALUES (
            v_order_reference,
            NULL,
            CASE
                WHEN v_has_special_product = 1 AND v_status_on_hold_id IS NOT NULL THEN 'On Hold'
                ELSE 'Preparando'
            END,
            CASE
                WHEN v_has_special_product = 1 AND v_status_on_hold_id IS NOT NULL THEN 'Transicion automatica inicial a On Hold generada por el sistema al detectar productos Marketplace.'
                ELSE 'Transicion automatica inicial a Preparando generada por el sistema.'
            END,
            NULL,
            'system-trigger',
            v_status_ingresado_id,
            v_target_status_id,
            v_created_at,
            v_created_at,
            v_created_at
        );
    END IF;
END//

CREATE PROCEDURE sp_sync_webface_order_detail(
    IN p_detail_id BIGINT UNSIGNED,
    IN p_is_update TINYINT(1)
)
proc: BEGIN
    -- Variables del detalle fuente, calculos financieros y resolucion de tienda.
    DECLARE v_source_order_id BIGINT UNSIGNED;
    DECLARE v_order_reference VARCHAR(9);
    DECLARE v_product_reference VARCHAR(64);
    DECLARE v_product_name TEXT;
    DECLARE v_warehouse_assigned VARCHAR(255);
    DECLARE v_special_product TEXT;
    DECLARE v_product_quantity INT UNSIGNED DEFAULT 0;
    DECLARE v_product_tax DECIMAL(10, 2) DEFAULT 0;
    DECLARE v_product_price_ti DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_product_price_te DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_total_price_ti DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_total_price_te DECIMAL(20, 6) DEFAULT 0;
    DECLARE v_line_happened_at DATETIME;
    DECLARE v_source_shipping_method VARCHAR(255);
    DECLARE v_shipping_method VARCHAR(32);
    DECLARE v_source_rank INT DEFAULT 0;
    DECLARE v_target_detail_id INT UNSIGNED;
    DECLARE v_store_id BIGINT UNSIGNED;
    DECLARE v_store_name VARCHAR(255);
    DECLARE v_status_ingresado_id INT UNSIGNED;
    DECLARE v_status_preparando_id INT UNSIGNED;
    DECLARE v_status_on_hold_id INT UNSIGNED;
    DECLARE v_current_status_id INT UNSIGNED;
    DECLARE v_target_status_id INT UNSIGNED;
    DECLARE v_has_special_product TINYINT(1) DEFAULT 0;

    -- Lee la linea remota y calcula los importes faltantes si la fuente no los trae materializados.
    -- La prioridad es respetar valores explicitos; si faltan, se recalculan desde impuesto/cantidad.
    SELECT
        o.id_order,
        NULLIF(TRIM(d.order_reference), ''),
        NULLIF(TRIM(d.product_reference), ''),
        NULLIF(TRIM(d.product_name), ''),
        NULLIF(TRIM(d.special_product), ''),
        CASE
            WHEN COALESCE(d.idSucursal, 0) > 0 THEN CAST(d.idSucursal AS CHAR(255))
            ELSE NULL
        END,
        GREATEST(COALESCE(d.product_quantity, 0), 0),
        ROUND(COALESCE(d.product_tax, 0), 2),
        ROUND(
            COALESCE(
                d.product_priceTI,
                CASE
                    WHEN d.product_priceTE IS NOT NULL THEN d.product_priceTE * (1 + (COALESCE(d.product_tax, 0) / 100))
                    ELSE d.price_byGroup
                END,
                0
            ),
            6
        ),
        ROUND(
            COALESCE(
                d.product_priceTE,
                CASE
                    WHEN d.product_priceTI IS NOT NULL AND COALESCE(d.product_tax, 0) > 0 THEN d.product_priceTI / (1 + (d.product_tax / 100))
                    ELSE d.price_byGroup
                END,
                0
            ),
            6
        ),
        ROUND(
            COALESCE(
                d.total_priceTI,
                GREATEST(COALESCE(d.product_quantity, 0), 0) * COALESCE(
                    d.product_priceTI,
                    CASE
                        WHEN d.product_priceTE IS NOT NULL THEN d.product_priceTE * (1 + (COALESCE(d.product_tax, 0) / 100))
                        ELSE d.price_byGroup
                    END,
                    0
                )
            ),
            6
        ),
        ROUND(
            COALESCE(
                d.total_priceTE,
                GREATEST(COALESCE(d.product_quantity, 0), 0) * COALESCE(
                    d.product_priceTE,
                    CASE
                        WHEN d.product_priceTI IS NOT NULL AND COALESCE(d.product_tax, 0) > 0 THEN d.product_priceTI / (1 + (d.product_tax / 100))
                        ELSE d.price_byGroup
                    END,
                    0
                )
            ),
            6
        ),
        NULLIF(TRIM(d.shipping_method), ''),
        COALESCE(o.date_order, CURRENT_TIMESTAMP)
    INTO
        v_source_order_id,
        v_order_reference,
        v_product_reference,
        v_product_name,
        v_special_product,
        v_warehouse_assigned,
        v_product_quantity,
        v_product_tax,
        v_product_price_ti,
        v_product_price_te,
        v_total_price_ti,
        v_total_price_te,
        v_source_shipping_method,
        v_line_happened_at
    FROM order_detail d
    LEFT JOIN orders o
        ON o.order_reference = d.order_reference
    WHERE d.id_order_detail = p_detail_id
    LIMIT 1;

    -- Una linea sin referencia de orden no puede sincronizarse de forma segura.
    IF v_order_reference IS NULL THEN
        LEAVE proc;
    END IF;

    -- Si no hay identificador minimo de producto, la linea se ignora.
    IF v_product_reference IS NULL AND v_product_name IS NULL THEN
        LEAVE proc;
    END IF;

    -- Asegura que la cabecera exista antes de persistir el hijo.
    IF v_source_order_id IS NOT NULL THEN
        CALL sp_sync_webface_order_header(v_source_order_id, 0);
    END IF;

        -- shipping_method se toma del detalle fuente y se normaliza al catalogo local.
    SET v_shipping_method = CASE
                WHEN v_source_shipping_method IS NULL THEN NULL
                WHEN LOWER(REPLACE(REPLACE(TRIM(v_source_shipping_method), '-', ' '), '_', ' ')) LIKE '%uber%' THEN 'Uber'
                WHEN LOWER(REPLACE(REPLACE(TRIM(v_source_shipping_method), '-', ' '), '_', ' ')) LIKE '%tienda%'
                    OR LOWER(REPLACE(REPLACE(TRIM(v_source_shipping_method), '-', ' '), '_', ' ')) LIKE '%pickup%'
                    OR LOWER(REPLACE(REPLACE(TRIM(v_source_shipping_method), '-', ' '), '_', ' ')) LIKE '%retiro%' THEN 'Tienda'
                WHEN LOWER(REPLACE(REPLACE(TRIM(v_source_shipping_method), '-', ' '), '_', ' ')) LIKE '%envio%'
                    OR LOWER(REPLACE(REPLACE(TRIM(v_source_shipping_method), '-', ' '), '_', ' ')) LIKE '%shipping%'
                    OR LOWER(REPLACE(REPLACE(TRIM(v_source_shipping_method), '-', ' '), '_', ' ')) LIKE '%delivery%'
                    OR LOWER(REPLACE(REPLACE(TRIM(v_source_shipping_method), '-', ' '), '_', ' ')) LIKE '%despacho%' THEN 'Envio'
                ELSE v_source_shipping_method
    END;

    -- Cuando existen lineas repetidas con la misma clave funcional
    -- (referencia + nombre + sucursal), se usa el orden de insercion remoto
    -- para emparejar contra el orden local y evitar sobreescrituras cruzadas.
    SELECT COUNT(*)
    INTO v_source_rank
    FROM order_detail d
    WHERE d.order_reference = v_order_reference
      AND COALESCE(TRIM(d.product_reference), '') = COALESCE(v_product_reference, '')
      AND COALESCE(TRIM(d.product_name), '') = COALESCE(v_product_name, '')
      AND COALESCE(CAST(d.idSucursal AS CHAR(255)), '') = COALESCE(v_warehouse_assigned, '')
      AND d.id_order_detail <= p_detail_id;

    IF v_source_rank <= 0 THEN
        SET v_source_rank = 1;
    END IF;

    -- Busca el n-esimo detalle local equivalente a la linea remota.
    SELECT ranked.id_order_detail
    INTO v_target_detail_id
    FROM (
        SELECT
            tod.id_order_detail,
            ROW_NUMBER() OVER (ORDER BY tod.id_order_detail) AS row_num
        FROM universal4shop_oms.tbl_order_details tod
        WHERE tod.order_reference = v_order_reference
          AND COALESCE(TRIM(tod.product_reference), '') = COALESCE(v_product_reference, '')
          AND COALESCE(TRIM(tod.product_name), '') = COALESCE(v_product_name, '')
          AND COALESCE(TRIM(tod.warehouse_assigned), '') = COALESCE(v_warehouse_assigned, '')
    ) ranked
    WHERE ranked.row_num = v_source_rank
    LIMIT 1;

    -- Si no existe detalle equivalente, se crea con estado logistico inicial Empacado.
    IF v_target_detail_id IS NULL THEN
        INSERT INTO universal4shop_oms.tbl_order_details (
            order_reference,
            product_remote_id,
            product_reference,
            product_name,
            special_product,
            product_quantity,
            is_picked,
            current_status,
            product_tax,
            product_priceTI,
            product_priceTE,
            total_priceTI,
            total_priceTE,
            warehouse_assigned,
            shipping_method,
            created_at,
            updated_at,
            manifest_id
        ) VALUES (
            v_order_reference,
            NULL,
            COALESCE(v_product_reference, ''),
            COALESCE(v_product_name, COALESCE(v_product_reference, '')),
            v_special_product,
            v_product_quantity,
            0,
            'Empacado',
            v_product_tax,
            v_product_price_ti,
            v_product_price_te,
            v_total_price_ti,
            v_total_price_te,
            v_warehouse_assigned,
            v_shipping_method,
            v_line_happened_at,
            IF(p_is_update = 1, CURRENT_TIMESTAMP, v_line_happened_at),
            NULL
        );
    ELSE
        -- Si ya existe, solo se refrescan los datos sincronizables del item.
        UPDATE universal4shop_oms.tbl_order_details
        SET product_reference = COALESCE(v_product_reference, product_reference),
            product_name = COALESCE(v_product_name, product_name),
            special_product = v_special_product,
            product_quantity = v_product_quantity,
            product_tax = v_product_tax,
            product_priceTI = v_product_price_ti,
            product_priceTE = v_product_price_te,
            total_priceTI = v_total_price_ti,
            total_priceTE = v_total_price_te,
            warehouse_assigned = v_warehouse_assigned,
            shipping_method = COALESCE(v_shipping_method, shipping_method),
            updated_at = CURRENT_TIMESTAMP
        WHERE id_order_detail = v_target_detail_id;
    END IF;

    -- Tras insertar/actualizar la linea, vuelve a intentar resolver la tienda de la cabecera.
    -- Se toma siempre la primera sucursal del detalle fuente para mantener una sola tienda involucrada.
    SELECT s.id, s.name
    INTO v_store_id, v_store_name
    FROM order_detail d
    JOIN universal4shop_oms.tbl_stores s
        ON s.universal_sucursal_id = CAST(COALESCE(NULLIF(CAST(d.idSucursal AS CHAR(255)), ''), '0') AS UNSIGNED)
    WHERE d.order_reference = v_order_reference
    ORDER BY d.id_order_detail
    LIMIT 1;

    IF v_store_id IS NOT NULL THEN
        UPDATE universal4shop_oms.tbl_orders
        SET store_id = v_store_id,
            store_name = COALESCE(v_store_name, store_name),
            updated_at = CURRENT_TIMESTAMP
        WHERE order_reference = v_order_reference;
    END IF;

    SELECT tos.id_order_status
    INTO v_status_ingresado_id
    FROM universal4shop_oms.tbl_order_statuses tos
    WHERE tos.code = 'Ingresado'
    LIMIT 1;

    SELECT tos.id_order_status
    INTO v_status_preparando_id
    FROM universal4shop_oms.tbl_order_statuses tos
    WHERE tos.code = 'Preparando'
    LIMIT 1;

    SELECT tos.id_order_status
    INTO v_status_on_hold_id
    FROM universal4shop_oms.tbl_order_statuses tos
    WHERE tos.code IN ('OnHold', 'on_hold')
       OR tos.name = 'On Hold'
    ORDER BY FIELD(tos.code, 'OnHold', 'on_hold'), tos.id_order_status
    LIMIT 1;

    SELECT status_id
    INTO v_current_status_id
    FROM universal4shop_oms.tbl_orders
    WHERE order_reference = v_order_reference
    LIMIT 1;

        SELECT EXISTS(
                SELECT 1
                FROM order_detail d
                WHERE d.order_reference = v_order_reference
                    AND UPPER(TRIM(COALESCE(d.special_product, ''))) = 'NOSTOCK'
        )
        INTO v_has_special_product;

    SET v_target_status_id = CASE
        WHEN v_has_special_product = 1 AND v_status_on_hold_id IS NOT NULL THEN v_status_on_hold_id
        ELSE COALESCE(v_status_preparando_id, v_status_on_hold_id, v_status_ingresado_id)
    END;

    IF v_target_status_id IS NOT NULL
       AND (v_current_status_id IS NULL OR v_current_status_id IN (
            COALESCE(v_status_ingresado_id, 0),
            COALESCE(v_status_preparando_id, 0),
            COALESCE(v_status_on_hold_id, 0)
       ))
       AND COALESCE(v_current_status_id, 0) <> v_target_status_id THEN
        UPDATE universal4shop_oms.tbl_orders
        SET status_id = v_target_status_id,
            updated_at = IF(p_is_update = 1, CURRENT_TIMESTAMP, v_line_happened_at)
        WHERE order_reference = v_order_reference;

        INSERT INTO universal4shop_oms.tbl_order_status_logs (
            order_reference,
            order_detail_id,
            status,
            note,
            user_id,
            user_name,
            status_id_old,
            status_id_new,
            happened_at,
            created_at,
            updated_at
        ) VALUES (
            v_order_reference,
            NULL,
            CASE
                WHEN v_has_special_product = 1 AND v_status_on_hold_id IS NOT NULL THEN 'On Hold'
                ELSE 'Preparando'
            END,
            CASE
                WHEN v_has_special_product = 1 AND v_status_on_hold_id IS NOT NULL THEN 'Cambio automatico por sincronizacion de detalle: pedido Marketplace detectado, orden movida a On Hold.'
                ELSE 'Cambio automatico por sincronizacion de detalle: pedido sin Marketplace, orden movida a Preparando.'
            END,
            NULL,
            'system-trigger',
            v_current_status_id,
            v_target_status_id,
            IF(p_is_update = 1, CURRENT_TIMESTAMP, v_line_happened_at),
            IF(p_is_update = 1, CURRENT_TIMESTAMP, v_line_happened_at),
            IF(p_is_update = 1, CURRENT_TIMESTAMP, v_line_happened_at)
        );
    END IF;
END//

-- Trigger de cabecera en alta:
-- crea la orden local, registra Ingresado y la mueve a Preparando u On Hold.
CREATE TRIGGER trg_webface_orders_ai_sync_orderflowpro
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    CALL sp_sync_webface_order_header(NEW.id_order, 1);
END//

-- Trigger de cabecera en actualizacion:
-- refresca datos comerciales/financieros sin destruir el estado operativo vigente.
CREATE TRIGGER trg_webface_orders_au_sync_orderflowpro
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    CALL sp_sync_webface_order_header(NEW.id_order, 0);
END//

-- Trigger de detalle en alta:
-- sincroniza el item y corrige store_id en la cabecera si esta pendiente.
CREATE TRIGGER trg_webface_order_detail_ai_sync_orderflowpro
AFTER INSERT ON order_detail
FOR EACH ROW
BEGIN
    CALL sp_sync_webface_order_detail(NEW.id_order_detail, 0);
END//

-- Trigger de detalle en actualizacion:
-- mantiene el item alineado con la fuente remota y conserva el estado local del flujo.
CREATE TRIGGER trg_webface_order_detail_au_sync_orderflowpro
AFTER UPDATE ON order_detail
FOR EACH ROW
BEGIN
    CALL sp_sync_webface_order_detail(NEW.id_order_detail, 1);
END//

DELIMITER ;

-- Notas operacionales:
-- 1. Este script usa la tabla fuente order_detail, que es la observada en el repo.
--    Si tu tabla fisica en produccion se llama orders_details, cambia ese identificador
--    en ambos triggers de detalle y dentro de sp_sync_webface_order_detail.
-- 2. shipping_method se toma desde order_detail.shipping_method y se normaliza a Envio,
--    Tienda o Uber cuando el valor fuente usa variantes comunes.
-- 3. El AFTER UPDATE de orders no pisa status_id si la orden ya avanzo en OrderFlow;
--    solo refresca datos comerciales y financieros.
-- 4. La resolucion de store_id depende de que tbl_stores.universal_sucursal_id este poblado.
-- 5. El emparejamiento de lineas repetidas depende del orden relativo de insercion de order_detail.