BEGIN TRANSACTION;

CREATE SEQUENCE PERMISSION_SEQ START 1;

CREATE TABLE PERMISSION (
	ID INTEGER PRIMARY KEY DEFAULT NEXTVAL('PERMISSION_SEQ'),
	NAME VARCHAR(50) UNIQUE NOT NULL,
	DESCRIPTION VARCHAR(250)
);

CREATE INDEX PERMISSION_NAME_IDX ON PERMISSION (NAME);

CREATE SEQUENCE USERS_SEQ START 1;

CREATE TABLE USERS (
        ID INTEGER PRIMARY KEY DEFAULT NEXTVAL('USERS_SEQ'),
        LOGIN VARCHAR(25) UNIQUE NOT NULL,
        PASSWORD CHAR(64) NOT NULL,
        NAME VARCHAR(50) NOT NULL,
        SURNAME VARCHAR(100) NOT NULL,
        EMAIL VARCHAR(254)
);

CREATE INDEX USERS_LOGIN_IDX ON USERS (LOGIN);
CREATE INDEX USERS_PASSWORD_IDX ON USERS (PASSWORD);

CREATE SEQUENCE ROLE_SEQ START 1;

CREATE TABLE ROLE (
        ID INTEGER PRIMARY KEY DEFAULT NEXTVAL('ROLE_SEQ'),
        NAME VARCHAR(50) UNIQUE NOT NULL,
        DESCRIPTION VARCHAR(250)
);

CREATE INDEX ROLE_NAME_IDX ON ROLE (NAME);


CREATE SEQUENCE SESSION_SEQ START 1;

CREATE TABLE SESSION (
        ID INTEGER PRIMARY KEY DEFAULT NEXTVAL('SESSION_SEQ'),
        USER_ID INTEGER NOT NULL REFERENCES USERS(ID),
        TOKEN CHAR(36) NOT NULL UNIQUE,
        START_TIME TIMESTAMP NOT NULL,
        EXPIRATION_TIME TIMESTAMP NOT NULL,
        VALID BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE INDEX SESSION_USER_ID_IDX ON SESSION (USER_ID);
CREATE INDEX SESSION_TOKEN_IDX ON SESSION (TOKEN);

CREATE TABLE ROLE_PERMISSION (
	ROLE_ID INTEGER NOT NULL REFERENCES ROLE(ID),
	PERMISSION_ID INTEGER NOT NULL REFERENCES PERMISSION(ID),
	PRIMARY KEY(ROLE_ID, PERMISSION_ID)
);

CREATE INDEX ROLE_PERMISSION_ROLE_ID_IDX ON ROLE_PERMISSION (ROLE_ID);

CREATE TABLE USER_ROLE (
        USER_ID INTEGER NOT NULL REFERENCES USERS(ID),
        ROLE_ID INTEGER NOT NULL REFERENCES ROLE(ID),
        PRIMARY KEY(USER_ID, ROLE_ID)
);

CREATE INDEX USER_ROLE_USER_ID_IDX ON USER_ROLE (USER_ID);



COMMIT;