alter table CONTENT add column KEY_SITE_EXECUTION_COURSE INT(11);
alter table CONTENT add index (KEY_SITE_EXECUTION_COURSE);

insert into CONTENT (OJB_CONCRETE_CLASS, KEY_SITE_EXECUTION_COURSE, ALTERNATIVE_SITE, MAIL, INITIAL_STATEMENT, 
INTRODUCTION,STYLE,DYNAMIC_MAIL_DISTRIBUTION,LESSON_PLANNING_AVAILABLE, SITE_TYPE, KEY_PERSON, ACTIVATED, SHOW_UNIT,
SHOW_PHOTO, SHOW_EMAIL,SHOW_CATEGORY,SHOW_ALTERNATIVE_HOMEPAGE,SHOW_TELEPHONE,SHOW_WORK_TELEPHONE,SHOW_MOBILE_TELEPHONE,
SHOW_ACTIVE_STUDENT_CURRICULAR_PLANS,SHOW_ALUMNI_DEGREES,SHOW_CURRENT_EXECUTION_COURSES,SHOW_CURRENT_ATTENDING_EXECUTION_COURSES,
SHOW_RESEARCH_UNIT_HOMEPAGE,RESEARCH_UNIT,RESEARCH_UNIT_HOMEPAGE,SHOW_INTERESTS,SHOW_PATENTS,SHOW_PUBLICATIONS,KEY_DEGREE,
DESCRIPTION, KEY_UNIT, SHOW_PARTICIPATIONS, KEY_LOGO, PERSONALIZED_LOGO, SHOW_BANNER, SHOW_INTRODUCTION, SHOW_ANNOUNCEMENTS,
SHOW_EVENTS, LAYOUT, SIDE_BANNER, SHOW_FLAGS, SHOW_INSTITUTION_LOGO, KEY_MODULE, SHOW_PRIZES, GOOGLE_ANALYTICS_CODE,OLD_ID_INTERNAL) select 
OJB_CONCRETE_CLASS, KEY_EXECUTION_COURSE, ALTERNATIVE_SITE, MAIL, INITIAL_STATEMENT,
INTRODUCTION,STYLE,DYNAMIC_MAIL_DISTRIBUTION,LESSON_PLANNING_AVAILABLE, SITE_TYPE, KEY_PERSON, ACTIVATED, SHOW_UNIT,
SHOW_PHOTO, SHOW_EMAIL,SHOW_CATEGORY,SHOW_ALTERNATIVE_HOMEPAGE,SHOW_TELEPHONE,SHOW_WORK_TELEPHONE,SHOW_MOBILE_TELEPHONE,
SHOW_ACTIVE_STUDENT_CURRICULAR_PLANS,SHOW_ALUMNI_DEGREES,SHOW_CURRENT_EXECUTION_COURSES,SHOW_CURRENT_ATTENDING_EXECUTION_COURSES,
SHOW_RESEARCH_UNIT_HOMEPAGE,RESEARCH_UNIT,RESEARCH_UNIT_HOMEPAGE,SHOW_INTERESTS,SHOW_PATENTS,SHOW_PUBLICATIONS,KEY_DEGREE,
DESCRIPTION, KEY_UNIT, SHOW_PARTICIPATIONS, KEY_LOGO, PERSONALIZED_LOGO, SHOW_BANNER, SHOW_INTRODUCTION, SHOW_ANNOUNCEMENTS,
SHOW_EVENTS, LAYOUT, SIDE_BANNER, SHOW_FLAGS, SHOW_INSTITUTION_LOGO, KEY_MODULE, SHOW_PRIZES, GOOGLE_ANALYTICS_CODE, ID_INTERNAL FROM SITE;


update CONTENT C1, CONTENT C2, SITE S set C1.KEY_TEMPLATE=C2.ID_INTERNAL where C1.OJB_CONCRETE_CLASS IN (SELECT DISTINCT OJB_CONCRETE_CLASS FROM SITE)  AND C2.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.SiteTemplate' and S.ID_INTERNAL=C1.OLD_ID_INTERNAL AND S.KEY_TEMPLATE=C2.OLD_ID_INTERNAL;


update CONTENT C1, CONTENT C2, SITE S set C1.KEY_MODULE=C2.ID_INTERNAL WHERE C1.OJB_CONCRETE_CLASS IN (SELECT DISTINCT OJB_CONCRETE_CLASS FROM SITE) AND C2.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.functionalities.Module' and S.ID_INTERNAL=C1.OLD_ID_INTERNAL AND S.KEY_MODULE=C2.OLD_ID_INTERNAL;

insert into NODE(OJB_CONCRETE_CLASS, KEY_PARENT, KEY_CHILD, VISIBLE,ASCENDING,NODE_ORDER) select 'net.sourceforge.fenixedu.domain.contents.ExplicitOrderNode', C2.ID_INTERNAL, C.ID_INTERNAL, '1', '1', AI.SECTION_ORDER FROM CONTENT C, CONTENT C2, ACCESSIBLE_ITEM AI WHERE AI.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.Section' AND AI.KEY_SUPERIOR_SECTION IS NULL AND C.OLD_ID_INTERNAL=AI.ID_INTERNAL AND C.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.Section' AND AI.KEY_SITE=C2.OLD_ID_INTERNAL AND C2.OJB_CONCRETE_CLASS IN (SELECT DISTINCT OJB_CONCRETE_CLASS FROM SITE);

 update CONTENT C, (select C1.ID_INTERNAL,C1.WRITERS,REPLACE(C1.WRITERS,C2.OLD_ID_INTERNAL,C2.ID_INTERNAL) AS NEW_WRITERS FROM CONTENT C1, CONTENT C2 WHERE C1.WRITERS LIKE CONCAT('%unitSiteManagers%', C2.OLD_ID_INTERNAL,'%DepartmentSite%') AND C2.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.DepartmentSite') AS VIEW set C.WRITERS=VIEW.NEW_WRITERS WHERE VIEW.ID_INTERNAL=C.ID_INTERNAL;

 update CONTENT C, (select C1.ID_INTERNAL,C1.WRITERS,REPLACE(C1.WRITERS,C2.OLD_ID_INTERNAL,C2.ID_INTERNAL) AS NEW_WRITERS FROM CONTENT C1, CONTENT C2 WHERE C1.WRITERS LIKE CONCAT('%unitSiteManagers%', C2.OLD_ID_INTERNAL,'%ResearchUnitSite%') AND C2.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.ResearchUnitSite') AS VIEW set C.WRITERS=VIEW.NEW_WRITERS WHERE VIEW.ID_INTERNAL=C.ID_INTERNAL;

 update CONTENT C, (select C1.ID_INTERNAL,C1.WRITERS,REPLACE(C1.WRITERS,C2.OLD_ID_INTERNAL,C2.ID_INTERNAL) AS NEW_WRITERS FROM CONTENT C1, CONTENT C2 WHERE C1.WRITERS LIKE CONCAT('%unitSiteManagers%', C2.OLD_ID_INTERNAL,'%\'UnitSite\'%') AND C2.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.UnitSite') AS VIEW set C.WRITERS=VIEW.NEW_WRITERS WHERE VIEW.ID_INTERNAL=C.ID_INTERNAL;

 update CONTENT C, (select C1.ID_INTERNAL,C1.WRITERS,REPLACE(C1.WRITERS,C2.OLD_ID_INTERNAL,C2.ID_INTERNAL) AS NEW_WRITERS FROM CONTENT C1, CONTENT C2 WHERE C1.WRITERS LIKE CONCAT('%unitSiteManagers%', C2.OLD_ID_INTERNAL,'%PedagogicalCouncilSite%') AND C2.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.PedagogicalCouncilSite') AS VIEW set C.WRITERS=VIEW.NEW_WRITERS WHERE VIEW.ID_INTERNAL=C.ID_INTERNAL;

 update CONTENT C, (select C1.ID_INTERNAL,C1.WRITERS,REPLACE(C1.WRITERS,C2.OLD_ID_INTERNAL,C2.ID_INTERNAL) AS NEW_WRITERS FROM CONTENT C1, CONTENT C2 WHERE C1.WRITERS LIKE CONCAT('%unitSiteManagers%', C2.OLD_ID_INTERNAL,'%ScientificCouncilSite%') AND C2.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.ScientificCouncilSite') AS VIEW set C.WRITERS=VIEW.NEW_WRITERS WHERE VIEW.ID_INTERNAL=C.ID_INTERNAL;
 
update EXECUTION_COURSE EC, CONTENT C set EC.KEY_SITE=C.ID_INTERNAL WHERE C.KEY_SITE_EXECUTION_COURSE=EC.ID_INTERNAL;

 update PARTY P, CONTENT C set P.KEY_HOMEPAGE=C.ID_INTERNAL WHERE P.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.Person' AND P.KEY_HOMEPAGE IS NOT NULL AND C.OLD_ID_INTERNAL=P.KEY_HOMEPAGE AND C.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.homepage.Homepage';

 update PARTY P, CONTENT C  set P.KEY_SITE=C.ID_INTERNAL where P.KEY_SITE IS NOT NULL AND P.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.organizationalStructure.Unit' AND C.OLD_ID_INTERNAL=P.KEY_SITE AND C.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.InstitutionSite';

update DEGREE D, CONTENT C set D.KEY_SITE=C.ID_INTERNAL WHERE D.KEY_SITE IS NOT NULL AND D.KEY_SITE=C.OLD_ID_INTERNAL AND C.OJB_CONCRETE_CLASS='net.sourceforge.fenixedu.domain.DegreeSite';

-- Update Unit Site Banners to the new references.
update CONTENT C, UNIT_SITE_BANNER USB set USB.KEY_SITE=C.ID_INTERNAL WHERE C.OJB_CONCRETE_CLASS LIKE '%Site' AND C.OLD_ID_INTERNAL=USB.KEY_SITE;
