CREATE TABLE IF NOT EXISTS domain_claims (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  did TEXT NOT NULL UNIQUE,
  domain TEXT NOT NULL UNIQUE,
  deleted TEXT
);

CREATE TABLE IF NOT EXISTS site_repos (
  repo_did TEXT PRIMARY KEY,
  owner_did TEXT NOT NULL,
  name TEXT NOT NULL,
  rkey TEXT NOT NULL,
  knot TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_site_repos_owner_did ON site_repos(owner_did);

CREATE TABLE IF NOT EXISTS repo_sites (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_did TEXT NOT NULL UNIQUE,
  branch TEXT NOT NULL,
  dir TEXT NOT NULL DEFAULT '/',
  is_index INTEGER NOT NULL DEFAULT 0,
  created TEXT NOT NULL,
  updated TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS site_deploys (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_did TEXT NOT NULL,
  branch TEXT NOT NULL,
  dir TEXT NOT NULL DEFAULT '/',
  commit_sha TEXT NOT NULL DEFAULT '',
  status TEXT NOT NULL CHECK (status IN ('success','failure')),
  trigger TEXT NOT NULL CHECK (trigger IN ('config_change','push')),
  error TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_site_deploys_repo_did ON site_deploys(repo_did);