drop database if exists vueltaciclista;

create database if not exists vueltaciclista;

use vueltaciclista;

create table if not exists ciclista(idciclista int primary key,
nombre varchar(50),
apellido varchar(50),
fechanacimiento date,
pais varchar(50))engine=innodb;

create table if not exists bicicleta(idbici int primary key,
marca varchar(100),
modelo varchar(100))engine=innodb;

create table if not exists maillot(idmaillot int primary key,
color varchar(50),
descrip varchar(100))engine=innodb;

create table if not exists competicion(idcompeticion int primary key,
nombre varchar(50),
fechaini date,
fechafin date,
ubi varchar(100))engine=innodb;

create table if not exists ciclistabicis(idciclista int,
idbici int,
foreign key (idciclista) references ciclista (idciclista) on delete cascade on update cascade,
foreign key (idbici) references bicicleta  (idbici) on delete cascade on update cascade)engine=innodb;

create table if not exists etapas(idetapa int primary key,
idcompeticion int,
nombre varchar(50),
distancia float,
fecha date,
foreign key (idcompeticion) references competicion  (idcompeticion) on delete cascade on update cascade)engine=innodb;

create table if not exists resultados (idciclista int,
idcompeticion int,
idetapa int,
posicion int,
foreign key (idciclista) references ciclista (idciclista) on delete cascade on update cascade,
foreign key (idcompeticion) references competicion (idcompeticion) on delete cascade on update cascade,
foreign key (idetapa) references etapas (idetapa) on delete cascade on update cascade)engine=innodb;

create table if not exists ciclistamaillots(idciclista int,
idmaillot int,
idcompeticion int,
idetapa int,
foreign key (idciclista) references ciclista (idciclista) on delete cascade on update cascade,
foreign key (idmaillot) references maillot (idmaillot) on delete cascade on update cascade,
foreign key (idcompeticion) references competicion (idcompeticion) on delete cascade on update cascade,
foreign key (idetapa) references etapas (idetapa) on delete cascade on update cascade)engine=innodb;

-- 1.- ¿Nombre de los ciclistas que no han llevado nunca el maillot amarillo?
select c.nombre from ciclista c
	inner join ciclistamaillots cm on cm.idciclista=c.idciclista
		where cm.idmaillot not in (select cm.idmaillot from ciclistamaillots cm
			inner join maillot m on cm.idmaillot = m.idmaillot where m.color = "Amarillo");
            
-- 2.- ¿Nombre de los ciclistas que han llevado el maillot amarillo en todas las 
-- competiciones? 
select c.nombre from ciclista c
	inner join ciclistamaillots cm on c.idciclista = cm.idciclista
	inner join maillot m on cm.idmaillot = m.idmaillot
		where m.color = 'Amarillo'
			group by c.idciclista
			having count(distinct cm.idcompeticion) = (select count(*) from competicion);
       
-- 3.- ¿Qué ciclistas han ganado todos los maillots?
select distinct c.nombre from ciclista c
	inner join ciclistamaillots cm on c.idciclista = cm.idciclista
	inner join maillot m on cm.idmaillot = m.idmaillot
		group by c.idciclista
        having count(distinct m.idmaillot) = (select count(*) from maillot);

-- 4.- ¿Qué ciclistas han ganado las competiciones del Tour de Francia y el Giro de 
-- Italia? 
select c.nombre, c.apellido from ciclista c
	inner join resultados r1 on c.idciclista = r1.idciclista
	inner join competicion comp1 on r1.idcompeticion = comp1.idcompeticion
	inner join resultados r2 on c.idciclista = r2.idciclista
	inner join competicion comp2 on r2.idcompeticion = comp2.idcompeticion
		where comp1.nombre = 'Tour de Francia' and comp2.nombre = 'Giro de Italia' and r1.posicion = 1
		and r2.posicion = 1;
        
-- 5.- ¿Qué ciclistas han ganado más etapas en primera posición? 
select c.nombre, c.apellido, count(*) as etapas_ganadas from ciclista c
	inner join resultados r on c.idciclista = r.idciclista
		where r.posicion = 1
			group by c.idciclista
				order by etapas_ganadas desc;
                
-- 6.- ¿Qué etapa es la más larga (más distancia)? 
select nombre from etapas 
	where distancia = (select max(distancia) from etapas);

-- 7.- ¿Qué ciclista es el más joven de la vuelta ciclista y dime su edad?
select nombre from ciclista
	where fechanacimiento = (select max(fechanacimiento) from ciclista);

-- 8.- ¿Qué ciclistas montan todas las marcas de bicis? 
select distinct c.nombre from ciclista c
	inner join ciclistabicis cb on cb.idciclista=c.idciclista
		group by c.idciclista
			having count(distinct cb.idbici)=(select count(*) from ciclistamaillots);
            
-- 9.- ¿Qué ciclista no ha ganado ninguna etapa? 
select distinct c.nombre from ciclista c
	inner join resultados r on c.idciclista = r.idciclista and r.posicon = 1
		where r.idciclista is null;
        
/* TRIGGER */

/*
Añadir a la tabla ciclista un campo llamado puntos_ciclista que inicialmente 
esté a 0 como puntuación de partida. 
Cada vez que el ciclista gane una etapa de una competición si ha quedado en 
primera posición se incrementa el número de puntos en 10 puntos. Si ha 
quedado el segundo 6 puntos y si ha quedado el tercero 4 puntos. Si no ha 
quedado en ninguna de estas posiciones no suma ningún punto.  
Proceso automático. 
*/

alter table ciclista add puntos_ciclista int;

DELIMITER $$

create trigger puntos_etapa after insert on resultados for each row
begin
    if new.posicion = 1 then
        update ciclista 
        set puntos_ciclista = puntos_ciclista + 10
        where idciclista = new.idciclista;

    elseif new.posicion = 2 then
        update ciclista 
        set puntos_ciclista = puntos_ciclista + 6
        where idciclista = new.idciclista;

    elseif new.posicion = 3 then
		 update ciclista 
        set puntos_ciclista = puntos_ciclista + 4
        where idciclista = new.idciclista;
    end if;
end$$

DELIMITER ;