---

DROP FUNCTION ourpension.f_create_snv_incoming_tessa(text, text, bigint, integer);



CREATE OR REPLACE FUNCTION ourpension.f_create_snv_incoming_tessa(
    p_snils text,
    p_full_name text,
    p_id_file bigint,
    p_user_id integer
)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
    _tessa_params json;
    _id_card text;
    _id_doc text;
    _tessa_path text := 'Integration/CreateCard';

    _file_name text;
    _file bytea;
    _username text;
BEGIN

    IF p_id_file IS NULL THEN
        RETURN NULL;
    END IF;

    SELECT u.username
    INTO _username
    FROM security."user" u
    WHERE u.id = p_user_id;

    IF _username IS NULL OR btrim(_username) = '' THEN
        RETURN NULL;
    END IF;

    SELECT d.file_name, d.file
    INTO _file_name, _file
    FROM ourpension.year_tax_deduction d
    WHERE d.id = p_id_file;

    IF _file_name IS NULL OR btrim(_file_name) = '' OR _file IS NULL THEN
        RETURN NULL;
    END IF;

    _tessa_params := json_build_object(
        'System', '5',
        'CardTypeID', '32078ec6-7b20-42c5-a4e8-77ae3b7a4c7c',
        'AuthorAdLogin', _username,
        'SubTypeID', '7eded5d0-8333-441f-9dca-031133205727',
        'SubTypeName', 'Сервисное заявление ФЛ',
        'OutgoingSubTypeID', 'c6137918-3652-44c8-b364-91aaa0264763',
        'OutgoingSubTypeName', 'Справка/ответ ФНС о налоговом вычете',
        'Summary', 'Справка о подтверждении получения/неполучения налогового вычета',
        'DeliveryMethodID', '4',
        'DeliveryMethodName', 'Личный прием',
        'SNILS', p_snils,
        'FullName', p_full_name,
        'NPF360Link', 'http://',
        'LinkCardID', '6f95b490-6f8f-46d1-945d-42650496e40c'
    );

    SELECT value || _tessa_path
    INTO _tessa_path
    FROM tools.npf360_settings
    WHERE "name" = 'TESSA_API';

    RAISE NOTICE '1) JSON param: %', _tessa_params::text;
    RAISE NOTICE '1.1) username: %', _username;

    _id_card := convert_from(
        robot.post_json(_tessa_path, _tessa_params),
        'UTF8'
    );

    RAISE NOTICE '2) id_card: %', _id_card;

    IF utils.f_is_tessa_guid(_id_card) THEN

        _id_doc := utils.post_file_to_tessa_card(
            _id_card,
            _username,
            _file_name,
            _file
        );

        RAISE NOTICE '3) id_doc: %', _id_doc;

        PERFORM utils.send_signal_to_tessa(
            _id_card,
            'complete1'
        );

        UPDATE ourpension.year_tax_deduction
        SET tessa_card_id = _id_card
        WHERE id = p_id_file;

    ELSE
        RETURN NULL;
    END IF;

    RETURN json_build_object(
        'id_card', _id_card,
        'id_doc', _id_doc,
        'JSON_params', _tessa_params
    )::text;
END;
$function$;