-- Notifications grouped by thread, filtered by repository, split into

-- participating and watching, and set aside when done (hub spec §10).

ALTER TABLE notifications ADD COLUMN repo_id INTEGER REFERENCES repositories(id) ON DELETE CASCADE;

-- The page the notification is about, without a fragment: one row per thread in the inbox.

ALTER TABLE notifications ADD COLUMN thread TEXT NOT NULL DEFAULT '';

ALTER TABLE notifications ADD COLUMN reason TEXT NOT NULL DEFAULT 'participating' CHECK (reason IN ('participating', 'watching'));

ALTER TABLE notifications ADD COLUMN done INTEGER NOT NULL DEFAULT 0;

UPDATE notifications SET thread = CASE WHEN instr(url, '#') > 0 THEN substr(url, 1, instr(url, '#') - 1) ELSE url END;

UPDATE notifications SET repo_id = (

    SELECT r.id FROM repositories r

    WHERE substr(notifications.url, 1, length('/' || r.owner || '/' || r.name || '/')) = '/' || r.owner || '/' || r.name || '/'

);

CREATE INDEX notifications_by_thread ON notifications (account_id, done, thread, at_ms DESC);