INSERT INTO product_packs (network_id, code, name, description, is_active)
SELECT NULL, 'starter-pack-demo', 'Pack de démarrage démo', 'Pack de démonstration pour les imports utilisateurs', 1
WHERE NOT EXISTS (SELECT 1 FROM product_packs WHERE code = 'starter-pack-demo');

INSERT INTO product_pack_items (pack_id, source_product_id, shop_id, quantity, item_order, title_override)
SELECT pp.id, 1, 2, 1, 10, 'Flyer de démarrage'
FROM product_packs pp
WHERE pp.code = 'starter-pack-demo'
  AND NOT EXISTS (
      SELECT 1 FROM product_pack_items ppi
      WHERE ppi.pack_id = pp.id AND ppi.source_product_id = 1
  );

INSERT INTO product_pack_items (pack_id, source_product_id, shop_id, quantity, item_order, title_override)
SELECT pp.id, 2, 2, 1, 20, 'Roll-up de démarrage'
FROM product_packs pp
WHERE pp.code = 'starter-pack-demo'
  AND NOT EXISTS (
      SELECT 1 FROM product_pack_items ppi
      WHERE ppi.pack_id = pp.id AND ppi.source_product_id = 2
  );

INSERT INTO order_bat_requests (order_id, order_item_id, title, message, asset_type, asset_path, status, sent_by_user_id, sent_at)
SELECT
    o.id,
    oi.id,
    'BAT initial démo',
    'Merci de valider ce BAT de démonstration.',
    'mockup',
    (
        SELECT spa.path
        FROM shop_product_assets spa
        WHERE spa.shop_product_id = oi.shop_product_id
          AND spa.asset_role = 'mockup'
        ORDER BY spa.position ASC
        LIMIT 1
    ),
    'sent',
    1,
    NOW()
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
WHERE o.order_number = 'ORD-000001'
  AND NOT EXISTS (
      SELECT 1 FROM order_bat_requests obr WHERE obr.order_id = o.id
  )
LIMIT 1;

UPDATE orders
SET status = 'in_bat',
    bat_sent_at = NOW()
WHERE order_number = 'ORD-000001'
  AND EXISTS (SELECT 1 FROM order_bat_requests obr WHERE obr.order_id = orders.id);
