-- Places-FirstNameExploit.sql
/*
2013-03-29 Tom Holden ve3meo
rev 2013-03-30 added [Place:plain] update to [Place:plain:first] for defaults only.
    needs regexp search & replace to deal with all combinations of Place modifiers

Exploits the Place:first option for Place names in narratives by
setting default sentences to Place:first and customising to Place 
only for the first event for a person in any place. 

This makes the narrative less wordy and repetitious of the higher levels 
in a Place name. However, it has no effect on the phrases used in the 
Children list at the bottom of a person's narrative which continue 
to use the full place name.
*/
-- Set default Fact sentences to use the first Place name.
UPDATE FactTypeTable
SET Sentence = REPLACE(Sentence, '[Place]', '[Place:first]');
UPDATE FactTypeTable
SET Sentence = REPLACE(Sentence, '[Place:plain]', '[Place:plain:first]');

-- set default role sentences to use the first Place name
UPDATE RoleTable
SET Sentence = REPLACE(Sentence, '[Place]', '[Place:first]');
UPDATE RoleTable
SET Sentence = REPLACE(Sentence, '[Place:plain]', '[Place:plain:first]');

-- create table of first event in a place for a person including shared events
DROP TABLE
IF EXISTS xFirstPlaceEvents;

CREATE TEMP TABLE
IF NOT EXISTS xFirstPlaceEvents AS
	SELECT *
	FROM (
		-- INDIV events
		SELECT EventID
			,0 AS isSharer
			,PlaceID
			,OwnerID
			,SortDate
		FROM EventTable
		WHERE OwnerType = 0
			AND PlaceID > 0
		
		UNION
		
		-- Husband events
		SELECT EventID
			,0 AS isSharer
			,PlaceID
			,FatherID AS OwnerID
			,SortDate
		FROM EventTable
		INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
			AND OwnerType = 1
			AND PlaceID > 0
		
		UNION
		
		-- wife events
		SELECT EventID
			,0 AS isSharer
			,PlaceID
			,MotherID AS OwnerID
			,SortDate
		FROM EventTable
		INNER JOIN FamilyTable ON EventTable.OwnerID = FamilyTable.FamilyID
			AND OwnerType = 1
			AND PlaceID > 0
		
		UNION
		
		-- shared events
		SELECT WitnessID
			,1 AS isSharer
			,EventTable.PlaceID AS PlaceID
			,WitnessTable.PersonID AS OwnerID
			,EventTable.SortDate AS SortDate
		FROM WitnessTable NATURAL
		INNER JOIN EventTable
		WHERE EventTable.PlaceID > 0
		ORDER BY OwnerID
			,SortDate DESC -- so next GROUP BY will pick up the smallest SortDate or first event in the group
		)
	GROUP BY OwnerID
		,PlaceID
	ORDER BY EventID; -- so the IN() expression in the following queries will see an ordered list

-- set all first events for a person to use the default sentence customised with the full Place
-- except those already with custom sentences 
UPDATE EventTable
SET Sentence = (
		SELECT REPLACE(FactTypeTable.Sentence, '[Place:first]', '[Place]')
		FROM EventTable Events
		INNER JOIN FactTypeTable ON Events.EventType = FactTypeID
		WHERE EventTable.EventID = Events.EventID
		)
WHERE EventID IN (
		-- EventIDs of first events having a PlaceID for all persons
		SELECT EventID
		FROM xFirstPlaceEvents
		WHERE isSharer = 0
		)
	AND EventTable.Sentence LIKE '' --change this to OR with a match to the default
	OR EventTable.Sentence LIKE (
		SELECT REPLACE(FactTypeTable.Sentence, '[Place:first]', '[Place]')
		FROM EventTable Events
		INNER JOIN FactTypeTable ON Events.EventType = FactTypeID
		WHERE EventTable.EventID = Events.EventID
		);

-- set all other custom sentences for first events to use the full name
UPDATE EventTable
SET Sentence = REPLACE(Sentence, '[Place:first]', '[Place]')
WHERE EventID IN (
		-- EventIDs of first events having a PlaceID for all persons
		SELECT EventID
		FROM xFirstPlaceEvents
		WHERE isSharer = 0
		);

-- set all other custom sentences for non-first events to use the first name
UPDATE EventTable
SET Sentence = REPLACE(Sentence, '[Place]', '[Place:first]')
WHERE EventID NOT IN (
		-- EventIDs of first events having a PlaceID for all persons
		SELECT EventID
		FROM xFirstPlaceEvents
		WHERE isSharer = 0
		);

--DO The Same steps for shared events
-- set all first witness for a person to use the default sentence customised with the full Place
-- except those already with custom sentences 
UPDATE WitnessTable
SET Sentence = (
		SELECT REPLACE(RoleTable.Sentence, '[Place:first]', '[Place]')
		FROM WitnessTable Witness
		INNER JOIN RoleTable ON Witness.ROLE = RoleTable.RoleID
		WHERE WitnessTable.WitnessID = Witness.WitnessID
		)
WHERE WitnessID IN (
		-- EventIDs of first events having a PlaceID for all persons
		SELECT EventID
		FROM xFirstPlaceEvents
		WHERE isSharer = 1
		)
	AND WitnessTable.Sentence LIKE '' --change this to OR with a match to the default
	OR WitnessTable.Sentence LIKE (
		SELECT REPLACE(RoleTable.Sentence, '[Place:first]', '[Place]')
		FROM WitnessTable Witness
		INNER JOIN RoleTable ON Witness.ROLE = RoleTable.RoleID
		WHERE WitnessTable.WitnessID = Witness.WitnessID
		);

-- set all other custom sentences for first witness events to use the full name
UPDATE WitnessTable
SET Sentence = REPLACE(Sentence, '[Place:first]', '[Place]')
WHERE WitnessID IN (
		-- EventIDs of first events having a PlaceID for all persons
		SELECT EventID
		FROM xFirstPlaceEvents
		WHERE isSharer = 1
		);

-- set all other custom sentences for non-first witness events to use the first name
UPDATE WitnessTable
SET Sentence = REPLACE(Sentence, '[Place]', '[Place:first]')
WHERE WitnessID NOT IN (
		-- EventIDs of first events having a PlaceID for all persons
		SELECT EventID
		FROM xFirstPlaceEvents
		WHERE isSharer = 1
		);