create table if not exists realm (
       realm_id integer primary key
       , created_at integer not null
);

create table if not exists realm_data (
       realm_data_id integer primary key
       , realm_id integer not null references realm(realm_id)
       , email_provider text not null
       , sms_provider text not null
       , created_at integer not null
       , deleted_at integer
);

create table if not exists identity (
       identity_id integer primary key
       , identity_public_id uuid not null
       , data text -- we do not prescribe JSON or any other serialization format
       , created_at integer not null
       , updated_at integer not null
       , deleted_at integer
       , unique(identity_public_id)
);

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

create unique index if not exists credential_passphrase_type_key_ix
on credential (partial_key)
where type = 'Passphrase';

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

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

create table if not exists session (
       session_id integer primary key
       , identity_id integer not null references identity(identity_id)
       , token_hash blob not null
       , created_at integer not null
       , expired_at integer not null
       , revoked_at integer
       , unique(token_hash)
);

create table if not exists message (
       message_id integer primary key
       , address_id integer not null references address(address_id)
       , subject text
       , body text
       , template text not null
       , template_vars text not null
       , created_at integer not null
       , sent_at integer
);