-- =============================================
-- Author:        Vitacore (с)
-- Create date: 2024-06-21
-- Description: Загрузка федеральных льготников в RDS
-- =============================================
DROP PROCEDURE IF EXISTS dbo.sp_fr_updatebeneficiaries;

DROP PROCEDURE IF EXISTS aps.sp_fr_updatebeneficiaries;

CREATE OR REPLACE PROCEDURE aps.sp_fr_updatebeneficiaries(
 "Date" timestamp DEFAULT NULL::timestamp
, "Ver" int DEFAULT NULL::int
, "Xml" text DEFAULT NULL
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    
    IF "Xml" IS NOT NULL
    THEN
        INSERT INTO
            aps.t_federal_register_load(federalregisterloadid, users, version, updatedate)
        SELECT gen_random_uuid(), NULL, "Ver", "Date";
        
        -- Generate table #SNILS
        DROP TABLE IF EXISTS tmpsnils;
        CREATE TEMP TABLE tmpsnils
        AS
            SELECT (XPATH('/FR_RECORD[1]/Snils[1]/text()', t.x))[1]::char(14) AS snils
            FROM (SELECT UNNEST(XPATH('/body/RECORDS/FR_RECORD', "Xml"::xml)) AS x
                 ) t
            UNION
            SELECT (XPATH('/FR_PATIENT[1]/Snils[1]/text()', t.x))[1]::char(14) AS snils
            FROM (SELECT UNNEST(XPATH('/body/PATIENTS/FR_PATIENT', "Xml"::xml)) AS x
                 ) t
            UNION
            SELECT (XPATH('/FR_DOCUMENT[1]/Snils[1]/text()', t.x))[1]::char(14) AS snils
            FROM (SELECT UNNEST(XPATH('/body/DOCUMENTS/FR_DOCUMENT', "Xml"::xml)) AS x
                 ) t;
        
        INSERT INTO aps.t_snils(snils)
        SELECT t.snils
        FROM tmpsnils t
        WHERE t.snils NOT IN (SELECT s.snils FROM aps.t_snils s);
        
        -- Drop Table
        DROP TABLE IF EXISTS tmpsnils;
        
        TRUNCATE TABLE aps.t_fr_patient;
        
        -- Generate table #T_FR_PATIENT
        DROP TABLE IF EXISTS tmp_fr_patient;
        CREATE TEMP TABLE tmp_fr_patient
        AS
            SELECT (XPATH('/FR_PATIENT[1]/FirstName[1]/text()', t.x))[1]::varchar(255)  AS firstname
                 , (XPATH('/FR_PATIENT[1]/LastName[1]/text()', t.x))[1]::varchar(255)   AS lastname
                 , (XPATH('/FR_PATIENT[1]/FatherName[1]/text()', t.x))[1]::varchar(255) AS fathername
                 , (XPATH('/FR_PATIENT[1]/Birthday[1]/text()', t.x))[1]::varchar(10)    AS birthday
                 , (XPATH('/FR_PATIENT[1]/Sex[1]/text()', t.x))[1]::text::int           AS sex
                 , (XPATH('/FR_PATIENT[1]/Snils[1]/text()', t.x))[1]::char(14)          AS snils
            FROM (SELECT UNNEST(XPATH('/body/PATIENTS/FR_PATIENT', "Xml"::xml)) AS x
                 ) t;
        
        INSERT INTO
            aps.t_fr_patient(frpatientid, snils, firstname, lastname, fathername, birthday, sex)
        SELECT gen_random_uuid(),
               fp.snils,
               fp.firstname,
               fp.lastname,
               fp.fathername,
               TO_TIMESTAMP(fp.birthday, 'DD.MM.YYYY'),
               fp.sex
        FROM tmp_fr_patient fp;
        
        -- Drop Table
        DROP TABLE IF EXISTS tmp_fr_patient;
        
        TRUNCATE TABLE aps.t_fr_record;
        
        -- Generate table #T_FR_RECORD
        DROP TABLE IF EXISTS tmp_fr_record;
        CREATE TEMP TABLE tmp_fr_record
        AS
            SELECT (XPATH('/FR_RECORD[1]/Deleted[1]/text()', t.x))[1]::text::boolean    AS deleted
                 , (XPATH('/FR_RECORD[1]/DateBegin[1]/text()', t.x))[1]::varchar(10)    AS datebegin
                 , (XPATH('/FR_RECORD[1]/DateEnd[1]/text()', t.x))[1]::varchar(10)      AS dateend
                 , (XPATH('/FR_RECORD[1]/NSU[1]/text()', t.x))[1]::text::boolean        AS nsu
                 , (XPATH('/FR_RECORD[1]/DateNsuBegin[1]/text()', t.x))[1]::varchar(10) AS datensubegin
                 , (XPATH('/FR_RECORD[1]/DateNsuEnd[1]/text()', t.x))[1]::varchar(10)   AS datensuend
                 , (XPATH('/FR_RECORD[1]/Nsus[1]/text()', t.x))[1]::text::boolean       AS nsus
                 , (XPATH('/FR_RECORD[1]/Version[1]/text()', t.x))[1]::text::int        AS version
                 , (XPATH('/FR_RECORD[1]/Snils[1]/text()', t.x))[1]::char(14)           AS snils
            FROM (SELECT UNNEST(XPATH('/body/RECORDS/FR_RECORD', "Xml"::xml)) AS x
                 ) t;
        
        INSERT INTO
            aps.t_fr_record(frrecordid,
                            snils,
                            deleted,
                            datebegin,
                            dateend,
                            nsu,
                            datensubegin,
                            datensuend,
                            nsus,
                            version)
        SELECT gen_random_uuid()
             , fr.snils
             , fr.deleted
             , TO_TIMESTAMP(fr.datebegin, 'DD.MM.YYYY')
             , TO_TIMESTAMP(fr.dateend, 'DD.MM.YYYY')
             , fr.nsu
             , TO_TIMESTAMP(fr.datensubegin, 'DD.MM.YYYY')
             , TO_TIMESTAMP(fr.datensuend, 'DD.MM.YYYY')
             , fr.nsus
             , fr.version
        FROM tmp_fr_record fr;
        
        -- Drop Table
        DROP TABLE IF EXISTS tmp_fr_record;
        
        TRUNCATE TABLE aps.t_fr_document;
        
        -- Generate table #T_FR_DOCUMENT
        DROP TABLE IF EXISTS tmp_fr_document;
        CREATE TEMP TABLE tmp_fr_document
        AS
            SELECT (XPATH('/FR_DOCUMENT[1]/Name[1]/text()', t.x))[1]::varchar(255)        AS name
                 , (XPATH('/FR_DOCUMENT[1]/Number[1]/text()', t.x))[1]::varchar(255)      AS number
                 , (XPATH('/FR_DOCUMENT[1]/BenefitViews[1]/text()', t.x))[1]::text::int   AS benefitviews
                 , (XPATH('/FR_DOCUMENT[1]/BenefitCode[1]/text()', t.x))[1]::varchar(255) AS benefitcode
                 , (XPATH('/FR_DOCUMENT[1]/Snils[1]/text()', t.x))[1]::char(14)           AS snils
            FROM (SELECT UNNEST(XPATH('/body/DOCUMENTS/FR_DOCUMENT', "Xml"::xml)) AS x
                 ) t;
        
        INSERT INTO
            aps.t_fr_document(frdocumentid, name, number, benefits, snils)
        SELECT gen_random_uuid(), dap.name, dap.number, b.benefitsid, dap.snils
        FROM tmp_fr_document dap
        JOIN vclib.t_enums   ebv ON ebv.enumtypeid = 190 AND ebv.code = dap.benefitviews
        JOIN aps.t_benefits  b ON b.benefitviews = ebv.enumid AND b.benefitcode = dap.benefitcode;
        
        -- Drop Table
        DROP TABLE IF EXISTS tmp_fr_document;
    END IF;
END;
$$;