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 'Есть записи в таблице зависимостей справочников!';
		end if;

		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
		conrelid::regclass AS ehrtable,
		ta.attname AS sch,
		sub.confrelid::regclass AS tbl,
		fa.attname AS colname,
		cast(ROW_NUMBER() over(order by sub.confrelid)as varchar(50)) rowNum
		FROM (
		    SELECT
		        conname,
		        conrelid,
		        confrelid,
		        unnest(conkey) AS conkey,
		        unnest(confkey) AS confkey
		    FROM pg_constraint c
		    WHERE c.conrelid = ('EHR.' || "EhrTableName")::regclass and c.contype = 'f'
		  ) sub
		JOIN pg_attribute AS ta ON ta.attrelid = sub.conrelid AND ta.attnum = sub.conkey
		JOIN pg_attribute AS fa ON fa.attrelid = sub.confrelid AND fa.attnum = sub.confkey;

		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 = ' || "IdRefB"::varchar(50)), '; ')
										from TableCol tt);
		--select "Execute" 
		EXECUTE "Execute";

		drop table TableCol;

		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;
		drop table EhrDelTable;
		drop table TableCol;
	End;
End;
$$;
