---

WITH latest_journal_ids AS (
    SELECT
        model_file_to_verification_id,
        MAX(id) AS max_id
    FROM report.fns_reference_request_journal
    WHERE model_file_to_verification_id IS NOT NULL
    GROUP BY model_file_to_verification_id
)
SELECT
    fj.id AS journal_id,
    fj.model_file_to_verification_id AS model_file_id,
    m.register_id AS smev_register_id,
    sr.number AS request_number,

    (
        SELECT count(*)
        FROM report.fns_reference_request_journal fj2
        WHERE fj2.model_file_to_verification_id =
              fj.model_file_to_verification_id
    ) AS journal_rows_count,

    (
        SELECT count(*)
        FROM dev.model_file_to_verification m2
        WHERE m2.register_id = m.register_id
    ) AS files_for_register_count,

    fj.is_sent_to_fns

FROM latest_journal_ids lj
JOIN report.fns_reference_request_journal fj
    ON fj.id = lj.max_id
JOIN dev.model_file_to_verification m
    ON m.id = fj.model_file_to_verification_id
JOIN report.smev_register sr
    ON sr.id = m.register_id
WHERE COALESCE(fj.is_sent_to_fns, false) = false
ORDER BY fj.id DESC
LIMIT 20;