-- Под администратором
-- Создаем пользователя
CREATE USER XDAC_USER IDENTIFIED BY xdac_password1!;

-- Предоставляем привилегии

GRANT CREATE SESSION TO XDAC_USER;
-- Позволяет пользователю подключаться к базе данных.
GRANT CREATE TABLE TO XDAC_USER;
-- Позволяет пользователю создавать таблицы.
GRANT CREATE VIEW TO XDAC_USER;
-- Позволяет пользователю создавать представления.
GRANT CREATE SEQUENCE TO XDAC_USER;
-- Позволяет пользователю создавать последовательности.
GRANT CREATE PROCEDURE TO XDAC_USER;
-- Позволяет пользователю создавать процедуры.
GRANT CREATE TRIGGER TO XDAC_USER;
-- Позволяет пользователю создавать триггеры.
GRANT CREATE ANY INDEX TO XDAC_USER;
-- Позволяет пользователю создавать индексы.


-- ПОДКЛЮЧАЕМСЯ НОВЫМ ПОЛЬЗОВАТЕЛЕМ
CONNECT XDAC_USER / xdac_password1!;

-------------------------------------------------------------------------------
create table T_CLIENTS
(
    ID            NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    LAST_NAME     VARCHAR2(50) not null,
    FIRST_NAME    VARCHAR2(50) not null,
    SECOND_NAME   VARCHAR2(50),
    PHONE         VARCHAR2(11) not null,
    DATE_OF_BIRTH DATE,
    EMAIL         VARCHAR2(100),
    CLI_TYPE      VARCHAR2(2) default 'FL'
)


INSERT INTO T_CLIENTS (ID, LAST_NAME, FIRST_NAME, SECOND_NAME, PHONE, DATE_OF_BIRTH, EMAIL, CLI_TYPE) VALUES (1, 'Горелов', 'Вадим', 'Захарович', '79991234567', DATE '1937-04-11', 'info@xsquare.ru', 'FL');
INSERT INTO T_CLIENTS (ID, LAST_NAME, FIRST_NAME, SECOND_NAME, PHONE, DATE_OF_BIRTH, EMAIL, CLI_TYPE) VALUES (2, 'Федотов', 'Павел', 'Игоревич', '79991234567', DATE '2011-06-17', 'info@xsquare.ru', 'FL');
INSERT INTO T_CLIENTS (ID, LAST_NAME, FIRST_NAME, SECOND_NAME, PHONE, DATE_OF_BIRTH, EMAIL, CLI_TYPE) VALUES (3, 'Артамонов', 'Роберт', 'Николаевич', '79991234567', DATE '1957-02-24', 'info@xsquare.ru', 'FL');
INSERT INTO T_CLIENTS (ID, LAST_NAME, FIRST_NAME, SECOND_NAME, PHONE, DATE_OF_BIRTH, EMAIL, CLI_TYPE) VALUES (4, 'Чесноков', 'Роберт', 'Алексеевич', '79991234567', DATE '1994-12-18', 'info@xsquare.ru', 'FL');
INSERT INTO T_CLIENTS (ID, LAST_NAME, FIRST_NAME, SECOND_NAME, PHONE, DATE_OF_BIRTH, EMAIL, CLI_TYPE) VALUES (5, 'Романов', 'Тихон', 'Семёнович', '79991234567', DATE '1954-08-23', 'info@xsquare.ru', 'FL');
INSERT INTO T_CLIENTS (ID, LAST_NAME, FIRST_NAME, SECOND_NAME, PHONE, DATE_OF_BIRTH, EMAIL, CLI_TYPE) VALUES (6, 'Жуков', 'Тихон', 'Платонович', '79991234567', DATE '2018-03-14', 'info@xsquare.ru', 'FL');
INSERT INTO T_CLIENTS (ID, LAST_NAME, FIRST_NAME, SECOND_NAME, PHONE, DATE_OF_BIRTH, EMAIL, CLI_TYPE) VALUES (7, 'Попов', 'Игорь', 'Семёнович', '79991234567', DATE '2018-07-14', 'info@xsquare.ru', 'FL');



-- Создаем пакет PKG_API с процедурами и функциями
CREATE OR REPLACE PACKAGE PKG_API is

    FUNCTION f_hello return clob;

    FUNCTION f_concat_str(
        p_first_str IN VARCHAR2 DEFAULT '',
        p_second_str IN VARCHAR2 DEFAULT ''
    ) RETURN CLOB;

    FUNCTION f_json_parse(p_json IN CLOB) RETURN CLOB;

    FUNCTION f_get_mass RETURN CLOB;

    FUNCTION f_get_client(p_id NUMBER) RETURN CLOB;

    FUNCTION f_get_mass_xml RETURN CLOB;


END PKG_API;

CREATE OR REPLACE PACKAGE BODY PKG_API is

    FUNCTION f_hello RETURN CLOB
    AS
    BEGIN
        RETURN '{"GET_RETURN": "Hello from oracle!!!"}';
    END;

-------------------------------------------------------------------------------
    FUNCTION f_concat_str(
        p_first_str IN VARCHAR2 DEFAULT '',
        p_second_str IN VARCHAR2 DEFAULT ''
    ) RETURN CLOB
    AS
        l_result VARCHAR2(4000);
    BEGIN
        l_result := p_first_str || p_second_str;
        RETURN '{"CONCAT_RESULT": "' || l_result || '"}';
    END;

-------------------------------------------------------------------------------
    FUNCTION f_json_parse(p_json IN CLOB)
        RETURN CLOB
    AS
        l_dummy CLOB;
    BEGIN
        l_dummy := JSON_VALUE(p_json, '$');

        RETURN '{"JSON_OUTPUT": ' || p_json || '}';
    EXCEPTION
        WHEN OTHERS THEN
            RETURN '{"error": "INVALID JSON!"}';
    END;

-------------------------------------------------------------------------------
    FUNCTION f_get_mass
        RETURN CLOB
        IS
        l_mass_clients CLOB;
    BEGIN
        SELECT JSON_ARRAYAGG(
                       JSON_OBJECT(
                               'id' VALUE id,
                               'last_name' VALUE last_name,
                               'first_name' VALUE first_name,
                               'date_of_birth' VALUE TO_CHAR(date_of_birth, 'DD.MM.YYYY'),
                               'email' VALUE email,
                               'phone' VALUE phone
                           )
                   )
        INTO l_mass_clients
        FROM (
                 SELECT c.id, c.last_name, c.first_name, c.date_of_birth, c.email, c.phone
                 FROM t_clients c
             );

        RETURN l_mass_clients;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN NULL;
    END;

-------------------------------------------------------------------------------
    FUNCTION f_get_client(p_id NUMBER)
        RETURN CLOB
        IS
        l_client_info CLOB;
    BEGIN
        SELECT JSON_OBJECT(
                       'fi'          VALUE (c.last_name || ' ' || c.first_name),
                       'date_of_birth' VALUE TO_CHAR(c.date_of_birth, 'DD.MM.YYYY'),
                       'phone'       VALUE c.phone,
                       'email'       VALUE c.email
                   )
        INTO l_client_info
        FROM t_clients c
        WHERE c.id = p_id;

        RETURN l_client_info;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN NULL;
    END;

-------------------------------------------------------------------------------
    FUNCTION f_get_mass_xml
        RETURN CLOB
        IS
        l_mass_clients CLOB;
    BEGIN
        SELECT XMLAGG(
                       XMLELEMENT("client",
                                  XMLELEMENT("last_name", last_name),
                                  XMLELEMENT("first_name", first_name),
                                  XMLELEMENT("date_of_birth", TO_CHAR(date_of_birth, 'DD.MM.YYYY')),
                                  XMLELEMENT("email", email),
                                  XMLELEMENT("phone", phone)
                           )
                   ).getCLOBVal()
        INTO l_mass_clients
        FROM (
                 SELECT c.last_name,
                        c.first_name,
                        c.date_of_birth,
                        c.email,
                        c.phone
                 FROM t_clients c
             );

        RETURN '{"RESULT": "' || l_mass_clients || '"}';
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN NULL;
    END;

END PKG_API;