-- Пример для psql
-- Создаем базу данных
CREATE DATABASE xdac_db;

-- Подключаемся к созданной базе данных
\connect xdac_db;

-- Создаем пользователя
CREATE USER XDAC_USER WITH PASSWORD 'XDAC_PASSWORD1!';

-- Создаем схему для функций и процедур
CREATE SCHEMA api;

-- Создаем схему для пользовательских данных
CREATE SCHEMA cli;

-- Предоставляем права пользователю
GRANT ALL PRIVILEGES ON SCHEMA api TO XDAC_USER;
GRANT ALL PRIVILEGES ON SCHEMA cli TO XDAC_USER;

-- ПОДКЛЮЧАЕМСЯ НОВЫМ ПОЛЬЗОВАТЕЛЕМ
-------------------------------------------------------------------------------
CREATE TABLE cli.t_clients (
  id BIGSERIAL, 
  last_name VARCHAR(50) NOT NULL, 
  first_name VARCHAR(50) NOT NULL, 
  second_name VARCHAR(50), 
  phone VARCHAR(11) NOT NULL, 
  date_of_birth DATE, 
  email VARCHAR(100), 
  cli_type VARCHAR(2) DEFAULT 'FL' :: character varying
);
INSERT INTO cli.t_clients (
  last_name, first_name, second_name, 
  phone, date_of_birth, email, cli_type
) 
VALUES 
  (
    'Gorelkov', 'Vadim', 'Zakharovich', 
    '79991234567', '1937-04-11', 'info@xsquare.local', 
    'FL'
  ), 
  (
    'Fedotov', 'Pavel', 'Igorevich', '79991234567', 
    '2011-06-17', 'info@xsquare.local', 
    'FL'
  ), 
  (
    'Artamonov', 'Robert', 'Nikolaevich', 
    '79991234567', '1957-02-24', 'info@xsquare.local', 
    'FL'
  ), 
  (
    'Chesnokov', 'Robert', 'Alekseevich', 
    '79991234567', '1994-12-18', 'info@xsquare.local', 
    'FL'
  ), 
  (
    'Romanov', 'Tikhon', 'Semyonovich', 
    '79991234567', '1954-08-23', 'info@xsquare.local', 
    'FL'
  ), 
  (
    'Zhukov', 'Tikhon', 'Platonovich', 
    '79991234567', '2018-03-14', 'info@xsquare.local', 
    'FL'
  ), 
  (
    'Popov', 'Igor', 'Semyonovich', '79991234567', 
    '2018-07-14', 'info@xsquare.local', 
    'FL'
  );

-------------------------------------------------------------------------------


create function api.f_hello() returns json
    language plpgsql
as
$$
DECLARE
BEGIN
    RETURN '{"GET_RETURN": "Hello world from PostgreSQL!"}'::json;
END;
$$;


-------------------------------------------------------------------------------

create function api.f_concat_str(p_first_str character varying DEFAULT ''::character varying, p_second_str character varying DEFAULT ''::character varying) returns json
    language plpgsql
as
$$
DECLARE
    l_result VARCHAR;
BEGIN
    l_result := CONCAT(p_first_str,p_second_str);
  
    RETURN ('{"CONCAT_RESULT": "'||l_result||'"}')::JSON;
END;
$$;


-------------------------------------------------------------------------------

create function api.f_json_parse(p_json json) returns json
    language plpgsql
as
$$
DECLARE
BEGIN
    RETURN ('{"JSON_OUTPUT": '||p_json::TEXT||'}')::JSON;
  
EXCEPTION WHEN OTHERS
THEN
    RETURN 'INVALID JSON!';
END;
$$;


-------------------------------------------------------------------------------

create function api.f_get_mass() returns jsonb
    language plpgsql
as
$$
    DECLARE
        l_mass_clients JSONB;
    BEGIN
        

        WITH tbl AS (
            SELECT c.last_name, c.first_name, c.second_name, c.date_of_birth, c.email
            FROM cli.t_clients c
            LIMIT 3
        )   
        SELECT jsonb_pretty(JSON_AGG(tbl.*)::JSONB) as mass_clients
        INTO l_mass_clients
        FROM tbl;

        RETURN l_mass_clients;
        
    END;
$$;

-------------------------------------------------------------------------------

create function api.f_get_client(p_id bigint) returns json
    language plpgsql
as
$$
    DECLARE
        l_client_info json;
    BEGIN
        SELECT json_build_object('fio', CONCAT(c.last_name,' ',c.first_name,' ',c.second_name),
                                 'date_of_birth', to_char(c.date_of_birth, 'dd.mm.yyyy'),
                                 'phone', c.phone,
                                 'email', c.email)
        INTO l_client_info
        FROM cli.t_clients c
        WHERE c.id = p_id;

        RETURN l_client_info;
    END;
$$;

