pragma foreign_keys = on;

create table Rooms (
	id integer primary key,
	name text
);
create unique index rooms_name_index on Rooms(name);

create table Members (
	room integer,
	user integer,
	primary key(room, user),
	foreign key(room) references Rooms(id) on delete cascade,
	foreign key(user) references Users(id) on delete cascade
);

create table Users (
	id integer primary key,
	name text,
	pass text
);
create unique index users_name_index on Users(name);

create table Messages (
	id integer primary key,
	room integer not null,
	user integer null,
	time integer not null,
	message blob,
	foreign key(room) references Rooms(id) on delete cascade,
	foreign key(user) references Users(id) on delete set null
);
create index messages_time_index on Messages(time);

create table Firebase (
	user integer,
	token text
);
create index firebase_user_index on Firebase(user);