/* SortDateSameDayOrderCustom.sql
   2011-12-20 ve3meo
   Alters SortDates of any set of Fact types 
   to a natural order when any pair or more occur on the same date. 
   Could be extended to order other facts also. SortDates are effectively assigned 
   (by arithmetical offsets) an absolute suffix -1, -2, ... related to the rank of the FactType.
   Affects only those events whose SortDates correspond to the Fact Date, as computed 
   by a Date encoding algorithm. The algorithm does not handle Date modifiers so not all 
   Event dates are handled, e.g. "Bef 1960". 
*/
DROP TABLE IF EXISTS TmpFactOrder
;
CREATE TEMP TABLE IF NOT EXISTS TmpFactOrder
(Rank INTEGER PRIMARY KEY, FactName TEXT)
;

/* list of Fact Names, standard and custom, to be sorted, in rank order.
   Revise the list to suit your needs */
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Birth');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Christen');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Baptism');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Death');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Funeral');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Cremation');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Burial');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Obituary');
INSERT INTO TmpFactOrder (Rank, FactName) VALUES (null, 'Memorial');
/* revise SortDates */
UPDATE EventTable 
SET SortDate = SortDate
  -6692012023  -- this offset goes to Date-1 if the event is a ranked event
  *( (
      SELECT Rank 
      FROM TmpFactOrder, 
           FactTypeTable 
      WHERE FactName LIKE Name 
      AND FactTypeID = EventType
      )>0
    )  
  +1048576  -- this offset adds steps of 1 to Date-1 multiplied by (rank-1)
  *( (
      SELECT Rank 
      FROM TmpFactOrder, 
           FactTypeTable 
      WHERE FactName LIKE Name 
      AND FactTypeID = EventType
      )-1
    ) -- maps the FactType to its order
WHERE EventID 
IN (SELECT EventID FROM EventTable
    INNER JOIN 
    (SELECT -- matching dates
     SortDate, OwnerID, COUNT()-1 AS Matches 
     FROM EventTable
     INNER JOIN FactTypeTable
     ON EventType = FactTypeID  
     WHERE EventTable.OwnerType = 0 
     AND Name IN (SELECT FactName FROM TmpFactOrder) 
     AND 
     SortDate =   -- equals encoded event Date (if not a match, suggests that user has modified SortDate so don't touch it)
       (CASE 
        WHEN Date LIKE '.%' 
        THEN 1 
        ELSE Substr(Date,3,5) END 
        +10000
        )*562949953421312 
        + Substr(Date,8,2)*35184372088832 
        + Substr(Date,10,2)*549755813888 
        + 17178820620 
      GROUP BY SortDate, OwnerID, EventTable.OwnerType
     )
     USING (OwnerID, SortDate)
     INNER JOIN FactTypeTable
     ON EventType = FactTypeID  
     WHERE Matches 
     AND EventTable.OwnerType = 0 
     AND Name IN (SELECT FactName FROM TmpFactOrder) 
    )
;