-- Создаем БД
CREATE DATABASE 'localhost:/tmp/xdac_db.fdb' USER 'SYSDBA' password 'reddb';

-- Создаем пользователя XDAC_USER
CREATE USER xdac_user password 'xdac_password1!';

-- Предоставляем права
GRANT CREATE TABLE TO USER xdac_user;
GRANT CREATE FUNCTION TO USER xdac_user;
GRANT CREATE PROCEDURE TO USER xdac_user;
GRANT CREATE package TO USER xdac_user;

-- ПОДКЛЮЧАЕМСЯ НОВЫМ ПОЛЬЗОВАТЕЛЕМ
-------------------------------------------------------------------------------

CREATE TABLE T_CLIENTS (
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
    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',
    CONSTRAINT INTEG_1 PRIMARY KEY (ID) USING ASCENDING INDEX RDB$PRIMARY1);

INSERT INTO t_clients (last_name, first_name, second_name, phone, date_of_birth, email, cli_type)
VALUES
    ('Горелов', 'Вадим', 'Захарович', '79991234567', '1937-04-11', 'info@xsquare.ru', 'FL');
INSERT INTO t_clients (last_name, first_name, second_name, phone, date_of_birth, email, cli_type)
VALUES
   ('Федотов', 'Павел', 'Игоревич', '79991234567', '2011-06-17', 'info@xsquare.ru', 'FL');
INSERT INTO t_clients (last_name, first_name, second_name, phone, date_of_birth, email, cli_type)
VALUES
    ('Артамонов', 'Роберт', 'Николаевич', '79991234567', '1957-02-24', 'info@xsquare.ru', 'FL');
INSERT INTO t_clients (last_name, first_name, second_name, phone, date_of_birth, email, cli_type)
VALUES
    ('Чесноков', 'Роберт', 'Алексеевич', '79991234567', '1994-12-18', 'info@xsquare.ru', 'FL');
INSERT INTO t_clients (last_name, first_name, second_name, phone, date_of_birth, email, cli_type)
VALUES
    ('Романов', 'Тихон', 'Семёнович', '79991234567', '1954-08-23', 'info@xsquare.ru', 'FL');
INSERT INTO t_clients (last_name, first_name, second_name, phone, date_of_birth, email, cli_type)
VALUES
    ('Жуков', 'Тихон', 'Платонович', '79991234567', '2018-03-14', 'info@xsquare.ru', 'FL');
INSERT INTO t_clients (last_name, first_name, second_name, phone, date_of_birth, email, cli_type)
VALUES
    ('Попов', 'Игорь', 'Семёнович', '79991234567', '2018-07-14', 'info@xsquare.ru', 'FL');


-- Создаем пакет PKG_API с процедурами и функциями
CREATE OR ALTER package pkg_api 
AS
BEGIN 

FUNCTION f_hello returns varchar(255);

FUNCTION f_concat_str (
  p_first_str varchar(255) DEFAULT '', 
  p_second_str varchar(255) DEFAULT ''
) returns varchar(255);

FUNCTION f_json_parse (
  p_json varchar(255)
) returns varchar(255);

FUNCTION f_get_mass returns blob sub_type 1;

FUNCTION f_get_client (p_id bigint) returns blob sub_type text COLLATE none;

END;

CREATE package body pkg_api
AS
BEGIN

FUNCTION f_hello
RETURNS VARCHAR(255)
AS
BEGIN
    RETURN '{"GET_RETURN": "Hello world from firebird!"}';
END

-----------------------------------------------------------------------------------------------------
FUNCTION F_CONCAT_STR (
    P_FIRST_STR VARCHAR(255),
    P_SECOND_STR VARCHAR(255) 
)
RETURNS VARCHAR(255) COLLATE NONE
AS
DECLARE l_result VARCHAR(255);
BEGIN
    l_result = p_first_str || p_second_str;
    RETURN '{"CONCAT_RESULT": "' || l_result || '"}';
END

-----------------------------------------------------------------------------------------------------
FUNCTION F_JSON_PARSE (
    P_JSON VARCHAR(255)
)
RETURNS VARCHAR(255) COLLATE NONE
AS
BEGIN
    IF (p_json IS JSON) THEN
        RETURN '{"JSON_OUTPUT": ' || p_json || '}';
    ELSE
        RETURN '{"JSON_OUTPUT": "INVALID JSON!"}';
END

-----------------------------------------------------------------------------------------------------
FUNCTION f_get_mass
RETURNS BLOB SUB_TYPE 1
AS
DECLARE VARIABLE l_mass_clients BLOB SUB_TYPE 1;
BEGIN
    SELECT JSON_ARRAYAGG(
               JSON_OBJECT(
                   'id' VALUE id,
                   'last_name' VALUE last_name,
                   'first_name' VALUE first_name,
                   'date_of_birth' VALUE CAST(date_of_birth AS VARCHAR(24)),
                   'email' VALUE email,
                   'phone' VALUE phone
               )
           )

    FROM (
             SELECT c.id, c.last_name, c.first_name, c.date_of_birth, c.email, c.phone
             FROM t_clients c
         )
   INTO l_mass_clients;

    RETURN l_mass_clients;
END

-----------------------------------------------------------------------------------------------------
FUNCTION F_GET_CLIENT (
    P_ID BIGINT
)
RETURNS BLOB SUB_TYPE TEXT COLLATE NONE
AS
DECLARE l_client_data BLOB SUB_TYPE 1;
BEGIN
    SELECT JSON_OBJECT(
               'fi' VALUE c.last_name || ' ' || c.first_name,
               'date_of_birth' VALUE CAST(c.date_of_birth AS VARCHAR(24)),
               'email' VALUE c.email,
               'phone' VALUE c.phone
           )

    FROM t_clients c
    WHERE c.id = :P_ID
    INTO l_client_data;

    RETURN l_client_data;
END

END;

