CREATE OR REPLACE PROCEDURE rds.sp_loadrrdirection(
    userid uuid,
    currentdatetime timestamp DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_userid          uuid      := userid;
    p_currentdatetime timestamp := currentdatetime;
BEGIN
    
    UPDATE idb.rr_direction
    SET id = gen_random_uuid();
    
    UPDATE idb.rr_direction_benefits
    SET id = gen_random_uuid();
    
    UPDATE idb.rr_direction
    SET ss = NULL
    WHERE ss = '';
    
    UPDATE idb.rr_direction
    SET ndok = NULL
    WHERE ndok = '';
    
    UPDATE idb.rr_direction
    SET sdok = NULL
    WHERE sdok = '';
    
    UPDATE idb.rr_direction rd
    SET realpac = rp.rrpatientid
    FROM rds.t_rr_patient rp
    WHERE rd.ss = rp.snils
      AND rd.realpac IS NULL
      AND rd.ss IS NOT NULL
      AND rd.ss <> ''
      AND rd.ss <> '              '
      AND (rp.dateend IS NULL OR rp.dateend >= CURRENT_TIMESTAMP);
    
    UPDATE idb.rr_direction rd
    SET realpoac = rp.rrpatientid
    FROM rds.t_rr_patient rp
    WHERE rp.docseries = rd.sdok
      AND rp.docnumber = rd.ndok
      AND realpac IS NULL
      AND rd.sdok IS NOT NULL
      AND rd.ndok IS NOT NULL
      AND rd.sdok <> ''
      AND rd.ndok <> ''
      AND (rp.dateend IS NULL OR rp.dateend >= CURRENT_TIMESTAMP)
      AND rp.birthday = rd.drod;
    
    UPDATE idb.rr_direction rd
    SET realpac = rp.rrpatientid
    FROM rds.t_rr_patient rp
    WHERE rp.lastname = rd.fam
      AND rp.firstname = rd.im
      AND (
        rp.fathername = rd.ot
            OR (rp.fathername IS NULL AND rd.ot IS NULL)
        )
      AND rp.birthday = rd.drod
      AND rd.realpac IS NULL
      AND (rp.dateend IS NULL OR rp.dateend >= CURRENT_TIMESTAMP);
    
    
    INSERT INTO
        rds.t_import_elements (importelementsid, error, users, loaddate, type_, filename)
    SELECT rd.id,
           NULL,
           p_userid,
           p_currentdatetime,
           (SELECT e.entityid
            FROM vclib.t_entities e
            WHERE e.entname = 'IMPORT_RR_DIRECTION'
              AND e.schema = 'RDS'
            LIMIT 1
           ),
           rd.filenamecolumn
    FROM idb.rr_direction rd
    UNION ALL
    SELECT rdb.id,
           NULL,
           p_userid,
           p_currentdatetime,
           (SELECT e.entityid
            FROM vclib.t_entities e
            WHERE e.entname = 'IMPORT_RR_DIRECTION_BENEFITS'
              AND e.schema = 'RDS'
            LIMIT 1
           ),
           rdb.filenamecolumn
    FROM idb.rr_direction_benefits rdb;
    
    INSERT INTO
        rds.t_import_rr_direction (importelementsid,
                                   kod,
                                   pac_id,
                                   fam,
                                   im,
                                   ot,
                                   sex,
                                   drod,
                                   snils,
                                   doctype,
                                   sdok,
                                   ndok,
                                   kodadr,
                                   lpu_inf,
                                   vid_i,
                                   dat_prov,
                                   real_pac_id,
                                   tipd,
                                   sost_r)
    SELECT rd.id,
           kod,
           pac_id,
           fam,
           im,
           ot,
           CASE WHEN w = 'М' THEN 10301 ELSE 10302 END,
           drod,
           ss,
           e.enumid,
           sdok,
           ndok,
           kodadr,
           lpu_inf,
           vid_i,
           dat_prov,
           COALESCE(realpac, pac_id),
           tipd,
           sost_r
    FROM idb.rr_direction rd
    JOIN rds.t_import_elements ie ON ie.importelementsid = rd.id
    LEFT JOIN vclib.t_enums e ON e.code = tipd AND e.enumtypeid = 105 AND ie.users = p_userid;
    
    INSERT INTO
        rds.t_import_rr_direction_benefits(importelementsid, importrrdirection, kod, benefits, mkb, dop, ndok, vid_i)
    SELECT rdb.id,
           rd.importelementsid,
           rdb.kod,
           b.benefitsid,
           rdb.mkb,
           rdb.dop,
           rdb.ndok,
           rdb.vid_i
    FROM idb.rr_direction_benefits rdb
    JOIN vclib.t_enums e ON e.enumtypeid = 190 AND e.code = CASE WHEN rdb.gr = '95' THEN 5 ELSE rdb.vid::int + 1 END
    LEFT JOIN rds.t_benefits b ON b.benefitcode = rdb.gr AND b.benefitviews = e.enumid
    CROSS JOIN LATERAL (SELECT * FROM rds.t_import_rr_direction rd WHERE rd.kod = rdb.kod LIMIT 1) rd
    JOIN rds.t_import_elements ie ON ie.importelementsid = rdb.id
    JOIN rds.t_import_elements iee
        ON iee.importelementsid = rd.importelementsid AND ie.users = p_userid AND iee.users = p_userid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') ||
                ' Обнаружен дубликат направлений в рамках данной МО или данные на этого человека предоставила другая организация!'
    FROM rds.t_import_rr_direction rd
    WHERE ie.importelementsid = rd.importelementsid
      AND EXISTS (SELECT 1
                  FROM rds.t_import_elements iee
                  JOIN rds.t_import_rr_direction rdd ON iee.importelementsid = rdd.importelementsid
                  WHERE rd.importelementsid <> rdd.importelementsid
                    AND ((rd.snils IS NOT NULL AND rdd.snils IS NOT NULL AND rd.snils = rdd.snils) OR
                         (rd.fam = rdd.fam AND rd.im = rdd.im AND rd.drod = rdd.drod AND
                          (rd.ot IS NOT NULL AND rdd.ot IS NOT NULL AND rd.ot = rdd.ot))
                      OR
                         (rd.sdok IS NOT NULL AND rd.ndok IS NOT NULL AND rdd.sdok IS NOT NULL AND
                          rdd.ndok IS NOT NULL AND rd.sdok = rdd.sdok AND rd.ndok = rdd.ndok))
                 );
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') ||
                ' для человека старше 3х лет льгота 57(ребенок первых 3х лет жизни) не действует!'
    FROM rds.t_import_rr_direction_benefits rdb
    JOIN rds.t_import_rr_direction rd ON rd.importelementsid = rdb.importrrdirection
    JOIN rds.t_benefits b ON b.benefitsid = rdb.benefits
    JOIN vclib.t_enums e ON e.enumid = b.benefitviews
    WHERE b.benefitcode = '57'
      AND e.code = 2
      AND rd.drod + INTERVAL '3 years' <= CURRENT_DATE
      AND ie.users = p_userid
      AND rdb.vid_i <> 3;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' снилс указан не корректно!'
    FROM rds.t_import_rr_direction rd
    WHERE LENGTH(TRIM(rd.snils)) <> 14
      AND ie.users = p_userid
      AND ie.importelementsid = rd.importelementsid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' снилс обязательное поле!'
    FROM rds.t_import_rr_direction rd
    WHERE rd.snils IS NULL
      AND rd.drod + INTERVAL '6 months' <= CURRENT_DATE
      AND ie.users = p_userid
      AND ie.importelementsid = rd.importelementsid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' пациент присутствует в федеральном регистре!'
    FROM rds.t_import_rr_direction rd
    WHERE ie.users = p_userid
      AND EXISTS (SELECT *
                  FROM rds.t_fr_patient fr
                  CROSS JOIN LATERAL
                      (
                      SELECT *
                      FROM rds.t_fr_record rec
                      WHERE rec.snils = fr.snils
                      ORDER BY rec.version DESC
                      LIMIT 1
                      ) t
                  WHERE fr.birthday = rd.drod
                    AND (fr.de_edv > CURRENT_TIMESTAMP OR fr.de_edv IS NULL OR fr.de_edv = '')
                    AND t.deleted = FALSE
                    AND ((rd.snils IS NOT NULL AND fr.snils = rd.snils) OR
                         (rd.sdok IS NOT NULL AND rd.ndok IS NOT NULL AND fr.docseries = rd.sdok AND
                          fr.docnumber = rd.ndok)
                      OR (fr.lastname = rd.fam AND fr.firstname = rd.im AND
                          (fr.fathername = rd.ot OR (fr.fathername IS NULL AND rd.ot IS NULL)) AND
                          ((rd.snils IS NULL AND fr.snils IS NULL) OR fr.snils = rd.snils)))
                 );
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' не указан id пациента(pac_id)!'
    FROM rds.t_import_rr_direction rd
    WHERE (rd.real_pac_id IS NULL OR rd.real_pac_id = 0)
      AND rd.vid_i <> 1
      AND ie.users = p_userid
      AND ie.importelementsid = rd.importelementsid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' пациент уже присутствует в базе! id пациента(pac_id): ' ||
                COALESCE(rd.pac_id::text, '')
    FROM rds.t_import_rr_direction rd
    WHERE rd.vid_i = 1
      AND rd.pac_id IS NOT NULL
      AND rd.pac_id <> 0
      AND ie.users = p_userid
      AND ie.importelementsid = rd.importelementsid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' пациент не найден в базе!'
    FROM rds.t_import_rr_direction rd
    WHERE rd.vid_i <> 1
      AND (
        rd.real_pac_id IS NULL
            OR rd.real_pac_id = 0
            OR (rd.real_pac_id IS NOT NULL AND rd.real_pac_id <> 0 AND rd.pac_id IS NOT NULL AND rd.pac_id <> 0 AND
                rd.pac_id <> rd.real_pac_id)
            OR NOT EXISTS (SELECT 1
                           FROM rds.t_rr_patient rr
                           WHERE rr.rrpatientid = rd.real_pac_id
                          )
        )
      AND ie.users = p_userid
      AND ie.importelementsid = rd.importelementsid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' не верный код мо!'
    FROM rds.t_import_rr_direction rd
    WHERE NOT EXISTS (SELECT 1
                      FROM rds.t_dlo_lpu dl
                      WHERE dl.mcod = rd.lpu_inf
                     )
      AND ie.users = p_userid
      AND ie.importelementsid = rd.importelementsid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' нет данных о льготах!'
    FROM rds.t_import_rr_direction rd
    WHERE rd.vid_i = 1
      AND NOT EXISTS (SELECT 1
                      FROM rds.t_import_rr_direction_benefits db
                      WHERE db.importrrdirection = ie.importelementsid
                     )
      AND ie.users = p_userid
      AND ie.importelementsid = rd.importelementsid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' дубль льгот!'
    FROM rds.t_import_rr_direction_benefits rd
    WHERE rd.vid_i = 1
      AND ie.users = p_userid
      AND EXISTS (SELECT 1
                  FROM rds.t_import_rr_direction_benefits db
                  WHERE db.importelementsid <> rd.importelementsid
                    AND db.importrrdirection = rd.importrrdirection
                    AND db.benefits = rd.benefits
                 )
      AND ie.importelementsid = rd.importelementsid;
    
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' данная льгота от указанной мо уже добавлена!'
    FROM rds.t_import_rr_direction_benefits rd
    JOIN rds.t_import_rr_direction rrd ON rrd.importelementsid = rd.importrrdirection
    WHERE rd.vid_i = 1
      AND ie.users = p_userid
      AND EXISTS (SELECT 1
                  FROM rds.t_rr_patient_benefit db
                  JOIN rds.t_rr_patient rrp ON rrp.id = db.rrpatient
                  WHERE rrp.rrpatientid = rrd.real_pac_id
                    AND db.benefits = rd.benefits
                    AND (db.enddate IS NULL OR db.enddate > CURRENT_DATE)
                    AND rrd.lpu_inf = db.lpu_inf
                 )
      AND ie.importelementsid = rd.importelementsid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' льгота от указанной мо не найдена!'
    FROM rds.t_import_rr_direction_benefits rdb
    JOIN rds.t_import_rr_direction rd ON rd.importelementsid = rdb.importrrdirection
    WHERE rdb.vid_i <> 1
      AND ie.users = p_userid
      AND NOT EXISTS (SELECT 1
                      FROM rds.t_rr_patient_benefit pb
                      JOIN rds.t_rr_patient rrp ON rrp.id = pb.rrpatient
                      WHERE pb.lpu_inf = rd.lpu_inf
                        AND rdb.benefits = pb.benefits
                        AND rrp.rrpatientid = rd.pac_id
                     )
      AND ie.importelementsid = rdb.importelementsid;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' льгота 95 не должна включаться в региональный регистр!'
    FROM rds.t_import_rr_direction_benefits rdb
    JOIN rds.t_benefits b ON b.benefitsid = rdb.benefits
    WHERE b.benefitcode = '95'
      AND ie.users = p_userid
      AND ie.importelementsid = rdb.importelementsid;
    
    IF EXISTS (SELECT 1
               FROM rds.t_sickness_group s
               JOIN rds.t_import_rr_direction_benefits rdb ON rdb.benefits = s.benefits
               JOIN rds.t_import_elements ie ON ie.importelementsid = rdb.importelementsid
               WHERE ie.users =p_userid
               LIMIT 1
              ) THEN
        -- Выполняем обновление
        UPDATE rds.t_import_elements ie
        SET error = COALESCE(ie.error, ' ') || ' не правильный код мкб для указанной льготы!'
        FROM rds.t_import_rr_direction_benefits rdb
        JOIN rds.t_benefits b ON b.benefitsid = rdb.benefits
        JOIN vclib.t_enums e ON e.enumid = b.benefitviews
        WHERE e.code = 3
          AND ie.users = p_userid
          AND NOT EXISTS (SELECT 1
                          FROM rds.t_sickness_group s
                          WHERE s.benefits = b.benefitsid
                            AND s.mkbk >= rdb.mkb
                            AND s.mkbn <= rdb.mkb
                         )
          AND ie.importelementsid = rdb.importelementsid;
    END IF;
    
    UPDATE rds.t_import_elements ie
    SET error = COALESCE(ie.error, ' ') || ' имеются ошибки в льготах!'
    FROM rds.t_import_rr_direction rd
    WHERE ie.users = p_userid
      AND EXISTS (SELECT 1
                  FROM rds.t_import_rr_direction_benefits db
                  JOIN rds.t_import_elements iee ON iee.importelementsid = db.importelementsid
                  WHERE db.importrrdirection = ie.importelementsid
                    AND iee.error IS NOT NULL
                 );
END;
$$

