-- TMG-RM_FalseSpousalEventSentences.sql
/*
2014-09-13 Tom Holden ve3meo
2014-09-24 added EventTable to include the Custom override for the Principal; 
           added Name and Where to help locate the sentence to be edited within RootsMagic.

TMG has dual Principal, non-spousal events that RM does not support.
Direct import converts these events to Individual type and variables to
Spousal type, i.e., [Spouse], [Husband], [Wife], which are invalid for 
a RM Indiv event. The name of the variable is outputted instead of nothing. 

This query finds occurrences of these spousal name variables in the Sentence 
fields of the FactTypeTable, EventTable, RoleTable and WitnessTable.   
*/

SELECT * FROM
(
-------------------FactTypeTable
SELECT CAST(Sentence AS TEXT) AS "Sentence", 'Sentence' AS "Field", 'FactTypeTable' AS "Table", ROWID, Name, 'Fact Type' AS "Where" FROM FactTypeTable
WHERE FactTypeID > 999    -- custom ones only
AND OwnerType = 0         -- Individual FactTypes
UNION ALL
-------------------EventTable
SELECT CAST(E.Sentence AS TEXT), 'Sentence', 'EventTable', E.ROWID, FT.Name, Surname || ' ' || Suffix || ', ' || Prefix || ' ' || Given || '-' || N.OwnerID FROM EventTable E
INNER JOIN FactTypeTable FT ON EventType = FactTypeID
AND FT.OwnerType = 0
INNER JOIN NameTable N USING(OwnerID) 
WHERE N.IsPrimary
UNION ALL
-------------------RoleTable
SELECT CAST(R.Sentence AS TEXT), 'Sentence', 'RoleTable', R.ROWID, RoleName, FT.Name || ' (Fact Type - Role)' FROM RoleTable R
INNER JOIN FactTypeTable FT ON EventType = FactTypeID
WHERE RoleID > 58    -- custom ones only
AND FT.OwnerType = 0
UNION ALL
-------------------WitnessTable
SELECT CAST(W.Sentence AS TEXT), 'Sentence', 'WitnessTable', W.ROWID, FT.Name || ' (Witness)', N.Surname || ' ' || N.Suffix || ', ' || N.Prefix || ' ' || N.Given || '-' || N.OwnerID  FROM WitnessTable W
INNER JOIN EventTable E USING(EventID)
INNER JOIN FactTypeTable FT ON EventType = FactTypeID
INNER JOIN NameTable N ON W.PersonID = N.OwnerID
WHERE N.IsPrimary
AND E.OwnerType = 0    -- Individual events
)
WHERE 
LOWER(Sentence) LIKE '%[husband]%'
OR
LOWER(Sentence) LIKE '%[wife]%'
OR
LOWER(Sentence) LIKE '%[spouse]%'