---

WITH unv_code AS (
    SELECT id
    FROM report.smev_register_codes
    WHERE short_name = 'УНВ'
    ORDER BY id
    LIMIT 1
)
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,
    CAST(:tax_period_year AS integer),
    CURRENT_DATE,
    'OUT',
    'Сформирован'
FROM unv_code uc
WHERE CAST(:tax_period_year AS integer)
      BETWEEN CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS integer) - 3
          AND CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS integer) - 1
  AND NOT EXISTS (
      SELECT 1
      FROM report.smev_register r
      WHERE r.smev_register_type_code = 'УНВ'
        AND r."year" = CAST(:tax_period_year AS integer)
        AND r.sample_status IS DISTINCT FROM 'Удалён'
  );





INSERT INTO report.unv_registry_item (
    registry_id,
    client_id,
    snils,
    contract_id,
    contract_type,
    tax_period_year,
    contributions_amount,
    annual_deduction_flag
)
WITH target_registry AS (
    SELECT r.id
    FROM report.smev_register r
    WHERE r.smev_register_type_code = 'УНВ'
      AND r."year" = CAST(:tax_period_year AS integer)
      AND r.sample_status IS DISTINCT FROM 'Удалён'
    ORDER BY r.id DESC
    LIMIT 1
),
eligible_contract AS (
    SELECT
        io.individual_id AS client_id,
        io.contract_id AS contract_id,
        ind.insurance_number AS snils,
        c.service_type AS contract_type,
        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."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 = io.contract_id
    JOIN ourpension.individual ind
        ON ind.id = io.individual_id
    WHERE io.individual_id = CAST(:client_id AS integer)
      AND io.contract_id = CAST(:contract_id AS integer)
      AND io.payment_return IS NOT TRUE
      AND CAST(EXTRACT(YEAR FROM io.date) AS integer) =
          CAST(:tax_period_year AS integer)
      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
        io.individual_id,
        io.contract_id,
        ind.insurance_number,
        c.service_type
    HAVING SUM(t.value) > 0
)
SELECT
    tr.id,
    ec.client_id,
    ec.snils,
    ec.contract_id,
    ec.contract_type,
    CAST(:tax_period_year AS smallint),
    ec.contributions_amount,
    CAST(:annual_deduction_flag AS integer)
FROM target_registry tr
CROSS JOIN eligible_contract ec
WHERE NOT EXISTS (
    SELECT 1
    FROM report.unv_registry_item existing_item
    WHERE existing_item.registry_id = tr.id
      AND existing_item.client_id = ec.client_id
      AND existing_item.contract_id = ec.contract_id
      AND existing_item.tax_period_year =
          CAST(:tax_period_year AS smallint)
);