create schema if not exists bi;

CREATE TABLE if not exists bi.dashboard ();
alter table bi.dashboard drop constraint if exists dashboard_id_pkey cascade;

alter table bi.dashboard add column if not exists dashboard_id text;
alter table bi.dashboard alter column dashboard_id set not null;
alter table bi.dashboard alter column dashboard_id set default next_id();

alter table bi.dashboard add column if not exists title text;
alter table bi.dashboard add column if not exists description text;
alter table bi.dashboard add column if not exists widgets json;
alter table bi.dashboard add column if not exists table_name text;
alter table bi.dashboard add column if not exists style json;
alter table bi.dashboard add column if not exists name text;
alter table bi.dashboard alter column name set not null;
alter table bi.dashboard add column if not exists panels json;
alter table bi.dashboard add column if not exists grid json;
alter table bi.dashboard add column if not exists filters json;
alter table bi.dashboard add column if not exists words text;
alter table bi.dashboard add column if not exists public boolean;
alter table bi.dashboard add column if not exists db text;
alter table bi.dashboard add column if not exists uid text;
alter table bi.dashboard add column if not exists cdate timestamp without time zone;
alter table bi.dashboard add column if not exists source text;

alter table bi.dashboard add CONSTRAINT dashboard_id_pkey PRIMARY KEY (dashboard_id);

COMMENT ON TABLE bi.dashboard IS 'Дашборди';
COMMENT ON COLUMN bi.dashboard.dashboard_id IS 'PK';
COMMENT ON COLUMN bi.dashboard.title IS 'Заголовок';
COMMENT ON COLUMN bi.dashboard.description IS 'Опис';
COMMENT ON COLUMN bi.dashboard.widgets IS 'Віджети';
COMMENT ON COLUMN bi.dashboard.table_name IS 'Назва таблиці';
COMMENT ON COLUMN bi.dashboard.style IS 'Стилі';
COMMENT ON COLUMN bi.dashboard.name IS 'Назва';
COMMENT ON COLUMN bi.dashboard.panels IS 'Панелі віджетів';
COMMENT ON COLUMN bi.dashboard.grid IS 'Сітка';
COMMENT ON COLUMN bi.dashboard.filters IS 'Фільтри';
COMMENT ON COLUMN bi.dashboard.words IS 'Ключові слова';
COMMENT ON COLUMN bi.dashboard.public IS 'Публічний доступ';
COMMENT ON COLUMN bi.dashboard.db IS 'БД';
COMMENT ON COLUMN bi.dashboard.uid IS 'User ID';
COMMENT ON COLUMN bi.dashboard.cdate IS 'Creation date';
COMMENT ON COLUMN bi.dashboard.source IS 'Джерело (file)';

CREATE TABLE if not exists bi.widget ();
alter table bi.widget drop constraint if exists widget_id_pk;
alter table bi.widget drop constraint if exists widget_id_fkey;

alter table bi.widget add column if not exists widget_id text;
alter table bi.widget alter column widget_id set not null;
alter table bi.widget alter column widget_id set default next_id();
alter table bi.widget add column if not exists type text;
alter table bi.widget add column if not exists title text;
alter table bi.widget add column if not exists style json;
alter table bi.widget add column if not exists data json;
alter table bi.widget add column if not exists table_name text;
alter table bi.widget add column if not exists dashboard_id text;
alter table bi.widget add column if not exists name text;
alter table bi.widget add column if not exists x text;
alter table bi.widget add column if not exists col numeric;
alter table bi.widget add column if not exists metrics text;
alter table bi.widget add column if not exists yml text;
alter table bi.widget add column if not exists query text;
alter table bi.widget add column if not exists cls text;

alter table bi.widget add CONSTRAINT widget_id_pk PRIMARY KEY (widget_id);
alter table bi.widget add CONSTRAINT widget_id_fkey FOREIGN KEY (dashboard_id) REFERENCES bi.dashboard (dashboard_id);

COMMENT ON TABLE bi.widget IS 'Віджети';
COMMENT ON COLUMN bi.widget.widget_id IS 'PK';
COMMENT ON COLUMN bi.widget.type IS 'Тип';
COMMENT ON COLUMN bi.widget.title IS 'Назва';
COMMENT ON COLUMN bi.widget.style IS 'Стилі';
COMMENT ON COLUMN bi.widget.yml IS 'Yml';
COMMENT ON COLUMN bi.widget.query IS 'Запит до таблиці';
COMMENT ON COLUMN bi.widget.x IS 'Колонка для осі метрик';
COMMENT ON COLUMN bi.widget.cls IS 'Класифікатор метрик';
COMMENT ON COLUMN bi.widget.yml IS 'Yml';
COMMENT ON COLUMN bi.widget.data IS 'Дані';
COMMENT ON COLUMN bi.widget.table_name IS 'Назва таблиці';
COMMENT ON COLUMN bi.widget.dashboard_id IS 'Ідентифікатор дашборду';

create table if not exists bi.cluster();
alter table bi.cluster drop constraint if exists bi_cluster_cluster_id_pkey;
alter table bi.cluster drop constraint if exists bi_cluster_title_type_unique;

alter table bi.cluster add column if not exists cluster_id text;
alter table bi.cluster alter column cluster_id set not null;
alter table bi.cluster alter column cluster_id set default next_id();

alter table bi.cluster alter column cluster_id set default next_id();
ALTER TABLE bi.cluster ADD COLUMN IF NOT EXISTS title text;
ALTER TABLE bi.cluster ADD COLUMN IF NOT EXISTS type text;
ALTER TABLE bi.cluster ADD COLUMN IF NOT EXISTS codifier text;
ALTER TABLE bi.cluster ADD COLUMN IF NOT EXISTS geom geometry(MultiPolygon,4326);

ALTER TABLE bi.cluster ADD COLUMN IF NOT EXISTS uid text;
ALTER TABLE bi.cluster ADD COLUMN IF NOT EXISTS files json;
ALTER TABLE bi.cluster ADD COLUMN IF NOT EXISTS cdate timestamp without time zone;
alter table bi.cluster alter column cdate set DEFAULT (now())::timestamp without time zone;
ALTER TABLE bi.cluster ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE bi.cluster ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;

ALTER TABLE bi.cluster ADD CONSTRAINT bi_cluster_cluster_id_pkey PRIMARY KEY (cluster_id);
ALTER TABLE bi.cluster ADD CONSTRAINT bi_cluster_title_type_unique UNIQUE(title, type);

comment on table bi.cluster is 'Дані для побудови cluster віджетів';

comment on column bi.cluster.title is 'Назва';
comment on column bi.cluster.type is 'Тип кластеру';