---

WITH base AS (
    SELECT
        x.id,
        x."year",
        x."type",
        x.sum_income,
        x."date",
        x.sum_tax_deduction,
        x.max_tax_deduction,
        x."number",
        x.canceled,
        x.accepted,
        x."version",

        contract.number AS contract_number,
        contract.date AS contract_date,

        coalesce(x."number", contract.number) AS certificate_group_key,

        row_number() over (
            partition by coalesce(x."number", contract.number)
            order by x."version" desc, x.id desc
        ) as rn

    FROM ourpension.year_tax_deduction x
    LEFT JOIN ourpension.application_termination_contract atc
        ON x.application_termination_contract_id = atc.id
    LEFT JOIN ourpension.application_personal_data_individual apdi
        ON apdi.id = atc.application_personal_data_individual_id
    LEFT JOIN ourpension.application_termination_contract_data atcd
        ON atcd.application_termination_contract_id = atc.id
    LEFT JOIN ourpension.sharer sh
        ON sh.id = atcd.sharer_id
    LEFT JOIN ourpension.contract contract
        ON contract.id = sh.contract_id

    WHERE x."type" != 'NONE'
      AND x."year" >= cast(to_char(contract.date, 'YYYY') as int)

      AND coalesce(:certificate_group_key, '') != ''
      AND coalesce(x."number", contract.number) = :certificate_group_key
)

SELECT
    CASE
        WHEN q.rn = 1 AND q.canceled IS NULL THEN 'Актуальная'
        ELSE 'Неактуальная'
    END AS actual,

    q."year" AS year,

    CASE q."type"
        WHEN 'NONE' THEN 'Нет справки'
        WHEN 'NOT_RECEIVED' THEN 'Вычет не получен'
        WHEN 'RECEIVED' THEN 'Вычет получен'
    END AS type,

    q.sum_income AS sum_income,
    to_char(q."date", 'DD.MM.YYYY') AS date,
    q.sum_tax_deduction AS sum_tax_deduction,
    q.max_tax_deduction AS max_tax_deduction,
    q."number" AS number,
    q.contract_number AS contract_number,

    to_char(q.accepted, 'DD.MM.YYYY HH24:MI:SS') AS accepted,
    to_char(q.canceled, 'DD.MM.YYYY HH24:MI:SS') AS canceled,

    q.id AS id,
    q.certificate_group_key AS certificate_group_key

FROM base q