-- Events-FMNO.sql
-- 2013-10-15 Tom Holden ve3meo
-- 2013-10-16 changed from MRIN to FMNO
/*
Exposes otherwise invisible FamilyID (except optionally in title of FGS)
by adding an event to each spouse containing the FamilyID.
The event added is a custom Family event named *FMNO, abbreviated FMNO which is 
first created by the script if one having the same abbreviation does not exist.
*/
-- Add a FMNO fact type if none exists
INSERT
	OR IGNORE
INTO FactTypeTable(OwnerType, NAME, Abbrev, GedcomTag, UseValue, UseDate, UsePlace, Sentence, Flags)
SELECT 1
	,'*FMNO'
	,'FMNO'
	,'EVEN'
	,1
	,0
	,0
	,CAST('[Desc].' AS BLOB)
	,- 1
WHERE (
		SELECT FactTypeID
		FROM FactTypeTable
		WHERE ABBREV LIKE 'FMNO'
			AND OwnerType = 1
		) ISNULL;

-- Delete all FMNO events  N.B. RM will delete all events for a FactType that you delete through it.
-- DELETE FROM EventTable WHERE EventType IN (SELECT FactTypeID FROM FactTypeTable WHERE ABBREV LIKE 'FMNO' AND OwnerType=1); 
-- Add FMNO event to each couple without such an event, description to contain FamilyID (invisible FMNO)
INSERT
	OR

ROLLBACK
INTO EventTable(EventType, OwnerType, OwnerID, FamilyID, PlaceID, SiteID, DATE, SortDate, IsPrimary, IsPrivate, Proof, STATUS, EditDate, Sentence, Details, Note)

SELECT (
		SELECT FactTypeID
		FROM FactTypeTable
		WHERE ABBREV LIKE 'FMNO'
			AND OwnerType = 1
		) AS EventType
	,1 AS OwnerType -- a Family event is type 1
	,FamilyID AS OwnerID
	,0 AS FamilyID
	,0 AS PlaceID
	,0 AS SiteID
	,'.' AS DATE
	,1 AS SortDate -- places event at or near top of list in Edit Person screen
	,0 AS IsPrimary
	,1 AS IsPrivate -- presumably having this event set as private will provide needed output control
	,0 AS Proof
	,0 AS STATUS
	,(
		SELECT JULIANDAY('now', 'localtime') - 2415018.5
		) AS EditDate -- does not affect Date last edited in People view
	,NULL AS Sentence
	,'FMNO ' || FamilyID AS Details -- having the prefix "FMNO" helps to stand out in tabular reports
	,NULL AS Note
FROM FamilyTable
WHERE FamilyID -- don't add an FMNO event to those couples already having one
	NOT IN (
		SELECT DISTINCT OwnerID -- list of all FamilyID's already having the FMNO event 
		FROM EventTable
		WHERE OwnerType = 1
			AND EventType IN -- in case there is more than one FMNO family fact type
			(
				SELECT FactTypeID
				FROM FactTypeTable
				WHERE ABBREV LIKE 'FMNO'
					AND OwnerType = 1
				)
		);