SELECT u.id_usuari, u.nom, u.nick_joc, u.email,
                  r.nom_rol, r.descripcio AS descripcio_rol
           FROM usuaris u
           INNER JOIN rols r ON u.id_rol = r.id_rol
           WHERE u.actiu = 1
           ORDER BY r.id_rol ASC, u.nom ASC;

SELECT r.nom_rol, r.descripcio,
                  COUNT(u.id_usuari) AS total_usuaris
           FROM rols r
           LEFT JOIN usuaris u ON r.id_rol = u.id_rol AND u.actiu = 1
           GROUP BY r.id_rol, r.nom_rol, r.descripcio
           ORDER BY total_usuaris DESC;

SELECT lp.nom_événiment, u.nick_joc, u.nom,
                  CASE WHEN i.ha_pagat = 1 THEN 'Sí' ELSE 'No' END AS ha_pagat,
                  CASE WHEN i.ha_assistit = 1 THEN 'Sí' ELSE 'No' END AS ha_assistit,
                  i.data_inscripcio
           FROM inscripcions i
           INNER JOIN usuaris u ON i.id_usuari = u.id_usuari
           INNER JOIN lan_party lp ON i.id_lan_party = lp.id_lan_party
           ORDER BY lp.id_lan_party ASC, i.ha_pagat DESC, u.nom ASC;

SELECT lp.nom_événiment, lp.capacitat_maxima,
                  COUNT(i.id_inscripcio) AS total_inscrits,
                  SUM(CASE WHEN i.ha_pagat = 1 THEN 1 ELSE 0 END) AS han_pagat,
                  SUM(CASE WHEN i.ha_assistit = 1 THEN 1 ELSE 0 END) AS han_assistit,
                  ROUND(SUM(CASE WHEN i.ha_assistit=1 THEN 1 ELSE 0 END)*100.0/COUNT(i.id_inscripcio),1) AS pct_assistencia
           FROM lan_party lp
           LEFT JOIN inscripcions i ON lp.id_lan_party = i.id_lan_party
           GROUP BY lp.id_lan_party, lp.nom_événiment, lp.capacitat_maxima
           HAVING COUNT(i.id_inscripcio) >= 3
           ORDER BY total_inscrits DESC;

SELECT t.id_torneig, t.nom_torneig, j.nom_joc, j.genere,
                  lp.nom_événiment, t.format, t.max_participants,
                  t.premi, t.estat, t.data_inici, t.data_fi
           FROM tornejos t
           INNER JOIN jocs j ON t.id_joc = j.id_joc
           INNER JOIN lan_party lp ON t.id_lan_party = lp.id_lan_party
           ORDER BY t.estat ASC, t.data_inici ASC;

SELECT j.nom_joc, j.genere,
                  COUNT(DISTINCT t.id_torneig) AS num_tornejos,
                  COUNT(pt.id_participacio) AS num_participacions,
                  COALESCE(MAX(pt.puntuacio), 0) AS puntuacio_maxima
           FROM jocs j
           LEFT JOIN tornejos t ON j.id_joc = t.id_joc
           LEFT JOIN participants_torneig pt ON t.id_torneig = pt.id_torneig
           GROUP BY j.id_joc, j.nom_joc, j.genere
           HAVING COUNT(pt.id_participacio) > 0
           ORDER BY num_participacions DESC;

SELECT u.nick_joc, u.nom,
                  COUNT(DISTINCT pt.id_torneig) AS tornejos_jugats,
                  SUM(pt.puntuacio) AS puntuacio_total,
                  MIN(pt.posicio_final) AS millor_posicio
           FROM usuaris u
           INNER JOIN participants_torneig pt ON u.id_usuari = pt.id_usuari
           GROUP BY u.id_usuari, u.nick_joc, u.nom
           ORDER BY puntuacio_total DESC;

SELECT u.nick_joc, u.nom,
                  (SELECT COUNT(*) FROM partides p
                   WHERE p.id_guanyador = u.id_usuari AND p.estat='finalitzada') AS victoríes,
                  (SELECT COUNT(*) FROM partides p
                   WHERE (p.id_jugador1=u.id_usuari OR p.id_jugador2=u.id_usuari)
                     AND p.estat='finalitzada') AS partides_jugades
           FROM usuaris u
           WHERE (SELECT COUNT(*) FROM partides p
                  WHERE p.id_guanyador=u.id_usuari AND p.estat='finalitzada') >= 2
           ORDER BY victoríes DESC;

SELECT t.nom_torneig, j.nom_joc, u.nick_joc, u.nom,
                  pt.posicio_final, pt.puntuacio
           FROM participants_torneig pt
           INNER JOIN tornejos t ON pt.id_torneig = t.id_torneig
           INNER JOIN jocs j ON t.id_joc = j.id_joc
           INNER JOIN usuaris u ON pt.id_usuari = u.id_usuari
           WHERE pt.posicio_final IS NOT NULL
           ORDER BY t.id_torneig ASC, pt.posicio_final ASC;

SELECT t.nom_torneig, p.ronda,
                  u1.nick_joc AS jugador_1, u2.nick_joc AS jugador_2,
                  p.puntuacio_j1, p.puntuacio_j2,
                  COALESCE(ug.nick_joc, 'Empat / Pendent') AS guanyador,
                  p.estat, p.data_hora
           FROM partides p
           INNER JOIN tornejos t ON p.id_torneig = t.id_torneig
           INNER JOIN usuaris u1 ON p.id_jugador1 = u1.id_usuari
           INNER JOIN usuaris u2 ON p.id_jugador2 = u2.id_usuari
           LEFT JOIN usuaris ug ON p.id_guanyador = ug.id_usuari
           WHERE p.estat = 'finalitzada'
           ORDER BY t.id_torneig ASC, p.ronda ASC;

SELECT lp.nom_événiment, u.nick_joc AS autor, m.contingut, m.data_hora,
                  CASE WHEN m.llegit = 1 THEN 'Llegit' ELSE 'No llegit' END AS estat_lectura
           FROM missatges_xat m
           INNER JOIN usuaris u ON m.id_usuari = u.id_usuari
           INNER JOIN lan_party lp ON m.id_lan_party = lp.id_lan_party
           ORDER BY lp.id_lan_party ASC, m.data_hora DESC;

SELECT n.tipus,
                  COUNT(*) AS total_no_llegides,
                  GROUP_CONCAT(n.titol, ' | ') AS titols_pendents
           FROM notificacions n
           WHERE n.llegida = 0
           GROUP BY n.tipus
           HAVING COUNT(*) >= 1
           ORDER BY total_no_llegides DESC;