---

-- 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$
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;
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_number := 'СНВ-' || p_application_termination_contract_id;

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

    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."number" = v_number
      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
    )
    VALUES (
        nextval('ourpension.year_tax_deduction_sequence'),
        v_calc_date,
        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
    )
    RETURNING id INTO v_new_id;

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

    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
    )
    FROM ourpension.individual i
    WHERE i.id = p_individual_id;

    RETURN 0;
END;
$function$;