CREATE TABLE IF NOT EXISTS ssh_servers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  host TEXT NOT NULL,
  port INTEGER NOT NULL DEFAULT 22,
  description TEXT NOT NULL DEFAULT '',
  tags_json TEXT NOT NULL DEFAULT '[]',
  enabled INTEGER NOT NULL DEFAULT 1,
  created_by_kind TEXT NOT NULL DEFAULT 'user',
  created_by_subject_id TEXT NOT NULL DEFAULT '',
  created_by_subject_name TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_ssh_servers_name ON ssh_servers(name);
CREATE INDEX IF NOT EXISTS idx_ssh_servers_host_port ON ssh_servers(host, port);

CREATE TABLE IF NOT EXISTS ssh_server_host_keys (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  server_public_id TEXT NOT NULL,
  algorithm TEXT NOT NULL,
  public_key TEXT NOT NULL,
  fingerprint TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  UNIQUE(server_public_id, fingerprint),
  FOREIGN KEY (server_public_id) REFERENCES ssh_servers(public_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_ssh_server_host_keys_server ON ssh_server_host_keys(server_public_id);

CREATE TABLE IF NOT EXISTS ssh_secrets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  kind TEXT NOT NULL,
  payload TEXT NOT NULL,
  metadata_json TEXT NOT NULL DEFAULT '{}',
  created_by_kind TEXT NOT NULL DEFAULT 'user',
  created_by_subject_id TEXT NOT NULL DEFAULT '',
  created_by_subject_name TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_ssh_secrets_kind ON ssh_secrets(kind);

CREATE TABLE IF NOT EXISTS ssh_access_profiles (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  server_public_id TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  remote_username TEXT NOT NULL,
  auth_method TEXT NOT NULL,
  owner_scope TEXT NOT NULL DEFAULT 'admin_shared',
  owner_user_public_id TEXT NOT NULL DEFAULT '',
  allow_user_edit INTEGER NOT NULL DEFAULT 0,
  enabled INTEGER NOT NULL DEFAULT 1,
  secret_public_id TEXT DEFAULT '',
  auth_public_key TEXT NOT NULL DEFAULT '',
  auth_public_key_fingerprint TEXT NOT NULL DEFAULT '',
  ssh_user_ca_public_id TEXT DEFAULT '',
  ssh_principal_mode TEXT NOT NULL DEFAULT 'explicit',
  ssh_principals_json TEXT NOT NULL DEFAULT '[]',
  ssh_principal_grant_ids_json TEXT NOT NULL DEFAULT '[]',
  default_valid_seconds INTEGER NOT NULL DEFAULT 3600,
  max_valid_seconds INTEGER NOT NULL DEFAULT 3600,
  created_by_kind TEXT NOT NULL DEFAULT 'user',
  created_by_subject_id TEXT NOT NULL DEFAULT '',
  created_by_subject_name TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (server_public_id) REFERENCES ssh_servers(public_id) ON DELETE CASCADE,
  FOREIGN KEY (secret_public_id) REFERENCES ssh_secrets(public_id) ON DELETE SET NULL,
  FOREIGN KEY (ssh_user_ca_public_id) REFERENCES ssh_user_cas(public_id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_server ON ssh_access_profiles(server_public_id);
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_owner_scope ON ssh_access_profiles(owner_scope);
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_owner_user ON ssh_access_profiles(owner_user_public_id);
CREATE INDEX IF NOT EXISTS idx_ssh_access_profiles_enabled ON ssh_access_profiles(enabled);

CREATE TABLE IF NOT EXISTS ssh_access_profile_targets (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  profile_public_id TEXT NOT NULL,
  target_type TEXT NOT NULL,
  target_public_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  UNIQUE(profile_public_id, target_type, target_public_id),
  FOREIGN KEY (profile_public_id) REFERENCES ssh_access_profiles(public_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_ssh_access_profile_targets_profile ON ssh_access_profile_targets(profile_public_id);
CREATE INDEX IF NOT EXISTS idx_ssh_access_profile_targets_target ON ssh_access_profile_targets(target_type, target_public_id);

CREATE TABLE IF NOT EXISTS ssh_sessions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  profile_public_id TEXT NOT NULL,
  server_public_id TEXT NOT NULL,
  user_public_id TEXT NOT NULL,
  username TEXT NOT NULL,
  remote_username TEXT NOT NULL,
  auth_method TEXT NOT NULL,
  host TEXT NOT NULL,
  port INTEGER NOT NULL,
  status TEXT NOT NULL,
  host_key_fingerprint TEXT NOT NULL DEFAULT '',
  requested_term TEXT NOT NULL DEFAULT 'xterm-256color',
  requested_cols INTEGER NOT NULL DEFAULT 80,
  requested_rows INTEGER NOT NULL DEFAULT 24,
  started_at INTEGER NOT NULL,
  connected_at INTEGER NOT NULL DEFAULT 0,
  ended_at INTEGER NOT NULL DEFAULT 0,
  remote_addr TEXT NOT NULL DEFAULT '',
  user_agent TEXT NOT NULL DEFAULT '',
  error TEXT NOT NULL DEFAULT '',
  FOREIGN KEY (profile_public_id) REFERENCES ssh_access_profiles(public_id) ON DELETE CASCADE,
  FOREIGN KEY (server_public_id) REFERENCES ssh_servers(public_id) ON DELETE CASCADE,
  FOREIGN KEY (user_public_id) REFERENCES users(public_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_ssh_sessions_user ON ssh_sessions(user_public_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_ssh_sessions_profile ON ssh_sessions(profile_public_id, started_at DESC);
CREATE INDEX IF NOT EXISTS idx_ssh_sessions_status ON ssh_sessions(status);