begin transaction;

create table newsgroup (
    id          SERIAL PRIMARY KEY,
    created_on  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by  TEXT NOT NULL,
    name        TEXT NOT NULL,
    description TEXT NOT NULL,
    writable    BOOLEAN NOT NULL DEFAULT FALSE
);

create table message (
    id            SERIAL PRIMARY KEY,
    created_on    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    message_id    TEXT NOT NULL UNIQUE,
    reference_ids TEXT,
    sender        TEXT NOT NULL,
    subject       TEXT NOT NULL,
    content       TEXT NOT NULL
);

create index message_created_on_idx on message (
    created_on
);

create table newsgroup_message (
    newsgroup_id INTEGER NOT NULL,
    message_id   INTEGER NOT NULL,

    FOREIGN KEY(newsgroup_id) REFERENCES newsgroup(id),
    FOREIGN KEY(message_id)   REFERENCES message(id)
);

create unique index newsgroup_message_newsgroup_id_idx on newsgroup_message (
    newsgroup_id, message_id
);

create table server_permission (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

insert into server_permission values
    (1, 'READ'),
    (2, 'POST'),
    (3, 'ADMIN');

create table server_user (
    id       SERIAL PRIMARY KEY,
    active   BOOLEAN NOT NULL DEFAULT TRUE,
    username TEXT NOT NULL,
    password TEXT,
    fullname TEXT,
    mail     TEXT NOT NULL
);

create table server_user_permission (
    permission_id INTEGER NOT NULL,
    user_id       INTEGER NOT NULL,

    UNIQUE(permission_id, user_id),
    FOREIGN KEY(permission_id) REFERENCES server_permission(id),
    FOREIGN KEY(user_id)       REFERENCES server_user(id)
);

commit;