-- Volatile Version -------------------------------------------------------------------------------------------------------------

-- Update ReportHeader to Set Archived Reports to DELETED
-- Update ReportHeader to Set child reports of deleted reports to DELETED
-- This is so all Report related logic below applies to child and archived headers

UPDATE ReportHeader SET ReportStatusCode = 'DELETED' WHERE ReportStatusCode = 'ARCHIVED';
UPDATE ReportHeader SET ReportStatusCode = 'DELETED' WHERE ReportId IN (SELECT c.ReportId FROM ReportHeader c INNER JOIN ReportHeader a ON (a.ReportId = c.ParentReportId) WHERE a.ReportStatusCode = 'DELETED') AND RoleCode = 'SUBQUERY';


-- Basic Report Elements
DELETE FROM ReportField WHERE ReportId IN  (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED');
DELETE FROM ReportFormat WHERE ReportId IN  (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED');
DELETE FROM ReportFieldParameter WHERE ReportId IN  (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED');
DELETE FROM ReportFieldConverter WHERE ReportId IN  (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED');
DELETE FROM ReportInstanceFilter WHERE InstanceId IN (SELECT b.ReportInstanceId FROM ReportHeader a INNER JOIN ReportInstance b ON (a.ReportId = b.ReportId) WHERE a.ReportStatusCode = 'DELETED');
DELETE FROM ReportInstance WHERE ReportInstanceId IN (SELECT b.ReportInstanceId FROM ReportHeader a INNER JOIN ReportInstance b ON (a.ReportId = b.ReportId) WHERE a.ReportStatusCode = 'DELETED');


-- Report Broadcasts
DELETE FROM ReportBroadcastRecipient WHERE BroadcastId IN (SELECT c.BroadcastId FROM ReportBroadcast c INNER JOIN ReportHeader b ON (c.ReportId = b.ReportId) WHERE b.ReportStatusCode = 'DELETED');
DELETE FROM ReportBroadcast WHERE ReportId IN (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED');
DELETE FROM ReportBroadcastRecipient WHERE BroadcastId NOT IN ( SELECT BroadcastId FROM ReportBroadcast);
DELETE FROM ReportBroadcastResult WHERE BroadcastId NOT IN ( SELECT BroadcastId FROM ReportBroadcast);

-- Report Text Entities
DELETE FROM TextData WHERE TextId IN (SELECT TextId FROM TextEntity a INNER JOIN ReportHeader b ON (a.SourceId = b.ReportId AND a.SourceTypeCode = 'REPORT') WHERE b.ReportStatusCode = 'DELETED');
DELETE FROM TextEntity WHERE SourceTypeCode = 'REPORT' AND SourceId IN (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED');

-- Report Cached Filters
DELETE FROM CachedFilterValue WHERE CachedFilterId IN (SELECT CachedFilterId FROM CachedFilter c INNER JOIN ReportHeader b ON (c.ReportId = b.ReportId) WHERE b.ReportStatusCode = 'DELETED');
DELETE FROM CachedFilter WHERE ReportId IN (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED');

-- Report Associates
DELETE FROM ReportAssociateField WHERE AssociationId IN  (SELECT AssociationId FROM ReportAssociate a INNER JOIN ReportHeader b ON (a.ParentReportId = b.ReportId) WHERE b.ReportStatusCode = 'DELETED');
DELETE FROM ReportAssociate WHERE ParentReportId IN (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED');

-- Report Charts
DELETE FROM ChartField WHERE ReportId IN (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED' );
DELETE FROM Chart WHERE ReportId IN (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED' );

-- Document Records
DELETE FROM DocumentItem WHERE SubjectTypeCode = 'REPORT' AND SubjectId IN (SELECT ReportId FROM ReportHeader WHERE ReportStatusCode = 'DELETED');
DELETE FROM DocumentItem WHERE SubjectTypeCode = 'REPORTFILTER' AND SubjectId NOT IN (SELECT FilterId FROM ReportFilter);
DELETE FROM DocumentRevision WHERE DocumentId NOT IN (SELECT DocumentId FROM DocumentItem);
DELETE FROM DocumentData WHERE RevisionId NOT IN (SELECT RevisionId FROM DocumentRevision);

-- Finally delete report headers that are DELETED.
DELETE FROM ReportHeader a WHERE a.ReportStatusCode = 'DELETED';

-- Delete Report Favourites that no longer exist
DELETE FROM PersonFavourite WHERE ContentTypeCode = 'REPORT' AND ContentId NOT IN (SELECT ReportId FROM ReportHeader)


-- Records Dependent on Views
-- ===================================

-- Update ReportView to Set Child Views of deleted Views to DELETED
-- This is so all View related logic below applies to child and archived views

UPDATE ReportView SET ViewStatusCode = 'DELETED' WHERE ViewId IN (SELECT b.ViewId FROM ReportView b INNER JOIN ReportView a ON (a.ViewId = b.ParentViewId) WHERE a.ViewStatusCode = 'DELETED' AND b.ViewTypeCode IN ('CHILDELEMENT','VIRTUAL') );  -- UPDATE to DELETED

-- Basic View Elements
DELETE FROM ReportView WHERE ParentViewId IN (SELECT ViewId FROM ReportView WHERE ViewStatusCode = 'DELETED') AND ViewTypeCode IN ('CHILDELEMENT','VIRTUAL');
DELETE FROM ReportFieldTemplate WHERE ViewId IN (SELECT ViewId FROM ReportView WHERE ViewStatusCode = 'DELETED');
DELETE FROM ReportFieldParameter WHERE EntityTypeCode = 'REPORTFIELDTEMPLATE' AND EntityId IN (SELECT c.FieldTemplateId FROM ReportFieldTemplate c INNER JOIN ReportView a ON (a.ViewID = c.ViewId) WHERE a.ViewStatusCode = 'DELETED');
DELETE FROM ReportFieldConverter WHERE ViewId IN (SELECT ViewId FROM ReportView WHERE ViewStatusCode = 'DELETED');

-- Document Records
DELETE FROM DocumentItem WHERE SubjectTypeCode = 'VIEW' AND SubjectId NOT IN (SELECT ViewId FROM ReportView);
DELETE FROM DocumentRevision WHERE DocumentId NOT IN (SELECT DocumentId FROM DocumentItem);
DELETE FROM DocumentData WHERE RevisionId NOT IN (SELECT RevisionId FROM DocumentRevision);


-- Finally delete report views that are DELETED.
DELETE FROM ReportView a WHERE a.ViewStatusCode = 'DELETED';


-- Delete View Fvourites that no longer exist.
DELETE FROM PersonFavourite WHERE ContentTypeCode = 'REPORTVIEW' AND ContentId NOT IN (SELECT ViewId FROM ReportView)