---

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",

        row_number() over (
            partition by x."year"
            order by x."version" desc, x.id desc
        ) as dt

    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.application_termination_contract_id = :appId
      AND x."year" >= cast(to_char(contract.date, 'YYYY') as int)
      AND x."type" != 'NONE'
)

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

    q."year" AS year,
    q.dt AS dt,

    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

FROM base q