---

WITH tessa (path) AS (
    SELECT value FROM tools.npf360_settings WHERE "name" = 'TESSA_API'
),
-- 1. Берём все строки справок (без NONE)
all_rows AS (
    SELECT td.*
    FROM ourpension.year_tax_deduction td
    WHERE td.type != 'NONE'
),
-- 2. Все моменты, в которые создавались версии справки для договора.
--    Группируем по application_termination_contract_id (т.к. number одинаковый).
versions AS (
    SELECT DISTINCT
        application_termination_contract_id,
        version AS cert_version
    FROM all_rows
),
-- 3. Для каждой версии T находим строки, входящие в эту версию,
--    и выбираем "репрезентативную" — с максимальным year.
version_rows AS (
    SELECT
        v.application_termination_contract_id,
        v.cert_version,
        (
            SELECT td.id
            FROM all_rows td
            WHERE td.application_termination_contract_id = v.application_termination_contract_id
              AND td.version <= v.cert_version
              AND (td.canceled IS NULL OR td.canceled > v.cert_version)
            ORDER BY td.year DESC, td.id DESC
            LIMIT 1
        ) AS representative_id
    FROM versions v
),
-- 4. Помечаем актуальность: rn = 1 → самая свежая версия по cert_version
ranked_versions AS (
    SELECT
        vr.*,
        ROW_NUMBER() OVER (
            PARTITION BY vr.application_termination_contract_id
            ORDER BY vr.cert_version DESC
        ) AS rn
    FROM version_rows vr
    WHERE vr.representative_id IS NOT NULL
)
SELECT
    CASE
        WHEN rv.rn = 1 THEN '<span class="glyphicon glyphicon-ok" style="color: green; align-items: center; justify-content: center;"></span>'
        ELSE '<span class="glyphicon glyphicon-remove" style="color: red; align-items: center; justify-content: center;"></span>'
    END AS actual,
    to_char(rv.cert_version, 'DD.MM.YYYY') AS created_at,
    to_char(td.date, 'DD.MM.YYYY') AS certificate_date,
    td.number AS document_number,
    CASE
        WHEN contract.entity_id IS NULL
            THEN '<a target="_blank" href="view?viewCode=Client_list&id=' || contract.individual_id || '">' || apdi.last_name || ' ' || apdi.first_name || coalesce(' ' || apdi.middle_name, '') || '</a>'
        ELSE '<a target="_blank" href="view?viewCode=NCIuIMAW&id=' || contract.entity_id || '">' || e.short_name || '</a>'
    END AS client_full_name,
    CASE sat.service_type
        WHEN 'NPO' THEN 'НПО'
        WHEN 'OPS' THEN 'ОПС'
        WHEN 'PDS' THEN 'ПДС'
    END AS contract_activity_type,
    contract.number AS contract_number,
    '<a target="_blank" href="view?viewCode=7HPZX8vF&id='||contract.id||'">'||contract.number||'</a>' AS contract_number_link,
    sh.number AS account_number,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM report.fns_reference_request_journal fj
            WHERE fj.contract_number = contract.number
              AND fj.response_received = true
        ) THEN 'ФНС'
        ELSE info.name
    END AS receive_method,
    a.last_name || ' ' || a.first_name || ' ' || a.middle_name AS created_by_username,
    '<a target="_blank" href="view?viewCode=application_rd_npo_pds&id='||atc.id||'">'||atc.number||'</a>' AS application_id,
    td.comment AS comment_text,
    atc.number AS atc_number,
    atc.id AS atc_id,
    to_char(contract.date, 'DD.MM.YYYY') AS contract_date,
    ind.id AS i_id,
    '<a target="_blank" href="' || tessa.path || 'card/'|| td.tessa_card_id ||'" target="_blank">' || td.tessa_card_id || '</a>' AS tessa_card_id,
    apdi.last_name || ' ' || apdi.first_name || coalesce(' ' || apdi.middle_name, '') AS full_fl_name,
    to_char(td.accepted, 'DD.MM.YYYY HH24:MI:SS') AS accepted,
    to_char(td.canceled, 'DD.MM.YYYY HH24:MI:SS') AS canceled,
    td.id AS id,
    -- Передаём cert_version для нижней таблицы (чтобы знать, какую версию раскрывать)
    extract(epoch from rv.cert_version)::bigint AS version_key,
    dev.get_history_link2('gYRJIbh0','ourpension.year_tax_deduction',td.id,'История изменений') AS history
FROM ranked_versions rv
JOIN ourpension.year_tax_deduction td ON td.id = rv.representative_id
LEFT JOIN ourpension.application_termination_contract atc ON atc.id = td.application_termination_contract_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.individual ind ON ind.id = apdi.individual_id
LEFT JOIN ourpension.sharer sh ON sh.id = atcd.sharer_id
LEFT JOIN ourpension.contract contract ON contract.id = sh.contract_id
LEFT JOIN ourpension.service_app_type sat ON atc.service_app_type_code = sat.code
LEFT JOIN ourpension.agent a ON td.agent_id = a.id
LEFT JOIN ourpension.information_source info ON info.id = atc.information_source_id
LEFT JOIN ourpension.entity e ON e.id = contract.entity_id
CROSS JOIN tessa
ORDER BY contract.number, rv.cert_version DESC;






SELECT
    CASE WHEN td.canceled IS NULL THEN '<span class="glyphicon glyphicon-ok" style="color: green; align-items: center; justify-content: center;"></span>'
         WHEN td.canceled IS NOT NULL THEN '<span class="glyphicon glyphicon-remove" style="color: red; align-items: center; justify-content: center;"></span>'
         ELSE 'не определено>'
    END AS actual,
    td.year AS year,
    CASE td.type
        WHEN 'NONE' THEN 'Нет справки'
        WHEN 'NOT_RECEIVED' THEN 'Вычет не получен'
        WHEN 'RECEIVED' THEN 'Вычет получен'
    END AS type,
    td.sum_income AS sum_income,
    to_char(td.date, 'DD.MM.YYYY') AS date,
    td.sum_tax_deduction AS sum_tax_deduction,
    td.max_tax_deduction AS max_tax_deduction
FROM ourpension.year_tax_deduction td
LEFT JOIN ourpension.application_termination_contract atc ON atc.id = td.application_termination_contract_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 1=1
  AND td.number = :document_number
  AND td.type != 'NONE'
  AND td.year >= cast(to_char(contract.date, 'YYYY') as int)
  -- Фильтр по версии: строки, входящие в выбранную версию
  AND td.version <= to_timestamp(:version_key)
  AND (td.canceled IS NULL OR td.canceled > to_timestamp(:version_key))
ORDER BY td.year;