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

create table if not exists secd.realm (
       realm_id bigserial primary key
       , created_at timestamptz not null
);

create table if not exists secd.realm_data (
       realm_data_id bigserial primary key
       , realm_id bigint not null references secd.realm(realm_id)
       , email_provider jsonb not null
       , sms_provider jsonb not null
       , created_at timestamptz not null
       , deleted_at timestamptz
);

create table if not exists secd.identity (
       identity_id bigserial primary key
       , identity_public_id uuid not null
       , data jsonb -- some things are dervied, others are not
       , created_at timestamptz not null
       , updated_at timestamptz not null
       , deleted_at timestamptz
       , unique(identity_public_id)
);

create table if not exists secd.credential (
       credential_id bigserial primary key
       , credential_public_id uuid not null
       , identity_id bigint not null references secd.identity(identity_id)
       , type text not null-- e.g. password, oidc, totop, lookup_secret, webauthn, ...
       , data jsonb not null
       , version integer not null
       , created_at timestamptz not null
       , revoked_at timestamptz
       , deleted_at timestamptz
);

create table if not exists secd.address (
       address_id bigserial primary key
       , address_public_id uuid not null
       , type text not null
       , value text not null
       , created_at timestamptz not null
       , unique(value, type)
);

create table if not exists secd.address_validation (
       address_validation_id bigserial primary key
       , address_validation_public_id uuid not null
       , identity_id bigint references secd.identity(identity_id)
       , address_id bigint not null references secd.address(address_id)
       , method text not null -- e.g. email, sms, voice, oidc
       , token_hash bytea
       , code_hash bytea
       , attempts integer not null
       , created_at timestamptz not null
       , expires_at timestamptz not null
       , revoked_at timestamptz
       , validated_at timestamptz
       , unique(address_validation_public_id)
);

create table if not exists secd.session (
       session_id bigserial primary key
       , identity_id bigint not null references secd.identity(identity_id)
       , token_hash bytea not null
       , created_at timestamptz not null
       , expired_at timestamptz not null
       , revoked_at timestamptz
       , unique(token_hash)
);

create table if not exists secd.message (
       message_id bigserial primary key
       , address_id bigint not null references secd.address(address_id)
       , subject text
       , body text
       , template text not null
       , template_vars jsonb not null
       , created_at timestamptz not null
       , sent_at timestamptz
);