DROP VIEW IF EXISTS akuz.v_rsosz_health_object;
CREATE OR REPLACE VIEW akuz.v_rsosz_health_object AS
SELECT
    gen_random_uuid()   AS rsoszhealthobjectid,
    pat.patientid       AS patient,
    diagnos.mkbcode     AS mkbcode,
    diagnos.fathername  AS doctorfathername,
    diagnos.firstname   AS doctorfirstname,
    diagnos.lastname    AS doctorlastname,
    diagnos.snils       AS doctorsnils,
    diagnos.oid         AS mooid,
    diagnos.postingdate AS postingdate,
    diagnos.diagnosis   AS diagnosis,
    diagnos.typedisease AS type
FROM (
    SELECT
        p.patientid
    FROM akuz.t_patient p
    JOIN vclib.t_enums e ON p.sex = e.enumid
    WHERE e.code = 1
     ) pat
JOIN LATERAL
    (
    SELECT
        mkb.mkbcode,
        post.lastname,
        post.fathername,
        post.firstname,
        post.snils,
        mkb.oid,
        mkb.postingdate,
        mkb308.nameehr AS diagnosis,
        mkb.typedisease
    FROM (
        SELECT
            m.mcbcode AS mkbcode,
            mr.post   AS post,
            mdr308.oid,
            mr.postingdate,
            CASE
                WHEN dis.typedisease = 1 THEN 2
                WHEN dis.typedisease = 0 THEN 3
                WHEN dis.typedisease IS NULL THEN 1
                END   AS typedisease,
            ROW_NUMBER() OVER (
                PARTITION BY ac.patient, m.mcbcode
                ORDER BY mr.postingdate ASC
                )     AS rn
        FROM akuz.t_ambulance_card ac
        JOIN akuz.t_medical_record mr ON mr.ambulancecard = ac.ambulancecardid
        JOIN akuz.t_outcome_of_inspection oi ON oi.medicalrecordid = mr.medicalrecordid
        LEFT JOIN akuz.t_accompanying_diseases dis ON oi.medicalrecordid = dis.medicalrecordid
        JOIN akuz.t_mkb m ON oi.mkbcode = m.mcbcode
        JOIN akuz.t_lpu_main lpu ON ac.cardlpu = lpu.lpuid
        LEFT JOIN ehr.t_ehr_rus_mdr308 mdr308 ON lpu.ehrrusmdr308 = mdr308.ehrrusid
        WHERE
              ac.patient = pat.patientid
          AND m.rsosztype = 0
         ) mkb
    JOIN LATERAL (
        SELECT
            w.lastname,
            w.fathername,
            w.firstname,
            w.snils
        FROM akuz.t_post p
        JOIN akuz.t_worker w ON w.workerid = p.worker
        WHERE p.resourceid = mkb.post
        ) post ON TRUE
    JOIN LATERAL (
        SELECT
            mkb308.nameehr
        FROM ehr.t_ehr_rus_mkb308 mkb308
        JOIN ehr.t_ehr_rus_ref_books rb ON mkb308.ehrrusrefbooks = rb.ehrrusrefbooksid
        WHERE
              mkb308.mkb_code = mkb.mkbcode
          AND rb.currversion =
              (
                  SELECT
                      rbb.currversion
                  FROM ehr.t_ehr_rus_ref_books rbb
                  WHERE mkb308.ehrrusrefbooks = rbb.ehrrusrefbooksid
                  ORDER BY
                      (STRING_TO_ARRAY(currversion, '.'))[1]::INT DESC,
                      (STRING_TO_ARRAY(currversion, '.'))[2]::INT DESC,
                      (STRING_TO_ARRAY(currversion, '.'))[3]::INT DESC,
                      (STRING_TO_ARRAY(currversion, '.'))[4]::INT DESC
                  LIMIT 1
              )
        LIMIT 1
        ) mkb308 ON TRUE
    WHERE mkb.rn = 1
    ) diagnos ON TRUE;


