-- DeletePhantoms2.sql
/*
2012-10-27 Tom Holden ve3meo
2012-11-13 added query to correct PersonTable.SpouseID to 0 for those whose family (spouse) 
was deleted.
2013-01-21 v2 removed scripts that delete unused Media, Places...

Cleans database of unused records in many tables,
some of which may give rise to phantoms in displays and reports.
These arise from incomplete cleanup by RootsMagic when persons, families, places, sources 
are deleted directly or by merging and, most certainly, by simple SQLite deletions
such as DeleteByColorCode.

There is an as-yet-unaddressed problem with shared events arising from the deletion of the Principal;
the event is deleted so there is nothing to share. It would be desirable to convert shared events to 
unshared singular events for every sharee.

*/
-- Set SpouseID=0 for persons with invalid FamilyID (TAH 2012-11-13)
UPDATE PersonTable
SET SpouseID = 0
WHERE SpouseID NOT IN (
		SELECT FamilyID
		FROM FamilyTable
		);

-- Delete Child from ChildTable if Child does not exist in PersonTable
DELETE
FROM ChildTable
WHERE ChildID NOT IN (
		SELECT PersonID
		FROM PersonTable
		);

-- Delete Child from ChildTable if FamilyID gone from FamilyTable
DELETE
FROM ChildTable
WHERE FamilyID NOT IN (
		SELECT FamilyID
		FROM FamilyTable
		);

-- Delete Names from NameTable if OwnerID is not a PersonID in PersonTable
DELETE
FROM NameTable
WHERE OwnerID NOT IN (
		SELECT PersonID
		FROM PersonTable
		);

-- Delete Individual's Events from EventTable if OwnerID not in PersonTable
DELETE
FROM EventTable
WHERE OwnerType = 0
	AND OwnerID NOT IN (
		SELECT PersonID
		FROM PersonTable
		);

-- Delete Family Events from EventTable if OwnerID not in FamilyTable
DELETE
FROM EventTable
WHERE OwnerType = 1
	AND OwnerID NOT IN (
		SELECT FamilyID
		FROM FamilyTable
		);

-- Clean out Citations that no do not link to an active record 
-- Personal
DELETE
FROM CitationTable
WHERE OwnerType = 0
	AND OwnerID NOT IN (
		SELECT PersonID
		FROM PersonTable
		);

-- Family
DELETE
FROM CitationTable
WHERE OwnerType = 1
	AND OwnerID NOT IN (
		SELECT FamilyID
		FROM FamilyTable
		);

-- Event citations
DELETE
FROM CitationTable
WHERE OwnerType = 2
	AND OwnerID NOT IN (
		SELECT EventID
		FROM EventTable
		);

-- Alternate Name citatoins
DELETE
FROM CitationTable
WHERE OwnerType = 7
	AND OwnerID NOT IN (
		SELECT NameID
		FROM NameTable
		WHERE NOT IsPrimary
		);

-- Delete Citations having lost their Source
-- DeleteUnsourcedCitations.sql
-- 2010-01-29 ve3meo
DELETE
FROM CitationTable
WHERE CitationID IN (
		SELECT CitationID
		FROM CitationTable c
		LEFT JOIN SourceTable s ON c.SourceID = s.SourceID
		WHERE s.SourceID ISNULL
		);

-- Clean up addresses
-- Addresses that are linked to non-existant persons or families
DELETE
FROM AddressTable
WHERE AddressID NOT IN (
		-- Addresses that are linked to existing persons
		SELECT AddressID
		FROM AddressLinkTable
		WHERE OwnerType = 0
			AND OwnerID IN (
				SELECT PersonID
				FROM PersonTable
				)
		
		UNION
		
		-- Addresses that are linked to existing families
		SELECT AddressID
		FROM AddressLinkTable
		WHERE OwnerType = 1
			AND OwnerID IN (
				SELECT FamilyID
				FROM FamilyTable
				)
		)
	AND AddressType = 0;

-- Remove orphaned records from AddressLinkTable
-- Links to non-existing persons
DELETE
FROM AddressLinkTable
WHERE OwnerType = 0
	AND OwnerID NOT IN (
		SELECT PersonID
		FROM PersonTable
		);

-- Links to non-existing families
DELETE
FROM AddressLinkTable
WHERE OwnerType = 1
	AND OwnerID NOT IN (
		SELECT FamilyID
		FROM FamilyTable
		);

-- Links to non-existing addresses
DELETE
FROM AddressLinkTable
WHERE AddressID NOT IN (
		SELECT AddressID
		FROM AddressTable
		);

-- DELETE Orphaned MediaLinks
-- Having a non-existant MediaID
DELETE
FROM MediaLinkTable
WHERE MediaID NOT IN (
		SELECT MediaID
		FROM MultiMediaTable
		);

-- Having a link to non-exsiting
-- Person media
DELETE
FROM MediaLinkTable
WHERE OwnerType = 0
	AND OwnerID NOT IN (
		SELECT PersonID
		FROM PersonTable
		);

-- Family media
DELETE
FROM MediaLinkTable
WHERE OwnerType = 1
	AND OwnerID NOT IN (
		SELECT FamilyID
		FROM FamilyTable
		);

-- Event Media
DELETE
FROM MediaLinkTable
WHERE OwnerType = 2
	AND OwnerID NOT IN (
		SELECT EventID
		FROM EventTable
		);

-- Master Source Media
DELETE
FROM MediaLinkTable
WHERE OwnerType = 3
	AND OwnerID NOT IN (
		SELECT SourceID
		FROM SourceTable
		);

-- Citation Media
DELETE
FROM MediaLinkTable
WHERE OwnerType = 4
	AND OwnerID NOT IN (
		SELECT CitationID
		FROM CitationTable
		);

-- Place Media
DELETE
FROM MediaLinkTable
WHERE OwnerType = 5
	AND OwnerID NOT IN (
		SELECT PlaceID
		FROM PlaceTable
		);

-- Delete orphaned Tasks
-- individuals
DELETE
FROM ResearchTable
WHERE OwnerType = 0
	AND OwnerID NOT IN (
		SELECT OwnerID
		FROM PersonTable
		);

-- families
DELETE
FROM ResearchTable
WHERE OwnerType = 1
	AND OwnerID NOT IN (
		SELECT FamilyID
		FROM FamilyTable
		);

-- Delete Headless Witnesses
DELETE
FROM WitnessTable
WHERE WitnessTable.PersonID > 0
	AND WitnessTable.PersonID NOT IN (
		SELECT PersonID
		FROM PersonTable
		);