DROP TABLE patient_study;
DROP TABLE studies_sites;
DROP TABLE studies;
DROP TABLE patients;
DROP TABLE sites;

CREATE TABLE patients (

    patient_id VARCHAR(15) NOT NULL,

    date_of_birth DATE,

    sex VARCHAR(1) 

);



CREATE TABLE patient_study

(

    patient_id VARCHAR(15) NOT NULL,

    study_id NUMERIC (11) NOT NULL,

    study_site NUMERIC(11),

    date_of_enrollment DATE

);



CREATE TABLE studies

(
  study_name       VARCHAR(50)            NOT NULL,
  study_id         NUMERIC(11)            NOT NULL,
  study_title      VARCHAR(255)           NOT NULL,

  description      VARCHAR(500),

  locked           NUMERIC(1)                    DEFAULT 0,

  date_start       DATE,

  is_active        NUMERIC(1)                    DEFAULT 1,

  manager          VARCHAR(30),

  date_terminated  DATE,

  web_url          VARCHAR(255)

);



CREATE TABLE sites

(
    site VARCHAR(255) NOT NULL,
	site_id NUMERIC(11) NOT NULL

 );

 

 CREATE TABLE studies_sites

 (

    study_id NUMERIC(11) NOT NULL,

    site_id NUMERIC(11) NOT NULL

 );



ALTER TABLE patients ADD 

    CONSTRAINT pk_patient

    PRIMARY KEY (patient_id);



ALTER TABLE patient_study ADD 

    CONSTRAINT pk_patient_study

    PRIMARY KEY (patient_id, study_id);



ALTER TABLE studies ADD 

    CONSTRAINT pk_studies

    PRIMARY KEY (study_id);



ALTER TABLE studies_sites ADD 

    CONSTRAINT pk_studies_sites

    PRIMARY KEY (study_id, site_id);



ALTER TABLE sites ADD 

    CONSTRAINT pk_sites

    PRIMARY KEY (site_id); 



     

ALTER TABLE patient_study ADD 

    CONSTRAINT fk_patient_study_patients

    FOREIGN KEY (patient_id)

    REFERENCES patients (patient_id);



ALTER TABLE patient_study ADD 

    CONSTRAINT fk_patient_study_studies

    FOREIGN KEY (study_id)

    REFERENCES studies (study_id);    



ALTER TABLE patient_study ADD 

    CONSTRAINT fk_patient_study_sites

    FOREIGN KEY (study_site)

    REFERENCES sites (site_id);    



ALTER TABLE studies_sites ADD 

    CONSTRAINT fk_studies_sites_studies

    FOREIGN KEY (study_id)

    REFERENCES studies (study_id);



ALTER TABLE studies_sites ADD 

    CONSTRAINT fk_studies_sites_sites

    FOREIGN KEY (site_id)

    REFERENCES sites (site_id);


COPY patients FROM 'C:/aa/patients.txt' NULL '' ENCODING 'UTF8';
COPY studies FROM 'C:/aa/studies.txt' NULL '' ENCODING 'UTF8';
COPY sites FROM 'C:/aa/sites.txt' NULL '' ENCODING 'UTF8';
COPY patient_study FROM 'C:/aa/patient_study.txt' NULL '' ENCODING 'UTF8';

--naplneni studies_sites
INSERT INTO studies_sites (site_id, study_id)
SELECT distinct study_site, study_id from patient_study