---

WITH tessa (path) AS (
    SELECT value
    FROM tools.npf360_settings
    WHERE "name" = 'TESSA_API'
),

base AS (
    SELECT
        td.id,
        td.canceled,
        td.accepted,
        td.version,
        td.date,
        td.number,
        td.year,
        td.type,
        td.comment,
        td.tessa_card_id,
        td.agent_id,

        atc.id AS atc_id,
        atc.number AS atc_number,

        contract.id AS contract_id,
        contract.number AS contract_number,
        contract.date AS contract_date,
        contract.entity_id,
        contract.individual_id AS contract_individual_id,

        apdi.last_name,
        apdi.first_name,
        apdi.middle_name,

        e.short_name,

        sat.service_type,

        sh.number AS account_number,

        info.name AS information_source_name,

        a.last_name AS agent_last_name,
        a.first_name AS agent_first_name,
        a.middle_name AS agent_middle_name,

        ind.id AS i_id,

        tessa.path AS tessa_path,

        row_number() over (
            partition by contract.number, td.year
            order by td.id desc
        ) as rn

    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.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}" != "")
        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 td.version >= to_date(:created_at_from, 'YYYY-MM-DD')
    #end

    #if ("${!created_at_to}" != "")
        AND td.version < to_date(:created_at_to, 'YYYY-MM-DD') + interval '1 day'
    #end
)

SELECT
    CASE
        WHEN q.canceled IS NULL THEN '<span class="glyphicon glyphicon-ok" style="color: green; align-items: center; justify-content: center;"></span>'
        WHEN q.canceled IS NOT NULL THEN '<span class="glyphicon glyphicon-remove" style="color: red; align-items: center; justify-content: center;"></span>'
        ELSE 'не определено'
    END AS actual,

    to_char(q.version, 'DD.MM.YYYY') AS created_at,
    to_char(q.date, 'DD.MM.YYYY') AS certificate_date,

    q.number AS document_number,

    CASE
        WHEN q.entity_id IS NULL
        THEN '<a target="_blank" href="view?viewCode=Client_list&id=' || q.contract_individual_id || '">' ||
             q.last_name || ' ' || q.first_name || coalesce(' ' || q.middle_name, '') ||
             '</a>'
        ELSE '<a target="_blank" href="view?viewCode=NCIuIMAW&id=' || q.entity_id || '">' ||
             q.short_name ||
             '</a>'
    END AS client_full_name,

    CASE q.service_type
        WHEN 'NPO' THEN 'НПО'
        WHEN 'OPS' THEN 'ОПС'
        WHEN 'PDS' THEN 'ПДС'
    END AS contract_activity_type,

    q.contract_number AS contract_number,

    '<a target="_blank" href="view?viewCode=7HPZX8vF&id=' || q.contract_id || '">' ||
    q.contract_number ||
    '</a>' AS contract_number_link,

    q.account_number AS account_number,

    CASE
        WHEN EXISTS (
            SELECT 1
            FROM report.fns_reference_request_journal fj
            WHERE fj.contract_number = q.contract_number
              AND fj.response_received = true
        )
        THEN 'ФНС'
        ELSE q.information_source_name
    END AS receive_method,

    q.agent_last_name || ' ' || q.agent_first_name || ' ' || q.agent_middle_name AS created_by_username,

    '<a target="_blank" href="view?viewCode=application_rd_npo_pds&id=' || q.atc_id || '">' ||
    q.atc_number ||
    '</a>' AS application_id,

    q.comment AS comment_text,

    q.atc_number AS atc_number,
    q.atc_id AS atc_id,

    to_char(q.contract_date, 'DD.MM.YYYY') AS contract_date,

    q.i_id AS i_id,

    '<a target="_blank" href="' || q.tessa_path || 'card/' || q.tessa_card_id || '" target="_blank">' ||
    q.tessa_card_id ||
    '</a>' AS tessa_card_id,

    q.last_name || ' ' || q.first_name || coalesce(' ' || q.middle_name, '') AS full_fl_name,

    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,

    dev.get_history_link2(
        'gYRJIbh0',
        'ourpension.year_tax_deduction',
        q.id,
        'История изменений'
    ) AS history

FROM base q
WHERE q.rn = 1
ORDER BY q.year;