CREATE OR REPLACE PROCEDURE public.sp_fr_loadbeneficiarieswithupdate(
    currentdatetime timestamp,
    file uuid DEFAULT NULL,
    "Full" boolean DEFAULT FALSE
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_currentdatetime timestamp   := currentdatetime;
    p_file            uuid        := file;
    p_full            boolean     := "Full";
    v_ver             int;
    v_datee           varchar(10) := '99991231';

BEGIN
    
    p_currentdatetime := CASE
                             WHEN EXTRACT(DAY FROM currentdatetime) < 10 THEN
                                 TO_DATE(TO_CHAR(currentdatetime, 'YYYYMM') || '01', 'YYYYMMDD')
                             WHEN EXTRACT(DAY FROM currentdatetime) BETWEEN 10 AND 19 THEN
                                 TO_DATE(TO_CHAR(currentdatetime, 'YYYYMM') || '10', 'YYYYMMDD')
                             WHEN EXTRACT(DAY FROM currentdatetime) >= 20 AND (
                                 (EXTRACT(DAY FROM currentdatetime) < 31 AND
                                  EXTRACT(MONTH FROM currentdatetime) <> 12) OR
                                 (EXTRACT(DAY FROM currentdatetime) < 28 AND EXTRACT(MONTH FROM currentdatetime) = 12)
                                 ) THEN
                                 TO_DATE(TO_CHAR(currentdatetime, 'YYYYMM') || '20', 'YYYYMMDD')
                             WHEN EXTRACT(DAY FROM currentdatetime) >= 28 AND EXTRACT(MONTH FROM currentdatetime) = 12
                                 THEN
                                 TO_DATE((EXTRACT(YEAR FROM currentdatetime) + 1)::TEXT || '0101', 'YYYYMMDD')
                         END;
    
    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;
    
    CREATE INDEX ci ON idb.fed_reg1 (codeizm) WHERE codeizm <> '00';
    CREATE INDEX ss ON idb.fed_reg1 (ss);
    CREATE INDEX ss1 ON idb.fed_reg2 (ss);
    
    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;
    
    UPDATE rds.t_fr_patient fr
    SET lastname   = TRIM(r1.fam),
        firstname  = TRIM(r1.im),
        fathername = TRIM(r1.ot),
        birthday   = dr::timestamp,
        sex        = CASE
                         WHEN UPPER(TRIM(w)) = 'Ж' THEN 1
                         WHEN UPPER(TRIM(w)) = 'М' THEN 0
                         ELSE NULL
                     END,
        regioncode = TRIM(r1.regioncode),
        rajoncode  = TRIM(r1.rajoncode),
        gr         = TRIM(r1.gr),
        formapr    = TRIM(r1.formapr),
        doccode    = TRIM(r1.doccode),
        doctype    = TRIM(r1.doctype),
        docseries  = TRIM(r1.docseries),
        docnumber  = TRIM(r1.docnumber),
        docdate    = r1.docdate::timestamp,
        docorg     = r1.docorg,
        addrlegal  = TRIM(r1.addrlegal),
        addrreal   = TRIM(r1.addrreal),
        location   = TRIM(r1.location),
        recnumber  = TRIM(r1.recnumber),
        prizn1     = TRIM(r1.prizn1),
        prizn2     = TRIM(r1.prizn2),
        prizn3     = TRIM(r1.prizn3),
        c_kat1     = TRIM(r1.c_kat1),
        c_kat2     = TRIM(r1.c_kat2),
        db_edv     = r1.db_edv::timestamp,
        de_edv     = CASE WHEN r1.de_edv::timestamp = '19000101'::timestamp THEN NULL ELSE r1.de_edv::timestamp END,
        date_rsb   = r1.date_rsb::timestamp,
        date_rse   = CASE WHEN r1.date_rse::timestamp = '19000101'::timestamp THEN NULL ELSE r1.date_rse::timestamp END,
        codeizm    = TRIM(r1.codeizm),
        prizn1next = TRIM(r1.prizn1next)
    FROM idb.fed_reg1 r1
    WHERE r1.ss = fr.snils;
    
    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,
           TRIM(fam),
           TRIM(im),
           TRIM(ot),
           dr::timestamp,
           CASE
               WHEN UPPER(TRIM(w)) = 'Ж' THEN 1
               WHEN UPPER(TRIM(w)) = 'М' THEN 0
               ELSE NULL
           END,
           TRIM(regioncode),
           TRIM(rajoncode),
           TRIM(gr),
           TRIM(formapr),
           TRIM(doccode),
           TRIM(doctype),
           TRIM(docseries),
           TRIM(docnumber),
           docdate::timestamp,
           TRIM(docorg),
           TRIM(addrlegal),
           TRIM(addrreal),
           TRIM(location),
           TRIM(recnumber),
           TRIM(prizn1),
           TRIM(prizn2),
           TRIM(prizn3),
           TRIM(c_kat1),
           TRIM(c_kat2),
           db_edv::timestamp,
           CASE WHEN r1.de_edv::timestamp = '19000101'::timestamp THEN NULL ELSE r1.de_edv::timestamp END,
           date_rsb::timestamp,
           CASE WHEN r1.date_rse::timestamp = '19000101'::timestamp THEN NULL ELSE r1.date_rse::timestamp END,
           TRIM(codeizm),
           TRIM(prizn1next)
    FROM idb.fed_reg1 r1
    WHERE NOT EXISTS (SELECT * FROM rds.t_fr_patient fr WHERE fr.snils = r1.ss);
    
    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(),
           TRIM(r2.name_d),
           TRIM(r2.sn_dl),
           TRIM(r2.nom_dl),
           b.benefitsid,
           r2.ss,
           date_doc::timestamp,
           TRIM(n_organ),
           date_bl::timestamp,
           date_el::timestamp,
           TRIM(pr),
           TRIM(r2.reserve)
    FROM idb.fed_reg2 r2
    JOIN vclib.t_enums e ON e.enumtypeid = 190 AND e.code = 1
    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
    WHERE NOT EXISTS (SELECT 1
                      FROM rds.t_fr_document frd
                      WHERE frd.snils = r2.ss
                        AND TRIM(r2.sn_dl) = frd.series
                        AND TRIM(r2.nom_dl) = frd.number
                        AND b.benefitsid = frd.benefits
                     );
    
    INSERT INTO
        rds.t_fr_record (frrecordid,
                         snils,
                         deleted,
                         datebegin,
                         dateend,
                         nsu,
                         datensubegin,
                         datensuend,
                         nsus,
                         version,
                         updatedate)
    SELECT gen_random_uuid(),
           r1.ss,
           0,
           r1.db_edv::timestamp,
           CASE
               WHEN r1.de_edv::timestamp = '1900-01-01'::timestamp THEN NULL
               ELSE r1.de_edv::timestamp
           END,
           CASE
               WHEN r1.prizn1 = 'Д' THEN 1
               ELSE 0
           END,
           CASE
               WHEN r1.prizn1 = 'Д' THEN
                   CASE
                       WHEN EXTRACT(YEAR FROM d.date_bl::timestamp) < EXTRACT(YEAR FROM NOW())
                           AND (d.date_el::timestamp = '1900-01-01'::timestamp OR
                                EXTRACT(YEAR FROM d.date_el::timestamp) >= EXTRACT(YEAR FROM NOW()))
                           THEN EXTRACT(YEAR FROM NOW()) || '0101'::text::timestamp
                       WHEN d.date_bl::timestamp > d.date_el::timestamp
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) = EXTRACT(YEAR FROM NOW())
                           THEN EXTRACT(YEAR FROM NOW()) || '0101'::text::timestamp
                       ELSE d.date_bl::timestamp
                   END
               ELSE NULL
           END,
           CASE
               WHEN r1.prizn1 = 'Д' THEN
                   CASE
                       WHEN d.date_el::timestamp = '1900-01-01'::timestamp
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) <= EXTRACT(YEAR FROM NOW())
                           THEN EXTRACT(YEAR FROM NOW()) || '1231'::text::timestamp
                       WHEN d.date_el::timestamp = '1900-01-01'::timestamp
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) > EXTRACT(YEAR FROM NOW())
                           THEN v_datee::timestamp
                       WHEN EXTRACT(YEAR FROM d.date_el::timestamp) > EXTRACT(YEAR FROM NOW())
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) <= EXTRACT(YEAR FROM NOW())
                           THEN EXTRACT(YEAR FROM NOW()) || '1231'::text::timestamp
                       WHEN d.date_bl::timestamp > d.date_el::timestamp
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) = EXTRACT(YEAR FROM NOW())
                           THEN EXTRACT(YEAR FROM NOW()) || '1231'::text::timestamp
                       ELSE d.date_el::timestamp
                   END
               ELSE NULL
           END,
           CASE
               WHEN r1.prizn1next = 'Д' THEN 1
               ELSE 0
           END,
           v_ver,
           p_currentdatetime
    FROM idb.fed_reg1 r1
    JOIN rds.t_fr_patient fr ON r1.ss = fr.snils
    CROSS JOIN LATERAL (
        SELECT *
        FROM idb.fed_reg2 r2
        WHERE r1.ss = r2.ss
        ORDER BY r2.date_el::timestamp
        LIMIT 1
        ) d
    CROSS JOIN LATERAL (
        SELECT *
        FROM rds.t_fr_record rec
        WHERE rec.snils = r1.ss
        ORDER BY rec.version DESC
        LIMIT 1
        ) t
    WHERE r1.codeizm = '00'
      AND (
        t.deleted = 1
            OR t.datebegin <> r1.db_edv::timestamp
            OR (t.dateend IS NULL AND (CASE
                                           WHEN r1.de_edv::timestamp = '1900-01-01'::timestamp THEN NULL
                                           ELSE r1.de_edv::timestamp
                                       END) IS NOT NULL)
            OR (t.dateend IS NOT NULL AND (CASE
                                               WHEN r1.de_edv::timestamp = '1900-01-01'::timestamp THEN NULL
                                               ELSE r1.de_edv::timestamp
                                           END) IS NULL)
            OR ((t.dateend IS NOT NULL AND (CASE
                                                WHEN r1.de_edv::timestamp = '1900-01-01'::timestamp THEN NULL
                                                ELSE r1.de_edv::timestamp
                                            END) IS NOT NULL) AND t.dateend <> (CASE
                                                                                    WHEN r1.de_edv::timestamp = '1900-01-01'::timestamp
                                                                                        THEN NULL
                                                                                    ELSE r1.de_edv::timestamp
                                                                                END))
            OR t.nsu <> CASE WHEN r1.prizn1 = 'Д' THEN 1 ELSE 0 END
            OR t.nsus <> CASE WHEN r1.prizn1next = 'Д' THEN 1 ELSE 0 END
            OR t.datensubegin <> d.date_bl::timestamp
            OR (t.datensuend IS NULL AND (CASE
                                              WHEN d.date_el::timestamp = '1900-01-01'::timestamp THEN NULL
                                              ELSE d.date_el::timestamp
                                          END) IS NOT NULL)
            OR (t.datensuend IS NOT NULL AND (CASE
                                                  WHEN d.date_el::timestamp = '1900-01-01'::timestamp THEN NULL
                                                  ELSE d.date_el::timestamp
                                              END) IS NULL)
            OR ((t.datensuend IS NOT NULL AND (CASE
                                                   WHEN d.date_el::timestamp = '1900-01-01'::timestamp THEN NULL
                                                   ELSE d.date_el::timestamp
                                               END) IS NOT NULL) AND t.datensuend <> (CASE
                                                                                          WHEN d.date_el::timestamp = '1900-01-01'::timestamp
                                                                                              THEN NULL
                                                                                          ELSE d.date_el::timestamp
                                                                                      END))
        );
    
    
    INSERT INTO
        rds.t_fr_record (frrecordid,
                         snils,
                         deleted,
                         datebegin,
                         dateend,
                         nsu,
                         datensubegin,
                         datensuend,
                         nsus,
                         version,
                         updatedate)
    SELECT gen_random_uuid(),
           r1.ss,
           0,
           r1.db_edv::timestamp,
           CASE
               WHEN r1.de_edv::timestamp = '1900-01-01'::timestamp THEN NULL
               ELSE r1.de_edv::timestamp
           END,
           CASE
               WHEN r1.prizn1 = 'Д' THEN 1
               ELSE 0
           END,
           CASE
               WHEN r1.prizn1 = 'Д' THEN
                   CASE
                       WHEN EXTRACT(YEAR FROM d.date_bl::timestamp) < EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
                           AND (d.date_el::timestamp = '1900-01-01'::timestamp OR
                                EXTRACT(YEAR FROM d.date_el::timestamp) >= EXTRACT(YEAR FROM CURRENT_TIMESTAMP))
                           THEN EXTRACT(YEAR FROM CURRENT_TIMESTAMP) || '0101'::text::timestamp
                       WHEN d.date_bl::timestamp > d.date_el::timestamp
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) = EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
                           THEN EXTRACT(YEAR FROM CURRENT_TIMESTAMP) || '0101'::text::timestamp
                       ELSE d.date_bl::timestamp
                   END
               ELSE NULL
           END,
           CASE
               WHEN r1.prizn1 = 'Д' THEN
                   CASE
                       WHEN d.date_el::timestamp = '1900-01-01'::timestamp
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) <= EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
                           THEN EXTRACT(YEAR FROM CURRENT_TIMESTAMP) || '1231'::text::timestamp
                       WHEN d.date_el::timestamp = '1900-01-01'::timestamp
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) > EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
                           THEN v_datee::timestamp
                       WHEN EXTRACT(YEAR FROM d.date_el::timestamp) > EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) <= EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
                           THEN EXTRACT(YEAR FROM CURRENT_TIMESTAMP) || '1231'::text::timestamp
                       WHEN d.date_bl::timestamp > d.date_el::timestamp
                           AND EXTRACT(YEAR FROM d.date_bl::timestamp) = EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
                           THEN EXTRACT(YEAR FROM CURRENT_TIMESTAMP) || '1231'::text::timestamp
                       ELSE d.date_el::timestamp
                   END
               ELSE NULL
           END,
           CASE
               WHEN r1.prizn1next = 'Д' THEN 1
               ELSE 0
           END,
           v_ver,
           p_currentdatetime
    FROM idb.fed_reg1 r1
    JOIN rds.t_fr_patient fr ON r1.ss = fr.snils
    CROSS JOIN LATERAL (
        SELECT *
        FROM idb.fed_reg2 r2
        WHERE r1.ss = r2.ss
        ORDER BY r2.date_el::timestamp
        LIMIT 1
        ) d
    WHERE NOT EXISTS (SELECT 1
                      FROM rds.t_fr_record r1_sub
                      WHERE r1_sub.snils = fr.snils
                     );
    
    DROP TABLE idb.fed_reg1;
    DROP TABLE idb.fed_reg2;

END;
$$
