-- Démo MVP personnalisation storefront / BAT
-- Produit boutique générique avec template figé, aperçu JPG et PDF source.

INSERT INTO products (
    sku,
    default_name,
    default_description,
    product_type,
    category_name,
    subcategory_name,
    base_spec_json,
    is_active
)
SELECT
    'PERSO-CARTE-DEMO',
    'Carte de visite – personnalisation coordonnées',
    'Produit de démonstration pour le module de personnalisation : le client place ses données sur un template figé puis une preuve PDF est générée.',
    'business_card',
    'Papeterie',
    'Carte de visite',
    JSON_OBJECT(
        'source', '007_personalization_demo',
        'notes', 'Produit démo pour placement Nom / Adresse / Téléphone / Email / Site / Photo',
        'recommended_usage', 'Tester 1 visuel puis 10 visuels x 100 ex'
    ),
    1
WHERE NOT EXISTS (
    SELECT 1 FROM products WHERE sku = 'PERSO-CARTE-DEMO'
);

SET @demo_product_id := (
    SELECT id FROM products WHERE sku = 'PERSO-CARTE-DEMO' ORDER BY id ASC LIMIT 1
);

INSERT INTO product_assets (product_id, asset_type, asset_role, path, position)
SELECT @demo_product_id, 'image', 'cover', '/uploads/seeds/personalization/demo-template-cover.jpg', 1
WHERE @demo_product_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM product_assets
      WHERE product_id = @demo_product_id
        AND asset_role = 'cover'
        AND path = '/uploads/seeds/personalization/demo-template-cover.jpg'
  );

INSERT INTO product_attributes (product_id, code, name, input_type, is_required, is_pricing_quantity, position)
SELECT @demo_product_id, 'quantite_du_lot', 'Quantité du lot', 'select', 1, 1, 10
WHERE @demo_product_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM product_attributes
      WHERE product_id = @demo_product_id AND code = 'quantite_du_lot'
  );

SET @demo_attr_qty_id := (
    SELECT id FROM product_attributes
    WHERE product_id = @demo_product_id AND code = 'quantite_du_lot'
    ORDER BY id ASC LIMIT 1
);

INSERT INTO product_attribute_options (product_attribute_id, value, label, position)
SELECT @demo_attr_qty_id, '10', '10', 10
WHERE @demo_attr_qty_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM product_attribute_options WHERE product_attribute_id = @demo_attr_qty_id AND value = '10');

INSERT INTO product_attribute_options (product_attribute_id, value, label, position)
SELECT @demo_attr_qty_id, '20', '20', 20
WHERE @demo_attr_qty_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM product_attribute_options WHERE product_attribute_id = @demo_attr_qty_id AND value = '20');

INSERT INTO product_attribute_options (product_attribute_id, value, label, position)
SELECT @demo_attr_qty_id, '100', '100', 30
WHERE @demo_attr_qty_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM product_attribute_options WHERE product_attribute_id = @demo_attr_qty_id AND value = '100');

INSERT INTO product_attribute_options (product_attribute_id, value, label, position)
SELECT @demo_attr_qty_id, '5000', '5000', 40
WHERE @demo_attr_qty_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM product_attribute_options WHERE product_attribute_id = @demo_attr_qty_id AND value = '5000');

INSERT INTO price_grids (product_id, code, name, currency, is_active)
SELECT @demo_product_id, 'default', 'Tarif démo personnalisation', 'EUR', 1
WHERE @demo_product_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM price_grids WHERE product_id = @demo_product_id AND code = 'default'
  );

SET @demo_price_grid_id := (
    SELECT id FROM price_grids
    WHERE product_id = @demo_product_id AND code = 'default'
    ORDER BY id ASC LIMIT 1
);

INSERT INTO price_grid_rows (price_grid_id, criteria_key, criteria_json, min_qty, max_qty, nominal_price_ht, cost_price_ht)
SELECT @demo_price_grid_id, 'default', JSON_OBJECT(), 10, 19, 4.80, 3.10
WHERE @demo_price_grid_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM price_grid_rows
      WHERE price_grid_id = @demo_price_grid_id AND criteria_key = 'default' AND min_qty = 10
  );

INSERT INTO price_grid_rows (price_grid_id, criteria_key, criteria_json, min_qty, max_qty, nominal_price_ht, cost_price_ht)
SELECT @demo_price_grid_id, 'default', JSON_OBJECT(), 20, 99, 3.80, 2.40
WHERE @demo_price_grid_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM price_grid_rows
      WHERE price_grid_id = @demo_price_grid_id AND criteria_key = 'default' AND min_qty = 20
  );

INSERT INTO price_grid_rows (price_grid_id, criteria_key, criteria_json, min_qty, max_qty, nominal_price_ht, cost_price_ht)
SELECT @demo_price_grid_id, 'default', JSON_OBJECT(), 100, 4999, 1.20, 0.70
WHERE @demo_price_grid_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM price_grid_rows
      WHERE price_grid_id = @demo_price_grid_id AND criteria_key = 'default' AND min_qty = 100
  );

INSERT INTO price_grid_rows (price_grid_id, criteria_key, criteria_json, min_qty, max_qty, nominal_price_ht, cost_price_ht)
SELECT @demo_price_grid_id, 'default', JSON_OBJECT(), 5000, NULL, 0.22, 0.11
WHERE @demo_price_grid_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM price_grid_rows
      WHERE price_grid_id = @demo_price_grid_id AND criteria_key = 'default' AND min_qty = 5000
  );

INSERT INTO shop_products (
    shop_id,
    source_product_id,
    item_type,
    is_active,
    is_visible,
    override_name,
    override_description,
    override_settings_json
)
SELECT
    1,
    @demo_product_id,
    'activation',
    1,
    1,
    'Carte de visite – personnalisation coordonnées',
    'Démo MVP : le client place ses coordonnées et sa photo sur un template figé, puis une preuve PDF est générée avant BAT.',
    JSON_OBJECT(
        'personalization',
        JSON_OBJECT(
            'enabled', TRUE,
            'allowed_fields', JSON_ARRAY('first_name', 'last_name', 'phone', 'email', 'website', 'photo'),
            'required_fields', JSON_ARRAY('first_name', 'last_name', 'phone', 'email'),
            'field_settings', JSON_OBJECT(
                'first_name', JSON_OBJECT('enabled', TRUE, 'required', TRUE, 'label', 'Prénom', 'max_length', 60, 'display_on_catalog', TRUE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 18, 'width_pct', 28, 'height_pct', 5, 'font_size', 14, 'font_family', 'Helvetica', 'color', '#1f2937')),
                'last_name', JSON_OBJECT('enabled', TRUE, 'required', TRUE, 'label', 'Nom', 'max_length', 80, 'display_on_catalog', TRUE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 24, 'width_pct', 34, 'height_pct', 5, 'font_size', 18, 'font_family', 'Helvetica-Bold', 'color', '#111827')),
                'phone', JSON_OBJECT('enabled', TRUE, 'required', TRUE, 'label', 'Téléphone', 'max_length', 30, 'display_on_catalog', TRUE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 44, 'width_pct', 34, 'height_pct', 5, 'font_size', 11, 'font_family', 'Helvetica', 'color', '#374151')),
                'email', JSON_OBJECT('enabled', TRUE, 'required', TRUE, 'label', 'Email', 'max_length', 100, 'display_on_catalog', TRUE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 50, 'width_pct', 42, 'height_pct', 5, 'font_size', 11, 'font_family', 'Helvetica', 'color', '#374151')),
                'website', JSON_OBJECT('enabled', TRUE, 'required', FALSE, 'label', 'Site', 'max_length', 100, 'display_on_catalog', FALSE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 56, 'width_pct', 42, 'height_pct', 5, 'font_size', 11, 'font_family', 'Helvetica', 'color', '#374151')),
                'photo', JSON_OBJECT('enabled', TRUE, 'required', FALSE, 'label', 'Photo', 'display_on_catalog', FALSE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 70, 'y_pct', 18, 'width_pct', 18, 'height_pct', 24, 'font_size', 12, 'font_family', 'Helvetica', 'color', '#0f172a', 'image_scale_pct', 110, 'image_x_pct', 0, 'image_y_pct', 0, 'border_radius_tl_pct', 50, 'border_radius_tr_pct', 50, 'border_radius_br_pct', 50, 'border_radius_bl_pct', 50))
            ),
            'instructions', 'Renseignez les données prévues pour ce produit boutique.',
            'default_visual_count', 1,
            'page_count', 1,
            'template_asset_role', 'source_pdf'
        )
    )
WHERE @demo_product_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM shop_products
      WHERE shop_id = 1
        AND source_product_id = @demo_product_id
  );

SET @demo_shop_product_id := (
    SELECT id
    FROM shop_products
    WHERE shop_id = 1
      AND source_product_id = @demo_product_id
    ORDER BY id ASC LIMIT 1
);

UPDATE shop_products
SET
    override_name = 'Carte de visite – personnalisation coordonnées',
    override_description = 'Démo MVP : le client place ses coordonnées et sa photo sur un template figé, puis une preuve PDF est générée avant BAT.',
    override_settings_json = JSON_OBJECT(
        'personalization',
        JSON_OBJECT(
            'enabled', TRUE,
            'allowed_fields', JSON_ARRAY('first_name', 'last_name', 'phone', 'email', 'website', 'photo'),
            'required_fields', JSON_ARRAY('first_name', 'last_name', 'phone', 'email'),
            'field_settings', JSON_OBJECT(
                'first_name', JSON_OBJECT('enabled', TRUE, 'required', TRUE, 'label', 'Prénom', 'max_length', 60, 'display_on_catalog', TRUE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 18, 'width_pct', 28, 'height_pct', 5, 'font_size', 14, 'font_family', 'Helvetica', 'color', '#1f2937')),
                'last_name', JSON_OBJECT('enabled', TRUE, 'required', TRUE, 'label', 'Nom', 'max_length', 80, 'display_on_catalog', TRUE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 24, 'width_pct', 34, 'height_pct', 5, 'font_size', 18, 'font_family', 'Helvetica-Bold', 'color', '#111827')),
                'phone', JSON_OBJECT('enabled', TRUE, 'required', TRUE, 'label', 'Téléphone', 'max_length', 30, 'display_on_catalog', TRUE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 44, 'width_pct', 34, 'height_pct', 5, 'font_size', 11, 'font_family', 'Helvetica', 'color', '#374151')),
                'email', JSON_OBJECT('enabled', TRUE, 'required', TRUE, 'label', 'Email', 'max_length', 100, 'display_on_catalog', TRUE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 50, 'width_pct', 42, 'height_pct', 5, 'font_size', 11, 'font_family', 'Helvetica', 'color', '#374151')),
                'website', JSON_OBJECT('enabled', TRUE, 'required', FALSE, 'label', 'Site', 'max_length', 100, 'display_on_catalog', FALSE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 10, 'y_pct', 56, 'width_pct', 42, 'height_pct', 5, 'font_size', 11, 'font_family', 'Helvetica', 'color', '#374151')),
                'photo', JSON_OBJECT('enabled', TRUE, 'required', FALSE, 'label', 'Photo', 'display_on_catalog', FALSE, 'display_on_product', TRUE, 'placement', JSON_OBJECT('page', 1, 'x_pct', 70, 'y_pct', 18, 'width_pct', 18, 'height_pct', 24, 'font_size', 12, 'font_family', 'Helvetica', 'color', '#0f172a', 'image_scale_pct', 110, 'image_x_pct', 0, 'image_y_pct', 0, 'border_radius_tl_pct', 50, 'border_radius_tr_pct', 50, 'border_radius_br_pct', 50, 'border_radius_bl_pct', 50))
            ),
            'instructions', 'Renseignez les données prévues pour ce produit boutique.',
            'default_visual_count', 1,
            'page_count', 1,
            'template_asset_role', 'source_pdf'
        )
    )
WHERE id = @demo_shop_product_id;

INSERT INTO shop_product_assets (shop_product_id, asset_type, asset_role, path, position)
SELECT @demo_shop_product_id, 'pdf', 'source_pdf', '/uploads/seeds/personalization/demo-template-source.pdf', 1
WHERE @demo_shop_product_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM shop_product_assets
      WHERE shop_product_id = @demo_shop_product_id
        AND asset_role = 'source_pdf'
        AND path = '/uploads/seeds/personalization/demo-template-source.pdf'
  );

INSERT INTO shop_product_assets (shop_product_id, asset_type, asset_role, path, position)
SELECT @demo_shop_product_id, 'image', 'pdf_preview', '/uploads/seeds/personalization/demo-template-preview.jpg', 1
WHERE @demo_shop_product_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM shop_product_assets
      WHERE shop_product_id = @demo_shop_product_id
        AND asset_role = 'pdf_preview'
        AND path = '/uploads/seeds/personalization/demo-template-preview.jpg'
  );

INSERT INTO shop_product_assets (shop_product_id, asset_type, asset_role, path, position)
SELECT @demo_shop_product_id, 'image', 'mockup', '/uploads/seeds/personalization/demo-template-mockup.jpg', 1
WHERE @demo_shop_product_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM shop_product_assets
      WHERE shop_product_id = @demo_shop_product_id
        AND asset_role = 'mockup'
        AND path = '/uploads/seeds/personalization/demo-template-mockup.jpg'
  );

INSERT INTO shop_product_assets (shop_product_id, asset_type, asset_role, path, position)
SELECT @demo_shop_product_id, 'image', 'gallery', '/uploads/seeds/personalization/demo-template-cover.jpg', 1
WHERE @demo_shop_product_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM shop_product_assets
      WHERE shop_product_id = @demo_shop_product_id
        AND asset_role = 'gallery'
        AND path = '/uploads/seeds/personalization/demo-template-cover.jpg'
  );
