-- Script pour initialiser le stock pour le magasin Shyva
-- Version 1.0 - Stock initial basé sur l'inventaire du 04/03/2025

-- Récupération de l'ID du magasin Shyva
SET @store_shyva = (SELECT id FROM users WHERE username = 'shyvalisa');

-- Fonction helper pour obtenir les IDs
-- JUMPSUITS STOCK
INSERT INTO stock (product_id, color_id, size_id, store_id, quantity, stock_type) 
SELECT 
    p.id as product_id,
    c.id as color_id, 
    s.id as size_id,
    @store_shyva as store_id,
    1 as quantity,
    'store' as stock_type
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Classic Fit Grey' AND c.name = 'Grey' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Cut out-Blue Electric' AND c.name = 'Blue Electric' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Classic Fit Black' AND c.name = 'Black' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Classic Fit Hazel' AND c.name = 'Hazel' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Cut Out-Re Cherry' AND c.name = 'Cherry' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Cut Out-Re Cherry' AND c.name = 'Cherry' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Open Back Beige' AND c.name = 'Beige' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Open Back Brown' AND c.name = 'Brown' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Flare Blue Electric' AND c.name = 'Blue Electric' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Glam Fit Black (dress+Short)' AND c.name = 'Black' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jumpsuit Flare Black' AND c.name = 'Black' AND s.name = 'S/M'

-- TOPS & T-SHIRTS STOCK
UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit T-Shirt Black' AND c.name = 'Black' AND s.name = 'XS'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Asymmetrical Fit Light Blue' AND c.name = 'Light Blue' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Asymmetrical Fit White' AND c.name = 'White' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Fashion Fit Blue' AND c.name = 'Blue Electric' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Midnight Blue' AND c.name = 'Midnight Blue' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Cream' AND c.name = 'Cream' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Flex Fit Brown' AND c.name = 'Brown' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Flex Fit White' AND c.name = 'White' AND s.name = 'XS'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Knot Brown' AND c.name = 'Brown' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Unique Fit Pink' AND c.name = 'Pink' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Unique Fit Purple' AND c.name = 'Purple' AND s.name = 'L/XL'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Naya Fit Grey' AND c.name = 'Grey' AND s.name = 'Free Size'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Knot Pink' AND c.name = 'Pink' AND s.name = 'XS'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Unique Fit Black' AND c.name = 'Black' AND s.name = 'XS'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Unique Fit Light Blue' AND c.name = 'Light Blue' AND s.name = 'M/L'

-- SETS STOCK (Bras)
UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Rose Pink Bra' AND c.name = 'Rose Pink' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Crystal Cross Fit Amethyst Bra' AND c.name = 'Amethyst' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Light Pink Bra' AND c.name = 'Light Pink' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Must Fit Brown Bra' AND c.name = 'Brown' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Brown Bra' AND c.name = 'Brown' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Corail Fit Bra' AND c.name = 'Corail' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'The Ultimate Fit Black Bra' AND c.name = 'Black' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit White Bra' AND c.name = 'White' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Glam Fit Black One Shoulder Bra' AND c.name = 'Black' AND s.name = 'S/M'

-- SETS STOCK (Leggings & Jupes)
UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Rose Pink Legging' AND c.name = 'Rose Pink' AND s.name = 'XS'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Crystal Cross Fit Amethyst Legging' AND c.name = 'Amethyst' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Light Pink Legging' AND c.name = 'Light Pink' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Brown Legging' AND c.name = 'Brown' AND s.name = 'S/M'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Brown Legging' AND c.name = 'Brown' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Corail Fit Legging' AND c.name = 'Corail' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'The Ultimate Fit Black Legging' AND c.name = 'Black' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Alive Fit Fushia Legging' AND c.name = 'Fushia' AND s.name = 'M/L'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Glam Fit Black Skirt' AND c.name = 'Black' AND s.name = 'S/M'

-- JACKETS STOCK
UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jacket Fit Fushia' AND c.name = 'Fushia' AND s.name = 'Free Size'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jacket Fit Light Blue' AND c.name = 'Light Blue' AND s.name = 'Free Size'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 1, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Jacket Fit White' AND c.name = 'White' AND s.name = 'XS'

-- ACCESSORIES STOCK
UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 2, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Socks Black' AND c.name = 'Black' AND s.name = 'Free Size'

UNION ALL SELECT p.id, c.id, s.id, @store_shyva, 2, 'store'
FROM products p, colors c, sizes s
WHERE p.name = 'Socks White' AND c.name = 'White' AND s.name = 'Free Size'

ON DUPLICATE KEY UPDATE 
quantity = quantity + VALUES(quantity);