CREATE SCHEMA formeditor;

CREATE TABLE formeditor.form_template
(
    id 					serial primary KEY,
    name 				varchar(40),
    xml 				text,
    xsl_history 		text,
    xsl_printing 		text,
    use_only_one_xsl 	boolean default false,
    version 			varchar(20),
    description 		text,
    jasper_template_id 	integer,
    subform_only 		boolean default false
);
ALTER TABLE formeditor.form_template ADD CONSTRAINT fk_formeditor_form_template_jasper_template_id
	foreign KEY(jasper_template_id) REFERENCES core.templates(id) ON delete set null;


CREATE TABLE formeditor.form
(
    id 					serial primary KEY,
    template_id 		integer NOT NULL,
    patient_id 			integer NOT NULL,
    incident_entry_id 	integer,
    subform_of 			integer default NULL,
    date_last_changed 	timestamp,
    date_created 		timestamp default CURRENT_TIMESTAMP
);
ALTER TABLE formeditor.form ADD CONSTRAINT fk_formeditor_form_template_id
	foreign KEY(template_id) REFERENCES formeditor.form_template(id) ON delete cascade;
ALTER TABLE formeditor.form ADD CONSTRAINT fk_formeditor_form_patient_id
	foreign KEY(patient_id) REFERENCES patient.patient(id) ON delete cascade;
ALTER TABLE formeditor.form ADD CONSTRAINT fk_formeditor_form_incident_entry_id
	foreign KEY(incident_entry_id) REFERENCES patient.incident_entry(id) ON delete cascade;
ALTER TABLE formeditor.form ADD CONSTRAINT fk_formeditor_form_subform_of
	foreign KEY(subform_of) REFERENCES formeditor.form(id) ON delete cascade;


CREATE TABLE formeditor.form_value
(
    id serial 	primary KEY,
    form_id 	integer NOT NULL,
    key_name 	varchar(40),
    value 		text
);
ALTER TABLE formeditor.form_value ADD CONSTRAINT fk_formeditor_form_value_form_id
    foreign KEY(form_id) REFERENCES formeditor.form(id) ON delete cascade;
    
    


CREATE INDEX idx_formeditor_form_patient_template_id
ON formeditor.form USING btree (patient_id, template_id);
CREATE INDEX idx_formeditor_form_incident_entry_id
ON formeditor.form USING btree (incident_entry_id);
CREATE INDEX idx_formeditor_form_subform_of
ON formeditor.form USING btree (subform_of);
CREATE INDEX idx_formeditor_form_value_form_id
ON formeditor.form_value USING btree (form_id);