-- Reports-PointFormNarrativeSetup2.sql
/*
2016-01-30 Tom Holden ve3meo
2016-04-05 eliminated Heading fact in favour of having [person]:]<CR> leading the Birth sentence

Backs up key tables or parts thereof to 'x' versions.
User control over sentence heading format and lead-in carriage returns, 
if using a SQLite manager which supports run-time variables, else 
defaults to bold and no <CR>s. 
Changes default sentences to point form without [person] variables and with
facttype name as heading.
-- removed: Adds _Heading event to provide the initial [person] sentence as the first 'fact' so
the report will start with the person's name and not be stated as 'factless'.
*/

BEGIN
;

-- create a temp table to store formatting parameters
DROP TABLE IF EXISTS xFormat
;
CREATE TEMP TABLE xFormat
AS 
SELECT
  CASE LOWER($FactNameFormat_ibun)
       WHEN 'n' THEN ''
       WHEN 'i' THEN '<i>'
       WHEN 'u' THEN '<u>'
       ELSE '<b>'       
  END AS Format1  -- start format, e.g., italics <i>
  ,CASE LOWER($FactNameFormat_ibun)
       WHEN 'n' THEN ''
       WHEN 'i' THEN '</i>'
       WHEN 'u' THEN '</u>'
       ELSE '</b>'       
  END AS Format0  -- end format, e.g., italics </i>
  , CASE $ParagraphCR_0_1_2 
      WHEN 2 THEN CHAR(0x0A)||CHAR(0x0A) 
      WHEN 1 THEN CHAR(0x0A) 
      ELSE ' ' END AS Para  -- paragraph control
; 
--Backup FactTypeTable
CREATE TABLE xFactTypeTable (FactTypeID INTEGER PRIMARY KEY, OwnerType INTEGER, Name TEXT COLLATE RMNOCASE, Abbrev TEXT, GedcomTag TEXT, UseValue INTEGER, UseDate INTEGER, UsePlace INTEGER, Sentence BLOB, Flags INTEGER )
;
INSERT INTO xFactTypeTable
SELECT * FROM FactTypeTable
;
--Backup RoleTable
CREATE TABLE xRoleTable (RoleID INTEGER PRIMARY KEY, RoleName TEXT COLLATE RMNOCASE, EventType INTEGER, RoleType INTEGER, Sentence TEXT )
;
INSERT INTO xRoleTable
SELECT * FROM RoleTable
;
--Backup WitnessTable Sentences
CREATE TABLE xWitnessTableSentences
AS
SELECT WitnessID, Sentence FROM WitnessTable WHERE Sentence NOT LIKE ''
;
--Backup EventTable Sentences
CREATE TABLE xEventTableSentences
AS
SELECT EventID, Sentence FROM EventTable WHERE Sentence NOT LIKE ''
;
--Backup NameTable sentences
CREATE TABLE xNameTableSentences
AS
SELECT NameID, Sentence FROM NameTable WHERE Sentence NOT LIKE ''
;

--Delete custom local event, name and witness sentences
UPDATE EventTable SET Sentence=''
;
UPDATE NameTable SET Sentence=''
;
UPDATE WitnessTable SET Sentence=''
;

/*
-- Add the _Heading fact type to the FactTypeTable
INSERT OR REPLACE INTO FactTypeTable
SELECT
  (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE '_Heading') AS FactTypeID
  , 0 AS OwnerType
  , '_Heading' AS Name
  , 'Heading' AS Abbrev
  , 'EVEN' AS Gedcom
  , 0 AS UseValue
  , 0 AS UseDate
  , 0 AS UsePlace
  , '[person]: ' AS Sentence
  , -56 AS Flags
;

-- Add _Heading fact to each person not already having it
INSERT INTO EventTable
(
  EventType
  , OwnerType
  , OwnerID
  , SortDate
  --, EditDate
)
SELECT
  (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE '_Heading') AS EventType
  , 0 AS OwnerType
  , PersonID AS OwnerID
  , 5630062501345361932 AS SortDate -- SortDate of 1
--  , 42399.4549927894 AS EditDate 
FROM PersonTable
WHERE PersonID 
NOT IN
(SELECT OwnerID FROM EventTable 
 WHERE EventType =
 (SELECT FactTypeID FROM FactTypeTable WHERE Name LIKE '_Heading')
 AND OwnerType=0
 ) 
;
*/
-- Set default fact/event sentences 
UPDATE FactTypeTable 
SET Sentence =
--     CASE FactTypeTable.Name WHEN 'Marriage' THEN '' ELSE (SELECT Para FROM xFormat) END   -- precede each sentence except marriage with CR       
     CASE FactTypeTable.Name WHEN 'Birth' THEN '[person]:' || CHAR(0x0A) ELSE (SELECT Para FROM xFormat) END   -- precede each sentence except Birth with CR       
     || (SELECT Format1 FROM xFormat)
     || FactTypeTable.Name                                                 -- followed by Fact Name
     || (SELECT Format0 FROM xFormat)
     || ':'                                                                -- followed by colon
     || CASE WHEN FactTypeTable.OwnerType THEN ' with [spouse:given:surname],' ELSE '' END -- followed by "with spousename" for family events
     || '< [Date:plain]>'                                                  -- Date 
     || '<?[Date]|<, [PlaceDetails:plain]>|< [PlaceDetails:plain]>>'       -- Place Details
     || '<?[Date][PlaceDetails]|<, [Place:plain]>|< [Place:plain]>>'       -- Place
     || '<?[Date][Place]|<; [Desc]>|< [Desc]>>.'                           -- Description
WHERE FactTypeTable.Name NOT LIKE '_Heading' -- all fact types except the special _Heading one
;

-- Set Alternate Name local sentences
UPDATE NameTable
SET Sentence =
--SELECT
  (SELECT Para FROM xFormat)
  || (SELECT Format1 FROM xFormat)
  || 
  CASE NameType
  WHEN 1 THEN 'AKA'
  WHEN 2 THEN 'Birth Name'
  WHEN 3 THEN 'Immigrant Name'
  WHEN 4 THEN 'Maiden Name'
  WHEN 5 THEN 'Married Name'
  WHEN 6 THEN 'Nickname'
  WHEN 7 THEN 'Other Spelling'
  ELSE 'Alternate Name'
  END  
  || (SELECT Format0 FROM xFormat)
  || ': <[Date], >[Desc].'
--FROM NameTable
WHERE NOT IsPrimary
;

-- SET default Role sentences 
UPDATE RoleTable
SET Sentence =
(
SELECT
  (SELECT Para FROM xFormat)
  || (SELECT Format1 FROM xFormat)
  || FactTypeTable.Name
  || (SELECT Format0 FROM xFormat)
  || ' ('
  || R.RoleName
  || '): '
  || CASE WHEN FactTypeTable.OwnerType THEN '[Couple]' ELSE '[Person]' END
  || '<, [Date:plain]>'                                                  -- Date 
  || '<?[Date]|<, [PlaceDetails:plain]>|< [PlaceDetails:plain]>>'       -- Place Details
  || '<?[Date][PlaceDetails]|<, [Place:plain]>|< [Place:plain]>>'       -- Place
  || '<?[Date][Place]|<; [Desc]>|< [Desc]>>.'                           -- Description
  
--  , RoleTable.Sentence
FROM RoleTable R
JOIN FactTypeTable 
ON R.EventType = FactTypeID
WHERE RoleTable.RoleID = R.RoleID
)
;

-- make a list of the last family event for each couple
-- (this ultimately should exclude those events that are not enabled
--  for narratives or are set to private - laters)
DROP TABLE IF EXISTS xLastCoupleEvent
;
CREATE TABLE xLastCoupleEvent
AS
SELECT EventID, EventType, OwnerID, MAX(SortDate) AS SortDate, Note
FROM EventTable
WHERE OwnerType
GROUP BY OwnerID
ORDER BY EventID
;

--Append two <CR>s to the Note of each couple's last event so 
-- the spouse's subsection starts a new paragraph
UPDATE EventTable
SET Note = Note || CHAR(0x0A,0x0D) || '{CR}' || CHAR(0x0A,0x0D) || '{CR}'
WHERE EventID IN (SELECT EventID FROM xLastCoupleEvent)
;

COMMIT
;

SELECT 'Reports-PointFormNarrativeSetup2.sql script completed without error' AS Status
-- end of script