-- Add up migration script here
create table players (
  id                         varchar(64) primary key not null
);

create table sessions (
  id   	     	             uuid primary key not null,
  player_id                  varchar(64) references players (id) not null,
  access_token	             varchar(2048) not null,
  access_token_expiration    timestamp with time zone not null,
  refresh_token		     varchar(1024) not null,
  last_refresh		     timestamp with time zone not null default now()
);

create table bridge_table (
  id			     uuid primary key not null
);

create type player_position as enum ('west', 'north', 'east', 'south');
create type suit as enum ('club', 'diamond', 'heart', 'spade');
create domain rank smallint check (value between 2 and 14);

-- TODO: Remove this.
create table object_journal (
  id   	     			uuid not null,
  seq				bigint not null,
  payload			jsonb not null
);
create unique index journal_entry on object_journal (id, seq);

create table table_players (
  table_id	     uuid not null references bridge_table (id),
  player_id                  varchar(64) not null references players (id),
  position		     player_position,
  primary key(table_id, player_id, position)
);
create unique index player_table on table_players (player_id);

create table table_boards (
   table_id uuid not null references bridge_table (id),
   board_number  integer not null,
   deal jsonb not null,
   primary key(table_id, board_number)
);

create table table_moves (
  table_id uuid not null,
  board_number integer not null,
  move_number integer not null,
  move jsonb not null,
  foreign key (table_id, board_number) references table_boards (table_id, board_number),
  primary key(table_id, board_number, move_number)
);