-- Create tables to store users, roles and the assignment of roles to users.

CREATE TABLE users (
    user_id UUID PRIMARY KEY NOT NULL,
    user_name TEXT NOT NULL UNIQUE,
    password_hash TEXT,
    created_at_utc TIMESTAMP NOT NULL,
    last_login_utc TIMESTAMP,
    locked BOOLEAN NOT NULL DEFAULT FALSE
);

COMMENT ON TABLE users IS 'All users known to the system.';

COMMENT ON COLUMN users.user_id IS 'A UUID given to the user. Stays with the user even after the user is renamed.';

COMMENT ON COLUMN users.user_name IS 'A textual user name for the user. Might be kept stable but some deployments may support renames.';

COMMENT ON COLUMN users.password_hash IS 'Hash of the user''s password. Null if the user does not have a password set.';

COMMENT ON COLUMN users.created_at_utc IS 'When the user was created, in UTC.';

COMMENT ON COLUMN users.last_login_utc IS 'When the user last logged in, in UTC.';

COMMENT ON COLUMN users.locked IS 'Whether the user has been locked.';

CREATE TABLE roles (
    role_id TEXT PRIMARY KEY NOT NULL,
    role_name TEXT NOT NULL
);

COMMENT ON TABLE roles IS 'A role is akin to a group or a permission. Users can be restricted depending on what roles they have.';

COMMENT ON COLUMN roles.role_id IS 'Unique code for the role. [a-zA-Z0-9_:-]+';

COMMENT ON COLUMN roles.role_name IS 'Human-friendly name for the role.';

CREATE TABLE users_roles (
    user_id UUID NOT NULL,
    role_id TEXT NOT NULL,

    granted_at_utc TIMESTAMP NOT NULL,

    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id)
);

COMMENT ON TABLE users_roles IS 'Association of users and their roles.';

COMMENT ON COLUMN users_roles.user_id IS 'User ID that has been assigned a role.';

COMMENT ON COLUMN users_roles.role_id IS 'Role ID that has been assigned to the user.';

COMMENT ON COLUMN users_roles.granted_at_utc IS 'When the role was assigned to the user.';