-- See Eternaltwin documentation for SQLite for details abv

create table main.outbound_email (
  "outbound_email_id" blob not null, -- outbound_email_id
  "idempotency_key" blob not null, -- idempotency_key
  "created_by" blob not null, -- user_id
  "submitted_at" text not null, -- instant
  "deadline" text not null, -- instant
  "sender" text not null, -- email_address
  "recipient" text not null, -- email_address
  "payload_kind" text not null, -- email_payload_kind
  "payload_version" integer not null, -- u8
  "payload" text not null, -- jsonb
  "text_size" integer not null, -- u16
  "text_sha2_256" blob not null, -- digest_sha2_256
  "text_sha3_256" blob not null, -- digest_sha3_256
  "html_size" integer not null, -- u16
  "html_sha2_256" blob not null, -- digest_sha2_256
  "html_sha3_256" blob not null, -- digest_sha3_256
  "delivery_status" text not null, -- email_delivery_status
  "next_request_at" text null, -- instant
  "read_at" text null, -- instant
  primary key ("outbound_email_id")
) strict;

create table main.outbound_email_request (
  "outbound_email_request_id" blob not null check (length("outbound_email_request_id") = 16),
  "outbound_email_id" blob not null check (length("outbound_email_id") = 16),
  "period_start" text not null check (period_start = strftime('%FT%T+00:00', period_start)),
  "period_end" text not null check (period_end = strftime('%FT%T+00:00', period_end)),
  "status" text not null check ("status" in ('Pending', 'Cancelled', 'Timeout', 'Ok', 'Error')),
  "result_version" integer null,
  "result" blob null,
  primary key ("outbound_email_request_id"),
  constraint period_ck check (period_start < period_end),
  constraint result_ck check (result is null or (result is not null and result_version is not null)),
  constraint outbound_email_request__outbound_email__fk foreign key ("outbound_email_id") references outbound_email("outbound_email_id") on delete restrict on update restrict
) strict;