-- REFN-MRIN-RM10.sql
/*
2016-01-16 Tom Holden ve3meo
rev.2024-08-06 modified for RM8-RM10

Globally adds/replaces Reference Number fact that displays the MRIN(s)
for a person. Format is Rrin mrinM mrinM ..., e.g., R1296 458M 523M
where the record number of the person is 1296 and he or she was in two
family (couple) relationships with MRINs 458 and 523. A person without 
child or spouse is assigned 0M.

Creates two temporary tables which are dropped when the SQLite manager
closes the database.

Requires the REGEXP and GROUP_CONCAT() functions which are available
in SQLiteSpy 1.9.10.
  
*/

BEGIN
;

DROP TABLE IF EXISTS xMRINbyPerson
;
/* 
Consolidate FatherID and MotherID under PersonID for each 
FamilyID (MRIN) in FamilyTable
*/
CREATE TEMP TABLE xMRINbyPerson
AS
SELECT FatherID AS PersonID, FamilyID AS MRIN FROM FamilyTable
UNION
SELECT MotherID, FamilyID FROM FamilyTable
;

-- add persons with no spouse and assign MRIN=0
INSERT INTO xMRINbyPerson
SELECT PersonID, 0 FROM PersonTable 
  WHERE PersonID NOT IN
  (SELECT PersonID FROM xMRINbyPerson)
; 

DROP TABLE IF EXISTS xREFN_MRINS
;
-- Intermediate table to hold the Details value for each Reference No fact
CREATE TEMP TABLE xREFN_MRINS (PersonID INTEGER Primary Key, REFN TEXT)
;

INSERT INTO xREFN_MRINS
SELECT PersonID
     , 'R' || PersonID || REPLACE(group_concat(' '||MRIN||'M'),',','') 
     AS REFN 
FROM xMRINbyPerson
GROUP BY PersonID 
;

-- Delete prior REFN facts in this format 
DELETE FROM EventTable
WHERE EventType = 35  -- FactTypeID for Reference No. fact
 AND Details REGEXP 'R[0-9]+ [0-9]+M.*' -- Details must match this pattern
;

INSERT OR ABORT INTO EventTable
--RM7: SELECT NULL,35,0,PersonID,0,0,0,'.',5630062501345361932,1,1,0,0,JULIANDAY('now', 'localtime') - 2415018.5,'<>',REFN,''
--RM10:
SELECT NULL,35,0,PersonID,0,0,0,'.',5630062501345361932,1,1,0,0,'<>',REFN,'',JULIANDAY('now', 'localtime') - 2415018.5
FROM xREFN_MRINS
;
COMMIT
;

-- show results in EventTable
SELECT OwnerID AS RIN, Details AS REFN FROM EventTable
WHERE EventType = 35
AND Details REGEXP 'R[0-9]+ [0-9]+M.*'
;

-- End of Script