-- Definitions for the AREA database
-- Author: Bruno Wolff III
-- Last Revision: 2016-09-04

-- Make sure database is set up properly

-- First connect as postgres superuser

\c - postgres

-- Make sure that not everyone can create schemas or temporary tables

revoke all on database :DBNAME from public;
grant connect on database :DBNAME to public;

-- Delete the public schema if it exists

drop schema public cascade;

-- Create the public schema with limited access

begin;
create schema public;
revoke all on schema public from public;
grant usage on schema public to public;
grant all on schema public to bruno;
end;

-- bruno now has enough rights that we can use his account to finish up

set session authorization bruno;

-- We should now have a clean database with appropriate extensions and
-- can set up the AREA specific stuff.

-- Privacy levels
-- This table is used in views to change fields to null if the privacy
-- level of the request is less than the privacy level of the row.

-- priv   Table name
-- pname  Name to be used to reference the privacy level
-- pord   A number used to order the privacy levels
-- ptxt   A text description that can be used in a prompt

-- pname values:
-- any       The data can be used for anything
-- web       The data can be placed on public web pages
-- forward   The data can be used to forward messages from the public
-- request   The data can be given out in response to one off requests
-- member    The data can be given to other AREA members
-- td        The data can be given to tournament directors
-- admin     The data can be always be seen by the AREA administrators

create table priv (
  pname text primary key,
  pord int4 not null unique constraint pord_nonnegative check (pord >= 0),
  ptxt text
);

-- Allow access to it from the web server account

grant select on priv to public;

-- The exact numbers used isn't relevant as only the ordering matters.
-- However leaving room to insert new levels in without changing
-- old ones seems like a good idea.

copy priv from stdin using delimiters ',';
any,0,No restrictions on access
web,100,Allow anonymous access via the web
forward,200,Allow use in blind forwarding
request,300,Allow access via one off requests
member,400,Allow access by people believed to be AREA members
td,500,Allow access by tournament directors
admin,600,Access by AREA administrators is always allowed
\.

-- Valid generation labels other than roman numerals

-- genlab  The last name suffix to be displayed
-- gen     The generation number this label implies

create table genlabs (
  genlab text constraint bad_genlab check
    (genlab ~ '^[^[:cntrl:][:space:]]+( [^[:cntrl:][:space:]]+)*$'),
  gen int4 constraint bad_generation check (gen > 0 and gen < 4000),
  primary key (genlab, gen)
);

-- Allow access to it from the web server account

grant select on genlabs to public;

-- This stuff is pretty constant so load it at database creation

copy genlabs from stdin using delimiters ',';
Sr,1
Jr,2
\.

-- The current name table definition.
-- This is the primary definition for AREA members
-- There will also need to be a history table to track areaid and name changes
-- so that the admins have a way to check on records to resolve problems.
-- Names are limited to US ASCII characters. They can contain letters (a-z)
-- with case being maintained. They may also have space, ', or - between two
-- letters. They will be sorted using the ASCII ordering with uppercase
-- characters treated as the lowercase equivalent.

-- cname      Table name
-- areaid     Current AREA ID of a person
-- displayid  The fancy cased version of area id
-- lname      Current last name of a person
-- fmname     Current first and middle names of a person
-- aname      Current alternate first and middle names of a person
-- gen        Generation number (Sr or I > 1, Jr or II > 2, III > 3, IV > 4, etc)
-- genlab     A label for the genartion if not using a roman numeral.
-- privacy    Privacy level for their name data
-- touched    When the areaid or name (not privacy) information last changed

create table cname (
  areaid text primary key constraint bad_char_in_id check
    (areaid ~ '^[ABCDEFGHIJKLMNOPQRSTUVWXYZ[:digit:]]{1,6}([.][[:digit:]]{2,})?$')
    constraint missing_lead_zeros check (areaid !~ '^[[:digit:]]{1,4}([.][[:digit:]]+)?$'),
  displayid text constraint ids_must_match check (upper(displayid) = areaid),
  lname text not null constraint bad_last_name check
    (lname ~ '^[[:alpha:]]+([ ''-][[:alpha:]]+)*$'),
  fmname text constraint bad_first_or_mid_name check
    (fmname ~ '^[[:alpha:]]+([ ''-][[:alpha:]]+)*$'),
  aname text constraint bad_alt_name check
    (aname ~ '^[[:alpha:]]+([ ''-][[:alpha:]]+)*$'),
  gen int4 constraint bad_generation check (gen > 0 and gen < 4000),
  genlab text constraint missing_gen check (genlab is null or gen is not null),
  privacy text not null constraint bad_privacy references priv,
  touched timestamp with time zone default current_timestamp not null,
  foreign key (genlab, gen) references genlabs
);

-- I think orderings by last name will be used enough to create an index.
-- The privacy checking may make it not so useful so its impact should
-- be tested. The default ordering is partially case insensitive, but I
-- want to use lower to force different cased last names to be equal.

create index lname_idx on cname (lower(lname));

-- No public access to cname

-- Web view of the above table
-- areaids are always considered public.
-- The name fields will only be available to the web server for
-- those people that said it was OK. This will include search
-- queries using these fields.
-- priv isn't needed and should be considered admin access only

create view cname_web with (security_barrier) as
  select
    areaid,
    displayid,
    case when a.pord <= b.pord then lname end as lname,
    case when a.pord <= b.pord then fmname end as fmname,
    case when a.pord <= b.pord then aname end as aname,
    case when a.pord <= b.pord then gen end as gen,
    case when a.pord <= b.pord then genlab end as genlab,
    case when a.pord <= b.pord then touched end as touched
  from cname, priv a, priv b
  where
    a.pname = privacy and
    b.pname = 'web';

-- Allow access to it from the web server account

grant select on cname_web to public;

-- Publisher table
-- This is used to provide consistant versions of publisher names and
-- a single place to keep track of a URL for the publisher.

-- publishers  Table name
-- pubid       Internal code for a publisher
-- pubname     The name of the publisher
-- puburl      An optional URL for the publisher

create table publishers (
  pubid text primary key constraint bad_char_in_id check
    (pubid ~ '^[[:upper:][:digit:]]+$'),
  pubname text not null constraint bad_char_in_name check
    (pubname ~ '^[^[:cntrl:][:space:]]+( [^[:cntrl:][:space:]]+)*$'),
  puburl text constraint bad_char_in_url check
    (puburl ~ '^[^[:cntrl:][:space:]]+$')
);

-- Allow access to it from the web server account

grant select on publishers to public;

-- Rating type table
-- This table defines valid rating type codes and provides a text description

-- rtype    Rating type code (e.g. WTA, TEAM, RACE, TWO, etc.)
-- descr    A description of the rating type

create table rtypes (
  rtype text primary key constraint bad_char_in_rtype check
    (rtype ~ '^[[:upper:][:digit:]]+$'),
  descr text not null constraint bad_char_in_descr check
    (descr ~ '^[^[:cntrl:][:space:]]+( [^[:cntrl:][:space:]]+)*$')
);

-- Allow access to it from the web server account

grant select on rtypes to public;

-- Contacts table
-- This table defines valid contact addresses (mostly for reporting results)
-- These are all public AREA email addresses

-- contact   Contact code
-- email     Email address

create table contacts (
  contact text primary key constraint bad_char_in_contact check
    (contact ~ '^[[:upper:][:digit:]]+$'),
  email text not null constraint bad_char_in_email check
    (email ~ '^[-_.[:alnum:]]+@[-_.[:alnum:]]+$')
);

-- Allow access to it from the web server account

grant select on contacts to public;

-- Game definition table
-- This is the primary definition for games.
-- Titles may only contain printable US ASCII characters and
-- imbedded spaces. Sorting will be done using the US ASCII colating
-- sequence with uppercase letters treated as lowercase.

-- games    Table name
-- gameid   Initially will be excel sheet name
-- title    The game title
-- rtype    Rating type (e.g. WTA, RACE, TEAM or TWO)
-- sol      Can the game win?
-- contact  Where to send results (by email)
-- touched  Last time gameid, or title (not ratings) changed

create table games (
  gameid text primary key constraint bad_char_in_id check
    (gameid ~ '^[[:upper:][:digit:]]+$'),
  title text unique not null constraint bad_char_in_title check
    (title ~ '^[^[:cntrl:][:space:]]+( [^[:cntrl:][:space:]]+)*$'),
  rtype text not null constraint bad_rtype references rtypes,
  sol bool not null,
  contact text not null constraint bad_contact references contacts,
  touched timestamp with time zone default current_timestamp not null
);

-- This probably won't help searching, but should help displaying the
-- sorted title index.

create index title_idx on games (lower(title));

-- Allow access to it from the web server account

grant select on games to public;

-- Table of publishers for a game

-- gamepubs   Table name
-- gameid    Game being published
-- pubid     One of the game's publishers

create table gamepubs (
  gameid text constraint bad_gameid references games,
  pubid text constraint bad_pubid references publishers,
  primary key (gameid, pubid)
);

-- Allow access to it from the web server account

grant select on gamepubs to public;

-- Table of wbc events (from any year, not just current)

-- wbc     Table name
-- code    A WBC code
-- event   The event title (may not always be known)
-- url     URL for the event page if any

create table wbc (
  code text primary key
    constraint bad_char_in_code check (code ~ '^[^[:cntrl:][:space:]]+$')
    constraint no_slash_in_code check (code !~ '/'),
  event text
    constraint bad_char_in_event check
      (event ~ '^[^[:cntrl:][:space:]]+( [^[:cntrl:][:space:]]+)*$'),
  url text
    constraint bad_char_in_url check (url ~ '^[^[:cntrl:][:space:]]+$')
);

-- For matching searches wbc codes are case insensitive and will be done
-- using lower case.

create unique index wbc_lower on wbc (lower(code));

-- Allow access to it from the web server account

grant select on wbc to public;

-- Table of WBC codes for games
-- There might be muliple entries for a single code or for a single game
-- because WBC events don't map 1 to 1 to games.
-- We can't have slashses (/) in the WBC code since we use the WBC code
-- in generated URLs. Unescaped slashes break relative references and
-- escaped ones are detected as not found before rewriting can catch
-- the request. Setting the base in generated html documents is one
-- possible work around if the WBC uses any in their codes.

-- wbcgames  Table name
-- code      WBC code from wbc table
-- gameid    gameid from games table

create table wbcgames (
  code text constraint bad_wbc_code references wbc,
  gameid text constraint bad_gameid references games,
  primary key (code, gameid)
);

-- Allow access to it from the web server account

grant select on wbcgames to public;

-- Current AREA ratings
-- Note this record should only be created for person - game pairs where
-- either the person has at least one recorded rated game or has specifically
-- notified AREA that they have an interest in the game.
-- This table should be used to retrive data even though it isn't the
-- ultimate source for data. It is too expensive to rederive this information
-- from the base data. If something bad happens, this table should be 
-- rebuildable from a transaction table that includes actions for the
-- results of rated games, expressing interest in a game, or making hand
-- corrections to fix problems.

-- crate    Table name
-- areaid   From the cname table
-- gameid   From the games table
-- rate     Current AREA rating
-- frq      Number of times payed rated games of this game
--          If frq is 0 the player has expressed interest in the game.
-- opp      Total number of different opponents played
-- rmp      Total number of rated games played remotely (PBM, PBEM, VASL, etc.)
-- trn      Total number of rated games played in tournaments played
-- rmc      Total number of rated games played in remote play competitions
-- gm       String indicating when last gm'd
-- touched  Timestamp from when information in this record was changed
--          Typically this should be the last time a match was entered
--          for this person.

create table crate (
  areaid text constraint bad_areaid references cname,
  gameid text constraint bad_gameid references games,
  rate int4 default 5000 not null constraint rate_nonnegative check (rate >= 0)
    constraint rate_other_interested check (frq > 0 or rate = 5000),
  frq int4 default 0 not null constraint frq_nonnegative check (frq >= 0),
  opp int4 default 0 not null constraint opp_nonnegative check (opp >= 0),
  rmp int4 default 0 not null constraint rmp_nonnegative check (rmp >= 0),
  trn int4 default 0 not null constraint trn_nonnegative check (trn >= 0),
  rmc int4 default 0 not null constraint rmc_nonnegative check (rmc >= 0),
  gm text,
  touched timestamp with time zone default current_timestamp not null,
  primary key (areaid, gameid)
);

-- Allow for fast access when searching by game and areaid.

create index crate_game on crate (gameid, areaid);

-- Allow for fast retrieval of when a player last played any game

create index crate_touched on crate (areaid, touched);

-- Allow access to it from the web server account

grant select on crate to public;

-- Related URL table
-- This is used to have links to other web sites related to a game displayed.

-- gameurls  Table name
-- gameid    Game the link applies to
-- url       The url for the link
-- comment   Text describing the relation
-- expires   When to stop displaying the link

create table gameurls (
  gameid text constraint bad_gameid references games,
  url text constraint bad_char_in_url check
    (url ~ '^[^[:cntrl:][:space:]]+$'),
  comment text not null constraint bad_char_in_comment check
    (comment ~ '^[^[:cntrl:][:space:]]+( [^[:cntrl:][:space:]]+)*$'),
  expires timestamp with time zone default 'infinity' not null,
  primary key (gameid, url)
);

-- Allow access to it from the web server account

grant select on gameurls to public;