CREATE PROCEDURE [Common].[uspDeleteBaseContent] @EmployeeId INT, @BaseId UniqueIdentifier
AS
BEGIN 

	SET NOCOUNT ON 
	
	DROP TABLE IF EXISTS #ChangesetsToBeDeleted 
	
	PRINT 'Library Sanitization Started.'
	
	BEGIN TRANSACTION; 
	BEGIN TRY
	
	-- Creation of temporary tables to be used within the SQL
	CREATE TABLE #ChangesetsToBeDeleted ( ChangesetID UNIQUEIDENTIFIER NOT NULL  ) 
	
	-- Identify the Changesets that should be deleted from all the tables
	INSERT INTO #ChangesetsToBeDeleted (ChangesetID  )
	SELECT CC.ID  
	FROM  Common.Changesets CC 
	WHERE CC.Status = 'A'
	AND BaseId=@BaseId
	
	--************************************************HARD DELETING RECORDS FROM QUESTIONNAIRE TABLES*************************************
	DELETE AA FROM Questionnaire.ClearAnswerActions							   AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.SingleSignOffActions                          AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.SetDefaultValueActions                        AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.ScopeConditions                               AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.FormulaConditions                             AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.FormattingActions                             AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.ItemActions                                   AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.RowFieldAnswerOptionValueTranslations         AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.RowFieldAnswerOptionValues                    AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.RowFieldPropertyValueTranslations             AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.RowFieldPropertyValues                        AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.QuestionConditions                            AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.RowFieldAnswerOptions                         AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.QuestionnaireExpressions                      AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.TableRowFields                                AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId 
	DELETE AA FROM Questionnaire.QuestionnaireFormulas                         AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.TableColumnValueTranslations                  AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.TableColumnValues                             AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.TableColumns                                  AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId 
	DELETE AA FROM Questionnaire.TableElements                                 AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.FieldValueTranslations                        AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.FieldValues                                   AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.Fieldtags                                     AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.Actions                                       AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.AnswerOptionValueTranslations                 AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.AnswerOptionValues                            AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.AnswerOptions                                 AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.Conditions                                    AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.Rules                                         AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.Fields						                   AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	DELETE AA FROM Questionnaire.Questionnaires				                   AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	
	--************************************************HARD DELETING RECORDS FROM AMT SCHEMA TABLES*************************************
	
	DELETE FROM DUH	FROM	AMT.DocumentUploadHistory DUH			JOIN AMT.ItemVersions IV ON DUH.ItemVersionId = IV.ID				JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId
	DELETE FROM TVH FROM Common.TagVersionHistory TVH				JOIN #ChangesetsToBeDeleted BB ON TVH.ChangesetId = BB.ChangesetId 
	DELETE FROM DTP FROM	AMT.DocumentTranslationProperties DTP	INNER JOIN AMT.ItemVersionTranslations IVT ON DTP.ItemVersionTranslationId = IVT.Id JOIN AMT.ItemVersions IV ON IVT.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId

	DELETE FROM IT FROM  AMT.ITEMTAGS IT JOIN AMT.ItemVersions IV ON IT.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId
	DELETE FROM RT FROM  AMT.Relationships RT JOIN AMT.ItemVersions IV ON RT.SourceItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId
	DELETE FROM RT FROM  AMT.Relationships RT JOIN AMT.ItemVersions IV ON RT.TargetItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId
	DELETE FROM DU FROM  AMT.DocumentUpload DU JOIN AMT.ItemVersions IV ON DU.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId
	DELETE FROM IVT FROM  AMT.ItemVersionTranslations IVT JOIN AMT.ItemVersions IV ON IVT.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId
	DELETE FROM IVT FROM  AMT.ItemVersionMasterTranslations IVT JOIN AMT.ItemVersions IV ON IVT.ItemVersionId = IV.ID JOIN #ChangesetsToBeDeleted BB ON IV.ChangesetId = BB.ChangesetId
	DELETE FROM IVT FROM Amt.ItemVersionTranslations IVT JOIN Common.LanguageVersions LV ON IVT.LanguageVersionId = LV.Id  JOIN #ChangesetsToBeDeleted BB ON LV.ChangesetId = BB.ChangesetId
	
	--************************************************HARD DELETING RECORDS FROM COMMON SCHEMA TABLES*************************************************
	
	DELETE FROM TR FROM Common.TagRelationships TR JOIN Common.TagVersions TV ON TR.SourceTagVersionId = TV.Id JOIN #ChangesetsToBeDeleted BB ON TV.ChangesetId = BB.ChangesetId 
	DELETE FROM TR FROM Common.TagRelationships TR JOIN Common.TagVersions TV ON TR.TargetTagVersionId = TV.Id JOIN #ChangesetsToBeDeleted BB ON TV.ChangesetId = BB.ChangesetId 
	DELETE FROM TR FROM Common.TagVersionTranslations TR JOIN Common.TagVersions TV ON TR.TagVersionId = TV.Id JOIN #ChangesetsToBeDeleted BB ON TV.ChangesetId = BB.ChangesetId
	DELETE FROM TR FROM Common.TagVersionTranslations TR JOIN Common.LanguageVersions LV ON TR.LanguageVersionId = LV.Id JOIN #ChangesetsToBeDeleted BB ON LV.ChangesetId = BB.ChangesetId
	
	--************************************************HARD DELETING RECORDS FROM MAIN TABLES*******************************************************
	DELETE AA FROM AMT.ItemVersions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	
	UPDATE LVA SET LVA.PreviousLanguageVersionId = NULL FROM Common.LanguageVersions LVA JOIN Common.LanguageVersions LVB ON LVA.PreviousLanguageVersionId = LVB.Id JOIN #ChangesetsToBeDeleted BB ON LVB.ChangesetId = BB.ChangesetId
	DELETE AA FROM Common.LanguageVersions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	
	UPDATE TVA SET TVA.PreviousTagVersionId = NULL FROM Common.TagVersions TVA JOIN Common.TagVersions TVB ON TVA.PreviousTagVersionId = TVB.Id JOIN #ChangesetsToBeDeleted BB ON TVB.ChangesetId = BB.ChangesetId
	DELETE AA FROM Common.TagVersions AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	
	DELETE AA FROM AMT.BaseSettings  AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	
	UPDATE Common.Changesets SET Status = 'D', ModifiedAt = GETUTCDATE(), ModifiedBy = @EmployeeId WHERE Id IN ( SELECT ChangesetId FROM #ChangesetsToBeDeleted )  
	
	--************************************************HARD DELETION ENDED******************************************************************
	
	
	--************************************************CLEANING UNWANTED DATA******************************************************************
	DELETE AA FROM Common.ValidationResults AA JOIN #ChangesetsToBeDeleted BB ON AA.ChangesetId = BB.ChangesetId
	
	PRINT 'Library Sanitization Completed.'
	
	END TRY
	BEGIN CATCH
		SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; 
		IF @@TRANCOUNT > 0  
	        ROLLBACK TRANSACTION; 
		THROW; 
	END CATCH
	
	IF @@TRANCOUNT > 0  
	BEGIN
	    COMMIT TRANSACTION;  
	
		--drop all temp tables
		DROP  TABLE #ChangesetsToBeDeleted 
	END

	SET NOCOUNT OFF 
END;
GO