BEGIN;

CREATE TABLE dossiers (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(255) NOT NULL CHECK (LENGTH(titre) >= 3),
	isactiv CHAR(3) NOT NULL DEFAULT 'OUI' CHECK(activ = 'OUI' OR activ = 'NON'),
    descr TEXT NULL DEFAULT NULL
);

CREATE TABLE diapos (
    id SERIAL PRIMARY KEY,
    id_dossier INT NOT NULL REFERENCES dossiers(id),
    titre VARCHAR(255) NOT NULL CHECK (LENGTH(titre) >= 3),
    descr TEXT NULL DEFAULT NULL
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    nick VARCHAR(255) NOT NULL CHECK (LENGTH(nick) >= 3),
    pass VARCHAR(255) NOT NULL CHECK (LENGTH(pass) >= 3),
    isadmin CHAR(3) NOT NULL DEFAULT 'NON' CHECK(isadmin = 'OUI' OR isadmin = 'NON'),
	isactiv CHAR(3) NOT NULL DEFAULT 'OUI' CHECK(activ = 'OUI' OR activ = 'NON'),
    descr TEXT NULL DEFAULT NULL
);

CREATE TABLE users_dossiers (
    id SERIAL PRIMARY KEY,
    id_user INT NOT NULL REFERENCES users(id),
    id_dossier INT NOT NULL REFERENCES dossiers(id)
);

COMMIT;