CREATE TABLE git_mirrors (
  repo_id INTEGER PRIMARY KEY,
  remote_url TEXT NOT NULL DEFAULT '',
  username TEXT NOT NULL DEFAULT '',
  password TEXT NOT NULL DEFAULT '',
  connect_host TEXT NOT NULL DEFAULT '',
  host_header TEXT NOT NULL DEFAULT '',
  tls_server_name TEXT NOT NULL DEFAULT '',
  tls_insecure_skip_verify INTEGER NOT NULL DEFAULT 0,
  pki_client_cert_id INTEGER REFERENCES pki_certs(id) ON DELETE SET NULL,
  prune INTEGER NOT NULL DEFAULT 1,
  force_update INTEGER NOT NULL DEFAULT 1,
  sync_interval_sec INTEGER NOT NULL DEFAULT 300,
  sync_enabled INTEGER NOT NULL DEFAULT 1,
  dirty INTEGER NOT NULL DEFAULT 1,
  next_sync_at INTEGER NOT NULL DEFAULT 0,
  sync_running INTEGER NOT NULL DEFAULT 0,
  sync_status TEXT NOT NULL DEFAULT 'idle',
  sync_error TEXT NOT NULL DEFAULT '',
  sync_step TEXT NOT NULL DEFAULT '',
  sync_total INTEGER NOT NULL DEFAULT 0,
  sync_done INTEGER NOT NULL DEFAULT 0,
  sync_failed INTEGER NOT NULL DEFAULT 0,
  last_sync_started_at INTEGER NOT NULL DEFAULT 0,
  last_sync_finished_at INTEGER NOT NULL DEFAULT 0,
  last_sync_success_at INTEGER NOT NULL DEFAULT 0,
  last_synced_revision TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (repo_id) REFERENCES repos(id) ON DELETE CASCADE
);

CREATE TABLE git_mirror_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  repo_id INTEGER NOT NULL,
  started_at INTEGER NOT NULL,
  finished_at INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'running',
  step TEXT NOT NULL DEFAULT '',
  total INTEGER NOT NULL DEFAULT 0,
  done INTEGER NOT NULL DEFAULT 0,
  failed INTEGER NOT NULL DEFAULT 0,
  revision TEXT NOT NULL DEFAULT '',
  error TEXT NOT NULL DEFAULT '',
  FOREIGN KEY(repo_id) REFERENCES git_mirrors(repo_id) ON DELETE CASCADE
);

CREATE INDEX idx_git_mirrors_due ON git_mirrors(sync_enabled, sync_running, next_sync_at, dirty, updated_at);
CREATE INDEX idx_git_mirror_runs_repo_started ON git_mirror_runs(repo_id, started_at DESC);
CREATE INDEX idx_git_mirror_runs_status ON git_mirror_runs(status);