-- Sources-NewspapersCom-LumpClean.sql
/* 2023-05-05 Tom Holden ve3meo

Part adapted from https://sqlitetoolsforrootsmagic.com/source-detail-view-parsing-xml/
Requires SQLite manager with fake RMNOCASE and support for REGEXP_REPLACE() function.
Developed and tested on SQLiteSpy v1.9.16 Win64.

Operates on Master Sources and Citations thereof imported via RM TreeShare from 
Ancestry.com's Newspapers.com Collection to lump Source per Page into Source per
Newspaper Title. In conjunction with another script that modifies the Ancestry
Record source template, report Footnotes and Bibliographies are cleaned.
 
Deconstructs the imported Source Name of the form:
 Newspapers.com - newspapername - publishdate - pagenumber
leaving only the newspapername in the Source Name and ensures that publishdate
and pagenumber are added to the Citation Detail if missing.

On completion, the database will have many identical Master Sources having the 
newspapername as the Source Name. RootsMagic's Source AutoMerge can be used to 
merge these into as few as one.

For clean report output, the built-in Ancestry Record source template must be 
modified using the companion script SourceTemplate-AncestryRecord-cleaned.sql.
*/

REINDEX -- ON RETURN TO RM, RUN REBUILD INDEXES UNDER TOOLS
;

-- Delete unused Newspapers.com Sources of type Ancestry Record
DELETE FROM SourceTable 
WHERE SourceID IN
(
SELECT SourceID FROM SourceTable
WHERE TemplateID=439 AND Name LIKE 'Newspapers.com% - Page%'
EXCEPT SELECT SourceID FROM CitationTable
)
;

-- Extract "Page #" as AddDetail from SourceName
DROP VIEW IF EXISTS SrcNewspapersComLumpVu1
;
CREATE TEMP VIEW SrcNewspapersComLumpVu1 AS
SELECT
 SourceID,
 CitationID,
 SourceName,
 SUBSTR(
 REPLACE(
  REPLACE(
   REPLACE(
    REPLACE(
     REPLACE(
      REPLACE(PageValue, '</Name>', ': '),
     '<Field><Name>', '; '),
    '</Value>',''),
   '<Value>',''),
  '<Value/>',''),
 '</Field>','')
 ,7) -- Trim off Page: 
 AS SourceDetail
 , SUBSTR(SourceName,INSTR(SourceName,' - ')+3) AS Part
 , TRIM(REGEXP_REPLACE(SourceName,'^.* - ','')) AS AddDetail -- that's a surprise; greedy find of last instance!
 , CitFields
FROM
 (
  SELECT 
    S.SourceID
    ,C.CitationID
    ,S.Name AS SourceName
	,SUBSTR(CAST(C.FIELDS AS TEXT),68, LENGTH(CAST(C.FIELDS AS TEXT))-100) AS PageValue
    ,C.Fields AS CitFields  -- stores values of Citation Details in XML format
  FROM CitationTable C LEFT JOIN SourceTable S USING (SourceID)
  WHERE S.Name LIKE 'Newspapers.com%- Page%' AND S.TemplateID=439
  )
ORDER BY SourceName
;

-- Extract newspaper Title as NewSourceName and publish date as AddDetail2 from SrcNewspapersComLumpVu1 
DROP VIEW IF EXISTS SrcNewspapersComLumpVu2
;
CREATE TEMP VIEW SrcNewspapersComLumpVu2 AS
SELECT * 
  ,TRIM(SUBSTR(Part,1, INSTR(Part,' - '))) AS NewSourceName
  ,TRIM(REPLACE(REPLACE(Part,SUBSTR(Part,1, INSTR(Part,' - '))||'- ',''),' - '||AddDetail,'')) AS AddDetail2
FROM SrcNewspapersComLumpVu1
;

-- build table of draft new values for Source Name and Citation Detail 
CREATE TEMP TABLE ModNewspapersComSourcesTable 
AS
SELECT SourceID, CitationID, SourceName, NewSourceName, SourceDetail, AddDetail2, AddDetail 
  ,  CASE 
    WHEN INSTR(SourceDetail, AddDetail2) AND NOT INSTR(SourceDetail, AddDetail)
      THEN SourceDetail||', '||AddDetail
    WHEN NOT INSTR(SourceDetail, AddDetail2) AND NOT INSTR(SourceDetail, AddDetail)
      THEN SourceDetail||', '||AddDetail2||', '||AddDetail
    ELSE
      SourceDetail
  END
  AS NewSourceDetail
  , CitFields
FROM SrcNewspapersComLumpVu2
-- 2710 records
--WHERE NOT INSTR(SourceDetail, AddDetail2) -- AddDetail2 missing from SourceDetail (30)
--WHERE NOT INSTR(SourceDetail, AddDetail) -- AddDetail missing from SourceDetail (2709)
;

/* for review
SELECT * FROM ModNewspapersComSourcesTable 
;
*/

-- apply the revised Citation Detail to the copy of the original XML field
UPDATE ModNewspapersComSourcesTable
 SET CitFields = REPLACE(CitFields, SourceDetail, NewSourceDetail)
;

/* for review that the table values are ready to be applied to the CitationTable
SELECT TRIM(CitFields) FROM ModNewspapersComSourcesTable
;
*/

-- STOP HERE IF YOU SAW SOMETHING WRONG IN THE TEMP Views and Table
-- Modify the Newspapers.com Citations to contain Page number and Publish Date
UPDATE CitationTable
  SET Fields
   = (SELECT CitFields FROM ModNewspapersComSourcesTable Mod
      WHERE CitationTable.CitationID = Mod.CitationID)
WHERE CitationID
 IN (SELECT CitationID FROM ModNewspapersComSourcesTable ORDER BY 1)
;

-- Draft new values for Newspapers.com Master Sources for subsequent Update of SourceTable 
CREATE TEMP VIEW SrcNewspapersComLumpVu3
AS
SELECT DISTINCT
 S.SourceID
 ,S.Name AS OldSourceName
 ,TRIM(S.Fields) AS OldSourceFields
 ,NewSourceName
 ,REGEXP_REPLACE(
    REGEXP_REPLACE(
      REGEXP_REPLACE(Fields,'PubDate</Name><Value>[^<]*</Value>', 'PubDate</Name><Value/>') -- empty PubDate
    ,'>Title</Name><Value>[^<]*</Value>', '>Title</Name><Value>'||NewSourceName||'</Value>') -- replace Title
  ,'>Publisher</Name><Value>[^<]*</Value>', '>Publisher</Name><Value/>') -- empty Publisher
  AS NewSourceFields  
FROM SourceTable S
JOIN ModNewspapersComSourcesTable USING(SourceID)
ORDER BY 1
;

/* for review
SELECT * FROM SrcNewspapersComLumpVu3
;
*/

-- apply new values for Newspapers.com sources to SourceTable
UPDATE SourceTable
  SET
   Name=(SELECT NewSourceName FROM SrcNewspapersComLumpVu3 Mod WHERE SourceTable.SourceID=Mod.SourceID)
   ,Fields=CAST((SELECT NewSourceFields FROM SrcNewspapersComLumpVu3 Mod WHERE SourceTable.SourceID=Mod.SourceID) AS BLOB)
WHERE SourceID IN (SELECT SourceID FROM SrcNewspapersComLumpVu3)
; 

SELECT
 'Script completed without SQLite error. On return to RootsMagic, REBUILD INDEXES!'
 AS Status
;