-- Sources-MergeDuplicates.sql
/*
2013-08-01 Tom Holden ve3meo
2013-08-08 ignores all white space in Source Text and Comments

AutoMerges nearly identical SourceTable records, tolerating differences in Names,
white space in ActualText and Comments...
Makes the lowest SourceID of a set of duplicates the master.
*/

--Create a table of the master sources having duplicate(s) 
DROP TABLE IF EXISTS xDupSourceTable
;

CREATE TABLE IF NOT EXISTS xDupSourceTable 
AS
--EXPLAIN QUERY PLAN
SELECT 
  SourceID
  , Name
  , RefNumber
  , ActualText_stripped
  , Comments_stripped 
  , IsPrivate
  , TemplateID
  , CAST(Fields AS BLOB) AS Fields
FROM
(
SELECT
  COUNT()-1 AS Dupes, *
FROM 
(
 SELECT
  SourceID
  , Name
  , RefNumber  
--  , TRIM(ActualText,' '||CAST(x'0A0D' AS TEXT)) AS ActualText -- strip outer white space
--  , TRIM(Comments,' '||CAST(x'0A0D' AS TEXT)) AS Comments
-- rev 2013-08-08 strip out all white space in ActualText and Comments
  , REPLACE(REPLACE(REPLACE(REPLACE(ActualText, ' ', ''), CAST(x'0A' AS TEXT), ''), CAST(x'0D' AS TEXT), ''), CAST(x'09' AS TEXT), '') AS ActualText_stripped
  , REPLACE(REPLACE(REPLACE(REPLACE(Comments, ' ', ''), CAST(x'0A' AS TEXT), ''), CAST(x'0D' AS TEXT), ''), CAST(x'09' AS TEXT), '') AS Comments_stripped
  , IsPrivate
  , TemplateID
  , Fields
 FROM SourceTable 
 ORDER BY TemplateID DESC
)
GROUP BY 
  RefNumber
  , ActualText_stripped  
  , Comments_stripped
  , IsPrivate
  , TemplateID
  , Fields
)
WHERE Dupes > 0
;

-- Prefix Master Source Names of merged sources with + to distinguish them
UPDATE SourceTable
SET Name = '+' || Name
WHERE SourceID IN (SELECT SourceID FROM xDupSourceTable ORDER BY SourceID)
;

-- Create table of matching custom sources 
DROP TABLE IF EXISTS xLookupSourceIDTable;
CREATE TABLE IF NOT EXISTS xLookupSourceIDTable
AS
-- EXPLAIN QUERY PLAN
SELECT
  S.SourceID 
  , xD.SourceID AS MasterID
--  , xD.ActualText  
--  , S.ActualText
FROM xDupSourceTable xD  
INNER JOIN SourceTable S
WHERE
  S.SourceID != xD.SourceID  
  AND  xD.RefNumber = S.RefNumber 
 -- AND xD.ActualText LIKE TRIM(S.ActualText,' '||CAST(x'0A0D' AS TEXT))
 --  AND INSTR(S.ActualText, xD.ActualText) 
 -- AND xD.Comments  LIKE TRIM(S.Comments,' '||CAST(x'0A0D' AS TEXT))
 --  AND INSTR(S.Comments, xD.Comments)
-- rev 2013-08-08 strip out all white space in ActualText and Comments
  AND xD.ActualText_stripped 
    LIKE REPLACE(REPLACE(REPLACE(REPLACE(S.ActualText, ' ', ''), CAST(x'0A' AS TEXT), ''), CAST(x'0D' AS TEXT), ''), CAST(x'09' AS TEXT), '')
  AND xD.Comments_stripped  
    LIKE REPLACE(REPLACE(REPLACE(REPLACE(S.Comments, ' ', ''), CAST(x'0A' AS TEXT), ''), CAST(x'0D' AS TEXT), ''), CAST(x'09' AS TEXT), '')
  AND xD.IsPrivate = S.IsPrivate
  AND xD.TemplateID = S.TemplateID
  AND xD.Fields = S.Fields  
ORDER BY S.SourceID
;

-- Revise CitationTable to point to master SourceID
/*--EXPLAIN QUERY PLAN
UPDATE CitationTable
SET SourceID = (SELECT MasterID FROM xLookupSourceIDTable xL WHERE CitationTable.SourceID=xL.SourceID)
WHERE SourceID IN (SELECT SourceID FROM xLookupSourceIDTable)
;
*/

--EXPLAIN QUERY PLAN
UPDATE CitationTable
SET SourceID = ifnull((SELECT MasterID FROM xLookupSourceIDTable xL WHERE CitationTable.SourceID=xL.SourceID), CitationTable.SourceID)
;

-- Delete now unused duplicate Sources
--EXPLAIN QUERY PLAN
DELETE FROM SourceTable
WHERE SourceID IN
(
SELECT SourceID FROM xLookupSourceIDTable --WHERE SourceID != MasterID
)
;

-- reassign MediaLinks, WebTags, Repositories  to merged sources
-- WebTags
---Delete Duplicate WebTags for Sources
--EXPLAIN QUERY PLAN
DELETE FROM URLTable 
WHERE LinkID IN
(
SELECT DISTINCT U.LinkID FROM URLTable U 
INNER JOIN xLookupSourceIDTable xL
ON 
U.OwnerID = xL.[SourceID]
INNER JOIN URLTable U2
USING(OwnerType, LinkType, Name, URL, Note)
WHERE U.OwnerType = 3
--AND xL.SourceID != xL.MasterID
)
;
---Remap non-duplicate WebTags for Sources
--EXPLAIN QUERY PLAN
UPDATE URLTable 
SET OwnerID = ifnull(
 (
  SELECT MasterID 
  FROM xLookUpSourceIDTable xL 
  WHERE UrlTable.OwnerID = xL.SourceID 
--  AND UrlTable.OwnerType = 3
  )
  , URLTable.OwnerID)
WHERE OwnerType = 3
;

--Repositories
---Delete Duplicate AddressLinks for Sources
--EXPLAIN QUERY PLAN
DELETE FROM AddressLinkTable 
WHERE LinkID IN
(
SELECT DISTINCT A.LinkID FROM AddressLinkTable A
INNER JOIN xLookupSourceIDTable xL
ON 
A.OwnerID = xL.[SourceID]
INNER JOIN AddressLinkTable A2
USING(OwnerType, AddressID, Details )
WHERE A.OwnerType = 3
--AND xL.SourceID != xL.MasterID
)
;

---Remap non-duplicate Repositories for Sources
--EXPLAIN QUERY PLAN
UPDATE AddressLinkTable 
SET OwnerID = ifnull(
 (
  SELECT MasterID 
  FROM xLookUpSourceIDTable xL 
  WHERE AddressLinkTable.OwnerID = xL.SourceID 
--  AND AddressLinkTable.OwnerType = 3
  )
  , AddressLinkTable.OwnerID)
WHERE OwnerType = 3
;

-- what if this results in second or more repository for same source with same AddressNum?
-- if all AddressNum 1 and no AddressNum 2, then shows only 1 Repo. Therefore, safe to delete dupes.
DELETE FROM AddressLinkTable
WHERE OwnerType = 3
AND LinkID NOT IN
(
SELECT LinkID FROM AddressLinkTable WHERE OwnerType = 3 GROUP BY AddressID, OwnerID
)
;

-- what if there are two or more different AddressID's for the same Source having the same AddressNum?
-- only the first is shown. Therefore need to assign different AddressNum but only 1, 2 are allowed
-- Could assign 1 to the first of a set, 2 to the last of a set or the second of a set, the rest are unviewable anyway

-- Assign AddressNum=2 to the last of a set of AddressIDs for the same source, including a set of 1
UPDATE AddressLinkTable
SET AddressNum = 2
WHERE LinkID IN
(
SELECT LinkID FROM
(
SELECT * FROM AddressLinkTable WHERE OwnerType = 3 ORDER BY LinkID ASC
) 
GROUP BY OwnerID
); 

-- Assign AddressNum=1 to the first of a set of AddressIDs for the same source, including a set of 1
UPDATE AddressLinkTable
SET AddressNum = 1
WHERE LinkID IN
(
SELECT LinkID FROM
(
SELECT * FROM AddressLinkTable WHERE OwnerType = 3 ORDER BY LinkID DESC
) 
GROUP BY OwnerID
); 


--MediaTags

--DELETE DUPLICATES
--EXPLAIN QUERY PLAN
DELETE FROM MediaLinkTable 
WHERE LinkID IN
(
SELECT DISTINCT M.LinkID FROM MediaLinkTable M
INNER JOIN xLookupSourceIDTable xL
ON 
M.OwnerID = xL.[SourceID]
INNER JOIN MediaLinkTable M2
USING(OwnerType, MediaID)
WHERE M.OwnerType = 3
--AND xL.SourceID != xL.MasterID
)
;

---Remap non-duplicate MediaTags for Sources
--EXPLAIN QUERY PLAN
UPDATE MediaLinkTable 
SET OwnerID = ifnull(
 (
  SELECT MasterID 
  FROM xLookUpSourceIDTable xL 
  WHERE MediaLinkTable.OwnerID = xL.SourceID 
--  AND MediaLinkTable.OwnerType = 3
  )
  , MediaLinkTable.OwnerID)
WHERE OwnerType = 3
;