/**
 *  caArray_Update_After_Install_1_3_1.sql
 *
 *	To be used AFTER running caArray_Update_After_Install_1_01.sql, caArray_Update_After_Install_1_2.sql, and
 *		caArray_Update_After_Install_3.sql
 */



set timing on
/**
 * we will add a unique constraint to as_user.login_name.
 */
select login_name, count(*) 
  from as_user 
 group by login_name 
having count(*) > 1
/
delete from AS_USER_ROLE_PROTECT_APP where user_id = 69;
delete from as_user where user_id = 69;
delete from AS_USER_ROLE_PROTECT_APP where user_id = 70;
delete from as_user where user_id = 70;
commit;

alter table as_user modify login_name not null
/
alter table as_user 
  add constraint unq_as_user_login_name
  unique (login_name)
/
insert into CA_FILETYPE (
   ID, CREATINGSOFTWARE, CREATINGSOFTWAREVERSION, 
   FILEEXTENSION, FILEZIPEXTENSION, FILECATEGORY, 
   REQUIRED, DESCRIPTION, OJBCONCRETECLASS)
values ( 'AffyArrayDesignFile', 'Affymetrix','1.0', 'xml','_xml.zip',
         'ARRAY_DESIGN', '1', 'An Affymetrix array design file.',
         'gov.nih.nci.caarray.common.data.fileupload2.FileType')
/
commit
/
create index feature_fgrp_idx on feature(feature_group_id)
/
create index feature_zone_idx on feature(zone_id)
/
create index dem_report_id_idx on designelementmap (reporter_id)
/
create index dem_com_seq_id_idx on designelementmap (composite_sequence_id)
/
create index f_ctrlled_f_cfid_idx on featurecontrolledfeature (controlled_feature_id)
/
create index f_ctrl_f_cfid_idx on featurecontrolfeature (control_feature_id)
/

alter table organization modify (ADDRESS varchar2(512))
/

alter table person modify (ADDRESS varchar2(512))
/
alter table PERSON modify (URI VARCHAR2(200))
/
alter table ORGANIZATION modify (URI VARCHAR2(200))
/
alter table BIBLIOGRAPHICREFERENCE modify (URI VARCHAR2(200))
/
alter table SOFTWARE modify (URI VARCHAR2(200))
/
alter table HARDWARE modify (URI VARCHAR2(200))
/
alter table PROTOCOL modify (URI VARCHAR2(200))
/
alter table DESCRIPTION modify (URI VARCHAR2(200))
/
alter table DATABASEENTRY modify (URI VARCHAR2(200))
/
alter table DATABASE modify (URI VARCHAR2(200))
/
alter table IMAGE modify (URI VARCHAR2(200))
/
alter table BIBLIOGRAPHICREFERENCE modify (TITLE VARCHAR2(300))
/

-- 
-- new tables in 1.3.1
--

create table ca_file_status (
  status               varchar2(50) primary key,
  description          varchar2(255) not null
)
/

insert into ca_file_status values( 'SUBMITTED', 'new submission')
/
insert into ca_file_status values( 'SUBMISSION ERROR', 'Error occured on submission of the file, see error code')
/
insert into ca_file_status values( 'IN PROCESS', 'In process...')
/
insert into ca_file_status values( 'PROCESSING ERROR', 'Error occured while processing the file, see error code')
/
insert into ca_file_status values( 'PROCESSING SUCCESSFUL', 'Processing Successful')
/
insert into ca_file_status values( 'DELETED', 'File is marked for deletion')
/
insert into ca_file_status values( 'DELETION ERROR', 'Error occured on deletion operation')
/
insert into ca_file_status values( 'VIRUS SCAN SUCCESSFUL', 'Virus scan successful')
/
insert into ca_file_status values( 'VIRUS SCAN ERROR ', 'Error occured on virus scan, see error code')
/

CREATE TABLE CA_FILEUPLOADENTRY (
  ID                 NUMBER                     PRIMARY KEY,
  FILENAME           VARCHAR2(200)              NOT NULL,
  UPLOADDATE         DATE DEFAULT SYSDATE       NOT NULL,
  FILE_TYPE_ID       VARCHAR2(20)               NOT NULL
    REFERENCES ca_filetype,
  ORIGINAL_FILENAME  VARCHAR2(255)              NOT NULL,
  USER_ID            VARCHAR2(30),
  STATUS             VARCHAR2(50)               NOT NULL
    REFERENCES ca_file_status,
  STATUS_DATE        DATE  DEFAULT SYSDATE      NOT NULL,
  VALIDATOR_CODE     VARCHAR2(255) DEFAULT -1   NOT NULL
)
/

alter table ca_fileuploadentry
  add constraint ca_fue_user_id_fk
  foreign key (user_id)
  references as_user (login_name)
/
create index idx_fue_user_id on CA_FILEUPLOADENTRY(user_id)
/
create index idx_fue_status on CA_FILEUPLOADENTRY(status)
/
create index idx_fue_type_id on CA_FILEUPLOADENTRY(file_type_id)
/

insert into CA_FILEUPLOADENTRY (
  ID, FILENAME, UPLOADDATE, FILE_TYPE_ID, ORIGINAL_FILENAME, 
  USER_ID, STATUS, STATUS_DATE)
select id,
       filename,
       uploaddate,
       file_type_id,
       filename,
     ( select distinct nvl(login_name, 'PUBLIC')
         from AS_protection_element pe,
              as_user u
        where instr(object_id, 'experiment') > 0
          and replace(object_id, 'caarray;experiment;', '') = fe.experiment_id
          and pe.user_id = u.user_id(+)),
       decode(valid, 1, 'SUBMITTED', 2, 'SUBMISSION ERROR', 3, 'DELETED'),
       nvl(validationdate, sysdate)
  from ca_hybridfileuploadentry fe
/
commit
/
create table ca_hybridfileuploadentry_bkp 
  as select * from ca_hybridfileuploadentry
/
alter table ca_hybridfileuploadentry modify experiment_id null
/
alter table ca_hybridfileuploadentry rename column id to file_upload_entry_id
/
-- NOTE: we will now drop the columns valid & validationdate 
-- (data has been migrated)
alter table ca_hybridfileuploadentry modify file_type_id null
/
alter table ca_hybridfileuploadentry modify filename null
/
alter table ca_hybridfileuploadentry modify uploaddate null
/
update ca_hybridfileuploadentry
   set valid = null,
       validationdate =null,
       uploaddate = null,
       file_type_id = null,
       filename = null
/
alter table ca_hybridfileuploadentry
 drop column valid
/
alter table ca_hybridfileuploadentry
 drop column validationdate
/
alter table ca_hybridfileuploadentry
 drop column uploaddate
/
alter table ca_hybridfileuploadentry
 drop column file_type_id
/
alter table ca_hybridfileuploadentry
 drop column filename
/

insert into CA_FILEUPLOADENTRY (
  ID, FILENAME, UPLOADDATE, FILE_TYPE_ID, ORIGINAL_FILENAME, 
  USER_ID, STATUS, STATUS_DATE)
select id,
       filename,
       uploaddate,
       file_type_id,
       filename,
     ( select distinct nvl(login_name, 'PUBLIC') ll
	    from AS_protection_element pe,
	         as_user u
	   where instr(object_id, 'array_design') > 0
	     and replace(object_id, 'caarray;array_design;', '') = fe.array_design_id
	     and pe.user_id = u.user_id(+)),
       decode(valid, 1, 'SUBMITTED', 2, 'SUBMISSION ERROR', 3, 'DELETED'),
       nvl(validationdate, sysdate)
  from ca_ARRAYDSGNFILEUPLOADENTRY fe
/
update CA_FILEUPLOADENTRY set user_id = 'PUBLIC' where user_id is null
/
alter table CA_FILEUPLOADENTRY modify (user_id not null)
/
commit
/
create table ca_ARR_DSGNFILEUPLOADENTRY_BKP
  as select * from ca_ARRAYDSGNFILEUPLOADENTRY
/
alter table ca_ARRAYDSGNFILEUPLOADENTRY modify file_type_id null
/
alter table ca_ARRAYDSGNFILEUPLOADENTRY modify filename null
/
alter table ca_ARRAYDSGNFILEUPLOADENTRY modify uploaddate null
/
update ca_ARRAYDSGNFILEUPLOADENTRY
   set valid = null,
       validationdate =null,
       uploaddate = null,
       file_type_id = null,
       filename = null
/
alter table ca_ARRAYDSGNFILEUPLOADENTRY
 drop column valid
/
alter table ca_ARRAYDSGNFILEUPLOADENTRY
 drop column validationdate
/
alter table ca_ARRAYDSGNFILEUPLOADENTRY
 drop column uploaddate
/
alter table ca_ARRAYDSGNFILEUPLOADENTRY
 drop column file_type_id
/
alter table ca_ARRAYDSGNFILEUPLOADENTRY
 drop column filename
/
alter table CA_ARRAYDSGNFILEUPLOADENTRY rename column id to file_upload_entry_id
/
ALTER TABLE CA_ARRAYDSGNFILEUPLOADENTRY ADD (
  CONSTRAINT CA_ADFILEUPLOADENTRY_FK13 FOREIGN KEY (FILE_UPLOAD_ENTRY_ID) 
    REFERENCES CA_FILEUPLOADENTRY (ID))
/

--
-- CA_MAGEMLFILEUPLOADENTRY  (Table) 
--
--  Dependencies: 
--   CA_FILEUPLOADENTRY (Table)
--   PERSON (Table)
--   CA_FILEPARSINGENTRY (Table)
--   CA_FILEUPLOADGROUP (Table)
--
CREATE TABLE CA_MAGEMLFILEUPLOADENTRY (
   NAME VARCHAR2(100),
   VERSION VARCHAR2(24),
   DESCRIPTION VARCHAR2(4000),
   CONTENT_TYPE VARCHAR2(50) DEFAULT NULL,
   CONTACT_ID NUMBER DEFAULT -1,
   MSG_TO_CURATOR VARCHAR2(4000),
   FILE_UPLOAD_ENTRY_ID NUMBER NOT NULL, 
   FILE_PARSING_ENTRY_ID NUMBER, 
   MAGEML_FILE_UPLOAD_GRP_ID NUMBER )
/
--
-- CA_MAGEMLFILEUPLOADENTRY_PK  (Index) 
--
--  Dependencies: 
--   CA_MAGEMLFILEUPLOADENTRY (Table)
--
CREATE UNIQUE INDEX CA_MAGEMLFILEUPLOADENTRY_PK ON CA_MAGEMLFILEUPLOADENTRY (FILE_UPLOAD_ENTRY_ID)
/

-- 
-- Non Foreign Key Constraints for Table CA_MAGEMLFILEUPLOADENTRY 
-- 
ALTER TABLE CA_MAGEMLFILEUPLOADENTRY ADD ( 
 CONSTRAINT CA_MAGEMLFILEUPLOADENTRY_PK PRIMARY KEY (FILE_UPLOAD_ENTRY_ID))
/

-- 
-- Foreign Key Constraints for Table CA_MAGEMLFILEUPLOADENTRY 
-- 
ALTER TABLE CA_MAGEMLFILEUPLOADENTRY ADD ( CONSTRAINT CA_MAGEMLFILEUPLOADENTRY_FK13 
  FOREIGN KEY (FILE_UPLOAD_ENTRY_ID) REFERENCES CA_FILEUPLOADENTRY (ID))
/
ALTER TABLE CA_MAGEMLFILEUPLOADENTRY ADD ( FOREIGN KEY (CONTACT_ID) REFERENCES PERSON (ID))
/
create index idx_magemlfue_contact_id on CA_MAGEMLFILEUPLOADENTRY(contact_id)
/
ALTER TABLE CA_MAGEMLFILEUPLOADENTRY ADD ( FOREIGN KEY (FILE_PARSING_ENTRY_ID) REFERENCES CA_FILEPARSINGENTRY (ID))
/
create index idx_magemlfue_fpe_id on CA_MAGEMLFILEUPLOADENTRY(FILE_PARSING_ENTRY_ID)
/
ALTER TABLE CA_MAGEMLFILEUPLOADENTRY ADD ( FOREIGN KEY (MAGEML_FILE_UPLOAD_GRP_ID) REFERENCES CA_FILEUPLOADGROUP (ID))
/
create index idx_magemlfue_mfug_id on CA_MAGEMLFILEUPLOADENTRY(MAGEML_FILE_UPLOAD_GRP_ID)
/

create table ca_file_upload_history (
  id                   number primary key,
  file_upload_entry_id number not null references CA_FILEUPLOADENTRY,
  status               varchar2(50) not null references ca_file_status,
  status_date          date not null,
  description          varchar2(255)
)
/

create index idx_ca_history_fueid on ca_file_upload_history(file_upload_entry_id)
/
create index idx_ca_history_status on ca_file_upload_history(status)
/
create table CA_FILEPARSINGENTRY_BKP as
select * from CA_FILEPARSINGENTRY
/
alter table CA_FILEPARSINGENTRY modify PARSINGSUCCEEDED null
/
alter table CA_FILEPARSINGENTRY drop constraint CA_FILEPARSINGENTRY_CK1
/
update CA_FILEPARSINGENTRY set PARSINGSUCCEEDED = null
/
alter table CA_FILEPARSINGENTRY modify (PARSINGSUCCEEDED varchar2(50))
/
update CA_FILEPARSINGENTRY t1
   set PARSINGSUCCEEDED = (
      select decode(parsingsucceeded, 0, 'IN PROCESS',
	                            1, 'PROCESSING SUCCESSFUL',
	                            2, 'PROCESSING ERROR')
	    from CA_FILEPARSINGENTRY_bkp t2
	   where t1.id = t2.id )
/
commit
/
alter table CA_FILEPARSINGENTRY modify (PARSINGSUCCEEDED not null)
/
alter table CA_FILEPARSINGENTRY
  add constraint ca_fileparsingentry_ps_fk
  foreign key (PARSINGSUCCEEDED)
  references ca_file_status
/
alter table CA_FILEPARSINGENTRY
  add constraint CA_FILEPARSINGENTRY_CK1
  check (PARSINGSUCCEEDED in ('IN PROCESS', 'PROCESSING SUCCESSFUL', 'PROCESSING ERROR'))
/
create table CA_FILEUPLOADGROUP_BKP as
select * from CA_FILEUPLOADGROUP
/
alter table CA_FILEUPLOADGROUP drop constraint CA_FILEUPLOADGROUP_CK1
/
update CA_FILEUPLOADGROUP set PARSINGSUCCEEDED = null
/
alter table CA_FILEUPLOADGROUP modify (PARSINGSUCCEEDED varchar2(50))
/
update CA_FILEUPLOADGROUP t1
   set PARSINGSUCCEEDED = (
       select decode(parsingsucceeded, 0, 'IN PROCESS',
	                            1, 'PROCESSING SUCCESSFUL',
	                            2, 'PROCESSING ERROR')
	     from CA_FILEUPLOADGROUP_BKP t2
	    where t1.id = t2.id )
/
commit
/
alter table CA_FILEUPLOADGROUP modify (PARSINGSUCCEEDED not null)
/
alter table CA_FILEUPLOADGROUP
  add constraint ca_CA_FILEUPLOADGROUP_ps_fk
  foreign key (PARSINGSUCCEEDED)
  references ca_file_status
/
alter table CA_FILEUPLOADGROUP
  add constraint CA_FILEUPLOADGROUP_CK1
  check (PARSINGSUCCEEDED in ('IN PROCESS', 'PROCESSING SUCCESSFUL', 'PROCESSING ERROR'))
/

CREATE TABLE CA_APPLICATION ( 
  APPLICATION_NAME VARCHAR2(20) DEFAULT 'CAARRAY' NOT NULL, 
  APPLICATION_VERSION VARCHAR2(20) DEFAULT '1.3.1', 
  APPLICATION_CODETAG VARCHAR2(100), 
  STATUS VARCHAR2(20) DEFAULT 'INVALID', 
  STATUS_DATETIME DATE DEFAULT SYSDATE, 
  DATABASE_SCHEMA VARCHAR2(20) DEFAULT 'DEV' )
/

INSERT INTO CA_APPLICATION ( 
   APPLICATION_NAME, APPLICATION_VERSION, APPLICATION_CODETAG, STATUS,
   STATUS_DATETIME, DATABASE_SCHEMA ) 
VALUES ( 'CAARRAY', '1.3.1', NULL, 'INVALID', NULL, 'DEV')
/
COMMIT
/
exec dbms_stats.gather_schema_stats('', estimate_percent=> 15, cascade=>true);

