/**
 * AccountState: deploy tokens a user created, by repository and minting token, so revoking them visits only the
 * repositories that can hold one (src/server/deploy-token-index.ts).
 */
export const DEPLOY_TOKEN_INDEX_DDL = [
  `CREATE TABLE IF NOT EXISTS deploy_token_index (
  token_id TEXT PRIMARY KEY,
  repo_id TEXT NOT NULL,
  parent_token_id TEXT,
  created_at INTEGER NOT NULL
)`,
  "CREATE INDEX IF NOT EXISTS deploy_token_index_parent ON deploy_token_index (parent_token_id)",
  "CREATE INDEX IF NOT EXISTS deploy_token_index_repo ON deploy_token_index (repo_id)",
];

/**
 * AccountState: repositories the account has any activity in (`touched_at`), and the first and last time of
 * counted activity (null when only pushes or views), so the contribution graph and account deletion ask only
 * those repositories instead of every one (src/server/activity-index.ts).
 */
export const ACTIVITY_REPOS_DDL = [
  `CREATE TABLE IF NOT EXISTS activity_repos (
  repo_id TEXT PRIMARY KEY,
  first_at INTEGER,
  last_at INTEGER,
  touched_at INTEGER NOT NULL
)`,
  "CREATE INDEX IF NOT EXISTS activity_repos_last ON activity_repos (last_at)",
];

/**
 * AccountState: attachment bytes per repository that count against this account, as quota owner and as uploader,
 * so the upload quota check reads one object instead of every repository (src/server/attachment-bytes.ts).
 */
export const ATTACHMENT_BYTES_DDL = [
  `CREATE TABLE IF NOT EXISTS attachment_bytes (
  repo_id TEXT PRIMARY KEY,
  owned INTEGER NOT NULL DEFAULT 0,
  uploaded INTEGER NOT NULL DEFAULT 0
)`,
];

/**
 * RepoState: activity index writes that could not reach the actor's AccountState when the activity was written;
 * the repository's alarm retries them (src/server/repo-state/housekeeping.ts).
 */
export const ACTIVITY_INDEX_PENDING_DDL = [
  `CREATE TABLE IF NOT EXISTS activity_index_pending (
  actor_id TEXT NOT NULL,
  repo_id TEXT NOT NULL,
  at INTEGER NOT NULL,
  counted INTEGER NOT NULL
)`,
];

/**
 * Both scopes: work a write made due, keyed by task name with the time it was first marked, so the object's alarm
 * runs it once after a short debounce however many writes came in (account- and repo-state/housekeeping.ts).
 */
export const SCOPE_DIRTY_DDL = [
  `CREATE TABLE IF NOT EXISTS scope_dirty (
  name TEXT PRIMARY KEY,
  at INTEGER NOT NULL
)`,
];

/**
 * AccountState: storage this account is billed for, per repository or site scope and meter, written by each
 * scope's RepoState; the account's gauges are their sums (src/server/metering/scope-gauges.ts).
 */
export const SCOPE_GAUGES_DDL = [
  `CREATE TABLE IF NOT EXISTS scope_gauges (
  scope_id TEXT NOT NULL,
  meter TEXT NOT NULL,
  value INTEGER NOT NULL,
  PRIMARY KEY (scope_id, meter)
)`,
];

/** RepoState: the accounts this scope last reported gauges to, so a removed one is zeroed. */
export const GAUGE_ACCOUNTS_DDL = [`CREATE TABLE IF NOT EXISTS gauge_accounts (account_id TEXT PRIMARY KEY)`];

/** AccountState: runners already reported offline, and when, so each outage is reported once (notify/runners.ts). */
export const RUNNER_OFFLINE_NOTICES_DDL = [
  `CREATE TABLE IF NOT EXISTS runner_offline_notices (
  runner_id TEXT PRIMARY KEY,
  notified_at INTEGER NOT NULL
)`,
];

/** RepoState (repository or site scope): finished days of `site_usage` already folded into the billed meters. */
export const SITE_ROLLUPS_DDL = [`CREATE TABLE IF NOT EXISTS site_rollups (day TEXT PRIMARY KEY, at INTEGER NOT NULL)`];