DROP PROCEDURE IF EXISTS dev.uspIntezmenyRemoveDelete
GO

CREATE PROCEDURE dev.uspIntezmenyRemoveDelete
   @pIntezmenyIds nvarchar(1000) 
  ,@pIsArchivTanevTorles bit = 0 
  ,@pDebugMode bit = 0 
AS
BEGIN
  DECLARE 
     @tableName nvarchar(100)
    ,@intezmenyIdColumn nvarchar(50)
    ,@tanevIdColumn nvarchar(50)
    ,@sql nvarchar(max)
    ,@firstParentCol nvarchar(50)
    ,@firstRefObj nvarchar(50)
    ,@secondParentCol nvarchar(50)
    ,@secondRefObj nvarchar(50)
    ,@message nvarchar(max)
    ,@rowCount int

  -- ==================================================
  -- Index az ENTITYHISTORY-ra
  -- ==================================================
  IF @pIntezmenyIds IS NULL AND @pIsArchivTanevTorles = 1 BEGIN
    PRINT 1
    SET @sql = '
      SELECT @pIntezmenyIds = CONCAT(@pIntezmenyIds + '','', i.ID) FROM dbo.T_INTEZMENY i WHERE i.TOROLT = ''F''
    '
    PRINT @sql
    EXEC sp_executesql @sql, N'@pIntezmenyIds nvarchar(1000) output', @pIntezmenyIds output
  END

  


  -- Archiv tanévek törléséhez kell a temp tábla
  CREATE TABLE #Tanev (TanevId int PRIMARY KEY)

  IF @pIsArchivTanevTorles = 1 BEGIN

    SET @sql = '
      SELECT tv.ID AS TanevId
      FROM dbo.T_TANEV tv 
      WHERE tv.C_ARCHIVADATBAZIS IS NOT NULL
        AND tv.C_INTEZMENYID IN (' + @pIntezmenyIds + ')
      '
    PRINT @pIntezmenyIds

    PRINT @sql
    INSERT INTO #Tanev (TanevId)
    EXEC sp_executesql @sql

    UPDATE tm SET
      tm.C_FELMENTESROGZITOTANARID = NULL,
      tm.C_FELMENTESROGZITOTANARNEV = fh.C_NYOMTATASINEV
    FROM T_TANULOMENTESSEG tm
      INNER JOIN T_FELHASZNALO fh ON fh.ID = tm.C_FELMENTESROGZITOTANARID
      INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
      INNER JOIN T_TANEV tv ON tv.ID = tm.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
    WHERE tm.C_TANEVID <> fh.C_TANEVID

    UPDATE z SET
      z.C_ROGZITOID = NULL,
      z.C_ROGZITONEV = fh.C_NYOMTATASINEV
    FROM T_ZARADEK z
      INNER JOIN T_FELHASZNALO fh ON fh.ID = z.C_ROGZITOID
      INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
      INNER JOIN T_TANEV tv ON tv.ID = z.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
    WHERE z.C_TANEVID <> fh.C_TANEVID

    UPDATE z SET
      z.C_UTOLSOMODOSITOID = NULL,
      z.C_UTOLSOMODOSITONEV = fh.C_NYOMTATASINEV
    FROM T_ZARADEK z
      INNER JOIN T_FELHASZNALO fh ON fh.ID = z.C_ROGZITOID
      INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
      INNER JOIN T_TANEV tv ON tv.ID = z.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
    WHERE z.C_TANEVID <> fh.C_TANEVID

    -- Ha kikerül az adatbázisból a két oszlop, akkor ezt is törölni kell az SP-ből
    UPDATE z SET
      z.C_ORATULAJDONOSID = NULL
    FROM T_ORARENDIORA z
      INNER JOIN T_FELHASZNALO fh ON fh.ID = z.C_ORATULAJDONOSID
      INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
      INNER JOIN T_TANEV tv ON tv.ID = z.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
    WHERE z.C_TANEVID <> fh.C_TANEVID
    
    UPDATE z SET
      z.C_TULAJDONOSID = NULL
    FROM T_NEMKOTOTTMUNKAIDO z
      INNER JOIN T_FELHASZNALO fh ON fh.ID = z.C_TULAJDONOSID
      INNER JOIN #Tanev tmp ON tmp.TanevId = fh.C_TANEVID
      INNER JOIN T_TANEV tv ON tv.ID = z.C_TANEVID AND tv.C_ARCHIVADATBAZIS IS NULL
    WHERE z.C_TANEVID <> fh.C_TANEVID


  END

  IF @pIsArchivTanevTorles = 0 BEGIN

    SET @message = 'CREATING ENTITYHISTORY INDEX... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT 
  
    SET @sql = N'
    DROP INDEX IF EXISTS IX_TMP_EntityHistory_IntezmenyId_Delete 
     ON dbo.T_ENTITYHISTORY

    CREATE NONCLUSTERED INDEX IX_TMP_EntityHistory_IntezmenyId_Delete 
      ON dbo.T_ENTITYHISTORY (C_INTEZMENYID) 
      WHERE C_INTEZMENYID IN (' + @pIntezmenyIds + ')
    '

    IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @sql
    SET @message = 'CREATING ENTITYHISTORY INDEX DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
    RAISERROR (@message, 10, 1) WITH NOWAIT   

  END

  RAISERROR ('DATA DELETION... ', 10, 1) WITH NOWAIT

  -- ==================================================
  -- ENTITYATTRIBUTEHISTORY & ENTITYBLOBVALUES törlés
  -- ==================================================
  IF @pIsArchivTanevTorles = 0 BEGIN
    SET @sql = N'
    DELETE eb 
    FROM T_ENTITYBLOBVALUES eb
      INNER JOIN T_ENTITYHISTORY e ON e.ID = eb.C_ENTITYHISTORYID 
    WHERE e.C_INTEZMENYID IN (' + @pIntezmenyIds + ')
    SET @rowCount = @@ROWCOUNT
    '

    IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
    SET @message = '    DEL-A ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_ENTITYBLOBVALUES - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
    RAISERROR (@message, 10, 1) WITH NOWAIT

    SET @sql = N'
    DELETE FROM T_ENTITYATTRIBUTEHISTORY
    WHERE EXISTS (SELECT 1 FROM T_ENTITYHISTORY eh WHERE eh.ID = C_ENTITYHISTORYID AND eh.C_INTEZMENYID IN (' + @pIntezmenyIds + '))
    SET @rowCount = @@ROWCOUNT
    '

    IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
    SET @message = '    DEL-A ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_ENTITYATTRIBUTEHISTORY - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
    RAISERROR (@message, 10, 1) WITH NOWAIT 

    SET @sql = N'
    DELETE eh
    FROM T_ENTITYHISTORY eh
    WHERE eh.C_INTEZMENYID IN (' + @pIntezmenyIds + ')
    SET @rowCount = @@ROWCOUNT
    '

    IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
    SET @message = '    DEL-A ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_ENTITYHISTORY - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
    RAISERROR (@message, 10, 1) WITH NOWAIT 

  END

  -- ==================================================
  -- Kapcsolótábla törlés
  -- ==================================================
  SET @sql = N''

  DECLARE tbl_cur CURSOR LOCAL FOR
  SELECT t.name
  FROM sys.tables t
  WHERE NOT EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_Id AND c.name = 'ID')
    AND NOT EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_Id AND c.name = 'C_ALINTEZMENYID')
    AND NOT EXISTS (SELECT 1 FROM sys.columns c WHERE c.object_id = t.object_Id AND c.name = 'C_INTEZMENYID') 
    AND t.name LIKE 'T[_]%'
    AND t.schema_id = 1
    AND t.name NOT IN ('T_ENTITYBLOBVALUES', 'T_AMIFOTARGY_AMIALTARGY', 'T_ENTITYATTRIBUTEHISTORY')
  OPEN tbl_cur
  FETCH NEXT FROM tbl_cur
  INTO @tableName

  WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT TOP(1) 
       @firstParentCol = pc.[name]-- AS parent_column_name
      ,@firstRefObj = OBJECT_NAME(rc.[object_id]) --as referenced_object_name
    FROM sys.foreign_key_columns f 
      INNER JOIN sys.columns pc ON pc.[object_id] = f.parent_object_id AND pc.column_id = f.parent_column_id
      INNER JOIN sys.columns rc ON rc.[object_id] = f.referenced_object_id AND rc.column_id = f.referenced_column_id
    WHERE parent_object_id = OBJECT_ID(@tableName)
    ORDER BY f.constraint_object_id ASC

    SELECT TOP(1)
       @secondParentCol = pc.[name]-- AS parent_column_name
      ,@secondRefObj = OBJECT_NAME(rc.[object_id]) --as referenced_object_name
    FROM sys.foreign_key_columns f 
      INNER JOIN sys.columns pc ON pc.[object_id] = f.parent_object_id AND pc.column_id = f.parent_column_id
      INNER JOIN sys.columns rc ON rc.[object_id] = f.referenced_object_id AND rc.column_id = f.referenced_column_id
    WHERE parent_object_id = OBJECT_ID(@tableName)
    ORDER BY f.constraint_object_id DESC

    SET @sql = N'
      DELETE x 
      FROM dbo.' + @tableName + ' x
        INNER JOIN dbo.' + @firstRefObj + ' s ON s.ID  = x.' + @firstParentCol + ' AND s.C_' + IIF(@firstRefObj IN ('T_TANULO', 'T_ALKALMAZOTT', 'T_OSZTALY', 'T_CSOPORT'), 'AL', '') + 'INTEZMENYID IN (' + @pIntezmenyIds + ')' + '
        INNER JOIN dbo.' + @secondRefObj + ' z ON z.ID  = x.' + @secondParentCol + ' AND z.C_' + IIF(@secondRefObj IN ('T_TANULO', 'T_ALKALMAZOTT', 'T_OSZTALY', 'T_CSOPORT'), 'AL', '') + 'INTEZMENYID IN (' + @pIntezmenyIds + ')' + '
        ' + IIF(@pIsArchivTanevTorles = 1, 'INNER JOIN #Tanev t ON t.TanevId = s.C_' + IIF(@firstRefObj IN ('T_TANULO', 'T_ALKALMAZOTT', 'T_OSZTALY', 'T_CSOPORT'), 'AL', '') + 'TANEVID ', '') + '
      SET @rowCount = @@ROWCOUNT
      '
    
    IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
    SET @message = '    DEL-B ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - ' + @tableName + ' - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
    RAISERROR (@message, 10, 1) WITH NOWAIT 
    
    FETCH NEXT FROM tbl_cur
    INTO @tableName
  END

  CLOSE tbl_cur
  DEALLOCATE tbl_cur

  -- ==================================================
  -- Normál tábla törlés
  -- ==================================================
   DECLARE tbl_cur CURSOR LOCAL FOR
    SELECT DISTINCT t.name AS TableName, c.name AS IntezmenyIdColumn, c2.name AS TanevIdColumn--, COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), 'ID', 'IsIdentity')
    FROM sys.tables t
      INNER JOIN sys.columns c ON c.object_id = t.object_id
      LEFT JOIN sys.columns c2 ON c2.object_id = t.object_id AND c2.name IN (N'C_ALTANEVID', N'C_TANEVID')
    WHERE c.name IN (N'C_ALINTEZMENYID', N'C_INTEZMENYID')
      AND t.name NOT IN (IIF(@pIsArchivTanevTorles = 1, 'T_ENTITYHISTORY', ''))
      
      AND t.name LIKE 'T[_]%'
      AND t.schema_id = 1
    ORDER BY t.name
    
  OPEN tbl_cur
  FETCH NEXT FROM tbl_cur
  INTO @tableName, @intezmenyIdColumn, @tanevIdColumn

  WHILE @@FETCH_STATUS = 0 BEGIN
    
    -- PRINT @tableName
    -- Achive törléskor csak azokból a táblákból törlünk, ahol van C_(AL)TANEVID oszlop
    IF @pIsArchivTanevTorles = 1 OR  @tanevIdColumn IS NOT NULL BEGIN
      SET @sql = N'
        DELETE x
        FROM dbo.' + @tableName + ' x
          ' + IIF(@pIsArchivTanevTorles = 1, 'INNER JOIN #Tanev t ON t.TanevId = x.' + @tanevIdColumn, '') + '
        WHERE x.' + @intezmenyIdColumn + ' IN (' + @pIntezmenyIds + ')

        SET @rowCount = @@ROWCOUNT
        '
    
      IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
      EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
      SET @message = '    DEL-C ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - ' + @tableName + ' - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
      RAISERROR (@message, 10, 1) WITH NOWAIT 
    END
    FETCH NEXT FROM tbl_cur
    INTO @tableName, @intezmenyIdColumn, @tanevIdColumn
  END
    
  CLOSE tbl_cur
  DEALLOCATE tbl_cur



  -- ==================================================
  -- INTEZMENY törlés
  -- ==================================================
  IF @pIsArchivTanevTorles = 0 BEGIN
     SET @sql = N'
      DELETE x
      FROM dbo.T_TANEV x
      WHERE x.C_INTEZMENYID IN (' + @pIntezmenyIds + ')

      SET @rowCount = @@ROWCOUNT
      '
 

    IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
    SET @message = '    DEL-C ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_TANEV - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
    RAISERROR (@message, 10, 1) WITH NOWAIT   

    
    SET @sql = N'
      DELETE x
      FROM dbo.T_INTEZMENY x
      WHERE x.ID IN (' + @pIntezmenyIds + ')

      SET @rowCount = @@ROWCOUNT
      '
 

    IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
    EXEC sp_executesql @sql, N'@rowCount int output', @rowCount output
    SET @message = '    DEL-C ' + FORMAT(GETDATE(), 'HH:mm:ss.ff') + ' - T_INTEZMENY - ' + CAST(@rowCount AS nvarchar(20)) + ' sor'
    RAISERROR (@message, 10, 1) WITH NOWAIT
  END

  -- ==================================================
  -- ENTITYHISTORY index törlés
  -- ================================================== 
  SET @message = 'DROPPING ENTITYHISTORY INDEX... - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
  RAISERROR (@message, 10, 1) WITH NOWAIT 
  
  SET @sql = N'
  DROP INDEX IF EXISTS IX_TMP_EntityHistory_IntezmenyId_Delete 
    ON dbo.T_ENTITYHISTORY
  '

  IF @pDebugMode = 1 RAISERROR (@sql, 10, 1) WITH NOWAIT
  EXEC sp_executesql @sql
  SET @message = 'DROPPING ENTITYHISTORY INDEX DONE - ' + FORMAT(GETDATE(), 'HH:mm:ss.ff')
  RAISERROR (@message, 10, 1) WITH NOWAIT 

END
GO