---

WITH tessa (path) AS (
    SELECT value FROM tools.npf360_settings WHERE "name" = 'TESSA_API'
),
-- 1. Все строки справок с расчётом, в какие версии они входят
all_rows AS (
    SELECT
        td.id,
        td.application_termination_contract_id,
        td.year,
        td.version,
        td.canceled
    FROM ourpension.year_tax_deduction td
    WHERE td.type != 'NONE'
),
-- 2. Все уникальные версии (моменты создания) для каждого договора
versions AS (
    SELECT DISTINCT
        application_termination_contract_id,
        version AS cert_version
    FROM all_rows
),
-- 3. Соединяем версии со строками, которые в них входят,
--    и сразу выбираем репрезентативную (макс year, макс id)
version_rows AS (
    SELECT
        v.application_termination_contract_id,
        v.cert_version,
        ar.id AS row_id,
        ROW_NUMBER() OVER (
            PARTITION BY v.application_termination_contract_id, v.cert_version
            ORDER BY ar.year DESC, ar.id DESC
        ) AS row_pick,
        ROW_NUMBER() OVER (
            PARTITION BY v.application_termination_contract_id
            ORDER BY v.cert_version DESC
        ) AS rn_version
    FROM versions v
    JOIN all_rows ar
        ON ar.application_termination_contract_id = v.application_termination_contract_id
       AND ar.version <= v.cert_version
       AND (ar.canceled IS NULL OR ar.canceled > v.cert_version)
),
-- 4. Оставляем по одной репрезентативной строке на версию
ranked_versions AS (
    SELECT
        application_termination_contract_id,
        cert_version,
        row_id AS representative_id,
        rn_version
    FROM version_rows
    WHERE row_pick = 1
)
SELECT
    CASE
        WHEN rv.rn_version = 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,
    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
WHERE td.type != 'NONE'
and 1=1
#if ( ("$!contract_number" == "")
    && ("$!client_full_name" == "")
    && ("$!contract_activity_type" == "")
    && ("$!account_number" == "")
    && ("$!created_at_from" == "")
    && ("$!created_at_to" == "") )
    and 1 = 0
#end

#if ("$!contract_number" != "")
    and contract.number ILIKE '%' || replace(:contract_number, chr(92), chr(92) || chr(92)) || '%' ESCAPE chr(92)
#end

#if ("$!client_full_name" != "")
    and apdi.last_name || ' ' || apdi.first_name || ' ' || apdi.middle_name ILIKE '%' || :client_full_name || '%'
#end

#if ("$!contract_activity_type" != "")
    and sat.service_type = cast(:contract_activity_type as ourpension.service_type)
#end

#if ("$!account_number" != "")
    and sh.number ILIKE '%' || :account_number || '%'
#end

#if ("$!created_at_from" != "")
    and rv.cert_version >= to_date(:created_at_from, 'YYYY-MM-DD')
#end

#if ("$!created_at_to" != "")
    -- +1 день, чтобы дата "по" была включительно
    and rv.cert_version < to_date(:created_at_to, 'YYYY-MM-DD') + interval '1 day'
#end