-- identity: squashed from 0300_identity.sql, 1900_retired_account_names.sql.

CREATE TABLE recovery_codes (
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  code_hash TEXT NOT NULL,
  used_at INTEGER,
  PRIMARY KEY (user_id, code_hash)
);

CREATE TABLE passkeys (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  public_key TEXT NOT NULL,
  algorithm INTEGER NOT NULL,
  sign_count INTEGER NOT NULL DEFAULT 0,
  transports TEXT NOT NULL DEFAULT '[]',
  last_used_at INTEGER,
  created_at INTEGER NOT NULL
);

CREATE INDEX passkeys_user ON passkeys(user_id);

CREATE TABLE access_token_repos (
  token_id TEXT NOT NULL REFERENCES access_tokens(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  PRIMARY KEY (token_id, repo_id)
);

CREATE INDEX access_token_repos_repo ON access_token_repos(repo_id);

CREATE TABLE deploy_tokens (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  token_hash TEXT NOT NULL UNIQUE,
  token_prefix TEXT NOT NULL,
  permission TEXT NOT NULL CHECK (permission IN ('read', 'write')),
  created_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  last_used_at INTEGER,
  expires_at INTEGER,
  created_at INTEGER NOT NULL
);

CREATE INDEX deploy_tokens_repo ON deploy_tokens(repo_id);

CREATE TABLE notification_settings (
  user_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
  email_participating INTEGER NOT NULL DEFAULT 1,
  email_watching INTEGER NOT NULL DEFAULT 0,
  web_participating INTEGER NOT NULL DEFAULT 1,
  web_watching INTEGER NOT NULL DEFAULT 1,
  updated_at INTEGER NOT NULL
);

CREATE TABLE thread_subscriptions (
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  subscribed INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, issue_id)
);

CREATE INDEX thread_subscriptions_issue ON thread_subscriptions(issue_id);

CREATE TABLE follows (
  follower_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  followee_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (follower_id, followee_id)
);

CREATE INDEX follows_followee ON follows(followee_id);

CREATE TABLE account_redirects (
  old_name TEXT PRIMARY KEY COLLATE NOCASE,
  account_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL
);

CREATE TABLE repo_redirects (
  owner_name TEXT NOT NULL COLLATE NOCASE,
  repo_name TEXT NOT NULL COLLATE NOCASE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (owner_name, repo_name)
);

CREATE INDEX repo_redirects_repo ON repo_redirects(repo_id);

-- Single-use auth state (email links, WebAuthn challenges, pending 2FA logins). D1 rather than KV so
-- consumption is atomic (DELETE ... RETURNING / conditional UPDATE).
CREATE TABLE auth_tokens (
  token_hash TEXT PRIMARY KEY,
  kind TEXT NOT NULL,
  user_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
  payload TEXT NOT NULL DEFAULT '{}',
  attempts INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  expires_at INTEGER NOT NULL
);

CREATE INDEX auth_tokens_user ON auth_tokens(user_id, kind);

CREATE INDEX auth_tokens_expiry ON auth_tokens(expires_at);

-- Names of deleted accounts stay retired forever so nobody can claim them and take over old URLs or git remotes (GIT-9).
CREATE TABLE retired_account_names (
  name TEXT PRIMARY KEY COLLATE NOCASE,
  retired_at INTEGER NOT NULL
);