---

SELECT
    ind.id AS client_id,
    ind.code_1c AS client_code,
    c.id AS contract_id,
    c.number AS contract_number,
    c.service_type AS contract_type,
    EXTRACT(YEAR FROM o.operation_date)::integer AS tax_period_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.operation_type ot
    ON ot.id = o.operation_type_id
JOIN back_office."transaction" t
    ON t.operation_id = o.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
JOIN ourpension.contract c
    ON c.id = io.contract_id
JOIN ourpension.individual ind
    ON ind.id = io.individual_id
WHERE o.operation_date >=
          make_date(EXTRACT(YEAR FROM CURRENT_DATE)::integer - 3, 1, 1)
  AND o.operation_date <
          make_date(EXTRACT(YEAR FROM CURRENT_DATE)::integer, 1, 1)

  AND o.approved IS NOT NULL
  AND o.deleted IS NULL
  AND io.payment_return IS NOT TRUE

  AND (
      (
          c.service_type = 'NPO'
          AND ot.code = 'DEPOSIT_ENROLLMENT'
          AND at.mnemonics = 'ИПС_ФЛ'
      )
      OR
      (
          c.service_type = 'PDS'
          AND ot.code = 'PDS_DEPOSIT_ENROLLMENT'
          AND at.mnemonics = 'ПДС СВ'
      )
  )

  AND (
      c.individual_id = ind.id
      OR (
          c.service_type = 'PDS'
          AND c.family_member IS TRUE
      )
  )

  AND NULLIF(TRIM(ind.insurance_number), '') IS NOT NULL

  AND NOT EXISTS (
      SELECT 1
      FROM ourpension.application_death_info adi
      WHERE adi.individual_id = ind.id
        AND adi.accepted IS NOT NULL
        AND adi.canceled IS NULL
  )

  AND NOT EXISTS (
      SELECT 1
      FROM report.unv_registry_item ri
      JOIN report.smev_register r
          ON r.id = ri.registry_id
      WHERE ri.client_id = ind.id
        AND ri.contract_id = c.id
        AND ri.tax_period_year =
            EXTRACT(YEAR FROM o.operation_date)::integer
        AND r.sample_status IS DISTINCT FROM 'Удалён'
  )

GROUP BY
    ind.id,
    ind.code_1c,
    c.id,
    c.number,
    c.service_type,
    EXTRACT(YEAR FROM o.operation_date)

HAVING SUM(t.value) > 0

LIMIT 20;