DROP SCHEMA IF EXISTS FESTIVAL;
CREATE SCHEMA FESTIVAL;
USE FESTIVAL;

CREATE TABLE ARTISTE (
    idArtiste int primary key,
    nomArtiste text
);

CREATE TABLE SCENE (
    idScene int primary key,
    nomScene text
);

CREATE TABLE CONCERT (
    idArtiste int,
    idScene int,
    primary key (idArtiste , idScene),
    constraint fk1 foreign key (idArtiste)
        references ARTISTE (idArtiste),
    foreign key (idScene)
        references SCENE (idScene),
    tarif float
);

CREATE TABLE TICKET (
    idTicket varchar(10) primary key,
    idArtiste int,
    nbrPlaces int,
    foreign key (idArtiste)
        references ARTISTE (idArtiste)
);

INSERT INTO ARTISTE VALUES (1, 'Rihanna'), (2, 'Deep Purple'), (3,
'Gnawa Diffusion');

INSERT INTO SCENE VALUES (1, 'OLM SOUISSI'), (2, 'Bouregreg');

INSERT INTO CONCERT VALUES (1,1,1200), (2,1,600),(3,2,600);

INSERT INTO TICKET VALUES ('T1', 1, 5), ('T2', 1, 2), ('T3', 2, 1);

-- 1


SELECT idArtiste, SUM(nbrPlaces) FROM TICKET
GROUP BY idArtiste
;

SELECT 
    SUM(nbrPlaces) as NbrTickets,
    tarif,
	TICKET.idArtiste,
    SUM(nbrPlaces) * tarif as TotalVentes
FROM
    TICKET,
    CONCERT
WHERE
    TICKET.idArtiste = CONCERT.idArtiste
GROUP BY TICKET.idArtiste;

-- #2 

CREATE VIEW details_festival AS
    SELECT 
        nomArtiste, nomScene, tarif
    FROM
        ARTISTE,
        SCENE,
        CONCERT
    WHERE
        ARTISTE.idArtiste = CONCERT.idArtiste
            AND SCENE.idScene = CONCERT.idScene;

SELECT * FROM details_festival;

-- #3

ALTER TABLE CONCERT DROP FOREIGN KEY fk1;
ALTER TABLE CONCERT DROP FOREIGN KEY concert_ibfk_1;

-- #4
DELIMITER $$
CREATE TRIGGER Insertion_concert
BEFORE INSERT ON CONCERT
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT idArtiste FROM ARTISTE WHERE idArtiste =
NEW.idArtiste) THEN
SIGNAL SQLSTATE '45000' set message_text = 'Violation de clé;';
END IF;
END;
$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER Mise_a_jour_concert
BEFORE UPDATE ON CONCERT
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT idArtiste FROM ARTISTE WHERE idArtiste =
NEW.idArtiste) THEN
SIGNAL SQLSTATE '45000' set message_text = 'Violation de clé;';
END IF;
END;
$$
DELIMITER ;


CREATE TRIGGER Suppression_artiste
BEFORE DELETE ON ARTISTE
FOR EACH ROW
UPDATE CONCERT SET idArtiste = NULL WHERE idArtiste = OLD.idArtiste;

CREATE TRIGGER Mise_a_jour_artiste
BEFORE UPDATE ON ARTISTE
FOR EACH ROW
UPDATE CONCERT SET idArtiste = NULL WHERE idArtiste = OLD.idArtiste;

-- #5.1
DELIMITER $$
CREATE TRIGGER Max_places_vendues
BEFORE INSERT ON TICKET
FOR EACH ROW
IF (SELECT SUM(nbrPlaces) FROM TICKET WHERE idArtiste = NEW.idArtiste)
+ NEW.nbrPlaces > 100
THEN
SIGNAL SQLSTATE '45000' set message_text =
'Nombre de places > 100;'; 
END IF;
$$ 
DELIMITER ;

UPDATE TICKET SET nbrPlaces = 10 WHERE idTicket = 'T2'; 

DELIMITER $$
CREATE TRIGGER Max_places_vendues_update
BEFORE UPDATE ON TICKET
FOR EACH ROW
IF (SELECT SUM(nbrPlaces) FROM TICKET WHERE idArtiste = NEW.idArtiste)
- OLD.nbrPlaces + NEW.nbrPlaces > 100
THEN
SIGNAL SQLSTATE '45000' set message_text =
'Nombre de places > 100;'; 
END IF;
$$ 
DELIMITER ;

-- #5.2

DELIMITER $$
CREATE TRIGGER Tarif_min_max
BEFORE INSERT ON CONCERT
FOR EACH ROW BEGIN
IF (NEW.tarif < 300)
THEN
SIGNAL SQLSTATE '45000' set message_text =
'Tarif < 300;'; 
END IF;
IF (NEW.tarif > 1600)
THEN
SIGNAL SQLSTATE '45000' set message_text =
'Tarif > 1600;'; 
END IF;
END;
$$ 
DELIMITER ;

DELIMITER $$
CREATE TRIGGER Tarif_min_max_update
BEFORE UPDATE ON CONCERT
FOR EACH ROW BEGIN
IF (NEW.tarif < 300)
THEN
SIGNAL SQLSTATE '45000' set message_text =
'Tarif < 300;'; 
END IF;
IF (NEW.tarif > 1600)
THEN
SIGNAL SQLSTATE '45000' set message_text =
'Tarif > 1600;'; 
END IF;
END;
$$ 
DELIMITER ;

-- 3

SELECT 
    ARTISTE.idArtiste
FROM
    ARTISTE,
    CONCERT, 
	TICKET
where	
	ARTISTE.idArtiste = TICKET.idArtiste AND
    ARTISTE.idArtiste = CONCERT.idArtiste
        AND tarif < 1000;

CREATE INDEX index_tarif ON CONCERT(tarif);

-- 4.1 Les niveaux d''isolation: READ UNCOMMITTED, READ COMMITTED,
-- REPEATABLE READ AND SERIALIZABLE

-- 4.2. Gagner en temps d'exécution et en espace mémoire.

-- 4.3. L'intégrité référentielle Etant données deux tables A et B, si
-- une information dans B référence une information dans A,
-- l’information référencée existe dans la table A.

-- 4.4. Un déclencheur par ligne s'exécute une fois pour chaque ligne,
-- un déclencheur par opération s'exécute une seule fois.

-- 4.5. UNION et UNION ALL font l'union de deux ensembles. UNION trie
-- le résultat et enlève les duplicats s'ils existent alors que UNION
-- ALL ne le fait pas.