-- DuplicateNameSearch.sql
-- Attempt to provide faster tool than RM4's DuplicateSearchMerge

DROP TABLE IF EXISTS namesort;
CREATE temp TABLE IF NOT EXISTS namesort AS
SELECT ownerid, LOWER(surname COLLATE nocase || given COLLATE nocase) AS NAME FROM nametable ORDER BY NAME COLLATE NOCASE;

CREATE INDEX IF NOT EXISTS idxNameSortName ON Namesort (NAME);
CREATE INDEX IF NOT EXISTS idxNameSortOwnerID ON Namesort (OwnerID);
 

SELECT 
  100*(
  1 
  + CASE P.birthyear<>0 AND S.birthyear<>0 WHEN 0 THEN 0 ELSE (3-ABS(P.BIRTHYEAR-S.BIRTHYEAR)) END
  + CASE P.Deathyear<>0 AND S.DeathYear<>0 WHEN 0 THEN 0 ELSE (3-ABS(P.Deathyear-S.Deathyear)) END
  + CASE LOWER(NF1.Surname || NF1.Given) WHEN LOWER(NF2.Surname || NF2.Given)  THEN 3 ELSE 0 END    
  + CASE LOWER(NM1.Surname || NM1.Given) WHEN LOWER(NM2.Surname || NM2.Given)  THEN 3 ELSE 0 END    
  )/13
  AS Score,   
  -- Score 1 for name match                                                    
  -- Score 3 for matching BirthYear less 1 for each year difference  
  -- Score 3 for matching DeathYear less 1 for each year difference
  -- Score 3 for matching Father names
  -- Score 3 for matching Mother names
  -- Divide by max possible score and multiply by 100
  P.SurName ||', '|| P.Given ||'-'||ID1 AS "Primary", P.BirthYear AS Born, P.DeathYear AS Died, 
    NF1.Surname ||', '|| NF1.Given ||'-'||NF1.OwnerID AS PrimesFather, 
    NM1.Surname ||', '|| NM1.Given ||'-'||NM1.OwnerID AS PrimesMother, 
  S.SurName ||', '|| S.Given ||'-'||ID2 AS Duplicate, S.BirthYear AS Born, S.DeathYear AS Died, 
    NF2.Surname ||', '|| NF2.Given ||'-'||NF2.OwnerID AS DupesFather,
    NM2.Surname ||', '|| NM2.Given ||'-'||NM2.OwnerID AS DupesMother
FROM
( SELECT ns1.ownerid AS ID1, ns2.ownerid AS ID2 FROM namesort ns1 
  INNER JOIN namesort ns2 USING (NAME) 
  WHERE ID1<ID2
  EXCEPT SELECT ID1,ID2 FROM EXCLUSIONTABLE WHERE ExclusionType=1)
INNER JOIN nametable P ON P.OwnerID=ID1 
INNER JOIN nametable S ON S.OwnerID=ID2
LEFT JOIN CHILDTABLE C1 ON P.OWNERID=C1.CHILDID
LEFT JOIN ChildTable C2 ON S.OWNERID=C2.CHILDID
LEFT JOIN FamilyTable F1 ON C1.FamilyID=F1.FamilyID
LEFT JOIN FamilyTable F2 ON C2.FamilyID=F2.FamilyID
LEFT JOIN NameTable NF1 ON F1.FatherID=NF1.OwnerID
LEFT JOIN NameTable NF2 ON F2.FatherID=NF2.OwnerID
LEFT JOIN NameTable NM1 ON F1.MotherID=NM1.OwnerID
LEFT JOIN NameTable NM2 ON F2.MotherID=NM2.OwnerID

WHERE +P.IsPrimary AND +S.IsPrimary 
  AND CASE +NF1.IsPrimary WHEN 0 THEN 0 ELSE 1 END 
  AND CASE +NF2.IsPrimary WHEN 0 THEN 0 ELSE 1 END
  AND CASE +NM1.IsPrimary WHEN 0 THEN 0 ELSE 1 END
  AND CASE +NM2.IsPrimary WHEN 0 THEN 0 ELSE 1 END
  AND NOT MIN(P.BIRTHYEAR, S.BIRTHYEAR, ABS(P.BIRTHYEAR-S.BIRTHYEAR)>2)
  AND NOT MIN(P.Deathyear, S.Deathyear, ABS(P.Deathyear-S.Deathyear)>2)
  AND CASE F1.FatherID WHEN ID2 THEN 0 ELSE 1 END
  AND CASE F1.MotherID WHEN ID2 THEN 0 ELSE 1 END
  AND CASE F2.FatherID WHEN ID1 THEN 0 ELSE 1 END
  AND CASE F2.MotherID WHEN ID1 THEN 0 ELSE 1 END
ORDER BY Score DESC, "Primary" ASC
LIMIT 100
;