drop table if exists users cascade;
create table users (
	id uuid not null default gen_random_uuid() primary key,
	username text not null,
	display_name text,
	pronouns text,
	password_hash text not null,
	dummy boolean not null default false,

	created_at timestamp with time zone not null,
	updated_at timestamp with time zone not null,

	check (created_at <= updated_at)
);
drop index if exists users_username_idx;
create index users_username_idx on users (username);
drop index if exists users_username_unique;
create unique index users_username_unique on users (lower(username));

drop table if exists login_tokens cascade;
create table login_tokens (
	token text not null primary key,
	user_id uuid not null references users(id) on delete cascade,
	created_at timestamp with time zone not null,
	expires_at timestamp with time zone not null,

	check (created_at < expires_at)
);


drop type if exists game_status cascade;
create type game_status as enum (
	'Lobby',
	'RoleReveal',
	'Started',
	'GameOver',
	'Cancelled'
);

drop table if exists games cascade;
create table games (
	id uuid not null primary key,
	host uuid not null references users(id) on delete cascade,
	created_at timestamp with time zone not null default now(),
	game_state jsonb not null,
	game_status game_status not null default 'Lobby'
);

drop table if exists game_characters cascade;
create table game_characters (
	character_id uuid not null primary key,
	game_id uuid not null references games(id) on delete cascade,
	player_id uuid not null references users(id) on delete cascade,
	number integer
);

drop index if exists game_characters_player_id_game_id_unique;
create unique index game_characters_player_id_game_id_unique on game_characters (player_id, game_id);


drop table if exists dead_chat cascade;
create table dead_chat (
	message_id uuid not null primary key,
	game_id uuid not null references games(id) on delete cascade,
	created_at timestamp with time zone not null,
	message jsonb not null
);

drop index if exists dead_chat_created_at;
create index dead_chat_created_at on dead_chat(created_at);