---- Fix Rogue Cases and Delete any remaining Orphaned Records
USE fogbugz;

-- Fix as many bad ixBugEventLatest records as we can
UPDATE Bug SET Bug.ixBugEventLatest = BugEventData.ixBugEvent
FROM (SELECT ixBug, MAX(ixBugEvent) AS ixBugEvent FROM BugEvent GROUP BY ixBug) AS BugEventData
WHERE Bug.ixBug = BugEventData.ixBug AND
      (Bug.ixBugEventLatest is NULL OR Bug.ixBugEventLatest <>  BugEventData.ixBugEvent);

-- Delete any records related to orphaned records (bug records without events)
DELETE BugView
FROM BugView INNER JOIN Bug
ON BugView.ixBug = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE BugRelation
FROM BugRelation INNER JOIN Bug
ON BugRelation.ixBugTo = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE BugRelation
FROM BugRelation INNER JOIN Bug
ON BugRelation.ixBugFrom = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE Scout
FROM Scout INNER JOIN Bug
ON Scout.ixBug = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE TagAssociation
FROM TagAssociation INNER JOIN Bug
ON TagAssociation.ixBug = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE Duplicates
FROM Duplicates INNER JOIN Bug
ON Duplicates.ixBugDupe = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE Duplicates
FROM Duplicates INNER JOIN Bug
ON Duplicates.ixBugDupeOf = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE TitleWord
FROM TitleWord INNER JOIN Bug
ON TitleWord.ixBug = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE CVS
FROM CVS INNER JOIN Bug
ON CVS.ixBug = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE TimeInterval
FROM TimeInterval INNER JOIN Bug
ON TimeInterval.ixBug = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE Subscriptions
FROM Subscriptions INNER JOIN Bug
ON Subscriptions.ixBug = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

DELETE BugEvent
FROM BugEvent INNER JOIN Bug
ON BugEvent.ixBug = Bug.ixBug
WHERE Bug.ixBugEventLatest IS NULL;

-- Delete any remaining orphaned records (bug records without events)
DELETE FROM Bug WHERE ixBugEventLatest IS NULL;


-- Set all rogue case priorities to default priority
UPDATE Bug SET ixPriority = (SELECT ixPriority from Priority WHERE fDefault = 1) WHERE ixPriority = 0;

-- Set the default Active status (per category) for rogue cases with an invalid status
UPDATE Bug SET Bug.ixStatus = Category.ixStatusDefaultActive FROM (Bug JOIN Category ON Bug.ixCategory = Category.ixCategory) WHERE Bug.ixStatus = 0;

-- Set an active, global milestone for all rogue cases without a valid milestone
UPDATE Bug SET ixFixFor = (SELECT TOP(1) ixFixFor FROM FixFor WHERE fDeleted = 0 AND fInactive = 0 AND ixProject = -1) WHERE ixFixFor = 0;

-- Set an open project for all rogue cases without a valid project
UPDATE Bug SET ixProject = (SELECT TOP(1) ixProject FROM Project WHERE fDeleted = 0) WHERE ixProject = 0;

-- Set an area in the case's project for all rogue cases without a valid area
UPDATE Bug SET Bug.ixArea = Area.ixArea FROM (Bug JOIN Area ON Bug.ixProject = Area.ixProject) WHERE Bug.ixArea = 0 AND Area.fDeleted=0;

-- Set any invalid person fields in rogue cases to an active administrator 
UPDATE Bug SET ixPersonOpenedBy = (SELECT TOP(1) ixPerson FROM Person WHERE fDeleted = 0 AND fAdministrator = 1) WHERE ixPersonOpenedBy <= 0;
UPDATE Bug SET ixPersonAssignedTo = (SELECT TOP(1) ixPerson FROM Person WHERE fDeleted = 0 AND fAdministrator = 1) WHERE ixPersonAssignedTo = 0;

---- END SQL