-- core: squashed from 0001_init.sql, 0002_ci_approvals.sql, 0003_repo_provisioning.sql, 0004_import_source.sql.

-- Accounts are the shared owner namespace for users and organizations.
CREATE TABLE accounts (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE COLLATE NOCASE,
  kind TEXT NOT NULL CHECK (kind IN ('user', 'org')),
  display_name TEXT,
  bio TEXT,
  website TEXT,
  created_at INTEGER NOT NULL,
  avatar_key TEXT
);

CREATE TABLE users (
  account_id TEXT PRIMARY KEY REFERENCES accounts(id) ON DELETE CASCADE,
  email TEXT NOT NULL UNIQUE COLLATE NOCASE,
  password_hash TEXT,
  github_id TEXT UNIQUE,
  is_admin INTEGER NOT NULL DEFAULT 0,
  email_verified_at INTEGER,
  totp_secret TEXT,
  totp_iv TEXT,
  totp_enabled_at INTEGER,
  totp_last_step INTEGER,
  sessions_revoked_at INTEGER
);

CREATE TABLE org_members (
  org_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'member')),
  created_at INTEGER NOT NULL,
  PRIMARY KEY (org_id, user_id)
);

CREATE TABLE access_tokens (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  token_hash TEXT NOT NULL UNIQUE,
  token_prefix TEXT NOT NULL,
  scopes TEXT NOT NULL,
  last_used_at INTEGER,
  expires_at INTEGER,
  created_at INTEGER NOT NULL,
  permissions TEXT,
  all_repos INTEGER NOT NULL DEFAULT 1,
  audience TEXT
);

CREATE INDEX access_tokens_user ON access_tokens(user_id);

-- However a token is deleted (Settings, the REST API, a password change, OAuth revocation), its OAuth
-- refresh tokens and every token it delegated go with it.
CREATE TRIGGER access_tokens_revoke_mcp BEFORE DELETE ON access_tokens
BEGIN
  DELETE FROM oauth_refresh_tokens WHERE access_token_id = OLD.id;
  DELETE FROM access_tokens WHERE id IN (SELECT token_id FROM mcp_token_agents WHERE parent_token_id = OLD.id);
END;

CREATE TABLE repos (
  id TEXT PRIMARY KEY,
  owner_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL COLLATE NOCASE,
  artifacts_name TEXT NOT NULL UNIQUE,
  artifacts_remote TEXT NOT NULL,
  description TEXT,
  website TEXT,
  visibility TEXT NOT NULL CHECK (visibility IN ('public', 'private')),
  default_branch TEXT NOT NULL DEFAULT 'main',
  status TEXT NOT NULL DEFAULT 'ready' CHECK (status IN ('ready', 'importing', 'forking', 'failed')),
  fork_of_id TEXT REFERENCES repos(id) ON DELETE SET NULL,
  is_archived INTEGER NOT NULL DEFAULT 0,
  has_issues INTEGER NOT NULL DEFAULT 1,
  has_actions INTEGER NOT NULL DEFAULT 1,
  stars_count INTEGER NOT NULL DEFAULT 0,
  forks_count INTEGER NOT NULL DEFAULT 0,
  open_issues_count INTEGER NOT NULL DEFAULT 0,
  open_pulls_count INTEGER NOT NULL DEFAULT 0,
  next_number INTEGER NOT NULL DEFAULT 1,
  next_run_number INTEGER NOT NULL DEFAULT 1,
  pushed_at INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  status_error TEXT,
  import_source_url TEXT,
  private_network INTEGER NOT NULL DEFAULT 0,
  bridged_into INTEGER NOT NULL DEFAULT 0,
  backfilling INTEGER NOT NULL DEFAULT 0,
  UNIQUE (owner_id, name)
);

CREATE INDEX repos_fork_of ON repos(fork_of_id);

CREATE INDEX repos_pushed ON repos(visibility, pushed_at DESC);

CREATE TRIGGER repos_owner_lfs AFTER UPDATE OF owner_id ON repos WHEN OLD.owner_id != NEW.owner_id BEGIN
  UPDATE lfs_objects SET owner_id = NEW.owner_id WHERE repo_id = NEW.id;
END;

CREATE TABLE collaborators (
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  permission TEXT NOT NULL CHECK (permission IN ('read', 'write', 'admin')),
  created_at INTEGER NOT NULL,
  PRIMARY KEY (repo_id, user_id)
);

CREATE INDEX collaborators_user ON collaborators(user_id);

CREATE TABLE stars (
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (user_id, repo_id)
);

CREATE INDEX stars_repo ON stars(repo_id);

CREATE TABLE watches (
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  level TEXT NOT NULL DEFAULT 'all',
  PRIMARY KEY (user_id, repo_id)
);

CREATE INDEX watches_repo ON watches(repo_id);

-- Issues and pull requests share a per-repo number sequence.
CREATE TABLE issues (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  number INTEGER NOT NULL,
  author_id TEXT NOT NULL REFERENCES accounts(id),
  title TEXT NOT NULL,
  body TEXT NOT NULL DEFAULT '',
  state TEXT NOT NULL CHECK (state IN ('open', 'closed')),
  is_pull INTEGER NOT NULL DEFAULT 0,
  comments_count INTEGER NOT NULL DEFAULT 0,
  locked INTEGER NOT NULL DEFAULT 0,
  closed_at INTEGER,
  closed_by TEXT REFERENCES accounts(id),
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  milestone_id TEXT REFERENCES milestones(id) ON DELETE SET NULL,
  type_id TEXT REFERENCES issue_types(id) ON DELETE SET NULL,
  parent_id TEXT REFERENCES issues(id) ON DELETE SET NULL,
  UNIQUE (repo_id, number)
);

CREATE INDEX issues_repo_state ON issues(repo_id, is_pull, state, number DESC);

CREATE INDEX issues_author ON issues(author_id);

CREATE INDEX issues_milestone ON issues(milestone_id, state);

CREATE INDEX issues_parent ON issues(parent_id, state);

CREATE TABLE pull_requests (
  issue_id TEXT PRIMARY KEY REFERENCES issues(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  head_repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  head_ref TEXT NOT NULL,
  head_sha TEXT NOT NULL,
  base_ref TEXT NOT NULL,
  base_sha TEXT NOT NULL,
  merge_base_sha TEXT,
  draft INTEGER NOT NULL DEFAULT 0,
  mergeable TEXT NOT NULL DEFAULT 'unknown' CHECK (mergeable IN ('unknown', 'clean', 'conflict')),
  merged_at INTEGER,
  merged_by TEXT REFERENCES accounts(id),
  merge_commit_sha TEXT,
  merge_method TEXT,
  refs_swept_at INTEGER
);

CREATE INDEX pull_requests_head ON pull_requests(head_repo_id, head_ref);

CREATE INDEX pull_requests_base ON pull_requests(repo_id, base_ref);

CREATE TABLE comments (
  id TEXT PRIMARY KEY,
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  author_id TEXT NOT NULL REFERENCES accounts(id),
  body TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX comments_issue ON comments(issue_id, created_at);

-- Timeline events: closed, reopened, labeled, merged, head pushed, etc.
CREATE TABLE issue_events (
  id TEXT PRIMARY KEY,
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  actor_id TEXT REFERENCES accounts(id),
  kind TEXT NOT NULL,
  data TEXT NOT NULL DEFAULT '{}',
  created_at INTEGER NOT NULL
);

CREATE INDEX issue_events_issue ON issue_events(issue_id, created_at);

CREATE TABLE reviews (
  id TEXT PRIMARY KEY,
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  author_id TEXT NOT NULL REFERENCES accounts(id),
  state TEXT NOT NULL CHECK (state IN ('approved', 'changes_requested', 'commented', 'dismissed')),
  body TEXT NOT NULL DEFAULT '',
  commit_sha TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  dismissed_by TEXT REFERENCES accounts(id),
  dismiss_reason TEXT
);

CREATE INDEX reviews_issue ON reviews(issue_id, created_at);

CREATE TABLE review_comments (
  id TEXT PRIMARY KEY,
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  review_id TEXT REFERENCES reviews(id) ON DELETE CASCADE,
  author_id TEXT NOT NULL REFERENCES accounts(id),
  path TEXT NOT NULL,
  line INTEGER NOT NULL,
  side TEXT NOT NULL CHECK (side IN ('LEFT', 'RIGHT')),
  commit_sha TEXT NOT NULL,
  body TEXT NOT NULL,
  in_reply_to TEXT REFERENCES review_comments(id) ON DELETE CASCADE,
  resolved INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  start_line INTEGER,
  start_side TEXT CHECK (start_side IN ('LEFT', 'RIGHT')),
  suggestion_applied_sha TEXT
);

CREATE INDEX review_comments_issue ON review_comments(issue_id, path, line);

CREATE TABLE review_requests (
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  created_at INTEGER NOT NULL,
  code_owner INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (issue_id, user_id)
);

CREATE TABLE labels (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  name TEXT NOT NULL COLLATE NOCASE,
  color TEXT NOT NULL,
  description TEXT,
  UNIQUE (repo_id, name)
);

CREATE TABLE issue_labels (
  issue_id TEXT NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  label_id TEXT NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
  PRIMARY KEY (issue_id, label_id)
);

CREATE INDEX issue_labels_label ON issue_labels(label_id);

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

CREATE INDEX issue_assignees_user ON issue_assignees(user_id);

CREATE TABLE branch_protections (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  pattern TEXT NOT NULL,
  require_pull_request INTEGER NOT NULL DEFAULT 1,
  required_approvals INTEGER NOT NULL DEFAULT 0,
  required_checks TEXT NOT NULL DEFAULT '[]',
  dismiss_stale_reviews INTEGER NOT NULL DEFAULT 0,
  allow_deletions INTEGER NOT NULL DEFAULT 0,
  enforce_admins INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  require_code_owner_review INTEGER NOT NULL DEFAULT 0,
  allow_force_pushes INTEGER NOT NULL DEFAULT 0,
  UNIQUE (repo_id, pattern)
);

CREATE TABLE webhooks (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  url TEXT NOT NULL,
  secret TEXT,
  events TEXT NOT NULL,
  active INTEGER NOT NULL DEFAULT 1,
  created_at INTEGER NOT NULL
);

CREATE INDEX webhooks_repo ON webhooks(repo_id);

CREATE TABLE webhook_deliveries (
  id TEXT PRIMARY KEY,
  webhook_id TEXT NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
  event TEXT NOT NULL,
  status_code INTEGER,
  error TEXT,
  request_body TEXT NOT NULL,
  response_body TEXT,
  duration_ms INTEGER,
  attempt INTEGER NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE INDEX webhook_deliveries_hook ON webhook_deliveries(webhook_id, created_at DESC);

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

CREATE TABLE workflow_runs (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  source_repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  run_number INTEGER NOT NULL,
  workflow_path TEXT NOT NULL,
  workflow_name TEXT NOT NULL,
  event TEXT NOT NULL,
  ref TEXT NOT NULL,
  sha TEXT NOT NULL,
  title TEXT NOT NULL,
  actor_id TEXT REFERENCES accounts(id),
  pr_number INTEGER,
  status TEXT NOT NULL CHECK (status IN ('queued', 'in_progress', 'completed')),
  conclusion TEXT CHECK (conclusion IN ('success', 'failure', 'cancelled', 'skipped')),
  created_at INTEGER NOT NULL,
  started_at INTEGER,
  completed_at INTEGER,
  approval_required INTEGER NOT NULL DEFAULT 0,
  workflow_source TEXT,
  context TEXT,
  trigger_depth INTEGER NOT NULL DEFAULT 0,
  rerun_of TEXT,
  untrusted INTEGER NOT NULL DEFAULT 0,
  UNIQUE (repo_id, run_number)
);

CREATE INDEX workflow_runs_repo ON workflow_runs(repo_id, created_at DESC);

CREATE INDEX workflow_runs_sha ON workflow_runs(repo_id, sha);

CREATE TABLE workflow_jobs (
  id TEXT PRIMARY KEY,
  run_id TEXT NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  job_key TEXT NOT NULL,
  name TEXT NOT NULL,
  runs_on TEXT NOT NULL,
  needs TEXT NOT NULL DEFAULT '[]',
  spec TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('pending', 'queued', 'in_progress', 'completed')),
  conclusion TEXT CHECK (conclusion IN ('success', 'failure', 'cancelled', 'skipped')),
  runner_id TEXT,
  runner_name TEXT,
  created_at INTEGER NOT NULL,
  started_at INTEGER,
  completed_at INTEGER,
  outputs TEXT,
  environment TEXT,
  gate TEXT,
  UNIQUE (run_id, job_key)
);

CREATE INDEX workflow_jobs_queue ON workflow_jobs(status, created_at);

CREATE INDEX workflow_jobs_runner ON workflow_jobs(runner_id, status);

CREATE TABLE job_steps (
  job_id TEXT NOT NULL REFERENCES workflow_jobs(id) ON DELETE CASCADE,
  idx INTEGER NOT NULL,
  name TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('pending', 'in_progress', 'completed')),
  conclusion TEXT CHECK (conclusion IN ('success', 'failure', 'cancelled', 'skipped')),
  started_at INTEGER,
  completed_at INTEGER,
  PRIMARY KEY (job_id, idx)
);

CREATE TABLE job_artifacts (
  id TEXT PRIMARY KEY,
  run_id TEXT NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE,
  job_id TEXT NOT NULL REFERENCES workflow_jobs(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  size INTEGER NOT NULL,
  r2_key TEXT NOT NULL,
  created_at INTEGER NOT NULL
);

CREATE INDEX job_artifacts_run ON job_artifacts(run_id);

CREATE TABLE commit_statuses (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  sha TEXT NOT NULL,
  context TEXT NOT NULL,
  state TEXT NOT NULL CHECK (state IN ('pending', 'success', 'failure', 'error')),
  description TEXT,
  target_url TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  workflow_path TEXT,
  workflow_hash TEXT,
  source TEXT CHECK (source IN ('ci', 'api')),
  UNIQUE (repo_id, sha, context)
);

-- Self-hosted runners are registered against a repo or an owner account.
CREATE TABLE runners (
  id TEXT PRIMARY KEY,
  repo_id TEXT REFERENCES repos(id) ON DELETE CASCADE,
  owner_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  labels TEXT NOT NULL,
  token_hash TEXT NOT NULL UNIQUE,
  last_seen_at INTEGER,
  created_at INTEGER NOT NULL,
  os TEXT NOT NULL DEFAULT '',
  arch TEXT NOT NULL DEFAULT '',
  version TEXT NOT NULL DEFAULT '',
  isolation TEXT NOT NULL DEFAULT 'none',
  max_jobs INTEGER NOT NULL DEFAULT 1,
  previous_token_hash TEXT,
  previous_token_expires_at INTEGER,
  token_rotated_at INTEGER,
  token_confirmed INTEGER NOT NULL DEFAULT 1,
  isolation_attested INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX runners_previous_token ON runners(previous_token_hash);

CREATE INDEX runners_repo ON runners(repo_id);

CREATE INDEX runners_owner ON runners(owner_id);

CREATE TABLE releases (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  tag TEXT NOT NULL,
  name TEXT NOT NULL,
  body TEXT NOT NULL DEFAULT '',
  author_id TEXT NOT NULL REFERENCES accounts(id),
  target_sha TEXT NOT NULL,
  draft INTEGER NOT NULL DEFAULT 0,
  prerelease INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  published_at INTEGER,
  UNIQUE (repo_id, tag)
);

CREATE TABLE release_assets (
  id TEXT PRIMARY KEY,
  release_id TEXT NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  size INTEGER NOT NULL,
  content_type TEXT NOT NULL,
  r2_key TEXT NOT NULL,
  download_count INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  UNIQUE (release_id, name)
);

CREATE TABLE activity (
  id TEXT PRIMARY KEY,
  actor_id TEXT REFERENCES accounts(id) ON DELETE CASCADE,
  repo_id TEXT REFERENCES repos(id) ON DELETE CASCADE,
  kind TEXT NOT NULL,
  data TEXT NOT NULL DEFAULT '{}',
  created_at INTEGER NOT NULL
);

CREATE INDEX activity_repo ON activity(repo_id, created_at DESC);

CREATE INDEX activity_actor ON activity(actor_id, created_at DESC);

CREATE TABLE notifications (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  repo_id TEXT REFERENCES repos(id) ON DELETE CASCADE,
  reason TEXT NOT NULL,
  title TEXT NOT NULL,
  url TEXT NOT NULL,
  read_at INTEGER,
  created_at INTEGER NOT NULL,
  category TEXT
);

CREATE INDEX notifications_user ON notifications(user_id, read_at, created_at DESC);

-- Deduplicates push processing across the git proxy and Artifacts event triggers.
CREATE TABLE push_events (
  id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
  ref TEXT NOT NULL,
  before_sha TEXT NOT NULL,
  after_sha TEXT NOT NULL,
  pusher_id TEXT REFERENCES accounts(id),
  created_at INTEGER NOT NULL
);

CREATE INDEX push_events_repo ON push_events(repo_id, created_at DESC);

CREATE VIRTUAL TABLE repos_fts USING fts5(repo_id UNINDEXED, full_name, description);

CREATE VIRTUAL TABLE issues_fts USING fts5(issue_id UNINDEXED, repo_id UNINDEXED, title, body);