CREATE TABLE IF NOT EXISTS users (
  id                   INTEGER PRIMARY KEY AUTOINCREMENT,
  username             TEXT UNIQUE NOT NULL,
  password_hash        TEXT,
  created_at           TEXT NOT NULL,
  avatar_version       INTEGER NOT NULL DEFAULT 1,
  is_pending           INTEGER NOT NULL DEFAULT 0,
  register_application TEXT,
  passkey_setup_started_at TEXT
);

CREATE TABLE IF NOT EXISTS passkeys (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  credential_id TEXT UNIQUE NOT NULL,
  public_key    TEXT NOT NULL,
  counter       INTEGER NOT NULL DEFAULT 0,
  created_at    TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS sessions (
  id         TEXT PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS repositories (
  id             INTEGER PRIMARY KEY AUTOINCREMENT,
  name           TEXT UNIQUE NOT NULL,
  description    TEXT,
  is_private     INTEGER NOT NULL DEFAULT 0,
  is_pinned      INTEGER NOT NULL DEFAULT 0,
  default_branch TEXT NOT NULL DEFAULT 'main',
  created_at     TEXT NOT NULL,
  issue_seq          INTEGER NOT NULL DEFAULT 0,
  patch_seq          INTEGER NOT NULL DEFAULT 0,
  issue_template     TEXT,
  patch_template     TEXT,
  allow_user_labels  INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS issues (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_id    INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  author_id  INTEGER REFERENCES users(id) ON DELETE SET NULL,
  number     INTEGER NOT NULL,
  title      TEXT NOT NULL,
  body       TEXT NOT NULL DEFAULT '',
  status     TEXT NOT NULL DEFAULT 'open',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  edited_at  TEXT,
  UNIQUE(repo_id, number)
);

CREATE TABLE IF NOT EXISTS issue_comments (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  issue_id   INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  author_id  INTEGER REFERENCES users(id) ON DELETE SET NULL,
  body       TEXT NOT NULL,
  created_at TEXT NOT NULL,
  edited_at  TEXT
);

CREATE TABLE IF NOT EXISTS issue_reactions (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  issue_id   INTEGER NOT NULL REFERENCES issues(id) ON DELETE CASCADE,
  comment_id INTEGER REFERENCES issue_comments(id) ON DELETE CASCADE,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  emoji      TEXT NOT NULL,
  UNIQUE(issue_id, comment_id, user_id)
);

CREATE TABLE IF NOT EXISTS patches (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_id       INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  author_id     INTEGER REFERENCES users(id) ON DELETE SET NULL,
  number        INTEGER NOT NULL,
  title         TEXT NOT NULL,
  description   TEXT NOT NULL DEFAULT '',
  patch_content TEXT NOT NULL,
  status        TEXT NOT NULL DEFAULT 'open',
  author_name   TEXT NOT NULL DEFAULT '',
  author_email  TEXT NOT NULL DEFAULT '',
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL,
  edited_at     TEXT,
  version       TEXT NOT NULL DEFAULT '',
  UNIQUE(repo_id, number)
);

CREATE TABLE IF NOT EXISTS patch_comments (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  patch_id   INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
  author_id  INTEGER REFERENCES users(id) ON DELETE SET NULL,
  body       TEXT NOT NULL,
  created_at TEXT NOT NULL,
  edited_at  TEXT
);

CREATE TABLE IF NOT EXISTS patch_reactions (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  patch_id   INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
  comment_id INTEGER REFERENCES patch_comments(id) ON DELETE CASCADE,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  emoji      TEXT NOT NULL,
  UNIQUE(patch_id, comment_id, user_id)
);

CREATE TABLE IF NOT EXISTS ssh_keys (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  public_key  TEXT NOT NULL,
  fingerprint TEXT NOT NULL UNIQUE,
  created_at  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS releases (
  id                  INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_id             INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  tag_name            TEXT,
  name                TEXT NOT NULL,
  notes               TEXT,
  include_source_code INTEGER NOT NULL DEFAULT 0,
  created_at          TEXT NOT NULL,
  UNIQUE(repo_id, tag_name)
);

CREATE TABLE IF NOT EXISTS release_assets (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  release_id   INTEGER NOT NULL REFERENCES releases(id) ON DELETE CASCADE,
  filename     TEXT NOT NULL,
  size         INTEGER NOT NULL,
  created_at   TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS labels (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_id    INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  name       TEXT NOT NULL,
  color      TEXT NOT NULL DEFAULT '#808080',
  created_at TEXT NOT NULL,
  UNIQUE(repo_id, name)
);

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

CREATE TABLE IF NOT EXISTS patch_labels (
  patch_id INTEGER NOT NULL REFERENCES patches(id) ON DELETE CASCADE,
  label_id INTEGER NOT NULL REFERENCES labels(id) ON DELETE CASCADE,
  PRIMARY KEY (patch_id, label_id)
);

CREATE TABLE IF NOT EXISTS ci_runs (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_id          INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  triggered_by     INTEGER REFERENCES users(id) ON DELETE SET NULL,
  trigger_source   TEXT NOT NULL,
  commit_sha       TEXT,
  commit_branch    TEXT,
  commit_tag       TEXT,
  status           TEXT NOT NULL DEFAULT 'pending',
  variable_overrides TEXT,
  started_at       TEXT,
  finished_at      TEXT,
  created_at       TEXT NOT NULL DEFAULT (datetime('now')),
  repo_run_id      INTEGER
);

CREATE TABLE IF NOT EXISTS ci_steps (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id      INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending',
  exit_code   INTEGER,
  started_at  TEXT,
  finished_at TEXT,
  log         TEXT NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS ci_artifacts (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  run_id     INTEGER NOT NULL REFERENCES ci_runs(id) ON DELETE CASCADE,
  filename   TEXT NOT NULL,
  size       INTEGER NOT NULL,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS ci_secrets (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  repo_id     INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  value       TEXT NOT NULL,
  description TEXT,
  created_at  TEXT NOT NULL DEFAULT (datetime('now')),
  UNIQUE(repo_id, name)
);

CREATE TABLE IF NOT EXISTS ci_run_counters (
  repo_id     INTEGER PRIMARY KEY REFERENCES repositories(id) ON DELETE CASCADE,
  last_run_id INTEGER NOT NULL DEFAULT 0
);

-- Container registry. Blob and manifest bodies live under DATA_DIR/registry
-- keyed by digest; these tables hold the per-image index.
CREATE TABLE IF NOT EXISTS registry_blobs (
  repo_id INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  image   TEXT NOT NULL,
  digest  TEXT NOT NULL,
  size    INTEGER NOT NULL,
  PRIMARY KEY (repo_id, image, digest)
);

CREATE TABLE IF NOT EXISTS registry_manifests (
  repo_id    INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  image      TEXT NOT NULL,
  digest     TEXT NOT NULL,
  media_type TEXT NOT NULL,
  created_at TEXT NOT NULL,
  PRIMARY KEY (repo_id, image, digest)
);

CREATE TABLE IF NOT EXISTS registry_tags (
  repo_id    INTEGER NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
  image      TEXT NOT NULL,
  tag        TEXT NOT NULL,
  digest     TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  PRIMARY KEY (repo_id, image, tag)
);

-- Indexes for common query patterns
CREATE INDEX IF NOT EXISTS idx_issues_repo_id ON issues(repo_id);
CREATE INDEX IF NOT EXISTS idx_issues_author_id ON issues(author_id);
CREATE INDEX IF NOT EXISTS idx_issues_repo_status ON issues(repo_id, status);
CREATE INDEX IF NOT EXISTS idx_patches_repo_id ON patches(repo_id);
CREATE INDEX IF NOT EXISTS idx_patches_author_id ON patches(author_id);
CREATE INDEX IF NOT EXISTS idx_patches_repo_status ON patches(repo_id, status);
CREATE INDEX IF NOT EXISTS idx_ci_runs_repo_id ON ci_runs(repo_id);
CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_ssh_keys_user_id ON ssh_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_labels_repo_id ON labels(repo_id);
CREATE INDEX IF NOT EXISTS idx_issue_labels_label_id ON issue_labels(label_id);
CREATE INDEX IF NOT EXISTS idx_patch_labels_label_id ON patch_labels(label_id);