

-- Roles
CREATE TABLE role (
        id INTEGER AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(30)
);

-- Mapping user role
CREATE TABLE user_role (
        id INTEGER AUTO_INCREMENT PRIMARY KEY,
        user INTEGER REFERENCES user,
        role INTEGER REFERENCES role

);

-- Roles
INSERT INTO role VALUES (1, 'admin');
INSERT INTO role VALUES (2, 'auteur');
INSERT INTO role VALUES (3, 'lecteur');
INSERT INTO role VALUES (4, 'validateur');

-- Mapping User Roles
-- chacun son role
INSERT INTO user_role VALUES (1, 1, 1);
INSERT INTO user_role VALUES (2, 2, 2);
INSERT INTO user_role VALUES (3, 3, 3);
INSERT INTO user_role VALUES (4, 4, 4);


-- Docs
CREATE TABLE doc (
        id INTEGER AUTO_INCREMENT PRIMARY KEY,
        nom VARCHAR(30) NOT NULL,
        statut VARCHAR(30) NOT NULL
);

-- Mapping User Docs
CREATE TABLE user_doc (
        id INTEGER AUTO_INCREMENT PRIMARY KEY,
        user INTEGER REFERENCES user,
        doc INTEGER REFERENCES doc
);


-- Docs
INSERT INTO doc VALUES (1, 'Mon premier document', '');
INSERT INTO doc VALUES (2, 'Mon second document', '');
INSERT INTO doc VALUES (3, 'Mon second document','');

-- Users Docs
-- doc1 : user2 et user3
INSERT INTO user_doc VALUES (1, 2, 1);
INSERT INTO user_doc VALUES (2, 3, 1);

-- doc2 : user2 (auteur)
INSERT INTO user_doc VALUES (3, 2, 2);

-- doc3 : user3 (lecteur) / user4 (validateur)
INSERT INTO user_doc VALUES (4, 3, 3);
INSERT INTO user_doc VALUES (5, 4, 3);
