-- SourceNames-AppendSurnames-RM9.sql
/* 2023-03-21 Tom Holden ve3meo

Appends to the Source Name the unique surnames of all persons in the database who
have a citation or 'use' of a Master Source in their profile. The list of surnames 
is enclosed in drawing symbols:╣surnamelist╠ acting as bookends. The resulting
extended name is truncated at 256 characters, the maximum RM9 supports in a
drag'n'drop transfer; the value is easily changed in four places in the same
statement.

The script execution creates a series of temporary Views (in-memory queries) to build 
the final View "TruncNewName" from which the SourceTable is updated with the 
╣surnamelist╠. At the start of the script after REINDEXing against the fake RMNOCASE,
the SourceTable is updated with Names stripped of previous ╣surnamelist╠.  

Requires a SQLite manager that has a "fake RMNOCASE collation" and supports
REGEXP_REPLACE(). Script was developed and tested with SQLiteSpy 1.9.16 64-bit 
and  the fake RMNOCASE extension from 
https://sqlitetoolsforrootsmagic.com/rmnocase-faking-it-in-sqlitespy/  
*/

REINDEX; -- Must Rebuild Indexes in RM on return

------REMOVE APPENDED SURNAMES-------
/* 
Sort of an UNDO by deleting the [surnames] but some
original Source Names that already exceeded the limit
of 150 have been irreversibly truncated as will any that already
had the " ╣" pattern of characters.
*/

DROP VIEW IF EXISTS RemoveSurnames
;

-- Make a View that has the current Source Name and 
-- the draft stripped of ╣surnameslist╠

CREATE TEMP VIEW RemoveSurnames AS
SELECT
  SourceID
  , Name
--  , TRIM(SUBSTR(S.Name,1,INSTR(S.Name,' [')-1)) AS NewName
  ,REGEXP_REPLACE(S.Name,'╣[^\╠]*╠','') AS NewName
FROM SourceTable S
WHERE Name LIKE '% ╣%'  -- ╣╠ bookends
;

-- APPLY THE STRIPPED Source Name to the database 
UPDATE SourceTable
SET Name= (SELECT NewName FROM RemoveSurnames RS WHERE SourceTable.SourceID=RS.SourceID)
WHERE SourceID IN (SELECT SourceID FROM RemoveSurnames)
;


-- SURNAMES BY SOURCE  -----------------------------
DROP VIEW IF EXISTS SurnamesBySource
;

CREATE TEMP VIEW SurnamesBySource AS

-- Persons (General)
SELECT DISTINCT
  S.SourceID
  ,S.Name
  ,N.Surname
  ,C.CitationName
FROM SourceTable S
JOIN CitationTable C USING(SourceID)
JOIN CitationLinkTable CL USING (CitationID)
JOIN NameTable N ON CL.OwnerID=N.OwnerID AND CL.OwnerType=0 AND N.IsPrimary --Persons
WHERE N.Surname IS NOT Null

UNION

-- Individual Events
SELECT DISTINCT
  S.SourceID
  ,S.Name
  ,N.Surname
  ,C.CitationName
FROM SourceTable S
JOIN CitationTable C USING(SourceID)
JOIN CitationLinkTable CL USING (CitationID)
JOIN EventTable E ON CL.OwnerID=E.EventID AND CL.OwnerType=2
JOIN NameTable N ON E.OwnerID=N.OwnerID AND E.OwnerType=0 AND N.IsPrimary --Indiv Events
WHERE N.Surname IS NOT Null

UNION

-- Couples (general
-- Husband
SELECT DISTINCT
  S.SourceID
  ,S.Name
  ,N1H.Surname
  ,C.CitationName
FROM SourceTable S
JOIN CitationTable C USING(SourceID)
JOIN CitationLinkTable CL USING (CitationID)
JOIN FamilyTable F ON CL.OwnerID=F.FamilyID AND CL.OwnerType=1
LEFT JOIN NameTable N1H ON F.FatherID=N1H.OwnerID AND N1H.IsPrimary --Couple Husband
WHERE N1H.Surname NOT LIKE ''

UNION

-- Wife
SELECT DISTINCT
  S.SourceID
  ,S.Name
  ,N1W.Surname
  ,C.CitationName
FROM SourceTable S
JOIN CitationTable C USING(SourceID)
JOIN CitationLinkTable CL USING (CitationID)
JOIN FamilyTable F ON CL.OwnerID=F.FamilyID AND CL.OwnerType=1
JOIN NameTable N1W ON F.MotherID=N1W.OwnerID AND N1W.IsPrimary --Couple Wife
WHERE N1W.Surname NOT LIKE ''

UNION

-- sources for couple events
-- Husband
SELECT DISTINCT
  S.SourceID
  ,S.Name
  ,N1H.Surname
  ,C.CitationName
FROM SourceTable S
JOIN CitationTable C USING(SourceID)
JOIN CitationLinkTable CL USING (CitationID)
JOIN EventTable E1 ON CL.OwnerID=E1.EventID AND CL.OwnerType=2 --Couple Events
JOIN FamilyTable F ON E1.OwnerID=F.FamilyID AND E1.OwnerType=1
LEFT JOIN NameTable N1H ON F.FatherID=N1H.OwnerID AND N1H.IsPrimary --Couple Events Husband
WHERE N1H.Surname NOT LIKE ''

UNION

-- Wife
SELECT DISTINCT
  S.SourceID
  ,S.Name
  ,N1W.Surname
  ,C.CitationName
FROM SourceTable S
JOIN CitationTable C USING(SourceID)
JOIN CitationLinkTable CL USING (CitationID)
JOIN EventTable E1 ON CL.OwnerID=E1.EventID AND CL.OwnerType=2 --Couple Events
JOIN FamilyTable F ON E1.OwnerID=F.FamilyID AND E1.OwnerType=1
LEFT JOIN NameTable N1W ON F.MotherID=N1W.OwnerID AND N1W.IsPrimary --Couple Events Wife
WHERE N1W.Surname NOT LIKE ''

UNION

-- Name citations
SELECT DISTINCT
  S.SourceID
  ,S.Name
  ,N.Surname
  ,C.CitationName
FROM SourceTable S
JOIN CitationTable C USING(SourceID)
JOIN CitationLinkTable CL USING (CitationID)
JOIN NameTable N ON CL.OwnerID=N.NameID AND CL.OwnerType=7 -- AND N.IsPrimary --Names
WHERE N.Surname IS NOT Null

UNION

-- Association citations OwnerType=19
-- Person 1
SELECT DISTINCT
  S.SourceID
  ,S.Name
  ,N.Surname
  ,C.CitationName
FROM SourceTable S
JOIN CitationTable C USING(SourceID)
JOIN CitationLinkTable CL USING (CitationID)
JOIN FANTable FAN ON CL.OwnerID=FAN.FanID AND CL.OwnerType=19 --Association Events
JOIN NameTable N ON FAN.ID1=N.OwnerID AND N.IsPrimary -- FAN 1st person
WHERE N.Surname NOT LIKE ''

UNION

-- Person 2
SELECT DISTINCT
  S.SourceID
  ,S.Name
  ,N.Surname
  ,C.CitationName
FROM SourceTable S
JOIN CitationTable C USING(SourceID)
JOIN CitationLinkTable CL USING (CitationID)
JOIN FANTable FAN ON CL.OwnerID=FAN.FanID AND CL.OwnerType=19 --Association Events
JOIN NameTable N ON FAN.ID2=N.OwnerID AND N.IsPrimary -- FAN 2nd person
WHERE N.Surname NOT LIKE ''
;

-- Count number of uses of source
DROP VIEW IF EXISTS SourceUses
;

CREATE TEMP VIEW SourceUses AS
SELECT
  SourceID
  , Name
  , Surname
  , CitationName
  , COUNT(SourceID) AS Uses
FROM SurnamesBySource
GROUP BY SourceID
;

-- Draft new Source name
DROP VIEW IF EXISTS NewName
;
CREATE TEMP VIEW NewName AS
SELECT SU.SourceID, SU.Name, SU.Name||' ╣'||GROUP_CONCAT(SBS.Surname)||'╠' AS NewSourceName
FROM SourceUses SU
JOIN SurnamesBySource SBS USING(SourceID)
WHERE SU.Uses>0 -- all uses
--AND NOT INSTR(SBS.Name,SBS.Surname) -- skip if Surname is already present in Source Name
AND SBS.Surname NOT LIKE ''
GROUP BY SBS.SourceID
; 

-- Trim new Source name to 256 chars, max carried via drag'n'drop
DROP VIEW IF EXISTS TruncNewName
;
CREATE TEMP VIEW TruncNewName AS
SELECT
  SourceID
  ,SUBSTR(NewSourceName,1,256-2)
   || CASE
        WHEN LENGTH(NewSourceName)>256-2 
        AND INSTR(NewSourceName,'╣')<(256-2)
        AND INSTR(NewSourceName,'╠')>(256) 
        THEN '…╠' ELSE '' END --bookend the surnames list
   AS TruncSourceName
  ,NewSourceName
FROM NewName
;

/*
Highlight the statements between the Comments symbols
and exceute only them.

-- INSPECT
SELECT * FROM TruncNewName
;
*/

----- APPLY CHANGES TO DATABASE-----
UPDATE SourceTable
SET Name =
  (SELECT TruncSourceName 
   FROM TruncNewName
   WHERE SourceTable.SourceID
        =TruncNewName.SourceID
        )
WHERE SourceID IN (SELECT SourceID FROM TruncNewName)
;
-------------------------------------

SELECT
  CHANGES()||' Source Names updated. 
Completed without SQLite error. 
REBUILD INDEXES on return to RM9!'
  AS Status
;

----------END OF SCRIPT-----------------------