58 lines
1.5 KiB
MySQL
58 lines
1.5 KiB
MySQL
|
drop table if exists users cascade;
|
||
|
|
create table users (
|
||
|
|
id uuid not null default gen_random_uuid() primary key,
|
||
|
|
name text,
|
||
|
|
username text not null,
|
||
|
|
password_hash text not null,
|
||
|
|
|
||
|
|
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),
|
||
|
|
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_outcome cascade;
|
||
|
|
create type game_outcome as enum (
|
||
|
|
'village_victory',
|
||
|
|
'wolves_victory'
|
||
|
|
);
|
||
|
|
|
||
|
|
drop table if exists games cascade;
|
||
|
|
create table games (
|
||
|
|
id uuid not null primary key,
|
||
|
|
outcome game_outcome,
|
||
|
|
state json not null,
|
||
|
|
story json not null,
|
||
|
|
|
||
|
|
started_at timestamp with time zone not null,
|
||
|
|
updated_at timestamp with time zone not null default now()
|
||
|
|
);
|
||
|
|
|
||
|
|
drop table if exists players;
|
||
|
|
create table players (
|
||
|
|
id uuid not null primary key,
|
||
|
|
user_id uuid references users(id)
|
||
|
|
);
|
||
|
|
|
||
|
|
drop table if exists game_players;
|
||
|
|
create table game_players (
|
||
|
|
game_id uuid not null references games(id),
|
||
|
|
player_id uuid not null references players(id),
|
||
|
|
|
||
|
|
primary key (game_id, player_id)
|
||
|
|
);
|