create table "group" (
    id_group  BIGSERIAL PRIMARY KEY,
    groupname TEXT UNIQUE
);

create table privilege (
    id_privilege BIGSERIAL PRIMARY KEY,
    name         TEXT UNIQUE
);

create table group_privilege (
    id_group     BIGINT,
    id_privilege BIGINT,
    PRIMARY KEY (id_group, id_privilege),
    FOREIGN KEY (id_group)     REFERENCES "group" (id_group),
    FOREIGN KEY (id_privilege) REFERENCES "privilege" (id_privilege)
);

create table "user" (
    id_user         BIGSERIAL PRIMARY KEY,
    username        TEXT      UNIQUE NOT NULL,
    matrix_address  TEXT      UNIQUE NOT NULL,
    password_bcrypt TEXT             NOT NULL,
    is_confirmed    BOOLEAN          DEFAULT false,
    creation_date   TIMESTAMP        NOT NULL,
    id_group        BIGINT           NOT NULL,
    last_connection TIMESTAMP        NOT NULL,
    FOREIGN KEY (id_group) REFERENCES "group" (id_group)
);

CREATE INDEX index_user ON "user"            (username, matrix_address);
CREATE INDEX index_group ON "group"          (groupname);
CREATE INDEX index_privileges ON "privilege" (name);

INSERT INTO privilege       (name)      VALUES ( 'CREATE_USER' ), ( 'DELETE_USER' ), ( 'LIST_USERS' ), ( 'UPDATE_USERS' );
INSERT INTO "group"         (groupname) VALUES ( 'admin' );
INSERT INTO group_privilege (id_group, id_privilege) 
    SELECT 
        ( 
            SELECT id_group from "group" where groupname='admin' 
        ) as id_group,
        id_privilege
    FROM privilege
    WHERE name in ( 'create_user', 'delete_user', 'list_users', 'update_users' );