CREATE SCHEMA IF NOT EXISTS admin;

CREATE TABLE IF NOT EXISTS admin.rules();
CREATE TABLE IF NOT EXISTS admin.users( uid text not null constraint admin_user_uid_pkey PRIMARY KEY DEFAULT next_id() );
ALTER TABLE admin.rules DROP CONSTRAINT IF EXISTS admin_rules_pkey cascade;
ALTER TABLE admin.rules add column if not exists rule_id text NOT NULL DEFAULT next_id();

ALTER TABLE admin.rules add column if not exists rule_type text;
ALTER TABLE admin.rules add column if not exists rule_name text;
ALTER TABLE admin.rules add column if not exists attr text;
ALTER TABLE admin.rules add column if not exists routes text[];
ALTER TABLE admin.rules add column if not exists uid text;
ALTER TABLE admin.rules add column if not exists cdate timestamp without time zone;
ALTER TABLE admin.rules add column if not exists rule_query text;
ALTER TABLE admin.rules add column if not exists cls text;
COMMENT ON TABLE admin.rules IS 'Повноваження';
COMMENT ON COLUMN admin.rules.rule_type IS 'Тип повноваження';
COMMENT ON COLUMN admin.rules.rule_name IS 'Назва';
COMMENT ON COLUMN admin.rules.attr IS 'Атрибут';
COMMENT ON COLUMN admin.rules.routes IS 'Роути';
COMMENT ON COLUMN admin.rules.uid IS 'Хто створив';
COMMENT ON COLUMN admin.rules.cdate IS 'Дата створення';
COMMENT ON COLUMN admin.rules.rule_query IS 'Запит';
COMMENT ON COLUMN admin.rules.cls IS 'Класифікатор';
ALTER TABLE admin.rules ALTER COLUMN attr DROP NOT NULL;
ALTER TABLE admin.rules ALTER COLUMN cdate DROP NOT NULL;ALTER TABLE admin.rules ALTER COLUMN cdate SET DEFAULT (now())::timestamp without time zone;
ALTER TABLE admin.rules ALTER COLUMN cls DROP NOT NULL;
ALTER TABLE admin.rules ALTER COLUMN routes DROP NOT NULL;
ALTER TABLE admin.rules ALTER COLUMN rule_id SET NOT NULL;ALTER TABLE admin.rules ALTER COLUMN rule_id SET DEFAULT next_id();
ALTER TABLE admin.rules ALTER COLUMN rule_name DROP NOT NULL;
ALTER TABLE admin.rules ALTER COLUMN rule_query DROP NOT NULL;
ALTER TABLE admin.rules ALTER COLUMN rule_type DROP NOT NULL;
ALTER TABLE admin.rules ALTER COLUMN uid DROP NOT NULL;
ALTER TABLE admin.rules add CONSTRAINT admin_rules_pkey PRIMARY KEY (rule_id);

CREATE INDEX if not exists admin_rules_id_idx ON admin.rules USING btree (rule_id COLLATE pg_catalog."default");


CREATE TABLE IF NOT EXISTS admin.accounts();
ALTER TABLE admin.accounts DROP CONSTRAINT IF EXISTS admin_accounts_pkey cascade;
ALTER TABLE admin.accounts add column if not exists account_id text NOT NULL DEFAULT next_id();

ALTER TABLE admin.accounts add column if not exists account_name text;
ALTER TABLE admin.accounts add column if not exists description text;
ALTER TABLE admin.accounts add column if not exists uid text;
ALTER TABLE admin.accounts add column if not exists cdate timestamp without time zone;
COMMENT ON TABLE admin.accounts IS 'Організації';
COMMENT ON COLUMN admin.accounts.account_name IS 'Назва';
COMMENT ON COLUMN admin.accounts.description IS 'Опис';
COMMENT ON COLUMN admin.accounts.uid IS 'Хто створив';
COMMENT ON COLUMN admin.accounts.cdate IS 'Дата створення';
ALTER TABLE admin.accounts ALTER COLUMN account_id SET NOT NULL;ALTER TABLE admin.accounts ALTER COLUMN account_id SET DEFAULT next_id();
ALTER TABLE admin.accounts ALTER COLUMN account_name DROP NOT NULL;
ALTER TABLE admin.accounts ALTER COLUMN cdate DROP NOT NULL;ALTER TABLE admin.accounts ALTER COLUMN cdate SET DEFAULT (now())::timestamp without time zone;
ALTER TABLE admin.accounts ALTER COLUMN description DROP NOT NULL;
ALTER TABLE admin.accounts ALTER COLUMN uid DROP NOT NULL;
ALTER TABLE admin.accounts add CONSTRAINT admin_accounts_pkey PRIMARY KEY (account_id);

CREATE INDEX if not exists admin_accounts_id_idx ON admin.accounts USING btree (account_id COLLATE pg_catalog."default");


CREATE TABLE IF NOT EXISTS admin.account_users();
ALTER TABLE admin.account_users DROP CONSTRAINT IF EXISTS admin_account_users_pkey;
ALTER TABLE admin.account_users DROP CONSTRAINT IF EXISTS admin_au_account_fkey;
ALTER TABLE admin.account_users DROP CONSTRAINT IF EXISTS admin_au_user_fkey;
ALTER TABLE admin.account_users add column if not exists au_id text NOT NULL DEFAULT next_id();

ALTER TABLE admin.account_users add column if not exists account_id text;
ALTER TABLE admin.account_users add column if not exists user_uid text;
ALTER TABLE admin.account_users add column if not exists uid text;
ALTER TABLE admin.account_users add column if not exists cdate timestamp without time zone;
COMMENT ON TABLE admin.account_users IS 'Організації - Користувачі';
COMMENT ON COLUMN admin.account_users.account_id IS 'id Організації';
COMMENT ON COLUMN admin.account_users.user_uid IS 'id Користувача';
COMMENT ON COLUMN admin.account_users.uid IS 'Хто створив';
COMMENT ON COLUMN admin.account_users.cdate IS 'Дата створення';
ALTER TABLE admin.account_users ALTER COLUMN account_id DROP NOT NULL;
ALTER TABLE admin.account_users ALTER COLUMN au_id SET NOT NULL;ALTER TABLE admin.account_users ALTER COLUMN au_id SET DEFAULT next_id();
ALTER TABLE admin.account_users ALTER COLUMN cdate DROP NOT NULL;ALTER TABLE admin.account_users ALTER COLUMN cdate SET DEFAULT (now())::timestamp without time zone;
ALTER TABLE admin.account_users ALTER COLUMN uid DROP NOT NULL;
ALTER TABLE admin.account_users ALTER COLUMN user_uid DROP NOT NULL;
ALTER TABLE admin.account_users add CONSTRAINT admin_account_users_pkey PRIMARY KEY (au_id);
ALTER TABLE admin.account_users ADD CONSTRAINT admin_au_account_fkey FOREIGN KEY (account_id) REFERENCES admin.accounts (account_id) MATCH SIMPLE;
ALTER TABLE admin.account_users ADD CONSTRAINT admin_au_user_fkey FOREIGN KEY (user_uid) REFERENCES admin.users (uid) MATCH SIMPLE;

CREATE INDEX if not exists admin_account_users_id_idx ON admin.account_users USING btree (au_id COLLATE pg_catalog."default");
CREATE INDEX if not exists admin_au_account_id_idx ON admin.account_users USING btree (account_id COLLATE pg_catalog."default");
CREATE INDEX if not exists admin_au_user_uid_idx ON admin.account_users USING btree (user_uid COLLATE pg_catalog."default");


CREATE TABLE IF NOT EXISTS admin.account_grants();
ALTER TABLE admin.account_grants DROP CONSTRAINT IF EXISTS admin_account_grants_pkey;
ALTER TABLE admin.account_grants DROP CONSTRAINT IF EXISTS admin_ag_account_fkey;
ALTER TABLE admin.account_grants DROP CONSTRAINT IF EXISTS admin_ag_user_fkey;
ALTER TABLE admin.account_grants add column if not exists ag_id text NOT NULL DEFAULT next_id();

ALTER TABLE admin.account_grants add column if not exists account_id text;
ALTER TABLE admin.account_grants add column if not exists rule_id text;
ALTER TABLE admin.account_grants add column if not exists routes text[];
ALTER TABLE admin.account_grants add column if not exists rule_values text[];
ALTER TABLE admin.account_grants add column if not exists grants_doc_id text;
ALTER TABLE admin.account_grants add column if not exists grant_scan text;
ALTER TABLE admin.account_grants add column if not exists expire_date date;
ALTER TABLE admin.account_grants add column if not exists actions text[];
ALTER TABLE admin.account_grants add column if not exists uid text;
ALTER TABLE admin.account_grants add column if not exists cdate timestamp without time zone;
COMMENT ON TABLE admin.account_grants IS 'Організації - Повноваження';
COMMENT ON COLUMN admin.account_grants.account_id IS 'id Організації';
COMMENT ON COLUMN admin.account_grants.rule_id IS 'id Правила';
COMMENT ON COLUMN admin.account_grants.routes IS 'Роути';
COMMENT ON COLUMN admin.account_grants.rule_values IS 'Значення';
COMMENT ON COLUMN admin.account_grants.grants_doc_id IS 'id Документу';
COMMENT ON COLUMN admin.account_grants.grant_scan IS 'id Скану';
COMMENT ON COLUMN admin.account_grants.expire_date IS 'Дата закінчення дії дозволу';
COMMENT ON COLUMN admin.account_grants.actions IS 'Дії';
COMMENT ON COLUMN admin.account_grants.uid IS 'Хто створив';
COMMENT ON COLUMN admin.account_grants.cdate IS 'Дата створення';
ALTER TABLE admin.account_grants ALTER COLUMN account_id DROP NOT NULL;
ALTER TABLE admin.account_grants ALTER COLUMN actions DROP NOT NULL;
ALTER TABLE admin.account_grants ALTER COLUMN ag_id SET NOT NULL;ALTER TABLE admin.account_grants ALTER COLUMN ag_id SET DEFAULT next_id();
ALTER TABLE admin.account_grants ALTER COLUMN cdate DROP NOT NULL;ALTER TABLE admin.account_grants ALTER COLUMN cdate SET DEFAULT (now())::timestamp without time zone;
ALTER TABLE admin.account_grants ALTER COLUMN expire_date DROP NOT NULL;
ALTER TABLE admin.account_grants ALTER COLUMN grant_scan DROP NOT NULL;
ALTER TABLE admin.account_grants ALTER COLUMN grants_doc_id DROP NOT NULL;
ALTER TABLE admin.account_grants ALTER COLUMN routes DROP NOT NULL;
ALTER TABLE admin.account_grants ALTER COLUMN rule_id DROP NOT NULL;
ALTER TABLE admin.account_grants ALTER COLUMN rule_values DROP NOT NULL;
ALTER TABLE admin.account_grants ALTER COLUMN uid DROP NOT NULL;
ALTER TABLE admin.account_grants add CONSTRAINT admin_account_grants_pkey PRIMARY KEY (ag_id);
ALTER TABLE admin.account_grants ADD CONSTRAINT admin_ag_account_fkey FOREIGN KEY (account_id) REFERENCES admin.accounts (account_id) MATCH SIMPLE;
ALTER TABLE admin.account_grants ADD CONSTRAINT admin_ag_user_fkey FOREIGN KEY (rule_id) REFERENCES admin.rules (rule_id) MATCH SIMPLE;

CREATE INDEX if not exists admin_account_users_id_idx ON admin.account_grants USING btree (ag_id COLLATE pg_catalog."default");
CREATE INDEX if not exists admin_ag_account_id_idx ON admin.account_grants USING btree (account_id COLLATE pg_catalog."default");
CREATE INDEX if not exists admin_ag_rule_id_idx ON admin.account_grants USING btree (rule_id COLLATE pg_catalog."default");