-- 创建基础连接表
CREATE TABLE IF NOT EXISTS connections (
  id TEXT PRIMARY KEY,
  group_id TEXT NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  kind TEXT NOT NULL,
  color TEXT,
  auto_connect INTEGER DEFAULT 0,
  display_order INTEGER DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_connections_group_id ON connections(group_id);

-- 数据库连接特有信息
CREATE TABLE IF NOT EXISTS db_connections (
  connection_id TEXT PRIMARY KEY REFERENCES connections(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  version TEXT,
  server TEXT,
  port INTEGER,
  username TEXT,
  password TEXT,
  database_name TEXT,
  connection_string TEXT,
  use_ssh_tunnel INTEGER DEFAULT 0,
  ssh_tunnel_connection_id TEXT,
  last_connected DATETIME
);
CREATE INDEX IF NOT EXISTS idx_db_connections_server ON db_connections(server);

-- 服务器/主机类连接特有信息
CREATE TABLE IF NOT EXISTS server_connections (
  connection_id TEXT PRIMARY KEY REFERENCES connections(id) ON DELETE CASCADE,
  type TEXT NOT NULL,
  version TEXT,
  server TEXT,
  port INTEGER,
  username TEXT,
  auth_type TEXT,
  private_key TEXT,
  use_sudo INTEGER DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_server_connections_server ON server_connections(server);

-- 连接间关联/层级关系表
CREATE TABLE IF NOT EXISTS connection_links (
  id TEXT PRIMARY KEY,
  parent_connection_id TEXT NOT NULL REFERENCES connections(id) ON DELETE CASCADE,
  child_connection_id TEXT NOT NULL REFERENCES connections(id) ON DELETE CASCADE,
  relation_type TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_connection_links_parent ON connection_links(parent_connection_id);
CREATE INDEX IF NOT EXISTS idx_connection_links_child ON connection_links(child_connection_id);