-- Get available roles after authentication
DROP FUNCTION auth.check_otp(player_name_param CITEXT, otp CHAR(6));
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));
  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 static passwords which could be set via protected with HTTPS web interface
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 = 'users' AND n.nspname = 'auth' AND a.attname = 'web_password'
    ) THEN
        ALTER TABLE auth.users ADD COLUMN web_password TEXT 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 = 'users' AND n.nspname = 'auth' AND a.attname = 'game_password'
    ) THEN
        ALTER TABLE auth.users ADD COLUMN game_password TEXT NULL;
    END IF;
END
$$;

-- Add allowed client type for user in the game
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_type t
        INNER JOIN pg_namespace p ON t.typnamespace=p.oid
        WHERE t.typname='client_types' AND p.nspname='auth'
    ) THEN
        CREATE TYPE auth.client_types AS ENUM ('p', 'o', 'm');
    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 = 'players' AND n.nspname = 'games' AND a.attname = 'client_type'
    ) THEN
        ALTER TABLE games.players ADD COLUMN client_type auth.client_types NOT NULL DEFAULT 'p';
    END IF;
END
$$;

-- Share chat between servers (2020-10-03)
-- Choose one server where chat will remain alive
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 = 'chat_history' AND n.nspname = 'public' AND a.attname = 'server_id'
    ) THEN
        ALTER TABLE chat_history ADD COLUMN server_id INT NOT NULL DEFAULT 1;
    END IF;
END
$$;

-- Alter function to don't send OTP to delegate now:
DROP FUNCTION auth.check_contact;
CREATE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20))
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
 INNER 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;

-- Alter function to send OTP to delegate also:
DROP FUNCTION auth.check_contact;
CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid_param VARCHAR(20))
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
 INNER 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
 UNION ALL
 SELECT c.protocol, c.address
 FROM games.players p
 INNER JOIN auth.users u ON u.player_name = p.delegate_name
 INNER JOIN auth.contacts c ON c.player_name = u.player_name
  AND c.is_active = TRUE
  AND c.delete_ts IS NULL
 WHERE p.player_name = player_name_param
 AND p.game_uid = game_uid_param
 AND delegate_name IS NOT NULL;
 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 field for team id:
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 = 'players' AND n.nspname = 'games' AND a.attname = 'team_id'
    ) THEN
        ALTER TABLE games.players ADD COLUMN team_id INT NOT NULL DEFAULT -1;
    END IF;
END
$$;

-- Add field for delegate player:
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 = 'players' AND n.nspname = 'games' AND a.attname = 'delegate_name'
    ) THEN
        ALTER TABLE games.players ADD COLUMN delegate_name CITEXT REFERENCES auth.users(player_name) NULL;
    END IF;
END
$$;

-- Add new argument to accept game UID to search for player delegate:
DROP FUNCTION IF EXISTS auth.check_contact(player_name_param CITEXT, otp CHAR(6));
DROP FUNCTION IF EXISTS auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid VARCHAR(20));
CREATE OR REPLACE FUNCTION auth.check_contact(player_name_param CITEXT, otp CHAR(6), game_uid VARCHAR(20))
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;

-- Create table itself:
CREATE TABLE IF NOT EXISTS games.games (
 game_uid VARCHAR(20) PRIMARY KEY,
 start_ts TIMESTAMP WITHOUT TIME ZONE NOT NULL
);
GRANT SELECT ON games.games TO freeorion;
-- Create table to link users and games:
CREATE TABLE IF NOT EXISTS games.players (
 game_uid VARCHAR(20) REFERENCES games.games(game_uid),
 player_name CITEXT REFERENCES auth.users(player_name),
 is_confirmed BOOLEAN NOT NULL DEFAULT FALSE,
 species VARCHAR(20) NOT NULL DEFAULT 'RANDOM',
 CONSTRAINT pk_players PRIMARY KEY (game_uid, player_name)
);
GRANT SELECT ON games.players TO freeorion;