alter table COMMENT add column REVIEW_ID integer;
alter table COMMENT add column REPLY_TO_COMMENT_ID integer;

#set review id on review comments
update COMMENT c
    set c.REVIEW_ID = (select r.REVIEW_ID from REVIEW_COMMENT r where r.COMMENT_ID = c.COMMENT_ID)
    where c.REVIEW_ID is null;

#set review id on revision comments
update COMMENT c
    set c.REVIEW_ID = (select frx.REVIEW_ID from REVISION_COMMENT rc, FR_EXTRA frx where rc.comment = c.COMMENT_ID and rc.FRX_ID = frx.FRX_ID order by rc.RC_ID limit 1)
    where c.REVIEW_ID is null;

#set review id on replies
update COMMENT c
    set c.REVIEW_ID = (select cc.REVIEW_ID from COMMENT cc, REPLY r where r.REPLY_ID = c.COMMENT_ID and r.COMMENT_ID = cc.COMMENT_ID)
    where c.REVIEW_ID is null;

#set reply to comment id on replies
update COMMENT c
    set c.REPLY_TO_COMMENT_ID = (select r.COMMENT_ID from REPLY r where r.REPLY_ID = c.COMMENT_ID);

#clean up dud comments that *may* exist
#delete replies to replies that shouldn't exist
delete from REPLY
    where COMMENT_ID in
    (select r.COMMENT_ID from REPLY r, COMMENT c where c.REVIEW_ID is null and r.COMMENT_ID = c.COMMENT_ID);
#delete any remaining comments with no associated review that may exist
delete from COMMENT where REVIEW_ID is null;

alter table COMMENT alter column REVIEW_ID set not null;
alter table COMMENT add constraint FK63717A3F252D1475 foreign key (REPLY_TO_COMMENT_ID) references COMMENT;
alter table COMMENT add constraint FK63717A3FE71841CE foreign key (REVIEW_ID) references REVIEW;
# and finally update the db version
UPDATE CRUVERSION SET version = 13 WHERE version_id = 1;