DROP PROCEDURE IF EXISTS ehr.sp_EhrDeleteRefBooks;

CREATE PROCEDURE ehr.sp_EhrDeleteRefBooks
(
    "IdRefB" integer
)
    language plpgsql
as
$$
Begin

	declare 
		"EhrTableName" varchar(200);
		"Execute" text;

	Begin
		"EhrTableName" := (select  'T_EHR_RUS_' || rb.Code from EHR.T_EHR_RUS_REF_BOOKS rb where rb.EhrRusRefBooksID  = "IdRefB");

		if(exists(select * from EHR.T_EHR_REFERENCE where EhrRusRefBooks = "IdRefB"))
		THEN
			RAISE EXCEPTION 'Есть записи в таблице зависимостей справочников(EHR_REFERENCE)!';
		else

		    delete  
		from EHR.T_ELEMENT_EHR_REPLACEMENT el
			Using  EHR.T_EHR_REPLACEMENT e
		    where e.EhrReplacementID = el.EhrReplacement and e.OldEhrRefBook = "IdRefB" or e.NewEhrRefBook = "IdRefB";

		    delete from EHR.T_EHR_REPLACEMENT where OldEhrRefBook = "IdRefB" or NewEhrRefBook = "IdRefB";
		    
		    DROP TABLE IF EXISTS TableCol;
		    CREATE TEMP TABLE TableCol(
		    ehrtable varchar(200),
		        sch varchar(200),
		        tbl varchar(200),
		        colname varchar(200),
		        rowNum varchar(50)
		    );

            INSERT INTO TableCol(ehrtable, sch,tbl,colname,rowNum)
        SELECT 
        sub.tblName AS ehrtable,
        ns_child.nspname AS sch,
        ta.relname AS tbl,
        fa.attname AS colname,
        cast(ROW_NUMBER() over(order by sub.confrelid)as varchar(50)) rowNum
        FROM (
            SELECT
                cls_parent.relname as tblName,
                conrelid,
                confrelid,
                unnest(conkey) AS conkey,
                unnest(confkey) AS confkey
            FROM pg_constraint c
        	JOIN pg_class cls_parent ON c.confrelid = cls_parent.oid
            WHERE cls_parent.relname = lower("EhrTableName") and c.contype = 'f'
          ) sub
        JOIN pg_class ta ON ta.oid = sub.conrelid
        JOIN pg_namespace ns_child ON ta.relnamespace = ns_child.oid
        JOIN pg_attribute AS fa ON fa.attrelid = sub.conrelid AND fa.attnum = sub.conkey;

		    DROP TABLE IF EXISTS EhrDelTable;
		    CREATE TEMP TABLE EhrDelTable(
		    EhrRusId int
		);

		"Execute" := (SELECT string_agg((case when tt.rowNum = '1' 
		    										then 'insert into EhrDelTable(EhrRusId) ' 
		    										else 'union ' 
		    										end || 'select distinct(d' || tt.rowNum || '.' || tt.colname || ') from ' || tt.sch || '.' || tt.tbl || ' d' || tt.rowNum 
		    														|| ' join EHR.' || tt.ehrtable || ' ehr on d' || tt.rowNum || '.' || tt.colname || ' = ehr.EhrRusID and ehr.EhrRusRefBooks = ' || 2131::varchar(50)), ' ') || ';'
										from TableCol tt);
		    --select "Execute" 
		    EXECUTE "Execute";


		    if((select Count(*) from EhrDelTable) > 0)
		    THEN
			 "Execute" := (select 'delete from EHR.' || "EhrTableName" || ' ehr where ehr.EhrRusRefBooks = ' || "IdRefB"::varchar(50) || ' and not exists(select 1 from EhrDelTable e where e.EhrRusId = ehr.EhrRusID) ;

		    						if((select count(*) from EHR.' || "EhrTableName" ||' ehr where ehr.EhrRusRefBooks = ' || "IdRefB"::varchar(50) || ') > 0)
		    						THEN
		    							update EHR.T_EHR_RUS_REF_BOOKS set Deleted = ''' || 1 || ''' where EhrRusRefBooksID = ' ||  "IdRefB"::varchar(50) || '; 
		    						else
		    							delete from EHR.T_EHR_RUS_REF_BOOKS where EhrRusRefBooksID = ' ||  "IdRefB"::varchar(50) || ';
		    						end if');
		    	EXECUTE "Execute";
		    else
			"Execute" := (select  'delete from EHR.' || "EhrTableName" || ' ehr where ehr.EhrRusRefBooks = ' || "IdRefB"::varchar(50) || ';');

		    	EXECUTE "Execute";

		    	delete from EHR.T_EHR_RUS_REF_BOOKS where EhrRusRefBooksID = "IdRefB";
		    end if;
        end if;
	End;
End;
$$;
