-- SourceTemplates-MergeDuplicates.sql
/*
2013-07-30 Tom Holden ve3meo
2013-08-07 corrected error in Update SourceTable which obtained only the first TemplateID from xLookupSourceTemplateTable
2016-04-22 now tolerates differences in case and space characters in Footnote and FieldDefs

Merges custom source templates that have identical
Footnote sentence templates and field definitions.
Ignores differences in Short Footnotes, Bibliography and other fields.
Makes the lowest TemplateID of a set of duplicates the master.

It is still sensitive to differences in hints in what would be otherwise identical field definitions that could be merged.
- needs parsing to clear that out.
*/

--Create a table of the master custom source templates having duplicate(s) 
DROP TABLE IF EXISTS xDupSourceTemplateTable
;

CREATE TABLE IF NOT EXISTS xDupSourceTemplateTable 
AS
SELECT 
   TemplateID
  , Name
  , Description
  , Favorite
  , Category 
  , FootnoteCore 
  , ShortFootnote
  , Bibliography
  , FieldDefsCore
FROM
(
SELECT COUNT()-1 AS Dupes,
   TemplateID
  , Name
  , Description
  , Favorite
  , Category 
  , REPLACE(LOWER(Footnote),' ','') AS FootnoteCore 
  , ShortFootnote
  , Bibliography
  , REPLACE(LOWER(FieldDefs),' ','') AS FieldDefsCore
FROM 
(SELECT * FROM SourceTemplateTable WHERE TemplateID > 999 ORDER BY TemplateID DESC)
GROUP BY FootnoteCore, FieldDefsCore
)
WHERE Dupes > 0
;

-- Create table of matching custom source templates
DROP TABLE IF EXISTS xLookupSourceTemplateIDTable;
CREATE TABLE IF NOT EXISTS xLookupSourceTemplateIDTable
AS
SELECT xD.TemplateID AS MasterID, ST.TemplateID FROM xDupSourceTemplateTable xD
INNER JOIN SourceTemplateTable ST
WHERE xD.FootnoteCore LIKE REPLACE(LOWER(ST.Footnote),' ','')
AND xD.FieldDefsCore LIKE REPLACE(LOWER(ST.FieldDefs),' ','')
AND ST.TemplateID > 999
;

-- Revise SourceTable to point to master TemplateID
--EXPLAIN QUERY PLAN
UPDATE SourceTable
SET TemplateID = (SELECT MasterID FROM xLookupSourceTemplateIDTable xL WHERE SourceTable.TemplateID=xL.TemplateID)
WHERE TemplateID IN (SELECT TemplateID FROM xLookupSourceTemplateIDTable)
;

-- Delete now unused duplicate Templates
DELETE FROM SourceTemplateTable
WHERE TemplateID IN
(
SELECT TemplateID FROM xLookupSourceTemplateIDTable WHERE TemplateID != MasterID
)
;