---

WITH tessa (path) AS (
    SELECT value FROM tools.npf360_settings WHERE "name" = 'TESSA_API'
),
all_rows AS (
    SELECT td.*
    FROM ourpension.year_tax_deduction td
    WHERE td.type != 'NONE'
),
versions AS (
    SELECT DISTINCT
        application_termination_contract_id,
        version AS cert_version
    FROM all_rows
),
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
),
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,
    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




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.year >= cast(to_char(contract.date, 'YYYY') as int)
    AND td.type != 'NONE'
    -- Фильтр по версии справки: строки, входящие в выбранную версию
    AND td.version <= to_timestamp(:version_key)
    AND (td.canceled IS NULL OR td.canceled > to_timestamp(:version_key))