-- Notifications: repo watch levels, issue subscriptions and per-user inbox rows.
-- Applied by store/migrate.rs. Timestamps are RFC 3339 UTC stamps from time.rs.

CREATE TABLE repo_watches (user_id TEXT NOT NULL REFERENCES users(id), repo_id TEXT NOT NULL REFERENCES repos(id),
  level TEXT NOT NULL, created_at TEXT NOT NULL, PRIMARY KEY (user_id, repo_id));
CREATE TABLE issue_subscriptions (user_id TEXT NOT NULL REFERENCES users(id), issue_id TEXT NOT NULL REFERENCES issues(id),
  state TEXT NOT NULL, reason TEXT NOT NULL, created_at TEXT NOT NULL, PRIMARY KEY (user_id, issue_id));
CREATE TABLE notifications (id TEXT PRIMARY KEY, user_id TEXT NOT NULL REFERENCES users(id),
  repo_id TEXT NOT NULL REFERENCES repos(id), issue_id TEXT NOT NULL REFERENCES issues(id),
  reason TEXT NOT NULL, last_activity TEXT NOT NULL, last_actor_id TEXT REFERENCES users(id),
  unread INTEGER NOT NULL DEFAULT 1, updated_at TEXT NOT NULL, UNIQUE (user_id, issue_id));
CREATE INDEX notifications_inbox ON notifications(user_id, unread, updated_at);