-- Name-Married_in_DeathSentence.sql
/* 2017-02-25 Tom Holden ve3meo

Creates a custom local sentence for the Death, Burial and Cremation events
for a wife from the default sentence templates for those fact types, 
replacing the maiden surname with that of the last undivorced husband.

Requires the Marriage event.
*/

DROP VIEW IF EXISTS vMarriedSurnames
;
CREATE TEMP VIEW vMarriedSurnames AS
SELECT Fm.MotherID, E.EventType, Fm.FatherID, N.Surname COLLATE NOCASE AS Husband , E.Date, E.SortDate FROM FamilyTable Fm
JOIN EventTable E 
ON Fm.FamilyID = E.OwnerID
AND E.EventType BETWEEN 300 AND 302
AND Fm.MotherID <> 0
AND Fm.FatherID <> 0
JOIN NameTable N
ON Fm.FatherID = N.OwnerID
AND N.IsPrimary
ORDER BY Fm.MotherID, E.SortDate  -- grouping will report the largest SortDate if it is in ascending order
;

DROP VIEW IF EXISTS vLatestMarriageStatus
;
CREATE TEMP VIEW vLatestMarriageStatus AS
SELECT * FROM vMarriedSurnames
GROUP BY MotherID
;

DROP VIEW IF EXISTS vNameAtDeath
;
CREATE TEMP VIEW vNameAtDeath AS
SELECT E.EventID, E.EventType, vLMS.MotherID, vLMS.Husband FROM EventTable E
JOIN vLatestMarriageStatus vLMS
ON E.OwnerID = vLMS.MotherID
AND E.EventType IN (2,4,5) -- Death, Burial, Cremation
AND vLMS.EventType = 300 -- wasn't annulled or divorced 
ORDER BY E.EventID
;

DROP VIEW IF EXISTS vDeathSentence
;
CREATE TEMP VIEW vDeathSentence AS
SELECT vNAD.*, FT.Sentence, REPLACE(FT.Sentence, '[person]', '[person:given] ' || vNAD.Husband) AS NewSentence
FROM vNameAtDeath vNAD
JOIN FactTypeTable FT
ON vNAD.EventType = FT.FactTypeID
;

UPDATE OR REPLACE EventTable
SET Sentence = (SELECT NewSentence FROM vDeathSentence vDS WHERE EventTable.EventID = vDS.EventID)
WHERE EventID IN (SELECT EventID FROM vDeathSentence)
;