DECLARE @daysToKeepNewsletters INT = 45
DECLARE @deleteBatchSize INT = 100000

PRINT 'Creating temp tables'
CREATE TABLE #EmailsToDelete(EmailId INT, EmailMessageId int)
CREATE TABLE #LinksToDelete(LinkId INT)
CREATE TABLE #RecipientsToDelete(RecipientId INT)

PRINT 'Finding emails to delete'
INSERT INTO #EmailsToDelete (EmailId, EmailMessageId) 
SELECT EMailId, EmailMessageId FROM EmailMarketingEmail WHERE EmailCreatedDate < DATEADD(DAY, -@daysToKeepNewsletters, GETDATE())

PRINT 'Deleting engagement index and split tests'
DELETE FROM EmailMarketingEngagementIndex WHERE EngagementIndexEmailId IN (SELECT EmailId FROM #EmailsToDelete)
DELETE FROM EmailMarketingSplitTest WHERE SplitTestEmailId IN (SELECT EmailId FROM #EmailsToDelete)

PRINT 'Deleting message tags'
DELETE FROM EmailMessageTag WHERE MessageTagMessageId IN (SELECT EmailMessageId FROM #EmailsToDelete)

PRINT 'Finding links to delete'
INSERT INTO #LinksToDelete (LinkId)
SELECT LinkId FROM OMCLink WHERE (LinkReferenceType = N'EmailMessaging') AND LinkReferenceKey IN (SELECT EmailMessageId FROM #EmailsToDelete)

PRINT 'Deleting link clicks'
DECLARE @r INT;
 
SET @r = 1;
 
WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;
  DELETE TOP (@deleteBatchSize) FROM dbo.OMCLinkClick WHERE LinkClickLinkId IN (SELECT LinkId FROM #LinksToDelete)
  SET @r = @@ROWCOUNT;
  COMMIT TRANSACTION;
  CHECKPOINT; 
END

PRINT 'Deleting links'
SET @r = 1;
 
WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;
  DELETE TOP (@deleteBatchSize) FROM dbo.OMCLink WHERE LinkId IN (SELECT LinkId FROM #LinksToDelete)
  SET @r = @@ROWCOUNT;
  COMMIT TRANSACTION;
  CHECKPOINT;    
END

PRINT 'Finding recipients'
INSERT INTO #RecipientsToDelete ( RecipientId )
SELECT RecipientId FROM dbo.EmailRecipient WHERE RecipientMessageId IN (SELECT EmailMessageId FROM #EmailsToDelete)

PRINT 'Deleting recipient tags'
SET @r = 1;
 
WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;
DELETE TOP (@deleteBatchSize) FROM EmailRecipientTag WHERE RecipientTagRecipientId IN (SELECT RecipientId FROM #RecipientsToDelete)
  SET @r = @@ROWCOUNT;
  COMMIT TRANSACTION;
  CHECKPOINT;
END


PRINT 'Deleting recipients'
SET @r = 1;
 
WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;
DELETE TOP (@deleteBatchSize) FROM EmailRecipient WHERE RecipientId IN (SELECT RecipientId FROM #RecipientsToDelete)
  SET @r = @@ROWCOUNT;
  COMMIT TRANSACTION;
  CHECKPOINT; 
END

PRINT 'Deleting email actions'
SET @r = 1;
 
WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;
DELETE TOP (@deleteBatchSize) FROM EmailAction WHERE ActionRecipientId IN (SELECT RecipientId FROM #RecipientsToDelete)
  SET @r = @@ROWCOUNT;
  COMMIT TRANSACTION;
  CHECKPOINT;  
END

PRINT 'Deleting emails'
DELETE FROM EmailMarketingEmail WHERE EmailId IN (SELECT EmailId FROM #EmailsToDelete)

PRINT 'Dropping temp tables'

DROP TABLE #EmailsToDelete
DROP TABLE #LinksToDelete
DROP TABLE #RecipientsToDelete