---

-- DROP FUNCTION integration.get_unv_available_contracts_api(text);

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$
;