
--------------------------------------------------
-- 07/17/2019
-- Author: Jennifer Zhuang, Tony Chen 
-- Following changes are made for 'Administration' functions. 
--------------------------------------------------

--------------------------------------------------
-- Create table 'roles'
--------------------------------------------------
CREATE TABLE roles 
(
	`ID` 			BIGINT AUTO_INCREMENT, 
    `NAME`			VARCHAR(255) NOT NULL,
    `ABBREVIATION` 	VARCHAR(16) NULL,
    
    CONSTRAINT pk_roles_01 PRIMARY KEY (ID),
    CONSTRAINT uq_roles_01 UNIQUE (`NAME`)
); 

INSERT INTO roles (`NAME`, `ABBREVIATION`) VALUES 
	('ADMINISTRATOR', 'ADMIN'),
    ('STANDARD', 'STD'),
    ('REST_USER', 'REST_USR'),
    ('REST_END_USER_UMBRELLA', 'REST_USR_UMBR'),
    ('TEACHER', 'TEACHER'),
    ('GUEST', 'GUEST')
;


--------------------------------------------------
-- Create table 'users_roles'
--------------------------------------------------
CREATE TABLE users_roles 
(
	`ID`			BIGINT AUTO_INCREMENT,
    `USER_ID`		BIGINT NOT NULL,
    `ROLE_ID`		BIGINT NOT NULL,
    
    CONSTRAINT pk_users_roles_01 PRIMARY KEY (ID),
    CONSTRAINT uq_users_roles_01 UNIQUE (`USER_ID`, `ROLE_ID`),
    
    CONSTRAINT fk_users_roles_01 FOREIGN KEY (`USER_ID`) REFERENCES users (`USER_ID`)
		ON UPDATE NO ACTION 	ON DELETE NO ACTION,
        
	CONSTRAINT fk_users_roles_02 FOREIGN KEY (`ROLE_ID`) REFERENCES roles (`ID`)
		ON UPDATE NO ACTION 	ON DELETE NO ACTION 
);
