-- contacts table
CREATE TABLE IF NOT EXISTS contacts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  nickname VARCHAR(64),
  public_key VARCHAR(64),
  first_name VARCHAR(64),
  last_name VARCHAR(64),
  bio VARCHAR(256),
  phone VARCHAR(20),
  last_time_connected INTEGER,
  socket_id VARCHAR(64),
  is_bridge boolean DEFAULT false
);
CREATE UNIQUE INDEX if not EXISTS nickname_unique on contacts ( nickname );
-- bridges table
CREATE TABLE IF NOT EXISTS bridges (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title VARCHAR(64),
  description VARCHAR(128),
  public_key VARCHAR(64),
  ip VARCHAR(128),
  port VARCHAR(128),
  last_perofile_version INTEGER,
  last_time_connected INTEGER
);
-- avatars table
CREATE TABLE IF NOT EXISTS avatars (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  contact_id INTEGER,
  file VARCHAR(128)
);
CREATE INDEX if NOT EXISTS contact_id_index on avatars ( contact_id );
-- pivot connections table
CREATE TABLE IF NOT EXISTS pivot_connections (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  contact_id INTEGER,
  bridge_id INTEGER 
);