CREATE SCHEMA agenda;

CREATE TABLE agenda.calendar (
	id SERIAL PRIMARY KEY,
	title VARCHAR(100) NULL,
	color INTEGER NULL,
	user_id INTEGER NULL,
	physician_id INTEGER NULL,
	created TIMESTAMP NULL,
	created_by INTEGER,
	modified TIMESTAMP,
	modified_by INTEGER,
	doctena_login TEXT,
	doctena_password TEXT,
	doctena_lastsync TIMESTAMP
);

ALTER TABLE agenda.calendar ADD CONSTRAINT fk_calendar_created_by
	FOREIGN KEY (created_by) REFERENCES usermanagement.gecamed_user(id) ON DELETE NO ACTION;

ALTER TABLE agenda.calendar ADD CONSTRAINT fk_calendar_user
	FOREIGN KEY (user_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE NO ACTION;

ALTER TABLE agenda.calendar ADD CONSTRAINT fk_calendar_physician
	FOREIGN KEY (physician_id) REFERENCES office.physician(id) ON DELETE CASCADE;



/* *************************************************************** */

CREATE TABLE agenda.appointment_type (
	id SERIAL PRIMARY KEY,
	name VARCHAR(30) NOT NULL,
	color INTEGER NULL,
	icon VARCHAR(100),
	type_class INTEGER DEFAULT 0,
	duration INTEGER NULL,
	calendar_id INTEGER NULL,
	deprecated BOOLEAN DEFAULT 'false'
);
ALTER TABLE agenda.appointment_type ADD CONSTRAINT fk_appointment_type_calendar
	FOREIGN KEY (calendar_id) REFERENCES agenda.calendar(id) ON DELETE CASCADE;

/* *************************************************************** */


CREATE TABLE agenda.appointment (
	id SERIAL PRIMARY KEY,
	type_id INTEGER NOT NULL,
	calendar_id INTEGER NOT NULL,
	start_date TIMESTAMP NOT NULL,
	end_date TIMESTAMP NOT NULL,
	patient_id INTEGER NULL,
	summary VARCHAR(100) NULL,
	description TEXT NULL,
	frequency INTEGER NULL,
	recuring_rule TEXT NULL,
	until DATE NULL,
	status INTEGER NULL,
	private BOOL DEFAULT 'false',
	created TIMESTAMP NULL,
	created_by INTEGER,
	is_background BOOL DEFAULT 'false',
	modified TIMESTAMP,
	modified_by INTEGER
);

ALTER TABLE agenda.appointment ADD CONSTRAINT fk_appoint_calendar
	FOREIGN KEY (calendar_id) REFERENCES agenda.calendar(id) ON DELETE CASCADE;

ALTER TABLE agenda.appointment ADD CONSTRAINT fk_appoint_patient
	FOREIGN KEY (patient_id) REFERENCES patient.patient(id) ON DELETE CASCADE;

ALTER TABLE agenda.appointment ADD CONSTRAINT fk_appoint_created_by
	FOREIGN KEY (created_by) REFERENCES usermanagement.gecamed_user(id) ON DELETE NO ACTION;
	
ALTER TABLE agenda.appointment ADD CONSTRAINT fk_appointment_appointment_type
	FOREIGN KEY (type_id) REFERENCES agenda.appointment_type(id) ON DELETE NO ACTION;

/* *************************************************************** */


/******************** Add Table: agenda.rel_user_calendar_selection ************************/
CREATE TABLE agenda.rel_user_calendar_selection (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NULL,
    calendar_id INTEGER NULL
);

ALTER TABLE agenda.rel_user_calendar_selection ADD CONSTRAINT fk_calendar_created_by
	FOREIGN KEY (user_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE CASCADE;

ALTER TABLE agenda.rel_user_calendar_selection ADD CONSTRAINT fk_calendar_user
	FOREIGN KEY (calendar_id) REFERENCES agenda.calendar(id) ON DELETE CASCADE;
	
/********* add column to distinct calendar and background calendar - 1.2.00_011 **********/
ALTER TABLE agenda.calendar ADD COLUMN is_background BOOL default false;



CREATE INDEX idx_agenda_appointment_calendar_id
	ON agenda.appointment USING btree (calendar_id);
CREATE INDEX idx_agenda_appointment_patient_id
	ON agenda.appointment USING btree (patient_id);