DROP SCHEMA rbacs CASCADE;
CREATE SCHEMA rbacs;

CREATE TABLE rbacs.actions (
	id				SERIAL PRIMARY KEY,
	action			TEXT NOT NULL
);

CREATE TABLE rbacs.activities (
	id				SERIAL PRIMARY KEY,
	description		TEXT NOT NULL,
	is_public		BOOLEAN DEFAULT FALSE
);

CREATE TABLE rbacs.roles (
	id				SERIAL PRIMARY KEY,
	name			TEXT NOT NULL
);

CREATE TABLE rbacs.activities_to_actions (
	activity_id		INT REFERENCES rbacs.activities(id) ON DELETE CASCADE,
	action_id		INT REFERENCES rbacs.actions(id) ON DELETE CASCADE
);

CREATE TABLE rbacs.roles_to_activities (
	role_id			INT REFERENCES rbacs.roles(id) ON DELETE CASCADE,
	activity_id		INT REFERENCES rbacs.activities(id) ON DELETE CASCADE
);

CREATE TABLE rbacs.users_to_roles (
	user_id			INT NOT NULL,
	role_id			INT REFERENCES rbacs.roles(id) ON DELETE CASCADE
);

CREATE INDEX rbacs__actions_action_idx ON rbacs.actions(action);
CREATE INDEX rbacs__activities_is_public_idx ON rbacs.activities(is_public);
CREATE INDEX rbacs__ata_activity_id_idx ON rbacs.activities_to_actions(activity_id);
CREATE INDEX rbacs__ata_action_id_idx ON rbacs.activities_to_actions(action_id);
CREATE INDEX rbacs__rta_role_id_idx ON rbacs.roles_to_activities(role_id);
CREATE INDEX rbacs__rta_activity_id_idx ON rbacs.roles_to_activities(activity_id);
CREATE INDEX rbacs__utr_role_id_idx ON rbacs.users_to_roles(role_id);