DROP VIEW IF EXISTS EHR.V_MKB_GENERAL;

CREATE VIEW EHR.V_MKB_GENERAL AS
SELECT gen_random_uuid() AS MkbID,
       MKB_CODE AS Code,
       NameEHr AS Name,
       0 AS Type,
       Ehr.date as ActualDate
FROM EHR.T_EHR_RUS_MKB308 ehr
WHERE ACTUAL = true
 AND EhrRusRefBooks = (
    SELECT rf.EhrRusRefBooksID
    FROM EHR.T_EHR_RUS_REF_BOOKS rf
    JOIN VCLIB.T_ENTITIES e ON e.EntityID = rf.Entities
        AND e.EntName = 'EHR_RUS_MKB308'
    ORDER BY
        (STRING_TO_ARRAY(rf.CurrVersion, '.'))[1]::INT DESC,
        (STRING_TO_ARRAY(rf.CurrVersion, '.'))[2]::INT DESC,
        (STRING_TO_ARRAY(rf.CurrVersion, '.'))[3]::INT DESC,
        (STRING_TO_ARRAY(rf.CurrVersion, '.'))[4]::INT DESC
    LIMIT 1
)
AND NOT EXISTS (
    SELECT 1
    FROM EHR.T_EHR_RUS_MKB308 child
    WHERE child.EhrRusTree = ehr.EhrRusID
        AND ACTUAL = true
)

UNION

SELECT gen_random_uuid() AS MkbID,
       Code AS Code,
       NameEhr AS Name,
       1 AS Type,
       null::timestamp as ActualDate
FROM EHR.T_EHR_RUS_23G4LTVREC ehr
WHERE EhrRusRefBooks = (
    SELECT rf.EhrRusRefBooksID
    FROM EHR.T_EHR_RUS_REF_BOOKS rf
    JOIN VCLIB.T_ENTITIES e ON e.EntityID = rf.Entities
        AND e.EntName = 'EHR_RUS_23G4LTVREC'
    ORDER BY
        (STRING_TO_ARRAY(rf.CurrVersion, '.'))[1]::INT DESC,
        (STRING_TO_ARRAY(rf.CurrVersion, '.'))[2]::INT DESC,
        (STRING_TO_ARRAY(rf.CurrVersion, '.'))[3]::INT DESC,
        (STRING_TO_ARRAY(rf.CurrVersion, '.'))[4]::INT DESC
    LIMIT 1
);
