---

WITH tessa AS MATERIALIZED (
    SELECT value AS path
    FROM tools.npf360_settings
    WHERE "name" = 'TESSA_API'
),
base AS MATERIALIZED (
    SELECT
        td.canceled AS actual,

        '<a target="_blank" href="view?viewCode=Client_list&id=' || contract.individual_id || '">'
        || ind.last_name || ' ' || ind.first_name || COALESCE(' ' || ind.middle_name, '')
        || '</a>' AS client_full_name,

        td.number AS document_number,
        td.year AS year,
        to_char(td.version, 'DD.MM.YYYY') AS created_at,
        to_char(td.date, 'DD.MM.YYYY') AS certificate_date,

        uat.activity_rus_type 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,

        to_char(contract.date, 'DD.MM.YYYY') AS contract_date,

        '<a target="_blank" href="view?viewCode=Pzbmp8Pg&id=' || sh.id || '">'
        || sh.number
        || '</a>' AS account_number_link,

        sh.number AS sh_number,
        sh.id AS sh_id,

        a.last_name || ' ' || a.first_name || ' ' || a.middle_name AS created_by_username,

        info.name AS info_name,

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

        coalesce(apdi.last_name, ind.last_name) || ' ' || coalesce(apdi.first_name, ind.first_name)  || ' ' || COALESCE(apdi.middle_name, ind.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,
        td.tessa_card_id AS tessa_id_pure,
        contract.id AS contract_id,

        td.canceled IS NOT NULL AS sort_canceled,
        td.version AS sort_version

    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 LATERAL (
        SELECT atcd.*
        FROM ourpension.application_termination_contract_data atcd
        WHERE atcd.application_termination_contract_id = atc.id
        ORDER BY atcd.id DESC
        LIMIT 1
    ) atcd ON TRUE

    LEFT JOIN ourpension.individual ind
        ON ind.id = COALESCE(td.individual_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.uv_activity_type uat
        ON uat.activity_eng_type = contract.service_type
     AND uat.client_type = contract.client_type

    LEFT JOIN ourpension.agent a
        ON td.agent_id = a.id

    LEFT JOIN ourpension.information_source info
        ON info.id = atc.information_source_id

    CROSS JOIN tessa

  WHERE td.type != 'NONE'
    and 1 = 1

      and 1 = 0
  
  
  
  
  
  
  
  ORDER BY 
    td.canceled IS NOT NULL,
    td.version DESC
)
SELECT
  case 
      when b.actual is null then '<span class="glyphicon glyphicon-ok" style="color: green; align-items: center; justify-content: center;"></span>'
      when b.actual is not null then '<span class="glyphicon glyphicon-remove" style="color: red;  align-items: center; justify-content: center;"></span>' 
      else 'не определено>'
    end as actual,
  b.client_full_name as client_full_name,
  b.document_number as document_number,
  b.year as year,
  b.created_at as created_at,
  b.certificate_date as certificate_date,
  b.contract_activity_type as contract_activity_type,
  b.contract_number as contract_number,
  b.contract_number_link as contract_number_link,
  b.contract_date as contract_date,
  b.account_number_link as account_number,
  b.created_by_username as created_by_username,
  CASE
        WHEN fj_resp.has_response_received THEN 'ФНС'
        ELSE b.info_name
    END as receive_method,
  b.application_id as application_id,
  b.comment_text as comment_text,
  b.atc_number as atc_number,
  b.atc_id as atc_id,
  b.i_id as i_id,
  b.tessa_card_id as tessa_card_id,
  '<a target="_blank" href="/ourpension/view?viewCode=7K6FKG8e&id=' || fj_last.smevRegisterId || '">'
    || sr.number
    || '</a>' as request_number_link,
  b.full_fl_name as full_fl_name,
  b.sh_number as sh_number,
  b.sh_id as sh_id,
  b.accepted as accepted,
  b.canceled as canceled,
  b.id as id,
  b.tessa_id_pure as tessa_id_pure,
  b.contract_id as contract_id 
FROM base b

LEFT JOIN LATERAL (
    SELECT
        fj.model_file_to_verification_id,
        fj.smevRegisterId
    FROM report.fns_reference_request_journal fj
    WHERE fj.contract_number = b.contract_number
    ORDER BY fj.request_date DESC
    LIMIT 1
) fj_last ON TRUE

LEFT JOIN LATERAL (
    SELECT true AS has_response_received
    FROM report.fns_reference_request_journal fj
    WHERE fj.contract_number = b.contract_number
     AND fj.response_received = true
    LIMIT 1
) fj_resp ON TRUE

LEFT JOIN dev.model_file_to_verification mfc
    ON mfc.id = fj_last.model_file_to_verification_id

LEFT JOIN report.smev_register sr
    ON sr.id = mfc.register_id