-- Script d'insertion pour les 52 produits Shyva (02/07/2025)
-- Mode admin (stock central) - store_id = 1, stock_type = 'admin'

-- 1. Ajouter les nouvelles couleurs nécessaires
INSERT IGNORE INTO colors (name, hex_code) VALUES
('Turquoise', '#40E0D0'),
('Green Fluo', '#39FF14'),
('Corail', '#FF7F50'),
('Brown Chocolate', '#7B3F00'),
('Dark Grey', '#A9A9A9'),
('Gardenia', '#F8F8FF'),
('Flare Beige', '#F5F5DC'),
('Army', '#4B5320'),
('Yellow', '#FFFF00'),
('Red Cherry', '#DE3163'),
('Amy', '#FFB6C1'),
('Black Cream', '#2F2F2F');

-- 2. Ajouter la catégorie Swimwears si elle n'existe pas
INSERT IGNORE INTO categories (name, description) VALUES
('Swimwears', 'Maillots de bain et vêtements de natation');

-- 3. Insérer tous les produits
INSERT INTO products (name, description, category_id, retail_price, created_at, updated_at) VALUES
-- Sets (bras + leggings)
('Pure Motion Fit Turquoise M/L Set', 'Set bra + legging Pure Motion Fit Turquoise M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('Pure Motion Fit Turquoise M/L Legging', 'Legging Pure Motion Fit Turquoise M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('One Shoulder Ribbed Fit Turquoise M/L Bra', 'Bra One Shoulder Ribbed Fit Turquoise M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('One Shoulder Ribbed Fit Turquoise M/L Legging', 'Legging One Shoulder Ribbed Fit Turquoise M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('One Shoulder Ribbed Fit Green Fluo M/L Bra', 'Bra One Shoulder Ribbed Fit Green Fluo M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('One Shoulder Ribbed Fit Green Fluo M/L Legging', 'Legging One Shoulder Ribbed Fit Green Fluo M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('Corail Fit S/M Bra', 'Bra Corail Fit S/M', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('Corail Fit S/M Legging', 'Legging Corail Fit S/M', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('Perfect Fit Brown Chocolate S/M Bra', 'Bra Perfect Fit Brown Chocolate S/M', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('Perfect Fit Brown Chocolate S/M Legging', 'Legging Perfect Fit Brown Chocolate S/M', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('Perfect Fit Dark Grey M/L Bra', 'Bra Perfect Fit Dark Grey M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('Perfect Fit Dark Grey M/L Legging', 'Legging Perfect Fit Dark Grey M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('Perfect Fit Gardenia M/L Bra', 'Bra Perfect Fit Gardenia M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('Perfect Fit Gardenia M/L Legging', 'Legging Perfect Fit Gardenia M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('Fashion Fit Flare Beige M/L Bra', 'Bra Fashion Fit Flare Beige M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), NOW()),
('Fashion Fit Flare Beige M/L Legging', 'Legging Fashion Fit Flare Beige M/L', (SELECT id FROM categories WHERE name = 'Sets'), 525.00, NOW(), NOW()),
('Fashion Fit Flare Black M/L Bra', 'Bra Fashion Fit Flare Black M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), NOW()),
('Fashion Fit Flare Black M/L Legging', 'Legging Fashion Fit Flare Black M/L', (SELECT id FROM categories WHERE name = 'Sets'), 525.00, NOW(), NOW()),
('Fashion Fit Army M/L Bra', 'Bra Fashion Fit Army M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), NOW()),
('Fashion Fit Army M/L Legging', 'Legging Fashion Fit Army M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('Align Fit Yellow S/M Bra', 'Bra Align Fit Yellow S/M', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('Align Fit Yellow XS/S Legging', 'Legging Align Fit Yellow XS/S', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('Align Fit Yellow M/L Short', 'Short Align Fit Yellow M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), NOW()),
('The Ultimate Fit Black M/L Bra', 'Bra The Ultimate Fit Black M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('The Ultimate Fit Black M/L Legging', 'Legging The Ultimate Fit Black M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('Cross Fit Beige M/L Bra', 'Bra Cross Fit Beige M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), NOW()),
('Cross Fit Beige M/L Legging', 'Legging Cross Fit Beige M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),
('Alive Fit Bra White M/L', 'Bra Alive Fit White M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), NOW()),
('Streamline Fit Blue M/L Legging', 'Legging Streamline Fit Blue M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), NOW()),

-- Tops & T-shirts
('Relaxed Fit T-Shirt Black M/L', 'T-Shirt Relaxed Fit Black M/L', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 389.00, NOW(), NOW()),
('Relaxed Fit T-Shirt White M/L', 'T-Shirt Relaxed Fit White M/L', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 389.00, NOW(), NOW()),
('Relaxed Fit T-Shirt Dark Purple M/L', 'T-Shirt Relaxed Fit Dark Purple M/L', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 389.00, NOW(), NOW()),
('Relaxed Fit T-Shirt Dark Purple S/M', 'T-Shirt Relaxed Fit Dark Purple S/M', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 389.00, NOW(), NOW()),
('Jacket Fit Cape Comfort Blue S/M', 'Jacket Fit Cape Comfort Blue S/M', (SELECT id FROM categories WHERE name = 'Jackets'), 499.00, NOW(), NOW()),
('Oversized Twist Shirt White S/M', 'Shirt Oversized Twist White S/M', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 599.00, NOW(), NOW()),
('Must Fit White Tank Top L/XL', 'Tank Top Must Fit White L/XL', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 349.00, NOW(), NOW()),
('Must Fit Black Tank Top M/L', 'Tank Top Must Fit Black M/L', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 349.00, NOW(), NOW()),
('Must Fit Light Blue Tank Top L/XL', 'Tank Top Must Fit Light Blue L/XL', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 349.00, NOW(), NOW()),
('Must Fit Midnight Blue Tank Top S/M', 'Tank Top Must Fit Midnight Blue S/M', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 349.00, NOW(), NOW()),
('Must Fit Pink Tank Top M/L', 'Tank Top Must Fit Pink M/L', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 349.00, NOW(), NOW()),
('Must Fit Army Tank Top XS/S', 'Tank Top Must Fit Army XS/S', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 349.00, NOW(), NOW()),
('Must Fit Light Purple Tank Top L/XL', 'Tank Top Must Fit Light Purple L/XL', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 349.00, NOW(), NOW()),

-- Accessories (Socks)
('Socks White', 'Chaussettes blanches', (SELECT id FROM categories WHERE name = 'Accessories'), 129.00, NOW(), NOW()),
('Socks Black', 'Chaussettes noires', (SELECT id FROM categories WHERE name = 'Accessories'), 129.00, NOW(), NOW()),

-- Swimwears
('Swimwear Army S/M', 'Maillot de bain Army S/M', (SELECT id FROM categories WHERE name = 'Swimwears'), 849.00, NOW(), NOW()),
('Swimwears Black Cream S/M', 'Maillot de bain Black Cream S/M', (SELECT id FROM categories WHERE name = 'Swimwears'), 849.00, NOW(), NOW()),

-- Jumpsuits
('Jumpsuit Cut Out Red Cherry M/L', 'Jumpsuit Cut Out Red Cherry M/L', (SELECT id FROM categories WHERE name = 'Jumpsuits'), 699.00, NOW(), NOW()),
('Faboulous Fit Amy M/L', 'Jumpsuit Faboulous Fit Amy M/L', (SELECT id FROM categories WHERE name = 'Jumpsuits'), 699.00, NOW(), NOW()),
('Faboulous Fit Purple M/L', 'Jumpsuit Faboulous Fit Purple M/L', (SELECT id FROM categories WHERE name = 'Jumpsuits'), 699.00, NOW(), NOW());

-- 4. Initialiser le stock en mode admin (stock central)
-- Récupérer les IDs des produits et couleurs pour le stock
SET @store_id = 1; -- Stock Central FitDose
SET @stock_type = 'admin';

-- Stock pour les Sets (quantité 1 chacun)
INSERT INTO stock (product_id, color_id, size_id, store_id, quantity, stock_type, created_at, updated_at)
SELECT p.id, c.id, s.id, @store_id, 1, @stock_type, NOW(), NOW()
FROM products p
JOIN colors c ON (
    (p.name LIKE '%Turquoise%' AND c.name = 'Turquoise') OR
    (p.name LIKE '%Green Fluo%' AND c.name = 'Green Fluo') OR
    (p.name LIKE '%Corail%' AND c.name = 'Corail') OR
    (p.name LIKE '%Brown Chocolate%' AND c.name = 'Brown Chocolate') OR
    (p.name LIKE '%Dark Grey%' AND c.name = 'Dark Grey') OR
    (p.name LIKE '%Gardenia%' AND c.name = 'Gardenia') OR
    (p.name LIKE '%Flare Beige%' AND c.name = 'Flare Beige') OR
    (p.name LIKE '%Flare Black%' AND c.name = 'Black') OR
    (p.name LIKE '%Army%' AND c.name = 'Army') OR
    (p.name LIKE '%Yellow%' AND c.name = 'Yellow') OR
    (p.name LIKE '%Ultimate%Black%' AND c.name = 'Black') OR
    (p.name LIKE '%Cross Fit Beige%' AND c.name = 'Flare Beige') OR
    (p.name LIKE '%White%' AND c.name = 'White') OR
    (p.name LIKE '%Blue%' AND c.name = 'Blue')
)
JOIN sizes s ON (
    (p.name LIKE '%S/M%' AND s.name = 'S/M') OR
    (p.name LIKE '%M/L%' AND s.name = 'M/L') OR
    (p.name LIKE '%XS/S%' AND s.name = 'XS/S') OR
    (p.name LIKE '%L/XL%' AND s.name = 'L/XL')
)
WHERE p.name LIKE '%Set%' OR p.name LIKE '%Bra%' OR p.name LIKE '%Legging%' OR p.name LIKE '%Short%';

-- Stock pour les Tops & T-shirts (quantité 1, sauf Dark Purple M/L = 2)
INSERT INTO stock (product_id, color_id, size_id, store_id, quantity, stock_type, created_at, updated_at)
SELECT p.id, c.id, s.id, @store_id, 
    CASE WHEN p.name = 'Relaxed Fit T-Shirt Dark Purple M/L' THEN 2 ELSE 1 END,
    @stock_type, NOW(), NOW()
FROM products p
JOIN colors c ON (
    (p.name LIKE '%Black%' AND c.name = 'Black') OR
    (p.name LIKE '%White%' AND c.name = 'White') OR
    (p.name LIKE '%Dark Purple%' AND c.name = 'Purple') OR
    (p.name LIKE '%Blue%' AND c.name = 'Blue') OR
    (p.name LIKE '%Pink%' AND c.name = 'Pink') OR
    (p.name LIKE '%Army%' AND c.name = 'Army') OR
    (p.name LIKE '%Light Purple%' AND c.name = 'Purple') OR
    (p.name LIKE '%Light Blue%' AND c.name = 'Blue') OR
    (p.name LIKE '%Midnight Blue%' AND c.name = 'Blue')
)
JOIN sizes s ON (
    (p.name LIKE '%S/M%' AND s.name = 'S/M') OR
    (p.name LIKE '%M/L%' AND s.name = 'M/L') OR
    (p.name LIKE '%XS/S%' AND s.name = 'XS/S') OR
    (p.name LIKE '%L/XL%' AND s.name = 'L/XL')
)
WHERE p.name LIKE '%T-Shirt%' OR p.name LIKE '%Jacket%' OR p.name LIKE '%Shirt%' OR p.name LIKE '%Tank Top%';

-- Stock pour les Socks (quantité 2 chacun)
INSERT INTO stock (product_id, color_id, size_id, store_id, quantity, stock_type, created_at, updated_at)
SELECT p.id, c.id, s.id, @store_id, 2, @stock_type, NOW(), NOW()
FROM products p
JOIN colors c ON (
    (p.name = 'Socks White' AND c.name = 'White') OR
    (p.name = 'Socks Black' AND c.name = 'Black')
)
JOIN sizes s ON s.name = 'Free Size'
WHERE p.name LIKE 'Socks%';

-- Stock pour les Swimwears (quantité 1 chacun)
INSERT INTO stock (product_id, color_id, size_id, store_id, quantity, stock_type, created_at, updated_at)
SELECT p.id, c.id, s.id, @store_id, 1, @stock_type, NOW(), NOW()
FROM products p
JOIN colors c ON (
    (p.name LIKE '%Army%' AND c.name = 'Army') OR
    (p.name LIKE '%Black Cream%' AND c.name = 'Black Cream')
)
JOIN sizes s ON s.name = 'S/M'
WHERE p.name LIKE 'Swimwear%';

-- Stock pour les Jumpsuits (quantité 1 chacun)
INSERT INTO stock (product_id, color_id, size_id, store_id, quantity, stock_type, created_at, updated_at)
SELECT p.id, c.id, s.id, @store_id, 1, @stock_type, NOW(), NOW()
FROM products p
JOIN colors c ON (
    (p.name LIKE '%Red Cherry%' AND c.name = 'Red Cherry') OR
    (p.name LIKE '%Amy%' AND c.name = 'Amy') OR
    (p.name LIKE '%Purple%' AND c.name = 'Purple')
)
JOIN sizes s ON s.name = 'M/L'
WHERE p.name LIKE '%Jumpsuit%' OR p.name LIKE 'Faboulous%';

-- Vérification du stock inséré
SELECT 
    'Vérification Stock Batch 2' as verification,
    COUNT(*) as total_articles_stock,
    SUM(quantity) as total_quantite
FROM stock 
WHERE store_id = 1 AND stock_type = 'admin'
AND created_at >= CURDATE();