-- TMG-RM_Sentence_Tweaks.sql

DROP TABLE IF EXISTS xSentenceTable
;

CREATE TEMP TABLE xSentenceTable
AS

-------------------FactTypeTable

SELECT CAST(Sentence AS TEXT) AS Sentence, 'Sentence' AS Field, 'FactTypeTable' AS [Table], ROWID AS [Key] FROM FactTypeTable
WHERE FactTypeID > 999    -- custom ones only
UNION ALL
-------------------RoleTable

SELECT CAST(Sentence AS TEXT), 'Sentence', 'RoleTable', ROWID FROM RoleTable
WHERE RoleID > 58    -- custom ones only

UNION ALL
-------------------WitnessTable

SELECT CAST(Sentence AS TEXT), 'Sentence', 'WitnessTable', ROWID FROM WitnessTable
;

--------Tweaker
DROP TABLE IF EXISTS xSentPlaceTable
;

CREATE TEMP TABLE xSentPlaceTable
AS
SELECT ROWID, Sentence 
FROM xSentenceTable
WHERE 
 SENTENCE LIKE '%[Place%'
 AND Sentence NOT LIKE '%[PlaceDetails%'
;

--< [PlaceDetails]>< [Place]>
UPDATE xSentPlaceTable
SET Sentence = REPLACE(Sentence, ' <[Place]>', '< [PlaceDetails]>< [Place]>')
WHERE Sentence LIKE '% <[Place]>%'
AND Sentence NOT LIKE '%[PlaceDetails%'
;

UPDATE xSentPlaceTable
SET Sentence = REPLACE(Sentence, '< [Place]>', '< [PlaceDetails]>< [Place]>')
WHERE Sentence LIKE '%< [Place]>%'
AND Sentence NOT LIKE '%[PlaceDetails%'
;

UPDATE xSentPlaceTable
SET Sentence = REPLACE(Sentence, '[Place', '[Place:plain')
WHERE Sentence NOT REGEXP '\[Place[^\^D]]*plain[^\]]*\]'
AND
  (
         Sentence REGEXP '[ \<\>]{1,2}of[ \<]{1,2}\[Place[^D]'
   OR    Sentence REGEXP '[ \<\>]{1,2}at[ \<]{1,2}\[Place[^D]'
   OR    Sentence REGEXP '[ \<\>]{1,2}from[ \<]{1,2}\[Place[^D]'
   OR    Sentence REGEXP '[ \<\>]{1,2}to[ \<]{1,2}\[Place[^D]'
   OR    Sentence REGEXP '[ \<\>]{1,2}in[ \<]{1,2}\[Place[^D]'
   OR    Sentence REGEXP '[ \<\>]{1,2}near[ \<]{1,2}\[Place[^D]'
   )
;

UPDATE xSentPlaceTable
SET Sentence = REPLACE(Sentence, '[PlaceDetails', '[PlaceDetails:plain')
WHERE Sentence NOT REGEXP '\[PlaceDetails[^\]]*plain[^\]]*\]'
AND 
 (
        Sentence REGEXP '[ \<\>]{1,2}of[ \<]{1,2}\[PlaceDetails'
  OR    Sentence REGEXP '[ \<\>]{1,2}at[ \<]{1,2}\[PlaceDetails'
  OR    Sentence REGEXP '[ \<\>]{1,2}from[ \<]{1,2}\[PlaceDetails'
  OR    Sentence REGEXP '[ \<\>]{1,2}to[ \<]{1,2}\[PlaceDetails'
  OR    Sentence REGEXP '[ \<\>]{1,2}in[ \<]{1,2}\[PlaceDetails'
  )
;

UPDATE xSentPlaceTable
SET Sentence = REPLACE(Sentence, '[Date', '[Date:plain')
WHERE Sentence NOT REGEXP '\[Date[^\]]*plain[^\]]*\]'
AND 
  (
   Sentence REGEXP '[ \<\>]{1,2}in[ \<]{1,2}\[Date'
   OR    
   Sentence REGEXP '[ \<\>]{1,2}on[ \<]{1,2}\[Date'
   OR    
   Sentence REGEXP '[ \<\>]{1,2}of[ \<]{1,2}\[Date'
   OR    
   Sentence REGEXP '[ \<\>]{1,2}to[ \<]{1,2}\[Date'
   OR    
   Sentence REGEXP '[ \<\>]{1,2}from[ \<]{1,2}\[Date'
   OR    
   Sentence REGEXP '[ \<\>]{1,2}at[ \<]{1,2}\[Date'
   OR    
   Sentence REGEXP '[ \<\>]{1,2}about[ \<]{1,2}\[Date'
   )
;

UPDATE xSentPlaceTable
SET Sentence = REPLACE(Sentence, '[L=English]', '')
WHERE Sentence LIKE '%[L=English]%'
;

--- Review
SELECT ROWID, New.Sentence AS New, Old.Sentence AS Old FROM xSentPlaceTable New
INNER JOIN xSentenceTable Old ON New.ROWID = Old.ROWID
;