-- Media-MoveFromSourceToCitation.sql
/* 2023-03-02 Tom Holden ve3meo
Copies Media tags from Master Sourcea to their Citations
Optionally deletes the tags from the Master Source. If not
 deleted, re-executing the copy will duplicate the Citation tags.
 
Procedure:
 Each step is numbered and is enclosed in comments: 
 /* ... /
 Select the Statements within the commented area and execute.
 Proceed to next step and do the same. 
 
Suitable for .rmtree database files; adaptible for .rmgc.
*/

/*
-- 1. Reference Source media to Target Citation of Source
DROP TABLE IF EXISTS zSourceMediaLinks
;
CREATE TEMP TABLE zSourceMediaLinks AS
SELECT ML.LinkID, ML.MediaID, ML.OwnerID, S.SourceID, S.Name, CitationID
FROM MediaLinkTable ML
JOIN SourceTable S ON ML.OwnerID=S.SourceID
AND ML.OwnerType=3
JOIN CitationTable USING(SourceID)
;

-- Inspect
SQLite * FROM zSourceMediaLinks
;
*/

/*
-- 2.Check if there is more than one citation per source
SELECT Name, SourceID, COUNT() AS Citations FROM zSourceMediaLinks
GROUP BY SourceID
ORDER BY Citations DESC, Name ASC
;
-- not a problem for tagging the media to Citations if more than 1
*/

/*
-- 3. View draft MediaLinks that Copy Source Media to Citations
DROP VIEW IF EXISTS DraftMediaLinks
;
CREATE TEMP VIEW DraftMediaLinks AS
SELECT 
  Null AS LinkID
  ,MediaID
  ,4 AS OwnerType
  ,CitationID AS OwnerID
  ,0,0,0,0,0,0,0,0,0,0
  ,'' AS Comments
  ,(SELECT julianday('now') - 2415018.5) AS UTCModDate
FROM zSourceMediaLinks
;

--INSPECT
SELECT * FROM DraftMediaLinks
;
*/

/*
-- 4. Tag the media to the Citations
INSERT INTO MediaLinkTable
SELECT * FROM DraftMediaLinks
;

--NOW INSPECT THE RESULT IN RM. USE ITS DATABASE TOOLS > REBUILD INDEXES
*/

/*
-- 5. Untag the media from the Sources
DELETE FROM MediaLinkTable
WHERE LinkID IN (SELECT LinkID FROM zSourceMediaLinks
;
*/