---

WITH unv_code AS (
    SELECT id
    FROM report.smev_register_codes
    WHERE short_name = 'УНВ'
    ORDER BY id
    LIMIT 1
),
params AS (
    SELECT
        CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS integer) - 1 AS target_year
)
INSERT INTO report.smev_register (
    id,
    smev_register_type_code,
    smev_register_codes_id,
    "year",
    "date",
    "type",
    sample_status
)
SELECT
    nextval('report.smev_register_id_sequence'),
    'УНВ',
    uc.id,
    p.target_year,
    CURRENT_DATE,
    'OUT',
    'Сформирован'
FROM unv_code uc
CROSS JOIN params p
WHERE NOT EXISTS (
    SELECT 1
    FROM report.smev_register r
    WHERE r.smev_register_type_code = 'УНВ'
      AND r."year" = p.target_year
      AND r.sample_status IS DISTINCT FROM 'Удалён'
);



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,
        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 io.date) AS integer) = p.target_year
      AND (
            (c.service_type = 'NPO' AND at.mnemonics = 'ИПС_ФЛ')
            OR
            (c.service_type = 'PDS' AND at.mnemonics = 'ПДС СВ')
          )
      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
      )
    GROUP BY
        ac.client_id,
        ac.contract_id,
        ind.insurance_number,
        c.service_type
    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,
    annual_deduction_flag
)
SELECT
    tr.id,
    c.client_id,
    c.snils,
    c.contract_id,
    c.contract_type,
    CAST(p.target_year AS smallint),
    c.contributions_amount,
    1
FROM target_registry tr
CROSS JOIN contributions c
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 = c.client_id
      AND existing_item.contract_id = c.contract_id
      AND existing_item.tax_period_year =
          CAST(p.target_year AS smallint)
);