-- https://github.com/jeremyevans/rodauth
GRANT CREATE ON SCHEMA :schema TO :password_role;

CREATE COLLATION ignore_accents (provider = icu, locale = 'und-u-ks-level1-kc-true', deterministic = false);

CREATE TABLE account_statuses(
    account_status_id smallint,
    name text NOT NULL,

    UNIQUE (name),
    CONSTRAINT account_statuses_pk PRIMARY KEY (account_status_id)
);
INSERT INTO account_statuses (account_status_id, name) VALUES (1, 'Unverified');
INSERT INTO account_statuses (account_status_id, name) VALUES (2, 'Verified');
INSERT INTO account_statuses (account_status_id, name) VALUES (3, 'Closed');

CREATE TABLE accounts(
    account_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    status_id smallint NOT NULL DEFAULT 1,
    -- email citext UNIQUE,
    email text UNIQUE,
    name text,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),

    UNIQUE (email),
    CONSTRAINT accounts_pk PRIMARY KEY (account_id),
    CONSTRAINT accounts_account_statuses_fk FOREIGN KEY (status_id) REFERENCES account_statuses(account_status_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,

    CONSTRAINT accounts_email_ck CHECK (char_length(email) <= 255)
);

CREATE INDEX accounts_email_uniq ON accounts (email) WHERE status_id IN (1, 2);

CREATE TABLE account_active_session_keys(
    session_id text NOT NULL,
    account_id bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    last_use timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT account_active_session_keys_pk PRIMARY KEY (session_id, account_id),
    CONSTRAINT account_active_session_keys_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION

);

CREATE OR REPLACE FUNCTION create_account(_email text, _password text)
RETURNS int
AS $$
    INSERT INTO accounts (email) VALUES (lower(_email)) RETURNING account_id;
$$
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION create_session(account_id bigint)
RETURNS text
AS $$
    INSERT INTO account_active_session_keys (session_id, account_id) VALUES 
    (pgsodium.crypto_auth_hmacsha256_keygen(), account_id) RETURNING session_id;
$$
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION get_account_by_session_id(_session_id text)
RETURNS SETOF accounts
AS $$
SELECT accounts.* FROM account_active_session_keys JOIN accounts USING(account_id) WHERE session_id = _session_id;
$$
LANGUAGE SQL;

GRANT SELECT, INSERT, UPDATE, DELETE ON account_statuses TO :password_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON accounts TO :password_role;

SET ROLE teamdraft_password;
\c :database_name

DROP TABLE IF EXISTS account_password_hashes;
CREATE TABLE account_password_hashes(
    account_id bigint NOT NULL,
    password_hash int NOT NULL,

    CONSTRAINT account_password_hashes_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE OR REPLACE FUNCTION get_salt(_account_id int8) RETURNS text AS $$
DECLARE salt text;
BEGIN
SELECT
CASE
    WHEN password_hash ~ '^\\$argon2id'
      THEN substring(password_hash from '\\$argon2id\\$v=\\d+\\$m=\\d+,t=\\d+,p=\\d+\\$.+\\$')
    ELSE substr(password_hash, 0, 30)
  END INTO salt
END
FROM account_password_hashes
WHERE _account_id = account_id;
RETURN salt;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = :schema, public;

CREATE OR REPLACE FUNCTION valid_password_hash(_account_id int8, hash text) RETURNS boolean AS $$
DECLARE valid boolean;
BEGIN
SELECT password_hash = hash INTO valid
FROM account_password_hashes
WHERE _account_id = account_id;
RETURN valid;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = :schema, public;

REVOKE ALL ON account_password_hashes FROM :schema;
REVOKE ALL ON FUNCTION get_salt(int8) FROM :schema;
REVOKE ALL ON FUNCTION valid_password_hash(int8, text) FROM :schema;
GRANT INSERT, UPDATE, DELETE ON account_password_hashes TO :password_role;
GRANT SELECT(account_id) ON account_password_hashes TO :password_role;
GRANT EXECUTE ON FUNCTION get_salt(int8) TO :password_role;
GRANT EXECUTE ON FUNCTION valid_password_hash(int8, text) TO :password_role;

SET ROLE teamdraft;