-- Creating domains

CREATE DOMAIN BOOLEAN_CHAR AS BOOLEAN DEFAULT FALSE;
CREATE DOMAIN CODE AS VARCHAR(20);
CREATE DOMAIN COLUMN_LABEL AS VARCHAR(40);
CREATE DOMAIN DESCRIPTION_250 AS VARCHAR(250);
CREATE DOMAIN DESCRIPTION_80 AS VARCHAR(80);
CREATE DOMAIN DOUBLE_PRECISION_VALUE AS DOUBLE PRECISION;
CREATE DOMAIN FILE AS BYTEA;
CREATE DOMAIN FILE_NAME AS VARCHAR(100);
CREATE DOMAIN GENERIC_VALUE AS VARCHAR(1024);
CREATE DOMAIN OBJECT_NAME AS VARCHAR(50);
CREATE DOMAIN REAL_VALUE AS REAL;
CREATE DOMAIN TECH_ID AS BIGINT;
CREATE DOMAIN TIME_STAMP AS TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
CREATE DOMAIN USER_ID AS VARCHAR(20);

-- Creating tables

CREATE TABLE DATA (ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,OBTY_ID TECH_ID NOT NULL,PROC_ID_ACQUIRED_BY TECH_ID NOT NULL,SAMP_ID_ACQUIRED_FROM TECH_ID,SAMP_ID_DERIVED_FROM TECH_ID);
CREATE TABLE DATA_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL);
CREATE TABLE DATA_VALUES (ID TECH_ID NOT NULL,DATA_ID TECH_ID NOT NULL,SACO_ID TECH_ID NOT NULL,VALUE DOUBLE_PRECISION_VALUE NOT NULL);
CREATE TABLE EXPERIMENTS (ID TECH_ID NOT NULL,CODE CODE NOT NULL,EXTY_ID TECH_ID NOT NULL,MATE_ID_STUDY_OBJECT TECH_ID,PERS_ID_REGISTERER TECH_ID,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,DESCRIPTION DESCRIPTION_250,PROJ_ID TECH_ID NOT NULL);
CREATE TABLE EXPERIMENT_ATTACHMENTS (ID TECH_ID NOT NULL,EXPE_ID TECH_ID NOT NULL,FILE_NAME FILE_NAME NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,VALUE FILE NOT NULL,VERSION INTEGER NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL);
CREATE TABLE EXPERIMENT_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE EXTERNAL_DATA (DATA_ID TECH_ID NOT NULL,LOCATION VARCHAR(200) NOT NULL,LOTY_ID TECH_ID NOT NULL,FFTY_ID TECH_ID NOT NULL);
CREATE TABLE FILE_FORMAT_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE INVALIDATIONS (ID TECH_ID NOT NULL,PERS_ID_REGISTERER TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,REASON DESCRIPTION_250);
CREATE TABLE LOCATOR_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE MATERIALS (ID TECH_ID NOT NULL,CODE CODE NOT NULL,MATY_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,MATE_ID_INHIBITOR_OF TECH_ID);
CREATE TABLE MATERIAL_BATCHES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,AMOUNT REAL_VALUE,MATE_ID TECH_ID NOT NULL,PROC_ID TECH_ID,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL);
CREATE TABLE MATERIAL_PROPERTIES (ID TECH_ID NOT NULL,MATE_ID TECH_ID NOT NULL,VALUE GENERIC_VALUE NOT NULL,MTPT_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL);
CREATE TABLE MATERIAL_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE MATERIAL_TYPE_PROPERTY_TYPES (ID TECH_ID NOT NULL,MATY_ID TECH_ID NOT NULL,PRTY_ID TECH_ID NOT NULL,IS_MANDATORY BOOLEAN_CHAR NOT NULL DEFAULT 'F',REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL);
CREATE TABLE OBSERVABLE_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);
CREATE TABLE ORGANIZATIONS (ID TECH_ID NOT NULL,CODE CODE NOT NULL);
CREATE TABLE PERSONS (ID TECH_ID NOT NULL,FIRST_NAME VARCHAR(30),LAST_NAME VARCHAR(30),USER_ID USER_ID,EMAIL OBJECT_NAME);
CREATE TABLE PROCEDURES (ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,EXPE_ID TECH_ID NOT NULL,PCTY_ID TECH_ID NOT NULL,PERS_ID TECH_ID);
CREATE TABLE PROCEDURE_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL);
CREATE TABLE PROJECTS (ID TECH_ID NOT NULL,CODE CODE NOT NULL,ORGA_ID TECH_ID NOT NULL);
CREATE TABLE PROPERTY_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80 NOT NULL,LABEL COLUMN_LABEL NOT NULL,DATY_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL);
CREATE TABLE SAMPLES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,SAMP_ID_TOP TECH_ID,SAMP_ID_GENERATED_FROM TECH_ID,SATY_ID TECH_ID NOT NULL,REGISTRATION_TIMESTAMP TIME_STAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PERS_ID_REGISTERER TECH_ID NOT NULL,INVA_ID TECH_ID);
CREATE TABLE SAMPLE_COMPONENTS (ID TECH_ID NOT NULL,CODE CODE NOT NULL,SAMP_ID TECH_ID NOT NULL);
CREATE TABLE SAMPLE_COMPONENT_MATERIALS (ID TECH_ID NOT NULL,SACO_ID TECH_ID NOT NULL,MABA_ID TECH_ID NOT NULL);
CREATE TABLE SAMPLE_INPUTS (ID TECH_ID NOT NULL,SAMP_ID TECH_ID NOT NULL,PROC_ID TECH_ID NOT NULL);
CREATE TABLE SAMPLE_TYPES (ID TECH_ID NOT NULL,CODE CODE NOT NULL,DESCRIPTION DESCRIPTION_80);

-- Creating sequences

CREATE SEQUENCE DATA_ID_SEQ;
CREATE SEQUENCE DATA_TYPE_ID_SEQ;
CREATE SEQUENCE DATA_VALUE_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_ATTACHMENT_ID_SEQ;
CREATE SEQUENCE EXPERIMENT_TYPE_ID_SEQ;
CREATE SEQUENCE FILE_FORMAT_TYPE_ID_SEQ;
CREATE SEQUENCE INVALIDATION_ID_SEQ;
CREATE SEQUENCE LOCATOR_TYPE_ID_SEQ;
CREATE SEQUENCE MATERIAL_BATCH_ID_SEQ;
CREATE SEQUENCE MATERIAL_ID_SEQ;
CREATE SEQUENCE MATERIAL_PROPERTY_ID_SEQ;
CREATE SEQUENCE MATERIAL_TYPE_ID_SEQ;
CREATE SEQUENCE MTPT_ID_SEQ;
CREATE SEQUENCE OBSERVABLE_TYPE_ID_SEQ;
CREATE SEQUENCE ORGANIZATION_ID_SEQ;
CREATE SEQUENCE PERSON_ID_SEQ;
CREATE SEQUENCE PROCEDURE_ID_SEQ;
CREATE SEQUENCE PROCEDURE_TYPE_ID_SEQ;
CREATE SEQUENCE PROJECT_ID_SEQ;
CREATE SEQUENCE PROPERTY_TYPE_ID_SEQ;
CREATE SEQUENCE SAMPLE_COMPONENT_ID_SEQ;
CREATE SEQUENCE SAMPLE_ID_SEQ;
CREATE SEQUENCE SAMPLE_INPUT_ID_SEQ;
CREATE SEQUENCE SAMPLE_TYPE_ID_SEQ;
CREATE SEQUENCE SCMA_ID_SEQ;
