/*

citationfields_and_webtag.sql    08/21/2025  Jerry Bryan

Updates Find A Grave Citations which have a webtag and
which have no citation name and where the URL in the
webtag is not included in the citation. 

1. Copies the webtag into the citation name, less the
   https:// prefix. If the https:// prefix is included
   in the citation name, clicking on the citation name
   from the Sources tab goes to the URL rather than opening
   the citation in RM, which is not desirable behavior.
   If the citation names are left blank, the citations
   are very difficult to manage.
   
2. Copies the webtab into the footnote text, including
   the https:// prefix. This is accomplished by copying
   the webtag into the CitationTable.Fields column. In
   particular, the webtag is copied into the Page field
   of the "Ancestry Record (cleaned)" source template.
   The Page field actually is named "Page" in this source
   template, but it is displayed on RM screens as the
   Detail field because Detail is the display name in the
   template. So it's he same field with two names, but the
   SQLite script has to refer to it by its real name rather
   than by its display name.   
   
It is probably a good idea to run the Merge All Duplicate
Citations tool in RM after running this script. That's
because there are many cases where the test database I'm
working with has the same webtag for several different
citations. The Merge All Duplicate Citation tool cannot
be run when citation names are blank. That's why the tool
has to be run after this script is run, and it's one of
the reasons this script needed to replace the blank
citation names with meaningful citaiton names.
   
*/

REINDEX;

WITH MatchingURLs AS (
    SELECT U.OwnerID, U.URL, C.Fields AS C_Fields
    FROM URLTable AS U
    JOIN CitationTable AS C
    JOIN SourceTable AS S ON S.SourceID = C.SourceID
    WHERE U.OwnerType = 4 AND U.OwnerID = C.CitationID
      AND S.Name LIKE '%find a grave%'
)

UPDATE CitationTable
SET CitationName = 
(
    SELECT REPLACE(REPLACE(URL,'https://',''),'http://','')
    FROM MatchingURLs
    WHERE MatchingURLs.OwnerID = CitationTable.CitationID    
)
, Fields = 
(
    SELECT CAST(REPLACE(C_Fields,
                '<Field><Name>Page</Name><Value></Value></Field>',
                '<Field><Name>Page</Name><Value>'
                || URL
                || '</Value></Field>') AS BLOB)  
    FROM MatchingURLs
    WHERE MatchingURLs.OwnerID = CitationTable.CitationID    
)
   
WHERE CitationID IN
(
    SELECT OwnerID
    FROM MatchingURLs
);