---

SELECT 
    contract_type,
    COUNT(*)                    AS cnt_contracts,
    COUNT(DISTINCT client_id)   AS cnt_clients,
    SUM(contributions_amount)   AS total_amount,
    MIN(contributions_amount)   AS min_amount,
    MAX(contributions_amount)   AS max_amount
FROM (
    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) = 2024
          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) = 2024
          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
        c.id                        AS contract_id,
        'NPO'                       AS contract_type,
        ind.id                      AS client_id,
        ind.insurance_number        AS snils,
        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
        c.id                        AS contract_id,
        'PDS'                       AS contract_type,
        ind.id                      AS client_id,
        ind.insurance_number        AS snils,
        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)
) q
GROUP BY contract_type