ALTER TABLE T_Edit ADD ZoneType nvarchar2(1) NULL;
COMMIT;

UPDATE T_Edit set ZoneType='E' where ZoneType is null;
COMMIT;

Alter TABLE T_Edit add UpdateDate DATE;
COMMIT;

UPDATE T_Edit set UpdateDate = sysdate;
COMMIT;

ALTER TABLE T_Participant ADD ReviewZoneRole NVARCHAR2(128) null;
COMMIT;

CREATE TABLE T_EditRole(
	ObjectId int NOT NULL,
	EditId int NOT NULL,
	SubjectId int NOT NULL,
	ReviewZoneRole nvarchar2(128) NULL
);
COMMIT;

CREATE UNIQUE INDEX IX_EditRole ON T_EditRole (ObjectId);
COMMIT;

CREATE INDEX IX_EditRole2 ON T_EditRole (EditId);
COMMIT;

CREATE INDEX IX_EditRole3 ON T_EditRole (SubjectId);
COMMIT;

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction, PolicyObject)
values ('59','SYSTEM','ALLOWINLINEEDITING','PERMIT','SYSTEM');

INSERT INTO T_SystemPolicy (ObjectId,Principal,Permission,PolicyAction, PolicyObject)
values ('60','SYSTEM','REVIEWFAILEDDOCUMENT','DENY','SYSTEM');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags)
values ('28', 'ReviewZone', 'Hidden', ';');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags)
values ('29', 'ReviewZone', 'Viewer', ';DOREVIEW;');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags)
values ('30', 'ReviewZone', 'Reviewer', ';DOREVIEW;COMMENT;');

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags)
values ('31', 'ReviewZone', 'Author', ';DOREVIEW;COMMENT;AUTHOR;');

COMMIT;

ALTER TABLE T_Document ADD DocSequence INT DEFAULT 0 NOT NULL;
COMMIT;

------------------------------------------------------------------------------------------------
--BATCH SCRIPT - Resetting Sequences
------------------------------------------------------------------------------------------------

create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val || 
                                                          ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/
------------------------------------------------------------------------------------------------
--BATCH SCRIPT - Doc Sequence update
------------------------------------------------------------------------------------------------

VARIABLE TotalReviewIdAffectedRow NUMBER;

-- Creating temporary tables
CREATE TABLE AffectedReviewId (IdentityId INT NOT NULL PRIMARY KEY, ReviewId INT);
COMMIT;

CREATE TABLE AffectedObjectId (IdentityId INT NOT NULL PRIMARY KEY, ObjectId INT);
COMMIT;

-- Creating Sequences	
CREATE SEQUENCE seq_AffectedReviewId;
COMMIT;

CREATE SEQUENCE seq_AffectedObjectId;
COMMIT;

-- Creating Trigger for auto increment values
CREATE TRIGGER trg_AffectedReviewId
before insert on AffectedReviewId
for each row
begin
  select seq_AffectedReviewId.nextval into :new.IdentityId from dual;
end;
/
COMMIT;

CREATE TRIGGER trg_AffectedObjectId
before insert on AffectedObjectId
for each row
begin
  select seq_AffectedObjectId.nextval into :new.IdentityId from dual;
end;
/
COMMIT;

EXECUTE reset_seq('seq_AffectedReviewId');

INSERT INTO AffectedReviewId (ReviewId)
SELECT DISTINCT(ReviewId) FROM T_Document WHERE DocSequence = 0 ORDER BY ReviewId ASC;
COMMIT;

EXECUTE SELECT COUNT(1) INTO :TotalReviewIdAffectedRow FROM AffectedReviewId;
COMMIT;

SET SERVEROUTPUT ON

DECLARE
    TotalObjectIdAffectedRow NUMBER := 1;
	CurrentReviewIdRow NUMBER := 1;
	CurrentObjectIdRow NUMBER := 1;
	ReviewIdValue NUMBER := 0;
	ObjectIdValue NUMBER := 0;
BEGIN
    
    WHILE CurrentReviewIdRow <= :TotalReviewIdAffectedRow LOOP            
        -- Get each ReviewId, and then process them
    	SELECT ReviewId INTO ReviewIdValue FROM AffectedReviewId WHERE IdentityId = CurrentReviewIdRow;
    	
        DELETE FROM AffectedObjectId;
        COMMIT;
        
        reset_seq('seq_AffectedObjectId');
        COMMIT;
        
        INSERT INTO AffectedObjectId(ObjectId) SELECT ObjectId FROM T_Document WHERE ReviewId = ReviewIdValue;
    	COMMIT;
        
        SELECT COUNT(1) INTO TotalObjectIdAffectedRow FROM AffectedObjectId;
        COMMIT;
        
        CurrentObjectIdRow := 1;
        WHILE CurrentObjectIdRow <= TotalObjectIdAffectedRow LOOP
            SELECT ObjectId INTO ObjectIdValue FROM AffectedObjectId WHERE IdentityId = CurrentObjectIdRow;
            UPDATE T_Document SET DocSequence = CurrentObjectIdRow WHERE ObjectId = ObjectIdValue;
            CurrentObjectIdRow := CurrentObjectIdRow + 1;
        END LOOP;
         
    	CurrentReviewIdRow := CurrentReviewIdRow + 1;
    END LOOP;
END;
/
COMMIT;

DROP SEQUENCE seq_AffectedReviewId;
COMMIT;

DROP TRIGGER trg_AffectedReviewId;
COMMIT;

DROP TABLE AffectedReviewId;
COMMIT;

DROP SEQUENCE seq_AffectedObjectId;
COMMIT;

DROP TRIGGER trg_AffectedObjectId;
COMMIT;

DROP TABLE AffectedObjectId;
COMMIT;


--SuperContributor

INSERT INTO T_PermissionSet (ObjectId, PermissionType, Title, PermissionFlags )
VALUES (32,'Review','Super-Contributor',
';DOREVIEW;COMMENT;AUTHOR;CONTRIBUTE;DEFINEEDITZONE;');
COMMIT;

UPDATE T_PermissionSet set PermissionFlags=';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;DEFINEEDITZONE;'
WHERE PermissionType='Review' and Title='Owner-Contributor'and ObjectId=7;
COMMIT;

UPDATE T_PermissionSet set PermissionFlags=';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;DEFINEEDITZONE;'
WHERE PermissionType='Workgroup' and Title='Author-Contributor'and ObjectId=11;
COMMIT;

UPDATE T_PermissionSet set PermissionFlags=';WORKGROUPADMIN;DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;DEFINEEDITZONE;'
WHERE PermissionType='Workgroup' and Title='Admin-Author-Contributor'and ObjectId=16;
COMMIT;

UPDATE T_PermissionSet set PermissionFlags=';DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;MONITOR;DEFINEEDITZONE;'
WHERE PermissionType='Workgroup' and Title='Author-Contributor-Monitor'and ObjectId=21;
COMMIT;

UPDATE T_PermissionSet set PermissionFlags=';WORKGROUPADMIN;DOREVIEW;COMMENT;AUTHOR;CREATEREVIEW;CONTRIBUTE;MONITOR;DEFINEEDITZONE;'
WHERE PermissionType='Workgroup' and Title='Admin-Author-Contributor-Monitor' and ObjectId=26;
COMMIT;
