-- GPG signing keys. Each key is scoped as one of:
--   'personal' — owned by a user (owner_user_id); for verifying that user's work
--   'project'  — owned by a project (owner_project_id); signs that project's repos
--   'global'   — organization-wide; selectable by any project
-- owner_user_id / owner_project_id are NULL when not applicable (global keys
-- have both NULL). They are FKs with ON DELETE CASCADE so deleting the owning
-- user or project removes the key (and its private material) automatically.
CREATE TABLE IF NOT EXISTS gpg_keys (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  fingerprint TEXT NOT NULL DEFAULT '',
  key_id TEXT NOT NULL DEFAULT '',
  public_key TEXT NOT NULL DEFAULT '',
  private_key TEXT NOT NULL DEFAULT '',
  scope TEXT NOT NULL DEFAULT 'global',
  owner_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  owner_project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL DEFAULT 0
);

-- Lookup indexes. The composite (scope, owner_*) indexes also serve scope-only
-- lookups via their leftmost column:
--   scope='global'                          -> either index's prefix
--   scope='personal' AND owner_user_id=?    -> idx_gpg_keys_scope_user
--   scope='project'  AND owner_project_id=? -> idx_gpg_keys_scope_project
CREATE INDEX IF NOT EXISTS idx_gpg_keys_scope_user ON gpg_keys(scope, owner_user_id);
CREATE INDEX IF NOT EXISTS idx_gpg_keys_scope_project ON gpg_keys(scope, owner_project_id);
-- Fast verification lookup by fingerprint, and dedup, for personal public keys.
CREATE UNIQUE INDEX IF NOT EXISTS idx_gpg_keys_personal_fingerprint ON gpg_keys(fingerprint) WHERE scope = 'personal';

-- A repo may reference a signing key used to sign its commits/tags (git) or
-- packages/metadata (rpm). NULL means no signing key; the reference is cleared
-- automatically if the referenced key is deleted.
ALTER TABLE repos ADD COLUMN signing_key_id INTEGER REFERENCES gpg_keys(id) ON DELETE SET NULL;