-- Script de vérification du stock intégré
-- Version 1.0 - Vérification des données importées

-- Vérification du nombre total d'articles par catégorie
SELECT 
    c.name as category_name,
    COUNT(DISTINCT p.id) as total_products,
    SUM(s.quantity) as total_stock_quantity
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
LEFT JOIN stock s ON p.id = s.product_id AND s.store_id = (SELECT id FROM users WHERE username = 'shyvalisa')
GROUP BY c.id, c.name
ORDER BY c.name;

-- Vérification des couleurs utilisées
SELECT 
    co.name as color_name,
    co.hex_code,
    COUNT(DISTINCT s.product_id) as products_using_color,
    SUM(s.quantity) as total_quantity
FROM colors co
LEFT JOIN stock s ON co.id = s.color_id AND s.store_id = (SELECT id FROM users WHERE username = 'shyvalisa')
GROUP BY co.id, co.name, co.hex_code
HAVING products_using_color > 0
ORDER BY products_using_color DESC;

-- Vérification des tailles utilisées
SELECT 
    sz.name as size_name,
    COUNT(DISTINCT s.product_id) as products_using_size,
    SUM(s.quantity) as total_quantity
FROM sizes sz
LEFT JOIN stock s ON sz.id = s.size_id AND s.store_id = (SELECT id FROM users WHERE username = 'shyvalisa')
GROUP BY sz.id, sz.name
HAVING products_using_size > 0
ORDER BY sz.sort_order;

-- Vue détaillée du stock Shyva
SELECT 
    p.name as product_name,
    c.name as category_name,
    co.name as color_name,
    sz.name as size_name,
    s.quantity,
    p.price as retail_price
FROM stock s
JOIN products p ON s.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN colors co ON s.color_id = co.id
JOIN sizes sz ON s.size_id = sz.id
WHERE s.store_id = (SELECT id FROM users WHERE username = 'shyvalisa')
ORDER BY c.name, p.name, co.name, sz.name;