
CREATE TABLE utente (
ut_tel VARCHAR(15) PRIMARY KEY,
ut_cognome VARCHAR(20) NOT NULL,
ut_nome VARCHAR(20) NOT NULL
);

CREATE TABLE gruppo (
gr_id INTEGER PRIMARY KEY,
gr_nome VARCHAR(40) NOT NULL
);

CREATE TABLE UtenteGruppo (
ug_utente VARCHAR(15) REFERENCES utente(ut_tel),
ug_gruppo INTEGER REFERENCES gruppo (gr_id),
PRIMARY KEY (ug_utente, ug_gruppo)
);

CREATE TABLE messaggio (
me_id INTEGER PRIMARY KEY,
me_msg TEXT NOT NULL,
me_mittente VARCHAR(15) REFERENCES utente(ut_tel) NOT NULL,
me_dest_gruppo INTEGER REFERENCES gruppo (gr_id),
me_dest_utente VARCHAR (15) REFERENCES utente(ut_tel),
me_orario TIMESTAMP NOT NULL,
me_allegato CHAR(1) NOT NULL CHECK (me_allegato IN ('S','N')),
CHECK ((me_dest_utente IS NULL )<>(me_dest_gruppo IS NULL)) -- uno dei due non deve essere NULL
);



-- Popolamento tabella utente
INSERT INTO utente (ut_tel, ut_cognome, ut_nome) VALUES
('3201111111', 'Rossi', 'Mario'),
('3202222222', 'Bianchi', 'Luca'),
('3203333333', 'Verdi', 'Anna'),
('3204444444', 'Neri', 'Giulia'),
('3205555555', 'Galli', 'Marco');

-- Popolamento tabella gruppo
INSERT INTO gruppo (gr_id, gr_nome) VALUES
(1, 'Amici'),
(2, 'Lavoro'),
(3, 'Universita');

-- Popolamento tabella UtenteGruppo
INSERT INTO UtenteGruppo (ug_utente, ug_gruppo) VALUES
('3201111111', 1),
('3202222222', 1),
('3203333333', 1),

('3201111111', 2),
('3204444444', 2),
('3205555555', 2),

('3202222222', 3),
('3203333333', 3),
('3205555555', 3);

-- Popolamento tabella messaggio
INSERT INTO messaggio (
    me_id,
    me_msg,
    me_mittente,
    me_dest_gruppo,
    me_dest_utente,
    me_orario,
    me_allegato
) VALUES
-- Messaggi privati
(1, 'Ciao Luca, come stai?', '3201111111', NULL, '3202222222',
 '2026-05-09 09:15:00', 'N'),

(2, 'Tutto bene, grazie!', '3202222222', NULL, '3201111111',
 '2026-05-09 09:17:00', 'N'),

(3, 'Ti mando il file richiesto', '3204444444', NULL, '3205555555',
 '2026-05-09 10:00:00', 'S'),

-- Messaggi ai gruppi
(4, 'Ragazzi usciamo stasera?', '3203333333', 1, NULL,
 '2026-05-09 11:30:00', 'N'),

(5, 'Meeting alle 15 in sala riunioni', '3201111111', 2, NULL,
 '2026-05-09 12:00:00', 'N'),

(6, 'Qualcuno ha gli appunti di oggi?', '3205555555', 3, NULL,
 '2026-05-09 14:20:00', 'N'),

(7, 'Ecco le slide del corso', '3203333333', 3, NULL,
 '2026-05-09 14:45:00', 'S');
 

/* Popolare il database con piu' dati in modo che si possa identificare un gruppo
   che abbia spedito piu' di un messaggio.
   poi creare una vista su cui fare la query 2 e la 3
*/

-- 1)Quali messaggi ha ricevuto e da chi un gruppo in un certo periodo di tempo

SELECT me_msg, me_orario, ut_nome, ut_tel
FROM messaggio, utente, gruppo
WHERE
	ut_tel = me_mittente AND me_dest_gruppo = gr_id
	AND gr_id = 1 AND me_orario > '2020-06-02 19:10'
-- ORDER BY me_orario, ut_nome
;


-- insert aggiuntive per vedere la correttezza della query

INSERT INTO messaggio (
    me_id,
    me_msg,
    me_mittente,
    me_dest_gruppo,
    me_dest_utente,
    me_orario,
    me_allegato
) VALUES
(8, 'allegato', '3203333333', 3, NULL,
 '2026-05-09 14:45:00', 'S'),
 (9, 'Meeting alle 15 in sala riunioni', '3201111111', 2, NULL,
 '2026-05-09 12:00:00', 'S'),
 (10, 'Meeting alle 15 in sala riunioni', '3201111111', 2, NULL,
 '2026-05-09 12:00:00', 'S');
 
 -- 2)Quale gruppo ha ricevuto piu' file multimediali

select gr_nome, count(*) as num_file_multimediali
from utente, messaggio, gruppo
where 
    me_mittente = ut_tel and me_dest_gruppo = gr_id
    and me_allegato = 'S'
group by gr_nome
having count(*) = (
    select count(*) as num_file_multimediali
    from utente, messaggio, gruppo
    where 
        me_mittente = ut_tel and me_dest_gruppo = gr_id
        and me_allegato = 'S'
    group by gr_nome
    order by num_file_multimediali
    limit 1
    )
order by num_file_multimediali, gr_nome;


-- tentiamo una soluzione piu' chiara con vista
-- mettere cognome none utente, nome gruppo ecc.
CREATE VIEW vista_allegati_gruppi AS
SELECT g.gr_nome, COUNT(*) AS totale_allegati
FROM gruppo g
JOIN messaggio m ON g.gr_id = m.me_dest_gruppo
WHERE m.me_allegato = 'S'
GROUP BY g.gr_nome;

--Per avere un gruppo da 3 allegati
INSERT INTO messaggio (
    me_id,
    me_msg,
    me_mittente,
    me_dest_gruppo,
    me_dest_utente,
    me_orario,
    me_allegato
) VALUES
 (22, 'Meeting alle 20 in sala riunioni', '3201111111', 3, NULL,
 '2026-05-09 12:00:00', 'S');

SELECT gr_nome, totale_allegati
FROM vista_allegati_gruppi
WHERE totale_allegati = (SELECT MAX(totale_allegati) FROM vista_allegati_gruppi);

3)L'utente che ha spedito piu' messaggi

CREATE VIEW vista_messaggi_utenti AS
SELECT u.ut_tel, u.ut_nome, u.ut_cognome, COUNT(*) AS num_messaggi
FROM utente u
JOIN messaggio m ON u.ut_tel = m.me_mittente
GROUP BY u.ut_tel, u.ut_nome, u.ut_cognome;

SELECT ut_nome, ut_cognome, num_messaggi
FROM vista_messaggi_utenti
WHERE num_messaggi = (SELECT MAX(num_messaggi) FROM vista_messaggi_utenti);
