create schema if not exists admin;

CREATE TABLE if not exists admin.cls();
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS clsid text;
ALTER TABLE admin.cls DROP CONSTRAINT IF EXISTS admin_cls_pkey;
ALTER TABLE admin.cls DROP CONSTRAINT IF EXISTS admin_cls_unique;

ALTER TABLE admin.cls ALTER COLUMN clsid SET NOT NULL; 
ALTER TABLE admin.cls ALTER COLUMN clsid SET DEFAULT next_id();
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS name text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS data text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS type text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS code text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS parent text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS ua text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS en text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS module text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS files json;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS cdate timestamp without time zone;
ALTER TABLE admin.cls ALTER COLUMN cdate SET DEFAULT (now())::timestamp without time zone;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS uid text;
ALTER TABLE admin.cls ALTER COLUMN uid SET NOT NULL;
ALTER TABLE admin.cls ALTER COLUMN uid SET DEFAULT '1'::text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS icon text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS color text;
ALTER TABLE admin.cls ADD COLUMN IF NOT EXISTS hash text;

ALTER TABLE admin.cls ADD CONSTRAINT admin_cls_pkey PRIMARY KEY (clsid);
ALTER TABLE admin.cls ADD CONSTRAINT admin_cls_unique UNIQUE (code, parent);

COMMENT ON COLUMN admin.cls.name IS 'Назва';
COMMENT ON COLUMN admin.cls.type IS 'Тип';
COMMENT ON COLUMN admin.cls.data IS 'SQL';
COMMENT ON COLUMN admin.cls.code IS 'Код';
COMMENT ON COLUMN admin.cls.parent IS 'Назва батьківського об''єкту';
COMMENT ON COLUMN admin.cls.module IS 'Модуль';
COMMENT ON COLUMN admin.cls.icon IS 'Іконка';
COMMENT ON COLUMN admin.cls.color IS 'Колір';

-- drop table if exists admin.user_cls;
CREATE TABLE if not exists admin.user_cls
(
  user_clsid text NOT NULL DEFAULT next_id(), -- ID
  code text, -- Код
  name text, -- Назва
  data text, -- SQL запит
  type text, -- Тип (json/sql)
  icon text, -- Іконка
  color text, -- Колір
  parent text,
  created_by text,
  created_at timestamp without time zone DEFAULT (now())::timestamp without time zone,
  updated_by text,
  updated_at timestamp without time zone DEFAULT (now())::timestamp without time zone,
  CONSTRAINT admin_user_cls_pkey PRIMARY KEY (user_clsid),
  CONSTRAINT admin_user_unique UNIQUE (code, parent),
  CHECK (case when parent is null then type in ('json', 'sql') else true end)
);

COMMENT ON TABLE admin.user_cls IS 'Користувацькі класифікатори';
COMMENT ON COLUMN admin.user_cls.user_clsid IS 'ID';
COMMENT ON COLUMN admin.user_cls.code IS 'Код';
COMMENT ON COLUMN admin.user_cls.name IS 'Назва';
COMMENT ON COLUMN admin.user_cls.data IS 'SQL запит';
COMMENT ON COLUMN admin.user_cls.icon IS 'Іконка';
COMMENT ON COLUMN admin.user_cls.color IS 'Колір';
COMMENT ON COLUMN admin.user_cls.type IS 'Тип (json/sql)';

ALTER TABLE admin.user_cls DROP CONSTRAINT IF EXISTS admin_user_cls_pkey;
ALTER TABLE admin.user_cls DROP CONSTRAINT IF EXISTS admin_user_unique;

ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS user_clsid text;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS code text;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS parent text;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS name text;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS icon text;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS data text;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS type text;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS files json;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS cdate timestamp without time zone;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS editor_id text;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS editor_date timestamp without time zone;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS uid text;
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS color text;

ALTER TABLE admin.user_cls ALTER COLUMN user_clsid SET NOT NULL;
ALTER TABLE admin.user_cls ALTER COLUMN user_clsid SET DEFAULT next_id();
ALTER TABLE admin.user_cls ALTER COLUMN cdate SET DEFAULT (now())::timestamp without time zone;
ALTER TABLE admin.user_cls ALTER COLUMN uid SET NOT NULL;
ALTER TABLE admin.user_cls ALTER COLUMN uid SET DEFAULT '1'::text;

ALTER TABLE admin.user_cls ADD CONSTRAINT admin_user_cls_pkey PRIMARY KEY(user_clsid);
ALTER TABLE admin.user_cls ADD CONSTRAINT admin_user_unique UNIQUE(code, parent);

COMMENT ON TABLE admin.user_cls IS 'Користувацькі класифікатори';
COMMENT ON COLUMN admin.user_cls.user_clsid IS 'ID';
COMMENT ON COLUMN admin.user_cls.code IS 'Код';
COMMENT ON COLUMN admin.user_cls.name IS 'Назва';
COMMENT ON COLUMN admin.user_cls.icon IS 'Іконка';
COMMENT ON COLUMN admin.user_cls.color IS 'Колір';

ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS alias text[];
ALTER TABLE admin.user_cls ADD COLUMN IF NOT EXISTS description text;
