CREATE OR REPLACE PROCEDURE public.sp_fr_loadbeneficiaries(
    "CurrentDateTime" text,
    "File" uuid DEFAULT NULL,
    "Full" boolean DEFAULT FALSE
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_file            uuid        := "File";
    p_currentdatetime timestamp   := "CurrentDateTime" :: timestamp;
    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;

        UPDATE "IDB"."FED_REG1" fr1
        SET 
            dr  = CASE
                           WHEN fr1.dr = ''
                               THEN null
                           ELSE fr1.dr
                       END,
            db_edv  = CASE
                           WHEN fr1.db_edv = ''
                               THEN null
                           ELSE fr1.db_edv
                       END,
            de_edv  = CASE
                           WHEN fr1.de_edv = ''
                               THEN null
                           ELSE fr1.de_edv
                       END,
            date_rse  = CASE
                           WHEN fr1.date_rse = ''
                               THEN null
                           ELSE fr1.date_rse
                       END,
            docdate  = CASE
                           WHEN fr1.docdate = ''
                               THEN null
                           ELSE fr1.docdate
                       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("recNumber")),
               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 IN ('99991231', '19000101') THEN NULL
                   ELSE r1.de_edv::timestamp
               END,
               date_rsb::timestamp,
               CASE
                   WHEN r1.date_rse IN ('99991231', '19000101') 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,
                        FALSE,
                        r1.db_edv::timestamp,
                        CASE WHEN r1.de_edv = '19000101' THEN NULL ELSE r1.de_edv::timestamp END,
                        CASE WHEN r1.prizn1 = 'Д' THEN TRUE ELSE FALSE END,
                        CASE WHEN r1.prizn1 = 'Д' THEN r2.date_bl::timestamp ELSE NULL END,
                        
                        CASE
                            WHEN r1.prizn1 = 'Д'
                                THEN CASE
                                         WHEN r2.date_el = '19000101' AND DATE_PART('year', r2.date_bl::timestamp) > EXTRACT(YEAR FROM CURRENT_TIMESTAMP) 
                                            THEN v_datee::timestamp
                                         WHEN r2.date_el = '19000101'
                                            THEN (EXTRACT(YEAR FROM CURRENT_TIMESTAMP)::text || '1231')::timestamp
                                         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;
$$