-- Beschreibung: "Meistverkaufte Produkte des Monats"
SELECT
    p.produktname,
    COUNT(*) as verkauft_mal,
    SUM(v.menge) as gesamtmenge
FROM produkte p
JOIN verkaeufe v ON p.id = v.produkt_id
WHERE MONTH(v.verkaufsdatum) = MONTH(CURRENT_DATE)
GROUP BY p.id, p.produktname
ORDER BY gesamtmenge DESC
LIMIT 10

-- Beschreibung: "Kundenbestellungen mit Produktdetails"
SELECT
    k.kundenname,
    b.bestellnummer,
    p.produktname,
    bp.menge,
    bp.einzelpreis,
    (bp.menge * bp.einzelpreis) as gesamtpreis
FROM bestellungen b
JOIN kunden k ON b.kunden_id = k.id
JOIN bestellpositionen bp ON b.id = bp.bestell_id
JOIN produkte p ON bp.produkt_id = p.id
WHERE b.bestelldatum >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY b.bestelldatum DESC

-- Beschreibung: "Umsatzanalyse nach Region"
SELECT
    r.regionsname,
    COUNT(DISTINCT b.kunden_id) as anzahl_kunden,
    COUNT(b.id) as anzahl_bestellungen,
    SUM(b.gesamtbetrag) as gesamtumsatz,
    AVG(b.gesamtbetrag) as durchschnittsbestellung
FROM regionen r
JOIN kunden k ON r.id = k.regions_id
JOIN bestellungen b ON k.id = b.kunden_id
GROUP BY r.id, r.regionsname
ORDER BY gesamtumsatz DESC

-- Beschreibung: "Kunden über Durchschnittsumsatz"
SELECT
    k.kundenname,
    SUM(b.gesamtbetrag) as kundengesamtumsatz,
    (
        SELECT AVG(gesamtbetrag)
        FROM bestellungen
        WHERE YEAR(bestelldatum) = YEAR(CURRENT_DATE)
    ) as durchschnittsumsatz
FROM kunden k
JOIN bestellungen b ON k.id = b.kunden_id
GROUP BY k.id, k.kundenname
HAVING kundengesamtumsatz > durchschnittsumsatz
ORDER BY kundengesamtumsatz DESC

-- Beschreibung: "Verkaufstrends mit Vormonatvergleich"
SELECT
    DATE_FORMAT(verkaufsdatum, '%Y-%m') as monat,
    SUM(betrag) as monatsumsatz,
    LAG(SUM(betrag)) OVER (ORDER BY DATE_FORMAT(verkaufsdatum, '%Y-%m')) as vormonatsumsatz,
    ROUND(
        ((SUM(betrag) - LAG(SUM(betrag)) OVER (ORDER BY DATE_FORMAT(verkaufsdatum, '%Y-%m'))) /
        LAG(SUM(betrag)) OVER (ORDER BY DATE_FORMAT(verkaufsdatum, '%Y-%m')) * 100),
        2
    ) as wachstum_prozent
FROM verkaeufe
GROUP BY monat
ORDER BY monat DESC

-- Beschreibung: "Kritischer Lagerbestand"
SELECT
    p.produktname,
    p.lagerbestand,
    p.mindestbestand,
    l.lieferantenname,
    k.kategoriename,
    COALESCE(v.durchschnitt_verkauf, 0) as monatlicher_durchsatz
FROM produkte p
LEFT JOIN (
    SELECT
        produkt_id,
        ROUND(AVG(menge), 2) as durchschnitt_verkauf
    FROM verkaeufe
    WHERE verkaufsdatum >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)
    GROUP BY produkt_id
) v ON p.id = v.produkt_id
JOIN lieferanten l ON p.lieferant_id = l.id
JOIN kategorien k ON p.kategorie_id = k.id
WHERE p.lagerbestand <= p.mindestbestand
ORDER BY p.lagerbestand ASC

-- Beschreibung: "Kundensegmentierung nach Kaufverhalten"
WITH kundenstatistik AS (
    SELECT
        k.id,
        k.kundenname,
        COUNT(b.id) as anzahl_bestellungen,
        SUM(b.gesamtbetrag) as gesamtumsatz,
        MAX(b.bestelldatum) as letzte_bestellung
    FROM kunden k
    LEFT JOIN bestellungen b ON k.id = b.kunden_id
    GROUP BY k.id, k.kundenname
)
SELECT
    kundenname,
    anzahl_bestellungen,
    gesamtumsatz,
    CASE
        WHEN gesamtumsatz > 10000 AND anzahl_bestellungen >= 10 THEN 'Premium'
        WHEN gesamtumsatz > 5000 OR anzahl_bestellungen >= 5 THEN 'Standard'
        ELSE 'Basis'
    END as kundensegment,
    DATEDIFF(CURRENT_DATE, letzte_bestellung) as tage_seit_letzter_bestellung
FROM kundenstatistik
ORDER BY gesamtumsatz DESC