-- reorder cards
-- only for sentences...
-- so that I don’t “sing” them (reading the beginning and remembering the end)
-- reading, listening, speaking, writing
-- but for the moment: only sentences above 80%, otherwise I’m not going to read much
-- because each fact has up to four sentences, we reorder four times


ANALYZE; -- sqlite generates temporary indices if it knows tables are large...
BEGIN;
-- which facts with which sentences are affected?
-- ...and store the three created columns so we can swap them around later
-- ...because the UPDATE order can not be influenced (no ring swap possible).
CREATE TEMPORARY TABLE cards_to_swap AS
    -- sentence_number is 'sentence'(!), 'sentence2', 'sentence3' or 'sentence4'
    SELECT
        c.factId as factId,
        'sentence' || trim(substr(fm.name, 9, 1), '1') as sentence_number,
        min(created) as first,
        sum(created)-min(created)-max(created) as second,
        max(created) as third,
        f.value as percentage
    -- driving table: cards: only the sentence cards LSR with >=80%
    --   grouped together (three cards)
    -- each card has card model name, to filter: sentence y/n, LSR only
    -- each card has it’s corresponding percentage field (correct sentence number)
    --   we need the field model to find the percentage field
    FROM cards c, cardModels cm, fields f, fieldModels fm
    WHERE
        c.cardModelId = cm.id AND
        f.fieldModelId = fm.id AND
        c.factId = f.factId AND -- now I got all fields for each card
        fm.name LIKE 'Sentence_ Percentage' AND -- now only four percentage fields/card
        -- reduces to LSR sentences AND to a single percentage field for those cards
        replace(cm.name, sentence_number, 'HIT')
            IN ('Listening HIT', 'Speaking HIT', 'Reading HIT')
    GROUP BY
        c.factId, sentence_number -- each fact up to four sentences
    HAVING
        CAST(f.value AS INTEGER) >= 80 -- percentage is the only field left over
;
-- assign all affected cards their new order, i.e. new created field
-- for new cards: modified, due, combinedDue are unimportant
-- for suspended cards: I don’t care
-- for cards to review: too late
-- for syncing: set modified to “now”
UPDATE cards
    SET created = (SELECT CASE substr(cm.name, 1, 7)
                        WHEN 'Reading' THEN temp.first
                        WHEN 'Listeni' THEN temp.second
                        WHEN 'Speakin' THEN temp.third
                        END
                    FROM cards_to_swap temp, cardModels cm
                    WHERE cards.factId = temp.factId AND
                        cards.cardModelId = cm.id AND
                        -- cm.name.endswith(temp.sentence_number)
                        substr(cm.name, -length(temp.sentence_number))
                            = temp.sentence_number
                    )
    WHERE EXISTS (SELECT 1 FROM cards_to_swap temp2, cardModels cm
        WHERE
             cards.factId = temp2.factId AND
             cards.cardModelId = cm.id AND
             -- this replace() checks both: correct sentence number & l-s-r type
             replace(cm.name, temp2.sentence_number, 'HIT')
                IN ('Listening HIT', 'Speaking HIT', 'Reading HIT'));

-- relativeDelay = (2 new, 1 failed, 0 review), type (-= 3 suspended, = relativeDelay else)
UPDATE cards SET due = created, combinedDue = created WHERE relativeDelay = 2;
UPDATE cards SET modified = strftime('%s', 'now');
COMMIT;

