---

-- Шаг 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 registry_date = CURRENT_DATE
      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
      )
),
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(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) 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
)
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)
  -- Исключаем договоры которые уже есть в реестре за этот год
  AND NOT EXISTS (
      SELECT 1 
      FROM report.unv_registry_item ri
      JOIN report.unv_registry r ON r.id = ri.registry_id
      WHERE ri.contract_id = npo.contract_id
        AND ri.tax_period_year = npo.contribution_year
        AND r.status <> 'DELETED'
  );


-- Шаг 3. Вставляем строки реестра по договорам ПДС
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 registry_date = CURRENT_DATE
      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
      )
),
dead_individuals AS (
    SELECT DISTINCT individual_id
    FROM ourpension.application_death_info
    WHERE accepted IS NOT NULL
      AND individual_id IS NOT NULL
),
pds_contributions AS (
    SELECT
        io.contract_id,
        io.individual_id,
        EXTRACT(YEAR FROM io.date)::int  AS contribution_year,
        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) 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
)
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)
  AND NOT EXISTS (
      SELECT 1 
      FROM report.unv_registry_item ri
      JOIN report.unv_registry r ON r.id = ri.registry_id
      WHERE ri.contract_id = pds.contract_id
        AND ri.tax_period_year = pds.contribution_year
        AND r.status <> 'DELETED'
  );