/* init_order = 1 */

create table oidc_model_instances (
  tenant_id varchar(21) not null
    references tenants (id) on update cascade on delete cascade,
  model_name varchar(64) not null,
  id varchar(128) not null,
  payload jsonb /* @use OidcModelInstancePayload */ not null,
  expires_at timestamptz not null,
  consumed_at timestamptz,
  primary key (id),
  constraint oidc_model_instances__model_name_id
    unique (tenant_id, model_name, id)
);

create index oidc_model_instances__model_name_payload_user_code
  on oidc_model_instances (
    tenant_id,
    model_name,
    (payload->>'userCode')
  );

create index oidc_model_instances__model_name_payload_uid
  on oidc_model_instances (
    tenant_id,
    model_name,
    (payload->>'uid')
  );

/* TODO: Consider dropping this full data index if the partial index proves to be effective and safe. */
create index oidc_model_instances__model_name_payload_grant_id
  on oidc_model_instances (
    tenant_id,
    model_name,
    (payload->>'grantId')
  );

create index oidc_model_instances__model_name_payload_grant_id_partial
  on oidc_model_instances (tenant_id, model_name, (payload->>'grantId'))
  where payload ? 'grantId';

create index oidc_model_instances__expires_at
  on oidc_model_instances (tenant_id, expires_at);

create index oidc_model_instances__session_payload_account_id_expires_at
  on oidc_model_instances (tenant_id, (payload->>'accountId'), expires_at)
  WHERE model_name = 'Session';

create index oidc_model_instances__grant_payload_account_id_expires_at
  on oidc_model_instances (tenant_id, (payload->>'accountId'), expires_at)
  WHERE model_name = 'Grant';

alter table oidc_model_instances set (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 5000,
  autovacuum_analyze_threshold = 2000
);
