create schema if not exists crm;

-- crm.notifications
-- DROP TABLE IF EXISTS crm.notifications;
CREATE TABLE IF NOT EXISTS crm.notifications();
ALTER TABLE crm.notifications DROP CONSTRAINT IF EXISTS crm_notifications_pkey;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS notification_id text NOT NULL DEFAULT next_id();

-- drop deprecated columns
ALTER TABLE crm.notifications DROP COLUMN IF EXISTS notification_user_id;
ALTER TABLE crm.notifications DROP COLUMN IF EXISTS notification_type;
ALTER TABLE crm.notifications DROP COLUMN IF EXISTS notification_status;

-- add actual columns
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS addressee_id text;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS read boolean;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS sent boolean;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS subject text;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS body text;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS link text;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS author_id text;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS entity_id text;

ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS uid text;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS files json;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS cdate timestamp without time zone DEFAULT (now())::timestamp without time zone;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE crm.notifications ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;
ALTER TABLE crm.notifications ADD CONSTRAINT crm_notifications_pkey PRIMARY KEY (notification_id);

COMMENT ON COLUMN crm.notifications.addressee_id is 'ID користувача отримувача повідомлення';
COMMENT ON COLUMN crm.notifications.read is 'Чи було повідомлення прочитане';
COMMENT ON COLUMN crm.notifications.sent is 'Чи було повідомлення відправлене';
COMMENT ON COLUMN crm.notifications.subject is 'Тема повідомлення';
COMMENT ON COLUMN crm.notifications.body is 'Зміст повідомлення';
COMMENT ON COLUMN crm.notifications.link is 'Посилання на об''єкт';
COMMENT ON COLUMN crm.notifications.author_id is 'ID користувача автора повідомлення';
COMMENT ON COLUMN crm.notifications.entity_id is 'ID на об''єкту';

-- crm.files
-- DROP TABLE IF EXISTS crm.files;
CREATE TABLE IF NOT EXISTS crm.files();
ALTER TABLE crm.files DROP CONSTRAINT IF EXISTS crm_files_pkey;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS file_id text NOT NULL DEFAULT next_id();

ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS uploaded_name text;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS entity_id text; -- object_id
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS entity_type text; -- table_name
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS file_type text;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS file_status numeric;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS doc_type text;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS subject text; -- title
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS size numeric;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS ext text;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS tags text[];
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS file_path text;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS ismain boolean default false;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS isverified boolean default false;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS resized boolean;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS uid text;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS files json;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS cdate timestamp without time zone DEFAULT (now())::timestamp without time zone;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE crm.files ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;
ALTER TABLE crm.files ADD CONSTRAINT crm_files_pkey PRIMARY KEY (file_id);

-- crm.communications
-- DROP TABLE IF EXISTS crm.communications;
CREATE TABLE IF NOT EXISTS crm.communications();
ALTER TABLE crm.communications DROP CONSTRAINT IF EXISTS crm_communications_pkey;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS communication_id text NOT NULL DEFAULT next_id();

ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS entity_id text; -- object_id
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS entity_type text; -- table_name
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS sender text;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS channel text;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS participant text;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS communication_date date;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS expiration_date date;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS announcement text;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS subject text; -- title
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS body text;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS mark numeric;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS parent_id text;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS target_id text[];
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS enabled boolean;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS uid text;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS files json;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS cdate timestamp without time zone DEFAULT (now())::timestamp without time zone;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE crm.communications ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;
ALTER TABLE crm.communications ADD CONSTRAINT crm_communications_pkey PRIMARY KEY (communication_id);

-- crm.checklists
-- DROP TABLE IF EXISTS crm.checklists;
CREATE TABLE IF NOT EXISTS crm.checklists();
ALTER TABLE crm.checklists DROP CONSTRAINT IF EXISTS crm_checklists_pkey;
ALTER TABLE crm.checklists ADD COLUMN IF NOT EXISTS checklist_id text NOT NULL DEFAULT next_id();

ALTER TABLE crm.checklists ADD COLUMN IF NOT EXISTS entity_id text; -- object_id
ALTER TABLE crm.checklists ADD COLUMN IF NOT EXISTS entity_type text; -- table_name
ALTER TABLE crm.checklists ADD COLUMN IF NOT EXISTS subject text; -- title
alter table crm.checklists add column IF NOT EXISTS is_done boolean default false;
alter table crm.checklists add column IF NOT EXISTS done_date date;
alter table crm.checklists add column IF NOT EXISTS category text;
alter table crm.checklists add column IF NOT EXISTS body text;
alter table crm.checklists add column IF NOT EXISTS parent_id text;
alter table crm.checklists add column IF NOT EXISTS flag boolean;

ALTER TABLE crm.checklists ADD COLUMN IF NOT EXISTS uid text;
ALTER TABLE crm.checklists ADD COLUMN IF NOT EXISTS files json;
ALTER TABLE crm.checklists ADD COLUMN IF NOT EXISTS cdate timestamp without time zone DEFAULT (now())::timestamp without time zone;
ALTER TABLE crm.checklists ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE crm.checklists ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;
ALTER TABLE crm.checklists ADD CONSTRAINT crm_checklists_pkey PRIMARY KEY (checklist_id);

-- DROP TABLE crm.properties;
CREATE TABLE IF NOT EXISTS crm.properties();
ALTER TABLE crm.properties DROP CONSTRAINT IF EXISTS crm_properties_pkey;
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS property_id text NOT NULL DEFAULT next_id();

ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS table_name text;
COMMENT ON COLUMN crm.properties.table_name IS 'Таблиця';
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS object_id text;
COMMENT ON COLUMN crm.properties.object_id IS 'ID Об''єкту';
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS property_key text;
COMMENT ON COLUMN crm.properties.object_id IS 'Назва атрибуту';
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS property_text text;
COMMENT ON COLUMN crm.properties.property_text IS 'Текст';
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS property_int integer;
COMMENT ON COLUMN crm.properties.property_int IS 'Integer';
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS property_json json;
COMMENT ON COLUMN crm.properties.property_json IS 'JSON';
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS property_date date;
COMMENT ON COLUMN crm.properties.property_json IS 'Дата';
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS property_type text;
COMMENT ON COLUMN crm.properties.property_type IS 'Тип даних';

ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS uid text NOT NULL DEFAULT '1'::text;
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS cdate timestamp without time zone DEFAULT now();
ALTER TABLE crm.properties ADD COLUMN IF NOT EXISTS files json;

ALTER TABLE crm.properties ADD CONSTRAINT crm_properties_pkey PRIMARY KEY(property_id);
