create schema if not exists admin;
create schema if not exists crm;

CREATE EXTENSION if not exists pg_trgm SCHEMA public VERSION "1.5";

-- drop old
-- DROP SCHEMA IF EXISTS setting cascade;

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

ALTER TABLE admin.properties ADD COLUMN IF NOT EXISTS property_entity text;
COMMENT ON COLUMN admin.properties.property_entity IS 'Сутність';
ALTER TABLE admin.properties ADD COLUMN IF NOT EXISTS property_key text;
COMMENT ON COLUMN admin.properties.property_key IS 'Ключ';
ALTER TABLE admin.properties ADD COLUMN IF NOT EXISTS property_text text;
COMMENT ON COLUMN admin.properties.property_text IS 'Текстове значення налаштування';
ALTER TABLE admin.properties ADD COLUMN IF NOT EXISTS property_int integer;
COMMENT ON COLUMN admin.properties.property_int IS 'Цілочислове значения';
ALTER TABLE admin.properties ADD COLUMN IF NOT EXISTS property_json json;
COMMENT ON COLUMN admin.properties.property_json IS 'Значення налаштування';
ALTER TABLE admin.properties ADD COLUMN IF NOT EXISTS level text;
COMMENT ON COLUMN admin.properties.level IS 'Рівень (user/system)';
ALTER TABLE admin.properties ADD COLUMN IF NOT EXISTS object_id text;
COMMENT ON COLUMN admin.properties.object_id IS 'ID Об''єкту';

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

ALTER TABLE admin.properties ADD CONSTRAINT admin_properties_property_id_pkey PRIMARY KEY(property_id);

COMMENT ON TABLE admin.properties IS 'Налаштування';

CREATE TABLE IF NOT EXISTS admin.custom_column();
ALTER TABLE admin.custom_column DROP CONSTRAINT IF EXISTS admin_custom_column_column_id_pkey cascade;
alter table admin.custom_column drop constraint if exists admin_custom_column_name_entity_uid_unique;

ALTER TABLE admin.custom_column ADD COLUMN IF NOT EXISTS column_id text;
ALTER TABLE admin.custom_column ALTER COLUMN column_id SET NOT NULL;
ALTER TABLE admin.custom_column ALTER COLUMN column_id SET DEFAULT next_id();

ALTER TABLE admin.custom_column ADD COLUMN IF NOT EXISTS name text;
ALTER TABLE admin.custom_column ADD COLUMN IF NOT EXISTS entity text;
ALTER TABLE admin.custom_column ADD COLUMN IF NOT EXISTS title text;
ALTER TABLE admin.custom_column ADD COLUMN IF NOT EXISTS format text;
ALTER TABLE admin.custom_column ADD COLUMN IF NOT EXISTS data text;
ALTER TABLE admin.custom_column ADD COLUMN IF NOT EXISTS filter boolean;
ALTER TABLE admin.custom_column ADD COLUMN IF NOT EXISTS _table boolean;

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

ALTER TABLE admin.custom_column ADD CONSTRAINT admin_custom_column_column_id_pkey PRIMARY KEY(column_id);
ALTER TABLE admin.custom_column ADD CONSTRAINT admin_custom_column_name_entity_uid_unique UNIQUE (name,entity,uid);

COMMENT ON TABLE admin.custom_column IS 'Налаштування таблиць користувача';
COMMENT ON COLUMN admin.custom_column.name IS 'Ключ';
COMMENT ON COLUMN admin.custom_column.entity IS 'Таблиця';
COMMENT ON COLUMN admin.custom_column.title IS 'Підпис';
COMMENT ON COLUMN admin.custom_column.format IS 'Формат';
COMMENT ON COLUMN admin.custom_column.data IS 'Класифікатор';
COMMENT ON COLUMN admin.custom_column.filter IS 'Виводити у фільтрах';
COMMENT ON COLUMN admin.custom_column._table IS 'Відображати у таблиці';

CREATE TABLE if not exists crm.extra_data();
ALTER TABLE crm.extra_data DROP CONSTRAINT IF EXISTS crm_extra_data_pkey;
ALTER TABLE crm.extra_data DROP CONSTRAINT IF EXISTS crm_extra_data_property_id_fkey;
ALTER TABLE crm.extra_data DROP CONSTRAINT IF EXISTS crm_extra_data_property_key_object_id_entity_unique;

ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS extra_data_id text;
ALTER TABLE crm.extra_data ALTER COLUMN extra_data_id SET NOT NULL;
ALTER TABLE crm.extra_data ALTER COLUMN extra_data_id SET DEFAULT next_id();

ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS property_id text;
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS property_key text;
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS property_entity text;
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS object_id text;
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS value_text text;
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS value_date timestamp without time zone;
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS value_array text[];
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS uid text;
ALTER TABLE crm.extra_data ALTER COLUMN uid SET DEFAULT '1'::text;
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS cdate timestamp without time zone;
ALTER TABLE crm.extra_data ALTER COLUMN cdate SET DEFAULT date_trunc('seconds'::text, now());
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;
ALTER TABLE crm.extra_data ADD COLUMN IF NOT EXISTS files json;

ALTER TABLE crm.extra_data ADD CONSTRAINT crm_extra_data_pkey PRIMARY KEY (extra_data_id);
-- delete from crm.extra_data where property_id not in (select column_id from admin.custom_column);
ALTER TABLE crm.extra_data ADD CONSTRAINT crm_extra_data_property_id_fkey FOREIGN KEY (property_id) REFERENCES admin.custom_column (column_id) MATCH SIMPLE;
ALTER TABLE crm.extra_data ADD CONSTRAINT crm_extra_data_property_key_object_id_entity_unique UNIQUE (property_key, object_id, property_entity);

COMMENT ON TABLE crm.extra_data IS 'Значення користувацьких полів';
COMMENT ON COLUMN crm.extra_data.property_id IS 'ID поля користувача';
COMMENT ON COLUMN crm.extra_data.property_key IS 'Назва колонки';
COMMENT ON COLUMN crm.extra_data.property_entity IS 'Таблиця';
COMMENT ON COLUMN crm.extra_data.object_id IS 'Ідентифікатор об''єкту';
COMMENT ON COLUMN crm.extra_data.value_text IS 'Значення';
COMMENT ON COLUMN crm.extra_data.value_date IS 'Дата';
COMMENT ON COLUMN crm.extra_data.uid IS 'ID користувача';

CREATE INDEX IF NOT EXISTS crm_extra_data_cdate_btree_idx ON crm.extra_data USING btree (cdate);
CREATE INDEX IF NOT EXISTS crm_extra_data_property_id_btree_idx ON crm.extra_data USING btree (property_id COLLATE pg_catalog."default");
CREATE INDEX IF NOT EXISTS crm_extra_data_property_entity_btree_idx ON crm.extra_data USING btree (property_entity COLLATE pg_catalog."default");
CREATE INDEX IF NOT EXISTS crm_extra_data_object_id_btree_idx ON crm.extra_data USING btree (object_id COLLATE pg_catalog."default");
