
-- Comptes front de démonstration
-- Mot de passe commun : admin1234

INSERT INTO users (network_id, external_identifier, email, password_hash, first_name, last_name, phone, status, created_by_user_id)
SELECT NULL, 'GEN-CLIENT-001', 'boutique.demo@example.test', '$2y$12$e40bYvCEAPIF10NVwHM8C..LtYxxhyowVcZSUpwNi3SFuUsfDi.le', 'Lucie', 'Martin', '0601000001', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'boutique.demo@example.test');

INSERT INTO users (network_id, external_identifier, email, password_hash, first_name, last_name, phone, status, created_by_user_id)
SELECT 1, 'ALPHA-AGENT-010', 'agence.demo@example.test', '$2y$12$e40bYvCEAPIF10NVwHM8C..LtYxxhyowVcZSUpwNi3SFuUsfDi.le', 'Thomas', 'Roux', '0601000002', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'agence.demo@example.test');

INSERT INTO users (network_id, external_identifier, email, password_hash, first_name, last_name, phone, status, created_by_user_id)
SELECT 1, 'ALPHA-MGR-010', 'agence.manager.demo@example.test', '$2y$12$e40bYvCEAPIF10NVwHM8C..LtYxxhyowVcZSUpwNi3SFuUsfDi.le', 'Emma', 'Garnier', '0601000003', 'active', 1
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'agence.manager.demo@example.test');

INSERT INTO user_shop_memberships (user_id, shop_id, role_code, is_active)
SELECT u.id, 1, 'independent_agent', 1
FROM users u
WHERE u.email = 'boutique.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM user_shop_memberships usm
      WHERE usm.user_id = u.id AND usm.shop_id = 1 AND usm.role_code = 'independent_agent'
  );

INSERT INTO user_network_memberships (user_id, network_id, role_code, is_active)
SELECT u.id, 1, 'agency_agent', 1
FROM users u
WHERE u.email = 'agence.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM user_network_memberships unm
      WHERE unm.user_id = u.id AND unm.network_id = 1 AND unm.role_code = 'agency_agent'
  );

INSERT INTO user_shop_memberships (user_id, shop_id, role_code, is_active)
SELECT u.id, 2, 'agency_agent', 1
FROM users u
WHERE u.email = 'agence.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM user_shop_memberships usm
      WHERE usm.user_id = u.id AND usm.shop_id = 2 AND usm.role_code = 'agency_agent'
  );

INSERT INTO user_network_memberships (user_id, network_id, role_code, is_active)
SELECT u.id, 1, 'agency_manager', 1
FROM users u
WHERE u.email = 'agence.manager.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM user_network_memberships unm
      WHERE unm.user_id = u.id AND unm.network_id = 1 AND unm.role_code = 'agency_manager'
  );

INSERT INTO user_shop_memberships (user_id, shop_id, role_code, is_active)
SELECT u.id, 2, 'agency_manager', 1
FROM users u
WHERE u.email = 'agence.manager.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM user_shop_memberships usm
      WHERE usm.user_id = u.id AND usm.shop_id = 2 AND usm.role_code = 'agency_manager'
  );

INSERT INTO addresses (user_id, type, label, company, line1, line2, postal_code, city, country, phone, is_default)
SELECT u.id, 'billing', 'Facturation', 'Boutique Démo', '16 rue des Carmes', 'Bâtiment B', '75005', 'Paris', 'FR', '0601000001', 1
FROM users u
WHERE u.email = 'boutique.demo@example.test'
  AND NOT EXISTS (SELECT 1 FROM addresses a WHERE a.user_id = u.id AND a.type = 'billing');

INSERT INTO addresses (user_id, type, label, company, line1, line2, postal_code, city, country, phone, is_default)
SELECT u.id, 'shipping', 'Livraison', 'Boutique Démo', '16 rue des Carmes', 'Bâtiment B', '75005', 'Paris', 'FR', '0601000001', 1
FROM users u
WHERE u.email = 'boutique.demo@example.test'
  AND NOT EXISTS (SELECT 1 FROM addresses a WHERE a.user_id = u.id AND a.type = 'shipping');

INSERT INTO addresses (user_id, type, label, company, line1, line2, postal_code, city, country, phone, is_default)
SELECT u.id, 'billing', 'Facturation', 'Agence Alpha Centre', '24 avenue Jean Jaurès', NULL, '69007', 'Lyon', 'FR', '0601000002', 1
FROM users u
WHERE u.email = 'agence.demo@example.test'
  AND NOT EXISTS (SELECT 1 FROM addresses a WHERE a.user_id = u.id AND a.type = 'billing');

INSERT INTO addresses (user_id, type, label, company, line1, line2, postal_code, city, country, phone, is_default)
SELECT u.id, 'shipping', 'Livraison', 'Agence Alpha Centre', '24 avenue Jean Jaurès', 'Quai logistique', '69007', 'Lyon', 'FR', '0601000002', 1
FROM users u
WHERE u.email = 'agence.demo@example.test'
  AND NOT EXISTS (SELECT 1 FROM addresses a WHERE a.user_id = u.id AND a.type = 'shipping');

-- Crédits de démonstration
INSERT INTO credit_accounts (owner_type, owner_id, currency, balance_cached)
SELECT 'user', u.id, 'EUR', 0.00
FROM users u
WHERE u.email IN ('boutique.demo@example.test', 'agence.demo@example.test')
  AND NOT EXISTS (
      SELECT 1 FROM credit_accounts ca WHERE ca.owner_type = 'user' AND ca.owner_id = u.id
  );

SET @has_credit_settlement_status := (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'credit_transactions'
      AND COLUMN_NAME = 'settlement_status'
);

SET @seed_credit_sql := IF(
    @has_credit_settlement_status > 0,
    "
INSERT INTO credit_transactions (account_id, movement_type, settlement_status, amount, reference, reason, source_type, source_id, metadata_json, created_by_user_id)
SELECT ca.id, 'grant', 'settled', 180.00, 'SEED-FRONT-180', 'Crédit de démonstration boutique front', 'seed', NULL, JSON_OBJECT('seed', '006_storefront_test_data'), 1
FROM credit_accounts ca
INNER JOIN users u ON u.id = ca.owner_id
WHERE ca.owner_type = 'user'
  AND u.email = 'boutique.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM credit_transactions ct WHERE ct.account_id = ca.id AND ct.reference = 'SEED-FRONT-180'
  );

INSERT INTO credit_transactions (account_id, movement_type, settlement_status, amount, reference, reason, source_type, source_id, metadata_json, created_by_user_id)
SELECT ca.id, 'grant', 'settled', 420.00, 'SEED-ALPHA-420', 'Crédit de démonstration agence', 'seed', NULL, JSON_OBJECT('seed', '006_storefront_test_data'), 1
FROM credit_accounts ca
INNER JOIN users u ON u.id = ca.owner_id
WHERE ca.owner_type = 'user'
  AND u.email = 'agence.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM credit_transactions ct WHERE ct.account_id = ca.id AND ct.reference = 'SEED-ALPHA-420'
  );
",
    "
INSERT INTO credit_transactions (account_id, movement_type, amount, reference, reason, created_by_user_id)
SELECT ca.id, 'grant', 180.00, 'SEED-FRONT-180', 'Crédit de démonstration boutique front', 1
FROM credit_accounts ca
INNER JOIN users u ON u.id = ca.owner_id
WHERE ca.owner_type = 'user'
  AND u.email = 'boutique.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM credit_transactions ct WHERE ct.account_id = ca.id AND ct.reference = 'SEED-FRONT-180'
  );

INSERT INTO credit_transactions (account_id, movement_type, amount, reference, reason, created_by_user_id)
SELECT ca.id, 'grant', 420.00, 'SEED-ALPHA-420', 'Crédit de démonstration agence', 1
FROM credit_accounts ca
INNER JOIN users u ON u.id = ca.owner_id
WHERE ca.owner_type = 'user'
  AND u.email = 'agence.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM credit_transactions ct WHERE ct.account_id = ca.id AND ct.reference = 'SEED-ALPHA-420'
  );
"
);

PREPARE storefront_seed_credit_stmt FROM @seed_credit_sql;
EXECUTE storefront_seed_credit_stmt;
DEALLOCATE PREPARE storefront_seed_credit_stmt;


-- Commandes de démonstration boutique générique
INSERT INTO orders (
    shop_id, user_id, order_number, status,
    billing_address_json, shipping_address_json,
    shipping_method_id, shipping_method_label,
    subtotal_ht, credit_used_ht, shipping_amount_ht, shipping_amount_ttc, shipping_tax_rate, shipping_rule_snapshot_json,
    total_ht, total_ttc, internal_note,
    submitted_at, fulfilled_at
)
SELECT
    1,
    u.id,
    'ORD-TEST-1001',
    'submitted',
    JSON_OBJECT('company', 'Boutique Démo', 'line1', '16 rue des Carmes', 'line2', 'Bâtiment B', 'postal_code', '75005', 'city', 'Paris', 'country', 'FR', 'phone', '0601000001'),
    JSON_OBJECT('company', 'Boutique Démo', 'line1', '16 rue des Carmes', 'line2', 'Bâtiment B', 'postal_code', '75005', 'city', 'Paris', 'country', 'FR', 'phone', '0601000001'),
    (SELECT sm.id FROM shipping_methods sm WHERE sm.code = 'standard' AND sm.scope_type = 'global' ORDER BY sm.id ASC LIMIT 1),
    (SELECT sm.name FROM shipping_methods sm WHERE sm.code = 'standard' AND sm.scope_type = 'global' ORDER BY sm.id ASC LIMIT 1),
    49.20,
    0.00,
    9.50,
    11.40,
    20.0000,
    JSON_OBJECT('status', 'matched', 'method_code', 'standard', 'zone_code', 'FR-METRO', 'seed', '006_storefront_test_data'),
    58.70,
    60.60,
    'Commande de test simple en attente de traitement.',
    DATE_SUB(NOW(), INTERVAL 4 DAY),
    NULL
FROM users u
WHERE u.email = 'boutique.demo@example.test'
  AND NOT EXISTS (SELECT 1 FROM orders WHERE order_number = 'ORD-TEST-1001');

INSERT INTO order_items (
    order_id, source_product_id, shop_product_id, label, quantity,
    unit_nominal_ht, applied_coefficient, unit_sale_ht, line_total_ht, configuration_json
)
SELECT
    o.id,
    3,
    6,
    'Mug personnalisé - lot de 12',
    12,
    4.10,
    1.0000,
    4.10,
    49.20,
    JSON_OBJECT('criteria', JSON_OBJECT(), 'criteria_labels', JSON_OBJECT(), 'seed', 'storefront')
FROM orders o
WHERE o.order_number = 'ORD-TEST-1001'
  AND NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id);

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, NULL, 'submitted', 'Commande boutique front soumise pour test.', 1, DATE_SUB(NOW(), INTERVAL 4 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-1001'
  AND NOT EXISTS (
      SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'submitted'
  );

INSERT INTO orders (
    shop_id, user_id, order_number, status,
    billing_address_json, shipping_address_json,
    shipping_method_id, shipping_method_label,
    subtotal_ht, credit_used_ht, shipping_amount_ht, shipping_amount_ttc, shipping_tax_rate, shipping_rule_snapshot_json,
    total_ht, total_ttc, internal_note,
    submitted_at, paid_at, shipped_at, fulfilled_at
)
SELECT
    1,
    u.id,
    'ORD-TEST-1002',
    'fulfilled',
    JSON_OBJECT('company', 'Boutique Démo', 'line1', '16 rue des Carmes', 'line2', 'Bâtiment B', 'postal_code', '75005', 'city', 'Paris', 'country', 'FR', 'phone', '0601000001'),
    JSON_OBJECT('company', 'Boutique Démo', 'line1', '16 rue des Carmes', 'line2', 'Bâtiment B', 'postal_code', '75005', 'city', 'Paris', 'country', 'FR', 'phone', '0601000001'),
    (SELECT sm.id FROM shipping_methods sm WHERE sm.code = 'standard' AND sm.scope_type = 'global' ORDER BY sm.id ASC LIMIT 1),
    (SELECT sm.name FROM shipping_methods sm WHERE sm.code = 'standard' AND sm.scope_type = 'global' ORDER BY sm.id ASC LIMIT 1),
    82.50,
    15.00,
    4.90,
    5.88,
    20.0000,
    JSON_OBJECT('status', 'matched', 'method_code', 'standard', 'zone_code', 'FR-METRO', 'seed', '006_storefront_test_data'),
    72.40,
    73.38,
    'Commande historique livrée et clôturée.',
    DATE_SUB(NOW(), INTERVAL 22 DAY),
    DATE_SUB(NOW(), INTERVAL 21 DAY),
    DATE_SUB(NOW(), INTERVAL 19 DAY),
    DATE_SUB(NOW(), INTERVAL 16 DAY)
FROM users u
WHERE u.email = 'boutique.demo@example.test'
  AND NOT EXISTS (SELECT 1 FROM orders WHERE order_number = 'ORD-TEST-1002');

INSERT INTO order_items (
    order_id, source_product_id, shop_product_id, label, quantity,
    unit_nominal_ht, applied_coefficient, unit_sale_ht, line_total_ht, configuration_json
)
SELECT
    o.id,
    2,
    2,
    'Roll-up 85x200',
    1,
    55.00,
    1.5000,
    82.50,
    82.50,
    JSON_OBJECT('criteria', JSON_OBJECT('format', '85x200'), 'criteria_labels', JSON_OBJECT('format', JSON_OBJECT('name', 'Format', 'label', '85 x 200 cm', 'value', '85x200')), 'seed', 'storefront')
FROM orders o
WHERE o.order_number = 'ORD-TEST-1002'
  AND NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id);

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, NULL, 'submitted', 'Commande boutique front soumise.', 1, DATE_SUB(NOW(), INTERVAL 22 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-1002'
  AND NOT EXISTS (
      SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'submitted' AND osh.created_at = DATE_SUB(NOW(), INTERVAL 22 DAY)
  );

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, 'submitted', 'paid', 'Paiement validé manuellement pour la démo.', 1, DATE_SUB(NOW(), INTERVAL 21 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-1002'
  AND NOT EXISTS (
      SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'paid'
  );

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, 'paid', 'shipped', 'Commande expédiée.', 1, DATE_SUB(NOW(), INTERVAL 19 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-1002'
  AND NOT EXISTS (
      SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'shipped'
  );

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, 'shipped', 'fulfilled', 'Commande réceptionnée et clôturée.', 1, DATE_SUB(NOW(), INTERVAL 16 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-1002'
  AND NOT EXISTS (
      SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'fulfilled'
  );

-- Commandes de démonstration réseau / BAT
INSERT INTO orders (
    shop_id, user_id, order_number, status,
    billing_address_json, shipping_address_json,
    shipping_method_id, shipping_method_label,
    subtotal_ht, credit_used_ht, shipping_amount_ht, shipping_amount_ttc, shipping_tax_rate, shipping_rule_snapshot_json,
    total_ht, total_ttc, internal_note,
    submitted_at, paid_at, bat_sent_at
)
SELECT
    2,
    u.id,
    'ORD-TEST-2001',
    'in_bat',
    JSON_OBJECT('company', 'Agence Alpha Centre', 'line1', '24 avenue Jean Jaurès', 'postal_code', '69007', 'city', 'Lyon', 'country', 'FR', 'phone', '0601000002'),
    JSON_OBJECT('company', 'Agence Alpha Centre', 'line1', '24 avenue Jean Jaurès', 'line2', 'Quai logistique', 'postal_code', '69007', 'city', 'Lyon', 'country', 'FR', 'phone', '0601000002'),
    (SELECT sm.id FROM shipping_methods sm WHERE sm.code = 'express' AND sm.scope_type = 'global' ORDER BY sm.id ASC LIMIT 1),
    (SELECT sm.name FROM shipping_methods sm WHERE sm.code = 'express' AND sm.scope_type = 'global' ORDER BY sm.id ASC LIMIT 1),
    117.00,
    0.00,
    14.50,
    17.40,
    20.0000,
    JSON_OBJECT('status', 'matched', 'method_code', 'express', 'zone_code', 'FR-METRO', 'seed', '006_storefront_test_data'),
    131.50,
    134.40,
    'Commande BAT en attente de validation client.',
    DATE_SUB(NOW(), INTERVAL 6 DAY),
    DATE_SUB(NOW(), INTERVAL 5 DAY),
    DATE_SUB(NOW(), INTERVAL 4 DAY)
FROM users u
WHERE u.email = 'agence.demo@example.test'
  AND NOT EXISTS (SELECT 1 FROM orders WHERE order_number = 'ORD-TEST-2001');

INSERT INTO order_items (
    order_id, source_product_id, shop_product_id, label, quantity,
    unit_nominal_ht, applied_coefficient, unit_sale_ht, line_total_ht, configuration_json
)
SELECT
    o.id,
    1,
    4,
    'Flyers Promo Été - 500 ex',
    500,
    0.18,
    1.3000,
    0.23,
    115.00,
    JSON_OBJECT('criteria', JSON_OBJECT('format', 'A5'), 'criteria_labels', JSON_OBJECT('format', JSON_OBJECT('name', 'Format', 'label', 'A5', 'value', 'A5')), 'seed', 'storefront')
FROM orders o
WHERE o.order_number = 'ORD-TEST-2001'
  AND NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id);

INSERT INTO order_items (
    order_id, source_product_id, shop_product_id, label, quantity,
    unit_nominal_ht, applied_coefficient, unit_sale_ht, line_total_ht, configuration_json
)
SELECT
    o.id,
    2,
    5,
    'Roll-up 85x200',
    1,
    55.00,
    1.3000,
    71.50,
    71.50,
    JSON_OBJECT('criteria', JSON_OBJECT('format', '85x200'), 'criteria_labels', JSON_OBJECT('format', JSON_OBJECT('name', 'Format', 'label', '85 x 200 cm', 'value', '85x200')), 'seed', 'storefront')
FROM orders o
WHERE o.order_number = 'ORD-TEST-2001'
  AND NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id AND oi.source_product_id = 2);

UPDATE orders
SET subtotal_ht = (
    SELECT COALESCE(SUM(oi.line_total_ht), 0.00)
    FROM order_items oi
    WHERE oi.order_id = orders.id
)
WHERE order_number = 'ORD-TEST-2001';

UPDATE orders
SET total_ht = ROUND((subtotal_ht - credit_used_ht) + shipping_amount_ht, 2),
    total_ttc = ROUND((subtotal_ht - credit_used_ht) + shipping_amount_ttc, 2)
WHERE order_number = 'ORD-TEST-2001';

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, NULL, 'submitted', 'Commande réseau soumise.', 1, DATE_SUB(NOW(), INTERVAL 6 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-2001'
  AND NOT EXISTS (SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'submitted');

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, 'submitted', 'paid', 'Règlement reçu.', 1, DATE_SUB(NOW(), INTERVAL 5 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-2001'
  AND NOT EXISTS (SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'paid' AND osh.comment = 'Règlement reçu.');

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, 'paid', 'in_bat', 'BAT transmis au client.', 1, DATE_SUB(NOW(), INTERVAL 4 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-2001'
  AND NOT EXISTS (SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'in_bat');

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 campagne vitrine',
    'Merci de valider le visuel ou de demander une correction.',
    'mockup',
    '/assets/shop/franchise-a/flyer-promo-ete.jpg',
    'commented',
    1,
    DATE_SUB(NOW(), INTERVAL 4 DAY)
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
WHERE o.order_number = 'ORD-TEST-2001'
  AND oi.source_product_id = 1
  AND NOT EXISTS (SELECT 1 FROM order_bat_requests obr WHERE obr.order_id = o.id);

INSERT INTO order_bat_comments (bat_request_id, order_id, created_by_user_id, author_scope, action_type, comment, created_at)
SELECT
    obr.id,
    obr.order_id,
    1,
    'admin',
    'comment',
    'Merci de vérifier le bandeau supérieur avant validation.',
    DATE_SUB(NOW(), INTERVAL 3 DAY)
FROM order_bat_requests obr
INNER JOIN orders o ON o.id = obr.order_id
WHERE o.order_number = 'ORD-TEST-2001'
  AND NOT EXISTS (
      SELECT 1 FROM order_bat_comments obc WHERE obc.bat_request_id = obr.id AND obc.author_scope = 'admin'
  );

INSERT INTO order_bat_comments (bat_request_id, order_id, created_by_user_id, author_scope, action_type, comment, created_at)
SELECT
    obr.id,
    obr.order_id,
    u.id,
    'user',
    'changes_requested',
    'Merci de grossir légèrement le logo et de réduire le fond orange.',
    DATE_SUB(NOW(), INTERVAL 2 DAY)
FROM order_bat_requests obr
INNER JOIN orders o ON o.id = obr.order_id
INNER JOIN users u ON u.email = 'agence.demo@example.test'
WHERE o.order_number = 'ORD-TEST-2001'
  AND NOT EXISTS (
      SELECT 1 FROM order_bat_comments obc WHERE obc.bat_request_id = obr.id AND obc.author_scope = 'user'
  );

INSERT INTO orders (
    shop_id, user_id, order_number, status,
    billing_address_json, shipping_address_json,
    shipping_method_id, shipping_method_label,
    subtotal_ht, credit_used_ht, shipping_amount_ht, shipping_amount_ttc, shipping_tax_rate, shipping_rule_snapshot_json,
    total_ht, total_ttc, internal_note,
    submitted_at, paid_at, shipped_at
)
SELECT
    2,
    u.id,
    'ORD-TEST-2002',
    'shipped',
    JSON_OBJECT('company', 'Agence Alpha Centre', 'line1', '24 avenue Jean Jaurès', 'postal_code', '69007', 'city', 'Lyon', 'country', 'FR', 'phone', '0601000002'),
    JSON_OBJECT('company', 'Agence Alpha Centre', 'line1', '24 avenue Jean Jaurès', 'line2', 'Quai logistique', 'postal_code', '69007', 'city', 'Lyon', 'country', 'FR', 'phone', '0601000002'),
    (SELECT sm.id FROM shipping_methods sm WHERE sm.code = 'standard' AND sm.scope_type = 'global' ORDER BY sm.id ASC LIMIT 1),
    (SELECT sm.name FROM shipping_methods sm WHERE sm.code = 'standard' AND sm.scope_type = 'global' ORDER BY sm.id ASC LIMIT 1),
    120.00,
    35.00,
    4.90,
    5.88,
    20.0000,
    JSON_OBJECT('status', 'matched', 'method_code', 'standard', 'zone_code', 'FR-METRO', 'seed', '006_storefront_test_data'),
    89.90,
    90.88,
    'Commande expédiée avec utilisation de crédit.',
    DATE_SUB(NOW(), INTERVAL 11 DAY),
    DATE_SUB(NOW(), INTERVAL 10 DAY),
    DATE_SUB(NOW(), INTERVAL 8 DAY)
FROM users u
WHERE u.email = 'agence.demo@example.test'
  AND NOT EXISTS (SELECT 1 FROM orders WHERE order_number = 'ORD-TEST-2002');

INSERT INTO order_items (
    order_id, source_product_id, shop_product_id, label, quantity,
    unit_nominal_ht, applied_coefficient, unit_sale_ht, line_total_ht, configuration_json
)
SELECT
    o.id,
    1,
    3,
    'Flyers Réseau Alpha - 500 ex',
    500,
    0.18,
    1.3000,
    0.23,
    115.00,
    JSON_OBJECT('criteria', JSON_OBJECT('format', 'A5'), 'criteria_labels', JSON_OBJECT('format', JSON_OBJECT('name', 'Format', 'label', 'A5', 'value', 'A5')), 'seed', 'storefront')
FROM orders o
WHERE o.order_number = 'ORD-TEST-2002'
  AND NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id);

UPDATE orders
SET subtotal_ht = (
    SELECT COALESCE(SUM(oi.line_total_ht), 0.00)
    FROM order_items oi
    WHERE oi.order_id = orders.id
)
WHERE order_number = 'ORD-TEST-2002';

UPDATE orders
SET total_ht = ROUND((subtotal_ht - credit_used_ht) + shipping_amount_ht, 2),
    total_ttc = ROUND((subtotal_ht - credit_used_ht) + shipping_amount_ttc, 2)
WHERE order_number = 'ORD-TEST-2002';

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, NULL, 'submitted', 'Commande soumise.', 1, DATE_SUB(NOW(), INTERVAL 11 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-2002'
  AND NOT EXISTS (SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'submitted' AND osh.comment = 'Commande soumise.');

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, 'submitted', 'paid', 'Commande réglée.', 1, DATE_SUB(NOW(), INTERVAL 10 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-2002'
  AND NOT EXISTS (SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'paid' AND osh.comment = 'Commande réglée.');

INSERT INTO order_status_history (order_id, from_status, to_status, comment, created_by_user_id, created_at)
SELECT o.id, 'paid', 'shipped', 'Commande expédiée au client.', 1, DATE_SUB(NOW(), INTERVAL 8 DAY)
FROM orders o
WHERE o.order_number = 'ORD-TEST-2002'
  AND NOT EXISTS (SELECT 1 FROM order_status_history osh WHERE osh.order_id = o.id AND osh.to_status = 'shipped');

INSERT INTO credit_transactions (account_id, order_id, movement_type, settlement_status, amount, reference, reason, source_type, source_id, metadata_json, created_by_user_id)
SELECT
    ca.id,
    o.id,
    'consume',
    'settled',
    -35.00,
    CONCAT('ORDER-', o.order_number),
    'Consommation de crédit de démonstration',
    'order',
    o.id,
    JSON_OBJECT('seed', '006_storefront_test_data', 'lifecycle', 'settled'),
    1
FROM credit_accounts ca
INNER JOIN users u ON u.id = ca.owner_id
INNER JOIN orders o ON o.user_id = u.id AND o.order_number = 'ORD-TEST-2002'
WHERE ca.owner_type = 'user'
  AND u.email = 'agence.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM credit_transactions ct
      WHERE ct.account_id = ca.id AND ct.order_id = o.id AND ct.movement_type = 'consume'
  );

-- Recalcul des soldes de crédits de démonstration
UPDATE credit_accounts ca
SET ca.balance_cached = (
    SELECT COALESCE(SUM(ct.amount), 0.00)
    FROM credit_transactions ct
    WHERE ct.account_id = ca.id
)
WHERE ca.owner_type = 'user'
  AND ca.owner_id IN (
      SELECT u.id FROM users u WHERE u.email IN ('boutique.demo@example.test', 'agence.demo@example.test')
  );

-- États persistés de panier / tunnel pour tests storefront
INSERT INTO storefront_cart_states (shop_id, user_id, payload_json)
SELECT
    1,
    u.id,
    JSON_OBJECT(
        'seed-cart-generic-mug-12',
        JSON_OBJECT(
            'line_id', 'seed-cart-generic-mug-12',
            'shop_product_id', 6,
            'source_product_id', 3,
            'criteria', JSON_OBJECT(),
            'criteria_labels', JSON_OBJECT(),
            'quantity', 12,
            'added_at', DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
        )
    )
FROM users u
WHERE u.email = 'boutique.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM storefront_cart_states scs WHERE scs.user_id = u.id AND scs.shop_id = 1
  );

INSERT INTO storefront_checkout_states (shop_id, user_id, payload_json)
SELECT
    1,
    u.id,
    JSON_OBJECT(
        'contact', JSON_OBJECT('first_name', 'Lucie', 'last_name', 'Martin', 'email', 'boutique.demo@example.test', 'phone', '0601000001', 'company', 'Boutique Démo'),
        'billing_address', JSON_OBJECT('label', 'Facturation', 'company', 'Boutique Démo', 'line1', '16 rue des Carmes', 'line2', 'Bâtiment B', 'postal_code', '75005', 'city', 'Paris', 'country', 'FR', 'phone', '0601000001'),
        'shipping_address', JSON_OBJECT('label', 'Livraison', 'company', 'Boutique Démo', 'line1', '16 rue des Carmes', 'line2', 'Bâtiment B', 'postal_code', '75005', 'city', 'Paris', 'country', 'FR', 'phone', '0601000001'),
        'shipping_method_id', (SELECT sm.id FROM shipping_methods sm WHERE sm.code = 'standard' AND sm.scope_type = 'global' ORDER BY sm.id ASC LIMIT 1),
        'credit_amount', 0.00,
        'customer_note', 'État de tunnel persisté pour démo.',
        'billing_same_as_shipping', TRUE,
        'save_addresses_to_profile', TRUE
    )
FROM users u
WHERE u.email = 'boutique.demo@example.test'
  AND NOT EXISTS (
      SELECT 1 FROM storefront_checkout_states scs WHERE scs.user_id = u.id AND scs.shop_id = 1
  );
