DROP PROCEDURE IF EXISTS sp_EhrRefUpdate;

CREATE PROCEDURE sp_EhrRefUpdate
(
    "IdRefB" integer
)
    language plpgsql
as
$$
Begin

IF "IdRefB" IS NULL 
THEN

    delete from  EHR.T_EHR_REFERENCE where Entities = EhrEntity or EhrRusRefBooks is null;
    
    delete   
    from EHR.T_EHR_REFERENCE ref
    	using VCLib.T_ENTITIES e
    where  e.EntityID = ref.Entities and not exists(select * 
    		from VCLib.T_ENTITIES_ATTRIBUTES ea 
    		where (ea.EntityID = ref.Entities or ea.EntityID = e.BaseEntity) and ea.Name = ref.AttributeName);
    	
    delete  
    from EHR.T_EHR_REFERENCE er
        Using VCLib.T_ENTITIES_ATTRIBUTES atr
        join VCLib.T_ENTITIES entRef on (entRef.Schema || '.' || entRef.EntName) = atr.SystemType
    where atr.Name = er.AttributeName and atr.EntityID = er.Entities and 
        entRef.Schema = 'EHR' and  er.EhrEntity != entRef.EntityID;
    
    DROP TABLE IF EXISTS TEMP_TABLE;
    CREATE TEMP TABLE TEMP_TABLE(
        Entities integer
      , AttributeName varchar
      , EhrEntity integer
    );
    
    INSERT INTO TEMP_TABLE(
        Entities
      , AttributeName
      , EhrEntity
    )
    SELECT
      atr.EntityID, 
      atr.Name, 
      entRef.EntityID 
    FROM
      VCLib.T_ENTITIES_ATTRIBUTES atr 
        JOIN VCLib.T_ENTITIES entRef ON (entRef.Schema || '.' || entRef.EntName) = atr.SystemType
    WHERE atr.EntityID != entRef.EntityID and entRef.Schema = 'EHR' and entRef.EntName != 'EHR_RUS_REF_BOOKS'
        AND NOT EXISTS(
                SELECT 1
                FROM EHR.T_EHR_REFERENCE ref
                WHERE
                    ref.Entities = atr.EntityID
                    AND ref.AttributeName = atr.Name
                    AND ref.EhrEntity = entRef.EntityID);
    
    WITH curVers (Entities, Rb) AS (
      SELECT
        rb.Entities, 
        rb.EhrRusRefBooksID 
      FROM
        EHR.T_EHR_RUS_REF_BOOKS rb 
      WHERE
          rb.CurrVersion IS NOT NULL
        AND EXISTS( SELECT 1 FROM TEMP_TABLE t WHERE t.EhrEntity = rb.Entities)
        AND NOT EXISTS( SELECT 1 
                        FROM EHR.T_EHR_RUS_REF_BOOKS rbb
                        WHERE
                            rbb.Entities = rb.Entities
                            AND (CAST(SUBSTRING(REPLACE(rbb.CurrVersion, ',', '.'),1,POSITION('.' IN REPLACE(rbb.CurrVersion, ',', '.')) -1) AS DECIMAL(31, 10))
                                > CAST(SUBSTRING(REPLACE(rb.CurrVersion, ',', '.'),1,POSITION('.' IN REPLACE(rb.CurrVersion, ',', '.')) -1) AS DECIMAL(31, 10))
                            OR (CAST(SUBSTRING(REPLACE(rbb.CurrVersion, ',', '.'),1,POSITION('.' IN REPLACE(rbb.CurrVersion, ',', '.')) -1) AS DECIMAL(31, 10))
                                = CAST(SUBSTRING(REPLACE(rb.CurrVersion, ',', '.'),1,POSITION('.' IN REPLACE(rb.CurrVersion, ',', '.')) -1) AS DECIMAL(31, 10))
                            AND CAST(SUBSTRING(REPLACE(rbb.CurrVersion, ',', '.'),POSITION('.' IN REPLACE(rbb.CurrVersion, ',', '.')) + 1,LENGTH(REPLACE(rbb.CurrVersion, ',', '.')) - POSITION('.' IN REPLACE(rbb.CurrVersion, ',', '.'))) AS DECIMAL(31, 10))
                                > CAST(SUBSTRING(REPLACE(rb.CurrVersion, ',', '.'),POSITION('.' IN REPLACE(rb.CurrVersion, ',', '.')) + 1,LENGTH(REPLACE(rb.CurrVersion, ',', '.')) - POSITION('.' IN REPLACE(rb.CurrVersion, ',', '.'))) AS DECIMAL(31, 10)))
                                )))
    INSERT INTO EHR.T_EHR_REFERENCE (
        EhrReferenceID
      , Entities
      , AttributeName
      , EhrRusRefBooks
      , EhrEntity
    )
    SELECT
        uuid_generate_v4()
      , tab.Entities
      , tab.AttributeName
      , cv.Rb
      , tab.EhrEntity
    FROM TEMP_TABLE tab
      JOIN curVers cv ON cv.Entities = tab.EhrEntity
    WHERE
        cv.Rb IS NOT NULL;
    
    DROP TABLE TEMP_TABLE;

ELSE
    update EHR.T_EHR_REFERENCE ref
		set EhrRusRefBooks = @IdRefB
	from EHR.T_EHR_RUS_REF_BOOKS refbook 
	where refbook.EhrRusRefBooksID = @IdRefB and ref.EhrEntity = refbook.Entities and ref.EhrRusRefBooks is null;
    
    INSERT INTO EHR.T_EHR_REFERENCE (
        EhrReferenceID
      , Entities
      , AttributeName
      , EhrRusRefBooks
      , EhrEntity
    ) 
    SELECT
        uuid_generate_v4()
      , ea.EntityID
      , ea.Name
      , "IdRefB"
      , eEhr.EntityID
    FROM VCLib.T_ENTITIES_ATTRIBUTES ea
        JOIN VCLib.T_ENTITIES eEhr ON eEhr.Schema || '.' || eEhr.EntName = ea.SystemType
        JOIN EHR.T_EHR_RUS_REF_BOOKS refbook ON refbook.EhrRusRefBooksID = "IdRefB" and eEhr.EntityID = refbook.Entities
    WHERE ea.EntityID != eEhr.EntityID and eEhr.EntName != 'EHR_RUS_REF_BOOKS' 
        AND NOT EXISTS(
                SELECT 1
                FROM EHR.T_EHR_REFERENCE err
                WHERE
                    err.EhrEntity = eEhr.EntityID
                    AND err.Entities = ea.EntityID
                    AND err.AttributeName = ea.Name);
    End IF;
End;
$$;
