CREATE SCHEMA cara;

/******************** Add Table: cara.medical_info ************************/
CREATE TABLE cara.medical_info
	(
 	id								SERIAL PRIMARY KEY,
    patient_id					INTEGER,
    allergies					TEXT,
    drug_contraindictions	TEXT,
    implants					TEXT
    );
ALTER TABLE cara.medical_info ADD CONSTRAINT fk_medical_info_patient_id
    FOREIGN KEY (patient_id) REFERENCES patient.patient(id) ON DELETE CASCADE;


/******************** Add Table: cara.study ************************/
CREATE TABLE cara.study
	(
 	id								SERIAL PRIMARY KEY,
	patient_id					INTEGER,
	cara_id						VARCHAR(40),
	hosp_id						VARCHAR(40),
	cara_desc					TEXT,
	hosp_desc				TEXT,
	cara_study_code		TEXT,
	hosp_study_code		TEXT,
	accession_number			VARCHAR(32),
	modality					VARCHAR(25),
	laterality					VARCHAR(25),
	incidence					TEXT,
	date							TIMESTAMP,
	prescription_date		TIMESTAMP,
	prescribing_physician	VARCHAR(50),
	hospital					VARCHAR(50),
	responsable_physician VARCHAR(50),
	events						TEXT,
	contrast_product		VARCHAR(200),
	contrast_product_volume	INTEGER,
	isotope						VARCHAR(200),
	activity						INTEGER,
	dicom_ids					TEXT,
	report						TEXT,
	status						VARCHAR(200)
    );
ALTER TABLE cara.study ADD CONSTRAINT fk_study_patient_id
    FOREIGN KEY (patient_id) REFERENCES patient.patient(id) ON DELETE CASCADE;
    
    
/******************** Add Table: cara.image ************************/
CREATE TABLE cara.image
    (
 	id						SERIAL PRIMARY KEY,
        data                                            BYTEA NULL,
        media_storage_sop_inst_uid                      VARCHAR(100),
        sop_inst_uid                                    VARCHAR(100),
        study_inst_uid                                  VARCHAR(100),
        series_inst_uid                                 VARCHAR(100),
        image_number                                    VARCHAR(10),
        thumbnail 											BYTEA NULL,
        size													VARCHAR(100),
        cols													VARCHAR(100),
        rows													VARCHAR(100)
    );
