---

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

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)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
BEGIN
  
    IF p_type IS NULL OR p_type = 'NONE' THEN
        RETURN 0;
    END IF;

   
    MERGE INTO ourpension.year_tax_deduction AS ytd
    USING (
        SELECT 
            COALESCE(p_date, atc."date") AS calc_date,
            p_max_tax_deduction AS p_max_tax_deduction, 
            p_sum_income AS p_sum_income, 
            p_sum_tax_deduction AS p_sum_tax_deduction, 
            p_type AS p_type, 
            p_year AS p_year, 
            p_application_termination_contract_id AS p_atc_id, 
            p_individual_id AS p_ind_id, 
            'СНВ-' || p_application_termination_contract_id AS p_number, 
            (SELECT a.id FROM ourpension.agent a WHERE a.user_id = p_user_id) AS p_agent_id, 
            (SELECT f.bytes FROM dev.upload_file f WHERE f.code = 'snv_card' AND f.username = p_username) AS p_file, 
            (SELECT f.name FROM dev.upload_file f WHERE f.code = 'snv_card' AND f.username = p_username) AS p_file_name
        FROM ourpension.application_termination_contract atc
        WHERE atc.id = p_application_termination_contract_id
    ) AS src 
    ON (ytd."year" = src.p_year AND ytd."number" = src.p_number)
    WHEN MATCHED THEN 
        UPDATE SET 
            "date" = src.calc_date, 
            max_tax_deduction = src.p_max_tax_deduction, 
            sum_income = src.p_sum_income, 
            sum_tax_deduction = src.p_sum_tax_deduction, 
            "type" = src.p_type, 
            "version" = now(), 
            application_termination_contract_id = src.p_atc_id, 
            individual_id = src.p_ind_id, 
            agent_id = src.p_agent_id, 
            file = src.p_file, 
            file_name = src.p_file_name
    WHEN NOT MATCHED THEN 
        INSERT (
            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
        ) 
        VALUES (
            nextval('ourpension.year_tax_deduction_sequence'), 
            src.calc_date, src.p_max_tax_deduction, src.p_sum_income, src.p_sum_tax_deduction, 
            src.p_type, now(), src.p_year, src.p_atc_id, src.p_ind_id, src.p_number, 
            src.p_agent_id, src.p_file, src.p_file_name
        );

    -- 3. Очистка временных файлов
    DELETE FROM dev.upload_file WHERE code = 'snv_card' AND username = p_username;

    -- 4. Интеграция с Tessa
    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),
        (SELECT td.id FROM ourpension.year_tax_deduction td 
         WHERE td.year = p_year AND td.number = 'СНВ-' || p_application_termination_contract_id LIMIT 1)
    )
    FROM ourpension.individual i
    WHERE i.id = p_individual_id;

    RETURN 0;
END;
$function$
;