create schema if not exists bi;

CREATE TABLE if not exists bi.dataset ();
alter table bi.dataset drop constraint if exists bi_dataset_id_pkey cascade;

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

alter table bi.dataset add column if not exists name text;
alter table bi.dataset add column if not exists db text;
alter table bi.dataset add column if not exists source_type text;
alter table bi.dataset add column if not exists file_path text;
alter table bi.dataset add column if not exists table_name text;

alter table bi.dataset add column if not exists sql_list json;
alter table bi.dataset add column if not exists column_list json;

alter table bi.dataset add column if not exists uid text;
alter table bi.dataset add column if not exists editor_id text;
alter table bi.dataset add column if not exists editor_date timestamp without time zone;
alter table bi.dataset add column if not exists cdate timestamp without time zone;
alter table bi.dataset alter column cdate set DEFAULT (now())::timestamp without time zone;
ALTER TABLE bi.dataset add column if not exists filter_list json;
ALTER TABLE bi.dataset add column if not exists dataset_file_path text;
ALTER TABLE bi.dataset add column if not exists files json;
ALTER TABLE bi.dataset add column if not exists style json;
ALTER TABLE bi.dataset add column if not exists data_source json;
ALTER TABLE bi.dataset add column if not exists setting json;
ALTER TABLE bi.dataset add column if not exists geom geometry;
ALTER TABLE bi.dataset add column if not exists pk text;
ALTER TABLE bi.dataset add column if not exists query text;
ALTER TABLE bi.dataset add column if not exists form_setting json;
ALTER TABLE bi.dataset add column if not exists access_level text;
ALTER TABLE bi.dataset add column if not exists export_columns text[];
ALTER TABLE bi.dataset add column if not exists dashboard_list text[];

alter table bi.dataset add CONSTRAINT bi_dataset_id_pkey PRIMARY KEY (dataset_id);

COMMENT ON TABLE bi.dataset IS 'Набори даних';

COMMENT ON COLUMN bi.dataset.dataset_id IS 'PK';
COMMENT ON COLUMN bi.dataset.name IS 'Назва';
COMMENT ON COLUMN bi.dataset.file_path IS 'Шлях до імпортованого файлу';
COMMENT ON COLUMN bi.dataset.table_name IS 'Таблиця';

