USE master
GO


DROP DATABASE FestivalCannes
GO

DROP login connexion1
DROP login connexion2
DROP login connexion3
DROP login connexion4
DROP login connexion5

CREATE DATABASE FestivalCannes
GO

USE FestivalCannes
GO
/*
drop table schema_jury.Edition, schema_jury.JuryEdition, schema_movies.Note, schema_movies.Film, schema_jury.Jury
drop schema schema_jury
go
drop schema schema_movies
go
drop login connexion1
drop login connexion2
drop login connexion3
drop login connexion4
drop login connexion5
go
drop role all_privileges
go
*/


CREATE schema schema_jury
GO
CREATE schema schema_movies
GO

CREATE login connexion1 WITH password = '1'
CREATE login connexion2 WITH password = '1'
CREATE login connexion3 WITH password = '1'
CREATE login connexion4 WITH password = '1'
CREATE login connexion5 WITH password = '1'
GO

CREATE TABLE schema_jury.Edition(
    Identifier INT PRIMARY KEY,
    Annee INT
);
CREATE TABLE schema_jury.JuryEdition(
    IdentifierJury INT NOT NULL,
    IdentifierEdition INT NOT NULL,
    EstPresident BIT
);
CREATE TABLE schema_movies.Note(
    IdentifierJury INT NOT NULL,
    IdentifierFilm INT NOT NULL,
    Note INT
);
CREATE TABLE schema_movies.Film(
    Identifier INT PRIMARY KEY,
    Titre NVARCHAR(50),
    Duree INT,
    IdentifiantEdition INT NOT NULL
);
CREATE TABLE schema_jury.Jury(
    Identifier INT PRIMARY KEY,
    Nom NVARCHAR(50),
    Prenom NVARCHAR(50)
);
GO

ALTER TABLE schema_jury.JuryEdition
ADD CONSTRAINT [pk_juryedition]
PRIMARY KEY (IdentifierJury, IdentifierEdition);

ALTER TABLE schema_movies.Note
ADD CONSTRAINT [pk_note]
PRIMARY KEY (IdentifierJury, IdentifierFilm);


ALTER TABLE schema_movies.Note ADD CONSTRAINT fk_Note_Jury FOREIGN KEY (IdentifierJury) REFERENCES schema_jury.Jury (Identifier);

ALTER TABLE schema_movies.Note ADD CONSTRAINT fk_Note_Film FOREIGN KEY (IdentifierFilm) REFERENCES schema_movies.Film (Identifier);

ALTER TABLE schema_jury.JuryEdition ADD CONSTRAINT fk_JuryEdition_Edition FOREIGN KEY (IdentifierEdition) REFERENCES schema_jury.Edition (Identifier);

ALTER TABLE schema_jury.JuryEdition ADD CONSTRAINT fk_JuryEdition_Jury FOREIGN KEY (IdentifierJury) REFERENCES schema_jury.Jury (Identifier);

ALTER TABLE schema_movies.Film ADD CONSTRAINT fk_Film_Edition FOREIGN KEY (IdentifiantEdition) REFERENCES schema_jury.Edition (Identifier);
GO

CREATE USER user1 FOR login connexion1
CREATE USER user2 FOR login connexion2
CREATE USER user3 FOR login connexion3
CREATE USER user4 FOR login connexion4
CREATE USER user5 FOR login connexion5
GO

GRANT SELECT, INSERT, UPDATE, DELETE ON schema_jury.Edition TO user1
GRANT INSERT ON schema_movies.Note TO user2
GRANT SELECT ON schema_movies.Note TO user3
GRANT SELECT, INSERT, UPDATE, DELETE ON schema_movies.Film TO user3
CREATE ROLE all_privileges
GRANT control ON schema ::schema_jury TO all_privileges
GRANT control ON schema ::schema_movies TO all_privileges
EXECUTE sp_addrolemember 'all_privileges', 'user4'
EXECUTE sp_addrolemember 'db_datareader', 'user5'
GO

EXECUTE AS USER = 'user1'
GO
INSERT INTO schema_jury.Edition VALUES (1, 2017);
SELECT * FROM schema_jury.Edition
INSERT INTO schema_jury.Jury VALUES (1, 'fdgfdg', 'fgh');
SELECT * FROM schema_jury.Jury
INSERT INTO schema_movies.Film VALUES (1, 'fdgfdg', 30, 1);
SELECT * FROM schema_movies.Film

EXECUTE AS USER = 'user3'
GO
INSERT INTO schema_movies.Film VALUES (1, 'fdgfdg', 30, 1);
SELECT * FROM schema_movies.Film

EXECUTE AS USER = 'user2'
GO
INSERT INTO schema_movies.Note VALUES (1, 1, 2);
SELECT * FROM schema_movies.Note

EXECUTE AS USER = 'user5'
GO
SELECT * FROM schema_movies.Film INNER JOIN schema_jury.Edition ON Edition.Identifier = 1
INSERT INTO schema_movies.Film VALUES (2, 'fdddddddddddddddgfdg', 30, 1);