---

-- ШАГ 1. Создаём 3 реестра за N-1, N-2, N-3
-- с проверкой на дубли (если реестр за год уже есть — пропускаем)

INSERT INTO report.unv_registry (
   registry_number,
   registry_date,
   tax_period_year,
   status
)
SELECT 
   'УНВ-' || EXTRACT(YEAR FROM CURRENT_DATE)::text 
       || '-' || (EXTRACT(YEAR FROM CURRENT_DATE)::int - n)::text  AS registry_number,
   CURRENT_DATE                                                      AS registry_date,
   (EXTRACT(YEAR FROM CURRENT_DATE)::int - n)                       AS tax_period_year,
   'FORMED'                                                          AS status
FROM (VALUES (1), (2), (3)) AS t(n)
WHERE NOT EXISTS (
   SELECT 1 
   FROM report.unv_registry r
   WHERE r.tax_period_year = (EXTRACT(YEAR FROM CURRENT_DATE)::int - n)
     AND r.status <> 'DELETED'
);

-- ШАГ 2. Вставляем строки реестра по договорам НПО и ПДС

INSERT INTO report.unv_registry_item (
   registry_id,
   client_id,
   snils,
   contract_id,
   contract_type,
   tax_period_year,
   contributions_amount
)
WITH

-- Только что созданные реестры
newly_created AS (
   SELECT id, tax_period_year
   FROM report.unv_registry
   WHERE status = 'FORMED'
     AND tax_period_year IN (
         EXTRACT(YEAR FROM CURRENT_DATE)::int - 1,
         EXTRACT(YEAR FROM CURRENT_DATE)::int - 2,
         EXTRACT(YEAR FROM CURRENT_DATE)::int - 3
     )
     -- Берём только те за которые ещё нет строк
     AND id NOT IN (SELECT DISTINCT registry_id FROM report.unv_registry_item)
),

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

-- Взносы по НПО (мнемоника 'ИПС_ФЛ')
npo_contributions AS (
   SELECT
       io.contract_id,
       io.individual_id,
       EXTRACT(YEAR FROM io.date)::int  AS contribution_year,
       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
   WHERE at.mnemonics = 'ИПС_ФЛ'
     AND io.payment_return = false
     AND EXTRACT(YEAR FROM io.date)::int IN (
         EXTRACT(YEAR FROM CURRENT_DATE)::int - 1,
         EXTRACT(YEAR FROM CURRENT_DATE)::int - 2,
         EXTRACT(YEAR FROM CURRENT_DATE)::int - 3
     )
     AND io.contract_id IS NOT NULL
   GROUP BY 
       io.contract_id, 
       io.individual_id,
       EXTRACT(YEAR FROM io.date)::int
),

-- Взносы по ПДС (мнемоника 'ПДС СВ')
pds_contributions AS (
   SELECT
       io.contract_id,
       io.individual_id,
       EXTRACT(YEAR FROM io.date)::int  AS contribution_year,
       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
   WHERE at.mnemonics = 'ПДС СВ'
     AND io.payment_return = false
     AND EXTRACT(YEAR FROM io.date)::int IN (
         EXTRACT(YEAR FROM CURRENT_DATE)::int - 1,
         EXTRACT(YEAR FROM CURRENT_DATE)::int - 2,
         EXTRACT(YEAR FROM CURRENT_DATE)::int - 3
     )
     AND io.contract_id IS NOT NULL
   GROUP BY 
       io.contract_id, 
       io.individual_id,
       EXTRACT(YEAR FROM io.date)::int
),

-- Итоговая выборка НПО
npo_result AS (
   SELECT
       nc.id                       AS registry_id,
       ind.id                      AS client_id,
       ind.insurance_number        AS snils,
       c.id                        AS contract_id,
       'NPO'::varchar              AS contract_type,
       npo.contribution_year       AS tax_period_year,
       npo.contributions_amount
   FROM npo_contributions npo
   JOIN newly_created nc
       ON nc.tax_period_year = npo.contribution_year
   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)
),

-- Итоговая выборка ПДС
pds_result AS (
   SELECT
       nc.id                       AS registry_id,
       ind.id                      AS client_id,
       ind.insurance_number        AS snils,
       c.id                        AS contract_id,
       'PDS'::varchar              AS contract_type,
       pds.contribution_year       AS tax_period_year,
       pds.contributions_amount
   FROM pds_contributions pds
   JOIN newly_created nc
       ON nc.tax_period_year = pds.contribution_year
   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)
)

-- Объединяем НПО и ПДС
SELECT * FROM npo_result
UNION ALL
SELECT * FROM pds_result;