CREATE OR REPLACE PROCEDURE RDS.sp_MilkRecipe(
    p_Xml xml,
    p_QueueName VARCHAR(100) DEFAULT 'RDSSync',
    p_dateTime TIMESTAMP DEFAULT NULL,
    p_User uuid DEFAULT NULL
)
AS
$$
DECLARE

BEGIN

    p_DateTime := COALESCE(p_dateTime, CURRENT_TIMESTAMP);

    CREATE TEMPORARY TABLE temp_MILK_RECIPE AS
    SELECT
        (XPATH('./MilkRecipeID', x))[1]::uuid                                   AS MilkRecipeID,
        (XPATH('./DairyCenter', x))[1]::INT                                     AS DairyCenter,
        (XPATH('./LPU', x))[1]::VARCHAR(6)                                      AS LPU,
        (XPATH('./LastName', x))[1]::VARCHAR(40)                                AS LastName,
        (XPATH('./FirstName', x))[1]::VARCHAR(40)                               AS FirstName,
        (XPATH('./FatherName', x))[1]::VARCHAR(40)                              AS FatherName,
        TO_TIMESTAMP((XPATH('./Birthday', x))[1]::VARCHAR(10), 'YYYY-MM-DD')    AS Birthday,
        (XPATH('./Sex', x))[1]::INTEGER                                         AS Sex,
        (XPATH('./DocSeries', x))[1]::VARCHAR(16)                               AS DocSeries,
        (XPATH('./DocNumber', x))[1]::VARCHAR(16)                               AS DocNumber,
        (XPATH('./DocType', x))[1]::INTEGER                                     AS DocType,
        (XPATH('./Polis', x))[1]::VARCHAR(16)                                   AS Polis,
        (XPATH('./Snils', x))[1]::VARCHAR(14)                                   AS Snils,
        TO_TIMESTAMP((XPATH('./BeginDate', x))[1]::VARCHAR(10), 'YYYY-MM-DD')   AS BeginDate,
        TO_TIMESTAMP((XPATH('./EndDate', x))[1]::VARCHAR(10), 'YYYY-MM-DD')     AS EndDate,
        TO_TIMESTAMP((XPATH('./PostingDate', x))[1]::VARCHAR(10), 'YYYY-MM-DD') AS PostingDate,
        (XPATH('./Category', x))[1]::INTEGER                                    AS Category,
        (XPATH('./Number', x))[1]::INTEGER                                      AS Number,
        (XPATH('./Seria', x))[1]::VARCHAR(255)                                  AS Seria,
        (XPATH('./MkbCode', x))[1]::VARCHAR(255)                                AS MkbCode,
        (XPATH('./Additional', x))[1]::INT::BOOLEAN                             AS Additional,
        (XPATH('./LowIncomeRegister', x))[1]::INT                               AS LowIncomeRegister,
        (XPATH('./RecipeDiscard', x))[1]::INT::BOOLEAN                          AS RecipeDiscard,
        (XPATH('./Age', x))[1]::INT                                             AS Age
    FROM UNNEST(XPATH('//MILK_RECIPE', p_Xml)) AS x;

    CREATE TEMPORARY TABLE temp_MILK_PRODUCT AS
    SELECT
        (XPATH('./RecipeID', x))[1]::uuid(40) AS RecipeID,
        (XPATH('./Amount', x))[1]::INT        AS Amount,
        (XPATH('./Dose', x))[1]::INT          AS Dose,
        (XPATH('./Name', x))[1]::VARCHAR(255) AS Name
    FROM UNNEST(XPATH('//MILK_RECIPE/MILK_PRODUCR', p_Xml)) AS x;

    INSERT INTO
        RDS.T_MILK_RECIPE
    (MilkRecipeID,
     DairyCenter,
     LpuMain,
     LastName,
     FirstName,
     FatherName,
     Birthday,
     Sex,
     DocSeries,
     DocNumber,
     DocType,
     Polis,
     SNILS,
     BeginDate,
     EndDate,
     Category,
     Number,
     Seria,
     MKBCode,
     Additional,
     LowIncomeRegister,
     Age,
     PostingDate)
    SELECT
        r.MilkRecipeID,
        dc.DairyCenterID,
        r.LPU,
        r.LastName,
        r.FirstName,
        r.FatherName,
        r.Birthday,
        eSex.EnumID,
        r.DocSeries,
        r.DocNumber,
        eDoc.EnumID,
        r.Polis,
        r.SNILS,
        r.BeginDate,
        r.EndDate,
        r.Category,
        r.Number,
        r.Seria,
        r.MKBCode,
        r.Additional,
        r.LowIncomeRegister,
        r.Age,
        r.PostingDate
    FROM temp_MILK_RECIPE r
    JOIN VCLib.T_ENUMS eSex ON eSex.EnumTypeID = 103 AND eSex.Code = r.Sex
    JOIN VCLib.T_ENUMS eDoc ON eDoc.EnumTypeID = 105 AND eDoc.Code = r.DocType
    LEFT JOIN LATERAL (
        SELECT
            dc.DairyCenterID
        FROM RDS.T_DAIRY_CENTER dc
        WHERE
              dc.Number = r.DairyCenter
          AND dc.IsActual = 1
        LIMIT 1) dc ON TRUE
    WHERE
          r.RecipeDiscard = 0
      AND NOT EXISTS (SELECT * FROM RDS.T_MILK_RECIPE mr WHERE mr.MilkRecipeID = r.MilkRecipeID);

    INSERT INTO
        RDS.T_MILK_PRODUCT (MilkProductID, MilkRecipe, Amount, Dose, Name)
    SELECT
        gen_random_uuid(),
        r.MilkRecipeID,
        mp.Amount,
        mp.Dose,
        mp.Name
    FROM temp_MILK_PRODUCT mp
    JOIN
    temp_MILK_RECIPE r ON r.MilkRecipeID = mp.RecipeId
    WHERE
          r.RecipeDiscard = FALSE
      AND NOT EXISTS (SELECT
                          1
                      FROM RDS.T_MILK_PRODUCT mpp
                      WHERE
                          mpp.MilkRecipe = r.MilkRecipeID);

    DELETE
    FROM RDS.T_MILK_PRODUCT mp
    WHERE
        EXISTS (SELECT
                    1
                FROM temp_MILK_RECIPE r
                WHERE
                      r.MilkRecipeID = mp.MilkRecipe
                  AND r.RecipeDiscard = TRUE);

    DELETE
    FROM RDS.T_MILK_RECIPE mr
    WHERE
        EXISTS (SELECT
                    1
                FROM temp_MILK_RECIPE r
                WHERE
                      r.MilkRecipeID = mr.MilkRecipeID
                  AND r.RecipeDiscard = TRUE);

    DROP TABLE temp_MILK_RECIPE;
    DROP TABLE temp_MILK_PRODUCT;
END;
$$
    LANGUAGE plpgsql;