---

#set ($rez1 = $db.eval("SELECT ourpension.f_merge_year_tax_deduction(
    CAST(NULLIF(CAST(:p_application_termination_contract_id AS text), '') AS integer),
    CAST(CASE
        WHEN CAST(:p_date AS text) = '9999-99-99' OR CAST(:p_date AS text) = '' THEN NULL
        ELSE CAST(:p_date AS text)
    END AS date),
    CAST(NULLIF(CAST(:p_individual_id AS text), '') AS integer),
    CAST(NULLIF(CAST(:p_max_tax_deduction AS text), '') AS integer),
    CAST(NULLIF(CAST(:p_sum_income AS text), '') AS numeric),
    CAST(CASE
        WHEN CAST(:p_sum_tax_deduction AS text) = '-1' OR CAST(:p_sum_tax_deduction AS text) = '' THEN NULL
        ELSE CAST(:p_sum_tax_deduction AS text)
    END AS numeric),
    CAST(:p_type AS text),
    CAST(NULLIF(CAST(:p_year AS text), '') AS integer),
    CAST(:username AS text),
    CAST(NULLIF(CAST(:user_id AS text), '') AS integer),
    CAST(NULLIF(CAST(:p_batch_id AS text), '') AS integer),
	CAST(NULLIF(CAST(:tessa_doc_id AS text), '') AS integer),
	  CAST(NULLIF(CAST(:p_version_datetime AS text), '') AS timestamp)
)
"))




-- DROP FUNCTION ourpension.f_merge_year_tax_deduction(int4, date, int4, int4, numeric, numeric, text, int4, text, int4, int4, int4, timestamp);

CREATE OR REPLACE FUNCTION ourpension.f_merge_year_tax_deduction(p_application_termination_contract_id integer, p_date date, p_individual_id integer, p_max_tax_deduction integer, p_sum_income numeric, p_sum_tax_deduction numeric, p_type text, p_year integer, p_username text, p_user_id integer, p_batch_id integer, tessa_doc_id integer, p_version_datetime timestamp without time zone DEFAULT NULL::timestamp without time zone)
RETURNS integer
LANGUAGE plpgsql
AS $function$
DECLARE
    v_calc_date date;
    v_number text;
    v_agent_id integer;
    v_file dev.upload_file.bytes%TYPE;
    v_file_name dev.upload_file.name%TYPE;
    v_new_id integer;
    v_card_link_id text;
BEGIN
    IF p_type IS NULL OR p_type = 'NONE' THEN
        RETURN 0;
    END IF;

    v_calc_date := COALESCE(
        p_date,
        (
            SELECT atc."date"
            FROM ourpension.application_termination_contract atc
            WHERE atc.id = p_application_termination_contract_id
            LIMIT 1
        )
    );

    v_new_id := nextval('ourpension.year_tax_deduction_sequence');
    v_number := 'СНВ-' || v_new_id || '_' || p_year;

    SELECT a.id
    INTO v_agent_id
    FROM ourpension.agent a
    WHERE a.user_id = p_user_id
    LIMIT 1;

select t_d.card_link_id into v_card_link_id
from integration.tessa_documents t_d
where t_d.id = tessa_doc_id;

    SELECT f.bytes, f.name
    INTO v_file, v_file_name
    FROM dev.upload_file f
    WHERE f.code = 'snv_card'
     AND f.username = p_username
    LIMIT 1;

    UPDATE ourpension.year_tax_deduction ytd
    SET
        canceled = now(),
        "version" = now()
    WHERE ytd."year" = p_year
     AND ytd.individual_id = p_individual_id
     AND ytd.canceled IS NULL;

    INSERT INTO ourpension.year_tax_deduction (
        id,
        "date",
        max_tax_deduction,
        sum_income,
        sum_tax_deduction,
        "type",
        "version",
        "year",
        application_termination_contract_id,
        individual_id,
        "number",
        agent_id,
        file,
        file_name,
        accepted,
        canceled,
        batch_id,
        tessa_card_id
    )
    VALUES (
        v_new_id,
        CASE
            WHEN p_version_datetime IS NOT NULL THEN p_version_datetime
            ELSE v_calc_date + CASE
                WHEN p_date IS NOT NULL THEN now()::time
                ELSE '00:00:00'::time
            END
        END,
        p_max_tax_deduction,
        p_sum_income,
        p_sum_tax_deduction,
        p_type,
        now(),
        p_year,
        p_application_termination_contract_id,
        p_individual_id,
        v_number,
        v_agent_id,
        v_file,
        v_file_name,
        now(),
        NULL,
        p_batch_id,
        v_card_link_id
    );

    DELETE FROM dev.upload_file
    WHERE code = 'snv_card'
     AND username = p_username;

     if v_card_link_id is null then
    
    PERFORM ourpension.f_create_snv_incoming_tessa(
    CAST(i.insurance_number AS text),
    CAST(btrim(concat_ws(' ', i.last_name, i.first_name, i.middle_name)) AS text),
    v_new_id,
    p_user_id
)
FROM ourpension.individual i
WHERE i.id = p_individual_id;
end if;

IF tessa_doc_id IS NOT NULL THEN
        UPDATE integration.tessa_documents
        SET app_id = p_batch_id
        WHERE id = tessa_doc_id;
    END IF;

    RETURN 0;
END;
$function$
;



-- DROP FUNCTION ourpension.f_create_snv_incoming_tessa(text, text, int8);

CREATE OR REPLACE FUNCTION ourpension.f_create_snv_incoming_tessa(p_snils text, p_full_name text, p_id_file bigint DEFAULT NULL::bigint)
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;
BEGIN
    
    IF p_id_file IS NULL 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', 'System',
        '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;


    _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, 'System', _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$
;