---

-- Отбор договоров для реестра УНВ
-- Параметр: :tax_year = текущий год - 1/2/3 (например 2024, 2023, 2022)

WITH 

-- 1. Личные взносы по НПО за налоговый период
npo_contributions AS (
    SELECT
        io.contract_id,
        io.individual_id,
        SUM(io.value)   AS contributions_amount
    FROM back_office.incoming_order io
    WHERE io.order_payment_type_id = 18         -- Поступление НПО от физлица
      AND io.payment_return = false              -- Исключаем возвраты
      AND EXTRACT(YEAR FROM io.date) = :tax_year
      AND io.contract_id IS NOT NULL
    GROUP BY io.contract_id, io.individual_id
),

-- 2. Личные взносы по ПДС за налоговый период
pds_contributions AS (
    SELECT
        io.contract_id,
        io.individual_id,
        SUM(io.value)   AS contributions_amount
    FROM back_office.incoming_order io
    WHERE io.order_payment_type_id = 40         -- Поступление сберегательных взносов от физлица
      AND io.payment_return = false              -- Исключаем возвраты
      AND EXTRACT(YEAR FROM io.date) = :tax_year
      AND io.contract_id IS NOT NULL
    GROUP BY io.contract_id, io.individual_id
),

-- 3. Клиенты с зарегистрированным фактом смерти (исключаем)
dead_individuals AS (
    SELECT DISTINCT individual_id
    FROM ourpension.application_death_info
    WHERE accepted IS NOT NULL
      AND individual_id IS NOT NULL
)

-- Договоры НПО
SELECT
    c.id                        AS contract_id,
    'NPO'                       AS contract_type,
    c.number                    AS contract_number,
    c.date                      AS contract_date,
    ind.id                      AS client_id,
    ind.insurance_number        AS snils,
    npo.contributions_amount,
    :tax_year                   AS tax_period_year
FROM npo_contributions npo
JOIN ourpension.contract c
    ON c.id = npo.contract_id
JOIN ourpension.individual ind
    ON ind.id = npo.individual_id
WHERE c.service_type = 'NPO'
  AND ind.insurance_number IS NOT NULL
  AND ind.insurance_number <> ''
  AND ind.id NOT IN (SELECT individual_id FROM dead_individuals)

UNION ALL

-- Договоры ПДС
SELECT
    c.id                        AS contract_id,
    'PDS'                       AS contract_type,
    c.number                    AS contract_number,
    c.date                      AS contract_date,
    ind.id                      AS client_id,
    ind.insurance_number        AS snils,
    pds.contributions_amount,
    :tax_year                   AS tax_period_year
FROM pds_contributions pds
JOIN ourpension.contract c
    ON c.id = pds.contract_id
JOIN ourpension.individual ind
    ON ind.id = pds.individual_id
WHERE c.service_type = 'PDS'
  AND ind.insurance_number IS NOT NULL
  AND ind.insurance_number <> ''
  AND ind.id NOT IN (SELECT individual_id FROM dead_individuals)

ORDER BY contract_type, contract_id




-- Быстрая проверка: сколько договоров попадёт в реестр за 2024 год
-- и есть ли аномалии (нулевые суммы, дубли)
SELECT 
    contract_type,
    COUNT(*)                AS cnt_contracts,
    COUNT(DISTINCT client_id) AS cnt_clients,
    SUM(contributions_amount) AS total_amount,
    MIN(contributions_amount) AS min_amount,
    MAX(contributions_amount) AS max_amount
FROM (<вставить запрос выше с :tax_year = 2024>)  q
GROUP BY contract_type