CREATE OR REPLACE PROCEDURE public.sp_fr_loadbeneficiaries(
    currentdatetime timestamp,
    file uuid DEFAULT NULL,
    "Full" boolean DEFAULT FALSE
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_file            uuid        := file;
    p_currentdatetime timestamp   := currentdatetime;
    p_full            boolean     := "Full";
    v_ver             int ;
    v_datee           varchar(10) := '99991231';
BEGIN
    IF EXISTS (SELECT 1
               FROM pg_class c
               JOIN pg_namespace n ON c.relnamespace = n.oid
               WHERE c.relname = 'fed_reg1'
                 AND n.nspname = 'idb'
                 AND c.relkind = 'r'
              ) AND EXISTS (SELECT 1
                            FROM pg_class c
                            JOIN pg_namespace n ON c.relnamespace = n.oid
                            WHERE c.relname = 'fed_reg2'
                              AND n.nspname = 'idb'
                              AND c.relkind = 'r'
                           ) THEN
        
        UPDATE idb.fed_reg2 fr2
        SET date_doc = CASE
                           WHEN fr2.date_doc = ''
                               THEN v_datee
                           ELSE fr2.date_doc
                       END,
            date_el  = CASE
                           WHEN fr2.date_el = '99999999'
                               THEN v_datee
                           ELSE fr2.date_el
                       END;
        SELECT MAX(version)
        INTO v_ver
        FROM rds.t_federal_register_load;
        
        IF (v_ver IS NULL)
        THEN
            
            v_ver := 1;
            DELETE FROM rds.t_federal_register_load;
            DELETE FROM rds.t_fr_record;
            DELETE FROM rds.t_fr_document;
            DELETE FROM rds.t_fr_record;
        
        ELSE
            
            v_ver := v_ver + 1;
        
        END IF;
        
        
        INSERT INTO
            rds.t_federal_register_load
            (federalregisterloadid, users, version, updatedate, file)
        SELECT gen_random_uuid(),
               NULL,
               v_ver,
               p_currentdatetime,
               p_file;
        
        DELETE FROM rds.t_fr_patient;
        
        INSERT INTO
            rds.t_fr_patient(frpatientid,
                             snils,
                             lastname,
                             firstname,
                             fathername,
                             birthday,
                             sex,
                             regioncode,
                             rajoncode,
                             gr,
                             formapr,
                             doccode,
                             doctype,
                             docseries,
                             docnumber,
                             docdate,
                             docorg,
                             addrlegal,
                             addrreal,
                             location,
                             recnumber,
                             prizn1,
                             prizn2,
                             prizn3,
                             c_kat1,
                             c_kat2,
                             db_edv,
                             de_edv,
                             date_rsb,
                             date_rse,
                             codeizm,
                             prizn1next)
        SELECT gen_random_uuid(),
               ss,
               RTRIM(LTRIM(fam)),
               RTRIM(LTRIM(im)),
               RTRIM(LTRIM(ot)),
               dr::timestamp,
               CASE WHEN (UPPER(LTRIM(RTRIM(w))) IN ('Ж')) THEN 1 ELSE 0 END,
               RTRIM(LTRIM(regioncode)),
               RTRIM(LTRIM(rajoncode)),
               RTRIM(LTRIM(gr)),
               RTRIM(LTRIM(formapr)),
               RTRIM(LTRIM(doccode)),
               RTRIM(LTRIM(doctype)),
               RTRIM(LTRIM(docseries)),
               RTRIM(LTRIM(docnumber)),
               docdate::timestamp,
               RTRIM(LTRIM(docorg)),
               RTRIM(LTRIM(addrlegal)),
               RTRIM(LTRIM(addrreal)),
               RTRIM(LTRIM(location)),
               RTRIM(LTRIM(recnumber)),
               RTRIM(LTRIM(prizn1)),
               RTRIM(LTRIM(prizn2)),
               RTRIM(LTRIM(prizn3)),
               RTRIM(LTRIM(c_kat1)),
               RTRIM(LTRIM(c_kat2)),
               db_edv::timestamp,
               CASE
                   WHEN r1.de_edv::timestamp IN ('99991231'::timestamp, '19000101'::timestamp) THEN NULL
                   ELSE r1.de_edv::timestamp
               END,
               date_rsb::timestamp,
               CASE
                   WHEN r1.date_rse::timestamp IN ('99991231'::timestamp, '19000101'::timestamp) THEN NULL
                   ELSE r1.date_rse::timestamp
               END,
               RTRIM(LTRIM(codeizm)),
               RTRIM(LTRIM(prizn1next))
        FROM idb.fed_reg1 r1
        WHERE EXISTS (SELECT 1 FROM idb.fed_reg2 r2 WHERE r1.ss = r2.ss);
        
        
        DELETE FROM rds.t_fr_document;
        
        INSERT INTO
            rds.t_fr_document(frdocumentid,
                              name,
                              series,
                              number,
                              benefits,
                              snils,
                              date_doc,
                              n_organ,
                              date_bl,
                              date_el,
                              pr,
                              reserve)
        SELECT gen_random_uuid(),
               LTRIM(RTRIM(r2.name_d)),
               LTRIM(RTRIM(r2.sn_dl)),
               LTRIM(RTRIM(r2.nom_dl)),
               b.benefitsid,
               r2.ss,
               date_doc::timestamp,
               RTRIM(LTRIM(n_organ)),
               date_bl::timestamp,
               date_el::timestamp,
               RTRIM(LTRIM(pr)),
               RTRIM(LTRIM(r2.reserve))
        FROM idb.fed_reg2 r2
        LEFT JOIN vclib.t_enums e ON e.enumtypeid = 190 AND e.code = 1
        LEFT JOIN rds.t_benefits b ON b.benefitcode = r2.c_kat AND b.benefitviews = e.enumid
        JOIN idb.fed_reg1 r1 ON r1.ss = r2.ss;
        
        DELETE FROM rds.t_fr_record;
        
        INSERT INTO
            rds.t_fr_record
        (frrecordid, snils, deleted, datebegin, dateend, nsu, datensubegin, datensuend, nsus, version, updatedate)
        SELECT DISTINCT gen_random_uuid(),
                        r2.ss,
                        0,
                        r1.db_edv,
                        CASE WHEN r1.de_edv::timestamp = '19000101'::timestamp THEN NULL ELSE r1.de_edv::timestamp END,
                        CASE WHEN r1.prizn1 = 'Д' THEN 1 ELSE 0 END,
                        CASE WHEN r1.prizn1 = 'Д' THEN r2.date_bl::timestamp ELSE NULL END,
                        
                        CASE
                            WHEN r1.prizn1 = 'Д'
                                THEN CASE
                                         WHEN r2.date_el::timestamp = '19000101'::timestamp AND
                                              DATE_PART('year', r2.date_bl::timestamp) >
                                              EXTRACT(YEAR FROM CURRENT_TIMESTAMP) THEN v_datee::timestamp
                                         WHEN r2.date_el::timestamp = '19000101'::timestamp
                                             THEN EXTRACT(YEAR FROM CURRENT_TIMESTAMP)::text || '1231'
                                         ELSE r2.date_el::timestamp
                                     END
                            ELSE NULL
                        END,
                        CASE WHEN r1.prizn1next = 'Д' THEN TRUE ELSE FALSE END,
                        v_ver,
                        p_currentdatetime
        FROM idb.fed_reg2 r2
        JOIN idb.fed_reg1 r1 ON r1.ss = r2.ss;
    
    END IF;
END;
$$