SQL & MySQL

-- EXEMPLE COMPLET : E-COMMERCE
-- Requête complexe avec plusieurs jointures

-- Structure complète
SELECT 
    -- Informations utilisateur
    u.nom AS client_nom,
    u.email AS client_email,
    
    -- Informations commande
    c.id AS commande_numero,
    c.date_commande,
    c.statut,
    
    -- Détails produits
    p.nom AS produit_nom,
    p.prix AS prix_unitaire,
    cd.quantite,
    (p.prix * cd.quantite) AS sous_total,
    
    -- Informations catégorie
    cat.nom AS categorie,
    
    -- Adresse livraison
    a.rue,
    a.ville,
    a.code_postal

FROM commandes c

-- Jointure utilisateur (obligatoire)
INNER JOIN users u ON c.user_id = u.id

-- Jointure détails commande
INNER JOIN commande_details cd ON c.id = cd.commande_id

-- Jointure produits
INNER JOIN produits p ON cd.produit_id = p.id

-- Jointure catégories (peut être NULL)
LEFT JOIN categories cat ON p.categorie_id = cat.id

-- Jointure adresse livraison
LEFT JOIN adresses a ON c.adresse_livraison_id = a.id

-- Filtres
WHERE c.date_commande >= '2024-01-01'
  AND c.statut IN ('confirmee', 'expediee', 'livree')
  AND u.actif = TRUE

-- Groupement et tri
ORDER BY c.date_commande DESC, c.id, cd.id;

-- REQUÊTES D'ANALYSE AVANCÉES

-- 1. Chiffre d'affaires par mois et catégorie
SELECT 
    DATE_FORMAT(c.date_commande, '%Y-%m') AS mois,
    cat.nom AS categorie,
    COUNT(DISTINCT c.id) AS nb_commandes,
    SUM(cd.quantite * p.prix) AS chiffre_affaires
FROM commandes c
INNER JOIN commande_details cd ON c.id = cd.commande_id
INNER JOIN produits p ON cd.produit_id = p.id
LEFT JOIN categories cat ON p.categorie_id = cat.id
WHERE c.statut = 'livree'
GROUP BY DATE_FORMAT(c.date_commande, '%Y-%m'), cat.id
ORDER BY mois DESC, chiffre_affaires DESC;

-- 2. Top 10 clients par valeur
SELECT 
    u.nom,
    u.email,
    COUNT(c.id) AS nb_commandes,
    SUM(
        SELECT SUM(cd.quantite * p.prix) 
        FROM commande_details cd 
        INNER JOIN produits p ON cd.produit_id = p.id 
        WHERE cd.commande_id = c.id
    ) AS montant_total
FROM users u
INNER JOIN commandes c ON u.id = c.user_id
WHERE c.statut = 'livree'
GROUP BY u.id
ORDER BY montant_total DESC
LIMIT 10;

-- 3. Produits jamais vendus
SELECT p.nom, p.prix, p.stock
FROM produits p
LEFT JOIN commande_details cd ON p.id = cd.produit_id
WHERE cd.produit_id IS NULL
ORDER BY p.date_creation DESC;

-- 4. Analyse des ventes par région
SELECT 
    a.ville,
    a.code_postal,
    COUNT(DISTINCT c.id) AS nb_commandes,
    COUNT(DISTINCT c.user_id) AS nb_clients_uniques,
    AVG(total_commande.montant) AS panier_moyen
FROM commandes c
INNER JOIN adresses a ON c.adresse_livraison_id = a.id
INNER JOIN (
    SELECT 
        c.id,
        SUM(cd.quantite * p.prix) AS montant
    FROM commandes c
    INNER JOIN commande_details cd ON c.id = cd.commande_id
    INNER JOIN produits p ON cd.produit_id = p.id
    GROUP BY c.id
) total_commande ON c.id = total_commande.id
WHERE c.statut = 'livree'
GROUP BY a.ville, a.code_postal
HAVING nb_commandes >= 5
ORDER BY nb_commandes DESC;

-- OPTIMISATIONS IMPORTANTES

-- 1. Index sur les colonnes de jointure
CREATE INDEX idx_commandes_user_id ON commandes(user_id);
CREATE INDEX idx_commandes_date ON commandes(date_commande);
CREATE INDEX idx_commande_details_commande_id ON commande_details(commande_id);
CREATE INDEX idx_commande_details_produit_id ON commande_details(produit_id);
CREATE INDEX idx_produits_categorie_id ON produits(categorie_id);

-- 2. Index composé pour requêtes fréquentes
CREATE INDEX idx_commandes_statut_date ON commandes(statut, date_commande);
CREATE INDEX idx_users_actif_email ON users(actif, email);

-- 3. Optimisation des requêtes avec EXPLAIN
EXPLAIN SELECT 
    u.nom, 
    COUNT(c.id) as nb_commandes
FROM users u 
LEFT JOIN commandes c ON u.id = c.user_id 
GROUP BY u.id;

-- JOINTURES AVEC CONDITIONS COMPLEXES

-- 1. Jointure avec conditions dans ON vs WHERE
SELECT u.nom, c.id
FROM users u
LEFT JOIN commandes c ON u.id = c.user_id 
    AND c.date_commande >= '2024-01-01'  -- Condition dans ON
WHERE u.actif = TRUE;                    -- Condition dans WHERE

-- 2. Self-join pour hiérarchies
SELECT 
    c1.nom AS categorie,
    c2.nom AS sous_categorie
FROM categories c1
INNER JOIN categories c2 ON c1.id = c2.parent_id;

-- 3. Jointure avec sous-requête
SELECT 
    u.nom,
    commandes_recentes.nb_commandes
FROM users u
INNER JOIN (
    SELECT 
        user_id,
        COUNT(*) as nb_commandes
    FROM commandes 
    WHERE date_commande >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY user_id
) commandes_recentes ON u.id = commandes_recentes.user_id;

-- UNION et UNION ALL

-- 1. Combiner différentes sources
SELECT nom, 'client' as type FROM users
UNION ALL
SELECT nom, 'produit' as type FROM produits;

-- 2. Statistiques combinées
SELECT 
    'Commandes aujourd\'hui' as metric,
    COUNT(*) as value
FROM commandes 
WHERE DATE(date_commande) = CURDATE()

UNION ALL

SELECT 
    'Nouveaux clients cette semaine',
    COUNT(*)
FROM users 
WHERE date_creation >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- =====================================================
-- PROJET E-COMMERCE - BASE DE DONNÉES COMPLÈTE
-- DevWeb Academy - Guide SQL MySQL
-- =====================================================

-- 1. CRÉATION DE LA BASE ET CONFIGURATION
-- =====================================================

DROP DATABASE IF EXISTS ecommerce;
CREATE DATABASE ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ecommerce;

SET foreign_key_checks = 1;
SET sql_mode = 'STRICT_TRANS_TABLES';

-- 2. TABLE DES CATÉGORIES
-- =====================================================

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    description TEXT,
    slug VARCHAR(100) UNIQUE NOT NULL,
    parent_id INT NULL,
    actif BOOLEAN DEFAULT TRUE,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_categories_slug (slug),
    INDEX idx_categories_parent (parent_id)
);

-- 3. TABLE DES UTILISATEURS
-- =====================================================

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    mot_de_passe VARCHAR(255) NOT NULL, -- Hash bcrypt
    telephone VARCHAR(20),
    date_naissance DATE,
    genre ENUM('M', 'F', 'Autre') NULL,
    
    -- Statuts et permissions
    actif BOOLEAN DEFAULT TRUE,
    email_verifie BOOLEAN DEFAULT FALSE,
    role ENUM('client', 'admin', 'manager') DEFAULT 'client',
    
    -- Dates de suivi
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    derniere_connexion DATETIME NULL,
    
    -- Index pour optimisation
    INDEX idx_users_email (email),
    INDEX idx_users_actif (actif),
    INDEX idx_users_role (role)
);

-- 4. TABLE DES ADRESSES
-- =====================================================

CREATE TABLE adresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('livraison', 'facturation') DEFAULT 'livraison',
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    entreprise VARCHAR(100) NULL,
    rue VARCHAR(255) NOT NULL,
    ville VARCHAR(100) NOT NULL,
    code_postal VARCHAR(10) NOT NULL,
    pays VARCHAR(50) DEFAULT 'France',
    telephone VARCHAR(20),
    
    -- Adresse par défaut
    defaut BOOLEAN DEFAULT FALSE,
    
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_adresses_user_id (user_id),
    INDEX idx_adresses_type (type)
);

-- 5. TABLE DES PRODUITS
-- =====================================================

CREATE TABLE produits (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    description_courte TEXT,
    description_longue LONGTEXT,
    
    -- Prix et stock
    prix DECIMAL(10,2) NOT NULL,
    prix_promo DECIMAL(10,2) NULL,
    stock INT DEFAULT 0,
    stock_min INT DEFAULT 5,
    
    -- Classification
    categorie_id INT NOT NULL,
    marque VARCHAR(100),
    reference VARCHAR(50) UNIQUE,
    code_barre VARCHAR(20) UNIQUE,
    
    -- Caractéristiques physiques
    poids DECIMAL(8,2), -- en grammes
    dimensions VARCHAR(50), -- L×l×h en cm
    couleur VARCHAR(50),
    
    -- SEO et images
    meta_title VARCHAR(160),
    meta_description VARCHAR(320),
    image_principale VARCHAR(255),
    
    -- Statuts
    actif BOOLEAN DEFAULT TRUE,
    en_vedette BOOLEAN DEFAULT FALSE,
    nouveau BOOLEAN DEFAULT TRUE,
    
    -- Dates
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (categorie_id) REFERENCES categories(id) ON DELETE RESTRICT,
    
    -- Index pour recherche et performance
    INDEX idx_produits_categorie (categorie_id),
    INDEX idx_produits_prix (prix),
    INDEX idx_produits_stock (stock),
    INDEX idx_produits_actif (actif),
    INDEX idx_produits_slug (slug),
    INDEX idx_produits_reference (reference),
    
    -- Index composés pour requêtes fréquentes
    INDEX idx_produits_actif_categorie (actif, categorie_id),
    INDEX idx_produits_actif_prix (actif, prix)
);

-- 6. TABLE DES IMAGES PRODUITS
-- =====================================================

CREATE TABLE produit_images (
    id INT PRIMARY KEY AUTO_INCREMENT,
    produit_id INT NOT NULL,
    nom_fichier VARCHAR(255) NOT NULL,
    alt_text VARCHAR(255),
    ordre INT DEFAULT 0,
    actif BOOLEAN DEFAULT TRUE,
    
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (produit_id) REFERENCES produits(id) ON DELETE CASCADE,
    INDEX idx_produit_images_produit (produit_id),
    INDEX idx_produit_images_ordre (produit_id, ordre)
);

-- 7. TABLE DES COMMANDES
-- =====================================================

CREATE TABLE commandes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    numero_commande VARCHAR(20) UNIQUE NOT NULL,
    user_id INT NOT NULL,
    
    -- Statut de la commande
    statut ENUM('panier', 'confirmee', 'payee', 'preparee', 'expediee', 'livree', 'annulee') 
           DEFAULT 'panier',
    
    -- Adresses (copies pour historique)
    adresse_livraison_id INT,
    adresse_facturation_id INT,
    
    -- Montants
    sous_total DECIMAL(10,2) NOT NULL DEFAULT 0,
    frais_livraison DECIMAL(10,2) DEFAULT 0,
    taxe DECIMAL(10,2) DEFAULT 0,
    remise DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) NOT NULL DEFAULT 0,
    
    -- Informations de livraison
    transporteur VARCHAR(100),
    numero_suivi VARCHAR(100),
    date_expedition DATETIME NULL,
    date_livraison_prevue DATE NULL,
    date_livraison_reelle DATETIME NULL,
    
    -- Paiement
    methode_paiement ENUM('carte', 'paypal', 'virement', 'cheque') NULL,
    transaction_id VARCHAR(100),
    date_paiement DATETIME NULL,
    
    -- Commentaires
    commentaire_client TEXT,
    commentaire_admin TEXT,
    
    -- Dates de suivi
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (adresse_livraison_id) REFERENCES adresses(id) ON DELETE SET NULL,
    FOREIGN KEY (adresse_facturation_id) REFERENCES adresses(id) ON DELETE SET NULL,
    
    INDEX idx_commandes_user_id (user_id),
    INDEX idx_commandes_statut (statut),
    INDEX idx_commandes_numero (numero_commande),
    INDEX idx_commandes_date_creation (date_creation),
    
    -- Index composés pour rapports
    INDEX idx_commandes_statut_date (statut, date_creation),
    INDEX idx_commandes_user_statut (user_id, statut)
);

-- 8. TABLE DES DÉTAILS DE COMMANDES
-- =====================================================

CREATE TABLE commande_details (
    id INT PRIMARY KEY AUTO_INCREMENT,
    commande_id INT NOT NULL,
    produit_id INT NOT NULL,
    
    -- Informations du produit au moment de la commande
    nom_produit VARCHAR(200) NOT NULL,
    prix_unitaire DECIMAL(10,2) NOT NULL,
    quantite INT NOT NULL DEFAULT 1,
    
    -- Sous-total de la ligne
    sous_total DECIMAL(10,2) GENERATED ALWAYS AS (prix_unitaire * quantite) STORED,
    
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (commande_id) REFERENCES commandes(id) ON DELETE CASCADE,
    FOREIGN KEY (produit_id) REFERENCES produits(id) ON DELETE RESTRICT,
    
    INDEX idx_commande_details_commande (commande_id),
    INDEX idx_commande_details_produit (produit_id),
    
    -- Éviter les doublons
    UNIQUE KEY unique_commande_produit (commande_id, produit_id)
);

-- 9. TABLE DES AVIS CLIENTS
-- =====================================================

CREATE TABLE avis (
    id INT PRIMARY KEY AUTO_INCREMENT,
    produit_id INT NOT NULL,
    user_id INT NOT NULL,
    commande_id INT,
    
    note INT CHECK (note BETWEEN 1 AND 5),
    titre VARCHAR(200),
    commentaire TEXT,
    
    -- Modération
    approuve BOOLEAN DEFAULT FALSE,
    signale BOOLEAN DEFAULT FALSE,
    
    -- Utilité
    votes_utiles INT DEFAULT 0,
    votes_inutiles INT DEFAULT 0,
    
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (produit_id) REFERENCES produits(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (commande_id) REFERENCES commandes(id) ON DELETE SET NULL,
    
    INDEX idx_avis_produit (produit_id),
    INDEX idx_avis_user (user_id),
    INDEX idx_avis_note (note),
    INDEX idx_avis_approuve (approuve),
    
    -- Un seul avis par user/produit
    UNIQUE KEY unique_user_produit (user_id, produit_id)
);

-- 10. TABLE DES COUPONS DE RÉDUCTION
-- =====================================================

CREATE TABLE coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(255),
    
    -- Type de réduction
    type ENUM('fixe', 'pourcentage') NOT NULL,
    valeur DECIMAL(10,2) NOT NULL,
    
    -- Conditions d'utilisation
    montant_minimum DECIMAL(10,2) DEFAULT 0,
    usage_max INT NULL, -- NULL = illimité
    usage_par_user INT DEFAULT 1,
    
    -- Validité
    date_debut DATE NOT NULL,
    date_fin DATE NOT NULL,
    actif BOOLEAN DEFAULT TRUE,
    
    -- Statistiques
    nb_utilisations INT DEFAULT 0,
    
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_coupons_code (code),
    INDEX idx_coupons_dates (date_debut, date_fin),
    INDEX idx_coupons_actif (actif)
);

-- 11. TABLE DES UTILISATIONS DE COUPONS
-- =====================================================

CREATE TABLE coupon_utilisations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    coupon_id INT NOT NULL,
    user_id INT NOT NULL,
    commande_id INT NOT NULL,
    montant_reduction DECIMAL(10,2) NOT NULL,
    
    date_utilisation DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (commande_id) REFERENCES commandes(id) ON DELETE CASCADE,
    
    INDEX idx_coupon_utilisations_coupon (coupon_id),
    INDEX idx_coupon_utilisations_user (user_id)
);

-- =====================================================
-- 12. DONNÉES DE TEST
-- =====================================================

-- Insertion des catégories
INSERT INTO categories (nom, description, slug) VALUES
('Électronique', 'Produits électroniques et high-tech', 'electronique'),
('Vêtements', 'Mode et vêtements', 'vetements'),
('Maison & Jardin', 'Articles pour la maison et le jardin', 'maison-jardin'),
('Sports & Loisirs', 'Équipements sportifs et loisirs', 'sports-loisirs'),
('Livres', 'Livres et publications', 'livres');

-- Insertion des utilisateurs de test
INSERT INTO users (nom, prenom, email, mot_de_passe, role) VALUES
('Admin', 'System', 'admin@ecommerce.com', '$2y$10$example_hash', 'admin'),
('Dupont', 'Jean', 'jean.dupont@email.com', '$2y$10$example_hash', 'client'),
('Martin', 'Marie', 'marie.martin@email.com', '$2y$10$example_hash', 'client'),
('Bernard', 'Pierre', 'pierre.bernard@email.com', '$2y$10$example_hash', 'client');

-- Insertion des adresses
INSERT INTO adresses (user_id, nom, prenom, rue, ville, code_postal, defaut) VALUES
(2, 'Dupont', 'Jean', '123 Rue de la Paix', 'Paris', '75001', TRUE),
(3, 'Martin', 'Marie', '456 Avenue des Champs', 'Lyon', '69001', TRUE),
(4, 'Bernard', 'Pierre', '789 Boulevard Victor Hugo', 'Marseille', '13001', TRUE);

-- Insertion des produits
INSERT INTO produits (nom, slug, description_courte, prix, stock, categorie_id, reference) VALUES
('iPhone 14 Pro', 'iphone-14-pro', 'Smartphone Apple dernière génération', 1099.00, 25, 1, 'APPLE-IP14P'),
('MacBook Air M2', 'macbook-air-m2', 'Ordinateur portable ultra-fin Apple', 1499.00, 15, 1, 'APPLE-MBA-M2'),
('Jean Slim Bleu', 'jean-slim-bleu', 'Jean coupe slim en denim bleu', 79.90, 50, 2, 'JEAN-SLIM-001'),
('Robe d\'été Florale', 'robe-ete-florale', 'Robe légère à motifs floraux', 59.90, 30, 2, 'ROBE-ETE-001'),
('Aspirateur Robot', 'aspirateur-robot', 'Robot aspirateur intelligent', 299.00, 20, 3, 'ROBOT-ASP-001');

-- Insertion des commandes de test
INSERT INTO commandes (numero_commande, user_id, statut, sous_total, frais_livraison, total, adresse_livraison_id) VALUES
('CMD-2024-001', 2, 'livree', 1178.90, 9.90, 1188.80, 1),
('CMD-2024-002', 3, 'expediee', 359.80, 7.90, 367.70, 2),
('CMD-2024-003', 4, 'confirmee', 79.90, 5.90, 85.80, 3);

-- Insertion des détails de commandes
INSERT INTO commande_details (commande_id, produit_id, nom_produit, prix_unitaire, quantite) VALUES
(1, 1, 'iPhone 14 Pro', 1099.00, 1),
(1, 3, 'Jean Slim Bleu', 79.90, 1),
(2, 5, 'Aspirateur Robot', 299.00, 1),
(2, 4, 'Robe d\'été Florale', 59.90, 1),
(3, 3, 'Jean Slim Bleu', 79.90, 1);

-- Insertion des avis
INSERT INTO avis (produit_id, user_id, note, titre, commentaire, approuve) VALUES
(1, 2, 5, 'Excellent produit !', 'iPhone de très bonne qualité, je recommande vivement.', TRUE),
(3, 3, 4, 'Bon rapport qualité-prix', 'Jean confortable et bien taillé.', TRUE),
(5, 4, 5, 'Révolutionnaire !', 'Cet aspirateur robot change la vie, très efficace.', TRUE);

-- Insertion des coupons
INSERT INTO coupons (code, description, type, valeur, montant_minimum, date_debut, date_fin) VALUES
('WELCOME10', 'Réduction de 10% pour les nouveaux clients', 'pourcentage', 10.00, 50.00, '2024-01-01', '2024-12-31'),
('NOEL2024', 'Réduction de 20€ pour Noël', 'fixe', 20.00, 100.00, '2024-12-01', '2024-12-31'),
('BLACKFRIDAY', 'Black Friday - 25% de réduction', 'pourcentage', 25.00, 0.00, '2024-11-29', '2024-11-29');

-- =====================================================
-- 13. REQUÊTES D'ANALYSE ET RAPPORTS
-- =====================================================

-- Chiffre d'affaires par mois
SELECT 
    DATE_FORMAT(date_creation, '%Y-%m') AS mois,
    COUNT(*) AS nb_commandes,
    SUM(total) AS chiffre_affaires
FROM commandes 
WHERE statut IN ('payee', 'expediee', 'livree')
GROUP BY DATE_FORMAT(date_creation, '%Y-%m')
ORDER BY mois DESC;

-- Top 10 des produits les plus vendus
SELECT 
    p.nom,
    p.prix,
    SUM(cd.quantite) AS total_vendu,
    SUM(cd.sous_total) AS ca_produit
FROM produits p
INNER JOIN commande_details cd ON p.id = cd.produit_id
INNER JOIN commandes c ON cd.commande_id = c.id
WHERE c.statut IN ('payee', 'expediee', 'livree')
GROUP BY p.id, p.nom, p.prix
ORDER BY total_vendu DESC
LIMIT 10;

-- Clients les plus actifs
SELECT 
    u.nom,
    u.prenom,
    u.email,
    COUNT(c.id) AS nb_commandes,
    SUM(c.total) AS montant_total,
    AVG(c.total) AS panier_moyen
FROM users u
INNER JOIN commandes c ON u.id = c.user_id
WHERE c.statut IN ('payee', 'expediee', 'livree')
GROUP BY u.id
ORDER BY montant_total DESC;

-- Analyse des stocks critiques
SELECT 
    p.nom,
    p.reference,
    p.stock,
    p.stock_min,
    c.nom AS categorie,
    CASE 
        WHEN p.stock = 0 THEN 'Rupture'
        WHEN p.stock <= p.stock_min THEN 'Stock faible'
        ELSE 'OK'
    END AS statut_stock
FROM produits p
INNER JOIN categories c ON p.categorie_id = c.id
WHERE p.actif = TRUE AND p.stock <= p.stock_min
ORDER BY p.stock ASC;

-- Performance des catégories
SELECT 
    c.nom AS categorie,
    COUNT(p.id) AS nb_produits,
    AVG(p.prix) AS prix_moyen,
    SUM(COALESCE(cd.quantite, 0)) AS total_ventes,
    SUM(COALESCE(cd.sous_total, 0)) AS ca_categorie
FROM categories c
LEFT JOIN produits p ON c.id = p.categorie_id
LEFT JOIN commande_details cd ON p.id = cd.produit_id
LEFT JOIN commandes cmd ON cd.commande_id = cmd.id 
    AND cmd.statut IN ('payee', 'expediee', 'livree')
GROUP BY c.id, c.nom
ORDER BY ca_categorie DESC;

-- =====================================================
-- 14. PROCÉDURES STOCKÉES UTILES
-- =====================================================

DELIMITER //

-- Procédure pour mettre à jour le total d'une commande
CREATE PROCEDURE UpdateCommandeTotal(IN commande_id_param INT)
BEGIN
    UPDATE commandes 
    SET sous_total = (
        SELECT COALESCE(SUM(sous_total), 0)
        FROM commande_details 
        WHERE commande_id = commande_id_param
    ),
    total = sous_total + frais_livraison + taxe - remise
    WHERE id = commande_id_param;
END//

-- Fonction pour calculer la note moyenne d'un produit
CREATE FUNCTION GetNoyenneProduit(produit_id_param INT) 
RETURNS DECIMAL(3,2)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE moyenne DECIMAL(3,2) DEFAULT 0;
    
    SELECT AVG(note) INTO moyenne
    FROM avis 
    WHERE produit_id = produit_id_param AND approuve = TRUE;
    
    RETURN COALESCE(moyenne, 0);
END//

-- Trigger pour décrémenter le stock après une commande
CREATE TRIGGER after_commande_detail_insert 
AFTER INSERT ON commande_details
FOR EACH ROW
BEGIN
    UPDATE produits 
    SET stock = stock - NEW.quantite 
    WHERE id = NEW.produit_id;
END//

-- Trigger pour incrementer le stock si annulation
CREATE TRIGGER after_commande_detail_delete 
AFTER DELETE ON commande_details
FOR EACH ROW
BEGIN
    UPDATE produits 
    SET stock = stock + OLD.quantite 
    WHERE id = OLD.produit_id;
END//

DELIMITER ;

-- =====================================================
-- 15. VUES UTILES POUR L'APPLICATION
-- =====================================================

-- Vue des produits avec informations complètes
CREATE VIEW vue_produits_complet AS
SELECT 
    p.id,
    p.nom,
    p.slug,
    p.description_courte,
    p.prix,
    p.prix_promo,
    p.stock,
    p.image_principale,
    c.nom AS categorie,
    c.slug AS categorie_slug,
    GetNoyenneProduit(p.id) AS note_moyenne,
    COUNT(a.id) AS nb_avis,
    CASE 
        WHEN p.prix_promo IS NOT NULL THEN p.prix_promo 
        ELSE p.prix 
    END AS prix_final
FROM produits p
INNER JOIN categories c ON p.categorie_id = c.id
LEFT JOIN avis a ON p.id = a.produit_id AND a.approuve = TRUE
WHERE p.actif = TRUE
GROUP BY p.id;

-- Vue des commandes avec détails clients
CREATE VIEW vue_commandes_details AS
SELECT 
    c.id,
    c.numero_commande,
    c.statut,
    c.total,
    c.date_creation,
    u.nom AS client_nom,
    u.prenom AS client_prenom,
    u.email AS client_email,
    COUNT(cd.id) AS nb_articles,
    SUM(cd.quantite) AS total_quantite
FROM commandes c
INNER JOIN users u ON c.user_id = u.id
LEFT JOIN commande_details cd ON c.id = cd.commande_id
GROUP BY c.id;

-- =====================================================
-- FIN DU SCRIPT - BASE PRÊTE À L'EMPLOI !
-- =====================================================