-- Users
CREATE TABLE user (
        id INTEGER AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(30) NOT NULL,
        password VARCHAR(40) NOT NULL,
        firstname VARCHAR(40),
        lastname VARCHAR(40)
);

-- 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

);

-- Users (pass: 12345)
INSERT INTO user VALUES (1, 'admin', '12345','Robert','Dupont');
INSERT INTO user VALUES (2, 'user2', '12345','Gaston','Lagaffe');
INSERT INTO user VALUES (3, 'user3', '12345','Luky','Luke');
INSERT INTO user VALUES (4, 'user4', '12345','Jean','Martin');

-- 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);