-- Sources-CreateExtremelySplitFromCitations.sql
/*
A work in progress, exploring the batch creation of Extremely Split sources
 and citations thereof from existing sources and citations

2013-06-18 Tom Holden ve3meo
2013-06-27 rev now converts Free Form lumped sources.
2013-06-28 rev now maps Detail Text webtags from lumped sources to Master Text webtags for newly split sources
2013-06-29 rev now maps images from lumped Master and Detail to newly split Master; repositories copied;
            exception process to prevent reconversion of already split sources and templates.            
2013-07-27 rev adds person name and fact type to Master Source Name for improved granularity.
2013-09-03 rev accommodates a null spouse that would previously null the new source name; 
revised test for Family vs Indiv, perhaps needlessly but further testing warranted to cover all combinations
2014-10-07 rev to handle a Source having no fields defined in the Source Template for the Master Source 


A heavy duty SQLite procedure that takes considerable time to complete. Optimisation
for speed yet to be done.

Deletion of lumped sources and their citations yet to be done; also for 
unused custom source templates. May need to clean out garbage before running 
the main script as well as after - Delete Phantoms query?
*/

-- First make a list of already ultimately split source templates
DROP TABLE IF EXISTS xSplitSourceTemplateExceptTable;
CREATE TABLE IF NOT EXISTS xSplitSourceTemplateExceptTable
AS
SELECT TemplateID FROM SourceTemplateTable 
WHERE CAST(FieldDefs AS TEXT) NOT LIKE '%<CitationField>True</CitationField>%'
;

-- Make a list of already ultimately split sources based on no values in CitationTable.Fields
DROP TABLE IF EXISTS xSplitSourceExceptTable;
CREATE TABLE IF NOT EXISTS xSplitSourceExceptTable
AS
SELECT SourceID FROM CitationTable C
EXCEPT 
SELECT SourceID FROM CitationTable C WHERE CAST(C.Fields AS TEXT) LIKE '%</VALUE>%'
;

-- 2014-10-07
DROP VIEW IF EXISTS SourceFieldsView
;

-- cast SourceTable.Fields to Text and replace the empty tag <Fields/> with the normally non-empty
-- pair <Fields></Fields> into a view for the subsequent building of xSplitSourceTable 2014-10-07
CREATE TEMP VIEW SourceFieldsView
AS SELECT SourceID, REPLACE(CAST(Fields AS TEXT), '<Fields/>', '<Fields></Fields>') AS FieldsTxt
FROM SourceTable
;

-- Create table of new Extreme Split Master Sources from Citations of existing Sources 
DROP TABLE
IF EXISTS xSplitSourceTable;

CREATE TABLE
IF NOT EXISTS xSplitSourceTable AS
	SELECT C.CitationID AS OldCitationID
		,NULL AS NewSourceID
    ,S.SourceID AS SourceID
		,'^' || S.NAME AS NAME -- needs more granular identifier
		,CASE
       WHEN S.RefNumber NOT LIKE '' AND C.RefNumber NOT LIKE ''
         THEN S.RefNumber || ', ' || C.RefNumber
       ELSE S.RefNumber || C.RefNumber
     END
     AS RefNumber --citation ref number concatenated to source refnumber
		,CASE
       WHEN S.ActualText NOT LIKE '' AND C.ActualText NOT LIKE ''
         THEN S.ActualText || ' || ' || C.ActualText
       ELSE S.ActualText || C.ActualText
     END
     AS ActualText -- citation text concatenated to source text 
		,CASE
       WHEN S.Comments NOT LIKE '' AND C.Comments NOT LIKE ''
         THEN S.Comments || ' || ' || C.Comments 
       ELSE S.Comments || C.Comments 
     END  
     AS Comments -- citation comments concatenated to source comments
		,S.IsPrivate OR C.IsPrivate AS IsPrivate -- if either is private then the new Source is too
		,S.TemplateID AS TemplateID -- points to existing TemplateID for now
		,SUBSTR(SFV.FieldsTxt, 1, INSTR(SFV.FieldsTxt, '</Fields>') - 1) || SUBSTR(CAST(C.Fields AS TEXT), INSTR(CAST(C.Fields AS TEXT), '<Fields>') + 8) 
     AS FieldsTxt -- citation fields concatenated to source fields (CAST as BLOB on INSERT into SourceTable)
	FROM CitationTable C
	INNER JOIN SourceTable S USING (SourceID)
  NATURAL JOIN SourceFieldsView SFV               --2014-10-07
  WHERE S.TemplateID NOT IN 
  (SELECT TemplateID FROM xSplitSourceTemplateExceptTable)
  OR C.SourceID NOT IN
  (SELECT SourceID FROM xSplitSourceExceptTable);  

-- Assign new SourceIDs to the new sources
UPDATE xSplitSourceTable
SET NewSourceID = (
		SELECT MAX(RowID)
		FROM SourceTable
		) + RowID;

-- Add Person name and fact type to Master Source Name
--- Personal citations
UPDATE xSplitSourceTable
SET Name =
(
SELECT S.Name || ' - ' || N.Surname || ', ' || N.Given || ' (Person)' FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN NameTable N ON C.OwnerID = N.OwnerID
WHERE +N.IsPrimary AND C.OwnerType = 0
AND xSplitSourceTable.OldCitationID = C.CitationID
)
WHERE OldCitationID IN (SELECT CitationID FROM CitationTable WHERE OwnerType = 0)
;  
--- Family citations 
---- Spouse 
UPDATE xSplitSourceTable
SET Name =
ifnull(
(
SELECT S.Name || ' - ' || ifnull(N1.Surname,'') || ', ' || ifnull(N1.Given,'') || ' (Spouse) ' || ifnull(N2.Surname,'') || ', ' || ifnull(N2.Given,'') FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN FamilyTable F ON C.OwnerID = F.FamilyID
INNER JOIN NameTable N1 ON F.FatherID = N1.OwnerID
INNER JOIN NameTable N2 ON F.MotherID = N2.OwnerID
WHERE +N1.IsPrimary AND +N2.IsPrimary AND C.OwnerType = 1
AND xSplitSourceTable.OldCitationID = C.CitationID
)
,Name) -- endof ifnull()
WHERE OldCitationID IN (SELECT CitationID FROM CitationTable WHERE OwnerType = 1)
;  
---- Parents (does not exist?)

--- Event citations 
----Indiv Event
UPDATE xSplitSourceTable
SET Name =
(
SELECT S.Name || ' - ' || N.Surname || ', ' || N.Given || ' (' || FT.Name || ')' FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN EventTable E ON C.OwnerID = E.EventID
INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID
INNER JOIN NameTable N ON E.OwnerID = N.OwnerID
WHERE +N.IsPrimary AND C.OwnerType = 2 
AND E.OwnerType = 0 AND FT.OwnerType = 0  -- 2013-09-03 corrected error failed to test FactTypeTable OwnerType
AND xSplitSourceTable.OldCitationID = C.CitationID
)
WHERE OldCitationID 
IN (SELECT CitationID 
    FROM CitationTable C 
    INNER JOIN EventTable E ON C.OwnerID = E.EventID 
    WHERE C.OwnerType = 2 AND E.OwnerType = 0
    )
;  
----Family Event
-- allow for null spouse
UPDATE xSplitSourceTable
SET Name =
ifnull(
(
SELECT S.Name || ' - ' || ifnull(N1.Surname,'') || ', ' || ifnull(N1.Given,'') || ' (' || FT.Name || ') ' || ifnull(N2.Surname,'') || ', ' || ifnull(N2.Given,'') FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN EventTable E ON C.OwnerID = E.EventID
INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID
INNER JOIN FamilyTable F ON E.OwnerID = F.FamilyID
INNER JOIN NameTable N1 ON F.FatherID = N1.OwnerID
INNER JOIN NameTable N2 ON F.MotherID = N2.OwnerID
WHERE +N1.IsPrimary AND +N2.IsPrimary AND C.OwnerType = 2 
AND E.OwnerType = 0 AND FT.OwnerType = 1  -- 2013-09-03 corrected error E.OwnerType = 1 which is for child-parent event not spousal
AND xSplitSourceTable.OldCitationID = C.CitationID
)
,Name) -- end of ifnull()
WHERE OldCitationID 
IN (SELECT CitationID 
    FROM CitationTable C 
    INNER JOIN EventTable E ON C.OwnerID = E.EventID 
    INNER JOIN FactTypeTable FT ON E.EventType = FT.FactTypeID  -- 2013-09-03 corrected for test of spousal events
    WHERE C.OwnerType = 2 AND FT.OwnerType = 1
    )
;  

--- Alternate Name
UPDATE xSplitSourceTable
SET Name =
(
SELECT S.Name || ' - ' || N1.Surname || ', ' || N1.Given || ' (Alt Name) ' || N2.Surname || ', ' || N2.Given FROM xSplitSourceTable S
INNER JOIN CitationTable C ON S.OldCitationID = C.CitationID
INNER JOIN NameTable N2 ON C.OwnerID = N2.NameID
INNER JOIN NameTable N1 ON N2.OwnerID = N1.OwnerID
WHERE +N1.IsPrimary AND NOT +N2.IsPrimary AND C.OwnerType = 7
AND xSplitSourceTable.OldCitationID = C.CitationID
)
WHERE OldCitationID IN (SELECT CitationID FROM CitationTable WHERE OwnerType = 7)
;  
------- Source names extended

/* Create new Extreme Split Source Templates in a table
started from those templates used by cited sources
*/


DROP TABLE
IF EXISTS xSplitSourceTemplateTable;
CREATE TABLE
IF NOT EXISTS xSplitSourceTemplateTable AS
	SELECT NULL AS NewTemplateID
		,*
	FROM SourceTemplateTable
	WHERE TemplateID IN (
			SELECT DISTINCT TemplateID
			FROM xSplitSourceTable
			)
--		AND NAME NOT LIKE '^%' --don't convert already converted ones
		;

/*Convert all fields in the Source Template defined as Source Details or Citation Details
to Master Source fields, thus enabling the template to be used for Extreme Splitting.*/
UPDATE xSplitSourceTemplateTable
SET FieldDefs = CAST(REPLACE(CAST(FieldDefs AS TEXT), '<CitationField>True</CitationField>', '<CitationField>False</CitationField>') AS BLOB)
--WHERE NAME NOT LIKE '^%' --don't convert already converted ones
	;

/* Create a template for extremely split Free Form sources
*/
INSERT INTO xSplitSourceTemplateTable
(NewTemplateID, TemplateID, Name, Description, Favorite, Category, Footnote, ShortFootnote, Bibliography, FieldDefs)
VALUES
(
Null
,0
,'Free Form -xSplit'
,'Free Form modified for extreme splitting'
,0
,'All'
,'[Footnote]<?[Page]|, [Page]>.'
,'[ShortFootnote]<?[Page]|, [Page]>.'
,'[Bibliography].'
,CAST('?<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields><Field><FieldName>Footnote</FieldName><DisplayName>Footnote</DisplayName><Type>Text</Type><Hint>Author, title, publisher (full description of source)</Hint><LongHint>Used for first footnote citing this source in a report.
Exports to GEDCOM TITL tag</LongHint><CitationField>False</CitationField></Field>
<Field><FieldName>ShortFootnote</FieldName><DisplayName>Short Footnote</DisplayName><Type>Text</Type><Hint>Author&apos;s surname or short title or other key for re-citing</Hint><LongHint>Used for subsequent footnotes citing a source previously footnoted in a report</LongHint><CitationField>False</CitationField></Field>
<Field><FieldName>Bibliography</FieldName><DisplayName>Bibliography</DisplayName><Type>Text</Type><Hint>Describe source sufficiently for others to identify</Hint><LongHint/><CitationField>False</CitationField></Field>
<Field><FieldName>Page</FieldName><DisplayName>Details</DisplayName><Type>Text</Type><Hint>Details to help find the citation within the source</Hint><LongHint>Used in the full and short footnotes.
</LongHint><CitationField>False</CitationField></Field></Fields></Root>'
 AS BLOB)
)
;

-- Assign new TemplateIDs to the new templates
UPDATE xSplitSourceTemplateTable
SET NewTemplateID = (
		SELECT MAX(TemplateID)
		FROM SourceTemplateTable
		) + RowID;

/*
-- IF the Free Form -xSplit template already exists in the SourceTemplateTable
-- merge multiple instances to one and put its TemplateID into NewTemplateID 
-- then delete it from SourceTemplateTable
*/

UPDATE SourceTable
SET TemplateID = 
(
 SELECT TemplateID FROM SourceTemplateTable 
 WHERE Name LIKE '^Free Form -xSplit'
 )
WHERE TemplateID IN 
(
 SELECT TemplateID FROM SourceTemplateTable 
 WHERE Name LIKE '^Free Form -xSplit'
 )
;

UPDATE xSplitSourceTemplateTable
SET NewTemplateID = 
ifnull(
(SELECT TemplateID FROM SourceTemplateTable 
 WHERE Name LIKE '^Free Form -xSplit')
 ,NewTemplateID)
WHERE TemplateID = 0
;

DELETE FROM SourceTemplateTable
WHERE Name LIKE '^Free Form -xSplit'
;


-- Point new xSources to the new xTemplates
UPDATE xSplitSourceTable
SET TemplateID = (
		SELECT NewTemplateID
		FROM xSplitSourceTemplateTable ST
		WHERE xSplitSourceTable.TemplateID = ST.TemplateID
		)
WHERE TemplateID IN (
		SELECT DISTINCT TemplateID
		FROM xSplitSourceTemplateTable
		);


-- Create a table of new split citations from the existing ones
	--SELECT CitationID, CAST(Fields AS TEXT) FROM CitationTable
DROP TABLE
IF EXISTS xSplitCitationTable;

CREATE TABLE
IF NOT EXISTS xSplitCitationTable AS
	SELECT NULL AS NewCitationID
		,NULL AS NewSourceID
		,*
	FROM CitationTable;

-- 
UPDATE xSplitCitationTable
SET NewCitationID = (
		SELECT MAX(CitationID)
		FROM CitationTable
		) + RowID
	,NewSourceID = (
		SELECT NewSourceID
		FROM xSplitSourceTable S
		WHERE xSplitCitationTable.CitationID = S.OldCitationID
		)
	,Fields = CAST('<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields/></Root>' AS BLOB);
	--SELECT CitationID, CAST(Fields AS TEXT) FROM xSplitCitationTable
	;

-- Insert the new split templates, sources and citations into the RM database
INSERT OR IGNORE INTO SourceTemplateTable
SELECT NewTemplateID
	,'^' || NAME
	,Description
	,Favorite
	,Category
	,Footnote
	,ShortFootnote
	,Bibliography
	,FieldDefs
FROM xSplitSourceTemplateTable;

INSERT INTO SourceTable
SELECT NewSourceID
	,NAME
	,RefNumber
	,ActualText
	,Comments
	,IsPrivate
	,TemplateID
	,CAST(FieldsTxt AS BLOB)
FROM xSplitSourceTable;

INSERT INTO CitationTable
SELECT NewCitationID
	,OwnerType
	,NewSourceID
	,OwnerID
	,Quality
	,IsPrivate
	,Comments
	,ActualText
	,RefNumber
	,Flags
	,Fields
FROM xSplitCitationTable;

-- Copy Detail Text WebTag from old lumped citation to new split source Master Text WebTag
INSERT INTO URLTable
SELECT NULL AS LinkID
  ,3 AS OwnerType
  ,C.NewSourceID AS OwnerID
  ,U.LinkType 
  ,U.Name
  ,U.URL
  ,U.Note  
FROM URLTable U 
INNER JOIN xSplitCitationTable C
ON U.OwnerID=C.CitationID 
WHERE U.OwnerType =4 
;

-- Copy Master Text WebTag from old lumped source to new split source Master Text WebTag
INSERT INTO URLTable
SELECT NULL AS LinkID
  ,3 AS OwnerType
  ,S.NewSourceID AS OwnerID
  ,U.LinkType 
  ,U.Name
  ,U.URL
  ,U.Note  
FROM URLTable U 
INNER JOIN xSplitSourceTable S
ON U.OwnerID=S.SourceID 
WHERE U.OwnerType =3 
;

-- Copy Repositories from original lumped source to split sources
INSERT INTO AddressLinkTable
SELECT
  NULL AS LinkID
  ,A.OwnerType
  ,A.AddressID
  ,S.NewSourceID
  ,A.AddressNum
  ,A.Details
FROM AddressLinkTable A
INNER JOIN xSplitSourceTable S
ON A.OwnerID = S.SourceID 
WHERE A.OwnerType=3
;

-- Copy MediaLinks from citation or Master of original lumped source to Master split source
DROP TABLE IF EXISTS xSplitMediaLinkTable;
CREATE TABLE IF NOT EXISTS xSplitMediaLinkTable
AS 
SELECT * FROM MediaLinkTable WHERE OwnerType = 4 -- copy citation links first
AND OwnerID IN (SELECT CitationID FROM xSplitCitationTable)
; --

INSERT INTO xSplitMediaLinkTable
SELECT * FROM MediaLinkTable 
WHERE OwnerType = 3 -- copy master source links second
AND OwnerID IN (SELECT DISTINCT SourceID FROM xSplitSourceTable)
;

---- Point links for lumped Master source to split Master Source
UPDATE xSplitMediaLinkTable
SET
  LinkID = NULL, 
  OwnerID = (SELECT NewSourceID FROM xSplitSourceTable S WHERE OwnerID=S.SourceID)
WHERE OwnerType = 3
--AND OwnerID IN (SELECT DISTINCT SourceID FROM xSplitSourceTable)
;

---- Point links for lumped citations to split Master Source
UPDATE OR REPLACE xSplitMediaLinkTable
SET
  LinkID = NULL, 
  OwnerType = 3,
  OwnerID = (SELECT NewSourceID FROM xSplitCitationTable C WHERE xSplitMediaLinkTable.OwnerID=C.CitationID)
WHERE OwnerType = 4
--AND OwnerID IN (SELECT DISTINCT CitationID FROM xSplitCitationTable)
;

INSERT INTO MediaLinkTable
SELECT * FROM xSplitMediaLinkTable
;

/* All lumped sources now duplicated by split sources for review.
Deletion of lumped sources and their citations yet to be done; also for 
unused custom source templates.
*/