CREATE TABLE MIGRATE_FORUMS(KEY_SITE INTEGER, KEY_FORUM INTEGER, NODE_ORDER INTEGER, MAX_NODE_ORDER INTEGER);

alter table MIGRATE_FORUMS add index(KEY_SITE), add index(KEY_FORUM);

insert into MIGRATE_FORUMS(KEY_SITE,KEY_FORUM,MAX_NODE_ORDER) 
select S.ID_INTERNAL, F.ID_INTERNAL, count(N.KEY_PARENT) FROM CONTENT S, CONTENT F, EXECUTION_COURSE E, NODE N WHERE  
F.OJB_CONCRETE_CLASS = "net.sourceforge.fenixedu.domain.messaging.ExecutionCourseForum" AND
S.OJB_CONCRETE_CLASS = "net.sourceforge.fenixedu.domain.ExecutionCourseSite" AND
F.KEY_FORUM_EXECUTION_COURSE=E.ID_INTERNAL AND S.KEY_SITE_EXECUTION_COURSE=E.ID_INTERNAL AND N.KEY_PARENT=S.ID_INTERNAL
GROUP BY N.KEY_PARENT;


CREATE TEMPORARY TABLE FORUMS_OFFSET(KEY_SITE INTEGER, KEY_FORUM INTEGER, OFFSET INTEGER);
alter table FORUMS_OFFSET add index(KEY_SITE), add index(KEY_FORUM);

insert into FORUMS_OFFSET(KEY_SITE,KEY_FORUM, OFFSET) 
SELECT MF1.KEY_SITE, MF1.KEY_FORUM, COUNT(MF1.KEY_SITE) FROM MIGRATE_FORUMS MF1, MIGRATE_FORUMS MF2 
WHERE MF1.KEY_SITE=MF2.KEY_SITE AND MF1.KEY_FORUM<=MF2.KEY_FORUM GROUP BY MF1.KEY_SITE;

update MIGRATE_FORUMS MF1, FORUMS_OFFSET OFFSET set MF1.NODE_ORDER=OFFSET.OFFSET WHERE MF1.KEY_SITE=OFFSET.KEY_SITE AND MF1.KEY_FORUM=OFFSET.KEY_FORUM;

insert into NODE(KEY_PARENT, KEY_CHILD, NODE_ORDER,VISIBLE,OJB_CONCRETE_CLASS, ASCENDING) 
SELECT KEY_SITE AS KEY_PARENT, KEY_FORUM AS KEY_CHILD, MAX_NODE_ORDER + NODE_ORDER, '1',
'net.sourceforge.fenixedu.domain.contents.ExplicitOrderNode','1' FROM MIGRATE_FORUMS;

DROP TABLE FORUMS_OFFSET;
DROP TABLE MIGRATE_FORUMS;

