-- actions: squashed from 0700_actions.sql, 1000_runner_daemon.sql, 1001_runner_attestation_cache.sql.

-- Trigger index of the default branch's workflows, refreshed on push, so non-push events and
-- schedules can be matched without reading git.
CREATE TABLE workflow_triggers (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  workflow_path TEXT NOT NULL,
  workflow_name TEXT NOT NULL,
  events TEXT NOT NULL,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, workflow_path)
);

CREATE TABLE workflow_schedules (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  workflow_path TEXT NOT NULL,
  cron TEXT NOT NULL,
  next_run_at INTEGER NOT NULL,
  last_run_at INTEGER,
  UNIQUE (repo_id, workflow_path, cron)
);

CREATE INDEX workflow_schedules_due ON workflow_schedules(next_run_at);

CREATE TABLE action_caches (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  ref TEXT NOT NULL,
  key TEXT NOT NULL,
  version TEXT NOT NULL,
  size INTEGER NOT NULL,
  r2_key TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  last_used_at INTEGER NOT NULL,
  content_sha256 TEXT,
  UNIQUE (repo_id, ref, key, version)
);

CREATE INDEX action_caches_lru ON action_caches(repo_id, last_used_at);

CREATE INDEX action_caches_idle ON action_caches(last_used_at);

CREATE INDEX action_caches_object ON action_caches(r2_key);

CREATE TABLE repo_variables (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  value TEXT NOT NULL,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, name)
);

-- Owner-level (user or organization) secrets and variables; `visibility` is 'all', 'private' or 'selected'.
CREATE TABLE owner_secrets (
  owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  ciphertext TEXT NOT NULL,
  iv TEXT NOT NULL,
  visibility TEXT NOT NULL CHECK (visibility IN ('all', 'private', 'selected')),
  selected_repos TEXT NOT NULL DEFAULT '[]',
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (owner_id, name)
);

CREATE TABLE owner_variables (
  owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  value TEXT NOT NULL,
  visibility TEXT NOT NULL CHECK (visibility IN ('all', 'private', 'selected')),
  selected_repos TEXT NOT NULL DEFAULT '[]',
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (owner_id, name)
);

CREATE TABLE environments (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  wait_timer_minutes INTEGER NOT NULL DEFAULT 0,
  reviewers TEXT NOT NULL DEFAULT '[]',
  branch_patterns TEXT NOT NULL DEFAULT '[]',
  prevent_self_review INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  UNIQUE (repo_id, name)
);

CREATE TABLE environment_secrets (
  environment_id TEXT NOT NULL REFERENCES environments(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  ciphertext TEXT NOT NULL,
  iv TEXT NOT NULL,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (environment_id, name)
);

CREATE TABLE environment_variables (
  environment_id TEXT NOT NULL REFERENCES environments(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  value TEXT NOT NULL,
  updated_at INTEGER NOT NULL,
  PRIMARY KEY (environment_id, name)
);

CREATE TABLE deployments (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  environment_id TEXT NOT NULL REFERENCES environments(id) ON DELETE CASCADE,
  run_id TEXT NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE,
  job_id TEXT NOT NULL UNIQUE REFERENCES workflow_jobs(id) ON DELETE CASCADE,
  ref TEXT NOT NULL,
  sha TEXT NOT NULL,
  creator_id TEXT,
  status TEXT NOT NULL CHECK (status IN ('waiting', 'queued', 'in_progress', 'success', 'failure', 'cancelled', 'rejected')),
  url TEXT,
  wait_until INTEGER,
  reviewed_by TEXT,
  reviewed_at INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX deployments_repo ON deployments(repo_id, created_at DESC);

CREATE INDEX deployments_environment ON deployments(environment_id, created_at DESC);

-- Short-lived job-scoped credentials (the GITHUB_TOKEN equivalent); only hashes are stored.
CREATE TABLE job_tokens (
  token_hash TEXT PRIMARY KEY,
  job_id TEXT NOT NULL REFERENCES workflow_jobs(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  permissions TEXT NOT NULL,
  expires_at INTEGER NOT NULL
);

CREATE INDEX job_tokens_job ON job_tokens(job_id);

CREATE INDEX job_tokens_expiry ON job_tokens(expires_at);

-- Strongly consistent markers: commits pushed with job tokens (never trigger workflows) and processed
-- repository events (queue redelivery must not start duplicate runs).
CREATE TABLE job_token_pushes (
  repo_id TEXT NOT NULL,
  sha TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, sha)
);

CREATE TABLE workflow_event_receipts (
  id TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL
);

CREATE INDEX workflow_event_receipts_age ON workflow_event_receipts(created_at);

-- Self-hosted runner daemon: single-use registration tokens, rotating runner credentials and fork policy.
CREATE TABLE runner_registration_tokens (
  token_hash TEXT PRIMARY KEY,
  repo_id TEXT REFERENCES repos(id) ON DELETE CASCADE,
  owner_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
  created_by TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  labels TEXT NOT NULL DEFAULT '[]',
  expires_at INTEGER NOT NULL,
  used_at INTEGER,
  created_at INTEGER NOT NULL,
  isolation TEXT NOT NULL DEFAULT 'container',
  CHECK ((repo_id IS NULL) != (owner_id IS NULL))
);

CREATE INDEX runner_registration_tokens_expiry ON runner_registration_tokens(expires_at);

CREATE INDEX runner_registration_tokens_repo ON runner_registration_tokens(repo_id);

CREATE INDEX runner_registration_tokens_owner ON runner_registration_tokens(owner_id);

-- Fork pull request (untrusted) runs never reach self-hosted runners unless a repo admin opts in.
CREATE TABLE runner_policies (
  repo_id TEXT PRIMARY KEY REFERENCES repos(id) ON DELETE CASCADE,
  allow_fork_pull_requests INTEGER NOT NULL DEFAULT 0,
  updated_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  updated_at INTEGER NOT NULL
);

-- Self-hosted runner time (free but recorded) and Actions cache storage, consumed by the usage metering workstream.
CREATE TABLE runner_usage_events (
  id TEXT PRIMARY KEY,
  owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  repo_id TEXT REFERENCES repos(id) ON DELETE CASCADE,
  kind TEXT NOT NULL CHECK (kind IN ('self_hosted_runner_ms', 'cache_bytes_stored', 'cache_bytes_freed')),
  quantity INTEGER NOT NULL,
  subject_id TEXT,
  created_at INTEGER NOT NULL
);

CREATE INDEX runner_usage_events_owner ON runner_usage_events(owner_id, created_at);