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


-- Admin Menu
-- DROP TABLE if exists admin.menu;
CREATE TABLE if not exists admin.menu();
alter table admin.menu DROP CONSTRAINT if exists admin_menu_id_pkey cascade;
alter table admin.menu DROP CONSTRAINT if exists admin_menu_name_unique;

alter table admin.menu add column if not exists menu_id text NOT NULL default next_id();
alter table admin.menu add column if not exists name text;
alter table admin.menu add column if not exists ord numeric;
alter table admin.menu add column if not exists enabled boolean NOT NULL DEFAULT true;
alter table admin.menu add column if not exists uid text;
alter table admin.menu add column if not exists editor_id text;
alter table admin.menu add column if not exists editor_date timestamp without time zone;
alter table admin.menu add column if not exists cdate timestamp without time zone DEFAULT now();
alter table admin.menu add CONSTRAINT admin_menu_id_pkey PRIMARY KEY (menu_id);
alter table admin.menu add CONSTRAINT admin_menu_name_unique UNIQUE (name);

COMMENT ON TABLE admin.menu IS 'Пункти меню';
COMMENT ON COLUMN admin.menu.menu_id IS 'Ідентифікатор пункту меню';
COMMENT ON COLUMN admin.menu.name IS 'Назва пункту меню';
COMMENT ON COLUMN admin.menu.ord IS 'Порядковий номер';
COMMENT ON COLUMN admin.menu.enabled IS 'On / Off';




-- Role (Group)
-- drop table if exists admin.roles;
CREATE TABLE if not exists admin.roles();
alter table admin.roles DROP CONSTRAINT if exists admin_roles_id_pkey cascade;

ALTER TABLE admin.roles ADD COLUMN if not exists role_id text NOT NULL DEFAULT next_id();
ALTER TABLE admin.roles ADD COLUMN if not exists name text;
ALTER TABLE admin.roles ADD COLUMN if not exists enabled boolean;
ALTER TABLE admin.roles ADD COLUMN if not exists info text;
ALTER TABLE admin.roles ADD COLUMN if not exists cdate timestamp without time zone DEFAULT date_trunc('seconds'::text, now());
ALTER TABLE admin.roles ADD COLUMN if not exists uid text;
ALTER TABLE admin.roles ADD COLUMN if not exists editor_id text;
ALTER TABLE admin.roles ADD COLUMN if not exists editor_date timestamp without time zone;

ALTER TABLE admin.roles ADD CONSTRAINT admin_roles_id_pkey PRIMARY KEY(role_id);

COMMENT ON TABLE admin.roles IS 'Групи';
COMMENT ON COLUMN admin.roles.role_id IS 'ID';
COMMENT ON COLUMN admin.roles.name IS 'Назва групи';
COMMENT ON COLUMN admin.roles.enabled IS 'On / Off';
COMMENT ON COLUMN admin.roles.info IS 'Опис групи';
COMMENT ON COLUMN admin.roles.cdate IS 'Дата створення';
COMMENT ON COLUMN admin.roles.uid IS 'Хто створив';
COMMENT ON COLUMN admin.roles.editor_id IS 'Останній редагувач';
COMMENT ON COLUMN admin.roles.editor_date IS 'Дата останнього редагування';

-- Routes (Interfaces)
-- DROP TABLE if exists admin.routes;
CREATE TABLE if not exists admin.routes();
alter table admin.routes DROP CONSTRAINT if exists admin_route_id_pkey cascade;
alter table admin.routes DROP constraint if exists admin_route_menu_id_fkey cascade;
alter table admin.routes DROP constraint if exists admin_route_alias_query_unique cascade;

alter table admin.routes add column if not exists route_id text;
alter table admin.routes alter column route_id set not null;
alter table admin.routes alter column route_id set default next_id();
alter table admin.routes add column if not exists alias text;
alter table admin.routes alter column alias drop not null;
-- alter table admin.routes alter column alias set not null;
alter table admin.routes add column if not exists table_name text;
alter table admin.routes alter column table_name drop not null;
-- alter table admin.routes alter column table_name set not null;
alter table admin.routes add column if not exists query text;
alter table admin.routes add column if not exists actions text[];
alter table admin.routes add column if not exists access text;
alter table admin.routes add column if not exists title text;
alter table admin.routes add column if not exists public boolean;
alter table admin.routes add column if not exists menu_id text;
alter table admin.routes add column if not exists uid text;
alter table admin.routes add column if not exists editor_id text;
alter table admin.routes add column if not exists editor_date timestamp without time zone;
alter table admin.routes add column if not exists cdate timestamp without time zone DEFAULT now();
alter table admin.routes alter column cdate set DEFAULT now();
alter table admin.routes add column if not exists enabled boolean;
alter table admin.routes alter column enabled set NOT NULL;
alter table admin.routes alter column enabled set DEFAULT true;

alter table admin.routes add CONSTRAINT admin_route_id_pkey PRIMARY KEY (route_id);
alter table admin.routes add constraint admin_route_menu_id_fkey FOREIGN KEY (menu_id) REFERENCES admin.menu(menu_id);

-- alter table admin.routes add CONSTRAINT admin_route_alias_query_unique UNIQUE (alias, query);

COMMENT ON TABLE admin.routes IS 'Список інтерфейсів';
COMMENT ON COLUMN admin.routes.route_id IS 'Ідентифікатор інтерфейса';
COMMENT ON COLUMN admin.routes.alias IS 'Назва файлу інтерфейса';

COMMENT ON COLUMN admin.routes.table_name IS 'Таблиця в БД';
comment on column admin.routes.query is 'Запит до таблиці';
COMMENT ON COLUMN admin.routes.actions IS 'Дії';
COMMENT ON COLUMN admin.routes.access IS 'Рівень доступу';
COMMENT ON COLUMN admin.routes.title IS 'Назва інтерфейсу українською';
COMMENT ON COLUMN admin.routes.public IS 'Ознака чи для всіх показується';
COMMENT ON COLUMN admin.routes.enabled IS 'On / Off';
COMMENT ON COLUMN admin.routes.menu_id IS 'Пункт меню (для collapse)';

-- User <> Role (Group)
-- DROP TABLE if exists admin.user_roles;

CREATE TABLE if not exists admin.user_roles();
ALTER TABLE admin.user_roles DROP CONSTRAINT IF EXISTS admin_user_roles_role_id_pkey;
ALTER TABLE admin.user_roles DROP CONSTRAINT IF EXISTS admin_user_roles_pkey;
ALTER TABLE admin.user_roles DROP CONSTRAINT IF EXISTS admin_user_roles_role_id_fkey;
ALTER TABLE admin.user_roles DROP CONSTRAINT IF EXISTS admin_user_roles_user_uid_fkey;
ALTER TABLE admin.user_roles DROP CONSTRAINT IF EXISTS admin_user_roles_user_uid_role_id_key;

ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS ugr_id text NOT NULL DEFAULT next_id();
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS user_uid text NOT NULL;
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS role_id text NOT NULL;
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS cdate timestamp without time zone DEFAULT date_trunc('seconds'::text, now());
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS uid text;
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS expiration date;
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS access_granted text;
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS access_granted_time timestamp without time zone;
ALTER TABLE admin.user_roles ADD COLUMN IF NOT EXISTS actions text [];

ALTER TABLE admin.user_roles ADD CONSTRAINT admin_user_roles_pkey PRIMARY KEY (ugr_id);
ALTER TABLE admin.user_roles ADD CONSTRAINT admin_user_roles_user_uid_role_id_key UNIQUE (user_uid, role_id);


COMMENT ON TABLE admin.user_roles IS 'Відношення користувачів до груп';
COMMENT ON COLUMN admin.user_roles.user_uid IS 'ID користувача';
COMMENT ON COLUMN admin.user_roles.role_id IS 'ID групи';
COMMENT ON COLUMN admin.user_roles.uid IS 'Користувач, який створив запис в БД';
COMMENT ON COLUMN admin.user_roles.expiration IS 'закінчення терміну дії доступу до групи';
COMMENT ON COLUMN admin.user_roles.access_granted IS 'Ідентифікатор користувача який надав доступ';
COMMENT ON COLUMN admin.user_roles.access_granted_time IS 'Час коли надали доступ';
COMMENT ON COLUMN admin.user_roles.actions IS 'Рівень доступу користувача в межах групи';

CREATE INDEX IF NOT EXISTS admin_user_roles_access_user_uid_idx ON admin.user_roles USING btree (user_uid COLLATE pg_catalog."default");
CREATE INDEX IF NOT EXISTS admin_user_roles_cdate_btree_idx ON admin.user_roles USING btree (cdate);
CREATE INDEX IF NOT EXISTS admin_user_roles_editor_date_btree_idx ON admin.user_roles USING btree (editor_date);
CREATE INDEX IF NOT EXISTS admin_user_roles_user_uid_gin_idx ON admin.user_roles USING gin (user_uid COLLATE pg_catalog."default" gin_trgm_ops);
CREATE INDEX IF NOT EXISTS admin_user_roles_role_id_idx ON admin.user_roles USING btree (role_id COLLATE pg_catalog."default");


-- Route (Interface) <> User / Role (Group)
-- drop table if exists admin.role_access;
drop table if exists admin.access;

CREATE TABLE if not exists admin.role_access();
alter table admin.role_access DROP CONSTRAINT if exists admin_access_id_pkey;
alter table admin.role_access DROP CONSTRAINT if exists admin_role_access_id_pkey;
alter table admin.role_access DROP CONSTRAINT if exists admin_access_role_id_fkey;
alter table admin.role_access DROP CONSTRAINT if exists admin_access_route_id_uid_unique;
alter table admin.role_access DROP CONSTRAINT if exists admin_access_route_id_fkey;

alter table admin.role_access add column if not exists access_id text NOT NULL DEFAULT next_id();
alter table admin.role_access add column if not exists route_id text NOT NULL;
alter table admin.role_access add column if not exists role_id text;
alter table admin.role_access add column if not exists user_uid text;
alter table admin.role_access add column if not exists scope text;
alter table admin.role_access add column if not exists actions text[];
ALTER TABLE admin.role_access ADD COLUMN IF NOT EXISTS access_granted text;
ALTER TABLE admin.role_access ADD COLUMN IF NOT EXISTS access_granted_time timestamp without time zone;
alter table admin.role_access add column if not exists cdate timestamp without time zone NOT NULL DEFAULT date_trunc('seconds'::text, now());
alter table admin.role_access add column if not exists uid text;
alter table admin.role_access add column if not exists editor_id text;
alter table admin.role_access add column if not exists editor_date timestamp without time zone;

alter table admin.role_access add CONSTRAINT admin_role_access_id_pkey PRIMARY KEY (access_id);
alter table admin.role_access add CONSTRAINT admin_access_role_id_fkey FOREIGN KEY (role_id) REFERENCES admin.roles (role_id);
alter table admin.role_access add CONSTRAINT admin_access_route_id_uid_unique UNIQUE (route_id, user_uid, role_id);
alter table admin.role_access add constraint admin_access_route_id_fkey FOREIGN KEY (route_id) REFERENCES admin.routes(route_id);


COMMENT ON TABLE admin.role_access IS 'Налаштування прав. Відношення груп / окремих користувачів до шаблонів';
COMMENT ON COLUMN admin.role_access.route_id IS 'ID шаблона';
COMMENT ON COLUMN admin.role_access.role_id IS 'ID групи';
COMMENT ON COLUMN admin.role_access.user_uid IS 'ID користувача';
COMMENT ON COLUMN admin.role_access.scope IS 'Обмеження виведення (власні, відповідальний, всі)';
COMMENT ON COLUMN admin.role_access.actions IS 'Доступні дії';
COMMENT ON COLUMN admin.role_access.access_granted IS 'Ідентифікатор користувача який надав доступ';
COMMENT ON COLUMN admin.role_access.access_granted_time IS 'Час коли надали доступ';

CREATE INDEX if not exists admin_access_route_id_idx ON admin.role_access USING btree (route_id COLLATE pg_catalog."default");
CREATE INDEX if not exists admin_access_role_id_idx ON admin.role_access USING btree (role_id COLLATE pg_catalog."default");


