-- 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
);