CREATE DATABASE IF NOT EXISTS universal4shop_orderflowpro_estados
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS universal4shop_orderflowpro_estados.tbl_estados (
  order_reference varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  original_order_reference varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  is_redespacho tinyint(1) NOT NULL DEFAULT 0,
  estado_factura varchar(2) NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_tbl_estados_order_reference (order_reference),
  INDEX idx_tbl_estados_original_order_reference (original_order_reference),
  INDEX idx_tbl_estados_is_redespacho (is_redespacho)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TRIGGER IF EXISTS universal4shop_oms.trg_tbl_orders_ai_sync_estados;
DROP TRIGGER IF EXISTS universal4shop_oms.trg_tbl_orders_au_sync_estados;

UPDATE universal4shop_orderflowpro_estados.tbl_estados AS estados
JOIN universal4shop_oms.tbl_orders AS ordenes
  ON estados.order_reference COLLATE utf8mb4_unicode_ci = ordenes.order_reference COLLATE utf8mb4_unicode_ci
SET estados.estado_factura = CASE ordenes.status_id
    WHEN 4 THEN 'LE'
    WHEN 5 THEN 'LR'
  WHEN 8 THEN 'CA'
    ELSE estados.estado_factura
  END,
    estados.original_order_reference = NULLIF(TRIM(ordenes.original_order_reference), ''),
    estados.is_redespacho = CASE
      WHEN NULLIF(TRIM(ordenes.original_order_reference), '') IS NULL THEN 0
      ELSE 1
    END,
    estados.updated_at = CURRENT_TIMESTAMP
WHERE ordenes.status_id IN (4, 5, 8)
  AND ordenes.order_reference IS NOT NULL;

INSERT INTO universal4shop_orderflowpro_estados.tbl_estados (
  order_reference,
  original_order_reference,
  is_redespacho,
  estado_factura,
  created_at,
  updated_at
)
SELECT
  ordenes.order_reference COLLATE utf8mb4_unicode_ci,
  NULLIF(TRIM(ordenes.original_order_reference), '') COLLATE utf8mb4_unicode_ci,
  CASE
    WHEN NULLIF(TRIM(ordenes.original_order_reference), '') IS NULL THEN 0
    ELSE 1
  END,
  CASE ordenes.status_id
    WHEN 4 THEN 'LE'
    WHEN 5 THEN 'LR'
    WHEN 8 THEN 'CA'
  END,
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
FROM universal4shop_oms.tbl_orders AS ordenes
WHERE NOT EXISTS (
  SELECT 1
  FROM universal4shop_orderflowpro_estados.tbl_estados AS estados
  WHERE estados.order_reference COLLATE utf8mb4_unicode_ci = ordenes.order_reference COLLATE utf8mb4_unicode_ci
)
  AND ordenes.status_id IN (4, 5, 8)
  AND ordenes.order_reference IS NOT NULL;

DELIMITER //

CREATE TRIGGER universal4shop_oms.trg_tbl_orders_ai_sync_estados
AFTER INSERT ON universal4shop_oms.tbl_orders
FOR EACH ROW
BEGIN
  DECLARE v_estado_factura varchar(2);
  DECLARE v_original_order_reference varchar(9);
  DECLARE v_is_redespacho tinyint(1);

  SET v_estado_factura = CASE NEW.status_id
    WHEN 4 THEN 'LE'
    WHEN 5 THEN 'LR'
    WHEN 8 THEN 'CA'
    ELSE NULL
  END;

  SET v_original_order_reference = NULLIF(TRIM(NEW.original_order_reference), '');
  SET v_is_redespacho = CASE
    WHEN v_original_order_reference IS NULL THEN 0
    ELSE 1
  END;

  IF NEW.order_reference IS NOT NULL AND v_estado_factura IS NOT NULL THEN
    UPDATE universal4shop_orderflowpro_estados.tbl_estados
    SET original_order_reference = v_original_order_reference,
        is_redespacho = v_is_redespacho,
        estado_factura = v_estado_factura,
        updated_at = CURRENT_TIMESTAMP
    WHERE order_reference COLLATE utf8mb4_unicode_ci = NEW.order_reference COLLATE utf8mb4_unicode_ci;

    IF ROW_COUNT() = 0 THEN
      INSERT INTO universal4shop_orderflowpro_estados.tbl_estados (
        order_reference,
        original_order_reference,
        is_redespacho,
        estado_factura,
        created_at,
        updated_at
      ) VALUES (
        NEW.order_reference,
        v_original_order_reference,
        v_is_redespacho,
        v_estado_factura,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
      );
    END IF;
  END IF;
END//

CREATE TRIGGER universal4shop_oms.trg_tbl_orders_au_sync_estados
AFTER UPDATE ON universal4shop_oms.tbl_orders
FOR EACH ROW
BEGIN
  DECLARE v_estado_factura varchar(2);
  DECLARE v_original_order_reference varchar(9);
  DECLARE v_is_redespacho tinyint(1);

  IF (NOT (NEW.status_id <=> OLD.status_id)
      OR NOT (NEW.original_order_reference <=> OLD.original_order_reference)
      OR NOT (NEW.order_reference <=> OLD.order_reference))
     AND NEW.order_reference IS NOT NULL THEN
    SET v_estado_factura = CASE NEW.status_id
      WHEN 4 THEN 'LE'
      WHEN 5 THEN 'LR'
      WHEN 8 THEN 'CA'
      ELSE NULL
    END;

    SET v_original_order_reference = NULLIF(TRIM(NEW.original_order_reference), '');
    SET v_is_redespacho = CASE
      WHEN v_original_order_reference IS NULL THEN 0
      ELSE 1
    END;

    IF v_estado_factura IS NOT NULL THEN
      UPDATE universal4shop_orderflowpro_estados.tbl_estados
      SET original_order_reference = v_original_order_reference,
          is_redespacho = v_is_redespacho,
          estado_factura = v_estado_factura,
          updated_at = CURRENT_TIMESTAMP
      WHERE order_reference COLLATE utf8mb4_unicode_ci = NEW.order_reference COLLATE utf8mb4_unicode_ci;

      IF ROW_COUNT() = 0 THEN
        INSERT INTO universal4shop_orderflowpro_estados.tbl_estados (
          order_reference,
          original_order_reference,
          is_redespacho,
          estado_factura,
          created_at,
          updated_at
        ) VALUES (
          NEW.order_reference,
          v_original_order_reference,
          v_is_redespacho,
          v_estado_factura,
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        );
      END IF;
    END IF;
  END IF;
END//

DELIMITER ;