create extension if not exists pgcrypto;
create extension if not exists citext;
create schema if not exists auth;

create table if not exists auth.identity (
       identity_id          bigserial          primary key
       , identity_public_id uuid               default gen_random_uuid()
       , created_at         timestamp not null default current_timestamp
       , unique(identity_public_id)
);

create table if not exists auth.email (
       email_id bigserial primary key
       , address text not null
);

create table if not exists auth.email_challenge_request (
       email_challenge_request_id bigserial primary key
       , email_id bigint not null references auth.email(email_id)
       , code text not null
       , created_at timestamp not null default current_timestamp
       , expires_at timestamp
       , revoked_at timestamp
);

create table if not exists auth.email_challenge_response (
       email_challenge_response_id bigserial primary key
       , email_challenge_request_id bigint not null references auth.email_challenge_request(email_challenge_request_id)
       , is_valid bool not null
       , raw_response text not null
       , created_at timestamp
);

create table if not exists auth.identity_email (
       identity_id bigint not null references auth.identity(identity_id)
       , email_id bigint not null references auth.email(email_id)
       , created_at timestamp not null default current_timestamp
       , deleted_at timestamp
);

create table if not exists auth.phone_number (
       phone_number_id bigserial primary key
       , digits text not null
);

create table if not exists auth.phone_number_challenge_request (
       phone_number_challenge_request_id bigserial primary key
       , phone_number_id bigint not null references auth.phone_number(phone_number_id)
       , code text not null
       , created_at timestamp not null default current_timestamp
       , expires_at timestamp
       , revoked_at timestamp
);

create table if not exists auth.phone_number_challenge_response (
       phone_number_challenge_response_id bigserial primary key
       , phone_number_challenge_request_id bigint not null references auth.phone_number_challenge_request(phone_number_challenge_request_id)
       , is_valid bool not null
       , raw_response text not null
       , created_at timestamp
);

create table if not exists auth.identity_phone_number (
       identity_id bigint not null references auth.identity(identity_id)
       , phone_number_id bigint not null references auth.phone_number(phone_number_id)
       , created_at timestamp not null default current_timestamp
       , deleted_at timestamp
);

create table if not exists auth.oauth_provider (
       oauth_provider_id bigserial primary key
       , provider text not null
       , consent_uri text not null
       , client_id text not null
       , client_secret_encrypted text not null
       , redirect_uri text
       , created_at timestamp not null default current_timestamp
       , unique(provider, client_id)
);

create table if not exists auth.oauth_request (
       oauth_request_id bigserial primary key
       , oauth_provider_id bigint not null references auth.oauth_provider(oauth_provider_id)
       , identity_id bigint not null references auth.identity(identity_id)
       , state text not null
       , created_at timestamp not null default current_timestamp
);

create table if not exists auth.oauth_response (
       oauth_response_id bigserial primary key
       , oauth_request_id bigint not null references auth.oauth_request(oauth_request_id)
       , is_error boolean not null
       , raw_response text not null
       , access_token text not null
       , expires_at timestamp not null
       , created_at timestamp not null default current_timestamp
);