/*insertion de membres du jury*/
insert into membre_jury (nom, prenom, genre) values ('SMITH', 'Sophie', 'F');
insert into membre_jury (nom, prenom, genre) values ('BROWN', 'Alexia', 'F');
insert into membre_jury (nom, prenom, genre) values ('WILSON', 'Anaïs', 'F');
insert into membre_jury (nom, prenom, genre) values ('CAMPBELL', 'Aurélie', 'F');
insert into membre_jury (nom, prenom, genre) values ('JAMES', 'Chloé ', 'F');
insert into membre_jury (nom, prenom, genre) values ('KING', 'Delphine', 'F');
insert into membre_jury (nom, prenom, genre) values ('LAMBERT', 'Emma', 'F');
insert into membre_jury (nom, prenom, genre) values ('BLANC', 'Gabrielle', 'F');
insert into membre_jury (nom, prenom, genre) values ('LEE', 'Laurence', 'F');
insert into membre_jury (nom, prenom, genre) values ('ROUSSEAU', 'Maélie', 'F');
insert into membre_jury (nom, prenom, genre) values ('MORRIS', 'Anthony', 'M');
insert into membre_jury (nom, prenom, genre) values ('PEDERSEN', 'Paul', 'M');
insert into membre_jury (nom, prenom, genre) values ('COOPER', 'Adam', 'M');
insert into membre_jury (nom, prenom, genre) values ('DUPONT', 'Charles', 'M');
insert into membre_jury (nom, prenom, genre) values ('WATSON', 'Édouard ', 'M');
insert into membre_jury (nom, prenom, genre) values ('OLSEN', 'Félix', 'M');
insert into membre_jury (nom, prenom, genre) values ('GARNIER', 'Isaac', 'M');
insert into membre_jury (nom, prenom, genre) values ('FAURE', 'Julien', 'M');
insert into membre_jury (nom, prenom, genre) values ('LEROY', 'Loïc', 'M');
insert into membre_jury (nom, prenom, genre) values ('TURNER', 'Malik', 'M');



/*insertion de candidat sans jury affecté*/
insert into candidat (nom, prenom, genre) values ('MITCHELL', 'Marilou', 'F');
insert into candidat (nom, prenom, genre) values ('JOHANSSON', 'Mélodie', 'F');
insert into candidat (nom, prenom, genre) values ('MORGAN', 'Olivia', 'F');
insert into candidat (nom, prenom, genre) values ('GAUTIER', 'Sofia', 'F');
insert into candidat (nom, prenom, genre) values ('MORIN', 'Alice', 'F');
insert into candidat (nom, prenom, genre) values ('YOUNG', 'Annabelle', 'F');
insert into candidat (nom, prenom, genre) values ('COOK', 'Camille', 'F');
insert into candidat (nom, prenom, genre) values ('STEWART', 'Clara', 'F');
insert into candidat (nom, prenom, genre) values ('PHILLIPS', 'Elizabeth', 'F');
insert into candidat (nom, prenom, genre) values ('MARCHAND', 'Emy', 'F');
insert into candidat (nom, prenom, genre) values ('EDWARDS', 'Olivier ', 'M');
insert into candidat (nom, prenom, genre) values ('OLSEN', 'Simon', 'M');
insert into candidat (nom, prenom, genre) values ('MULLER', 'Victor', 'M');
insert into candidat (nom, prenom, genre) values ('MURPHY', 'Alex', 'M');
insert into candidat (nom, prenom, genre) values ('ANDERSEN', 'Antoine', 'M');
insert into candidat (nom, prenom, genre) values ('SHAW', 'Christopher', 'M');
insert into candidat (nom, prenom, genre) values ('FONTAINE', 'Elliot', 'M');
insert into candidat (nom, prenom, genre) values ('BRUN', 'Gabriel', 'M');
insert into candidat (nom, prenom, genre) values ('JEAN', 'Jacob', 'M');
insert into candidat (nom, prenom, genre) values ('REED', 'Justin', 'M');


/*insertion de candidats avec jury affecté*/
insert into candidat (nom, prenom, genre, planif, id_jury_f, id_jury_m) values (
    'GILBERT', 
    'Léa', 
    'F', 
    '2022-04-01 14:00:00',
    (select id_membre from membre_jury where (nom,prenom) = ('SMITH','Sophie')), 
    (select id_membre from membre_jury where (nom,prenom) = ('PEDERSEN','Paul')) 
);

insert into candidat (nom, prenom, genre, planif, id_jury_f, id_jury_m) values (
    'TOTO', 
    'Robert', 
    'M', 
    '2022-04-03 14:30:00',
    (select id_membre from membre_jury where (nom,prenom) = ('SMITH','Sophie')), 
    (select id_membre from membre_jury where (nom,prenom) = ('PEDERSEN','Paul')) 
);

insert into candidat (nom, prenom, genre, planif, id_jury_f, id_jury_m) values (
    'FONTAINE', 
    'Jade', 
    'F', 
    '2022-04-01 10:30:00',
    (select id_membre from membre_jury where (nom,prenom) = ('BLANC','Gabrielle')), 
    (select id_membre from membre_jury where (nom,prenom) = ('TURNER','Malik')) 
);
insert into candidat (nom, prenom, genre, planif, id_jury_f, id_jury_m) values (
    'BLANCHARD', 
    'Léo', 
    'M', 
    '2022-04-05 15:30:00',
    (select id_membre from membre_jury where (nom,prenom) = ('BLANC','Gabrielle')), 
    (select id_membre from membre_jury where (nom,prenom) = ('OLSEN','Félix')) 
);
insert into candidat (nom, prenom, genre, planif, id_jury_f, id_jury_m) values (
    'BOURGEOIS', 
    'Hugo', 
    'M', 
    '2022-04-04 17:30:00',
    (select id_membre from membre_jury where (nom,prenom) = ('CAMPBELL','Aurélie')), 
    (select id_membre from membre_jury where (nom,prenom) = ('LEROY','Loïc')) 
);