114 lines
2.5 KiB
SQL
114 lines
2.5 KiB
SQL
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 plans cascade;
|
|
create table plans (
|
|
id uuid not null default gen_random_uuid() primary key,
|
|
title text,
|
|
created_by uuid not null references users(id),
|
|
start_time timestamp with time zone not null,
|
|
|
|
created_at timestamp with time zone not null,
|
|
updated_at timestamp with time zone not null,
|
|
|
|
check (created_at <= updated_at)
|
|
);
|
|
|
|
drop type if exists half_hour cascade;
|
|
create type half_hour as enum (
|
|
'Hour0Min0',
|
|
'Hour0Min30',
|
|
'Hour1Min0',
|
|
'Hour1Min30',
|
|
'Hour2Min0',
|
|
'Hour2Min30',
|
|
'Hour3Min0',
|
|
'Hour3Min30',
|
|
'Hour4Min0',
|
|
'Hour4Min30',
|
|
'Hour5Min0',
|
|
'Hour5Min30',
|
|
'Hour6Min0',
|
|
'Hour6Min30',
|
|
'Hour7Min0',
|
|
'Hour7Min30',
|
|
'Hour8Min0',
|
|
'Hour8Min30',
|
|
'Hour9Min0',
|
|
'Hour9Min30',
|
|
'Hour10Min0',
|
|
'Hour10Min30',
|
|
'Hour11Min0',
|
|
'Hour11Min30',
|
|
'Hour12Min0',
|
|
'Hour12Min30',
|
|
'Hour13Min0',
|
|
'Hour13Min30',
|
|
'Hour14Min0',
|
|
'Hour14Min30',
|
|
'Hour15Min0',
|
|
'Hour15Min30',
|
|
'Hour16Min0',
|
|
'Hour16Min30',
|
|
'Hour17Min0',
|
|
'Hour17Min30',
|
|
'Hour18Min0',
|
|
'Hour18Min30',
|
|
'Hour19Min0',
|
|
'Hour19Min30',
|
|
'Hour20Min0',
|
|
'Hour20Min30',
|
|
'Hour21Min0',
|
|
'Hour21Min30',
|
|
'Hour22Min0',
|
|
'Hour22Min30',
|
|
'Hour23Min0',
|
|
'Hour23Min30'
|
|
);
|
|
|
|
drop table if exists plan_days cascade;
|
|
create table plan_days (
|
|
id uuid not null default gen_random_uuid() primary key,
|
|
plan_id uuid not null references plans(id),
|
|
day_offset smallint not null,
|
|
day_start half_hour not null,
|
|
day_end half_hour not null,
|
|
|
|
check (day_offset >= 0),
|
|
unique(plan_id, day_offset)
|
|
);
|
|
|
|
drop table if exists day_tiles cascade;
|
|
create table day_tiles (
|
|
day_id uuid not null references plan_days(id),
|
|
user_id uuid not null references users(id),
|
|
tiles half_hour[] not null,
|
|
updated_at timestamp with time zone not null default now(),
|
|
|
|
unique(day_id, user_id)
|
|
);
|
|
|
|
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)
|
|
);
|