-- Intézmény adatokat eltávolító szkript, ha csak pár intézmény kell az adatbázisba
declare @INTEZMENYEK varchar(max) = 'klik034885002'; -- <----- Megmaradó intézmény azonosítók 
declare @SQL nvarchar(max);



-- EZUTÁN KÖVETKEZŐ SOROKHOZ NE NYÚLJ! -----------------------------------------------------------------------
-- ellenőrzés
select @SQL = 'Érvénytelen intézmény azonosító: ' + a.value
from string_split(@INTEZMENYEK, ',') a
where not exists(select * from T_INTEZMENY where C_AZONOSITO = a.value);

if @SQL > '' throw 50001, @SQL, 1;

set @INTEZMENYEK = stuff((
    select concat(',', i.ID)
    from string_split(@INTEZMENYEK, ',') a
    inner join T_INTEZMENY i on i.C_AZONOSITO = a.value
    for xml path('')
), 1, 1, '');

if isnull(ltrim(rtrim(@INTEZMENYEK)), '') = '' throw 50001, N'Nem található intézmény ID', 1;
print concat('Intézmény ID-k: ', @INTEZMENYEK);



-- simple recovery bekapcsolása
set @SQL = 'ALTER DATABASE ' + db_name() + ' SET RECOVERY SIMPLE';
if exists(select * from sys.databases where database_id = db_id() and recovery_model_desc <> 'SIMPLE') exec(@SQL);



-- triggerek letiltása ------------------------------------------------------------------------------------------------
set @SQL = '';
select @SQL += 'DISABLE TRIGGER ' + tr.name + ' ON ' + s.name + '.' + t.name + ';' + char(13)
from sys.triggers tr
inner join sys.tables t on t.object_id = tr.parent_id
inner join sys.schemas s on s.schema_id = t.schema_id
where tr.is_disabled = 0;

exec(@SQL);



-- foreign key-ek letiltása -------------------------------------------------------------------------------------------
set @SQL = '';
select @SQL += 'ALTER TABLE ' + s.name + '.' + t.name + ' NOCHECK CONSTRAINT ' + fk.name + ';' + char(13)
from sys.foreign_keys fk
inner join sys.tables t on t.object_id = fk.parent_object_id
inner join sys.schemas s on s.schema_id = t.schema_id
where fk.is_not_trusted = 0 or is_disabled = 0;

exec(@SQL);



-- nonclustered indexek törlése (opcionális) --------------------------------------------------------------------------
set @SQL = '';
select @SQL += 'DROP INDEX ' + i.name + ' ON [' + s.name + '].[' + t.name + '];' + char(13)
from sys.indexes i
inner join sys.tables t on t.object_id = i.object_id
inner join sys.schemas s on s.schema_id = t.schema_id
where i.type_desc = 'NONCLUSTERED'
and not exists(select * from sys.key_constraints pk where pk.type in ('PK', 'UQ') and pk.unique_index_id = i.index_id)
and i.is_unique = 0;

exec(@SQL);



-- intézmény adatok törlése -------------------------------------------------------------------------------------------
set xact_abort on;
set nocount on;
declare @object_id int = 0;
declare @DROP nvarchar(max), @INTO nvarchar(max), @TRUNCATE nvarchar(max), @INSERT nvarchar(max), @ADD nvarchar(max);

while (1 = 1)
    begin

    select  top 1
            @object_id = t.object_id,
            @DROP = '', 
            @INTO = '',
            @TRUNCATE = '', 
            @INSERT = '', 
            @ADD = ''
    from sys.tables t
    inner join sys.schemas s on s.schema_id = t.schema_id
    inner join sys.columns c on c.object_id = t.object_id
    where 1 = 1
	and t.object_id > @object_id
    and c.name in ('C_INTEZMENYID', 'C_ALINTEZMENYID')
    and s.name not in ('auditlog', 'core', 'dev', 'HangFire')
    and t.name not in ('XmlAudit')
    order by t.object_id;


    if @@rowcount = 0 break;


    select  
            @DROP +=    'ALTER TABLE ' + p.parent_object_name + ' DROP CONSTRAINT ' + fk.name + ';' + char(13),
            @INTO =     'SELECT * INTO ' + t_columns.current_object_name + '_temp FROM ' + t_columns.current_object_name + ' WHERE ' + c.name + ' IN (' + @INTEZMENYEK + ');' + char(13),
            @TRUNCATE = 'TRUNCATE TABLE ' + t_columns.current_object_name + ';' + char(13),
            @INSERT =   case when t_columns.has_identity = 1 then 'SET IDENTITY_INSERT ' + t_columns.current_object_name + ' ON;' + char(13) else '' end + 
                        'INSERT INTO ' + t_columns.current_object_name + '(' + t_columns.column_list + ') ' + 
                        'SELECT ' + t_columns.column_list + ' FROM ' + t_columns.current_object_name + '_temp;' + char(13) +
                        'DROP TABLE ' + t_columns.current_object_name + '_temp;' + char(13) +
                        case when t_columns.has_identity = 1 then 'SET IDENTITY_INSERT ' + t_columns.current_object_name + ' OFF;' + char(13) else '' end,
            @ADD +=     'ALTER TABLE ' + p.parent_object_name + ' WITH NOCHECK ADD CONSTRAINT ' + fk.name + ' ' +
                        'FOREIGN KEY (' + p_columns.column_list + ') REFERENCES ' + r.referenced_object_name + '(' + r_columns.column_list + ');' + char(13) +
                        'ALTER TABLE ' + p.parent_object_name + ' NOCHECK CONSTRAINT ' + fk.name + ';' + char(13)
    from sys.tables t
    inner join sys.schemas s on s.schema_id = t.schema_id
    inner join sys.columns c on c.object_id = t.object_id
    left join (
		sys.foreign_keys fk 
		inner join sys.tables pt  on pt.object_id = fk.parent_object_id
		inner join sys.schemas ps on ps.schema_id = pt.schema_id
		inner join sys.tables rt  on rt.object_id = fk.referenced_object_id 
		inner join sys.schemas rs on rs.schema_id = rt.schema_id
	) on fk.referenced_object_id = t.object_id
    outer apply (select ps.name + '.' + pt.name as parent_object_name) p
    outer apply (select rs.name + '.' + rt.name as referenced_object_name) r
    outer apply (
        select stuff((
                select ',' + c.name 
                from sys.columns c 
                where c.object_id = t.object_id
                and c.is_computed = 0
                order by c.column_id
                for xml path('')
            ), 1, 1, '') as column_list,
            case when exists(select * from sys.columns c where c.object_id = t.object_id and c.is_identity = 1) then 1 else 0 end as has_identity,
            s.name + '.' + t.name as current_object_name
    ) t_columns
    outer apply (
        select stuff((
                select ',' + c.name 
                from sys.columns c 
                inner join sys.foreign_key_columns fkc on fkc.parent_column_id = c.column_id and fkc.parent_object_id = c.object_id
                where fkc.constraint_object_id = fk.object_id 
                order by fkc.constraint_column_id 
                for xml path('')
            ), 1, 1, '') as column_list
    ) p_columns
    outer apply (
        select stuff((
                select ',' + c.name 
                from sys.columns c 
                inner join sys.foreign_key_columns fkc on fkc.referenced_column_id = c.column_id and fkc.referenced_object_id = c.object_id
                where fkc.constraint_object_id = fk.object_id
                order by fkc.constraint_column_id 
                for xml path('')
            ), 1, 1, '') as column_list
    ) r_columns
    where t.object_id = @object_id
    and c.name in ('C_INTEZMENYID', 'C_ALINTEZMENYID');



    set @SQL = CONCAT(
					'BEGIN TRANSACTION;' + char(13),
					@DROP, 
					@INTO,
					@TRUNCATE,
					@INSERT,
					@ADD,
					'COMMIT TRANSACTION;' + char(13),
					'CHECKPOINT;'
			   );
    begin try
        exec(@SQL);
    end try
    begin catch
        print @SQL;
        throw;
    end catch

    end

-- speciális táblák
-- T_ENTITYATTRIBUTEHISTORY
ALTER TABLE dbo.T_ENTITYBLOBVALUES DROP CONSTRAINT IF EXISTS FK_EntityBLOBValues;

BEGIN TRANSACTION;
SELECT * 
INTO dbo.T_ENTITYATTRIBUTEHISTORY_temp
FROM dbo.T_ENTITYATTRIBUTEHISTORY eah
WHERE EXISTS(SELECT * FROM dbo.T_ENTITYHISTORY eh WHERE eh.ID = eah.C_ENTITYHISTORYID);


TRUNCATE TABLE dbo.T_ENTITYATTRIBUTEHISTORY;

SET IDENTITY_INSERT dbo.T_ENTITYATTRIBUTEHISTORY ON;
INSERT INTO dbo.T_ENTITYATTRIBUTEHISTORY(ID, C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
SELECT ID, C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID FROM dbo.T_ENTITYATTRIBUTEHISTORY_temp
SET IDENTITY_INSERT dbo.T_ENTITYATTRIBUTEHISTORY OFF;

DROP TABLE dbo.T_ENTITYATTRIBUTEHISTORY_temp;
COMMIT TRANSACTION;



-- foreign key-ek visszakapcsolása (opcionális) -----------------------------------------------------------------------
set @SQL = '';
select @SQL += 'ALTER TABLE ' + s.name + '.' + t.name + ' CHECK CONSTRAINT ' + fk.name + ';' + char(13)
from sys.foreign_keys fk
inner join sys.tables t on t.object_id = fk.parent_object_id
inner join sys.schemas s on s.schema_id = t.schema_id
where fk.is_not_trusted = 1 or is_disabled = 1;

exec(@SQL);
go


-- shrinkelés ---------------------------------------------------------------------------------------------------------
DBCC SHRINKDATABASE(0);
CHECKPOINT;
DBCC SHRINKDATABASE(0);