CREATE SCHEMA IF NOT EXISTS rbac;
ALTER DATABASE "test" SET search_path TO "$user", rbac, public;
SET search_path TO "$user", rbac, public;

DROP TYPE IF EXISTS http_method;
CREATE TYPE http_method AS ENUM ('GET', 'POST', 'PUT', 'DELETE', 'PATCH');

DROP TABLE IF EXISTS rbac.pra CASCADE;
DROP TABLE IF EXISTS rbac.ura CASCADE;
DROP TABLE IF EXISTS rbac.inherits CASCADE;
DROP TABLE IF EXISTS rbac.role CASCADE;
DROP TABLE IF EXISTS rbac.usr CASCADE;
DROP TABLE IF EXISTS rbac.obj CASCADE;

CREATE TABLE IF NOT EXISTS rbac.obj(
  obj_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  obj_name text NOT NULL
);

CREATE TABLE IF NOT EXISTS rbac.usr(
  usr_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  usr_name text NOT NULL
);

CREATE TABLE IF NOT EXISTS rbac.role(
  role_id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  role_name text NOT NULL
);

CREATE TABLE IF NOT EXISTS rbac.inherits(
  role_id_child  integer REFERENCES rbac.role(role_id),
  role_id_parent integer REFERENCES rbac.role(role_id),
  PRIMARY KEY (role_id_child, role_id_parent)
);

CREATE TABLE IF NOT EXISTS rbac.ura(
  usr_id integer REFERENCES rbac.usr(usr_id),
  role_id integer REFERENCES rbac.role(role_id),
  PRIMARY KEY (usr_id, role_id)
);

CREATE TABLE IF NOT EXISTS rbac.pra(
  role_id integer REFERENCES rbac.role(role_id),
  obj_id integer REFERENCES rbac.obj(obj_id),
  act_id http_method,
  PRIMARY KEY (role_id, obj_id, act_id)
);
