-- Database schema version 3

pragma foreign_keys = on;

create table Meta (
	version integer
);

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

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

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

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

create table Firebase (
	user integer not null,
	token text not null,
	foreign key(user) references Users(id) on delete cascade
);
create index firebase_user_index on Firebase(user);