---

-- ПРОВЕРКА 1. Смотрим что создастся в unv_registry (без INSERT)
SELECT 
   'УНВ-' || CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS varchar) 
       || '-' || CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - n AS varchar)  AS registry_number,
   CURRENT_DATE                                                                        AS registry_date,
   CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS 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 = CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - n
     AND r.status <> 'DELETED'
);



-- ПРОВЕРКА 2. Смотрим что попадёт в unv_registry_item (без INSERT)
WITH
newly_created AS (
    SELECT id, tax_period_year
    FROM report.unv_registry
    WHERE status = 'FORMED'
      AND tax_period_year IN (
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS 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,
        CAST(EXTRACT(YEAR FROM io.date) AS 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 CAST(EXTRACT(YEAR FROM io.date) AS int) IN (
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 3
      )
      AND io.contract_id IS NOT NULL
    GROUP BY 
        io.contract_id, 
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS int)
),
pds_contributions AS (
    SELECT
        io.contract_id,
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS 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 CAST(EXTRACT(YEAR FROM io.date) AS int) IN (
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 3
      )
      AND io.contract_id IS NOT NULL
    GROUP BY 
        io.contract_id, 
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS int)
)
SELECT
    nc.id                       AS registry_id,
    ind.id                      AS client_id,
    ind.insurance_number        AS snils,
    c.id                        AS contract_id,
    'NPO'                       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)

UNION ALL

SELECT
    nc.id                       AS registry_id,
    ind.id                      AS client_id,
    ind.insurance_number        AS snils,
    c.id                        AS contract_id,
    'PDS'                       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);






-- ШАГ 1. Создаём 3 реестра
INSERT INTO report.unv_registry (
    registry_number,
    registry_date,
    tax_period_year,
    status
)
SELECT 
    'УНВ-' || CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS varchar) 
        || '-' || CAST(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - n AS varchar),
    CURRENT_DATE,
    CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - n,
    'FORMED'
FROM (VALUES (1), (2), (3)) AS t(n)
WHERE NOT EXISTS (
    SELECT 1 
    FROM report.unv_registry r
    WHERE r.tax_period_year = CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS 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 (
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS 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,
        CAST(EXTRACT(YEAR FROM io.date) AS 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 CAST(EXTRACT(YEAR FROM io.date) AS int) IN (
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 3
      )
      AND io.contract_id IS NOT NULL
    GROUP BY 
        io.contract_id,
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS int)
),
pds_contributions AS (
    SELECT
        io.contract_id,
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS 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 CAST(EXTRACT(YEAR FROM io.date) AS int) IN (
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 1,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 2,
          CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - 3
      )
      AND io.contract_id IS NOT NULL
    GROUP BY 
        io.contract_id,
        io.individual_id,
        CAST(EXTRACT(YEAR FROM io.date) AS 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;