CREATE TABLE docker_mirrors (
  repo_id INTEGER NOT NULL,
  image TEXT NOT NULL DEFAULT '',
  remote_url TEXT NOT NULL DEFAULT '',
  tags TEXT NOT NULL DEFAULT '',
  username TEXT NOT NULL DEFAULT '',
  password TEXT NOT NULL DEFAULT '',
  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,
  PRIMARY KEY (repo_id, image),
  FOREIGN KEY (repo_id) REFERENCES repos(id) ON DELETE CASCADE
);

CREATE TABLE docker_mirror_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  repo_id INTEGER NOT NULL,
  image TEXT 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, image) REFERENCES docker_mirrors(repo_id, image) ON DELETE CASCADE
);

CREATE INDEX idx_docker_mirrors_due ON docker_mirrors(sync_enabled, sync_running, next_sync_at, dirty, updated_at);
CREATE INDEX idx_docker_mirror_runs_repo_image_started ON docker_mirror_runs(repo_id, image, started_at DESC);
CREATE INDEX idx_docker_mirror_runs_status ON docker_mirror_runs(status);