---

CREATE OR REPLACE FUNCTION integration.get_unv_available_contracts_api(p_request text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$

DECLARE

    v_params     jsonb;

    v_client_id  integer;

    v_result     jsonb;

BEGIN

    /* Проверка входного JSON */

    IF NOT utils.check_json(p_request) THEN

        RETURN jsonb_build_object(

            'status', 'Ошибка',

            'message', 'Неверный формат JSON'

        )::text;

    END IF;



    v_params := p_request::jsonb;



    /* Проверка идентификатора клиента */

    IF COALESCE(v_params ->> 'id', '') !~ '^[0-9]+$' THEN

        RETURN jsonb_build_object(

            'status', 'Ошибка',

            'message', 'Не указан корректный идентификатор клиента'

        )::text;

    END IF;



    v_client_id := (v_params ->> 'id')::integer;



    IF NOT EXISTS (

        SELECT 1

        FROM ourpension.individual ind

        WHERE ind.id = v_client_id

    ) THEN

        RETURN jsonb_build_object(

            'status', 'Ошибка',

            'message', 'Клиент не найден'

        )::text;

    END IF;



    WITH params AS (

        SELECT

            EXTRACT(YEAR FROM CURRENT_DATE)::integer AS current_year

    ),

    contributions AS (

        SELECT

            c.id AS contract_id,

            c.number AS contract_number,

            c.service_type AS contract_type,

            c."date" AS contract_date,

            EXTRACT(YEAR FROM o.operation_date)::integer

                AS tax_period_year,

            SUM(t.value) AS contributions_amount

        FROM back_office.incoming_order io

        JOIN back_office.incoming_order_operation ioo

            ON ioo.incoming_order_id = io.id

        JOIN back_office.operation o

            ON o.id = ioo.operation_id

        JOIN back_office.operation_type ot

            ON ot.id = o.operation_type_id

        JOIN back_office."transaction" t

            ON t.operation_id = o.id

        JOIN back_office.account a

            ON a.id = t.credit_account_id

        JOIN back_office.account_type at

            ON at.id = a.account_type_id

        JOIN ourpension.contract c

            ON c.id = io.contract_id

        JOIN ourpension.individual ind

            ON ind.id = io.individual_id

        CROSS JOIN params p

        WHERE io.individual_id = v_client_id



          /* Три предыдущих налоговых периода */

          AND o.operation_date >=

              make_date(p.current_year - 3, 1, 1)

          AND o.operation_date <

              make_date(p.current_year, 1, 1)



          /* Только авторизованные и неудалённые операции */

          AND o.approved IS NOT NULL

          AND o.deleted IS NULL

          AND io.payment_return IS NOT TRUE



          /* Проверка кода операции и мнемоники счёта */

          AND (

                (

                    c.service_type = 'NPO'

                    AND ot.code = 'DEPOSIT_ENROLLMENT'

                    AND at.mnemonics = 'ИПС_ФЛ'

                )

                OR

                (

                    c.service_type = 'PDS'

                    AND ot.code = 'PDS_DEPOSIT_ENROLLMENT'

                    AND at.mnemonics = 'ПДС СВ'

                )

              )



          /* Клиент должен относиться к договору */

          AND (

                c.individual_id = v_client_id

                OR (

                    c.service_type = 'PDS'

                    AND c.family_member IS TRUE

                )

              )



          /* СНИЛС должен быть заполнен */

          AND NULLIF(TRIM(ind.insurance_number), '') IS NOT NULL



          /* Исключаем умерших клиентов */

          AND NOT EXISTS (

              SELECT 1

              FROM ourpension.application_death_info adi

              WHERE adi.individual_id = ind.id

                AND adi.accepted IS NOT NULL

                AND adi.canceled IS NULL

          )

        GROUP BY

            c.id,

            c.number,

            c.service_type,

            c."date",

            EXTRACT(YEAR FROM o.operation_date)

        HAVING SUM(t.value) > 0

    ),

    calculated AS (

        SELECT

            c.contract_id,

            c.contract_number,

            c.contract_type,

            c.tax_period_year,

            c.contributions_amount,



            /* Предварительный вычет — 13% с учётом лимита */

            ROUND(

                LEAST(

                    c.contributions_amount,

                    CASE

                        WHEN c.contract_type = 'NPO'

                         AND c.contract_date < DATE '2025-01-01'

                            THEN 19500::numeric

                        ELSE 52000::numeric

                    END

                ) * 0.13,

                2

            ) AS preliminary_deduction_amount

        FROM contributions c

    ),

    contracts AS (

        SELECT

            c.contract_id,

            c.contract_number,

            c.contract_type,

            COALESCE(s.annual_deduction_flag, 0)

                AS annual_deduction_flag,

            jsonb_agg(

                jsonb_build_object(

                    'tax_period_year',

                        c.tax_period_year,

                    'contributions_amount',

                        c.contributions_amount,

                    'preliminary_deduction_amount',

                        c.preliminary_deduction_amount

                )

                ORDER BY c.tax_period_year DESC

            ) AS periods

        FROM calculated c

        LEFT JOIN report.unv_annual_setting s

            ON s.client_id = v_client_id

           AND s.contract_id = c.contract_id

        GROUP BY

            c.contract_id,

            c.contract_number,

            c.contract_type,

            s.annual_deduction_flag

    )

    SELECT jsonb_build_object(

        'client_id',

            v_client_id,

        'contracts',

            COALESCE(

                jsonb_agg(

                    jsonb_build_object(

                        'contract_id',

                            c.contract_id,

                        'contract_number',

                            c.contract_number,

                        'contract_type',

                            c.contract_type,

                        'annual_deduction_flag',

                            c.annual_deduction_flag,

                        'periods',

                            c.periods

                    )

                    ORDER BY

                        c.contract_type,

                        c.contract_number

                ),

                '[]'::jsonb

            )

    )

    INTO v_result

    FROM contracts c;



    RETURN v_result::text;

END;

$function$