CREATE SCHEMA letter;

/******************** Add Table: letter.templates ************************/
CREATE TABLE letter.templates
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(200) NOT NULL,
   created_on TIMESTAMP NOT NULL,
   modified_on TIMESTAMP NOT NULL,
   description VARCHAR(2000),
   physician_id INTEGER NOT NULL,
   generated_filename VARCHAR(200)  NOT NULL, 
   mime_type VARCHAR(100),
   type VARCHAR(100)  NULL,
   created_by INTEGER  NOT NULL,
   modified_by INTEGER  NOT NULL,
   share BOOL NULL,
   addresstype VARCHAR(20) NULL,
   requires_contact BOOLEAN NULL DEFAULT false,
   placeholder_version INTEGER DEFAULT 0
);

/******************** Add Table: letter.letters ************************/
CREATE TABLE letter.letters
(
 id SERIAL PRIMARY KEY,
 incident_entry_id INTEGER,
 mime_type VARCHAR(100),
 type VARCHAR(100)
);


ALTER TABLE letter.letters ADD CONSTRAINT fk_incident_entry
    FOREIGN KEY (incident_entry_id) REFERENCES patient.incident_entry (id) ON DELETE CASCADE;

-- create the DB table for the placeholders
CREATE TABLE letter.placeholders
(
	id		SERIAL PRIMARY KEY,
	name	VARCHAR(100) DEFAULT NULL UNIQUE,
	type	VARCHAR(100) DEFAULT NULL,
	comment	TEXT DEFAULT NULL,
	script	TEXT NOT NULL DEFAULT ''
);


CREATE INDEX idx_letter_letters_incident_entry_id
ON letter.letters USING btree (incident_entry_id);
CREATE INDEX idx_letter_letters_type
ON letter.letters USING btree (type);