DROP PROCEDURE IF EXISTS dbo.sp_updateclassificationgroupsrds;

DROP PROCEDURE IF EXISTS sp_updateclassificationgroupsrds;

CREATE OR REPLACE PROCEDURE sp_updateclassificationgroupsrds(
    "Xml" text DEFAULT NULL,
    "QueueName" character varying DEFAULT 'RDSSync'::character varying,
    "dateTime" timestamp without time zone DEFAULT NULL::timestamp without time zone
)
    LANGUAGE plpgsql
AS
$$
BEGIN
    "dateTime" := COALESCE("dateTime", CURRENT_TIMESTAMP::TIMESTAMP);
    
    -- Получаем данные из XML
    DROP TABLE IF EXISTS tmp_classificationgroups;
    CREATE TEMP TABLE tmp_classificationgroups
    AS
        SELECT (XPATH('/GROUP_OF_DRUGS_CLASSIFICATION/Name[1]/text()', x))[1]::varchar(255) AS name,
               (XPATH('/GROUP_OF_DRUGS_CLASSIFICATION/Code[1]/text()', x))[1]::text::int    AS code
        FROM UNNEST(XPATH(
            '/GROUPS_OF_DRUGS_CLASSIFICATION/body/GROUPS_OF_DRUGS_CLASSIFICATION/GROUP_OF_DRUGS_CLASSIFICATION',
            "Xml"::xml)) AS x;
    
    -- Загружаем новые лекарственные формы
    INSERT INTO pharm.t_groups_of_drugs_classification(code, name)
    SELECT nmf.code, nmf.name
    FROM tmp_classificationgroups nmf
    WHERE NOT EXISTS(SELECT 1 FROM pharm.t_groups_of_drugs_classification mf WHERE mf.code = nmf.code);
    
    -- Обновляем имеющиеся лекарственные формы
    UPDATE pharm.t_groups_of_drugs_classification mf
    SET name = nmf.name
    FROM tmp_classificationgroups nmf
    WHERE nmf.code = mf.code;

END;
$$;