pragma foreign_keys = on;

create table Rooms (
	id integer primary key,
	name text
);

create table RoomNames (
	name text primary key,
	room integer not null,
	foreign key(room) references Rooms(id) on delete cascade
);

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,
	username text,
	pass text
);

create table UserNames (
	name text primary key,
	user integer,
	foreign key(user) references Users(id) on delete cascade
);

create table Messages (
	id integer primary key,
	room integer not null,
	user integer null,
	time integer not null,
	message text,
	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);
