-- Users and tokens

-- !Ups

CREATE TABLE users (
                       id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
                       username VARCHAR UNIQUE NOT NULL,
                       gid VARCHAR UNIQUE NOT NULL,
                       password_digest VARCHAR
);

CREATE TABLE auth_tokens (
                             id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
                             user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                             token VARCHAR NOT NULL,
                             created_at TIMESTAMP NOT NULL,
                             expires_at TIMESTAMP NOT NULL
);

CREATE INDEX idx_auth_tokens_user_id_expires_at ON auth_tokens (user_id, expires_at);

-- !Downs

DROP INDEX idx_auth_tokens_user_id_expires_at;
DROP TABLE auth_tokens;
DROP TABLE users;