-- SourceTemplateRevisionUtilities.sql
/* 2013-09-24 ve3meo
2013-09-25 added ripple update of the name of the source template through to the citation comment

This series of queries is intended to facilitate review and editing of 
copies of the builtin source templates to remove extraneous punctuation
and text when fields are left empty. The series assumes a clean database
with one person RIN=1 to whom an empty citation using each source template
will be linked. The series:
1. Creates a copy of each builtin source template which can then be edited in RM, name prefixed with *
2. Creates an empty source for each copy of the builtin source templates, source name = template name
3. Creates an empty citation of each source linked to a person whose RIN is 1.
4. Ripples the name of the source template through to citation comment for use in end notes.

The database can be opened by RM on the Citation Manager for Person 1. The names of the sources 
are ordered alphabetically to match the order in the Source Template List. Selecting a citation
shows the three sentences resulting from the empty source citation. Using an external SQLite manager,
the SourceTemplateTable sentence templates for Footnote, Short Footnote and Bibliography can be edited,
thus saving much drilling up and down in RM. Simply selecting another citation in RM and returning
refreshes the sentence generation to catch up to the changes in the template.  

Of course, there may other ways of working effectively, such as two instances of RootsMagic (resident and portable)
open on the common database, one in the Citation Manager, the other in the Source Templates window.
*/

-- Create a copy of each builtin source template
INSERT INTO SourceTemplateTable
SELECT TemplateID + 10000
	,'*' || NAME
	,Description
	,Favorite
	,Category
	,Footnote
	,ShortFootnote
	,Bibliography
	,FieldDefs
FROM SourceTemplateTable
WHERE TemplateID < 10000;

-- Create an empty source for each copy of the builtin source templates
INSERT INTO SourceTable
SELECT NULL AS SourceID
	,NAME
	,'' AS RefNumber
	,'' AS ActualText
	,'' AS Comments
	,0 AS IsPrimary
	,TemplateID
	,(
		SELECT CAST('??<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields></Fields></Root>' AS BLOB)
		) AS Fields
FROM SourceTemplateTable ST
WHERE ST.TemplateID > 10000;

-- Create an empty citation for each empty source based on copies of the builtin source templates
-- all these citations are to the person with RIN=1
INSERT INTO CitationTable
SELECT NULL AS CitationID
	,0 AS OwnerType
	,SourceID
	,1 AS OwnerID
	,'~~~' AS Quality
	,0 AS IsPrimary
	,'' AS Comments
	,'' AS ActualText
	,'' AS RefNumber
	,0 AS Flags
	,(
		SELECT CAST('??<?xml version="1.0" encoding="UTF-8"?>
<Root><Fields></Fields></Root>' AS BLOB)
		) AS Fields
FROM SourceTable S
WHERE S.TemplateID > 10000
ORDER BY S.NAME;

/* Ripple SourceTemplate name through to Citation comments
 via Source comments for listing in end notes. Having the name
 in Source comments may also be convenient.
*/
UPDATE SourceTable
SET Name = (
		SELECT ST.NAME
		FROM SourceTemplateTable ST
		WHERE SourceTable.TemplateID = ST.TemplateID
		);

UPDATE SourceTable
SET Comments = Name;

UPDATE CitationTable
SET Comments = CAST(x'0A' AS TEXT) || '{' || (
		SELECT NAME
		FROM SourceTable S
		WHERE CitationTable.SourceID = S.SourceID
		) || '}';