-- security: squashed from 0900_security.sql, 0901_trusted_checks.sql, 0902_signing_key_owners.sql, 0904_branch_force_push.sql.

CREATE TABLE repo_security_settings (
  repo_id TEXT PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
  secret_scanning INTEGER NOT NULL DEFAULT 1,
  push_protection INTEGER NOT NULL DEFAULT 0,
  private_reporting INTEGER NOT NULL DEFAULT 0,
  dependency_graph INTEGER NOT NULL DEFAULT 1,
  updated_at INTEGER NOT NULL
);

-- Secrets are never stored: only a SHA-256 fingerprint and a redacted preview.
CREATE TABLE secret_alerts (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  number INTEGER NOT NULL,
  detector TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  preview TEXT NOT NULL,
  path TEXT,
  line INTEGER,
  commit_sha TEXT,
  state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'resolved')),
  resolution TEXT CHECK (resolution IN ('revoked', 'false_positive', 'used_in_tests', 'wont_fix', 'bypassed')),
  resolved_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  resolved_at INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE (repo_id, fingerprint),
  UNIQUE (repo_id, number)
);

CREATE INDEX secret_alerts_state ON secret_alerts(repo_id, state, created_at DESC);

-- Pushes blocked by push protection, so the pusher can review and bypass with a reason.
CREATE TABLE secret_push_blocks (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  user_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
  detector TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  preview TEXT NOT NULL,
  blob_sha TEXT,
  bypassed_at INTEGER,
  created_at INTEGER NOT NULL
);

CREATE INDEX secret_push_blocks_repo ON secret_push_blocks(repo_id, created_at DESC);

CREATE TABLE security_advisories (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  ghsa_id TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  severity TEXT NOT NULL CHECK (severity IN ('low', 'moderate', 'high', 'critical')),
  cve_id TEXT,
  cwe_ids TEXT,
  ecosystem TEXT,
  package_name TEXT,
  affected_versions TEXT,
  patched_versions TEXT,
  state TEXT NOT NULL CHECK (state IN ('triage', 'draft', 'published', 'closed')),
  source TEXT NOT NULL CHECK (source IN ('maintainer', 'report')),
  author_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  published_at INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX security_advisories_repo ON security_advisories(repo_id, state, updated_at DESC);

CREATE INDEX security_advisories_package ON security_advisories(ecosystem, package_name, state);

CREATE TABLE advisory_collaborators (
  advisory_id TEXT NOT NULL REFERENCES security_advisories(id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (advisory_id, user_id)
);

CREATE TABLE advisory_comments (
  id TEXT PRIMARY KEY,
  advisory_id TEXT NOT NULL REFERENCES security_advisories(id) ON DELETE CASCADE,
  author_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  body TEXT NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE INDEX advisory_comments_advisory ON advisory_comments(advisory_id, created_at);

CREATE TABLE sarif_uploads (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  uploader_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  commit_sha TEXT NOT NULL,
  ref TEXT NOT NULL,
  r2_key TEXT NOT NULL,
  size INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'complete', 'failed')),
  error TEXT,
  tools TEXT,
  results_count INTEGER,
  created_at INTEGER NOT NULL,
  processed_at INTEGER
);

CREATE INDEX sarif_uploads_repo ON sarif_uploads(repo_id, created_at DESC);

CREATE TABLE code_scanning_alerts (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  number INTEGER NOT NULL,
  ref TEXT NOT NULL,
  tool TEXT NOT NULL,
  rule_id TEXT NOT NULL,
  rule_name TEXT,
  severity TEXT NOT NULL CHECK (severity IN ('error', 'warning', 'note', 'none')),
  security_severity TEXT,
  message TEXT NOT NULL,
  path TEXT,
  start_line INTEGER,
  end_line INTEGER,
  commit_sha TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'fixed', 'dismissed')),
  dismissed_reason TEXT,
  dismissed_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  last_upload_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE (repo_id, ref, tool, fingerprint),
  UNIQUE (repo_id, number)
);

CREATE INDEX code_scanning_alerts_ref ON code_scanning_alerts(repo_id, ref, state);

CREATE INDEX code_scanning_alerts_commit ON code_scanning_alerts(repo_id, commit_sha);

CREATE TABLE security_counters (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  kind TEXT NOT NULL,
  next_number INTEGER NOT NULL,
  PRIMARY KEY (repo_id, kind)
);

CREATE TABLE dependency_manifests (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  path TEXT NOT NULL,
  ecosystem TEXT NOT NULL,
  commit_sha TEXT NOT NULL,
  dependency_count INTEGER NOT NULL,
  scanned_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, path)
);

CREATE TABLE dependencies (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  manifest_path TEXT NOT NULL,
  ecosystem TEXT NOT NULL,
  name TEXT NOT NULL,
  requirement TEXT,
  scope TEXT NOT NULL CHECK (scope IN ('runtime', 'development')),
  direct INTEGER NOT NULL,
  PRIMARY KEY (repo_id, manifest_path, name, scope)
);

CREATE INDEX dependencies_package ON dependencies(ecosystem, name);

CREATE TABLE "signing_keys" (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  kind TEXT NOT NULL CHECK (kind IN ('ssh', 'gpg')),
  title TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  public_key TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  UNIQUE (user_id, kind, fingerprint)
);

CREATE INDEX signing_keys_user ON signing_keys(user_id);

CREATE INDEX signing_keys_fingerprint ON signing_keys(kind, fingerprint);

-- Every GPG primary key and subkey, so issuer key IDs resolve to the registered key.
CREATE TABLE "signing_key_ids" (
  key_id TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  signing_key_id TEXT NOT NULL REFERENCES "signing_keys"(id) ON DELETE CASCADE,
  PRIMARY KEY (fingerprint, signing_key_id)
);

CREATE INDEX signing_key_ids_key_id ON signing_key_ids(key_id);

CREATE INDEX signing_key_ids_signing_key ON signing_key_ids(signing_key_id);

-- Verification is recorded once per commit, mirroring how forges persist the first result.
CREATE TABLE commit_signatures (
  commit_sha TEXT PRIMARY KEY,
  verified INTEGER NOT NULL,
  reason TEXT NOT NULL,
  kind TEXT,
  key_fingerprint TEXT,
  signer_id TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  verified_at INTEGER NOT NULL
);

CREATE INDEX commit_signatures_key ON commit_signatures(key_fingerprint, reason);

-- Hidden-ref drops still owed after a visibility change; retried by the cron until they succeed.
CREATE TABLE hidden_ref_cleanups (
  repo_id TEXT PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
  attempts INTEGER NOT NULL DEFAULT 0,
  requested_at INTEGER NOT NULL,
  next_attempt_at INTEGER NOT NULL DEFAULT 0
);