create table CRUPROJECT (PROJECT_ID integer not null, name varchar(255), key varchar(255), defaultRepositoryName varchar(255), allowReviewersToJoin bit, permissionScheme integer, defaultModerator integer, primary key (PROJECT_ID));
create table CRUPROJECT_ALWDREV (PROJECT_ID integer not null, USER_ID integer not null, primary key (PROJECT_ID, USER_ID));
create table CRUPROJECT_ALWDREVGRP (PROJECT_ID integer not null, GROUP_NAME varchar(255));
create table CRUPROJECT_DEFREV (PROJECT_ID integer not null, USER_ID integer not null, primary key (PROJECT_ID, USER_ID));
create table CRUPROJECT_DEFREVGRP (PROJECT_ID integer not null, GROUP_NAME varchar(255));

create table PERM_SCHEME (PS_ID integer not null, name varchar(255), primary key (PS_ID), unique (name));
create table PS_ALL_USER (ps integer not null, actionName varchar(255) not null, primary key (ps, actionName));
create table PS_ANON (ps integer not null, actionName varchar(255) not null, primary key (ps, actionName));
create table PS_GROUP (ps integer not null, pid varchar(255) not null, actionName varchar(255) not null, primary key (ps, pid, actionName));
create table PS_REVIEW_ROLE (ps integer not null, pid varchar(255) not null, actionName varchar(255) not null, primary key (ps, pid, actionName));
create table PS_USER (ps integer not null, pid varchar(255) not null, actionName varchar(255) not null, primary key (ps, pid, actionName));

#Add projects to reviews
alter table REVIEW add COLUMN project integer;

create table REVPERMAID (key varchar(255) not null, number integer not null, REVIEW_ID integer, primary key (key, number));

#seed default permission scheme
INSERT INTO PERM_SCHEME VALUES(1,'default');
INSERT INTO PS_ALL_USER VALUES(1,'action:createReview');
INSERT INTO PS_ALL_USER VALUES(1,'action:viewReview');
INSERT INTO PS_ANON VALUES(1,'action:viewReview');
INSERT INTO PS_REVIEW_ROLE VALUES(1,'Creator','action:abandonReview');
INSERT INTO PS_REVIEW_ROLE VALUES(1,'Moderator','action:abandonReview');
INSERT INTO PS_REVIEW_ROLE VALUES(1,'Moderator','action:approveReview');
INSERT INTO PS_REVIEW_ROLE VALUES(1,'Moderator','action:closeReview');
INSERT INTO PS_REVIEW_ROLE VALUES(1,'Moderator','action:rejectReview');
INSERT INTO PS_REVIEW_ROLE VALUES(1,'Moderator','action:reopenReview');
INSERT INTO PS_REVIEW_ROLE VALUES(1,'Moderator','action:summarizeReview');
INSERT INTO PS_REVIEW_ROLE VALUES(1,'Reviewer','action:completeReview');
#Create default project
INSERT INTO CRUPROJECT VALUES(1,'Default Project','CR',NULL,FALSE,1,NULL);

INSERT INTO REVPERMAID (key, number, REVIEW_ID) select 'CR', REVIEW_ID, REVIEW_ID from REVIEW;
UPDATE REVIEW set project = 1;

create index proj_name_idx on CRUPROJECT (name);
create index proj_key_idx on CRUPROJECT (key);
alter table CRUPROJECT add constraint FK343E6733C0C9A9C foreign key (permissionScheme) references PERM_SCHEME;
alter table CRUPROJECT add constraint FK343E6739DDC2572 foreign key (defaultModerator) references USER;
alter table CRUPROJECT_ALWDREV add constraint FKA85AE77FA384A286 foreign key (PROJECT_ID) references CRUPROJECT;
alter table CRUPROJECT_ALWDREV add constraint FKA85AE77F1811CB9D foreign key (USER_ID) references USER;
alter table CRUPROJECT_ALWDREVGRP add constraint FKAAA288E6A384A286 foreign key (PROJECT_ID) references CRUPROJECT;
alter table CRUPROJECT_DEFREV add constraint FK6D3B6DCAA384A286 foreign key (PROJECT_ID) references CRUPROJECT;
alter table CRUPROJECT_DEFREV add constraint FK6D3B6DCA1811CB9D foreign key (USER_ID) references USER;
alter table CRUPROJECT_DEFREVGRP add constraint FK76CE5ABBA384A286 foreign key (PROJECT_ID) references CRUPROJECT;

create index ps_name_idx on PERM_SCHEME (name);
alter table PS_ALL_USER add constraint FK5C45A285CF03846B foreign key (ps) references PERM_SCHEME;
alter table PS_ANON add constraint FK1AEE3388CF03846B foreign key (ps) references PERM_SCHEME;
alter table PS_GROUP add constraint FK432E9D23CF03846B foreign key (ps) references PERM_SCHEME;
alter table PS_REVIEW_ROLE add constraint FK97DB18C1CF03846B foreign key (ps) references PERM_SCHEME;
alter table PS_USER add constraint FK1AF75C87CF03846B foreign key (ps) references PERM_SCHEME;

alter table REVIEW add constraint FK8FE1EB58680499E foreign key (project) references CRUPROJECT;

alter table REVPERMAID add constraint FK1DF5A649E71841CE foreign key (REVIEW_ID) references REVIEW;

UPDATE CRUVERSION SET version = 19 WHERE version_id = 1;
