-- Analisi query lente
SELECT 
    q.query_text,
    COUNT(*) as esecuzioni,
    AVG(q.execution_time) as tempo_medio_ms,
    MAX(q.execution_time) as tempo_massimo_ms,
    SUM(q.rows_examined) as righe_esaminate
FROM query_log q
WHERE q.execution_date >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
GROUP BY q.query_text
HAVING tempo_medio_ms > 1000
ORDER BY tempo_medio_ms DESC

-- Dimensioni e crescita delle tabelle
SELECT 
    t.table_name,
    t.table_rows,
    FORMAT((t.data_length + t.index_length) / 1024 / 1024, 2) as dimensione_mb,
    FORMAT(t.data_length / 1024 / 1024, 2) as dati_mb,
    FORMAT(t.index_length / 1024 / 1024, 2) as indici_mb
FROM information_schema.tables t
WHERE t.table_schema = DATABASE()
ORDER BY (t.data_length + t.index_length) DESC

-- Performance vendite mensili
WITH vendite_mensili AS (
    SELECT 
        DATE_FORMAT(v.data_vendita, '%Y-%m') as mese,
        c.categoria,
        COUNT(*) as num_vendite,
        SUM(v.importo) as totale_vendite,
        COUNT(DISTINCT v.cliente_id) as clienti_unici
    FROM vendite v
    JOIN prodotti p ON v.prodotto_id = p.id
    JOIN categorie c ON p.categoria_id = c.id
    WHERE v.data_vendita >= DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH)
    GROUP BY mese, c.categoria
)
SELECT 
    mese,
    categoria,
    FORMAT(totale_vendite, 2) as totale_vendite_eur,
    num_vendite,
    clienti_unici,
    FORMAT(totale_vendite / num_vendite, 2) as valore_medio_ordine,
    FORMAT(totale_vendite / clienti_unici, 2) as valore_per_cliente
FROM vendite_mensili
ORDER BY mese DESC, totale_vendite DESC

-- Segmentazione clienti avanzata
WITH metriche_clienti AS (
    SELECT 
        c.cliente_id,
        c.nome_cliente,
        COUNT(DISTINCT v.ordine_id) as num_ordini,
        SUM(v.importo) as totale_speso,
        MAX(v.data_vendita) as ultimo_acquisto,
        DATEDIFF(CURRENT_DATE, MAX(v.data_vendita)) as giorni_ultimo_acquisto,
        AVG(v.importo) as valore_medio_ordine
    FROM clienti c
    LEFT JOIN vendite v ON c.cliente_id = v.cliente_id
    GROUP BY c.cliente_id, c.nome_cliente
)
SELECT 
    CASE 
        WHEN num_ordini >= 10 AND totale_speso >= 10000 THEN 'VIP'
        WHEN num_ordini >= 5 AND totale_speso >= 5000 THEN 'Gold'
        WHEN num_ordini >= 2 THEN 'Silver'
        ELSE 'Bronze'
    END as segmento,
    COUNT(*) as num_clienti,
    FORMAT(AVG(totale_speso), 2) as spesa_media_eur,
    FORMAT(AVG(valore_medio_ordine), 2) as ordine_medio_eur,
    ROUND(AVG(giorni_ultimo_acquisto)) as media_giorni_ultimo_acquisto
FROM metriche_clienti
GROUP BY segmento
ORDER BY AVG(totale_speso) DESC

-- Suggerimenti indici
SELECT 
    t.table_name,
    t.column_name,
    t.data_type,
    t.column_key,
    t.column_default,
    t.is_nullable,
    s.rows_examined,
    s.rows_examined / s.executions as rows_per_scan
FROM information_schema.columns t
JOIN performance_schema.table_io_waits_summary_by_index_usage s 
    ON t.table_name = s.object_name
WHERE t.table_schema = DATABASE()
AND s.rows_examined > 1000
ORDER BY rows_per_scan DESC

-- Analisi e ottimizzazione
EXPLAIN ANALYZE
SELECT 
    p.nome_prodotto,
    c.categoria,
    COUNT(*) as vendite,
    SUM(v.importo) as totale
FROM vendite v
JOIN prodotti p ON v.prodotto_id = p.id
JOIN categorie c ON p.categoria_id = c.id
WHERE v.data_vendita >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY p.nome_prodotto, c.categoria
HAVING vendite > 10
ORDER BY totale DESC