CREATE TABLE PERMISSION (
	ID                  INTEGER DEFAULT 0,
	PARAMS              VARCHAR(256),
	DESCRIPTION         VARCHAR(128),
	NAME                VARCHAR(128)
);

CREATE TABLE ROLES (
	ID                  INTEGER DEFAULT 0,
	DESCRIPTION         VARCHAR(128),
	NAME                VARCHAR(128)
);

CREATE TABLE ROLE_PERMISSION (
	ROLE_ID             INTEGER,
	PERMISSION_ID       INTEGER
);


CREATE TABLE USERS (
	ID                  INTEGER Not Null,
	PASS                VARCHAR(128),
	VALIDATED           SMALLINT,
	DESCRIPTION         VARCHAR(128),
	EMAIL               VARCHAR(128),
	NAME                VARCHAR(128)
);

CREATE TABLE USER_ROLE (
	USER_ID             INTEGER,
	ROLE_ID             INTEGER
);

CREATE UNIQUE INDEX PK_PERMISSION ON PERMISSION(ID);
CREATE UNIQUE INDEX PK_ROLES ON ROLES(ID);
CREATE UNIQUE INDEX PK_USERS ON USERS(ID);

CREATE GENERATOR GEN_PERMISSION;
CREATE GENERATOR GEN_ROLES;
CREATE GENERATOR GEN_USERS;

SET TERM ; ;
COMMIT WORK;

/* procedures */

SET AUTODDL OFF;
SET TERM ^ ;

CREATE PROCEDURE ROLE_ADD (
	NAME VARCHAR(128),
	DESCRIPTION VARCHAR(128)
) RETURNS (
	ID INTEGER
) AS
begin
  insert into "ROLES"
    (DESCRIPTION, NAME)
  values
    (:DESCRIPTION, :NAME);
  select max(ID) from "ROLES" into :ID;
end

^

CREATE PROCEDURE USER_ADD (
	NAME VARCHAR(128),
	PASS VARCHAR(128),
	DESCRIPTION VARCHAR(128),
	EMAIL VARCHAR(128),
	VALIDATED SMALLINT
) RETURNS (
	ID INTEGER
) AS
begin
  insert into "USERS"
    (PASS, VALIDATED, DESCRIPTION, EMAIL, NAME)
  values
    (:PASS, :VALIDATED, :DESCRIPTION, :EMAIL, :NAME);
  select max(ID) from "USERS" into :ID;
end

^

COMMIT WORK^

SET AUTODDL ON^

CREATE TRIGGER SET_PERMISSIONID FOR PERMISSION ACTIVE BEFORE INSERT AS
begin
NEW.ID = gen_id(GEN_PERMISSION, 1);
end
^

CREATE TRIGGER SET_USERSID FOR USERS ACTIVE BEFORE INSERT AS
begin
NEW.ID = gen_id(GEN_USERS, 1);
end
^

SET TERM ;^
COMMIT WORK;