-- Создаем БД 
CREATE DATABASE xdac_db;

USE xdac_db;

-- Создаем схему api
CREATE SCHEMA api;

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

-- Устанавливаем схему для пользователя
ALTER USER XDAC_USER WITH DEFAULT_SCHEMA = api;

-- Предоставляем права на схему
GRANT CONTROL ON SCHEMA :: api TO XDAC_USER;

-- ПОДКЛЮЧАЕМСЯ НОВЫМ ПОЛЬЗОВАТЕЛЕМ
-------------------------------------------------------------------------------
CREATE TABLE api.t_clients (
                               id            INT IDENTITY(1,1) PRIMARY KEY,
                               last_name     NVARCHAR(50) NOT NULL,
                               first_name    NVARCHAR(50) NOT NULL,
                               second_name   NVARCHAR(50),
                               phone         NVARCHAR(11) NOT NULL,
                               date_of_birth DATE,
                               email         NVARCHAR(100),
                               cli_type      NVARCHAR(2) DEFAULT 'FL'
);
INSERT INTO api.t_clients (last_name, first_name, second_name, phone, date_of_birth, email, cli_type)
VALUES
    (N'Горелов', N'Вадим', N'Захарович', '79991234567', '1937-04-11', 'info@xsquare.ru', 'FL'),
    (N'Федотов', N'Павел', N'Игоревич', '79991234567', '2011-06-17', 'info@xsquare.ru', 'FL'),
    (N'Артамонов', N'Роберт', N'Николаевич', '79991234567', '1957-02-24', 'info@xsquare.ru', 'FL'),
    (N'Чесноков', N'Роберт', N'Алексеевич', '79991234567', '1994-12-18', 'info@xsquare.ru', 'FL'),
    (N'Романов', N'Тихон', N'Семёнович', '79991234567', '1954-08-23', 'info@xsquare.ru', 'FL'),
    (N'Жуков', N'Тихон', N'Платонович', '79991234567', '2018-03-14', 'info@xsquare.ru', 'FL'),
    (N'Попов', N'Игорь', N'Семёнович', '79991234567', '2018-07-14', 'info@xsquare.ru', 'FL');

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

CREATE OR ALTER FUNCTION f_hello()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN '{"GET_RETURN": "Hello from MSSQL Server!"}';
END;


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

CREATE FUNCTION api.f_concat_str
(
    @p_first_str NVARCHAR(MAX) = '',
    @p_second_str NVARCHAR(MAX) = ''
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @l_result NVARCHAR(MAX);
    SET @l_result = CONCAT(@p_first_str, @p_second_str);

    RETURN CONCAT('{"CONCAT_RESULT": "', @l_result, '"}');
END;


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

CREATE FUNCTION api.f_json_parse
(
    @p_json NVARCHAR(MAX)
)
    RETURNS NVARCHAR(MAX)
AS
BEGIN
    IF ISJSON(@p_json) = 1
        BEGIN
            RETURN CONCAT('{"JSON_OUTPUT": ', @p_json, '}');
        END
    RETURN N'INVALID JSON!';
END;


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

CREATE FUNCTION api.f_get_mass()
    RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @l_mass_clients NVARCHAR(MAX);

    WITH tbl AS (
        SELECT
            c.last_name,
            c.first_name,
            FORMAT(c.date_of_birth, 'dd.MM.yyyy') AS date_of_birth,
            c.email,
            c.phone
        FROM api.t_clients c
    )

    SELECT @l_mass_clients = (
        SELECT
            last_name,
            first_name,
            date_of_birth,
            email,
            phone
        FROM tbl
        FOR JSON PATH
    );

    RETURN @l_mass_clients;
END

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

CREATE FUNCTION api.f_get_client
(
    @p_id BIGINT
)
    RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @l_client_info NVARCHAR(MAX);

    SELECT @l_client_info = (
        SELECT
            CONCAT(c.last_name, ' ', c.first_name) AS fi,
            FORMAT(c.date_of_birth, 'dd.MM.yyyy') AS date_of_birth,
            c.phone,
            c.email
        FROM api.t_clients c
        WHERE c.id = @p_id
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );

    RETURN @l_client_info;
END;

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