Reminder.sq

all:
SELECT id FROM reminder ORDER BY scheduledFor DESC, title ASC;

urgentReminders:
SELECT id FROM reminder WHERE
    (scheduledFor <= strftime('%s','now', 'utc') OR scheduledFor <= strftime('%s', 'now', '+48 hours'))
    AND acknowledged = 0
ORDER BY scheduledFor ASC, created DESC;

upcomingReminders:
SELECT id FROM reminder WHERE
    (scheduledFor > (strftime('%s', 'now', '+48 hours'))) AND acknowledged = 0
ORDER BY scheduledFor ASC, created DESC;

pastReminders:
SELECT id FROM reminder WHERE acknowledged = 1 ORDER BY scheduledFor DESC, created DESC;

upsert:
INSERT INTO reminder (id, title, description, scheduledFor, created, lastModified, notificationId) VALUES (
    :id,
    :title,
    :description,
    :scheduledFor,
    :created,
    :lastModified,
    :notificationId
) ON CONFLICT (id) DO UPDATE SET
    title = :title,
    description = :description,
    scheduledFor = :scheduledFor,
    notificationId = :notificationId,
    lastModified = :lastModified;

delete:
DELETE FROM reminder WHERE id = :reminderId;

tagLetter:
INSERT INTO letterToReminder (letterId, reminderId) VALUES (
    :letterId, :reminderId
);

untagLetter:
DELETE FROM letterToReminder WHERE letterId = :letterId AND reminderId = :reminderId;

largestNotificationId:
   SELECT MAX(notificationId) FROM reminder LIMIT 1;

reminder:
SELECT * FROM reminder WHERE id = ?;

reminderDetail:
SELECT
    *,
    GROUP_CONCAT(letterToReminder.letterId) AS letterIds
FROM reminder
LEFT JOIN letterToReminder ON reminderId = reminder.id
WHERE id = :reminderId
GROUP BY reminder.id;

reminderInfo:
SELECT
    id,
    title,
    description,
    scheduledFor,
    acknowledged,
    created
FROM reminder WHERE id = :reminderId;

acknowledgeReminder:
UPDATE reminder SET acknowledged = 1 WHERE id = :reminderId;

notificationId:
SELECT notificationId FROM reminder WHERE id = :reminderId;

remindersForLetter:
SELECT reminderId FROM letterToReminder WHERE letterId = ?;

lettersForReminder:
SELECT letterId FROM letterToReminder WHERE reminderId = ?;

unacknowledgedReminders:
SELECT * FROM reminder WHERE acknowledged = 0;