-- notify: squashed from 1700_notify.sql.

CREATE TABLE notification_category_prefs (
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  category TEXT NOT NULL,
  web INTEGER NOT NULL,
  email INTEGER NOT NULL,
  digest TEXT NOT NULL DEFAULT 'off',
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, category)
);

CREATE TABLE notification_digest_items (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  repo_id TEXT REFERENCES repos(id) ON DELETE CASCADE,
  category TEXT NOT NULL,
  title TEXT NOT NULL,
  url TEXT NOT NULL,
  account_id TEXT,
  subject TEXT,
  access TEXT NOT NULL,
  flush_after INTEGER NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE INDEX notification_digest_items_user ON notification_digest_items(user_id, category, created_at);

CREATE INDEX notification_digest_items_flush ON notification_digest_items(flush_after);

CREATE TABLE notification_email_counters (
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  window_start INTEGER NOT NULL,
  sent INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, window_start)
);

CREATE TABLE notification_unsubscribe_tokens (
  user_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
  token TEXT NOT NULL UNIQUE,
  created_at INTEGER NOT NULL
);

-- Org-level security managers receive security alerts for every repo in the org, like admins.
CREATE TABLE security_managers (
  account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (account_id, user_id)
);

-- Per-message, per-recipient markers so queue redeliveries never notify twice.
CREATE TABLE notification_deliveries (
  message_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (message_id, user_id)
);

CREATE INDEX notification_deliveries_created ON notification_deliveries(created_at);

-- Last CI failure/success per workflow and ref, so a success after a failure can send a "fixed" notice.
-- last_run_id/previous_conclusion make a retried finish step see the same "previous" as the first attempt.
CREATE TABLE ci_notify_state (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  workflow_path TEXT NOT NULL,
  ref TEXT NOT NULL,
  conclusion TEXT NOT NULL,
  last_run_id TEXT,
  previous_conclusion TEXT,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, workflow_path, ref)
);

-- Runners already reported offline, so the cron only notifies once per outage.
CREATE TABLE runner_offline_notices (
  runner_id TEXT PRIMARY KEY REFERENCES runners(id) ON DELETE CASCADE,
  notified_at INTEGER NOT NULL
);

-- Quota thresholds already reported per account, metric and period.
CREATE TABLE quota_notices (
  account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  metric TEXT NOT NULL,
  period TEXT NOT NULL,
  threshold INTEGER NOT NULL,
  notified_at INTEGER NOT NULL,
  PRIMARY KEY (account_id, metric, period, threshold)
);