PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  email TEXT NOT NULL,
  password_hash TEXT,
  is_admin INTEGER NOT NULL DEFAULT 0,
  auth_source TEXT NOT NULL DEFAULT 'db',
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  disabled INTEGER NOT NULL DEFAULT 0
);

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

CREATE TABLE IF NOT EXISTS projects (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  home_page TEXT NOT NULL DEFAULT 'info',
  created_by INTEGER NOT NULL,
  updated_by INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  created_at_unix INTEGER NOT NULL DEFAULT 0,
  updated_at_unix INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (created_by) REFERENCES users(id)
  FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS project_members (
  project_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  role TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (project_id, user_id),
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS repos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  project_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  type TEXT NOT NULL DEFAULT 'git',
  path TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS issues (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  project_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  status TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  assignee_id INTEGER,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (assignee_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS issue_comments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  issue_id INTEGER NOT NULL,
  body TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP NOT NULL,
  FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS wiki_pages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  project_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  slug TEXT NOT NULL,
  body TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  UNIQUE (project_id, slug),
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS uploads (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  project_id INTEGER NOT NULL,
  filename TEXT NOT NULL,
  content_type TEXT NOT NULL,
  size INTEGER NOT NULL,
  storage_path TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  created_at TIMESTAMP NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS project_repos (
  project_id INTEGER NOT NULL,
  repo_id INTEGER NOT NULL,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (project_id, repo_id),
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (repo_id) REFERENCES repos(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS app_settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS api_keys (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  user_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  token_hash TEXT NOT NULL,
  token_prefix TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  last_used_at INTEGER NOT NULL DEFAULT 0,
  expires_at INTEGER NOT NULL DEFAULT 0,
  disabled INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS pki_cas (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL UNIQUE,
  parent_ca_id INTEGER,
  is_root INTEGER NOT NULL DEFAULT 0,
  cert_pem TEXT NOT NULL,
  key_pem TEXT NOT NULL,
  serial_counter INTEGER NOT NULL DEFAULT 1,
  status TEXT NOT NULL DEFAULT 'active',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY(parent_ca_id) REFERENCES pki_cas(id)
);

CREATE TABLE IF NOT EXISTS pki_certs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  ca_id INTEGER,
  serial_hex TEXT NOT NULL,
  common_name TEXT NOT NULL,
  san_dns TEXT NOT NULL DEFAULT '',
  san_ips TEXT NOT NULL DEFAULT '',
  is_ca INTEGER NOT NULL DEFAULT 0,
  cert_pem TEXT NOT NULL,
  key_pem TEXT NOT NULL,
  not_before INTEGER NOT NULL,
  not_after INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  revoked_at INTEGER NOT NULL DEFAULT 0,
  revocation_reason TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL,
  FOREIGN KEY(ca_id) REFERENCES pki_cas(id) ON DELETE CASCADE,
  UNIQUE(ca_id, serial_hex)
);

CREATE TABLE IF NOT EXISTS tls_listeners (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL UNIQUE,
  enabled INTEGER NOT NULL DEFAULT 1,
  http_addrs TEXT NOT NULL DEFAULT '',
  https_addrs TEXT NOT NULL DEFAULT '',
  tls_server_cert_source TEXT NOT NULL DEFAULT 'files',
  tls_cert_file TEXT NOT NULL DEFAULT '',
  tls_key_file TEXT NOT NULL DEFAULT '',
  tls_pki_server_cert_id TEXT NOT NULL DEFAULT '',
  tls_client_auth TEXT NOT NULL DEFAULT 'none',
  tls_client_ca_file TEXT NOT NULL DEFAULT '',
  tls_pki_client_ca_id TEXT NOT NULL DEFAULT '',
  tls_min_version TEXT NOT NULL DEFAULT '1.2',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  auth_policy TEXT NOT NULL DEFAULT 'default',
  apply_policy_api INTEGER NOT NULL DEFAULT 0,
  apply_policy_git INTEGER NOT NULL DEFAULT 0,
  apply_policy_rpm INTEGER NOT NULL DEFAULT 0,
  apply_policy_v2 INTEGER NOT NULL DEFAULT 0,
  client_cert_allowlist TEXT NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS service_principals (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL UNIQUE,
  description TEXT NOT NULL DEFAULT '',
  disabled INTEGER NOT NULL DEFAULT 0,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  is_admin INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS cert_principal_bindings (
  fingerprint TEXT PRIMARY KEY,
  principal_id INTEGER NOT NULL,
  enabled INTEGER NOT NULL DEFAULT 1,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (principal_id) REFERENCES service_principals(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS principal_project_roles (
  principal_id INTEGER NOT NULL,
  project_id INTEGER NOT NULL,
  role TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  PRIMARY KEY (principal_id, project_id),
  FOREIGN KEY (principal_id) REFERENCES service_principals(id) ON DELETE CASCADE,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rpm_repo_dirs (
  repo_id INTEGER NOT NULL,
  path TEXT NOT NULL,
  mode TEXT NOT NULL DEFAULT 'local',
  allow_delete INTEGER NOT NULL DEFAULT 0,
  remote_url 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,
  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,
  sync_deleted 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, path),
  FOREIGN KEY (repo_id) REFERENCES repos(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rpm_mirror_runs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  repo_id INTEGER NOT NULL,
  path 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,
  deleted INTEGER NOT NULL DEFAULT 0,
  revision TEXT NOT NULL DEFAULT '',
  error TEXT NOT NULL DEFAULT '',
  FOREIGN KEY(repo_id, path) REFERENCES rpm_repo_dirs(repo_id, path) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_users_disabled ON users(disabled);
CREATE INDEX IF NOT EXISTS idx_projects_name ON projects(name);
CREATE INDEX IF NOT EXISTS idx_repos_project ON repos(project_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_repos_project_name_type ON repos(project_id, name, type);
CREATE INDEX IF NOT EXISTS idx_issues_project ON issues(project_id);
CREATE INDEX IF NOT EXISTS idx_wiki_project ON wiki_pages(project_id);
CREATE INDEX IF NOT EXISTS idx_uploads_project ON uploads(project_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_api_keys_hash ON api_keys(token_hash);
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_expires_at ON api_keys(expires_at);
CREATE INDEX IF NOT EXISTS idx_api_keys_disabled ON api_keys(disabled);
CREATE INDEX IF NOT EXISTS idx_project_repos_project ON project_repos(project_id);
CREATE INDEX IF NOT EXISTS idx_project_repos_repo ON project_repos(repo_id);
CREATE INDEX IF NOT EXISTS idx_pki_cas_parent ON pki_cas(parent_ca_id);
CREATE INDEX IF NOT EXISTS idx_pki_certs_ca ON pki_certs(ca_id);
CREATE INDEX IF NOT EXISTS idx_cert_principal_bindings_principal_id ON cert_principal_bindings(principal_id);
CREATE INDEX IF NOT EXISTS idx_principal_project_roles_project_id ON principal_project_roles(project_id);