/*
    Creazione tabelle Prodotto, cliente e ordine con i rispettivi dati
*/

-- Creazione delle tabelle
CREATE TABLE prodotto (
    id INT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    prezzo DECIMAL(10, 2) NOT NULL
);

CREATE TABLE cliente (
    id INT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE ordini (
    id_prodotto INT,
    id_cliente INT,
    data DATE,
    FOREIGN KEY (id_prodotto) REFERENCES prodotto(id),
    FOREIGN KEY (id_cliente) REFERENCES cliente(id)
);

-- Inserimento dei dati nella tabella 'prodotto'
INSERT INTO prodotto (id, nome, prezzo) VALUES
(1, 'Smartphone', 699.99),
(2, 'Laptop', 1099.99),
(3, 'Auricolari Bluetooth', 49.99),
(4, 'Monitor 4K', 299.99),
(5, 'Mouse Wireless', 19.99),
(6, 'Tastiera Meccanica', 89.99),
(7, 'Tablet', 499.99),
(8, 'Smartwatch', 199.99),
(9, 'Fotocamera DSLR', 799.99),
(10, 'Stampante', 129.99);

-- Inserimento dei dati nella tabella 'cliente'
INSERT INTO cliente (id, email) VALUES
(1, 'mario.rossi@example.com'),
(2, 'luigi.verdi@example.com'),
(3, 'anna.bianchi@example.com'),
(4, 'carla.neri@example.com'),
(5, 'giulia.ferri@example.com');

-- Inserimento dei dati nella tabella 'ordini'
INSERT INTO ordini (id_prodotto, id_cliente, data) VALUES
(1, 1, '2025-01-01'),
(2, 1, '2025-01-02'),
(3, 2, '2025-01-03'),
(4, 3, '2025-01-04'),
(5, 4, '2025-01-05'),
(6, 5, '2025-01-06'),
(7, 1, '2025-01-07'),
(8, 2, '2025-01-08'),
(9, 3, '2025-01-09'),
(10, 4, '2025-01-10'),
(1, 5, '2025-01-11'),
(2, 1, '2025-01-12'),
(3, 2, '2025-01-13'),
(4, 3, '2025-01-14'),
(5, 4, '2025-01-15'),
(6, 5, '2025-01-16'),
(7, 1, '2025-01-17'),
(8, 2, '2025-01-18'),
(9, 3, '2025-01-19'),
(10, 4, '2025-01-20');



/*
    1. Preparazione dei Dati

    Creazione di una vista temporanea che comprende il raggruppamento delle tabelle con i rispettivi dati.
    Viene effettuata una join tra "prodotto" e "odini" utilizzando la colonna "id" per la tabella "prodotto" e la colonna 
    "id_prodotto" per la tabella "ordini". Per effettuare il collegamento tra "cliente" e "ordini" invece utilizzo 
    la colonna "id" per la tabella "cliente" e la colonna "id_cliente" dalla tabella "ordini" in modo da avere una sola tabella su cui effettuare le query.
*/

create view vista_cliente_ordini_prodotto as
SELECT
	c.id AS id_cliente,
	c.email AS email,
	p.id AS id_prodotto,
	p.nome AS nome_prodotto,
	p.prezzo AS prezzo,
	o.data AS data_ordine
from ordini o
JOIN cliente c ON c.id = o.id_cliente
JOIN prodotto p ON o.id_prodotto = p.id;

-- 2. Analisi dei Clienti

--  Calcolare la spesa totale per ogni cliente sommando i prezzi dei prodotti acquistati.

SELECT 
    id_cliente,
    email,
    SUM(prezzo) AS totale
FROM vista_cliente_ordini_prodotto
GROUP BY id_cliente, email;

-- Ordinare i clienti in base alla spesa totale e identificare i top spender.

SELECT 
    id_cliente, 
    email,
    SUM(prezzo) AS totale
FROM vista_cliente_ordini_prodotto
GROUP BY id_cliente, email
ORDER BY totale DESC;

-- 3. Analisi dei Prodotti

-- Determinare il fatturato totale generato da ciascun prodotto.

SELECT 
    id_prodotto,
    nome_prodotto,
    SUM(prezzo) AS totale
FROM vista_cliente_ordini_prodotto
GROUP BY id_prodotto, nome_prodotto;

-- Ordinare i prodotti in base al fatturato totale per identificare i best-seller.

SELECT 
    id_prodotto, 
    nome_prodotto, 
    SUM(prezzo) AS totale
FROM vista_cliente_ordini_prodotto
GROUP BY id_prodotto, nome_prodotto
ORDER BY totale DESC;

-- 4. Analisi Temporale

-- Aggregare i dati delle vendite per giorno della settimana.

SELECT 
    DAYNAME(data_ordine) AS nome_giorno, 
    DAYOFWEEK(data_ordine) AS giorno, 
    SUM(prezzo) AS totale
FROM vista_cliente_ordini_prodotto
GROUP BY DAYNAME(data_ordine), DAYOFWEEK(data_ordine)
ORDER BY giorno ASC;

-- Calcolare il fatturato medio giornaliero e individuare i giorni con la media più alta.

SELECT 
    DAYNAME(data_ordine) AS nome_giorno, 
    DAYOFWEEK(data_ordine) AS giorno, 
    SUM(prezzo) AS totale,
    AVG(prezzo) AS media
FROM vista_cliente_ordini_prodotto
GROUP BY DAYNAME(data_ordine), DAYOFWEEK(data_ordine)
ORDER BY media DESC;

-- 5. Analisi degli Ordini

-- Calcolare il numero di prodotti per ciascun ordine.

SELECT data, COUNT(*) AS numero_prodotti
FROM ordini
GROUP BY data;

-- Determinare la media e la distribuzione del numero di prodotti per ordine.
/*
    Nello schema attuale
        - la tabella ordini non ha un identificativo ordini
        - ad ogni riga della tabella ordine corrisponde ad un ordine
        - raggruppare per data corrisponde selezionare tutte le righe della tabella ordine

    Assumendo che:
        - in una stessa data esiste un solo ordine
        - tutti i record con la stessa data fanno parte dello stesso ordine

    CONCLUSIONE:

    Raggruppando per data si assume che ad ogni data corrisponde un solo ordine. Poiché nel modello ogni data compare una sola volta il numero medio dei prodotti è 1.
    Se in fututo ci fossero più ordini con la stessa data allora sarebbe più corretto aggiungere un id_ordine

*/

SELECT AVG(numero_prodotti) AS media_prodotti_per_cliente
FROM (
    SELECT data, COUNT(*) AS numero_prodotti
    FROM ordini
    GROUP BY data
) AS t;