/* Paragraph-add.sql
   2011-12-22 ve3meo
   Adds trailing double CR/LF to Notes of Family and Individual Events.
   Removes last pair of CR/LF from the last Person Fact or Name Note unless 
   followed by a Person General note.
   Does not yet work on Family notes. 
*/

UPDATE EventTable SET Note = Note || CAST (X'0D0A0D0A' AS TEXT) WHERE LENGTH (Note) > 0
;
--UPDATE PersonTable SET Note = Note || CAST (X'0D0A0D0A' AS TEXT) WHERE LENGTH (Note) > 0
;
UPDATE FamilyTable SET Note = Note || CAST (X'0D0A0D0A' AS TEXT) WHERE LENGTH (Note) > 0
;
UPDATE NameTable SET Note = Note || CAST (X'0D0A0D0A' AS TEXT) WHERE LENGTH (Note) > 0
;

DROP TABLE IF EXISTS LastNoteLog 
;

CREATE TEMP TABLE IF NOT EXISTS LastNoteLog
AS
-- Person general note exists
SELECT PersonID, Null AS NameID, Null AS EventID, Null AS SortDate, 'PG' AS Type FROM PersonTable WHERE Note NOT LIKE ''
;

INSERT INTO LastNoteLog
SELECT * -- Last Indiv Event ID
FROM
  (
   SELECT OwnerID AS PersonID, Null AS NameID, EventID, SortDate, 'PF' AS Type FROM EventTable WHERE OwnerType=0
   ORDER BY SortDate
   )
GROUP BY PersonID
; 

INSERT INTO LastNoteLog 
SELECT * -- Last Alt Name
FROM
  (
   SELECT OwnerID AS PersonID, NameID, Null AS EventID, SortDate, 'AN' AS Type FROM NameTable WHERE NOT +IsPrimary
   ORDER BY SortDate
   )
GROUP BY PersonID
; 

-- Delete trailing CR/LF from last Alt Name Note if there is NOT a Person general note AND no later Person Fact
UPDATE NameTable 
SET Note = SUBSTR (Note, 1, LENGTH(Note)-4) 
--SELECT * FROM NameTable
WHERE Note NOT LIKE '' 
AND NameID IN
  (
     SELECT NameID FROM LastNoteLog
    )
AND OwnerID 
NOT IN
   (
    SELECT PersonID FROM LastNoteLog WHERE Type LIKE 'PG'
    )
AND SortDate > (SELECT SortDate FROM LastNoteLog WHERE Type LIKE 'PF' AND OwnerID = PersonID)
;

-- Delete trailing CR/LF from last Person Fact Note if there is NOT a Person general note AND no later Alt Name Fact
UPDATE EventTable 
SET Note = SUBSTR (Note, 1, LENGTH(Note)-4) 
--SELECT * FROM EventTable
WHERE Note NOT LIKE '' 
AND EventID IN
  (
     SELECT EventID FROM LastNoteLog
    )
AND OwnerID 
NOT IN
   (
    SELECT PersonID FROM LastNoteLog WHERE Type LIKE 'PG'
    )
AND SortDate > (SELECT SortDate FROM LastNoteLog WHERE Type LIKE 'AN' AND OwnerID = PersonID)
;