CREATE GENERATOR USERS_ROLES_ID_SEQ;
SET GENERATOR USERS_ROLES_ID_SEQ TO 3;
DROP GENERATOR USERS_GROUPS_ID_SEQ;

CREATE TABLE LIBERTY_CONTENT_ROLE_MAP
(
  CONTENT_ID Integer NOT NULL,
  ROLE_ID Integer NOT NULL,
  PRIMARY KEY (CONTENT_ID,ROLE_ID)
);

COMMIT;

INSERT INTO LIBERTY_CONTENT_ROLE_MAP ( CONTENT_ID, ROLE_ID )
SELECT lg.CONTENT_ID, lg.GROUP_ID AS ROLE_ID FROM LIBERTY_CONTENT_GROUP_MAP lg;

DROP TABLE LIBERTY_CONTENT_GROUP_MAP;

ALTER TABLE LIBERTY_CONTENT_PERMISSIONS DROP CONSTRAINT LIBERTY_CONTENT_PERM_GROUP_REF;
ALTER TABLE LIBERTY_CONTENT_PERMISSIONS DROP CONSTRAINT INTEG_83;
ALTER TABLE LIBERTY_CONTENT_PERMISSIONS ALTER GROUP_ID TO ROLE_ID;
ALTER TABLE LIBERTY_CONTENT_PERMISSIONS
ADD CONSTRAINT PK_LIBERTY_CONTENT_PERMISSIONS
PRIMARY KEY (ROLE_ID, PERM_NAME, CONTENT_ID);

ALTER TABLE THEMES_LAYOUTS ALTER GROUPS TO ROLES;

CREATE TABLE USERS_ROLES
(
  ROLE_ID Integer NOT NULL,
  USER_ID Integer NOT NULL,
  ROLE_NAME Varchar(30),
  IS_DEFAULT Varchar(1),
  ROLE_DESC Varchar(255),
  ROLE_HOME Varchar(255),
  IS_PUBLIC Varchar(1),
  AFTER_REGISTRATION_PAGE Varchar(255),
  PRIMARY KEY (ROLE_ID)
);

CREATE TABLE USERS_ROLES_MAP
(
  USER_ID Integer NOT NULL,
  ROLE_ID Integer NOT NULL,
  PRIMARY KEY (USER_ID,ROLE_ID)
);

CREATE TABLE USERS_ROLE_PERMISSIONS
(
  ROLE_ID Integer NOT NULL,
  PERM_NAME Varchar(30) NOT NULL,
  PERM_VALUE Varchar(1) DEFAULT '',
  PRIMARY KEY (ROLE_ID,PERM_NAME)
);

ALTER TABLE USERS_USERS ALTER DEFAULT_GROUP_ID TO DEFAULT_ROLE_ID;

ALTER TABLE LIBERTY_CONTENT_ROLE_MAP ADD CONSTRAINT PROTECTOR_CONTENT_REF
  FOREIGN KEY (CONTENT_ID) REFERENCES LIBERTY_CONTENT (CONTENT_ID);
ALTER TABLE LIBERTY_CONTENT_ROLE_MAP ADD CONSTRAINT PROTECTOR_ROLE_REF
  FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
  
CREATE INDEX LIBERTY_CONTENT_PERM_ROLE_IDX ON LIBERTY_CONTENT_PERMISSIONS (ROLE_ID);

ALTER TABLE LIBERTY_CONTENT_PERMISSIONS ADD CONSTRAINT LIBERTY_CONTENT_PERM_ROLE_REF
  FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);

ALTER TABLE USERS_ROLES ADD CONSTRAINT USERS_ROLES_USER_REF
  FOREIGN KEY (USER_ID) REFERENCES USERS_USERS (USER_ID);
CREATE INDEX USERS_ROLES_USER_IDX ON USERS_ROLES (USER_ID);
CREATE UNIQUE INDEX USERS_ROLES_USER_NAME_IDX ON USERS_ROLES (USER_ID,ROLE_NAME);
ALTER TABLE USERS_ROLES_MAP ADD CONSTRAINT USERS_ROLES_MAP_ROLE_REF
  FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
ALTER TABLE USERS_ROLES_MAP ADD CONSTRAINT USERS_ROLES_MAP_USER_REF
  FOREIGN KEY (USER_ID) REFERENCES USERS_USERS (USER_ID);
CREATE INDEX USERS_ROLES_MAP_ROLE_IDX ON USERS_ROLES_MAP (ROLE_ID);
CREATE INDEX USERS_ROLES_MAP_USER_IDX ON USERS_ROLES_MAP (USER_ID);
ALTER TABLE USERS_ROLE_PERMISSIONS ADD CONSTRAINT USERS_ROLE_PERM_PERM_REF
  FOREIGN KEY (PERM_NAME) REFERENCES USERS_PERMISSIONS (PERM_NAME);
ALTER TABLE USERS_ROLE_PERMISSIONS ADD CONSTRAINT USERS_ROLE_PERM_ROLE_REF
  FOREIGN KEY (ROLE_ID) REFERENCES USERS_ROLES (ROLE_ID);
CREATE INDEX USERS_ROLE_PERM_PERM_IDX ON USERS_ROLE_PERMISSIONS (PERM_NAME);
CREATE INDEX USERS_ROLE_PERM_ROLE_IDX ON USERS_ROLE_PERMISSIONS (ROLE_ID);

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON LIBERTY_CONTENT_ROLE_MAP TO  SYSDBA WITH GRANT OPTION;
 
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON USERS_PERMISSIONS TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON USERS_ROLES TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON USERS_ROLES_MAP TO  SYSDBA WITH GRANT OPTION;

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON USERS_ROLE_PERMISSIONS TO  SYSDBA WITH GRANT OPTION;

COMMIT;

INSERT INTO USERS_ROLES
SELECT * FROM USERS_GROUPS;

INSERT INTO USERS_ROLES_MAP
SELECT * FROM USERS_GROUPS_MAP;

INSERT INTO USERS_ROLE_PERMISSIONS
SELECT * FROM USERS_GROUP_PERMISSIONS;

DROP TABLE USERS_GROUP_PERMISSIONS;
DROP TABLE USERS_GROUPS_MAP;
DROP TABLE USERS_GROUPS;
DROP INDEX LIBERTY_CONTENT_PERM_GROUP_IDX;

INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_assign_role_perms', 'Can assign permissions to personal roles', 'editors', 'users');
INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_assign_role_members', 'Can assign users to personal roles', 'registered', 'users');
INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_role_subroles', 'Can include other roles in roles', 'editors', 'users');
INSERT INTO USERS_PERMISSIONS (PERM_NAME, PERM_DESC, PERM_LEVEL, PACKAGE) VALUES ('p_users_create_personal_roles', 'Can create personal user roles', 'editors', 'users');

UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_members' WHERE PERM_NAME = 'p_users_assign_group_members';
UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_perms' WHERE PERM_NAME = 'p_users_assign_group_perms';
UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_role_subroles' WHERE PERM_NAME = 'p_users_group_subgroups';
UPDATE USERS_ROLE_PERMISSIONS SET PERM_NAME = 'p_users_create_personal_roles' WHERE PERM_NAME = 'p_users_create_personal_groups';
UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_members' WHERE PERM_NAME = 'p_users_assign_group_members';
UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_assign_role_perms' WHERE PERM_NAME = 'p_users_assign_group_perms';
UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_role_subroles' WHERE PERM_NAME = 'p_users_group_subgroups';
UPDATE LIBERTY_CONTENT_PERMISSIONS SET PERM_NAME = 'p_users_create_personal_roles' WHERE PERM_NAME = 'p_users_create_personal_groups';

DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_assign_group_members';
DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_assign_group_perms';
DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_group_subgroups';
DELETE FROM USERS_PERMISSIONS WHERE PERM_NAME = 'p_users_create_personal_groups';

UPDATE KERNEL_CONFIG SET 
CONFIG_NAME = 'protector_single_role', 
PACKAGE = 'protector', 
CONFIG_VALUE = 'y'
WHERE CONFIG_NAME = 'protector_single_group';