# Setup SQL data
Public server uses PostgreSQL.
Create `freeorion` database:
```sql
CREATE DATABASE freeorion;
```
Create user `freeorion` for freeoriond server:
```sql
CREATE USER freeorion;
```
# Execute migrations
Migrations have format `YYYYMMDD_N_name...sql` Execute them on `freeorion` database.

CREATE SCHEMA IF NOT EXISTS games;
GRANT USAGE ON SCHEMA games TO freeorion;

-- Allow to use different protocols:
DROP FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6));
CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6))
RETURNS TABLE (
 protocol auth.contact_protocol,
 address CITEXT) AS
$$
BEGIN
 CREATE TEMP TABLE tmp_cnts ON COMMIT DROP AS
 SELECT c.protocol, c.address
 FROM auth.users u
 LEFT JOIN auth.contacts c ON c.player_name = u.player_name
  AND c.is_active = TRUE
  AND c.delete_ts IS NULL
 WHERE u.player_name = player_name_param;
 
 IF EXISTS (SELECT * FROM tmp_cnts t WHERE t.protocol IS NOT NULL AND t.address IS NOT NULL) THEN
  WITH hashed_otp AS (VALUES (crypt(otp, gen_salt('bf', 8))))
  INSERT INTO auth.otp (player_name, otp, create_ts)
  VALUES (player_name_param, (TABLE hashed_otp), NOW()::timestamp)
  ON CONFLICT (player_name) DO UPDATE SET otp = (TABLE hashed_otp), create_ts = NOW()::timestamp;
 END IF;
 RETURN QUERY SELECT *
 FROM tmp_cnts;
END
$$ LANGUAGE plpgsql;

-- Add new type to contact enum
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_type t
        INNER JOIN pg_namespace p ON t.typnamespace=p.oid
        INNER JOIN pg_enum e ON e.enumtypid = t.oid
        WHERE t.typname='contact_protocol' AND p.nspname='auth' AND e.enumlabel = 'email'
    ) THEN
        ALTER TYPE auth.contact_protocol ADD VALUE 'email'; 
    END IF;
END
$$;

-- Granting required priveleges for freeoriond:
GRANT SELECT ON auth.users TO freeorion;
GRANT SELECT ON auth.contacts TO freeorion;
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.otp TO freeorion;

-- Next create functions to work with auth data.
-- First is to check player in database. If player found function should save OTP and return active
-- contacts or `NULL` of there no available contacts. If there no player in database then function
-- returns 0 rows.
CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6))
RETURNS TABLE (
 protocol auth.contact_protocol,
 address CITEXT) AS
$$
BEGIN
 CREATE TEMP TABLE tmp_cnts ON COMMIT DROP AS
 SELECT c.protocol, c.address
 FROM auth.users u
 LEFT JOIN auth.contacts c ON c.player_name = u.player_name
  AND c.is_active = TRUE
  AND c.protocol = 'xmpp'
  AND c.delete_ts IS NULL
 WHERE u.player_name = player_name_param;
 
 IF EXISTS (SELECT * FROM tmp_cnts t WHERE t.protocol IS NOT NULL AND t.address IS NOT NULL) THEN
  WITH hashed_otp AS (VALUES (crypt(otp, gen_salt('bf', 8))))
  INSERT INTO auth.otp (player_name, otp, create_ts)
  VALUES (player_name_param, (TABLE hashed_otp), NOW()::timestamp)
  ON CONFLICT (player_name) DO UPDATE SET otp = (TABLE hashed_otp), create_ts = NOW()::timestamp;
 END IF;
 RETURN QUERY SELECT *
 FROM tmp_cnts;
END
$$ LANGUAGE plpgsql;
-- Second function checks if entered OTP correct:
CREATE OR REPLACE FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6))
RETURNS BOOLEAN AS
$$
  WITH hashed_otp AS (DELETE FROM auth.otp WHERE otp.player_name = player_name_param RETURNING otp.otp)
  SELECT (TABLE hashed_otp) IS NOT NULL AND (TABLE hashed_otp) = crypt(otp, (TABLE hashed_otp));
$$ LANGUAGE sql VOLATILE;

-- Create tables to store users, their contact data and OTPs:
CREATE TABLE IF NOT EXISTS auth.users (
 player_name CITEXT PRIMARY KEY,
 create_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL
);
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_type t
        INNER JOIN pg_namespace p ON t.typnamespace=p.oid
        WHERE t.typname='contact_protocol' AND p.nspname='auth'
    ) THEN
        CREATE TYPE auth.contact_protocol AS ENUM ('xmpp');
    END IF;
END
$$;
CREATE TABLE IF NOT EXISTS auth.contacts (
 player_name CITEXT REFERENCES auth.users(player_name),
 protocol auth.contact_protocol NOT NULL,
 address CITEXT NOT NULL,
 is_active BOOLEAN NOT NULL,
 create_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
 delete_ts TIMESTAMP WITHOUT TIME ZONE,
 CONSTRAINT pk_contact PRIMARY KEY (protocol, address)
);
CREATE TABLE IF NOT EXISTS auth.otp (
 player_name CITEXT PRIMARY KEY REFERENCES auth.users(player_name),
 otp TEXT NOT NULL,
 create_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL
);

-- Use separate schema as it contains sensitive data and grants access to freeoriond user
CREATE SCHEMA IF NOT EXISTS auth;
GRANT USAGE ON SCHEMA auth TO freeorion;

-- Enable `pgcrypto` extension to have safe storage for passwords:
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Enable `citext` extension to have case-insensitive column for logins and addresses;
CREATE EXTENSION IF NOT EXISTS citext;

-- Create table and grant freeoriond server to read from it and write new messages
CREATE TABLE IF NOT EXISTS chat_history(
 ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
 player_name VARCHAR(100) NULL,
 text TEXT NOT NULL,
 text_color BIGINT NULL
);
GRANT SELECT, INSERT ON chat_history TO freeorion;

-- Revoke privileges to create table for non-root user
REVOKE CREATE ON SCHEMA public FROM PUBLIC;