CREATE OR REPLACE PROCEDURE rds.sp_importrrdirection(
    userid uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_userid   uuid := userid;
    v_count    int;
    v_maxcount bigint;

BEGIN
    
    IF (EXISTS(SELECT 1
               FROM rds.t_import_rr_direction rd
               JOIN rds.t_import_elements ie ON ie.importelementsid = rd.importelementsid
               WHERE rd.real_pac_id = 0
                  OR rd.real_pac_id IS NULL
                   AND ie.error IS NULL
                   AND ie.users = p_userid
              )) THEN
        
        
        SELECT COUNT(*)
        INTO v_count
        FROM rds.t_import_rr_direction rd
        JOIN rds.t_import_elements ie ON ie.importelementsid = rd.importelementsid
        WHERE (rd.real_pac_id = 0 OR rd.real_pac_id IS NULL)
          AND ie.error IS NULL
          AND vid_i = 1
          AND ie.users = p_userid;
        
        SELECT MAX(rrpatientid)
        INTO v_maxcount
        FROM rds.t_rr_patient;
        
        CALL public.sp_generateidforentity('RR_PATIENT', 'PrimaryKey', v_count);
        
        UPDATE rds.t_import_rr_direction rd
        SET real_pac_id = t.kod
        FROM rds.t_import_elements ie
        JOIN (SELECT (ROW_NUMBER() OVER (ORDER BY rd.importelementsid DESC) + v_maxcount) AS kod,
                     rd.importelementsid                                                  AS id
             ) t ON t.id = rd.importelementsid
        WHERE ie.importelementsid = rd.importelementsid
          AND (rd.real_pac_id = 0 OR rd.real_pac_id IS NULL)
          AND ie.error IS NULL
          AND vid_i = 1
          AND ie.users = p_userid;
    
    END IF;
    
    
    UPDATE rds.t_rr_patient rp
    SET lastname     = CASE WHEN p.dat_prov > rp.dat_prov THEN fam ELSE rp.lastname END,
        firstname    = CASE WHEN p.dat_prov > rp.dat_prov THEN im ELSE rp.firstname END,
        fathername   = CASE WHEN p.dat_prov > rp.dat_prov THEN ot ELSE rp.fathername END,
        birthday     = CASE WHEN p.dat_prov > rp.dat_prov THEN drod ELSE rp.birthday END,
        sex          = p.sex,
        snils        = p.snils,
        dateend      = NULL,
        lasteditdate = CURRENT_TIMESTAMP,
        lasteditlpu  = '999999',
        lpu_inf      = p.lpu_inf,
        docseries    = CASE WHEN p.dat_prov > rp.dat_prov THEN sdok ELSE rp.docseries END,
        docnumber    = CASE WHEN p.dat_prov > rp.dat_prov THEN ndok ELSE rp.docnumber END,
        dat_prov     = CASE WHEN p.dat_prov > rp.dat_prov THEN p.dat_prov ELSE rp.dat_prov END,
        kodadr       = p.kodadr,
        sost_r       = p.sost_r
    FROM rds.t_import_rr_direction p
    JOIN rds.t_import_elements ie ON ie.importelementsid = p.importelementsid
    WHERE p.real_pac_id = rp.rrpatientid
      AND vid_i IN (2, 3)
      AND ie.error IS NULL
      AND p.lpu_inf IS NOT NULL
      AND ie.users = p_userid;
    
    INSERT INTO
        rds.t_rr_patient
    (rrpatientid,
     fathername,
     firstname,
     lastname,
     birthday,
     sex,
     snils,
     datebegin,
     dateend,
     lasteditdate,
     lasteditlpu,
     docseries,
     docnumber,
     tipd,
     kodadr,
     sost_r,
     dat_prov,
     lpu_inf,
     id)
    SELECT real_pac_id,
           ot,
           im,
           fam,
           drod,
           sex,
           snils,
           CURRENT_TIMESTAMP,
           NULL,
           CURRENT_TIMESTAMP,
           '999999',
           sdok,
           ndok,
           tipd,
           kodadr,
           sost_r,
           dat_prov,
           rr.lpu_inf,
           CURRENT_TIMESTAMP
    FROM rds.t_import_rr_direction rr
    JOIN rds.t_import_elements ie ON ie.importelementsid = rr.importelementsid
    WHERE rr.lpu_inf IS NOT NULL
      AND ie.error IS NULL
      AND rr.real_pac_id IS NOT NULL
      AND vid_i = 1
      AND ie.users = p_userid
      AND NOT EXISTS (SELECT 1 FROM rds.t_rr_patient rrr WHERE rrr.rrpatientid = rr.real_pac_id);
    
    INSERT INTO
        rds.t_rr_direction (lastname,
                            firstname,
                            fathername,
                            birthday,
                            sex,
                            rrdirectionid,
                            rrpatient,
                            enterdate,
                            date,
                            lpu,
                            changetype,
                            snils,
                            docseries,
                            docnumber,
                            tipd,
                            kodadr,
                            sost_r,
                            dat_prov,
                            lpu_inf)
    SELECT rr.fam,
           rr.im,
           rr.ot,
           rr.drod,
           rr.sex,
           rr.importelementsid,
           rrp.id,
           CURRENT_TIMESTAMP,
           CURRENT_TIMESTAMP,
           '999999',
           rr.vid_i,
           rr.snils,
           rr.sdok,
           rr.ndok,
           rr.tipd,
           rr.kodadr,
           rr.sost_r,
           rr.dat_prov,
           rr.lpu_inf
    FROM rds.t_import_rr_direction rr
    JOIN rds.t_import_elements ie ON ie.importelementsid = rr.importelementsid
    JOIN rds.t_rr_patient rrp ON rrp.rrpatientid = rr.real_pac_id
    WHERE rr.lpu_inf IS NOT NULL
      AND ie.error IS NULL
      AND ie.users = p_userid;
    
    
    INSERT INTO
        rds.t_rr_direction_benefit (rrdirectionbenefitid,
                                    rrdirection,
                                    diagdate,
                                    document,
                                    documentdate,
                                    dop,
                                    mkb,
                                    benefits,
                                    changetype)
    SELECT l.importelementsid,
           l.importrrdirection,
           l.date_lb,
           l.ndok,
           NULL,
           l.dop,
           l.mkb,
           b.benefitsid,
           l.vid_i
    FROM rds.t_import_rr_direction_benefits l
    JOIN rds.t_import_elements iee ON iee.importelementsid = l.importelementsid
    JOIN rds.t_import_rr_direction p ON p.importelementsid = l.importrrdirection
    JOIN rds.t_import_elements ie ON ie.importelementsid = p.importelementsid
    JOIN rds.t_benefits b ON b.benefitsid = l.benefits
    WHERE iee.error IS NULL
      AND ie.error IS NULL
      AND ie.users = p_userid
      AND iee.users = p_userid;
    
    UPDATE rds.t_rr_patient_benefit pb
    SET enddate      = CASE
                           WHEN l.vid_i = 3 THEN CURRENT_DATE
                           WHEN (bn.benefitcode = '57' AND e.code = 2 AND p.snils IS NOT NULL AND p.snils <> '')
                               THEN (p.birthday + INTERVAL '3 years') - INTERVAL '1 day'
                           WHEN (bn.benefitcode = '57' AND e.code = 2 AND (p.snils IS NULL OR p.snils = ''))
                               THEN (p.birthday + INTERVAL '6 months') - INTERVAL '1 day'
                           ELSE pb.enddate
                       END,
        lasteditdate = CURRENT_TIMESTAMP,
        mkb          = l.mkb,
        dop          = l.dop,
        document     = l.ndok,
        lasteditlpu  = '999999',
        lpu_inf      = pp.lpu_inf
    FROM rds.t_rr_patient p
    JOIN rds.t_import_rr_direction_benefits l ON l.benefits = pb.benefits
    JOIN rds.t_benefits bn ON bn.benefitsid = l.benefits
    JOIN vclib.t_enums e ON e.enumid = bn.benefitviews
    JOIN rds.t_import_rr_direction pp ON pp.importelementsid = l.importrrdirection
    JOIN rds.t_import_elements ie ON ie.importelementsid = pp.importelementsid
    JOIN rds.t_import_elements iee ON iee.importelementsid = l.importelementsid
    WHERE p.id = pb.rrpatient
      AND pp.real_pac_id = p.rrpatientid
      AND l.vid_i IN (2, 3)
      AND ie.error IS NULL
      AND iee.error IS NULL
      AND pp.lpu_inf = pb.lpu_inf
      AND ie.users = p_userid
      AND iee.users = p_userid;
    
    INSERT INTO
        rds.t_rr_patient_benefit(rrpatientbenefitid,
                                 diagdate,
                                 document,
                                 documentdate,
                                 dop,
                                 mkb,
                                 benefits,
                                 rrpatient,
                                 begindate,
                                 enddate,
                                 lasteditdate,
                                 lasteditlpu,
                                 lpu_inf)
    SELECT l.importelementsid,
           l.date_lb,
           l.ndok,
           NULL,
           l.dop,
           l.mkb,
           l.benefits,
           rrp.id,
           CURRENT_TIMESTAMP,
           CASE
               WHEN (b.benefitcode = '57' AND e.code = 2 AND p.snils IS NOT NULL AND p.snils <> '')
                   THEN (p.drod + INTERVAL '3 years') - INTERVAL '1 day'
               WHEN (b.benefitcode = '57' AND e.code = 2 AND (p.snils IS NULL OR p.snils <> ''))
                   THEN (p.drod + INTERVAL '6 months') - INTERVAL '1 day'
               ELSE l.date_le
           END,
           CURRENT_TIMESTAMP,
           '999999',
           p.lpu_inf
    FROM rds.t_import_rr_direction_benefits l
    JOIN rds.t_import_rr_direction p ON p.importelementsid = l.importelementsid
    JOIN rds.t_import_elements ie ON ie.importelementsid = p.importelementsid
    JOIN rds.t_import_elements iee ON iee.importelementsid = l.importelementsid
    JOIN rds.t_benefits b ON b.benefitsid = l.benefits
    JOIN vclib.t_enums e ON e.enumid = b.benefitviews
    JOIN rds.t_rr_patient rrp ON rrp.rrpatientid = p.real_pac_id
    WHERE l.vid_i = 1
      AND ie.error IS NULL
      AND iee.error IS NULL
      AND ie.users = p_userid
      AND iee.users = p_userid;
    
    UPDATE rds.t_rr_patient r
    SET dateend      = t.enddate,
        lasteditdate = CURRENT_TIMESTAMP
    FROM rds.t_rr_patient rr
    CROSS JOIN LATERAL (
        SELECT rrb.enddate
        FROM rds.t_rr_patient_benefit rrb
        WHERE rrb.rrpatient = rr.id
        ORDER BY COALESCE(rrb.enddate, '99990101') DESC
        LIMIT 1) t
    JOIN rds.t_import_rr_direction p ON p.real_pac_id = rr.rrpatientid
    JOIN rds.t_import_elements ie ON ie.importelementsid = p.importelementsid
    WHERE rr.id = r.id
      AND ie.error IS NULL
      AND p.lpu_inf IS NOT NULL
      AND ie.users = p_userid;
    
    DELETE
    FROM rds.t_import_rr_direction_benefits rd
        USING rds.t_import_elements ie
    WHERE ie.importelementsid = rd.importelementsid
      AND ie.users = p_userid
      AND ie.error IS NULL;
    
    DELETE
    FROM rds.t_import_rr_direction rd
        USING rds.t_import_elements ie
    WHERE ie.importelementsid = rd.importelementsid
      AND ie.users = p_userid
      AND ie.error IS NULL;
    
    DELETE
    FROM rds.t_import_elements
    WHERE users = p_userid
      AND type_ = (SELECT e.entityid
                   FROM vclib.t_entities e
                   WHERE e.entname = 'IMPORT_RR_DIRECTION'
                     AND e.schema = 'RDS'
                   LIMIT 1
                  )
      AND error IS NULL;
    
    DELETE
    FROM rds.t_import_elements
    WHERE users = p_userid
      AND type_ = (SELECT e.entityid
                   FROM vclib.t_entities e
                   WHERE e.entname = 'IMPORT_RR_DIRECTION_BENEFITS'
                     AND e.schema = 'RDS'
                   LIMIT 1
                  )
      AND error IS NULL;
    
    UPDATE public.t_entity_ids e
    SET id = (SELECT MAX(rrpatientid) + 1 FROM rds.t_rr_patient)
    WHERE e.entname IN ('RR_PATIENT', 'RDS.RR_PATIENT');


END;
$$
