-- Sources-CopyRepositoryInfoTo.sql
/*
2014-12-11 Tom Holden ve3meo
2014-12-13 rev to copy repository info from AddressTable to sources using any
template (custom or builtin) having [Repository], [RepositoryLoc] and 
[RepositoryReference] fields. Copies only to empty fields for now.

Use the script Sources-UnifyAliases_RepositoryLoc.sql first to rename fields
that are aliases for the two above, commonly used in the built-in templates. 

This script copies the values from the AddressTable to 
the source fields.
AddressTable.Name ==> [Repository]
AddressTable.(City, State) ==> [RepositoryLoc]
*/

-- make a list of templates having the Repository field
DROP VIEW IF EXISTS TemplatesWithRepository
;
CREATE TEMP VIEW TemplatesWithRepository AS
SELECT * FROM SourceTemplateTable WHERE FieldDefs LIKE '%<FieldName>Repository%'
;

-- make a list of the values to be put into the [Repository], 
-- [RepositoryLoc] and [RepositoryReference] fields
DROP VIEW IF EXISTS RepositoriesBySourceID
;
CREATE TEMP VIEW RepositoriesBySourceID
AS
SELECT
 S.SourceID
 , A.Name AS Repository
 , CASE  
    WHEN LENGTH(A.City || A.State) = 0 THEN ''
    WHEN LENGTH(A.City) = 0 AND LENGTH(A.State) > 0 THEN A.State
    WHEN LENGTH(A.City) > 0 AND LENGTH(A.State) = 0 THEN A.City
    ELSE A.City || ', ' || A.State
   END AS RepositoryLoc 
 , AL.Details AS RepositoryReference
FROM AddressTable A
INNER JOIN AddressLinkTable AL USING(AddressID)
INNER JOIN SourceTable S ON AL.OwnerID = S.SourceID
WHERE AL.OwnerType = 3 -- Owner is a Source
AND AL.AddressNum = 1 -- take first Repository only
ORDER BY SourceID
;

-- temp source table on which to revise values for repository fields 
DROP TABLE IF EXISTS SourcesWithRepositoryFields
;
CREATE TEMP TABLE 
 SourcesWithRepositoryFields(SourceID INTEGER PRIMARY KEY, Name TEXT COLLATE RMNOCASE, RefNumber TEXT, ActualText TEXT, Comments TEXT, IsPrivate INTEGER, TemplateID INTEGER, Fields BLOB )
 ;
  
INSERT INTO SourcesWithRepositoryFields
SELECT * FROM SourceTable
WHERE SourceID IN (SELECT SourceID FROM RepositoriesBySourceID)
;

-- replace empty repository fields in the temp table with the values from the list RepositoriesBySourceID
-- 1. Repository 
UPDATE SourcesWithRepositoryFields
SET Fields = CAST(
  REPLACE
  (Fields
   , 'Repository</Name><Value/>'
   , 'Repository</Name><Value>' || (SELECT Repository FROM RepositoriesBySourceID RS WHERE SourcesWithRepositoryFields.SourceID = RS.SourceID) || '</Value>'
   ) AS BLOB)
;

-- 2. RepositoryLoc
UPDATE SourcesWithRepositoryFields
SET Fields = CAST(
  REPLACE
  (Fields
   , 'RepositoryLoc</Name><Value/>'
   , 'RepositoryLoc</Name><Value>' || (SELECT RepositoryLoc FROM RepositoriesBySourceID RS WHERE SourcesWithRepositoryFields.SourceID = RS.SourceID) || '</Value>'
   ) AS BLOB)
;

-- 3. RepositoryReference
UPDATE SourcesWithRepositoryFields
SET Fields = CAST(
  REPLACE
  (Fields
   , 'RepositoryReference</Name><Value/>'
   , 'RepositoryReference</Name><Value>' || (SELECT RepositoryReference FROM RepositoriesBySourceID RS WHERE SourcesWithRepositoryFields.SourceID = RS.SourceID) || '</Value>'
   ) AS BLOB)
;

-- Now update SourceTable with the revised records
INSERT OR REPLACE INTO SourceTable
SELECT * FROM SourcesWithRepositoryFields
;

SELECT 'Script executed - check Source List in RootsMagic for results' AS Status
;
-- END of SCRIPT