---

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

DECLARE

    v_request jsonb;

    v_request_id uuid;

    v_client_id integer;

    v_contract_id integer;

    v_flag integer;

    v_year integer;

    v_current_year integer :=

        EXTRACT(YEAR FROM CURRENT_DATE)::integer;

    v_max_year integer;



    v_contract jsonb;

    v_period jsonb;

    v_contracts jsonb := '[]'::jsonb;

    v_periods jsonb;

    v_payload jsonb;



    v_saved report.unv_api_request%ROWTYPE;



    v_available jsonb;

    v_available_contract jsonb;

    v_available_period jsonb;



    v_selected jsonb := '[]'::jsonb;

    v_selected_item jsonb;

    v_items jsonb := '[]'::jsonb;

    v_response jsonb;



    v_snils text;

    v_code_id report.smev_register_codes.id%TYPE;

    v_registry_id report.smev_register.id%TYPE;

    v_item_id report.unv_registry_item.id%TYPE;



    v_amount numeric;

    v_deduction numeric;

    v_rows integer;

BEGIN

    -- Проверяем формат запроса.

    BEGIN

        v_request := p_request::jsonb;

    EXCEPTION

        WHEN invalid_text_representation THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'Передан невалидный JSON';

    END;



    IF jsonb_typeof(v_request) IS DISTINCT FROM 'object' THEN

        RAISE EXCEPTION USING

            ERRCODE = 'P0001',

            MESSAGE = 'Ожидается JSON-объект';

    END IF;



    BEGIN

        v_request_id := (v_request ->> 'request_id')::uuid;

    EXCEPTION

        WHEN invalid_text_representation THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'request_id должен быть UUID';

    END;



    IF v_request_id IS NULL THEN

        RAISE EXCEPTION USING

            ERRCODE = 'P0001',

            MESSAGE = 'Не указан request_id';

    END IF;



    IF jsonb_typeof(v_request -> 'id') IS DISTINCT FROM 'number'

     OR COALESCE(v_request ->> 'id', '') !~ '^[1-9][0-9]{0,9}$'

    THEN

        RAISE EXCEPTION USING

            ERRCODE = 'P0001',

            MESSAGE = 'id должен быть положительным целым числом';

    END IF;



    IF (v_request ->> 'id')::bigint > 2147483647 THEN

        RAISE EXCEPTION USING

            ERRCODE = 'P0001',

            MESSAGE = 'id превышает допустимый диапазон';

    END IF;



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



    IF jsonb_typeof(v_request -> 'contracts')

     IS DISTINCT FROM 'array'

    THEN

        RAISE EXCEPTION USING

            ERRCODE = 'P0001',

            MESSAGE = 'contracts должен быть массивом';

    END IF;



    IF jsonb_array_length(v_request -> 'contracts') = 0 THEN

        RAISE EXCEPTION USING

            ERRCODE = 'P0001',

            MESSAGE = 'Не выбраны договоры';

    END IF;



    -- Проверяем договоры и приводим запрос к единому виду.

    FOR v_contract IN

        SELECT value

        FROM jsonb_array_elements(v_request -> 'contracts')

    LOOP

        IF jsonb_typeof(v_contract) IS DISTINCT FROM 'object' THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'Элемент contracts должен быть объектом';

        END IF;



        IF jsonb_typeof(v_contract -> 'contract_id')

         IS DISTINCT FROM 'number'

         OR COALESCE(v_contract ->> 'contract_id', '')

             !~ '^[1-9][0-9]{0,9}$'

        THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'Некорректный contract_id';

        END IF;



        IF (v_contract ->> 'contract_id')::bigint > 2147483647 THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'contract_id превышает допустимый диапазон';

        END IF;



        v_contract_id := (v_contract ->> 'contract_id')::integer;



        IF EXISTS (

            SELECT 1

            FROM jsonb_array_elements(v_contracts) x

            WHERE (x.value ->> 'contract_id')::integer =

                 v_contract_id

        ) THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'Один договор указан несколько раз';

        END IF;



        IF jsonb_typeof(v_contract -> 'annual_deduction_flag')

         IS DISTINCT FROM 'number'

         OR COALESCE(v_contract ->> 'annual_deduction_flag', '')

             NOT IN ('0', '1')

        THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'annual_deduction_flag должен быть 0 или 1';

        END IF;



        v_flag := (v_contract ->> 'annual_deduction_flag')::integer;



        IF jsonb_typeof(v_contract -> 'periods')

         IS DISTINCT FROM 'array'

        THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'periods должен быть массивом годов';

        END IF;



        IF jsonb_array_length(v_contract -> 'periods')

         NOT BETWEEN 1 AND 3

        THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'Для договора нужно выбрать от одного до трёх периодов';

        END IF;



        v_periods := '[]'::jsonb;



        FOR v_period IN

            SELECT value

            FROM jsonb_array_elements(v_contract -> 'periods')

        LOOP

            IF jsonb_typeof(v_period) IS DISTINCT FROM 'number'

             OR v_period::text !~ '^[0-9]{4}$'

            THEN

                RAISE EXCEPTION USING

                    ERRCODE = 'P0001',

                    MESSAGE = 'Налоговый период должен быть четырёхзначным целым годом';

            END IF;



            v_year := v_period::text::integer;



            IF v_periods @> jsonb_build_array(v_year) THEN

                RAISE EXCEPTION USING

                    ERRCODE = 'P0001',

                    MESSAGE = 'Для договора повторяется налоговый период';

            END IF;



            v_periods :=

                v_periods || jsonb_build_array(v_year);

        END LOOP;



        SELECT jsonb_agg(

            x.value ORDER BY x.value::text::integer

        )

        INTO v_periods

        FROM jsonb_array_elements(v_periods) x;



        v_contracts :=

            v_contracts || jsonb_build_array(

                jsonb_build_object(

                    'contract_id', v_contract_id,

                    'periods', v_periods,

                    'annual_deduction_flag', v_flag

                )

            );

    END LOOP;



    SELECT jsonb_agg(

        x.value ORDER BY (x.value ->> 'contract_id')::integer

    )

    INTO v_contracts

    FROM jsonb_array_elements(v_contracts) x;



    v_payload := jsonb_build_object(

        'id', v_client_id,

        'contracts', v_contracts

    );



    -- Запоминаем запрос.

    -- Порядок договоров/годов и служебные RequestParams не влияют на распознавание повторной подачи.

    INSERT INTO report.unv_api_request (

        request_id,

        client_id,

        request_payload

    )

    VALUES (

        v_request_id,

        v_client_id,

        v_payload

    )

    ON CONFLICT (request_id) DO NOTHING;



    GET DIAGNOSTICS v_rows = ROW_COUNT;



    IF v_rows = 0 THEN

        SELECT *

        INTO STRICT v_saved

        FROM report.unv_api_request

        WHERE request_id = v_request_id

        FOR UPDATE;



        IF v_saved.client_id <> v_client_id

         OR v_saved.request_payload IS DISTINCT FROM v_payload

        THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'request_id уже использован с другими параметрами';

        END IF;



        IF v_saved.completed_at IS NULL

         OR v_saved.response_payload IS NULL

        THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = 'У запроса отсутствует завершённый результат';

        END IF;



        RETURN v_saved.response_payload::text;

    END IF;



    -- Последовательная обработка новых запросов этого POST.

    -- Ручная операция и регламент пока не используют этот lock.

    PERFORM pg_advisory_xact_lock(20260916, 1);



    SELECT NULLIF(TRIM(i.insurance_number), '')

    INTO v_snils

    FROM ourpension.individual i

    WHERE i.id = v_client_id;



    IF v_snils IS NULL THEN

        RAISE EXCEPTION USING

            ERRCODE = 'P0001',

            MESSAGE = 'Клиент не найден или не указан СНИЛС';

    END IF;



    -- Используем существующий GET для проверки доступности и получения рассчитанных сумм.

    v_available :=

        integration.get_unv_available_contracts_api(

            jsonb_build_object('id', v_client_id)::text

        )::jsonb;



    IF jsonb_typeof(v_available -> 'contracts')

     IS DISTINCT FROM 'array'

    THEN

        RAISE EXCEPTION USING

            ERRCODE = 'XX000',

            MESSAGE = 'GET доступных договоров вернул неожиданный формат';

    END IF;



    -- Проверяем все выбранные пары до создания реестров.

    FOR v_contract IN

        SELECT value

        FROM jsonb_array_elements(v_contracts)

    LOOP

        v_contract_id :=

            (v_contract ->> 'contract_id')::integer;



        SELECT x.value

        INTO v_available_contract

        FROM jsonb_array_elements(v_available -> 'contracts') x

        WHERE (x.value ->> 'contract_id')::integer =

             v_contract_id;



        IF v_available_contract IS NULL THEN

            RAISE EXCEPTION USING

                ERRCODE = 'P0001',

                MESSAGE = format(

                    'Договор %s недоступен для УНВ данного клиента',

                    v_contract_id

                );

        END IF;



        IF jsonb_typeof(v_available_contract -> 'periods')

         IS DISTINCT FROM 'array'

         OR COALESCE(

             v_available_contract ->> 'contract_type',

             ''

         ) NOT IN ('NPO', 'PDS')

        THEN

            RAISE EXCEPTION USING

                ERRCODE = 'XX000',

                MESSAGE = format(

                    'GET вернул некорректные данные договора %s',

                    v_contract_id

                );

        END IF;



        FOR v_period IN

            SELECT value

            FROM jsonb_array_elements(v_contract -> 'periods')

        LOOP

            v_year := v_period::text::integer;



            IF v_year < v_current_year - 3

             OR v_year >= v_current_year

            THEN

                RAISE EXCEPTION USING

                    ERRCODE = 'P0001',

                    MESSAGE = 'Доступны только три завершённых налоговых года';

            END IF;



            SELECT x.value

            INTO v_available_period

            FROM jsonb_array_elements(

                v_available_contract -> 'periods'

            ) x

            WHERE (x.value ->> 'tax_period_year')::integer =

                 v_year;



            IF v_available_period IS NULL THEN

                RAISE EXCEPTION USING

                    ERRCODE = 'P0001',

                    MESSAGE = format(

                        'Период %s недоступен по договору %s',

                        v_year,

                        v_contract_id

                    );

            END IF;



            v_amount :=

                (v_available_period ->> 'contributions_amount')::numeric;



            v_deduction :=

                (v_available_period ->> 'preliminary_deduction_amount')::numeric;



            IF v_amount IS NULL

             OR v_amount <= 0

             OR v_deduction IS NULL

             OR v_deduction < 0

            THEN

                RAISE EXCEPTION USING

                    ERRCODE = 'XX000',

                    MESSAGE = 'GET вернул некорректные суммы';

            END IF;



            -- Повтор в любом неудалённом реестре отклоняется.

            -- Корректировки и отказы требуют отдельного сценария.

            IF EXISTS (

                SELECT 1

                FROM report.unv_registry_item i

                JOIN report.smev_register r

                    ON r.id = i.registry_id

                WHERE i.client_id = v_client_id

                 AND i.contract_id = v_contract_id

                 AND i.tax_period_year = v_year

                 AND r.sample_status IS DISTINCT FROM 'Удалён'

            ) THEN

                RAISE EXCEPTION USING

                    ERRCODE = 'P0001',

                    MESSAGE = format(

                        'Договор %s за %s год уже включён в неудалённый реестр',

                        v_contract_id,

                        v_year

                    );

            END IF;



            v_selected :=

                v_selected || jsonb_build_array(

                    jsonb_build_object(

                        'contract_id', v_contract_id,

                        'tax_period_year', v_year,

                        'contract_type',

                            v_available_contract ->> 'contract_type',

                        'annual_deduction_flag',

                            (v_contract ->> 'annual_deduction_flag')::integer,

                        'contributions_amount', v_amount,

                        'preliminary_deduction_amount', v_deduction

                    )

                );

        END LOOP;

    END LOOP;



    SELECT id

    INTO v_code_id

    FROM report.smev_register_codes

    WHERE short_name = 'УНВ'

    ORDER BY id

    LIMIT 1;



    IF v_code_id IS NULL THEN

        RAISE EXCEPTION USING

            ERRCODE = 'XX000',

            MESSAGE = 'В справочнике smev_register_codes отсутствует УНВ';

    END IF;



    -- Создаём строки выбранных договоров и периодов.

    FOR v_selected_item IN

        SELECT value

        FROM jsonb_array_elements(v_selected)

        ORDER BY

            (value ->> 'tax_period_year')::integer,

            (value ->> 'contract_id')::integer

    LOOP

        v_year :=

            (v_selected_item ->> 'tax_period_year')::integer;



        -- Дополняем только реестр в статусе «Сформирован».

        SELECT r.id

        INTO v_registry_id

        FROM report.smev_register r

        WHERE r.smev_register_type_code = 'УНВ'

         AND r."year" = v_year

         AND r."type" = 'OUT'

         AND r.sample_status = 'Сформирован'

        ORDER BY r.id DESC

        LIMIT 1

        FOR UPDATE;



        IF v_registry_id IS NULL THEN

            INSERT INTO report.smev_register (

                id,

                smev_register_type_code,

                smev_register_codes_id,

                "year",

                "date",

                "type",

                sample_status

            )

            VALUES (

                nextval('report.smev_register_id_sequence'),

                'УНВ',

                v_code_id,

                v_year,

                CURRENT_DATE,

                'OUT',

                'Сформирован'

            )

            RETURNING id INTO v_registry_id;

        END IF;



        INSERT INTO report.unv_registry_item (

            registry_id,

            client_id,

            snils,

            contract_id,

            contract_type,

            tax_period_year,

            contributions_amount,

            preliminary_deduction_amount,

            annual_deduction_flag,

            status,

            send_count

        )

        VALUES (

            v_registry_id,

            v_client_id,

            v_snils,

            (v_selected_item ->> 'contract_id')::integer,

            v_selected_item ->> 'contract_type',

            v_year,

            (v_selected_item ->> 'contributions_amount')::numeric,

            (v_selected_item ->> 'preliminary_deduction_amount')::numeric,

            (v_selected_item ->> 'annual_deduction_flag')::integer,

            'Сформирован',

            0

        )

        RETURNING id INTO v_item_id;



        v_items :=

            v_items || jsonb_build_array(

                jsonb_build_object(

                    'item_id', v_item_id,

                    'registry_id', v_registry_id,

                    'contract_id',

                        (v_selected_item ->> 'contract_id')::integer,

                    'tax_period_year', v_year,

                    'status', 'Сформирован'

                )

            );

    END LOOP;



    -- Сохраняем настройку ежегодного вычета по каждому договору.

    FOR v_contract IN

        SELECT value

        FROM jsonb_array_elements(v_contracts)

    LOOP

        SELECT MAX(x.value::text::integer)

        INTO v_max_year

        FROM jsonb_array_elements(v_contract -> 'periods') x;



        INSERT INTO report.unv_annual_setting AS s (

            client_id,

            contract_id,

            annual_deduction_flag,

            selected_tax_period,

            source

        )

        VALUES (

            v_client_id,

            (v_contract ->> 'contract_id')::integer,

            (v_contract ->> 'annual_deduction_flag')::integer,

            v_max_year,

            'LKK'

        )

        ON CONFLICT (client_id, contract_id)

        DO UPDATE SET

            annual_deduction_flag =

                EXCLUDED.annual_deduction_flag,



            -- Подача за старый год не сдвигает

            -- начало ежегодного включения назад.

            selected_tax_period = GREATEST(

                s.selected_tax_period,

                EXCLUDED.selected_tax_period

            ),



            source = EXCLUDED.source,

            updated_at = CURRENT_TIMESTAMP;

    END LOOP;



    v_response := jsonb_build_object(

        'status', 'success',

        'request_id', v_request_id,

        'client_id', v_client_id,

        'message', 'Заявление принято. Строки реестра сформированы.',

        'items', v_items

    );



    UPDATE report.unv_api_request

    SET

        response_payload = v_response,

        completed_at = CURRENT_TIMESTAMP

    WHERE request_id = v_request_id;



    RETURN v_response::text;



EXCEPTION

    WHEN SQLSTATE 'P0001' THEN

        -- Все изменения текущего вызова откатываются.

        -- Остальные SQL-ошибки передаются в ws.sp_request_root.

        RETURN jsonb_build_object(

            'status', 'error',

            'request_id', v_request_id,

            'message', SQLERRM

        )::text;

END;

$function$