INSERT INTO shipping_zones (scope_type, network_id, shop_id, code, name, description, country_code, postal_code_pattern, is_active)
SELECT 'global', NULL, NULL, 'FR-METRO', 'France métropolitaine', 'Zone standard France métropolitaine', 'FR', NULL, 1
WHERE NOT EXISTS (
    SELECT 1 FROM shipping_zones WHERE code = 'FR-METRO' AND scope_type = 'global' AND network_id IS NULL AND shop_id IS NULL
);

INSERT INTO shipping_zones (scope_type, network_id, shop_id, code, name, description, country_code, postal_code_pattern, is_active)
SELECT 'global', NULL, NULL, 'FR-CORSE', 'Corse', 'Zone spécifique avec exclusion de démonstration', 'FR', '20*', 1
WHERE NOT EXISTS (
    SELECT 1 FROM shipping_zones WHERE code = 'FR-CORSE' AND scope_type = 'global' AND network_id IS NULL AND shop_id IS NULL
);

INSERT INTO shipping_methods (scope_type, network_id, shop_id, code, name, description, carrier_code, base_amount_ht, variable_percent, free_shipping_threshold_ht, tax_rate, is_default, is_active)
SELECT 'global', NULL, NULL, 'standard', 'Livraison standard', 'Livraison standard démo', 'STD', 7.50, 0.0000, 250.00, 20.0000, 1, 1
WHERE NOT EXISTS (
    SELECT 1 FROM shipping_methods WHERE code = 'standard' AND scope_type = 'global' AND network_id IS NULL AND shop_id IS NULL
);

INSERT INTO shipping_methods (scope_type, network_id, shop_id, code, name, description, carrier_code, base_amount_ht, variable_percent, free_shipping_threshold_ht, tax_rate, is_default, is_active)
SELECT 'global', NULL, NULL, 'express', 'Livraison express', 'Livraison express démo', 'EXP', 14.50, 0.0000, NULL, 20.0000, 0, 1
WHERE NOT EXISTS (
    SELECT 1 FROM shipping_methods WHERE code = 'express' AND scope_type = 'global' AND network_id IS NULL AND shop_id IS NULL
);

INSERT INTO shipping_method_rates (shipping_method_id, shipping_zone_id, min_order_amount_ht, max_order_amount_ht, flat_amount_ht, variable_percent, item_order, is_active)
SELECT sm.id, sz.id, 0.00, 99.99, 9.50, 0.0000, 10, 1
FROM shipping_methods sm
INNER JOIN shipping_zones sz ON sz.code = 'FR-METRO' AND sz.scope_type = 'global'
WHERE sm.code = 'standard'
  AND sm.scope_type = 'global'
  AND NOT EXISTS (
      SELECT 1
      FROM shipping_method_rates r
      WHERE r.shipping_method_id = sm.id
        AND r.shipping_zone_id = sz.id
        AND COALESCE(r.min_order_amount_ht, 0.00) = 0.00
        AND COALESCE(r.max_order_amount_ht, 0.00) = 99.99
  );

INSERT INTO shipping_method_rates (shipping_method_id, shipping_zone_id, min_order_amount_ht, max_order_amount_ht, flat_amount_ht, variable_percent, item_order, is_active)
SELECT sm.id, sz.id, 100.00, 249.99, 4.90, 0.0000, 20, 1
FROM shipping_methods sm
INNER JOIN shipping_zones sz ON sz.code = 'FR-METRO' AND sz.scope_type = 'global'
WHERE sm.code = 'standard'
  AND sm.scope_type = 'global'
  AND NOT EXISTS (
      SELECT 1
      FROM shipping_method_rates r
      WHERE r.shipping_method_id = sm.id
        AND r.shipping_zone_id = sz.id
        AND COALESCE(r.min_order_amount_ht, 0.00) = 100.00
        AND COALESCE(r.max_order_amount_ht, 0.00) = 249.99
  );

INSERT INTO shipping_method_rates (shipping_method_id, shipping_zone_id, min_order_amount_ht, max_order_amount_ht, flat_amount_ht, variable_percent, item_order, is_active)
SELECT sm.id, sz.id, 0.00, NULL, 16.00, 0.0000, 10, 1
FROM shipping_methods sm
INNER JOIN shipping_zones sz ON sz.code = 'FR-METRO' AND sz.scope_type = 'global'
WHERE sm.code = 'express'
  AND sm.scope_type = 'global'
  AND NOT EXISTS (
      SELECT 1
      FROM shipping_method_rates r
      WHERE r.shipping_method_id = sm.id
        AND r.shipping_zone_id = sz.id
        AND COALESCE(r.min_order_amount_ht, 0.00) = 0.00
  );

INSERT INTO shipping_method_exclusions (shipping_method_id, shipping_zone_id, reason)
SELECT sm.id, sz.id, 'Exclusion démo Corse'
FROM shipping_methods sm
INNER JOIN shipping_zones sz ON sz.code = 'FR-CORSE' AND sz.scope_type = 'global'
WHERE sm.code = 'express'
  AND sm.scope_type = 'global'
  AND NOT EXISTS (
      SELECT 1
      FROM shipping_method_exclusions e
      WHERE e.shipping_method_id = sm.id
        AND e.shipping_zone_id = sz.id
  );

UPDATE orders o
LEFT JOIN shipping_methods sm ON sm.code = 'standard' AND sm.scope_type = 'global'
SET
    o.shipping_method_id = sm.id,
    o.shipping_method_label = sm.name,
    o.shipping_amount_ht = 4.90,
    o.shipping_amount_ttc = 5.88,
    o.shipping_tax_rate = 20.0000,
    o.shipping_rule_snapshot_json = JSON_OBJECT(
        'status', 'matched',
        'method_code', sm.code,
        'method_name', sm.name,
        'zone_code', 'FR-METRO',
        'zone_name', 'France métropolitaine',
        'rule_label', '100-249.99 HT'
    ),
    o.total_ht = GREATEST(o.subtotal_ht - o.credit_used_ht, 0.00) + 4.90,
    o.total_ttc = GREATEST(o.subtotal_ht - o.credit_used_ht, 0.00) + 5.88
WHERE o.order_number = 'ORD-000001'
  AND o.shipping_method_id IS NULL
  AND sm.id IS NOT NULL;
