-- Script d'insertion corrigé pour les 52 produits - Batch 2
-- Date: 02/07/2025 - Stock Central Admin

-- 1. Ajouter les nouvelles couleurs
INSERT 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'),
('Cape Comfort Blue', '#4682B4'),
('Twist White', '#FFFFFF'),
('Light Blue', '#ADD8E6'),
('Midnight Blue', '#191970'),
('Pink', '#FFC0CB'),
('Light Purple', '#DDA0DD'),
('Red Cherry', '#DE3163'),
('Amy', '#FFB6C1'),
('Purple', '#800080'),
('Black Cream', '#2F2F2F')
ON DUPLICATE KEY UPDATE hex_code = VALUES(hex_code);

-- 2. Ajouter les nouvelles catégories si nécessaire
INSERT INTO categories (name, description) VALUES
('Sets', 'Ensembles bra + legging'),
('Tops & T-shirts', 'T-shirts, débardeurs et hauts'),
('Jackets', 'Vestes et manteaux'),
('Accessories', 'Accessoires divers'),
('Swimwears', 'Maillots de bain'),
('Jumpsuits', 'Combinaisons')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- 3. Ajouter les nouvelles tailles
INSERT INTO sizes (name, sort_order) VALUES
('S/M', 1),
('M/L', 2),
('L/XL', 3),
('XS/S', 0),
('Free Size', 4)
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order);

-- 4. Insérer tous les produits avec le bon nom de colonne 'price'
INSERT INTO products (name, description, category_id, price, created_at, is_active) 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(), 1),
('Pure Motion Fit Turquoise M/L Legging', 'Legging Pure Motion Fit Turquoise M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),
('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(), 1),
('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(), 1),
('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(), 1),
('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(), 1),
('Corail Fit S/M Bra', 'Bra Corail Fit S/M', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), 1),
('Corail Fit S/M Legging', 'Legging Corail Fit S/M', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),
('Perfect Fit Brown Chocolate S/M Bra', 'Bra Perfect Fit Brown Chocolate S/M', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), 1),
('Perfect Fit Brown Chocolate S/M Legging', 'Legging Perfect Fit Brown Chocolate S/M', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),
('Perfect Fit Dark Grey M/L Bra', 'Bra Perfect Fit Dark Grey M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), 1),
('Perfect Fit Dark Grey M/L Legging', 'Legging Perfect Fit Dark Grey M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),
('Perfect Fit Gardenia M/L Bra', 'Bra Perfect Fit Gardenia M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), 1),
('Perfect Fit Gardenia M/L Legging', 'Legging Perfect Fit Gardenia M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),
('Fashion Fit Flare Beige M/L Bra', 'Bra Fashion Fit Flare Beige M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), 1),
('Fashion Fit Flare Beige M/L Legging', 'Legging Fashion Fit Flare Beige M/L', (SELECT id FROM categories WHERE name = 'Sets'), 525.00, NOW(), 1),
('Fashion Fit Flare Black M/L Bra', 'Bra Fashion Fit Flare Black M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), 1),
('Fashion Fit Flare Black M/L Legging', 'Legging Fashion Fit Flare Black M/L', (SELECT id FROM categories WHERE name = 'Sets'), 525.00, NOW(), 1),
('Fashion Fit Army M/L Bra', 'Bra Fashion Fit Army M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), 1),
('Fashion Fit Army M/L Legging', 'Legging Fashion Fit Army M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),
('Align Fit Yellow S/M Bra', 'Bra Align Fit Yellow S/M', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), 1),
('Align Fit Yellow XS/S Legging', 'Legging Align Fit Yellow XS/S', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),
('Align Fit Yellow M/L Short', 'Short Align Fit Yellow M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), 1),
('The Ultimate Fit Black M/L Bra', 'Bra The Ultimate Fit Black M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), 1),
('The Ultimate Fit Black M/L Legging', 'Legging The Ultimate Fit Black M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),
('Cross Fit Beige M/L Bra', 'Bra Cross Fit Beige M/L', (SELECT id FROM categories WHERE name = 'Sets'), 399.00, NOW(), 1),
('Cross Fit Beige M/L Legging', 'Legging Cross Fit Beige M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),
('Alive Fit Bra White M/L', 'Bra Alive Fit White M/L', (SELECT id FROM categories WHERE name = 'Sets'), 389.00, NOW(), 1),
('Streamline Fit Blue M/L Legging', 'Legging Streamline Fit Blue M/L', (SELECT id FROM categories WHERE name = 'Sets'), 499.00, NOW(), 1),

-- 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(), 1),
('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(), 1),
('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(), 1),
('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(), 1),
('Jacket Fit Cape Comfort Blue S/M', 'Jacket Fit Cape Comfort Blue S/M', (SELECT id FROM categories WHERE name = 'Jackets'), 499.00, NOW(), 1),
('Oversized Twist Shirt White S/M', 'Shirt Oversized Twist White S/M', (SELECT id FROM categories WHERE name = 'Tops & T-shirts'), 599.00, NOW(), 1),
('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(), 1),
('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(), 1),
('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(), 1),
('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(), 1),
('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(), 1),
('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(), 1),
('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(), 1),

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

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

-- 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(), 1),
('Faboulous Fit Amy M/L', 'Jumpsuit Faboulous Fit Amy M/L', (SELECT id FROM categories WHERE name = 'Jumpsuits'), 699.00, NOW(), 1),
('Faboulous Fit Purple M/L', 'Jumpsuit Faboulous Fit Purple M/L', (SELECT id FROM categories WHERE name = 'Jumpsuits'), 699.00, NOW(), 1)

ON DUPLICATE KEY UPDATE 
    description = VALUES(description),
    price = VALUES(price);

-- 5. Initialiser le stock en mode admin (stock central)
-- Note: Ce script nécessite que les IDs des produits, couleurs et tailles soient récupérés dynamiquement
-- Pour simplifier, voici la structure de base. Les IDs exacts devront être ajustés selon votre base.

INSERT INTO stock (product_id, color_id, size_id, store_id, quantity, stock_type, created_at, updated_at)
SELECT 
    p.id as product_id,
    c.id as color_id, 
    s.id as size_id,
    1 as store_id, -- Stock Central FitDose
    CASE 
        WHEN p.name LIKE '%Dark Purple M/L%' THEN 2
        WHEN p.name LIKE 'Socks%' THEN 2
        ELSE 1
    END as quantity,
    'admin' as stock_type,
    NOW() as created_at,
    NOW() as updated_at
FROM products p
CROSS JOIN colors c
CROSS JOIN sizes s
WHERE p.name IN (
    'Pure Motion Fit Turquoise M/L Set', 'Pure Motion Fit Turquoise M/L Legging',
    'One Shoulder Ribbed Fit Turquoise M/L Bra', 'One Shoulder Ribbed Fit Turquoise M/L Legging',
    'One Shoulder Ribbed Fit Green Fluo M/L Bra', 'One Shoulder Ribbed Fit Green Fluo M/L Legging',
    'Corail Fit S/M Bra', 'Corail Fit S/M Legging',
    'Perfect Fit Brown Chocolate S/M Bra', 'Perfect Fit Brown Chocolate S/M Legging',
    'Perfect Fit Dark Grey M/L Bra', 'Perfect Fit Dark Grey M/L Legging',
    'Perfect Fit Gardenia M/L Bra', 'Perfect Fit Gardenia M/L Legging',
    'Fashion Fit Flare Beige M/L Bra', 'Fashion Fit Flare Beige M/L Legging',
    'Fashion Fit Flare Black M/L Bra', 'Fashion Fit Flare Black M/L Legging',
    'Fashion Fit Army M/L Bra', 'Fashion Fit Army M/L Legging',
    'Align Fit Yellow S/M Bra', 'Align Fit Yellow XS/S Legging', 'Align Fit Yellow M/L Short',
    'The Ultimate Fit Black M/L Bra', 'The Ultimate Fit Black M/L Legging',
    'Cross Fit Beige M/L Bra', 'Cross Fit Beige M/L Legging',
    'Alive Fit Bra White M/L', 'Streamline Fit Blue M/L Legging',
    'Relaxed Fit T-Shirt Black M/L', 'Relaxed Fit T-Shirt White M/L',
    'Relaxed Fit T-Shirt Dark Purple M/L', 'Relaxed Fit T-Shirt Dark Purple S/M',
    'Jacket Fit Cape Comfort Blue S/M', 'Oversized Twist Shirt White S/M',
    'Must Fit White Tank Top L/XL', 'Must Fit Black Tank Top M/L',
    'Must Fit Light Blue Tank Top L/XL', 'Must Fit Midnight Blue Tank Top S/M',
    'Must Fit Pink Tank Top M/L', 'Must Fit Army Tank Top XS/S',
    'Must Fit Light Purple Tank Top L/XL',
    'Socks White', 'Socks Black',
    'Swimwear Army S/M', 'Swimwears Black Cream S/M',
    'Jumpsuit Cut Out Red Cherry M/L', 'Faboulous Fit Amy M/L', 'Faboulous Fit Purple M/L'
)
AND (
    (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 '%Beige%' AND c.name = 'Flare Beige') OR
    (p.name LIKE '%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 '%White%' AND c.name = 'White') OR
    (p.name LIKE '%Blue%' AND c.name = 'Light Blue') OR
    (p.name LIKE '%Cape Comfort Blue%' AND c.name = 'Cape Comfort Blue') OR
    (p.name LIKE '%Midnight Blue%' AND c.name = 'Midnight Blue') OR
    (p.name LIKE '%Dark Purple%' AND c.name = 'Purple') OR
    (p.name LIKE '%Pink%' AND c.name = 'Pink') OR
    (p.name LIKE '%Light Purple%' AND c.name = 'Light Purple') OR
    (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') OR
    (p.name LIKE '%Black Cream%' AND c.name = 'Black Cream')
)
AND (
    (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 '%L/XL%' AND s.name = 'L/XL') OR
    (p.name LIKE '%XS/S%' AND s.name = 'XS/S') OR
    (p.name LIKE 'Socks%' AND s.name = 'Free Size')
)
ON DUPLICATE KEY UPDATE 
    quantity = VALUES(quantity),
    updated_at = NOW();

-- 6. Vérification du stock ajouté
SELECT 
    COUNT(*) as total_products_added,
    SUM(CASE WHEN quantity = 2 THEN 2 ELSE 1 END) as total_items_count
FROM stock s
JOIN products p ON s.product_id = p.id
WHERE s.store_id = 1 
AND s.stock_type = 'admin'
AND p.created_at >= CURDATE();
