---

INSERT INTO report.smev_register (
    id,
    smev_register_type_code,
    smev_register_codes_id,
    "year",
    "date",
    "type",
    sample_status,
    guid
)
SELECT
    nextval('report.smev_register_id_sequence'),
    'УНВ',
    (SELECT id FROM report.smev_register_codes WHERE short_name = 'УНВ'),
    CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - n,
    CURRENT_DATE,
    'OUT',
    'Сформирован',
    CAST(uuid_in(md5(random()::text)::cstring) AS varchar)
FROM (VALUES (1), (2), (3)) AS t(n)
WHERE NOT EXISTS (
    SELECT 1
    FROM report.smev_register r
    WHERE r."year" = CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS int) - n
      AND r.smev_register_type_code = 'УНВ'
      AND r.sample_status <> 'Удалён'
)







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, "year" AS tax_period_year
    FROM report.smev_register
    WHERE smev_register_type_code = 'УНВ'
      AND "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,
        CAST('NPO' AS 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,
        CAST('PDS' AS 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