-- Add some notes to add in RSS
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_class c
        INNER JOIN pg_namespace n ON n.oid = c.relnamespace
        INNER JOIN pg_attribute a ON a.attrelid = c.oid
        WHERE c.relname = 'games' AND n.nspname = 'games' AND a.attname = 'notes_html'
    ) THEN
        ALTER TABLE games.games ADD COLUMN notes_html VARCHAR(4096) NULL;
    END IF;
END
$$;

-- Check permanent password without game uid
DROP FUNCTION IF EXISTS auth.check_otp(player_name_param CITEXT, otp CHAR(6));
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))
    OR u.game_password = crypt(otp, u.game_password)
  FROM auth.users u WHERE u.player_name = player_name_param;
$$ LANGUAGE sql VOLATILE;

-- Check permanent password
DROP FUNCTION IF EXISTS auth.check_otp(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20));
CREATE OR REPLACE FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20))
RETURNS TABLE (
 authenticated BOOLEAN,
 client_type auth.client_types) AS
$$
 BEGIN
  CREATE TEMP TABLE tmp_cnts ON COMMIT DROP 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))
    OR u.game_password = crypt(otp, u.game_password)
  FROM auth.users u WHERE u.player_name = player_name_param;
  RETURN QUERY SELECT t.*, p.client_type
  FROM tmp_cnts t
  CROSS JOIN games.players p
  WHERE p.player_name = player_name_param
  AND p.game_uid = game_uid_param;
 END
$$ LANGUAGE plpgsql;

-- Add table to store tokens to reset password
CREATE TABLE IF NOT EXISTS auth.reset_tokens (
 player_name CITEXT REFERENCES auth.users(player_name) PRIMARY KEY,
 token UUID NOT NULL,
 ip_address TEXT NULL,
 create_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
 last_error TEXT NULL,
 UNIQUE(token)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON auth.reset_tokens TO freeorion;

-- Add fields to games for RSS channel
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_class c
        INNER JOIN pg_namespace n ON n.oid = c.relnamespace
        INNER JOIN pg_attribute a ON a.attrelid = c.oid
        WHERE c.relname = 'games' AND n.nspname = 'games' AND a.attname = 'fo_forum_url'
    ) THEN
        ALTER TABLE games.games ADD COLUMN fo_forum_url VARCHAR(512) NULL;
    END IF;
    IF NOT EXISTS (
        SELECT 1 FROM pg_class c
        INNER JOIN pg_namespace n ON n.oid = c.relnamespace
        INNER JOIN pg_attribute a ON a.attrelid = c.oid
        WHERE c.relname = 'games' AND n.nspname = 'games' AND a.attname = 'create_ts'
    ) THEN
        ALTER TABLE games.games ADD COLUMN create_ts TIMESTAMP WITHOUT TIME ZONE NULL;
    END IF;
END
$$;

-- Add table to store turn time
CREATE TABLE IF NOT EXISTS games.turns (
 game_uid VARCHAR(20) REFERENCES games.games(game_uid),
 turn INT NOT NULL,
 turn_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL,
 CONSTRAINT pk_turns PRIMARY KEY (game_uid, turn)
);
GRANT SELECT, INSERT, UPDATE ON games.turns TO freeorion;

-- Allow NULL roles without game
DROP FUNCTION IF EXISTS auth.check_otp(player_name_param CITEXT, otp CHAR(6));
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;