---

WITH params AS (
    SELECT
        CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS integer) - 1
            AS target_year
),
target_registry AS (
    SELECT r.id
    FROM report.smev_register r
    CROSS JOIN params p
    WHERE r.smev_register_type_code = 'УНВ'
      AND r."year" = p.target_year
      AND r.sample_status IS DISTINCT FROM 'Удалён'
    ORDER BY r.id DESC
    LIMIT 1
),
annual_contracts AS (
    SELECT
        s.client_id,
        s.contract_id
    FROM report.unv_annual_setting s
    CROSS JOIN params p
    WHERE s.annual_deduction_flag = 1

      /* Автоматическое включение начинается со следующего периода */
      AND s.selected_tax_period < p.target_year
),
contributions AS (
    SELECT
        ac.client_id,
        ac.contract_id,
        ind.insurance_number AS snils,
        c.service_type AS contract_type,
        c.date AS contract_date,
        SUM(t.value) AS contributions_amount
    FROM annual_contracts ac
    JOIN back_office.incoming_order io
        ON io.individual_id = ac.client_id
       AND io.contract_id = ac.contract_id
    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."transaction" t
        ON t.operation_id = ioo.operation_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 = ac.contract_id
    JOIN ourpension.individual ind
        ON ind.id = ac.client_id
    CROSS JOIN params p
    WHERE io.payment_return IS NOT TRUE

      /* Взносы учитываются по дате операции */
      AND CAST(EXTRACT(YEAR FROM o.operation_date) AS integer) =
          p.target_year

      /* Только личные взносы по нужным мнемоникам */
      AND (
            (c.service_type = 'NPO' AND at.mnemonics = 'ИПС_ФЛ')
            OR
            (c.service_type = 'PDS' AND at.mnemonics = 'ПДС СВ')
          )

      /* Личный договор либо ПДС в пользу близкого родственника */
      AND (
            c.individual_id = ac.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
        ac.client_id,
        ac.contract_id,
        ind.insurance_number,
        c.service_type,
        c.date
    HAVING SUM(t.value) > 0
)
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
)
SELECT
    tr.id,
    contr.client_id,
    contr.snils,
    contr.contract_id,
    contr.contract_type,
    CAST(p.target_year AS smallint),
    contr.contributions_amount,

    /* Предварительный вычет: 13% с установленными ограничениями */
    CASE
        WHEN contr.contract_type = 'NPO'
         AND contr.contract_date < DATE '2025-01-01'
        THEN LEAST(
            ROUND(contr.contributions_amount * 13 / 100, 2),
            CAST(19500 AS numeric)
        )
        ELSE LEAST(
            ROUND(contr.contributions_amount * 13 / 100, 2),
            CAST(52000 AS numeric)
        )
    END,

    1,
    'Сформирован',
    0
FROM target_registry tr
CROSS JOIN contributions contr
CROSS JOIN params p
WHERE NOT EXISTS (
    SELECT 1
    FROM report.unv_registry_item existing_item
    WHERE existing_item.registry_id = tr.id
      AND existing_item.client_id = contr.client_id
      AND existing_item.contract_id = contr.contract_id
      AND existing_item.tax_period_year =
          CAST(p.target_year AS smallint)
);