--Notes-RemoveDupMerged-CTE.sql
/* 2023-02-15 Tom Holden ve3meo
    '23-02-17 TRIM added to OldNote comparison to ease comparison
              Renamed script from MergeDupeNotes-CTE.sql
    
From RM4 on, merging of persons preserved the Person (General) Note
value of the secondary person by concatenating it with that of the
primary person separated by the string '-- MERGED NOTE ------------'
and some whitespace. In some cases, both persons had the same Note
but RM concatenated anyway. And in some cases, there have been 
multiple merges with duplication of notes occurring in any pattern.

This script parses the Note into the parts between the flags, keeps
the distinctly different parts and concatenates them into a new Note
with a symbolic demarcation '-+-+-+-+-+-+-+-' that is less brazen
but still useful.

Uses Common Table Expression (CTE) and the REGEXP_REPLACE() function
and was tested on SQLiteSpy 1.9.15 Win32

Usage:
Close the database in RM with a backup.
Open the database in SQLiteSpy or equivalent. Load script and execute.
Inspect the NewNote against the OldNote.
If satisfied with the results, select the first UPDATE statement at the 
end of the script and execute it (Ctrl+F9 in Spy).
If you wish to revert to the original note, select the second UPDATE
statement at the end of the script before running the script again or
closing the  database out of sqlite.
The three temporary views created by the script won't empty until
the script is run a second time, reflecting that the temporary
PersonNoteOriginal has been emptied of those with the MERGED NOTE flag; 
they will all be dropped on closing the database from the SQLite
manager.
*/
-- Copy Notes with the -- MERGED NOTE ---------- flag to a temp table
DROP TABLE

IF EXISTS PersonNoteOriginal;
  CREATE TEMP TABLE PersonNoteOriginal AS

SELECT PersonID
  ,Note
FROM PersonTable
WHERE Note LIKE "%-- MERGED NOTE ------------%";

-- Parse these Notes containing the MERGED NOTE flag into parts.
DROP VIEW IF EXISTS zParseMergedNote;
  CREATE TEMP VIEW zParseMergedNote AS
    WITH x(PersonID, part, firstone, rest) AS (
        SELECT PersonID
          ,0 AS part
          ,substr(Note, 1, instr(Note, '-- MERGED NOTE ------------') - 1) AS firstone
          ,substr(Note, instr(Note, '-- MERGED NOTE ------------') + 27) AS rest
        FROM PersonNoteOriginal
        
        UNION ALL
        
        SELECT PersonID
          ,part + 1
          ,substr(rest, 1, instr(rest, '-- MERGED NOTE ------------') - 1) AS firstone
          ,substr(rest, instr(rest, '-- MERGED NOTE ------------') + 27) AS rest
        FROM x
        WHERE rest LIKE "%-- MERGED NOTE ------------%" --LIMIT 200
        )

SELECT PersonID
  ,part
  ,TRIM(firstone, CHAR(13) || CHAR(10) || ' ') AS NotePart
FROM x

UNION ALL

SELECT PersonID
  ,part + 1
  ,TRIM(rest, CHAR(13) || CHAR(10) || ' ')
FROM x
WHERE rest NOT LIKE "%-- MERGED NOTE ------------%"
ORDER BY PersonID
  ,part;

--Identify distinctly different parts ignoring whitespace, punctuation, symbols
DROP VIEW IF EXISTS zDistinctNoteParts;
  CREATE TEMP VIEW zDistinctNoteParts AS

SELECT PersonID
  ,part
  ,NotePart
FROM zParseMergedNote
GROUP BY PersonID
  ,REGEXP_REPLACE(NotePart, '[\W]', '')
-- group by purely alphanumeric differences between parts
ORDER BY PersonID
  ,part;

-- assemble the unduplicated parts into the new Note
DROP VIEW IF EXISTS zNewMergedNote;
  CREATE TEMP VIEW zNewMergedNote AS

SELECT PersonID
  ,GROUP_CONCAT(NotePart
  , CHAR(13) || CHAR(10) || CHAR(13) || CHAR(10)
    || '-+-+-+-+-+-+-+-' 
    || CHAR(13) || CHAR(10) || CHAR(13) || CHAR(10)) 
    AS NewNote
FROM zDistinctNoteParts
GROUP BY PersonID;

-- Inspect results
SELECT NULL AS PersonID
  ,'Compare DRAFT NewNote vs OldNote.' AS NewNote
  ,'Steps to commit are commented in script.' AS OldNote

UNION ALL

SELECT PersonID
  ,NewNote
  ,TRIM(Note) AS OldNote -- '23-02-17 TRIM added to facilitate comparison
FROM zNewMergedNote
JOIN PersonNoteOriginal USING (PersonID);
  --
  /* highlight and execute the following statement if happy with the construct of the NewNote
   DO SO ONLY ONCE IF YOU WISH TO REVERT WITHIN THIS SESSION
UPDATE PersonTable
SET Note = (SELECT NewNote FROM zNewMergedNote z WHERE PersonTable.PersonID=z.PersonID)
WHERE PersonID IN (SELECT PersonID FROM zNewMergedNote)
;
*/
  /*   if you want to revert to the OldNotes without having to restore your database, 
      do so before executing the script a second time and before closing the 
      database from sqlite. Select the statement below and execute it
UPDATE PersonTable
  SET Note=(SELECT Note FROM PersonNoteOriginal PNO WHERE PersonTable.PersonID=PNO.PersonID)
  WHERE PersonID IN (SELECT PersonID FROM PersonNoteOriginal)
;
*/
  --END--