create schema if not exists admin;

-- DROP TABLE is exists admin.users;
CREATE TABLE if not exists admin.users();
ALTER TABLE admin.users add column if not exists uid text NOT NULL DEFAULT next_id();
ALTER TABLE admin.users DROP CONSTRAINT if exists admin_user_uid_pkey cascade;
ALTER TABLE admin.users DROP CONSTRAINT if exists user_pk cascade;
ALTER TABLE admin.users DROP CONSTRAINT if exists admin_user_user_rnokpp;
ALTER TABLE admin.users DROP CONSTRAINT if exists admin_user_type_check;

ALTER TABLE admin.users add column if not exists login text;
ALTER TABLE admin.users add column if not exists password text NOT NULL DEFAULT ''::text;
ALTER TABLE admin.users add column if not exists user_name text;
ALTER TABLE admin.users add column if not exists sur_name text;
ALTER TABLE admin.users add column if not exists father_name text;
ALTER TABLE admin.users add column if not exists email text;
ALTER TABLE admin.users add column if not exists phone text;
ALTER TABLE admin.users add column if not exists avatar text;
ALTER TABLE admin.users add column if not exists enabled boolean;
ALTER TABLE admin.users add column if not exists user_personal_code text;
ALTER TABLE admin.users add column if not exists last_activity_date timestamp without time zone;
ALTER TABLE admin.users add column if not exists user_type text DEFAULT 'regular'::text;
ALTER TABLE admin.users add column if not exists user_rnokpp text;
ALTER TABLE admin.users alter column user_rnokpp drop not null;
ALTER TABLE admin.users add column if not exists social_auth_id text;
ALTER TABLE admin.users add column if not exists social_auth_type text;
ALTER TABLE admin.users add column if not exists salt text;
ALTER TABLE admin.users add column if not exists cdate timestamp without time zone DEFAULT date_trunc('seconds'::text, now());
ALTER TABLE admin.users add column if not exists editor_id text;
ALTER TABLE admin.users add column if not exists editor_date timestamp without time zone;
ALTER TABLE admin.users add column if not exists twofa boolean not null DEFAULT true;

ALTER TABLE admin.users add CONSTRAINT admin_user_uid_pkey PRIMARY KEY (uid);
ALTER TABLE admin.users add CONSTRAINT admin_user_user_rnokpp UNIQUE (user_rnokpp);

-- update admin.users set user_type='regular' where user_type not in ('viewer', 'regular','admin','superadmin');
-- ALTER TABLE admin.users add constraint admin_user_type_check CHECK (user_type=any(array['viewer', 'regular','admin','superadmin']));
insert into admin.users (uid,login,user_name,email,sur_name,password,user_type,enabled)values('viewer','viewer','viewer','viewer','viewer','viewer','viewer', false) on conflict (uid) do update set enabled=excluded.enabled, sur_name=excluded.sur_name;
-- update admin.users set enabled=true where uid='viewer';

COMMENT ON TABLE admin.users IS 'Користувачі';

COMMENT ON COLUMN admin.users.uid IS 'ID користувача';
COMMENT ON COLUMN admin.users.login IS 'Логін користувача';
COMMENT ON COLUMN admin.users.password IS 'Пароль користувача';
COMMENT ON COLUMN admin.users.user_name IS 'Ім''я користувача';
COMMENT ON COLUMN admin.users.sur_name IS 'Прізвище користувача';
COMMENT ON COLUMN admin.users.father_name IS 'По-батькові користувача';
COMMENT ON COLUMN admin.users.email IS 'Ел. пошта користувача';
COMMENT ON COLUMN admin.users.phone IS 'Номер телефону користувача';
COMMENT ON COLUMN admin.users.avatar IS 'Аватар';
COMMENT ON COLUMN admin.users.enabled IS 'On / Off';
COMMENT ON COLUMN admin.users.last_activity_date IS 'Дата останньої активності';
COMMENT ON COLUMN admin.users.user_type IS 'Тип користувача';
COMMENT ON COLUMN admin.users.salt IS 'Сіль';
COMMENT ON COLUMN admin.users.twofa IS 'Двофакторна авторизація';

CREATE EXTENSION if not exists pgcrypto SCHEMA public VERSION "1.3";
CREATE OR REPLACE FUNCTION admin.crypt(text, text) RETURNS text AS '$libdir/pgcrypto', 'pg_crypt' LANGUAGE c IMMUTABLE STRICT COST 1;

-- DROP FUNCTION admin.insert_update_user_before();
CREATE OR REPLACE FUNCTION admin.insert_update_user_before()
RETURNS trigger AS

$BODY$
DECLARE 

iterations  int;
hash character varying;

BEGIN

 if(TG_OP='INSERT' or (TG_OP='UPDATE' and new.password<>old.password)) then
	if(char_length(new.password) <> 0 and char_length(new.password) < 8) then
		--raise exception 'password must be longer than 8 characters';
	end if;
	new.salt=md5(now()::text);
	--raise exception '%','change pass';
	if(new.salt ='') then
		new.salt=gen_salt('md5');	
	end if;	
	iterations = 10;
	hash=''; 
	
	loop   		
		if iterations=0 then
			exit;
		end if;
		hash = md5(new.password||hash||new.salt);
		iterations=iterations-1;
	end loop;
	new.password=admin.crypt(hash,new.salt);
	
 end if;
 RETURN new;
END
$BODY$

LANGUAGE plpgsql VOLATILE COST 100;
  
DROP TRIGGER if exists insert_update_user_before on admin.users;
CREATE TRIGGER insert_update_user_before BEFORE INSERT OR UPDATE ON admin.users FOR EACH ROW 
EXECUTE PROCEDURE admin.insert_update_user_before();

-- DROP TABLE is exists admin.users_social_auth;
CREATE TABLE if not exists admin.users_social_auth();
ALTER TABLE admin.users_social_auth add column if not exists users_social_auth_id text NOT NULL DEFAULT next_id();
ALTER TABLE admin.users_social_auth alter column users_social_auth_id set DEFAULT next_id();
ALTER TABLE admin.users_social_auth DROP CONSTRAINT if exists users_social_auth_pk cascade;
ALTER TABLE admin.users_social_auth DROP CONSTRAINT if exists users_social_auth_users_fk cascade;
ALTER TABLE admin.users_social_auth DROP CONSTRAINT if exists users_social_auth_social_id_email_unique;

ALTER TABLE admin.users_social_auth add column if not exists uid text not null;
ALTER TABLE admin.users_social_auth add column if not exists user_name text;
ALTER TABLE admin.users_social_auth add column if not exists sur_name text;
ALTER TABLE admin.users_social_auth add column if not exists email text;
ALTER TABLE admin.users_social_auth add column if not exists city text;
ALTER TABLE admin.users_social_auth add column if not exists phone text;
ALTER TABLE admin.users_social_auth add column if not exists social_auth_id text;
ALTER TABLE admin.users_social_auth add column if not exists social_auth_type text;
ALTER TABLE admin.users_social_auth add column if not exists social_auth_code text;
ALTER TABLE admin.users_social_auth add column if not exists social_auth_obj json;
ALTER TABLE admin.users_social_auth add column if not exists social_auth_date timestamp without time zone;
ALTER TABLE admin.users_social_auth add column if not exists social_auth_softpro_code text;
ALTER TABLE admin.users_social_auth add column if not exists enabled boolean;
ALTER TABLE admin.users_social_auth add column if not exists cdate timestamp without time zone;
ALTER TABLE admin.users_social_auth alter column cdate SET DEFAULT date_trunc('seconds'::text, now());

ALTER TABLE admin.users_social_auth add column if not exists editor_id text;
ALTER TABLE admin.users_social_auth add column if not exists lang text;
ALTER TABLE admin.users_social_auth add column if not exists editor_date timestamp without time zone;
ALTER TABLE admin.users_social_auth add column if not exists social_auth_url text;
ALTER TABLE admin.users_social_auth add CONSTRAINT users_social_auth_pk PRIMARY KEY (users_social_auth_id);
ALTER TABLE admin.users_social_auth add CONSTRAINT users_social_auth_users_fk FOREIGN KEY (uid) REFERENCES admin.users (uid) MATCH SIMPLE on delete cascade;
ALTER TABLE admin.users_social_auth add CONSTRAINT users_social_auth_social_id_email_unique UNIQUE (social_auth_id,email);

COMMENT ON COLUMN admin.users_social_auth.uid IS 'id пользователя';
COMMENT ON COLUMN admin.users_social_auth.user_name IS 'Имя пользователя';
COMMENT ON COLUMN admin.users_social_auth.sur_name IS 'Фамилия';
COMMENT ON COLUMN admin.users_social_auth.email IS 'email пользователя';
COMMENT ON COLUMN admin.users_social_auth.city IS 'Місто';
COMMENT ON COLUMN admin.users_social_auth.phone IS 'Телефон';
COMMENT ON COLUMN admin.users_social_auth.social_auth_id IS 'ID в соцсети';
COMMENT ON COLUMN admin.users_social_auth.social_auth_type IS 'тип соцсети';
COMMENT ON COLUMN admin.users_social_auth.social_auth_code IS 'код обьекта соцсети, используеться соцсетью';
COMMENT ON COLUMN admin.users_social_auth.social_auth_obj IS 'обьект соцсети';
COMMENT ON COLUMN admin.users_social_auth.social_auth_date IS 'время получение последнего обьекта соцсети';
COMMENT ON COLUMN admin.users_social_auth.social_auth_softpro_code IS 'код обьекта соцсети, создаваемый и используемый сервером авторизации';
COMMENT ON COLUMN admin.users_social_auth.enabled IS 'Выключатель';
COMMENT ON COLUMN admin.users_social_auth.social_auth_url IS 'URL для QR code';

update admin.users set twofa=false where uid='1';