--REFN_CopyFSID-RM9.sql
/*
2023-11-22 Tom Holden ve3meo
Updated for RM9 (probably also for RM8)
from REFN_CopyFSID.sql 2016-09-05 Tom Holden ve3meo

Creates a Ref# fact containing the FamilySearch ID for each
person in the database matched to a person on FamilySearch Family Tree
in the format "fsid: XXXX-XXX".

This enables the Duplicate Search Merge (DSM) option 
"Find people with the same reference numbers (and ignore everything else)" 
to pair up people with the same FSID Ref#. 

Duplicate Search Merge  compares only the first 
Ref# fact for a person to the first Ref# fact for each other person. Thus it 
requires this FSID Ref# fact to be the lowest record number of all the Ref# facts
for a person. This script shuffles existing Ref# facts to follow the FSID Ref#
facts it creates by copying the former out to a temp fable xRefnBak, deleting 
the originals from the EventTable and then appending them after the FSID Ref# 
facts are created.

The script also preserves and pushes to the front existing FSID Ref# facts 
for a person not currently matched in the database to Family Search, i.e., 
the FSID has been somehow lost or the fact was manually added. This feature 
is of uncertain value. 

A temporary table xRefnBak is created by the script and is deleted when the SQLite 
manager closes the database.
*/

BEGIN
;
-- Delete prior FSID REFN facts for persons with FSID 
DELETE FROM EventTable
WHERE EventType = 35  -- FactTypeID for Reference No. fact
AND Details REGEXP 'fsid\: [A-Z,0-9]{4}-[A-Z,0-9]{3}' -- FSID pattern
AND OwnerID IN
 (SELECT DISTINCT rmID FROM FamilySearchTable WHERE LinkType=0) --persons having FSID
;

-- Backup other Ref# facts to temp table so they can be
-- added after the FSID Ref#
DROP TABLE IF EXISTS xRefnBak
;
CREATE TEMP TABLE xRefnBak
AS       -- first, the prior FSID Ref# facts for persons without FSID
SELECT * FROM EventTable 
WHERE EventType = 35     --Ref# FactTypeID
AND Details REGEXP 'fsid\: [A-Z,0-9]{4}-[A-Z,0-9]{3}' -- FSID pattern
;

-- Delete prior FSID REFN facts for persons without FSID 
DELETE FROM EventTable
WHERE EventType = 35  -- FactTypeID for Reference No. fact
AND Details REGEXP 'fsid\: [A-Z,0-9]{4}-[A-Z,0-9]{3}' -- FSID pattern
;

-- backup all other Ref# facts so they follow any FSID Ref# facts
INSERT INTO xRefnBak
SELECT * FROM EventTable -- then, prior FSID Ref# facts for persons without FSID
WHERE EventType = 35
; 

-- null out EventID so it will be reassigned on return to EventTable
UPDATE xRefnBak
SET EventID = NULL
;

-- Delete all prior REFN facts 
DELETE FROM EventTable
WHERE EventType = 35  -- FactTypeID for Reference No. fact
;

INSERT OR ABORT INTO EventTable
SELECT NULL,35,0,rmID,0,0,0,'.',5630062501345361932,1,1,0,0,'<>','fsid: ' || fsID,'',JULIANDAY('now', 'localtime') - 2415018.5
FROM FamilySearchTable
WHERE fsID REGEXP '[A-Z,0-9]{4}-[A-Z,0-9]{3}' -- FSID patternAND extSystem = 1  --presumably the flag for FamilySearch
AND LinkType = 0   --presumably the flag for an individual
AND rmID           --corresponds to PersonID or RIN
 IN (SELECT PersonID FROM PersonTable) -- an existing RIN, i.e., don't add phantoms
;

-- add back in the original Ref# facts
INSERT OR ROLLBACK INTO EventTable
SELECT * FROM xRefnBak
;

COMMIT
;

-- show results in EventTable
SELECT OwnerID AS RIN, Details AS REFN, * FROM EventTable
WHERE EventType = 35
AND Details REGEXP '[A-Z,0-9]{4}-[A-Z,0-9]{3}' -- FSID pattern
;

-- End of Script