-- CitationSortOrders.sql
/* 2024-01-21 Tom Holden ve3meo

Sorts the order of Citations as viewed in the "Sources" pane of the Edit Person 
form by exploiting the undocumented SortOrder column in the CitationLinkTable. 
N.B.: The slide-in workflow option ignores this column and must be dsiabled.

Requires a SQLite Manager that supports run-time variables and the REGEXP 
function. Developed and tested with SQLite Expert Personal 5.5.8.619 (x64)
on a database under RootsMagic 9.1.3.0 on a Windows 10 pc.

A sort order can be chosen from several by entering one of the following codes
when prompted for SortKeys (case-insensitive):
SortKeys	Description/Effect
C		Citation Name ascending in alphabetical order
CID		CitationID ascending, earliest created citation at top of list
CID-	CitationID descending, most recently created citation at top of list
CM 		Citation Modified Date asc, most recently edited last
CM-		Citation Modified Date desc, most recently edited last
Q 		Citation Quality descending, best Evidence at top of list
QS 		Citation Quality descending, Source Name secondary, ascending
QSC 	Citation Quality desc, Source Name asc, Citation Name ascending
S		Source Name ascending in alphabetical order
SC		Source Name, Citation Name ascending
SID		SourceID ascending, earliest created source at top of list
SID-	SourceID descending, most recently created source at top of list
SM 		Source Modified Date asc, most recently edited last
SM-		Source Modified Date desc, most recently edited last
TID		CitationLinkID asc, earliest created link|tag|use at top of list
TID-	CitationLinkID desc, most recently created link|tag|use at top of list
TM 		Citation Tag (Link) Modified Date asc, most recently edited last
TM- 	Citation Tag (Link) Modified Date desc, most recently edited first

The second prompt, OverwriteSortOrderIntegersYN, asks whether you want to 
overwrite Integer values in the SortOrder column. Only Y or y will do so.
The reason for this constraint is the expectation (hope) that RM Inc will
develop further functionality including manual sorting of the sources for
a fact. Other implementations of such sorting in the RM app use integers.
Therefore, this script by default exempts integer driven sortorders from
its global changes. 
 
Note on 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


*/

--Gather the potential Sort Keys into a temporary View
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)
;

--SELECT * FROM CitationLinkView
;

UPDATE CitationLinkTable
SET SortOrder=
CASE UPPER($SortKeys)

 WHEN 'C'  -- Citation Name Ascending
 THEN
  (SELECT '▲'||CitationName FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)
   
 WHEN 'CID' -- CitationID Ascending
 THEN
  (SELECT '▲'||FORMAT('%+010i', CitationID) FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'CID-' -- CitationID Descending
 THEN
  (SELECT '▼'||FORMAT('%+010i', 10000000000-CitationID) FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'CM'  -- Citation Modified Date ascending
 THEN
  (SELECT cUTCModDate FROM CitationLinkView CLV 
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'CM-'  -- Citation Modified Date descending
 THEN
  (SELECT -cUTCModDate FROM CitationLinkView CLV 
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'Q'  -- Citation Quality descending
 THEN
  (SELECT '▲'||Quality FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)
   
 WHEN 'QS'  -- Citation Quality descending, Source name ascending
 THEN
  (SELECT '▲'||Quality||Name FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)
   
 WHEN 'QSC'  -- Citation Quality descending, Source name, Citation name ascending
 THEN
  (SELECT '▲'||Quality||Name||CitationName FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)
   
 WHEN 'S'  -- Source Name Ascending
 THEN
  (SELECT '▲'||Name FROM CitationLinkView CLV 
   WHERE CitationLinkTable.LinkID = CLV.LinkID)
 
 WHEN 'SC'  -- Source Name, Citation Name ascending
 THEN
  (SELECT '▲'||Name||CitationName FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)
   
 WHEN 'SID' -- SourceID Ascending
 THEN
  (SELECT '▲'||FORMAT('%+010i', SourceID) FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'SID-' -- SourceID Descending
 THEN
  (SELECT '▼'||FORMAT('%+010i', 10000000000-SourceID) FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'SM'  -- Source Modified Date ascending
 THEN
  (SELECT sUTCModDate FROM CitationLinkView CLV 
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'SM-'  -- Source Modified Date descending
 THEN
  (SELECT -sUTCModDate FROM CitationLinkView CLV 
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'TID' -- LinkID Ascending
 THEN
  (SELECT '▲'||FORMAT('%+010i', LinkID) FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'TID-' -- LinkID Descending
 THEN
  (SELECT '▼'||FORMAT('%+010i', 10000000000-LinkID) FROM CitationLinkView CLV
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'TM'  -- Citation Tag (Link) Modified Date ascending
 THEN
  (SELECT clUTCModDate FROM CitationLinkView CLV 
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 WHEN 'TM-'  -- Citation Tag (Link) Modified Date descending
 THEN
  (SELECT -clUTCModDate FROM CitationLinkView CLV 
   WHERE CitationLinkTable.LinkID = CLV.LinkID)

 ELSE SortOrder -- Change nothing
END

WHERE 
CASE UPPER($OverwriteSortOrderIntegersYN)
 WHEN 'Y' THEN 1
 ELSE
  CitationLinkTable.SortOrder NOT REGEXP '^[0-9]+$' -- Don't change integer values
END
;
------------END OF SCRIPT---------------