CREATE SCHEMA core;

/******************** Add Table: core.gender ************************/
CREATE TABLE core.gender
	(
 	id					SERIAL PRIMARY KEY,
    value			VARCHAR 
    );

/******************** Add Table: core.language ************************/
CREATE TABLE core.language
	(
 	id					SERIAL PRIMARY KEY,
    value			VARCHAR 
    );
    
/******************** Add Table: core.marital_status ************************/
CREATE TABLE core.marital_status
	(
 	id					SERIAL PRIMARY KEY,
    value			VARCHAR 
    );

/******************** Add Table: core.nationality ************************/
CREATE TABLE core.nationality
	(
 	id					SERIAL PRIMARY KEY,
    value			VARCHAR 
    );
    
/******************** Add Table: core.locale ************************/
CREATE TABLE core.locale
	(
 	id					SERIAL PRIMARY KEY,
    language			VARCHAR ,
    country			VARCHAR 
    );

/******************** Add Table: core.title ************************/
CREATE TABLE core.title
	(
 	id					SERIAL PRIMARY KEY,
    value			VARCHAR 
    );
    
/******************** Add Table: core.dr_title ************************/
CREATE TABLE core.dr_title
	(
 	id					SERIAL PRIMARY KEY,
    value			VARCHAR 
    );
    
/******************** Add Table: core.phone_type ************************/
CREATE TABLE core.phone_type
	(
 	id					SERIAL PRIMARY KEY,
    value			VARCHAR 
    );
    
/******************** Add Table: core.template_type ************************/
CREATE TABLE core.template_type
	(
 	id					SERIAL PRIMARY KEY,
    sequence_number 	INTEGER DEFAULT 0,
    value				VARCHAR,
    template_file_path VARCHAR
);
    
/******************** Add Table: core.national_holiday ************************/
CREATE TABLE core.national_holiday
	(
 	id					SERIAL PRIMARY KEY,
    day					date,
    description			VARCHAR,
    legal				boolean 
    );

/****************** Add Table: core.log ***********************************/
CREATE TABLE core.log (
    id  SERIAL PRIMARY KEY,
    time    TIMESTAMP NOT NULL,
    user_id    INTEGER NULL,
    username VARCHAR NULL,
    type_id INTEGER NOT NULL,
    operation VARCHAR NULL,
    module  VARCHAR NULL,
    text    TEXT,
    host VARCHAR,
    site VARCHAR,
    duration int8
);

/***************** Add Table: core.log_types ******************************/
CREATE TABLE core.log_types (
    id SERIAL PRIMARY KEY,
    name    VARCHAR
);

/******************** Add Table:core.templates ************************/
CREATE TABLE core.templates
	(
 	id					SERIAL PRIMARY KEY,
   	type				VARCHAR ,
    name			VARCHAR ,
    physician		INTEGER ,
    expiry			DATE NULL ,
    jrxml			 	BYTEA NULL ,
    jasper		 	BYTEA NULL 
);
    
/*************** FOREIGN KEY log type -> log **********************/
ALTER TABLE core.log ADD CONSTRAINT fk_log_log_type_id
    FOREIGN KEY (type_id) REFERENCES core.log_types(id) ON DELETE CASCADE;


CREATE TABLE core.info
(
   id serial PRIMARY KEY not null,
   date timestamp not null,
   key VARCHAR not null,
   value text
);

/******************** Add Table:core.autocompletion ************************/
CREATE TABLE core.autocompletion
(
	id 					SERIAL PRIMARY KEY,
	context 			VARCHAR NOT NULL,
	short_cut 			VARCHAR DEFAULT NULL,
	replacement_text 	TEXT,
	user_id 			INTEGER DEFAULT NULL,
	created 			TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	modified 			TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	source				TEXT
);

/*
** Table core.autocompletion has a CONSTRAINT fk_core_autocompletion_user_id which is defined in the package
** usermanagement because the installer of GECAMed first create all core tables and late the usermanagement tables.
*/

/******************** Add Table:core.xsl_templates ************************/
CREATE TABLE core.xsl_templates
(
	id 				SERIAL 	PRIMARY KEY,
	name 			TEXT 	DEFAULT NULL,
	data 			TEXT 	DEFAULT NULL,
	use_for_history BOOLEAN DEFAULT FALSE,
	use_to_edit 	BOOLEAN DEFAULT FALSE,
	use_to_print 	BOOLEAN DEFAULT FALSE,
	reference_type 	TEXT 	DEFAULT NULL,
	reference_id 	INTEGER DEFAULT NULL,
	physician_id 	INTEGER DEFAULT NULL
);

/*
** Table core.xsl_templates has a CONSTRAINT CONSTRAINT fk_core_xsl_templates_physician_id which is defined in 
** the package office because the installer of GECAMed first create all core tables and late the office tables.
*/




CREATE INDEX idx_core_autocompletion_context_user_id
ON core.autocompletion USING btree (context, user_id);

/********* add a new table a the external password store **********/
CREATE TABLE core.external_password_store
(
	id				SERIAL 	PRIMARY KEY,
 	user_id			INTEGER	DEFAULT NULL,
 	physician_id	INTEGER DEFAULT NULL,
	type			TEXT	DEFAULT NULL,
    key				TEXT	DEFAULT NULL,
	value			TEXT	DEFAULT NULL
);
