-- REFN-RematchFSID.sql

/*
Creates entries in LinkTable corresponding to the FSID stored in the REFN, 
a process complementary to REFN_CopyFSID.sql that may be useful when
the database has passed through another system without FamilySearch Family Tree
support thus losing the matches. RM's AutoMatch may not recover all matches.
This script forces a match based on the FamilySearchID value that existed
when REFN_CopyFSID captured it. Of course, it is possible that the FSID no longer
exists due to changes in FamilySearch Family Tree.

Even if a match is restored by this script, FamilySearch Central will report that 
there is new information.
*/

DROP VIEW IF EXISTS FSID
;
CREATE TEMP VIEW FSID AS
SELECT OwnerID AS rmID, REPLACE(Details, 'fsid: ','') AS FSID
FROM EventTable
WHERE EventType = 35
AND Details LIKE 'fsid: %'
ORDER BY rmID
;

DROP VIEW IF EXISTS FSIDmatches
;
CREATE TEMP VIEW FSIDmatches AS
SELECT rmID, extID
FROM LinkTable 
WHERE extSystem = 1
AND LinkType = 0
ORDER BY rmID
;

DROP VIEW IF EXISTS FSIDrematches
;
CREATE TEMP VIEW FSIDrematches AS
SELECT rmID FROM FSID
EXCEPT SELECT rmID FROM FSIDmatches
;

--DELETE FROM LinkTable WHERE LinkID=2

BEGIN
;
INSERT OR REPLACE INTO LinkTable
SELECT 
  NULL AS LinkID
  , 1 AS extSystem
  , 0 AS LinkType
  , rmID
  , [FSID] AS extID
  , 1 AS Modified
  , '' AS extVersion
  , 0.0 AS extDate
  , 0 AS Status
  , '' AS Note
FROM FSIDrematches 
NATURAL JOIN FSID
;
COMMIT
;