CREATE SCHEMA office;

/******************** Add Table: office.office ************************/
CREATE TABLE office.office
	(
 	id					SERIAL PRIMARY KEY,
    name			VARCHAR(100) ,
    website			VARCHAR(100) ,
    email			VARCHAR(100) ,
    information    TEXT ,
    ucm_code		VARCHAR(50)
    );
    
/******************** Add Table: office.office_phone ************************/
CREATE TABLE office.office_phone
(
    id SERIAL 						PRIMARY KEY,
	number							VARCHAR(40) ,
	type								VARCHAR(20) ,
    office_id 						INTEGER
);
ALTER TABLE office.office_phone ADD CONSTRAINT fk_office_phone_office_id
    FOREIGN KEY (office_id) REFERENCES office.office (id) ON DELETE NO ACTION;
    

/******************** Add Table: office.office_address ************************/
CREATE TABLE office.office_address
(
    id SERIAL PRIMARY KEY,
    office_id INTEGER NULL,
    streetname VARCHAR(50) ,
    streetnumber VARCHAR(10) ,
    zip VARCHAR(10) ,
    locality VARCHAR(50) ,
    country VARCHAR(50) ,
    type VARCHAR(20) ,
	date DATE ,
	locality_id INTEGER
);
ALTER TABLE office.office_address ADD CONSTRAINT fk_office_address_office_id
    FOREIGN KEY (office_id) REFERENCES office.office (id) ON DELETE NO ACTION;
ALTER TABLE office.office_address ADD CONSTRAINT fk_office_address_locality_id
    FOREIGN KEY (locality_id) REFERENCES address.locality (id) ON DELETE NO ACTION;


/******************** Add Table: office.physician_address ************************/
CREATE TABLE office.physician_address
(
    id SERIAL PRIMARY KEY,
    streetname VARCHAR(50) ,
    streetnumber VARCHAR(10) ,
    zip VARCHAR(10) ,
    locality VARCHAR(50) ,
    country VARCHAR(50) ,
    type VARCHAR(20) ,
	date DATE ,
	locality_id INTEGER
);
ALTER TABLE office.physician_address ADD CONSTRAINT fk_physician_address_locality_id
    FOREIGN KEY (locality_id) REFERENCES address.locality (id) ON DELETE NO ACTION;

/******************** Add Table: office.physician ************************/
CREATE TABLE office.physician
	(
 	id						SERIAL PRIMARY KEY,
 	user_id					INTEGER ,
    name					VARCHAR(40) ,
    first_name				VARCHAR(40) ,
    ucm_code				VARCHAR(50) ,
    speciality				TEXT ,
    email					VARCHAR(100) ,
    title					VARCHAR(30) ,
	phone_extension    		VARCHAR(20) ,
	fax					    VARCHAR(20) ,
	gsm					    VARCHAR(20) ,
	color 					INTEGER,
	address_id				INTEGER,
	ucm_code_facturation  	VARCHAR(50)
    );
ALTER TABLE office.physician ADD CONSTRAINT fkphysician_userid
      FOREIGN KEY (user_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE CASCADE;    
ALTER TABLE office.physician ADD CONSTRAINT fk_physician_address_physician_id
    FOREIGN KEY (address_id) REFERENCES office.physician_address (id) ON DELETE NO ACTION;

/*
** CONSTRAINT fk_core_xsl_templates_physician_id is defined here because the installer of GECAMed first create
** all core tables and late the office tables.
*/
ALTER TABLE core.xsl_templates ADD CONSTRAINT fk_core_xsl_templates_physician_id
	FOREIGN KEY (physician_id) REFERENCES office.physician(id) ON DELETE CASCADE;

/******************** Add Table: office.site_address ************************/
CREATE TABLE office.site_address
(
    id SERIAL PRIMARY KEY,
    streetname VARCHAR(50) ,
    streetnumber VARCHAR(10) ,
    zip VARCHAR(10) ,
    locality VARCHAR(50) ,
    country VARCHAR(50) ,
    type VARCHAR(20) ,
    date DATE ,
    locality_id INTEGER
);
ALTER TABLE office.site_address ADD CONSTRAINT fk_site_address_locality_id
    FOREIGN KEY (locality_id) REFERENCES address.locality (id) ON DELETE NO ACTION;
  
 /******************** Add Table: office.site ************************/
CREATE TABLE office.site
    (
    id                  SERIAL PRIMARY KEY,
    office_id        INTEGER NULL,
    name            VARCHAR(100) ,
    website         VARCHAR(100) ,
    email             VARCHAR(100) ,
    phone            VARCHAR(100) ,
    fax                 VARCHAR(100) ,
    information     TEXT ,
    ucm_code        VARCHAR(50),
    address_id              INTEGER
    );

ALTER TABLE office.site ADD CONSTRAINT fk_site_office_id
    FOREIGN KEY (office_id) REFERENCES office.office (id) ON DELETE NO ACTION;


/******************** ADD FOREIGN KEYS OF OTHER SCHEMAS ************************/

ALTER TABLE core.external_password_store ADD CONSTRAINT fk_external_password_store_physician_id
      FOREIGN KEY (physician_id) REFERENCES office.physician(id) ON DELETE CASCADE;