CREATE SCHEMA dicom;

/******************** Add Table: dicom.study ************************/
CREATE TABLE dicom.study (
    id          SERIAL PRIMARY KEY,
    patient_id  INTEGER,
    incident_entry_id INTEGER,
    instance_uid VARCHAR(100),
    number    INTEGER,
    description TEXT,
    institution TEXT,
    date        TIMESTAMP
);
ALTER TABLE dicom.study ADD CONSTRAINT fk_dicom_study_patient
    FOREIGN KEY (patient_id) REFERENCES patient.patient(id) ON DELETE CASCADE;

/******************** Add Table: dicom.series ************************/
CREATE TABLE dicom.series (
    id          SERIAL PRIMARY KEY,
    study_id    INTEGER,
    incident_entry_id INTEGER,
    instance_uid VARCHAR(100),
    number      INTEGER,
    description TEXT,
    modality    VARCHAR(50),
    protocol    TEXT,
    physician VARCHAR(200),
    date        TIMESTAMP
);
ALTER TABLE dicom.series ADD CONSTRAINT fk_dicom_series_study
    FOREIGN KEY (study_id) REFERENCES dicom.study(id) ON DELETE CASCADE;

/******************** Add Table: dicom.image ************************/
CREATE TABLE dicom.image (
    id          SERIAL PRIMARY KEY,
    patient_id  INTEGER,
    series_id    INTEGER,
    instance_uid VARCHAR(100),
    number      INTEGER,
    description TEXT,
    modality    VARCHAR(50),
    type        VARCHAR(100),
    date        TIMESTAMP,
    preview     BYTEA,
    rows        INTEGER,
    cols        INTEGER,
    size        BIGINT
);
ALTER TABLE dicom.image ADD CONSTRAINT fk_dicom_image_series
    FOREIGN KEY (series_id) REFERENCES dicom.series(id) ON DELETE CASCADE;
    
INSERT INTO "patient"."incident_entry_types" (name,mime_type) VALUES ('dicom','text/plain');





CREATE INDEX idx_dicom_image_series_id
	ON dicom.image USING btree (series_id);
CREATE INDEX idx_dicom_image_instance_uid
	ON dicom.image USING btree (instance_uid);
CREATE INDEX idx_dicom_series_study_id
	ON dicom.series USING btree (study_id);
CREATE INDEX idx_dicom_series_instance_uid
	ON dicom.series USING btree (instance_uid);
CREATE INDEX idx_dicom_study_patient_id
	ON dicom.study USING btree (patient_id);
CREATE INDEX idx_dicom_study_instance_uid
	ON dicom.study USING btree (instance_uid);