---

-- Установка в TEST_BUD_32.
-- Требуются существующие GET и report.unv_api_request.
-- XML и уведомления в этой версии не создаются.
-- Совместную работу с ручной операцией и регламентом
-- подключаем отдельным следующим шагом.

BEGIN;

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

-- Регистрируем метод до первого вызова через ws.getdata.
INSERT INTO ws.methods (
    id,
    code,
    resp_lookup_sec
)
SELECT
    nextval('ws.seq_common'),
    'integration.submit_unv_application_api',
    0
WHERE NOT EXISTS (
    SELECT 1
    FROM ws.methods
    WHERE code = 'integration.submit_unv_application_api'
);

-- Отключаем кэш только для нового POST-метода.
UPDATE ws.methods
SET resp_lookup_sec = 0
WHERE code = 'integration.submit_unv_application_api';

COMMIT;