-- CitationSortTests.sql
/* 2024-01-20 Tom Holden ve3meo
Experiments in sorting Citations in Edit Person window
*/

DROP VIEW IF EXISTS CitationLinkView
;
CREATE TEMP VIEW CitationLinkView AS
SELECT
   CL.LinkID, CL.SortOrder, CL.Quality, CL.UTCModDate AS clUTCModDate
 , C.CitationID, C.CitationName, C.UTCModDate AS cUTCModDate 
 , S.SourceID, S.Name, S.UTCModDate AS sUTCModDate
FROM CitationLinkTable CL
JOIN CitationTable C USING (CitationID)
JOIN SourceTable S USING (SourceID)
;

/* Sort by SourceID
UPDATE CitationLinkTable
SET SortOrder=(SELECT SourceID FROM CitationLinkView CLV WHERE CitationLinkTable.LinkID = CLV.LinkID)
;
*/

/* Sort by Source Name
UPDATE CitationLinkTable
SET SortOrder=(SELECT Name FROM CitationLinkView CLV WHERE CitationLinkTable.LinkID = CLV.LinkID)
;
*/

/* Sort by Citation Name
UPDATE CitationLinkTable
SET SortOrder=(SELECT CitationName FROM CitationLinkView CLV WHERE CitationLinkTable.LinkID = CLV.LinkID)
;
*/

/* Sort by Source Name & CitationName
UPDATE CitationLinkTable
SET SortOrder=(SELECT Name||CitationName FROM CitationLinkView CLV WHERE CitationLinkTable.LinkID = CLV.LinkID)
;
*/

/* Sort by Citation Quality 
Relies on the characters associated with each of the categories and should result
in an order having Primary Information+Direct Evidence+OriginalSource first 
and Unknown+Unknown+Unknown last. 
See https://sqlitetoolsforrootsmagic.com/understanding-the-rootsmagic-8-database-type-decodes/#Citations
Could be combined with another field for secondary sort, e.g., Source Name

UPDATE CitationLinkTable
SET SortOrder=(SELECT Quality FROM CitationLinkView CLV WHERE CitationLinkTable.LinkID = CLV.LinkID)
;
*/

/* Sort by Citation Quality, Source Name 
UPDATE CitationLinkTable
SET SortOrder=(SELECT Quality||Name FROM CitationLinkView CLV WHERE CitationLinkTable.LinkID = CLV.LinkID)
;
*/

/* Sort by Citation ModDate ascending (oldest first)
UPDATE CitationLinkTable
SET SortOrder=(SELECT cUTCModDate FROM CitationLinkView CLV WHERE CitationLinkTable.LinkID = CLV.LinkID)
;
*/

/* Sort by Citation ModDate descending (most recent first)
UPDATE CitationLinkTable
SET SortOrder=(SELECT -cUTCModDate FROM CitationLinkView CLV WHERE CitationLinkTable.LinkID = CLV.LinkID)
;
*/