CREATE TABLE mlc_user(
  username TEXT PRIMARY KEY, 
  password TEXT,
  email TEXT
) WITHOUT ROWID;

CREATE TABLE mlc_device(
  id TEXT,
  username TEXT,
  PRIMARY KEY (id, username),
  FOREIGN KEY (username) REFERENCES mlc_user(username)
) WITHOUT ROWID;

CREATE TABLE mlc_group(id TEXT PRIMARY KEY) WITHOUT ROWID;

CREATE TABLE mlc_group_participant(
  id TEXT PRIMARY KEY,
  group_id BIGINT,
  device_id TEXT,
  username TEXT,
  FOREIGN KEY (group_id) REFERENCES mlc_group(id),
  FOREIGN KEY (device_id, username) REFERENCES mlc_device(id, username)
) WITHOUT ROWID;

CREATE TABLE mlc_keypackage(
  hash_ref BLOB PRIMARY KEY, 
  device_id TEXT,
  username TEXT,
  expiration BIGINT,
  given BOOLEAN,
  data BLOB,
  FOREIGN KEY (device_id, username) REFERENCES mlc_device(id, username)
) WITHOUT ROWID;

CREATE TABLE mlc_inbox(
  id TEXT PRIMARY KEY,
  device_id TEXT,
  username TEXT,
  FOREIGN KEY (device_id, username) REFERENCES mlc_device(id, username)
) WITHOUT ROWID;