CREATE TABLE `LOGIN_ALIAS` (
  `ID_INTERNAL` int(11) unsigned NOT NULL auto_increment,
  `ALIAS` varchar(50) NOT NULL default '',  
  `KEY_LOGIN` int(11) unsigned NOT NULL default '0', 
  `TYPE` varchar(50) NOT NULL default '',
  `ROLE_TYPE`  varchar(50) default NULL,
  `KEY_ROOT_DOMAIN_OBJECT` int(11) NOT NULL default '1',
  PRIMARY KEY  (`ID_INTERNAL`),
  KEY `KEY_LOGIN` (`KEY_LOGIN`),
  KEY `KEY_ROOT_DOMAIN_OBJECT` (`KEY_ROOT_DOMAIN_OBJECT`)
) ENGINE=InnoDB;

alter table LOGIN_ALIAS add index (ALIAS);
alter table IDENTIFICATION add index (USERNAME);

-- Insert Institutional Alias
insert into LOGIN_ALIAS
    select null, USER.USER_U_ID, IDENTIFICATION.ID_INTERNAL, 'INSTITUTION_ALIAS', null, 1
    from IDENTIFICATION, USER
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
	    and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and USER.USER_U_ID is not null;

-- Insert Person Aliasses
insert into LOGIN_ALIAS
    select null, concat('P', PARTY.ID_INTERNAL), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'PERSON', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'PERSON';

-- Insert Teacher Aliasses
insert into LOGIN_ALIAS
    select null, concat('D', TEACHER.TEACHER_NUMBER), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'TEACHER', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE, TEACHER
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and TEACHER.KEY_PERSON = PARTY.ID_INTERNAL
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'TEACHER';

-- Insert Employee Aliasses
insert into LOGIN_ALIAS
    select null, concat('F', ass_FUNCIONARIO.EMPLOYEE_NUMBER), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'EMPLOYEE', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE, ass_FUNCIONARIO
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and ass_FUNCIONARIO.KEY_PERSON = PARTY.ID_INTERNAL
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'EMPLOYEE';


-- Insert GrantOwner Aliasses
insert into LOGIN_ALIAS
    select null, concat('B', GRANT_OWNER.NUMBER), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'GRANT_OWNER', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE, GRANT_OWNER
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and GRANT_OWNER.KEY_PERSON = PARTY.ID_INTERNAL
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'GRANT_OWNER';

-- Insert ProjectManager Aliasses
insert into LOGIN_ALIAS
    select null, concat('G', PARTY.ID_INTERNAL), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'PROJECTS_MANAGER', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'PROJECTS_MANAGER';
insert into LOGIN_ALIAS
    select null, concat('G', PARTY.ID_INTERNAL), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'INSTITUCIONAL_PROJECTS_MANAGER', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'INSTITUCIONAL_PROJECTS_MANAGER';

-- Insert Candidate Aliasses
insert into LOGIN_ALIAS
    select null, concat('C', PARTY.ID_INTERNAL), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'CANDIDATE', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'CANDIDATE';
insert into LOGIN_ALIAS
    select null, concat('C', PARTY.ID_INTERNAL), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'MASTER_DEGREE_CANDIDATE', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'MASTER_DEGREE_CANDIDATE';

-- Insert Alumni Aliasses
insert into LOGIN_ALIAS
    select null, concat('L', STUDENT.NUMBER), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'ALUMNI', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE, STUDENT, REGISTRATION, STUDENT_CURRICULAR_PLAN, DEGREE_CURRICULAR_PLAN, DEGREE
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and STUDENT.KEY_PERSON = PARTY.ID_INTERNAL
        and REGISTRATION.KEY_STUDENT = STUDENT.ID_INTERNAL
        and STUDENT_CURRICULAR_PLAN.KEY_STUDENT = REGISTRATION.ID_INTERNAL
        and DEGREE_CURRICULAR_PLAN.ID_INTERNAL = STUDENT_CURRICULAR_PLAN.KEY_DEGREE_CURRICULAR_PLAN
        and DEGREE.ID_INTERNAL = DEGREE_CURRICULAR_PLAN.KEY_DEGREE
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'ALUMNI'
        and DEGREE.TIPO_CURSO = 'DEGREE'
        and STUDENT_CURRICULAR_PLAN.CURRENT_STATE = 'CONCLUDED';

-- Insert STUDENT Aliasses
insert into LOGIN_ALIAS
    select null, concat('I', STUDENT.NUMBER), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'STUDENT', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE, STUDENT, REGISTRATION, STUDENT_CURRICULAR_PLAN, DEGREE_CURRICULAR_PLAN, DEGREE, STUDENT_KIND
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and STUDENT.KEY_PERSON = PARTY.ID_INTERNAL
        and REGISTRATION.KEY_STUDENT = STUDENT.ID_INTERNAL
        and STUDENT_CURRICULAR_PLAN.KEY_STUDENT = REGISTRATION.ID_INTERNAL
        and DEGREE_CURRICULAR_PLAN.ID_INTERNAL = STUDENT_CURRICULAR_PLAN.KEY_DEGREE_CURRICULAR_PLAN
        and DEGREE.ID_INTERNAL = DEGREE_CURRICULAR_PLAN.KEY_DEGREE
        and STUDENT_KIND.ID_INTERNAL = REGISTRATION.KEY_STUDENT_KIND
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'STUDENT'
        and STUDENT_CURRICULAR_PLAN.CURRENT_STATE = 'ACTIVE'
        and STUDENT_KIND.STUDENT_TYPE = 3;
insert into LOGIN_ALIAS
    select null, concat('A', STUDENT.NUMBER), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'STUDENT', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE, STUDENT, REGISTRATION, STUDENT_CURRICULAR_PLAN, DEGREE_CURRICULAR_PLAN, DEGREE, STUDENT_KIND
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and STUDENT.KEY_PERSON = PARTY.ID_INTERNAL
        and REGISTRATION.KEY_STUDENT = STUDENT.ID_INTERNAL
        and STUDENT_CURRICULAR_PLAN.KEY_STUDENT = REGISTRATION.ID_INTERNAL
        and DEGREE_CURRICULAR_PLAN.ID_INTERNAL = STUDENT_CURRICULAR_PLAN.KEY_DEGREE_CURRICULAR_PLAN
        and DEGREE.ID_INTERNAL = DEGREE_CURRICULAR_PLAN.KEY_DEGREE
        and STUDENT_KIND.ID_INTERNAL = REGISTRATION.KEY_STUDENT_KIND
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'STUDENT'
        and STUDENT_CURRICULAR_PLAN.CURRENT_STATE = 'ACTIVE'
        and STUDENT_KIND.STUDENT_TYPE = 4;
insert into LOGIN_ALIAS
    select null, concat('T', STUDENT.NUMBER), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'STUDENT', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE, STUDENT, REGISTRATION, STUDENT_CURRICULAR_PLAN, DEGREE_CURRICULAR_PLAN, DEGREE, STUDENT_KIND
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and STUDENT.KEY_PERSON = PARTY.ID_INTERNAL
        and REGISTRATION.KEY_STUDENT = STUDENT.ID_INTERNAL
        and STUDENT_CURRICULAR_PLAN.KEY_STUDENT = REGISTRATION.ID_INTERNAL
        and DEGREE_CURRICULAR_PLAN.ID_INTERNAL = STUDENT_CURRICULAR_PLAN.KEY_DEGREE_CURRICULAR_PLAN
        and DEGREE.ID_INTERNAL = DEGREE_CURRICULAR_PLAN.KEY_DEGREE
        and STUDENT_KIND.ID_INTERNAL = REGISTRATION.KEY_STUDENT_KIND
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'STUDENT'
        and STUDENT_CURRICULAR_PLAN.CURRENT_STATE = 'ACTIVE'
        and STUDENT_KIND.STUDENT_TYPE = 1
        and DEGREE.TIPO_CURSO = 'BOLONHA_ADVANCED_FORMATION_DIPLOMA';
insert into LOGIN_ALIAS
    select null, concat('M', STUDENT.NUMBER), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'STUDENT', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE, STUDENT, REGISTRATION, STUDENT_CURRICULAR_PLAN, DEGREE_CURRICULAR_PLAN, DEGREE, STUDENT_KIND
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and STUDENT.KEY_PERSON = PARTY.ID_INTERNAL
        and REGISTRATION.KEY_STUDENT = STUDENT.ID_INTERNAL
        and STUDENT_CURRICULAR_PLAN.KEY_STUDENT = REGISTRATION.ID_INTERNAL
        and DEGREE_CURRICULAR_PLAN.ID_INTERNAL = STUDENT_CURRICULAR_PLAN.KEY_DEGREE_CURRICULAR_PLAN
        and DEGREE.ID_INTERNAL = DEGREE_CURRICULAR_PLAN.KEY_DEGREE
        and STUDENT_KIND.ID_INTERNAL = REGISTRATION.KEY_STUDENT_KIND
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'STUDENT'
        and STUDENT_CURRICULAR_PLAN.CURRENT_STATE = 'ACTIVE'
        and STUDENT_KIND.STUDENT_TYPE = 1
        and DEGREE.TIPO_CURSO = 'MASTER_DEGREE';
insert into LOGIN_ALIAS
    select null, concat('L', STUDENT.NUMBER), IDENTIFICATION.ID_INTERNAL, 'ROLE_TYPE_ALIAS', 'STUDENT', 1
    from IDENTIFICATION, USER, PARTY, PERSON_ROLE, ROLE, STUDENT, REGISTRATION, STUDENT_CURRICULAR_PLAN, DEGREE_CURRICULAR_PLAN, DEGREE, STUDENT_KIND
    where IDENTIFICATION.KEY_USER = USER.ID_INTERNAL
        and USER.KEY_PERSON = PARTY.ID_INTERNAL
        and PERSON_ROLE.KEY_PERSON = PARTY.ID_INTERNAL
        and ROLE.ID_INTERNAL = PERSON_ROLE.KEY_ROLE
        and STUDENT.KEY_PERSON = PARTY.ID_INTERNAL
        and REGISTRATION.KEY_STUDENT = STUDENT.ID_INTERNAL
        and STUDENT_CURRICULAR_PLAN.KEY_STUDENT = REGISTRATION.ID_INTERNAL
        and DEGREE_CURRICULAR_PLAN.ID_INTERNAL = STUDENT_CURRICULAR_PLAN.KEY_DEGREE_CURRICULAR_PLAN
        and DEGREE.ID_INTERNAL = DEGREE_CURRICULAR_PLAN.KEY_DEGREE
        and STUDENT_KIND.ID_INTERNAL = REGISTRATION.KEY_STUDENT_KIND
        and IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and ROLE.ROLE_TYPE = 'STUDENT'
        and STUDENT_CURRICULAR_PLAN.CURRENT_STATE = 'ACTIVE'
        and STUDENT_KIND.STUDENT_TYPE = 1
        and DEGREE.TIPO_CURSO in ('DEGREE', 'BOLONHA_DEGREE', 'BOLONHA_INTEGRATED_MASTER_DEGREE');

-- Insert Custome Alias
insert into LOGIN_ALIAS
    select null, IDENTIFICATION.USERNAME, IDENTIFICATION.ID_INTERNAL, 'CUSTOM_ALIAS', null, 1
    from IDENTIFICATION left join LOGIN_ALIAS on LOGIN_ALIAS.ALIAS = IDENTIFICATION.USERNAME
    where IDENTIFICATION.OJB_CONCRETE_CLASS like '%Login'
        and IDENTIFICATION.USERNAME is not null
        and LOGIN_ALIAS.ALIAS is null;

alter table LOGIN_ALIAS drop key ALIAS;
alter table IDENTIFICATION drop key USERNAME;
        