---

INSERT INTO report.unv_registry (
    registry_number,
    registry_date,
    tax_period_year,
    status
)
VALUES (
    nextval('report.unv_registry_number_seq'), -- нужна sequence
    CURRENT_DATE,
    :tax_year,
    'FORMED'
)
RETURNING id; -- получаем id созданного реестра





INSERT INTO report.unv_registry_item (
    registry_id,
    client_id,
    snils,
    contract_id,
    contract_type,
    tax_period_year,
    contributions_amount
)
WITH 
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
),
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
),
dead_individuals AS (
    SELECT DISTINCT individual_id
    FROM ourpension.application_death_info
    WHERE accepted IS NOT NULL
      AND individual_id IS NOT NULL
)
SELECT
    :registry_id            AS registry_id,  -- id из шага 1
    ind.id                  AS client_id,
    ind.insurance_number    AS snils,
    c.id                    AS contract_id,
    'NPO'                   AS contract_type,
    :tax_year               AS tax_period_year,
    npo.contributions_amount
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
    :registry_id            AS registry_id,
    ind.id                  AS client_id,
    ind.insurance_number    AS snils,
    c.id                    AS contract_id,
    'PDS'                   AS contract_type,
    :tax_year               AS tax_period_year,
    pds.contributions_amount
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)





-- Журнал реестров (верхняя таблица)
SELECT
    r.id,
    r.registry_number,
    r.registry_date,
    r.sent_to_fns_date,
    r.tax_period_year,
    r.xml_document,
    r.fns_response,
    r.status
FROM report.unv_registry r
WHERE r.status <> 'DELETED'
  AND r.registry_date BETWEEN :date_from AND :date_to  -- обязательный фильтр
  AND (:registry_number IS NULL OR r.registry_number = :registry_number)
  AND (:sent_date_from IS NULL OR r.sent_to_fns_date >= :sent_date_from)
  AND (:sent_date_to IS NULL OR r.sent_to_fns_date <= :sent_date_to)
  AND (:tax_period_year IS NULL OR r.tax_period_year = :tax_period_year)
  AND (:status IS NULL OR r.status = :status)
ORDER BY r.registry_date DESC;

-- Детализация по клиентам (нижняя таблица, при выборе реестра)
SELECT
    ri.id,
    ri.client_id,
    ind.last_name || ' ' || ind.first_name || 
        COALESCE(' ' || ind.middle_name, '')    AS client_name,
    ri.snils,
    c.service_type                              AS contract_type,
    ri.contract_id,
    c.number                                    AS contract_number,
    ri.contributions_amount,
    ri.has_fns_lk,
    ri.last_sent_date,
    ri.error_code,
    ri.error_description,
    ri.error_status
FROM report.unv_registry_item ri
JOIN ourpension.individual ind  ON ind.id = ri.client_id
JOIN ourpension.contract c      ON c.id = ri.contract_id
WHERE ri.registry_id = :registry_id  -- id выбранного реестра
  AND (:error_code IS NULL OR ri.error_code = :error_code)
  AND (:error_status IS NULL OR ri.error_status = :error_status)
ORDER BY ri.contract_type, ri.client_id