-- Views to handle the update access of CERA
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------

-- Identifiing the entry
create or replace view SEL_ENTRY as
   select ENTRY.entry_id, entry_name, entry_acronym, creation_date, summary, 
          entry_type, project_acronym
     from ENTRY, SUMMARY, ENTRY_TYPE, CAMPAIGN, PROJECT
    where ENTRY.summary_id      = SUMMARY.summary_id(+)
      and ENTRY.entry_type_id   = ENTRY_TYPE.entry_type_id(+)
      and ENTRY.entry_id        = CAMPAIGN.entry_id(+)
      and   CAMPAIGN.project_id   = PROJECT.project_id(+);

-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- Editing the CAMPAIGN(s)
create or replace view UPD_CAMPA as
   select entry_id,CAMPAIGN.project_id,project_name,project_acronym,project_descr
     from CAMPAIGN,PROJECT
    where CAMPAIGN.project_id   = PROJECT.project_id;

-- ----------------------------------------------------------------------------
-- Editing the CONTACT information(s)
create or replace view UPD_CONTA as
   select entry_id, CONTACT.contact_type_id, contact_type, CONTACT.person_id,
          first_name||' '||last_name||' - '||INSTITUTE1.institute_acronym per_name,
          CONTACT.institute_id,
          INSTITUTE.institute_name||'-'||INSTITUTE.department_name||' ('||
          INSTITUTE.street||', '||INSTITUTE.place||', '||INSTITUTE.country||')' ins_name
     from CONTACT, CONTACT_TYPE, PERSON, INSTITUTE, INSTITUTE1
    where CONTACT.person_id        = PERSON.person_id
      and CONTACT.institute_id     = INSTITUTE.institute_id
      and CONTACT.contact_type_id  = CONTACT_TYPE.contact_type_id
      and PERSON.institute_id      = INSTITUTE1.institute_id;

-- ----------------------------------------------------------------------------
-- Editing the entries' COVERAGE information, i.e. only the connection of the entry 
--   to temp. and spat. coverage, not the records in TEMP. and SPATIAL_COVERAGE.
create or replace view UPD_COVER as
   select entry_id, 
          'N:'||max_lat||' S:'||min_lat||' E:'||max_lon||' W:'||min_lon NSEWbound,
--              max_lat|| ';' ||min_lat|| ';' ||max_lon|| ';' ||min_lon hor_bound,
          'u:'||max_altitude||' '||UNIT2.unit_acronym||' l:'||min_altitude||' '||UNIT1.unit_acronym ULbound,
--              max_altitude||' '||UNIT2.unit_acronym|| ';' ||min_altitude||' '||UNIT1.unit_acronym ver_bound,
          COVERAGE.spatial_coverage_id,  min_lat, max_lat, min_lon, max_lon,
                                         min_altitude, UNIT1.unit_acronym min_alt_unit,
                                         max_altitude, UNIT2.unit_acronym max_alt_unit,
          start_year||'.'||start_month||'.'||start_day start_date,
          stop_year ||'.'||stop_month ||'.'||stop_day  stop_date,
          COVERAGE.temporal_coverage_id, start_year, start_month, start_day, 
                                         stop_year, stop_month, stop_day, currentness_ref_descr
     from COVERAGE, SPATIAL_COVERAGE, TEMPORAL_COVERAGE, UNIT1, UNIT2, CURRENTNESS_REF
    where COVERAGE.spatial_coverage_id  = SPATIAL_COVERAGE.spatial_coverage_id
      and   min_alt_unit_id               = UNIT1.unit_id
      and   max_alt_unit_id               = UNIT2.unit_id
      and COVERAGE.temporal_coverage_id = TEMPORAL_COVERAGE.temporal_coverage_id
      and   TEMPORAL_COVERAGE.currentness_ref_id = CURRENTNESS_REF.currentness_ref_id;

-- ----------------------------------------------------------------------------
-- Editing the entry's specification according DIF (directory interchange format)
create or replace view UPD_DIF_E as 
   select dif_name,dif_title,entry_id 
     from DIF_ENTRY;

-- ----------------------------------------------------------------------------
-- Editing the entries' DISTRIBUTION information
create or replace view UPD_DISTR as
   select entry_id, data_size,
          DISTRIBUTION.access_type_id,      access_acronym, access_descr,
          DISTRIBUTION.fees_id,             fees_acronym, fees_descr,
          DISTRIBUTION.format_id,           format_acronym, format_descr,
          DISTRIBUTION.access_constraint_id,ACCESS_CONSTRAINT.constraint_descr acc_con_descr,
          DISTRIBUTION.use_constraint_id,   USE_CONSTRAINT.constraint_descr use_con_descr
     from DISTRIBUTION, ACCESS_TYPE, FEES, FORMAT, ACCESS_CONSTRAINT, USE_CONSTRAINT
    where DISTRIBUTION.access_type_id      = ACCESS_TYPE.access_type_id
      and DISTRIBUTION.fees_id             = FEES.fees_id
      and DISTRIBUTION.format_id           = FORMAT.format_id
      and DISTRIBUTION.access_constraint_id= ACCESS_CONSTRAINT.access_constraint_id
      and DISTRIBUTION.use_constraint_id   = USE_CONSTRAINT.use_constraint_id;

-- ----------------------------------------------------------------------------
-- Editing the ENTRY's CONNECTions to other symmetrical or more general entries
create or replace view UPD_ENT_G as
   select spec_entry_id,
          ENTRY_CONNECT.connect_type_id, connect_type, connect_type_descr,
          gen_entry_id,                  ENTRY.entry_id, ENTRY.entry_name, 
                                         ENTRY.entry_acronym, ENTRY.creation_date,
          entry_type
     from ENTRY, ENTRY_CONNECT, CONNECT_TYPE, ENTRY_TYPE
    where ENTRY_CONNECT.connect_type_id = CONNECT_TYPE.connect_type_id
      and ENTRY_CONNECT.gen_entry_id    = ENTRY.entry_id
      and ENTRY.entry_type_id           = ENTRY_TYPE.entry_type_id;

-- ----------------------------------------------------------------------------
-- Editing the ENTRY's CONNECTions to other symmetrical or more special entries
create or replace view UPD_ENT_S as
   select gen_entry_id,
          ENTRY_CONNECT.connect_type_id, connect_type, connect_type_descr,
          spec_entry_id,                 ENTRY.entry_id, ENTRY.entry_name, 
                                         ENTRY.entry_acronym, ENTRY.creation_date,
          entry_type
     from ENTRY, ENTRY_CONNECT, CONNECT_TYPE, ENTRY_TYPE
    where ENTRY_CONNECT.connect_type_id = CONNECT_TYPE.connect_type_id
      and ENTRY_CONNECT.spec_entry_id   = ENTRY.entry_id
      and ENTRY.entry_type_id           = ENTRY_TYPE.entry_type_id;

-- ----------------------------------------------------------------------------
-- Editing the entries' ENTRY and (partly) STATUS information,
--   ommitting the DIF_ENTRY information, as it is not used at PIK
create or replace view UPD_ENTRY as
   select ENTRY.entry_id, entry_name, entry_acronym, creation_date, review_date, 
                                 future_review_date,
          ENTRY.entry_type_id,   entry_type, entry_type_descr,
          ENTRY.summary_id,      summary,
          ENTRY.quality_id,      accuracy_report, consistency_report,
                                 completeness_report, horizontal_acc_report,
                                 vertical_acc_report,
          ENTRY.progress_id,     progress_descr
     from ENTRY, ENTRY_TYPE, SUMMARY, QUALITY, PROGRESS
    where ENTRY.entry_type_id    = ENTRY_TYPE.entry_type_id(+)
      and ENTRY.summary_id       = SUMMARY.summary_id(+)
      and ENTRY.quality_id       = QUALITY.quality_id(+)
      and ENTRY.progress_id      = PROGRESS.progress_id(+);

-- ----------------------------------------------------------------------------
-- Editing the KEYword(s)
create or replace view UPD_KEY_C as
   select entry_id, KEY_CONNECT.general_key_id, general_key
     from KEY_CONNECT, GENERAL_KEY
    where KEY_CONNECT.general_key_id = GENERAL_KEY.general_key_id;

-- ----------------------------------------------------------------------------
-- Editing the LOCATION(s)
create or replace view UPD_LOCAT as
   select entry_id, LOCATION_CONNECT.location_id, LOCATION.location_name name, 
          LOCATION.ref_date, LOCATION1.location_name pointer
     from LOCATION_CONNECT, LOCATION, LOCATION1
    where LOCATION_CONNECT.location_id = LOCATION.location_id
      and LOCATION.location_pointer    = LOCATION1.location_id;

-- ----------------------------------------------------------------------------
-- Editing the entries' MODEL INPUT (according CERA Module)
create or replace view UPD_MODEL as
   
   select entry_id, 
          MODEL_INPUT.model_id,          model_name, model_acronym,
                                         model_type_name, model_type_acronym,
          MODEL_INPUT.topic_id,          topic_name, topic_acronym,
          MODEL_INPUT.value_number,      value_string,calculation_start,
          MODEL_INPUT.unit_id,           unit_name, unit_acronym
     from MODEL_INPUT, MODEL, MODEL_TYPE, TOPIC, UNIT
    where MODEL_INPUT.model_id          = MODEL.model_id
      and   MODEL.model_type_id           = MODEL_TYPE.model_type_id
      and MODEL_INPUT.topic_id          = TOPIC.topic_id
      and MODEL_INPUT.unit_id           = UNIT.unit_id;

-- ----------------------------------------------------------------------------
-- Editing the entries' PARAMETER(s)
create or replace view UPD_PARAM as
   select entry_id, 
          PARAMETER.topic_id,            topic_name, topic_acronym,
          PARAMETER.unit_id,             unit_name,  unit_acronym,
          PARAMETER.spatial_data_org_id, reference_method,
          PARAMETER.aggregation_id,      aggregation_descr,
          PARAMETER.data_org_id,       
          PARAMETER.data_access_id,      access_structure_name,
             STORAGE1.storage_name storage1, STORAGE2.storage_name storage2,
             STORAGE3.storage_name storage3, STORAGE4.storage_name storage4
     from PARAMETER, TOPIC, UNIT, SPATIAL_DATA_ORG, AGGREGATION, DATA_ACCESS,
          ACCESS_STRUCTURE, STORAGE1, STORAGE2, STORAGE3, STORAGE4
    where PARAMETER.topic_id            = TOPIC.topic_id
      and PARAMETER.unit_id             = UNIT.unit_id
      and PARAMETER.spatial_data_org_id = SPATIAL_DATA_ORG.spatial_data_org_id
      and PARAMETER.aggregation_id      = AGGREGATION.aggregation_id
--    following line omitted, as CERA Module DATA_ORGANIZATION is not used at PIK
--    and PARAMETER.data_org_id         = DATA_ORG.data_org_id
      and PARAMETER.data_access_id      = DATA_ACCESS.data_access_id
      and   DATA_ACCESS.access_structure_id = ACCESS_STRUCTURE.access_structure_id
      and   DATA_ACCESS.storage1_id         = STORAGE1.storage_id
      and   DATA_ACCESS.storage2_id         = STORAGE2.storage_id
      and   DATA_ACCESS.storage3_id         = STORAGE3.storage_id
      and   DATA_ACCESS.storage4_id         = STORAGE4.storage_id;

-- ----------------------------------------------------------------------------
-- Editing the data PROCESSing(s)
create or replace view UPD_PROCE as
   select entry_id, process_descr, process_date, PROCESS_STEP.person_id, 
          first_name||' '||last_name||' - '||institute_acronym name
     from PROCESS_STEP, PERSON, INSTITUTE
    where PROCESS_STEP.person_id    = PERSON.person_id
      and PERSON.institute_id       = INSTITUTE.institute_id;

-- ----------------------------------------------------------------------------
-- Editing the REFERENCE(s) related to the entry
create or replace view UPD_REFER as
   select entry_id, REFERENCE.citation_id, REFERENCE.ref_type_id,
          ref_type_descr, authors||':'||title||'('||place||')' citation
     from REFERENCE, REF_TYPE, CITATION
    where REFERENCE.ref_type_id     = REF_TYPE.ref_type_id
      and REFERENCE.citation_id     = CITATION.citation_id;

-- ----------------------------------------------------------------------------
-- Editing the entries' SPATIAL REFERENCEs (coord. systems)
create or replace view UPD_SPATI as
   select entry_id,
          SPATIAL_REFERENCE.hor_coord_sys_id, HOR_COORD_SYS.sys_descr hor_coord_sys_descr,
          SPATIAL_REFERENCE.ver_coord_sys_id, VER_COORD_SYS.sys_descr ver_coord_sys_descr
     from SPATIAL_REFERENCE, HOR_COORD_SYS, VER_COORD_SYS
    where SPATIAL_REFERENCE.hor_coord_sys_id = HOR_COORD_SYS.hor_coord_sys_id
      and SPATIAL_REFERENCE.ver_coord_sys_id = VEr_COORD_SYS.ver_coord_sys_id;

-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- Information on the affected Entries when editing the entry's SUMMARY
create or replace view UPD_SUMMA_INFO as 
   select entry_id,entry_name,creation_date,summary_id
     from ENTRY;

-- ----------------------------------------------------------------------------
-- Information on the affected Entries when editing the entry's QUALITY
create or replace view UPD_QUALI_INFO as 
   select entry_id,entry_name,creation_date,quality_id
     from ENTRY;

-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- from here on follow the views for changing the tables of the second level --
--    that are not directly connected to the table ENTRY but are connected   --
--    to it via a relation table    -------------------------------------------
-- ----------------------------------------------------------------------------
-- Editing the spatial coverage of an entry (longitude, latitude, altitude)
create or replace view UPD_COV_S as
   select spatial_coverage_id, min_lat, max_lat, min_lon, max_lon,
          min_altitude, min_alt_unit_id, UNIT1.unit_acronym min_alt_unit_acronym,
          max_altitude, max_alt_unit_id, UNIT2.unit_acronym max_alt_unit_acronym
     from SPATIAL_COVERAGE, UNIT1, UNIT2
    where min_alt_unit_id             = UNIT1.unit_id
      and max_alt_unit_id             = UNIT2.unit_id;

-- ----------------------------------------------------------------------------
-- ----- Information on the affected ENTRY records and COVERAGE records when --
-- ------------------------------------ editing the entry's SPATIAL COVERAGE --
create or replace view UPD_COV_S_INFO as 
   select distinct ENTRY.entry_id, entry_name, creation_date,
          spatial_coverage_id, COVERAGE.temporal_coverage_id, start_date, stop_date
     from ENTRY, COVERAGE, TEMPO_COVER
    where ENTRY.entry_id                = COVERAGE.entry_id
      and COVERAGE.temporal_coverage_id = TEMPO_COVER.temporal_coverage_id;

-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- Editing the temporal coverage of an entry (startdate, stopdate)
create or replace view UPD_COV_T as
   select temporal_coverage_id, start_year, start_month, start_day,
                                stop_year,  stop_month,  stop_day,
          TEMPORAL_COVERAGE.currentness_ref_id, currentness_ref_descr
     from TEMPORAL_COVERAGE, CURRENTNESS_REF
    where TEMPORAL_COVERAGE.currentness_ref_id = CURRENTNESS_REF.currentness_ref_id;

-- ----------------------------------------------------------------------------
-- ----- Information on the affected ENTRY records and COVERAGE records when --
-- ----------------------------------- editing the entry's TEMPORAL COVERAGE --
create or replace view UPD_COV_T_INFO as 
   select distinct ENTRY.entry_id, entry_name, creation_date,
          temporal_coverage_id, COVERAGE.spatial_coverage_id, hor_bound, ver_bound
     from ENTRY, COVERAGE, SPATI_COVER
    where ENTRY.entry_id              = COVERAGE.entry_id
      and COVERAGE.spatial_coverage_id = SPATI_COVER.spatial_coverage_id;

-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- Editing specifications of the access to the data
create or replace view UPD_D_ACC as
   select data_access_id, DATA_ACCESS.access_structure_id, 
            access_structure_name, access_structure_descr,
          storage1_id, STORAGE1.storage_name storage1_name, 
          storage2_id, STORAGE2.storage_name storage2_name,
          storage3_id, STORAGE3.storage_name storage3_name, 
          storage4_id, STORAGE4.storage_name storage4_name,
          rec_structure_id, modification_date
     from DATA_ACCESS, ACCESS_STRUCTURE, STORAGE1, STORAGE2, STORAGE3, STORAGE4
    where DATA_ACCESS.access_structure_id = ACCESS_STRUCTURE.access_structure_id
      and DATA_ACCESS.storage1_id         = STORAGE1.storage_id
      and DATA_ACCESS.storage2_id         = STORAGE2.storage_id
      and DATA_ACCESS.storage3_id         = STORAGE3.storage_id
      and DATA_ACCESS.storage4_id         = STORAGE4.storage_id ;

-- ----------------------------------------------------------------------------
-- ---- Information on the affected ENTRY records and PARAMETER records when --
-- ----------------------------- editing the entry's DATA ACCESS information --
create or replace view UPD_D_ACC_INFO as 
   select distinct ENTRY.entry_id, entry_name, creation_date, DATA_ACCESS_ID,
          topic_acronym, unit_acronym, aggregation_descr
     from ENTRY, PARAMETER, TOPIC, UNIT, AGGREGATION
    where ENTRY.entry_id    = PARAMETER.entry_id
      and PARAMETER.topic_id       = TOPIC.topic_id    
      and PARAMETER.unit_id        = UNIT.unit_id     
      and PARAMETER.aggregation_id = AGGREGATION.aggregation_id;

-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
