-- SQL script for generation of CERA Core 2.4 loading tables

create table LOAD_ENTRY        (
       entry_id                number(15)    PRIMARY KEY,
       entry_name              varchar(160)                       NOT NULL,
       entry_acronym           varchar(31)   DEFAULT 'not filled' NOT NULL,
       creation_date           date          DEFAULT SYSDATE      NOT NULL,
       review_date             date          DEFAULT SYSDATE      NOT NULL,
       future_review_date      date          DEFAULT SYSDATE      NOT NULL,
       project_acronym         varchar(31)   DEFAULT 'not filled' NOT NULL,
       progress_descr          varchar(2000) DEFAULT 'not filled' NOT NULL,
       summary_id              number(15)                         NOT NULL,
       summary                 varchar(2000) DEFAULT 'not filled' NOT NULL,
       quality_id              number(15)                         NOT NULL,
       accuracy_report         varchar(2000) DEFAULT 'not filled' NOT NULL,
       consistency_report      varchar(2000) DEFAULT 'not filled' NOT NULL,
       completeness_report     varchar(2000) DEFAULT 'not filled' NOT NULL,
       horizontal_acc_report   varchar(2000) DEFAULT 'not filled' NOT NULL,
       vertical_acc_report     varchar(2000) DEFAULT 'not filled' NOT NULL,
       entry_type              varchar(31)   DEFAULT 'not filled' NOT NULL,
       entry_type_descr        varchar(2000) DEFAULT 'not filled' NOT NULL
       );

create table LOAD_CONTA        (
       entry_id                number(15)    NOT NULL references LOAD_ENTRY on delete cascade,
       contact_type            varchar(80)   DEFAULT 'not filled' NOT NULL,
       institute_id            number(15)    DEFAULT 0            NOT NULL,
       institute_info          varchar(250)                               ,
       person_id               number(15)    DEFAULT 0            NOT NULL,
       person_info             varchar(80)   
       );

create table LOAD_PARAM        (
       entry_id                number(15)    NOT NULL references LOAD_ENTRY on delete cascade,
       topic_name              varchar(80)   DEFAULT 'not filled' NOT NULL,
       unit_name               varchar(80)   DEFAULT 'not filled' NOT NULL,
       aggregation_descr       varchar(80)   DEFAULT 'not filled' NOT NULL,
       reference_method        varchar(80)   DEFAULT 'not filled' NOT NULL
       );

create table LOAD_REFER        (
       entry_id                number(15)    NOT NULL references LOAD_ENTRY on delete cascade,
       ref_type_descr          varchar(2000) DEFAULT 'not filled' NOT NULL,
       citation_id             number(15)    DEFAULT 0            NOT NULL,
       citation_info           varchar(80)
       );

create table LOAD_COVER        (
       entry_id                number(15)    NOT NULL references LOAD_ENTRY on delete cascade,
       location_name           varchar(80)                                ,
       spatial_coverage_id     number(15)                         NOT NULL,
       min_lat                 number(8,4)   DEFAULT -999.0       NOT NULL,
       max_lat                 number(8,4)   DEFAULT -999.0       NOT NULL,
       min_lon                 number(8,4)   DEFAULT -999.0       NOT NULL,
       max_lon                 number(8,4)   DEFAULT -999.0       NOT NULL,
       min_altitude            number(15,6)  DEFAULT 0            NOT NULL,
       min_alt_unit            varchar(31)   DEFAULT 'not filled' NOT NULL,
       max_altitude            number(15,6)  DEFAULT 0            NOT NULL,
       max_alt_unit            varchar(31)   DEFAULT 'not filled' NOT NULL,
       temporal_coverage_id    number(15)                         NOT NULL,
       start_year              number(15)                         NOT NULL,
       start_month             number(2)     DEFAULT 0            NOT NULL,
       start_day               number(2)     DEFAULT 0            NOT NULL,
       stop_year               number(15)                         NOT NULL,
       stop_month              number(2)     DEFAULT 0            NOT NULL,
       stop_day                number(2)     DEFAULT 0            NOT NULL,
       currentness_ref_descr   varchar(2000) DEFAULT 'not filled' NOT NULL
       );

create table LOAD_DISTR        (
       entry_id                number(15)    NOT NULL references LOAD_ENTRY on delete cascade,
       data_size               number(38)    DEFAULT 0            NOT NULL,
       format_acronym          varchar(31)   DEFAULT 'not filled' NOT NULL,
       access_acronym          varchar(31)   DEFAULT 'not filled' NOT NULL,
       fees_acronym            varchar(31)   DEFAULT 'not filled' NOT NULL,
       access_constraint_descr varchar(2000) DEFAULT 'not filled' NOT NULL,
       use_constraint_descr    varchar(2000) DEFAULT 'not filled' NOT NULL
       );

create table LOAD_SPATI        (
       entry_id                number(15)    NOT NULL references LOAD_ENTRY on delete cascade,
       hor_sys_descr           varchar(2000) DEFAULT 'not filled' NOT NULL,
       ver_sys_descr           varchar(2000) DEFAULT 'not filled' NOT NULL
       );

create table LOAD_PROCE        (
       entry_id                number(15)    NOT NULL references LOAD_ENTRY on delete cascade,
       process_descr           varchar(2000) DEFAULT 'not filled' NOT NULL,
       process_date            date          DEFAULT SYSDATE      NOT NULL,
       person_id               number(15)    DEFAULT 0            NOT NULL,
       person_info             varchar(80)   
       );

create table LOAD_KEY_C        (
       entry_id                number(15)    NOT NULL references LOAD_ENTRY on delete cascade,
       general_key             varchar(250)                       NOT NULL 
       );

