-- TMG-RM_convertTMG_IDtoREF#.sql
/*
2014-09-23 Tom Holden ve3meo

RootsMagic 6.3.2.0 imports the TMG_ID into a custom fact type TMG_ID.
This fact type cannot be displayed after the name of the person. Some users
would prefer that it did, either by having it as RIN or by a new feature 
specifically for TMG.

This procedure makes a clone of the TMG_ID as a Reference # fact which can 
optionally be displayed after the name. However, as multiple REF# facts  are
allowed, only the lowest record number in the EventTable is displayed. Therefore
the procedure also relocates pre-existing REF# facts to the end of the table. 

****N.B. Be sure to close and re-open the database with RM after running this procedure****
Otherwise, for some arcane reason, adding most any fact edits one of these new REF# facts.
Probably, some last record number register in the program has to be updated.


*/



DROP TABLE IF EXISTS xREFNUMs
;
-- Store existing REF# facts in a table for later relocation
CREATE TEMP TABLE xREFNUMs
AS
SELECT E.* FROM EventTable E
INNER JOIN FactTypeTable FT
ON E.EventType = FT.FactTypeID
WHERE FT.ABBREV LIKE 'Ref #'
AND FT.FactTypeID < 1000
;

DROP VIEW IF EXISTS xTMG_IDs
;
-- store existing TMG_ID facts in a view 
CREATE TEMP VIEW xTMG_IDs
AS
SELECT E.* FROM EventTable E
INNER JOIN FactTypeTable FT
ON E.EventType = FT.FactTypeID
WHERE FT.ABBREV LIKE 'TMG_ID'
AND FT.FactTypeID >= 1000
;

-- append REF# facts having TMG_ID value
INSERT INTO EventTable
SELECT 
  NULL AS EventID
  , (SELECT FactTypeID FROM FactTypeTable WHERE ABBREV LIKE 'Ref #') AS EventType
  , OwnerType
  , OwnerID
  , FamilyID
  , PlaceID 
  , SiteID 
  , Date 
  , SortDate 
  , IsPrimary 
  , IsPrivate 
  , Proof 
  , Status 
  , EditDate 
  , Sentence 
  , Details 
  , Note 
FROM 
xTMG_IDs
;

-- Move (copy/delete) preceding RefNums to bottom of table after the TMG_ID refnums
-- Why? because Display Ref # after name takes the lower EventID of multiples
-- Copy
INSERT INTO EventTable
SELECT 
  NULL AS EventID
  , EventType
  , OwnerType
  , OwnerID
  , FamilyID
  , PlaceID 
  , SiteID 
  , Date 
  , SortDate 
  , IsPrimary 
  , IsPrivate 
  , Proof 
  , Status 
  , EditDate 
  , Sentence 
  , Details 
  , Note 
FROM 
xREFNUMs
;

-- Delete the earlier ones
DELETE FROM EventTable
WHERE EventID 
IN (SELECT EventID FROM xREFNUMs)
;  

-- End of script