CREATE SCHEMA usermanagement;

/******************** Add Table: usermanagement.permission ************************/

CREATE TABLE usermanagement.permission
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    description TEXT NULL,
    CONSTRAINT unique_permission_name UNIQUE (NAME)
);


/******************** Add Table: usermanagement.role ************************/

CREATE TABLE usermanagement.role
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    description TEXT NULL,
    CONSTRAINT unique_role_name UNIQUE (NAME)
 );


/******************** Add Table: usermanagement.rel_role_permission ************************/

CREATE TABLE usermanagement.rel_role_permission
(
    id 			 SERIAL PRIMARY KEY,
    role_id       INTEGER NOT NULL,
    permission_id INTEGER NOT NULL
);

ALTER TABLE usermanagement.rel_role_permission ADD CONSTRAINT fk_rel_role_permission_role_id
    FOREIGN KEY (role_id) REFERENCES usermanagement.role (id) ON DELETE CASCADE;

ALTER TABLE usermanagement.rel_role_permission ADD CONSTRAINT fk_role_permission_permission_id
    FOREIGN KEY (permission_id) REFERENCES usermanagement.permission (id) ON DELETE CASCADE;

CREATE UNIQUE INDEX idx_role_permission	ON usermanagement.rel_role_permission	(role_id,permission_id);

/******************** Add Table: usermanagement.gecamed_user **********************/

CREATE TABLE usermanagement.gecamed_user
(
    id SERIAL PRIMARY KEY,
    login VARCHAR(30) NULL,
    passwordhash VARCHAR(50) NULL,
    name VARCHAR(60) NULL,
    language VARCHAR(20) NULL,
    last_modification TIMESTAMP NULL,
    modified_by INTEGER NULL ,
    password_date TIMESTAMP NULL ,
    locale_id INTEGER
);
ALTER TABLE usermanagement.gecamed_user ADD CONSTRAINT fk_usermanagement_gecamed_user_locale
    FOREIGN KEY (locale_id) REFERENCES core.locale (id);

/*
** CONSTRAINT fk_core_autocompletion_user_id is defined here because the installer of GECAMed first create
** all core tables and late the usermanagement tables.
*/
ALTER TABLE core.autocompletion ADD CONSTRAINT fk_core_autocompletion_user_id
	FOREIGN KEY (user_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE SET NULL;

/******************** Add Table: usermanagement.setting ************************/

CREATE TABLE usermanagement.setting
(
    id SERIAL PRIMARY KEY,
    user_id INTEGER ,
    xml_config TEXT NULL
);

/******************** Add Table: usermanagement.rel_user_permission ************************/

CREATE TABLE usermanagement.rel_user_permission
(
    id 			 SERIAL PRIMARY KEY,
    user_id 		 INTEGER NOT NULL,
    permission_id INTEGER NOT NULL
);

ALTER TABLE usermanagement.rel_user_permission ADD CONSTRAINT fk_rel_user_permission_user_id
    FOREIGN KEY (user_id) REFERENCES usermanagement.gecamed_user (id) ON DELETE CASCADE;

ALTER TABLE usermanagement.rel_user_permission ADD CONSTRAINT fk_rel_user_permission_permission_id
    FOREIGN KEY (permission_id) REFERENCES usermanagement.permission (id) ON DELETE CASCADE;

CREATE UNIQUE INDEX idx_user_permission	ON usermanagement.rel_user_permission	(user_id,permission_id);


/******************** Add Table: usermanagement.rel_user_role ************************/

CREATE TABLE usermanagement.rel_user_role
(
    id 			 SERIAL PRIMARY KEY,
    user_id		INTEGER NOT NULL,
    role_id 		INTEGER NOT NULL
);

ALTER TABLE usermanagement.rel_user_role ADD CONSTRAINT fk_rel_user_role_user_id
    FOREIGN KEY (user_id) REFERENCES usermanagement.gecamed_user (id) ON DELETE CASCADE;

ALTER TABLE usermanagement.rel_user_role ADD CONSTRAINT fk_rel_user_role_role_id
    FOREIGN KEY (role_id) REFERENCES usermanagement.role (id) ON DELETE CASCADE;

CREATE UNIQUE INDEX idx_user_role	ON usermanagement.rel_user_role	(user_id,role_id);


/******************** Add Table: usermanagement.message ************************/

CREATE TABLE usermanagement.message
(
    id SERIAL PRIMARY KEY,
    sender_id INTEGER NULL,
    receiver_id INTEGER NULL,
    message TEXT NULL,
    date TIMESTAMP NULL,
    read BOOLEAN DEFAULT 'false',
    important BOOLEAN DEFAULT 'false',
    all_user BOOLEAN DEFAULT 'false'
);

ALTER TABLE usermanagement.message ADD CONSTRAINT fk_message_sender_id
    FOREIGN KEY (sender_id) REFERENCES usermanagement.gecamed_user (id);

ALTER TABLE usermanagement.message ADD CONSTRAINT fk_message_receiver_id
    FOREIGN KEY (receiver_id) REFERENCES usermanagement.gecamed_user (id);


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

ALTER TABLE core.external_password_store ADD CONSTRAINT fk_external_password_store_user_id
      FOREIGN KEY (user_id) REFERENCES usermanagement.gecamed_user(id) ON DELETE CASCADE;